1. 面试复盘那些年我们踩过的索引失效坑上周刚结束狗东的数据库开发岗面试面试官连环追问索引失效的场景让我印象深刻。作为MySQL性能优化的核心知识点索引失效问题在实际开发中几乎每天都会遇到。今天我就把面试中讨论的10个典型场景整理出来结合8年MySQL调优经验从原理到实战帮你彻底搞懂这个高频考点。索引就像图书馆的目录系统能帮我们快速定位数据位置。但当索引失效时数据库就不得不进行全表扫描好比在图书馆里逐本书翻找性能会呈指数级下降。根据我的统计生产环境中约60%的慢查询都与索引失效有关。下面这些场景有些是新手容易忽略的陷阱有些甚至是老司机都会翻车的隐蔽情况。2. 索引失效的10种典型场景2.1 违反最左匹配原则这是联合索引最常见的失效场景。假设我们有个商品表建立了(category_id, brand_id, price)的联合索引-- 有效索引查询 SELECT * FROM products WHERE category_id 1 AND brand_id 5; -- 失效查询缺少最左字段category_id SELECT * FROM products WHERE brand_id 5;原理说明联合索引的存储结构是按照索引字段顺序构建的B树。跳过最左字段时数据库无法利用索引的有序性就像跳过了字典的字母索引直接按页码查找。实战建议设计联合索引时将区分度高的字段放在左边无法避免时要考虑单独建立索引或使用索引覆盖2.2 对索引列使用函数或运算-- 失效案例使用函数 SELECT * FROM orders WHERE DATE_FORMAT(create_time,%Y-%m) 2023-01; -- 失效案例使用运算 SELECT * FROM users WHERE age 1 20;优化方案-- 改为范围查询 SELECT * FROM orders WHERE create_time 2023-01-01 AND create_time 2023-02-01;2.3 隐式类型转换当字段类型与查询条件类型不一致时-- user_id是varchar类型但用数字查询 SELECT * FROM users WHERE user_id 10086; -- 实际执行等价于导致索引失效 SELECT * FROM users WHERE CAST(user_id AS signed int) 10086;避坑技巧使用EXPLAIN查看执行计划时注意type列出现ALL或index往往说明索引失效2.4 使用不等于(! / )查询-- 全表扫描 SELECT * FROM products WHERE status ! online;替代方案-- 改为IN查询 SELECT * FROM products WHERE status IN (draft, offline, deleted);2.5 LIKE以通配符开头-- 失效查询 SELECT * FROM articles WHERE title LIKE %优化%; -- 有效查询能使用索引 SELECT * FROM articles WHERE title LIKE 性能%;特殊场景处理必须使用%xxx%时考虑全文索引数据量大时可使用Elasticsearch等专业搜索工具2.6 OR条件使用不当-- 索引失效案例 SELECT * FROM orders WHERE user_id 1001 OR amount 1000; -- 优化方案使用UNION SELECT * FROM orders WHERE user_id 1001 UNION ALL SELECT * FROM orders WHERE amount 1000;2.7 索引列参与IS NULL判断-- 可能失效取决于数据分布 SELECT * FROM customers WHERE phone IS NULL;优化建议NULL值较少时可考虑WHERE phone IS NOT NULL反转查询重要字段建议设置NOT NULL约束并设置默认值2.8 范围查询后的条件失效-- 只有category_id和price能用索引color失效 SELECT * FROM products WHERE category_id 1 AND price 100 AND color red;索引设计技巧将等值查询字段放在联合索引左侧范围查询字段尽量放在右侧2.9 使用NOT IN条件-- 全表扫描 SELECT * FROM products WHERE category_id NOT IN (1, 2, 3);替代方案-- 使用NOT EXISTS SELECT * FROM products p WHERE NOT EXISTS ( SELECT 1 FROM categories c WHERE c.id IN (1,2,3) AND c.id p.category_id );2.10 数据量过少时优化器放弃索引当表中数据量很少如小于全表10%时优化器可能认为全表扫描比索引更快。应对策略使用FORCE INDEX强制使用索引通过ANALYZE TABLE更新统计信息3. 诊断索引失效的实用技巧3.1 EXPLAIN执行计划分析重点关注以下字段typeALL表示全表扫描key实际使用的索引rows预估扫描行数ExtraUsing filesort或Using temporary需要警惕3.2 开启慢查询日志配置参数slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 13.3 使用性能分析工具推荐工具Percona Toolkit的pt-index-usageMySQL Enterprise Monitor阿里云的DAS诊断报告4. 索引设计的最佳实践三星索引原则一星WHERE条件匹配索引列二星ORDER BY匹配索引列三星SELECT列被索引覆盖索引选择策略高选择性字段优先建索引避免过度索引每个索引都有维护成本定期使用pt-index-usage清理无用索引联合索引设计口诀等值查询放左边范围查询放右边排序字段放最后分组字段要前置5. 真实案例电商系统索引优化去年优化过一个日均百万订单的电商系统通过索引优化将结算页响应时间从2.3秒降到400毫秒。主要措施将(user_id, status)联合索引改为(user_id, status, create_time)为支付时间字段添加函数索引(DATE(pay_time))将ORDER BY create_time DESC改为ORDER BY id DESC利用主键索引优化后效果索引命中率从65%提升到92%数据库CPU使用率下降40%慢查询数量减少85%6. 面试加分技巧当被问到索引失效问题时可以这样展示深度从存储结构解释 MySQL的InnoDB引擎使用B树索引当查询条件不能利用树的有序性时...结合优化器原理 优化器会根据统计信息选择执行计划当预估索引扫描行数超过阈值...引用实际案例 在我们订单系统中曾遇到...通过...方案解决了...延伸讨论索引下推优化(ICP)MRR多范围读取优化覆盖索引与回表代价最后分享一个排查索引问题的黄金法则当发现查询变慢时先看执行计划再看索引设计最后考虑SQL重写。记住好的索引设计应该像精心规划的交通网络让数据查询永远走快速路。
