SQL实战经验 - 运算导致索引失效
Copyright © 2024 PawSQL
Problem
Let's say we have a b-tree index c_acctbal_idx defined on table tpch.customer to accelarate queries with a condition on column c_acctbal. Let's check a query as follows:
select * from tpch.customer where c_acctbal + 100 = 10000.0
Use explain to get the query plan:
-> Filter: ((customer.C_ACCTBAL + 100) = 10000.0) (cost=102301.50 rows=900955)
-> Table scan on customer (cost=102301.50 rows=900955)
From the query plan, we can see the database optimizer uses a table scan instead of the index lookup as we expected. The query cost is very high because of the full table scan and the arithmatic compution on each of the row.
Diagnosis
Let's replace the condition with an equivalent one by moving the calculation from left to right,
select * from tpch.customer where c_acctbal = 10000.0 - 100;
Use explain to get the query plan:
-> Index lookup on customer using c_acctbal_idx (C_ACCTBAL=(10000.0 - 100)) (cost=5.50 rows=5)
From the query plan , we can see the database optimizer chooses index lookup using index c_acctbal_idx and the cost is less than 1%% of the previous SQL query.
Diagnosis: From above experiment, we can conclude that if there is some calculation on indexed column, the index usage will be disabled. This conclusion is not only appliable to arthematic but also functions, and all other calculations on indexed column.
select * from tpch.orders where ADDDATE(o_orderdate, INTERVAL 31 DAY) =date '2019-10-10'
While following equivalent query can take advantage of index on o_orderdate
select * from tpch.orders where o_orderdate = subdate(date '2019-10-10' , INTERVAL 31 DAY);
Solution
There are two solutions to solve this problem,
-
one solution is to rewrite the original query to an equivalent one by moving the calculation to other side of the condition, as the examples in Diagnosis paragraph, so that the query can take advantage of existing indexes on the column.
-
the other solution is to add a functional index to match the condition.
create index phone_func_idx on customer(left(c_phone, 3))The functional index is not applicable to other conditions if the conditions don't have the same signature of the index definition. For example, the above index will not applicable to a condition
left(c_phone, 4) = '1399'.
PawSQL
PawSQL implements the first solution by automatically detecting the problematic predicates, automatically rewriting them into equivalent ones in rewrite optimization rule FuncWithColumnInPredicateRewrite.
Built-in Operators
PawSQL support following arithmatic operators and the combination of them.
| * | Multiplication operator |
|---|---|
| + | Addition operator |
| - | Minus operator |
| / | Division operator |
| - | Change the sign of the argument |
Built-in Functions
MySQL(8.0)
PawSQL for MySQL built-in functions,
| Name | Description | Column in function | PawSQL Rewrite |
|---|---|---|---|
| ADDDATE() | Add time values (intervals) to a date value | ADDDATE(column, 31) = date '2019-10-10' ADDDATE(column, INTERVAL 31 DAY) =date '2019-10-10' ; | column = subdate(date '2019-10-10' , 31) column = subdate(date '2019-10-10' , INTERVAL 31 DAY) |
| DATE_ADD() | Add time values (intervals) to a date value | DATE_ADD(column, 31) = date '2019-10-10' DATE_ADD(column, INTERVAL 31 DAY); | column = subdate(date '2019-10-10' , 31) column = subdate(date '2019-10-10' , INTERVAL 31 DAY) |
| DATE_SUB() | Subtract a time value (interval) from a date | DATE_SUB(column, 31) = date '2019-10-10' DATE_SUB(column, INTERVAL 31 DAY); | column = adddate(date '2019-10-10' , 31) column = adddate(date '2019-10-10' , INTERVAL 31 DAY) |
| SUBDATE() | Synonym for DATE_SUB() when invoked with three arguments | SUBDATE(column, 31) = date '2019-10-10' SUBDATE(column, INTERVAL 31 DAY); | column = adddate(date '2019-10-10' , 31) column = adddate(date '2019-10-10' , INTERVAL 31 DAY) |
| DATEDIFF() | Subtract two dates | DATEDIFF(currentdate(),col) > 7 | column < datesub(currentdate(),7) |
| ADDTIME() | Add time | ADDTIME(column, '02:00:00.999998') > '23:00:00.999998' | column> subtime('23:00:00.999998' - '02:00:00.999998') |
| SUBTIME() | Subtract times | ADDTIME(column, '02:00:00.999998') > '23:00:00.999998' | column> subtime('23:00:00.999998' - '02:00:00.999998') |
| TIMEDIFF() | Subtract time | TIMEDIFF(column, '2000-01-01 00:00:00.000001') = '10:10:1.0000001' | column = addtime('2000-01-01 00:00:00.000001', '10:10:1.0000001') |
| TIMESTAMPADD() | Add an interval to a datetime expression | TIMESTAMPADD(MINUTE,1,column )> '2003-01-02 00:01:00' | column > TIMESTAMPADD(MINUTE, -1,'2003-01-02 00:01:00' ) |
| TIMESTAMPDIFF() | Subtract an interval from a datetime expression | TIMESTAMPDIFF(MINUTE,CURRENT_TIMESTAMP(),column )> 10 | TIMESTAMPDIFF(MINUTE,10, CURRENT_TIMESTAMP())> col |
| SEC_TO_TIME() | Converts seconds to 'hh:mm:ss' format | SEC_TO_TIME(column)= '22:23:00' | column = TIME_TO_SEC('22:23:00') |
| TIME_TO_SEC() | Return the argument converted to seconds | TIME_TO_SEC(column)= 32435 | column = SEC_TO_TIME(32435) |
| FROM_DAYS() | Convert a day number to a date | FROM_DAYS(column)= '2022-10-10' | column = TO_DAYS('2022-10-10') |
| TO_DAYS() | Return the date argument converted to days | TO_DAYS(column)= 32435 | column = FROM_DAYS(32435) |
| FROM_UNIXTIME() | Format Unix timestamp as a date | FROM_UNIXTIME(column)= '2005-03-27 03:00:00' | column = UNIX_TIMESTAMP('2005-03-27 03:00:00') |
| UNIX_TIMESTAMP() | Return a Unix timestamp | UNIX_TIMESTAMP(column) = 1447431619 | column = FROM_UNIXTIME(1447431619) |
| INET_ATON() | Return the numeric value of an IP address | INET_ATON(column)= 13234234 | column = INET_NTOA(13234234) |
| INET_NTOA() | Return the IP address from a numeric value | INET_NTOA(column) = '201.1.1.2' | column = INET_ATON('201.1.1.2') |
| INET6_ATON() | Return the numeric value of an IPv6 address | INET_ATON(column)= 13234234 | column = INET_NTOA(13234234) |
| INET6_NTOA() | Return the IPv6 address from a numeric value | INET_NTOA(column) = '201.1.1.2' | column = INET_ATON('201.1.1.2') |
| LEFT() | Return the leftmost number of characters as specified | LEFT(column, 5) = 'abcde'; LEFT(column, 5) = 'abcd' | column like 'abcde%' column = 'abcd' |
| STRCMP() | Compare two strings | STRCMP(column, 'abc') = 1,-1, 0 | column > 'abc', col < 'abc', column = 'abc' |
| DATE_FORMAT() | Format date as specified | DATE_FORMAT(column, '%W %M %Y') = 'Sunday October 2009' | column = STR_TO_DATE( 'Sunday October 2009', '%m/%d/%Y') |
| STR_TO_DATE() | Convert a string to a date | STR_TO_DATE(column, '%m/%d/%Y') = '2022-10-10' | column = DATE_FORMAT('2022-10-10', '%m/%d/%Y') |
| YEAR() | Return the year | YEAR(column)= 1987 | column between Date('1987-01-01') and Date('1987-12-31') |
| EXTRACT() | Extract part of a date | EXTRACT(YEAR FROM col) ='2019' EXTRACT(YEAR_MONTH FROM col)='201910' | column between '2019-01-01' and '2019-12-31' |
| CAST() | Cast a value as a certain type | cast(column as type) = date '2022-01-01' | column = cast(date '2022-01-01' as coltype) |
| IFNULL() | Null if/else construct | IFNULL(column, 0) = 10 | column is null and 0=10 or col=10; |
| ISNULL() | Test whether the argument is NULL | ISNULL(column)= 1 ISNULL(column)= 0 | column is not null column is null |
Summarize
By applying the rewrite optimization FuncWithColumnInPredicateRewrite, the SQL query is not only able to
- take advantage of index to reduce the data access cost, but also able to
- avoid computing on each of row by moving calculation from on columns to on constant literals.
About PawSQL
PawSQL is dedicated to automatic and intelligent database performance optimization. The products provided by PawSQL include:
- PawSQL Cloud, an online automated SQL optimization tool that supports SQL auditing, intelligent query rewriting, cost-based index recommendations, suitable for database administrators and data application developers.
- PawSQL Advisor, an IntelliJ plugin that is suitable for data application developers and can be installed via the IDEA/DataGrip marketplace by searching for "PawSQL Advisor" by name.
- PawSQL Engine, which is the backend optimization engine of the PawSQL series of products, can be installed and deployed independently, and provides SQL optimization services through http/json interfaces. PawSQL Engine is provided for deployment and installation as a docker image.
Contact Us
Email: service@pawsql.com
Twitter: https://twitter.com/pawsql