适用于MySQL、PostgreSQL、Oracle等各种数据库的优化技巧
设想我们为customer
表的c_acctbal
列创建了一个B树索引c_acctbal_idx
,以加速相关查询。然而,一个看似无害的计算可能会阻碍索引的使用。比如这样的查询:
SELECT *
FROM customer
WHERE c_acctbal + 100 = 10000.0
通过EXPLAIN
命令查看查询计划:
-> Filter: ((customer.C_ACCTBAL + 100) = 10000.0) (cost=102301.50 rows=900955)
-> Table scan on customer (cost=102301.50 rows=900955)
显而易见,数据库优化器选择了全表扫描而非预期的索引查找,导致查询成本激增。
我们尝试将计算从左侧移至右侧,改写查询条件:
SELECT *
FROM customer
WHERE c_acctbal = 10000.0 - 100;
再次使用EXPLAIN
:
-> Index lookup on customer using c_acctbal_idx (C_ACCTBAL=(10000.0 - 100)) (cost=5.50 rows=5)
这次,优化器明智地选择了c_acctbal_idx
索引,查询成本降至原来的1%以下。
诊断结论: 实验表明,在索引列上进行计算会导致索引失效。这不仅适用于算术运算,也适用于函数调用和其他所有索引列上的计算。
面对这一问题,我们有两种解决策略:
PawSQL通过自动检测问题条件,并将其自动重写为等价形式,实现了上述第一个解决方案。
PawSQL的自动重写优化支持以下算术运算符及其组合在条件上的运算:
PawSQL智能重写多种MySQL 8.0内置函数,包括但不限于:
ADDDATE()
, DATE_ADD()
, DATE_SUB()
, SUBDATE()
, DATEDIFF()
等ADDTIME()
, SUBTIME()
, TIMEDIFF()
TIMESTAMPADD()
, TIMESTAMPDIFF()
SEC_TO_TIME()
, TIME_TO_SEC()
, FROM_DAYS()
, TO_DAYS()
INET_ATON()
, INET_NTOA()
, INET6_ATON()
, INET6_NTOA()
LEFT()
, STRCMP()
DATE_FORMAT()
, STR_TO_DATE()
YEAR()
, EXTRACT()
, CAST()
, IFNULL()
, ISNULL()
其他数据库的函数支持将在后续添加.
PawSQL重写前后的SQL对比
PawSQL重写前后的执行计划对比
总结
通过应用PawSQL的
重写优化,SQL查询不仅能够:
这大大提升了查询的性能和效率。🚀