1. 那些年我们踩过的MySQL索引坑凌晨三点手机铃声突然响起。运维同事急促的声音从听筒传来线上订单查询接口超时整个交易链路卡死了我强忍睡意打开电脑发现一条本该走索引的SQL竟然在进行全表扫描。这个百万级数据表上的全表操作直接拖垮了数据库——而这仅仅是因为查询条件中一个不起眼的函数调用。这样的场景对DBA和开发人员来说都不陌生。MySQL索引就像数据库的目录但错误的用法会让这个目录完全失效。根据我的实战经验索引失效问题占数据库性能问题的60%以上而其中90%的情况都集中在几个典型场景。2. 七大索引失效场景深度解析2.1 隐式类型转换安静的索引杀手当查询条件的数据类型与字段定义不匹配时MySQL会进行隐式类型转换。例如-- user_id是varchar类型但存储的是数字 SELECT * FROM users WHERE user_id 10086;这里MySQL会将user_id字段值转换为数字再比较导致索引失效。解决方案保持字段与条件类型一致使用显式CAST()函数但要注意性能开销最根本的是设计表时就统一类型提示可通过EXPLAIN查看执行计划出现typeALL就说明索引失效了2.2 函数操作索引的克星在索引字段上使用函数会使索引失效-- 不会使用create_time的索引 SELECT * FROM orders WHERE DATE(create_time) 2023-07-15;替代方案使用范围查询SELECT * FROM orders WHERE create_time 2023-07-15 00:00:00 AND create_time 2023-07-16 00:00:00;使用计算列索引ALTER TABLE orders ADD COLUMN create_date DATE AS (DATE(create_time)) STORED; CREATE INDEX idx_create_date ON orders(create_date);2.3 前导通配符LIKE的陷阱-- 不会使用name的索引 SELECT * FROM products WHERE name LIKE %手机%;解决方案尽量避免前导通配符使用全文索引(FULLTEXT)替代考虑专门的搜索引擎如Elasticsearch2.4 OR条件部分失效的隐患-- 如果age没有索引整个查询都不会使用索引 SELECT * FROM users WHERE user_id 10086 OR age 18;优化方案使用UNION替代ORSELECT * FROM users WHERE user_id 10086 UNION SELECT * FROM users WHERE age 18;确保OR条件涉及的字段都有索引2.5 不等于(!/)全表扫描的元凶-- 不会使用status的索引 SELECT * FROM orders WHERE status ! completed;替代方案使用范围查询SELECT * FROM orders WHERE status completed OR status completed;考虑业务设计是否需要频繁查询不等于条件2.6 索引列参与运算隐藏的性能黑洞-- 不会使用price的索引 SELECT * FROM products WHERE price 100 500;正确做法将运算移到条件右侧SELECT * FROM products WHERE price 400;使用计算列索引2.7 复合索引顺序最左前缀原则对于复合索引INDEX(a,b,c)以下情况会失效-- 不会使用完整索引 SELECT * FROM table WHERE b 2 AND c 3; -- 只使用a列的索引 SELECT * FROM table WHERE a 1 AND c 3;解决方案确保查询条件包含最左列调整索引顺序匹配查询模式使用索引合并(index merge)3. 实战中的排查与修复流程3.1 如何发现索引失效问题慢查询监控-- 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒记录使用EXPLAIN分析EXPLAIN SELECT * FROM users WHERE user_id 10086;重点关注type列最好到range级别key列是否使用了预期索引rows列估算扫描行数3.2 紧急修复方案当线上出现索引失效导致性能问题时临时解决方案-- 强制使用索引 SELECT * FROM users FORCE INDEX(primary) WHERE user_id 10086;长期解决方案修改SQL写法调整索引设计考虑查询重写3.3 索引设计最佳实践选择性高的列优先-- 计算选择性 SELECT COUNT(DISTINCT column)/COUNT(*) FROM table;避免过度索引每个索引都会增加写入开销一般表不超过5个索引定期维护索引ANALYZE TABLE users; -- 更新统计信息4. 进阶索引失效的底层原理4.1 B树索引结构回顾MySQL索引采用B树结构特点所有数据存储在叶子节点叶子节点形成有序链表非叶子节点只存储键值和指针4.2 为什么函数操作会失效索引存储的是原始值而原始值 - 索引B树 - 快速查找 函数(原始值) - 无法使用索引B树 - 全表扫描4.3 优化器的成本计算MySQL选择执行计划基于成本估算影响因素索引选择性数据分布系统变量(如range_optimizer_max_mem_size)可以通过optimizer_trace查看详细决策过程SET optimizer_traceenabledon; SELECT * FROM users WHERE user_id 10086; SELECT * FROM information_schema.optimizer_trace;5. 特殊场景下的索引问题5.1 NULL值处理-- 可能不会使用索引 SELECT * FROM users WHERE phone IS NULL;解决方案使用IS NULL条件时确保索引包含NULL值考虑用特殊值替代NULL5.2 字符集与排序规则不同字符集比较可能导致索引失效-- utf8与utf8mb4比较 SELECT * FROM users WHERE name 张三 COLLATE utf8mb4_general_ci;确保表字段字符集一致连接使用相同字符集5.3 分区表索引分区表的索引是局部的全局查询可能效率低下-- 需要在所有分区查找 SELECT * FROM orders PARTITION(p1,p2) WHERE user_id 10086;优化方案包含分区键在查询条件中考虑本地索引设计6. 工具链与监控体系6.1 性能分析工具pt-index-usage分析索引使用情况pt-index-usage /var/lib/mysql/mysql-slow.logsys schema内置性能视图SELECT * FROM sys.schema_unused_indexes;6.2 持续监控方案Prometheus Grafana监控关键指标索引命中率、扫描行数报警阈值全表扫描比例5%定期索引审查每月执行一次索引使用分析删除三个月未使用的索引7. 从架构层面预防索引问题7.1 开发规范SQL审核禁止在索引列上使用函数OR条件必须验证索引使用所有查询必须EXPLAIN验证代码审查清单[ ] 查询条件类型匹配[ ] 避免前导通配符[ ] 复合索引顺序7.2 测试策略性能测试使用真实数据量测试监控执行计划变化混沌工程随机移除索引验证系统容错模拟数据量激增场景7.3 应急方案索引问题应急预案快速回滚SQL查询重写技巧限流降级方案工具箱准备常用索引修改语句模板历史执行计划备份紧急联系名单经过多次深夜故障的洗礼我现在会在每次上线前用这个检查清单验证SQLEXPLAIN确认索引使用检查WHERE条件字段类型验证复合索引顺序避免索引列参与运算处理NULL值特殊情况评估OR条件影响测试不同数据量下的表现记住索引不是银弹合理的设计加上严格的验证才能避免半夜被叫起来处理性能问题。