MySQL 索引失效常见的 9 种情况

在 MySQL 中，索引能有效加速查询，提高数据检索的效率。然而，有时即使表中的某列创建了索引，查询也可能不会使用索引，而是选择全表扫描。这种现象被称为索引失效。本节将介绍导致索引失效的常见原因及解决方法，以便在实际应用中尽量避免这些情况，确保索引正常工作。

1. 什么是索引失效？

索引失效是指在查询时，MySQL 优化器没有选择使用索引，而是直接进行了全表扫描。索引失效通常会导致查询速度变慢，影响系统性能。

2. 导致索引失效的常见原因

2.1 查询条件中使用函数或计算

当查询条件使用了函数或表达式时，索引无法直接匹配，导致失效。例如：

SELECT * FROM t_employee WHERE LEFT(name, 3) = 'Tom';

解决方法：避免在索引列上使用函数或计算，将条件改为原始字段查询，例如：

SELECT * FROM t_employee WHERE name LIKE 'Tom%';

2.2 使用不等于（<> 或 !=）操作符

使用 <> 或 != 查询时，索引会失效，因为数据库无法通过范围查找快速定位数据。

SELECT * FROM t_employee WHERE salary != 5000;

解决方法：尽量避免使用不等于运算符，或者使用其他逻辑条件替代，若非必要时可不使用索引。

2.3 使用 IS NULL 或 IS NOT NULL

在某些情况下，索引列的 IS NULL 或 IS NOT NULL 条件也会导致索引失效，因为 MySQL 需要检查每一行的 NULL 状态。

SELECT * FROM t_employee WHERE position IS NOT NULL;

解决方法：使用默认值替代 NULL，或尽量避免在查询中检查 NULL。

2.4 使用 OR 条件

OR 条件通常会导致索引失效，除非 OR 的每个条件列上都有索引。

SELECT * FROM t_employee WHERE position = 'Manager' OR salary > 7000;

解决方法：可以将 OR 改为 UNION 或者使用多个查询条件，例如：

SELECT * FROM t_employee WHERE position = 'Manager'
UNION
SELECT * FROM t_employee WHERE salary > 7000;

2.5 隐式类型转换

如果查询条件的类型与索引列的类型不一致，MySQL 会进行隐式转换，从而导致索引失效。例如：

SELECT * FROM t_employee WHERE employee_id = '123'; -- employee_id 是 INT 类型

解决方法：确保查询条件的类型与索引列的数据类型一致，避免隐式类型转换。

2.6 使用 LIKE 时通配符在开头

LIKE 查询使用通配符 ％ 或 _ 开头时，MySQL 会进行全表扫描，导致索引失效。例如：

SELECT * FROM t_employee WHERE name LIKE '%Smith';

解决方法：尽量将通配符放在结尾，例如：

SELECT * FROM t_employee WHERE name LIKE 'Smith%';

2.7 使用范围条件后再用索引列排序

如果在查询中先使用了范围条件（如 BETWEEN、<、>），再对索引列排序，可能会导致索引无法被完全利用。

SELECT * FROM t_employee WHERE salary > 5000 ORDER BY position;

解决方法：根据查询场景，适当调整排序或索引列顺序，或将排序条件改为非范围条件。

2.8 使用联合索引时未遵循最左前缀原则

复合索引中的列是有顺序的，如果查询中没有包含最左侧的列，索引将不会生效。这称为最左前缀原则。

-- 假设有联合索引 (name, position)
SELECT * FROM t_employee WHERE position = 'Manager';

解决方法：确保查询中包含复合索引的最左列，例如：

SELECT * FROM t_employee WHERE name = 'John' AND position = 'Manager';

2.9 数据选择性过低

当索引列的数据选择性过低（如只有几个不同的值），MySQL 优化器可能会认为全表扫描比索引查找更有效率，从而选择跳过索引。

SELECT * FROM t_employee WHERE gender = 'M'; -- 假设 gender 列只有 "M" 和 "F" 两个值

解决方法：对于选择性低的列，可以考虑不创建索引，或改用其他高选择性的列来过滤数据。

3. 小结

索引是 MySQL 查询优化的关键，但不合理的查询方式或条件可能会导致索引失效。掌握导致索引失效的常见原因并进行优化，可以提升数据库性能。在实际应用中，应注意避免在查询条件中使用函数、不等于运算符、OR 条件等情况，遵循复合索引的最左前缀原则，并确保数据类型一致，以保证索引的有效性。