SQL性能优化秘籍：如何避免计算导致索引失效

适用于MySQL、PostgreSQL、Oracle等各种数据库的优化技巧

问题剖析

设想我们为customer表的c_acctbal列创建了一个B树索引c_acctbal_idx，以加速相关查询。然而，一个看似无害的计算可能会阻碍索引的使用。比如这样的查询：

代码语言：javascript复制

SELECT * 
FROM customer 
WHERE c_acctbal   100 = 10000.0

通过EXPLAIN命令查看查询计划：

代码语言：javascript复制

-> Filter: ((customer.C_ACCTBAL   100) = 10000.0)  (cost=102301.50 rows=900955)
   -> Table scan on customer  (cost=102301.50 rows=900955)

显而易见，数据库优化器选择了全表扫描而非预期的索引查找，导致查询成本激增。

深入诊断

我们尝试将计算从左侧移至右侧，改写查询条件：

代码语言：javascript复制

SELECT * 
FROM customer 
WHERE c_acctbal = 10000.0 - 100;

再次使用EXPLAIN：

代码语言：javascript复制

-> Index lookup on customer using c_acctbal_idx (C_ACCTBAL=(10000.0 - 100))  (cost=5.50 rows=5)

这次，优化器明智地选择了c_acctbal_idx索引，查询成本降至原来的1%以下。

诊断结论： 实验表明，在索引列上进行计算会导致索引失效。这不仅适用于算术运算，也适用于函数调用和其他所有索引列上的计算。

解决方案

面对这一问题，我们有两种解决策略：

1. 重写查询，将计算从索引列移至其他位置，以便查询能够利用现有的索引。
2. 创建函数索引以匹配特定条件。例如： CREATE INDEX phone_func_idx ON customer(LEFT(c_phone, 3)) 注意：函数索引仅适用于与索引定义完全一致的条件，譬如LEFT(c_phone, 4) = '1390'将不会采用这个函数索引。

PawSQL的智能优化

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查询不仅能够：

• 利用索引减少数据访问成本，还能
• 通过将计算从列上移到常量字面值上，避免对每一行进行计算。

这大大提升了查询的性能和效率。