索引是MySQL提升查询性能的关键机制，通过有序存储数据指针，大幅减少数据检索时的I/O操作。然而在实际应用中，开发者常遇到明明创建了索引但查询执行计划却未使用索引的情况，即索引失效。本文将详细分析MySQL索引失效的原理、常见场景及排查方法，帮助开发者优化查询性能。

索引失效原理

MySQL索引基于B+树等数据结构实现，通过有序排列的数据键值指向表中的数据行。索引失效的根本原因是查询优化器判断使用索引比全表扫描成本更高时，会选择放弃索引。这通常发生在以下情况：索引选择性低（索引列值重复率高）、查询条件导致索引无法有效利用、数据量小全表扫描反而更快，或统计信息不准确导致优化器做出错误判断。

常见索引失效场景及排查方法

函数或表达式操作

对索引列使用函数或表达式会导致索引失效：

-- 失效示例：对索引列使用函数
SELECT * FROM users WHERE UPPER(name) = 'JOHN';

-- 有效写法：避免对索引列使用函数
SELECT * FROM users WHERE name = 'john';

排查方法：检查EXPLAIN执行计划中的type列是否为ALL（全表扫描），Extra列是否显示"Using filesort"或"Using temporary"。

隐式类型转换

当查询条件的值类型与索引列类型不匹配时，会导致隐式类型转换和索引失效：

-- 假设id是整型索引
-- 失效示例：使用字符串类型
SELECT * FROM users WHERE id = '123';

-- 有效写法：确保类型一致
SELECT * FROM users WHERE id = 123;

LIKE以通配符开头

使用LIKE查询时，如果以通配符（%或_）开头，索引将无法使用：

-- 失效示例：LIKE以通配符开头
SELECT * FROM users WHERE name LIKE '%john';

-- 有效写法：避免前导通配符
SELECT * FROM users WHERE name LIKE 'john%';

OR连接条件

当查询条件中使用OR连接多个条件，且这些条件涉及不同的索引列时，索引可能失效：

-- 假设有name和age两个索引
-- 失效示例：OR连接不同索引列
SELECT * FROM users WHERE name = 'john' OR age = 30;

-- 有效写法：使用UNION ALL替代OR
SELECT * FROM users WHERE name = 'john'
UNION ALL
SELECT * FROM users WHERE age = 30;

联合索引未遵循最左前缀原则

联合索引（复合索引）必须遵循最左前缀原则，即查询条件必须包含索引的最左列：

-- 假设有联合索引(name, age)
-- 失效示例：跳过最左列
SELECT * FROM users WHERE age = 30;

-- 有效写法：包含最左列
SELECT * FROM users WHERE name = 'john' AND age = 30;

查询条件包含IS NULL或IS NOT NULL

对索引列使用IS NULL或IS NOT NULL可能导致索引失效，特别是当索引列允许NULL值时：

-- 失效示例：对允许NULL的索引列使用IS NULL
SELECT * FROM users WHERE age IS NULL;

使用!=或<>操作符

使用不等于操作符（!=或<>）通常会导致索引失效：

-- 失效示例：使用不等于操作符
SELECT * FROM users WHERE age != 30;

-- 有效写法：使用其他条件替代
SELECT * FROM users WHERE age < 30 OR age > 30;

使用NOT IN或NOT EXISTS

使用NOT IN或NOT EXISTS通常会导致索引失效：

-- 失效示例：使用NOT IN
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- 有效写法：使用LEFT JOIN替代
SELECT u.* FROM users u
LEFT JOIN (SELECT id FROM temp_users) t ON u.id = t.id
WHERE t.id IS NULL;

排查工具与方法

  1. EXPLAIN命令：分析查询执行计划，判断是否使用了索引。
   EXPLAIN SELECT * FROM users WHERE name = 'john';
  1. SHOW INDEX命令：查看表的索引信息。
   SHOW INDEX FROM users;
  1. 慢查询日志：启用慢查询日志，分析未使用索引的查询。
   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;
  1. Performance Schema：使用MySQL内置的性能监控工具分析查询性能。

注意事项与优化建议

  1. 定期更新统计信息，确保优化器能够做出正确决策：
   ANALYZE TABLE users;
  1. 避免过度索引，索引过多会降低写操作性能。
  1. 对于大型表，考虑使用覆盖索引，即查询字段全部包含在索引中。
  1. 使用FORCE INDEX强制使用特定索引（谨慎使用）：
   SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'john';
  1. 对于复杂的查询，考虑拆分为多个简单查询。

总结

MySQL索引失效是数据库性能优化的常见问题，了解其原理和常见场景对于提升查询效率至关重要。通过合理设计索引、遵循最佳实践以及使用适当的排查工具，可以有效避免和解决索引失效问题，确保数据库查询性能达到最优。在实际开发中，应始终关注查询执行计划，及时发现并解决索引使用不当的问题。

主楼