避免索引失效的WHERE子句写法指南

在数据库性能优化中，索引失效往往是导致查询缓慢的罪魁祸首。很多开发者明明创建了索引，却发现SQL执行计划依然走全表扫描，问题往往就出在WHERE子句的写法上。本文将通过几个典型场景，剖析导致索引失效的常见写法，并给出优化方案，帮助你写出真正高效利用索引的查询。

场景一：对索引列使用函数或运算

假设你有一个订单表，创建了索引idx_order_date，查询某天订单时习惯写成：

SELECT * FROM orders WHERE DATE(order_date) = '2024-01-01';

这种写法对索引列应用了DATE()函数，导致数据库无法直接使用索引，而必须对每一行的order_date都计算一次函数，从而退化为全表扫描。这是最常见的索引失效场景之一。

优化写法：将函数操作移到等号右侧，或使用范围查询：

SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2024-01-02';

同理，对索引列进行算术运算（如price + 1 > 100）也会导致索引失效，应改为price > 99的形式。

场景二：隐式类型转换

当索引列的类型与查询条件中的类型不一致时，数据库会进行隐式转换，导致索引失效。例如，user_id是VARCHAR类型，但查询时传入数字：

SELECT * FROM users WHERE user_id = 123;

数据库会将user_id隐式转换为数字，相当于对索引列应用了CAST()函数，从而无法使用索引。

优化写法：确保查询参数类型与列类型一致，传入字符串：

SELECT * FROM users WHERE user_id = '123';

在设计表结构时，也应避免用数字类型存储手机号等本应作为字符串的字段，从根源消除转换问题。

场景三：LIKE模糊匹配以通配符开头

搜索功能经常用到LIKE，但LIKE '%关键词'或LIKE '%关键词%'会导致索引失效，因为无法利用B+树的顺序查找特性。只有LIKE '关键词%'（前缀匹配）才能用到索引。

优化方案：如果业务确实需要后缀匹配，考虑使用全文索引（如MySQL的FULLTEXT）或反向索引（如Elasticsearch）。若数据量不大，可以接受全表扫描，但需评估性能。

场景四：OR条件连接非索引列

当查询条件使用OR连接时，如果其中一个列没有索引，整个查询可能无法使用索引。例如：

SELECT * FROM orders WHERE status = 'completed' OR user_id = 123;

假设status没有索引，即使user_id有索引，优化器也可能选择全表扫描。

优化写法：将OR改写为UNION ALL，分别利用索引：

SELECT * FROM orders WHERE status = 'completed'
UNION ALL
SELECT * FROM orders WHERE user_id = 123;

或者为status也创建索引，让优化器有机会使用索引合并（Index Merge）。

场景五：范围查询与复合索引顺序不当

复合索引遵循最左前缀原则。假设创建了复合索引(a, b, c)，查询条件为WHERE b = 1 AND a > 10，由于a是范围条件，后面的b无法用于索引排序，但索引本身仍可被用于定位a > 10的范围。而如果写成WHERE b = 1（跳过a），则完全无法使用该复合索引。

优化建议：设计复合索引时，将等值查询的列放在前面，范围查询的列放在后面。同时，避免在索引列上进行范围查询后再对其他索引列进行排序或分组，否则可能增加额外的文件排序。

场景六：NULL值判断

在SQL中，NULL不等于任何值，包括它自己。因此，WHERE column IS NULL或IS NOT NULL在某些数据库（如MySQL）中可能无法使用索引。不过，MySQL对IS NULL有特殊优化，如果索引列允许NULL且统计信息显示有大量NULL，优化器可能选择全表扫描。

优化方案：设计表时尽量使用NOT NULL约束，并为默认值（如空字符串或0）创建索引。如果业务必须允许NULL，可以考虑使用COALESCE或IFNULL函数配合表达式索引（MySQL 8.0+支持函数索引）。

场景七：使用不等于（!= 或 <>）

WHERE column != 'value'通常无法使用索引，因为索引只能用于等值或范围查找，而“不等于”需要扫描所有不等于该值的记录，优化器一般会选择全表扫描。同理，NOT IN、NOT LIKE也容易导致索引失效。

优化建议：尽量将“不等于”改写为范围条件，例如WHERE column < 'value' OR column > 'value'，但注意这又引入了OR，需谨慎。更实际的做法是，如果业务经常需要这种查询，考虑使用其他存储方案或接受全表扫描。

如何确认索引是否失效？

在MySQL中，可以使用EXPLAIN查看执行计划，关注type字段：若为ALL表示全表扫描，index表示全索引扫描，range表示范围扫描（通常用到索引），ref或eq_ref表示较好的索引使用方式。此外，key字段为NULL表示未使用索引。

例如，执行EXPLAIN SELECT * FROM orders WHERE DATE(order_date) = '2024-01-01';，你会看到type=ALL，说明索引失效。

总结与最佳实践

避免索引失效的核心原则是：保持索引列的“纯净”，即不在索引列上做任何运算、函数、类型转换，并尽量使用等值或范围查询。以下是一些实用建议：

查询条件中，将函数或运算移到列的另一侧。
确保参数类型与列类型一致，必要时显式转换。
LIKE查询尽量使用前缀匹配，避免前导通配符。
OR条件确保所有列都有索引，或改写为UNION。
合理设计复合索引，将等值列放前面。
避免对索引列使用IS NULL或<>，必要时使用函数索引。
定期使用EXPLAIN审查执行计划，及时发现索引失效问题。

这些原则不仅适用于MySQL，也适用于大多数关系型数据库。更多关于索引原理和查询优化的内容，可以参考MySQL InnoDB存储引擎索引原理与B+树慢查询优化实战和数据库索引失效的常见原因与排查方法。

参考资料

延伸阅读