1. 索引失效的典型场景分析
慢查询是数据库性能优化的头号敌人，而索引失效则是导致慢查询的罪魁祸首。根据多年DBA经验，90%的生产环境性能问题都源于索引使用不当。下面我将结合实战案例，剖析最常见的9种索引失效场景。
1.1 隐式类型转换陷阱
当查询条件的数据类型与索引列定义不一致时，MySQL会进行隐式类型转换，导致索引失效。例如：
-- user_id字段为varchar类型但建立了索引 SELECT * FROM users WHERE user_id = 10086; -- 失效 SELECT * FROM users WHERE user_id = '10086'; -- 有效注意：所有与索引列比较的值，必须严格匹配字段定义的数据类型。日期字段尤其需要注意格式一致性。
1.2 最左前缀原则违反
联合索引遵循最左匹配原则，以下情况会导致索引失效：
-- 联合索引为 (status, create_time) SELECT * FROM orders WHERE create_time > '2023-01-01'; -- 失效 SELECT * FROM orders WHERE status = 1 AND create_time > '2023-01-01'; -- 有效实测案例：某电商平台订单查询性能从2秒提升到50ms，仅通过调整查询条件顺序。
1.3 索引列参与运算
在索引列上使用函数或运算会导致索引失效：
-- create_time字段有索引 SELECT * FROM logs WHERE YEAR(create_time) = 2023; -- 失效 SELECT * FROM logs WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'; -- 有效替代方案：使用范围查询或冗余存储计算结果的派生列。
2. 优化器行为导致的索引失效
2.1 统计信息不准确
MySQL优化器依赖统计信息选择执行计划。当统计信息过时时：
ANALYZE TABLE orders; -- 更新统计信息经验：大表数据变化超过10%时应手动更新统计信息，特别是After凌晨批量作业。
2.2 索引选择性不足
当索引列不同值较少时（如性别、状态码），优化器可能选择全表扫描：
-- 订单状态字段只有0-5六个值 SELECT * FROM orders WHERE status = 1; -- 可能失效解决方案：考虑使用复合索引或强制索引：
SELECT * FROM orders FORCE INDEX(idx_status) WHERE status = 1;2.3 OR条件处理
使用OR连接不同字段的条件时：
-- name和phone字段分别有索引 SELECT * FROM users WHERE name = '张三' OR phone = '13800138000'; -- 失效优化方案：改用UNION ALL：
SELECT * FROM users WHERE name = '张三' UNION ALL SELECT * FROM users WHERE phone = '13800138000';3. 索引设计缺陷
3.1 过长的索引列
索引列长度过长会导致：
- 索引存储空间暴增
- 查询效率下降
-- 优化前 ALTER TABLE articles ADD INDEX idx_content(content); -- content是TEXT类型 -- 优化后 ALTER TABLE articles ADD INDEX idx_content_prefix(content(100)); -- 只索引前100字符3.2 不当的索引顺序
联合索引列顺序严重影响查询效率：
-- 场景：90%查询按时间范围筛选，10%按状态 -- 错误设计 ALTER TABLE orders ADD INDEX idx_status_time(status, create_time); -- 正确设计 ALTER TABLE orders ADD INDEX idx_time_status(create_time, status);3.3 冗余索引
重复索引会降低写入性能：
-- 冗余索引示例 ALTER TABLE users ADD INDEX idx_email(email); ALTER TABLE users ADD INDEX idx_email_phone(email, phone); -- 包含前一个索引检查工具：
SELECT * FROM sys.schema_redundant_indexes;4. 高级排查技巧
4.1 执行计划分析
使用EXPLAIN查看索引使用情况：
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 1 AND amount > 100;重点关注：
key：实际使用的索引type：访问类型（range优于index）rows：预估扫描行数
4.2 性能监控
开启慢查询日志：
-- my.cnf配置 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1分析工具：
mysqldumpslow -s t /var/log/mysql/mysql-slow.log4.3 索引优化实战案例
某金融系统交易表优化前后对比：
| 指标 | 优化前 | 优化后 |
|---|---|---|
| 查询耗时 | 1200ms | 35ms |
| CPU占用 | 80% | 15% |
| 索引大小 | 12GB | 4.8GB |
优化措施：
- 重建联合索引调整列顺序
- 增加覆盖索引
- 移除3个冗余索引
5. 避坑指南
5.1 索引创建原则
- 优先为WHERE条件列创建索引
- 联合索引按区分度降序排列
- 避免在更新频繁的列上建索引
- 单表索引数建议不超过5个
5.2 日常维护建议
- 每周检查
information_schema.statistics - 监控
Handler_read%状态变量 - 定期使用
pt-index-usage分析索引使用率 - 大表ALTER操作使用Online DDL工具
5.3 紧急处理方案
当出现索引突然失效时：
- 检查表统计信息
- 验证SQL执行计划
- 考虑使用FORCE INDEX临时解决
- 在低峰期重建索引
-- 索引重建命令 ALTER TABLE orders ENGINE=InnoDB; ANALYZE TABLE orders;
