赞
踩
适用于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%以下。
诊断结论: 实验表明,在索引列上进行计算会导致索引失效。这不仅适用于算术运算,也适用于函数调用和其他所有索引列上的计算。
面对这一问题,我们有两种解决策略:
重写查询,将计算从索引列移至其他位置,以便查询能够利用现有的索引。
创建函数索引以匹配特定条件。例如:
CREATE INDEX phone_func_idx ON customer(LEFT(c_phone, 3))
注意:函数索引仅适用于与索引定义完全一致的条件,譬如LEFT(c_phone, 4) = '1390'将不会采用这个函数索引。
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()
IP地址转换函数: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查询不仅能够:
利用索引减少数据访问成本,还能
通过将计算从列上移到常量字面值上,避免对每一行进行计算。
这大大提升了查询的性能和效率。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。