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

PawSQL官方账号

于 2024-07-23 19:14:54 发布

阅读量832

点赞数 18

文章标签： sql 性能优化数据库 pawsql

本文链接：https://blog.csdn.net/PawSQL/article/details/140643967

版权

适用于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'将不会采用这个函数索引。