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 ALLSELECT * 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 FORMATJSON SELECT * FROM orders WHERE status 1 AND amount 100;重点关注key实际使用的索引type访问类型range优于indexrows预估扫描行数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 索引优化实战案例某金融系统交易表优化前后对比指标优化前优化后查询耗时1200ms35msCPU占用80%15%索引大小12GB4.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 ENGINEInnoDB; ANALYZE TABLE orders;