mysql索引失效场景及解决方案
原创
MySQL 索引失效是数据库性能优化中常见的问题，理解索引失效的场景对于提升查询效率至关重要。以下是 MySQL 索引失效的常见场景及解决方案：
一、查询条件不满足最左匹配原则
场景：复合索引(a, b, c)在以下查询中失效：
-- 未从索引首列开始
SELECT * FROM table WHERE b = 1 AND c = 2;
-- 跳过中间列
SELECT * FROM table WHERE a = 1 AND c = 2;解决方案：
- 确保查询条件从复合索引的最左列开始
- 调整索引顺序以匹配常用查询模式
二、在索引列上使用函数或表达式
-- 使用函数
SELECT * FROM table WHERE YEAR(create_time) = 2023;
-- 使用表达式
SELECT * FROM table WHERE price * 1.1 > 100;解决方案：
将计算移到查询条件右侧：sql
SELECT * FROM table WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'; SELECT * FROM table WHERE price > 100 / 1.1;
三、隐式类型转换导致索引失效
-- 字段类型为VARCHAR，但查询时未加引号
SELECT * FROM table WHERE phone = 13800138000;
-- 字段类型为INT，但查询时加了引号
SELECT * FROM table WHERE id = '123';解决方案：
- 确保查询条件中的数据类型与字段类型一致
- 使用
EXPLAIN检查是否存在类型转换
四、使用 LIKE 通配符开头
SELECT * FROM table WHERE name LIKE '%keyword';解决方案：
避免通配符开头，改用：sql
SELECT * FROM table WHERE name LIKE 'keyword%';- 若必须支持双向搜索，可考虑：
- 全文索引（FULLTEXT）
- 应用层预处理（如建立反向索引）
五、OR 条件导致索引失效
SELECT * FROM table WHERE a = 1 OR b = 2;解决方案：
若a和b均有独立索引，可拆分为UNION：sql
SELECT * FROM table WHERE a = 1 UNION SELECT * FROM table WHERE b = 2;- 为
(a, b)创建复合索引
六、索引字段参与 NULL 值比较
SELECT * FROM table WHERE column IS NULL;
SELECT * FROM table WHERE column IS NOT NULL;解决方案：
- 若查询频繁，可为字段添加
NOT NULL约束
对IS NULL条件创建索引：sql
CREATE INDEX idx_column ON table (column);七、范围查询导致后续索引列失效
场景：复合索引(a, b, c)在以下查询中b和c索引失效：
SELECT * FROM table WHERE a > 1 AND b = 2 AND c = 3;解决方案：
将等值条件前置：sql
SELECT * FROM table WHERE b = 2 AND a > 1 AND c = 3;- 为
(b, a, c)创建复合索引
八、数据分布导致优化器选择全表扫描
场景：
- 索引选择性低（如性别字段）
- 统计信息过时
解决方案：
- 执行
ANALYZE TABLE更新统计信息
使用FORCE INDEX强制使用索引：sql
SELECT * FROM table FORCE INDEX (idx_column) WHERE column = 'value';九、覆盖索引未被正确使用
-- 复合索引 (a, b)
SELECT a, b FROM table WHERE a = 1; -- 覆盖索引有效
SELECT a, b, c FROM table WHERE a = 1; -- 覆盖索引失效解决方案：
- 确保查询只包含索引列
- 扩展索引包含
c：(a, b, c)
十、索引被禁用或损坏
场景：
- 索引被手动禁用
- 索引文件损坏
解决方案：
启用索引：sql
ALTER TABLE table ENABLE KEYS;重建索引：sql
ALTER TABLE table DROP INDEX idx_column; ALTER TABLE table ADD INDEX idx_column (column);十一、存储引擎限制
场景：
- MyISAM 不支持外键约束
- InnoDB 在
WHERE子句中对主键和唯一索引使用范围查询时会失效
解决方案：
- 了解存储引擎特性
- 对 InnoDB 主键范围查询，考虑改用等值查询或分页优化
十二、索引优化建议
使用 EXPLAIN 分析查询：
EXPLAIN SELECT * FROM table WHERE column = 'value';- 避免冗余索引：
- 索引
(a, b)包含了索引(a)
- 索引
定期维护索引：
ANALYZE TABLE table; -- 更新统计信息 OPTIMIZE TABLE table; -- 重建表和索引监控索引使用情况：
SHOW STATUS LIKE 'Handler_read%';通过避免上述场景，合理设计索引，并结合EXPLAIN工具进行分析，可显著提升 MySQL 查询性能。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号