MySQL查询不使用索引汇总
在数据库优化中，索引是提高查询性能的关键。然而，并非所有查询都能有效利用索引。本文将系统梳理导致MySQL查询无法使用索引的常见场景，帮助开发者识别和避免这些性能陷阱。
一、索引失效的常见原因
1. 对索引列进行函数或表达式操作
当查询条件中对索引列应用函数或数学表达式时，MySQL通常无法使用该列的索引。
-- 假设在create_time字段上有索引 SELECT * FROM orders WHERE YEAR(create_time) = 2023; -- 无法使用索引 -- 应改写为范围查询 SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'; -- 可使用索引
2. 隐式类型转换
当查询条件中的数据类型与索引列定义的数据类型不匹配时，MySQL会进行隐式转换，导致索引失效。
-- phone字段定义为VARCHAR，但查询时使用数字 SELECT * FROM users WHERE phone = 13800138000; -- 无法使用索引 -- 应使用字符串字面量 SELECT * FROM users WHERE phone = '13800138000'; -- 可使用索引
3. 使用不等于操作符
使用 != 或 <> 操作符时，MySQL通常无法使用索引，因为这类查询通常需要扫描大部分数据行。
SELECT * FROM products WHERE price != 100; -- 可能无法使用索引
4. 使用LIKE以通配符开头
当LIKE模式以通配符%开头时，索引无法被用于快速定位数据。
SELECT * FROM articles WHERE title LIKE '%MySQL%'; -- 无法使用索引 -- 以固定前缀开头可以使用索引 SELECT * FROM articles WHERE title LIKE 'MySQL%'; -- 可使用索引
5. OR条件连接非索引列
当OR条件连接的多个列中至少有一个没有索引时，MySQL通常会放弃使用索引而进行全表扫描。
-- 假设name有索引，age没有索引 SELECT * FROM employees WHERE name = 'John' OR age = 30; -- 可能无法使用索引
6. 复合索引不满足最左前缀原则
对于复合索引 (col1, col2, col3)，查询必须从最左边的列开始连续使用索引列，否则无法充分利用索引。
-- 创建复合索引 (last_name, first_name, age) CREATE INDEX idx_name_age ON employees(last_name, first_name, age); -- 以下查询可以使用索引 SELECT * FROM employees WHERE last_name = 'Smith'; -- 使用第一列 SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John'; -- 使用前两列 SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John' AND age = 30; -- 使用全部三列 -- 以下查询无法有效使用索引 SELECT * FROM employees WHERE first_name = 'John'; -- 跳过第一列 SELECT * FROM employees WHERE last_name = 'Smith' AND age = 30; -- 跳过第二列
7. 数据分布导致优化器选择全表扫描
当MySQL查询优化器认为全表扫描比使用索引更高效时，会选择不使用索引。这通常发生在以下情况：
- 查询结果集占表数据的比例过高（通常超过20%-30%）
- 索引的选择性过低（即索引列的不同值太少）
- 统计信息不准确或过时
-- 如果表中90%的记录都是active状态，这个查询可能不会使用索引 SELECT * FROM users WHERE status = 'active';
8. IS NULL或IS NOT NULL条件
在某些存储引擎和索引类型下，IS NULL或IS NOT NULL条件可能无法使用索引。
SELECT * FROM customers WHERE email IS NULL; -- 可能无法使用索引
9. ORDER BY与索引顺序不一致
当ORDER BY子句的排序顺序与索引的列顺序不一致时，MySQL可能无法利用索引进行排序。
-- 假设有索引 (category_id, price) SELECT * FROM products WHERE category_id = 1 ORDER BY price DESC; -- 可以使用索引排序 SELECT * FROM products WHERE category_id = 1 ORDER BY price ASC; -- 可以使用索引排序 SELECT * FROM products WHERE category_id = 1 ORDER BY name; -- 无法使用索引排序
10. 使用NOT IN或NOT EXISTS
在某些情况下，NOT IN和NOT EXISTS可能导致索引失效。
SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers WHERE status = 'inactive'); -- 可能无法使用索引
二、如何诊断索引使用情况
1. 使用EXPLAIN分析查询计划
EXPLAIN命令是诊断索引使用情况的最强大工具，它可以显示MySQL将如何执行查询。
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';
关注EXPLAIN输出中的以下关键字段：
- type：访问类型，从好到坏依次为const、eq_ref、ref、range、index、ALL
- key：实际使用的索引，NULL表示未使用索引
- rows：估计需要检查的行数
- Extra：额外信息，如Using filesort、Using temporary等
2. 查看慢查询日志
启用慢查询日志可以帮助识别执行时间长的查询，这些查询往往存在索引使用问题。
-- 查看慢查询日志设置 SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; -- 启用慢查询日志 SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 2; -- 记录执行时间超过2秒的查询
3. 使用Performance Schema
MySQL的Performance Schema提供了更详细的性能监控信息，可以用来分析索引使用情况。
三、优化建议
1. 合理设计索引
- 根据查询模式创建合适的单列索引或复合索引
- 遵循最左前缀原则设计复合索引
- 考虑索引的选择性和数据分布情况
2. 重写查询语句
- 避免在索引列上使用函数和表达式
- 确保查询条件的数据类型与索引列匹配
- 尽量使用覆盖索引，减少回表操作
3. 定期维护索引
- 定期更新表的统计信息：ANALYZE TABLE table_name;
- 删除不再使用的冗余索引
- 监控索引的使用情况，识别低效索引
4. 调整MySQL配置
- 适当增加缓冲池大小（innodb_buffer_pool_size）
- 调整查询缓存相关参数
- 根据硬件资源合理配置并发连接数
四、总结
理解MySQL索引失效的场景对于数据库性能优化至关重要。通过避免上述常见的索引使用误区，合理使用EXPLAIN等诊断工具，以及遵循索引设计和查询优化的最佳实践，可以显著提升数据库的查询性能。记住，索引并非越多越好，过度索引会增加写操作的开销并占用更多存储空间，需要在读写性能和存储成本之间找到平衡。