MySQL索引失效与SQL优化实战指南

1. 从一条致命SQL到爬山邀请的故事上周五临下班前我像往常一样提交了当日最后一个功能更新。没想到第二天刚进办公室经理就拿着咖啡笑眯眯地站在我工位旁小王啊周末有空吗我知道有座山风景不错... 当时我的后背瞬间就湿透了——在IT圈爬山这个梗谁不知道这分明是线上出了重大事故事情源于我写的一条看似简单的统计SQLSELECT * FROM order_detail WHERE DATE_FORMAT(create_time,%Y-%m-%d) 2023-07-15 ORDER BY product_price DESC;2. EXPLAIN诊断索引失效的元凶2.1 性能灾难现场还原当DBA把EXPLAIN结果甩到我面前时我立刻明白了问题所在idselect_typetabletypepossible_keyskeyrowsExtra1SIMPLEorder_detailALLidx_createNULL287万Using filesort这个结果揭示了三个致命问题全表扫描typeALL表示没用到任何索引287万行数据被全部遍历索引失效possible_keys显示有idx_create索引但实际key为NULL额外排序Using filesort表示在内存中进行昂贵排序2.2 函数导致的索引失效原理核心问题出在DATE_FORMAT(create_time,%Y-%m-%d)这个函数调用上。MySQL的B树索引存储的是原始字段值当查询条件对字段使用函数时需要先对所有数据执行函数计算再将计算结果与条件值比较导致索引无法直接定位数据退化成全表扫描3. 索引优化的黄金法则3.1 正确改写SQL的三种方式方案一使用范围查询SELECT * FROM order_detail WHERE create_time BETWEEN 2023-07-15 00:00:00 AND 2023-07-15 23:59:59 ORDER BY product_price DESC;方案二冗余存储日期字段ALTER TABLE order_detail ADD COLUMN create_date DATE; UPDATE order_detail SET create_date DATE(create_time); CREATE INDEX idx_create_date ON order_detail(create_date); SELECT * FROM order_detail WHERE create_date 2023-07-15 ORDER BY product_price DESC;方案三使用生成列(MySQL 5.7)ALTER TABLE order_detail ADD COLUMN create_date DATE AS (DATE(create_time)) STORED, ADD INDEX idx_create_date(create_date);3.2 复合索引的最佳实践对于带排序的查询最优解是建立覆盖索引CREATE INDEX idx_create_price ON order_detail(create_time, product_price DESC); EXPLAIN SELECT * FROM order_detail WHERE create_time BETWEEN 2023-07-15 00:00:00 AND 2023-07-15 23:59:59 ORDER BY product_price DESC;执行计划显示typerange利用索引范围查询ExtraUsing index condition索引条件下推完全消除了filesort4. 索引使用的避坑指南4.1 索引失效的六大场景隐式类型转换WHERE user_id 1001user_id是INT类型前导模糊查询WHERE product_name LIKE %手机%OR条件不当WHERE a1 OR b2a、b需分别有索引NOT条件WHERE status ! 1IS NULL判断WHERE address IS NULL计算表达式WHERE price10 1004.2 索引选择性的重要性建立索引前先用这个SQL评估字段选择性SELECT COUNT(DISTINCT status)/COUNT(*) AS selectivity, COUNT(*) AS total_rows FROM orders;经验值选择性10%适合建索引选择性5%考虑其他优化方式5. 慢查询应急处理方案5.1 线上事故紧急止血当发现慢查询拖垮数据库时通过SHOW PROCESSLIST定位问题SQL用KILL QUERY [process_id]终止执行临时添加FORCE INDEX强制走索引SELECT * FROM order_detail FORCE INDEX(idx_create) WHERE DATE(create_time) 2023-07-15 LIMIT 100;5.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定期使用pt-query-digest分析pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt6. 我的血泪经验总结EXPLAIN是必备技能每条SQL上线前必须用EXPLAIN验证执行计划索引不是万能的维护索引有成本单表索引建议不超过5个ORM框架要谨慎MyBatis、Hibernate生成的SQL可能包含隐式转换监控要常态化配置PrometheusGranfa监控QPS、慢查询等指标那次事故后我养成了SQL审查清单[ ] 是否使用了函数操作索引字段[ ] ORDER BY字段是否有索引支持[ ] 查询范围是否过大[ ] 是否使用了覆盖索引现在我的SQL再没出过问题经理的爬山邀请也变成了每周的技术分享会。记住每个DBA手里都有一份黑名单别让你的SQL成为他们茶余饭后的段子。