【Java项目技术亮点】EXPLAIN深度分析与慢查询治理 写在前面说实话做后端开发这些年我见过太多项目上线后因为一条慢SQL把数据库拖垮的事故。有一次凌晨两点被叫醒生产环境MySQL CPU飙到90%一查发现一条统计SQL执行了30秒连接池被打满整个服务差点雪崩。那次之后我才真正重视起慢查询治理。这篇文章把我踩过的坑和积累的经验整理出来希望能帮你少走点弯路。文章目录一、为什么慢查询是性能杀手1.1 一个真实的生产事故1.2 慢查询的危害1.3 生活类比堵车二、开启慢查询日志2.1 MySQL配置2.2 查看慢查询日志2.3 慢查询日志分析要点三、EXPLAIN字段逐行解读3.1 EXPLAIN基本用法3.2 id列执行顺序3.3 select_type列查询类型3.4 table列访问的表3.5 type列访问类型重点3.6 possible_keys列可能使用的索引3.7 key列实际使用的索引3.8 key_len列索引使用长度3.9 ref列索引匹配条件3.10 rows列预估扫描行数3.11 Extra列额外信息重点四、常见慢查询场景与优化4.1 场景1全表扫描typeALL4.2 场景2索引失效4.3 场景3Using filesortORDER BY无索引4.4 场景4Using temporaryGROUP BY/DISTINCT无索引4.5 场景5大表JOIN效率低五、慢查询治理体系5.1 事前SQL Review5.2 事中实时监控5.3 事后定期分析5.4 治理流程六、踩坑指南七、问题与解答Q1EXPLAIN和EXPLAIN ANALYZE有什么区别Q2为什么加了索引查询还是很慢Q3慢查询日志记录了太多数据怎么过滤八、面试高频考点汇总考点1EXPLAIN中type列有哪些值性能排序是怎样的考点2Extra列中Using filesort和Using temporary代表什么考点3什么是覆盖索引考点4索引失效的常见场景有哪些考点5如何分析一条慢SQL九、模拟面试官提问和参考答案场景题1生产环境CPU飙高你如何判断是不是慢查询导致的场景题2有个分页查询 LIMIT 1000000, 10 很慢怎么优化场景题3表有联合索引 (a, b, c)以下SQL能否用到索引场景题4索引加了但EXPLAIN显示不走索引可能是什么原因场景题5如何设计一套慢查询治理方案十、互动话题十一、参考资料一、为什么慢查询是性能杀手1.1 一个真实的生产事故去年双十一前夕我们系统的订单查询接口突然超时。监控告警狂响MySQL CPU飙到90%QPS从平时的2000暴跌到200。紧急排查发现运营同学跑了一条统计SQLSELECTCOUNT(*)FROMorderWHEREcreate_time2024-01-01ANDstatus1;这条SQL执行了30秒扫描了800万行数据。连接池被打满其他正常请求全部排队等待整个系统差点雪崩。1.2 慢查询的危害危害类型具体表现影响程度响应延迟接口响应从100ms变成10s用户体验极差连接池打满数据库连接被慢查询占满系统不可用雪崩效应上游服务超时重试流量翻倍级联故障主从延迟慢查询在主库执行从库复制滞后数据不一致1.3 生活类比堵车慢查询就像城市主干道上的严重堵车。一条主干道堵了整个城市的交通都受影响。救护车、消防车过不去后果可想而知。数据库也是一样一条慢SQL能把整个系统拖下水。二、开启慢查询日志2.1 MySQL配置找到MySQL配置文件通常是my.cnf或my.ini添加或修改以下配置[mysqld] # 开启慢查询日志 slow_query_log 1 # 慢查询日志文件路径 slow_query_log_file /var/log/mysql/slow.log # 超过1秒的查询记录为慢查询 long_query_time 1 # 记录未使用索引的查询建议开启 log_queries_not_using_indexes 1修改后重启MySQLsudosystemctl restart mysql2.2 查看慢查询日志方式一直接查看日志文件# 查看最新的慢查询tail-f/var/log/mysql/slow.log方式二使用 mysqldumpslow 工具# 按执行时间排序显示前10条mysqldumpslow-st-t10/var/log/mysql/slow.log# 按执行次数排序mysqldumpslow-sc-t10/var/log/mysql/slow.log方式三使用 pt-query-digest 工具推荐# 安装 Percona Toolkitsudoapt-getinstallpercona-toolkit# 分析慢查询日志生成详细报告pt-query-digest /var/log/mysql/slow.logslow_query_report.txtpt-query-digest输出包含Rank按查询时间占比排名Query ID查询指纹Response time总响应时间和单次平均时间Calls执行次数R/Call每次调用平均时间V/M响应时间方差均值越大越不稳定2.3 慢查询日志分析要点指标含义关注重点频率多久出现一次高频慢查询优先处理执行时间单次执行多久超过1秒就要警惕返回行数返回了多少数据返回行数远大于需要 浪费扫描行数扫描了多少行扫描行数 / 返回行数 100 严重三、EXPLAIN字段逐行解读3.1 EXPLAIN基本用法EXPLAINSELECT*FROMuserWHEREid100;输出大概长这样---------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | ----------------------------------------------------------------------------------------------------------下面逐字段解读。3.2 id列执行顺序id相同执行顺序从上到下id不同id越大越先执行。-- id相同的情况EXPLAINSELECT*FROMuseru,orderoWHEREu.ido.user_id;-- id都是1先执行user表再执行order表-- id不同的情况子查询EXPLAINSELECT*FROMuserWHEREid(SELECTuser_idFROMorderWHEREid100);-- 子查询id2先执行外层id1后执行3.3 select_type列查询类型类型含义示例SIMPLE简单查询不包含子查询或UNIONSELECT * FROM userPRIMARY最外层查询包含子查询时的外层SUBQUERY子查询WHERE id IN (SELECT ...)DERIVED派生表FROM后的子查询FROM (SELECT ...) AS tUNIONUNION中的第二个及后续查询SELECT ... UNION SELECT ...3.4 table列访问的表显示当前行访问的是哪张表。如果是派生表会显示derivedNN是子查询的id。3.5 type列访问类型重点type是判断SQL性能的核心指标性能从好到差system const eq_ref ref range index ALLtype含义性能示例system表只有一行数据极好系统表const通过主键或唯一索引一次命中极好WHERE id 1eq_refJOIN时被驱动表通过主键或唯一索引匹配极好联表查询ON条件是主键ref通过普通索引匹配好WHERE name 张三name有索引range索引范围扫描还行WHERE id BETWEEN 1 AND 100index全索引扫描较差扫描整个索引树ALL全表扫描极差没有用到索引踩坑提醒type出现index或ALL就要警惕了。我见过太多人看到index以为用了索引就万事大吉实际上index是扫描整个索引树和全表扫描差不了多少3.6 possible_keys列可能使用的索引显示MySQL认为可能用到的索引。注意只是可能实际不一定用。3.7 key列实际使用的索引显示MySQL实际选择的索引。如果为NULL表示没有用到索引。3.8 key_len列索引使用长度这个字段很关键可以判断联合索引实际用了几个字段。数据类型key_lenint4bigint8varchar(20)20 * 4 2 82utf8mb4datetime5示例-- 联合索引 idx_name_age (name, age)EXPLAINSELECT*FROMuserWHEREname张三ANDage20;-- key_len 82 4 86说明两个字段都用到了EXPLAINSELECT*FROMuserWHEREname张三;-- key_len 82说明只用到了name字段3.9 ref列索引匹配条件显示索引的哪一列被使用了常见值const常量匹配库名.表名.字段名表的字段匹配3.10 rows列预估扫描行数MySQL预估需要扫描的行数。这个数字越小越好。踩坑提醒rows是预估的不是实际的基于统计信息计算如果统计信息过期这个值可能偏差很大。我踩过这个坑EXPLAIN显示rows100实际执行扫描了100万行。3.11 Extra列额外信息重点Extra值含义好坏Using index覆盖索引不需要回表极好Using index conditionICP索引下推减少回表好Using whereServer层过滤数据一般Using filesort文件排序没有用到索引排序差Using temporary使用了临时表差Using join buffer使用Join缓存一般示例解读-- 覆盖索引性能好EXPLAINSELECTid,nameFROMuserWHEREname张三;-- Extra: Using index-- 文件排序性能差EXPLAINSELECT*FROMuserORDERBYage;-- Extra: Using filesort-- 使用了临时表EXPLAINSELECTstatus,COUNT(*)FROMuserGROUPBYstatus;-- Extra: Using temporary; Using filesort四、常见慢查询场景与优化4.1 场景1全表扫描typeALL问题SQL-- user表的phone字段没有索引EXPLAINSELECT*FROMuserWHEREphone13800138000;EXPLAIN结果type: ALL rows: 1000000 Extra: Using where优化方案加索引-- 添加索引ALTERTABLEuserADDINDEXidx_phone(phone);-- 再次EXPLAINtype: refrows:1key: idx_phone4.2 场景2索引失效2.1 隐式类型转换-- phone是varchar类型传入数字EXPLAINSELECT*FROMuserWHEREphone13800138000;-- type: ALL索引失效-- 正确写法EXPLAINSELECT*FROMuserWHEREphone13800138000;-- type: ref索引生效2.2 对索引字段做函数操作-- 错误对create_time做函数操作EXPLAINSELECT*FROMuserWHEREYEAR(create_time)2024;-- type: ALL-- 正确改写为范围查询EXPLAINSELECT*FROMuserWHEREcreate_timeBETWEEN2024-01-01AND2024-12-31;-- type: range2.3 like ‘%xxx’ 前缀模糊-- 前缀模糊索引失效EXPLAINSELECT*FROMuserWHEREnameLIKE%张三%;-- type: ALL-- 后缀模糊索引生效EXPLAINSELECT*FROMuserWHEREnameLIKE张三%;-- type: range4.3 场景3Using filesortORDER BY无索引问题SQL-- 按create_time排序但create_time没有索引EXPLAINSELECT*FROMorderWHEREuser_id100ORDERBYcreate_timeDESCLIMIT10;-- Extra: Using where; Using filesort优化方案加复合索引-- 添加复合索引注意字段顺序ALTERTABLEorderADDINDEXidx_user_time(user_id,create_time);-- 再次EXPLAIN-- Extra: Using index condition-- 索引直接有序不需要额外排序踩坑提醒复合索引的字段顺序很重要把等值查询的字段放前面范围查询/排序的字段放后面。我见过太多人索引字段顺序写反了结果索引只用了一半。4.4 场景4Using temporaryGROUP BY/DISTINCT无索引问题SQLEXPLAINSELECTstatus,COUNT(*)FROMuserGROUPBYstatus;-- Extra: Using temporary; Using filesort优化方案-- 方案1给GROUP BY字段加索引ALTERTABLEuserADDINDEXidx_status(status);-- 方案2改写为子查询数据量大时SELECTstatus,cntFROM(SELECTstatus,COUNT(*)AScntFROMuserWHEREid0GROUPBYstatus)t;4.5 场景5大表JOIN效率低问题SQL-- user表1000万行order表5000万行EXPLAINSELECT*FROMuseruJOINorderoONu.ido.user_idWHEREu.status1;优化方案小表驱动大表-- 确保驱动表是小结果集EXPLAINSELECT*FROM(SELECT*FROMuserWHEREstatus1)uJOINorderoONu.ido.user_id;-- 或者使用STRAIGHT_JOIN强制驱动顺序EXPLAINSELECT*FROMuseru STRAIGHT_JOINorderoONu.ido.user_idWHEREu.status1;同时确保JOIN字段有索引ALTERTABLEorderADDINDEXidx_user_id(user_id);五、慢查询治理体系5.1 事前SQL Review代码审查时必须检查SQL新加的SQL是否走了索引是否有全表扫描风险是否在大表上做全量操作是否用了SELECT *Review Checklist检查项通过标准是否用到索引EXPLAIN的type至少为range是否扫描过多数据rows预估 10000是否有filesortExtra不包含Using filesort是否有temporaryExtra不包含Using temporary是否SELECT *只查询需要的字段5.2 事中实时监控搭建 Prometheus Grafana 慢查询监控大盘# Prometheus 配置-job_name:mysqlstatic_configs:-targets:[localhost:9104]关键监控指标指标名告警阈值含义mysql_global_status_slow_queries 10/分钟慢查询数量mysql_global_status_threads_running 50正在执行的线程mysql_global_status_innodb_row_lock_waits 10/分钟行锁等待次数5.3 事后定期分析每周慢查询TOP10分析报告模板1. 查询SQL 2. 执行次数 / 平均执行时间 3. 扫描行数 / 返回行数 4. EXPLAIN分析 5. 优化建议 6. 优化后预计提升5.4 治理流程发现慢查询 → EXPLAIN分析 → 定位原因 → 优化SQL/加索引 → 验证效果 → 监控持续观察 ↑ | └──────────────── 定期巡检形成闭环 ────────────────────────────┘六、踩坑指南坑1EXPLAIN的rows是预估不是实际我踩过这个坑。开发环境数据量小EXPLAIN显示rows100信心满满地上线。结果生产环境800万数据扫描了100万行。一定要在生产环境的从库上验证坑2开发环境和生产环境EXPLAIN结果差异大开发库就几百条数据优化器可能选择全表扫描。生产库几百万数据同样的SQL可能需要走索引。务必在数据量相近的环境验证。坑3索引加对了但执行计划不走索引有时候明明有索引EXPLAIN显示keyNULL。大概率是统计信息过期了执行ANALYZETABLEuser;更新统计信息后优化器就会选择正确的索引。坑4优化器选择错误极少数情况下优化器会选错执行计划。可以用FORCE INDEX强制走索引SELECT*FROMuserFORCEINDEX(idx_phone)WHEREphone13800138000;但这只是应急手段不建议常态化使用。七、问题与解答Q1EXPLAIN和EXPLAIN ANALYZE有什么区别AEXPLAIN只显示执行计划预估不真正执行SQL。EXPLAIN ANALYZEMySQL 8.0.18会真正执行SQL显示实际执行时间和实际扫描行数。-- 仅看执行计划EXPLAINSELECT*FROMuserWHEREid1;-- 真正执行并分析注意会实际跑SQLEXPLAINANALYZESELECT*FROMuserWHEREid1;生产环境慎用EXPLAIN ANALYZE特别是UPDATE/DELETEQ2为什么加了索引查询还是很慢A可能的原因索引没用到检查EXPLAIN的key列是否为NULL回表次数太多SELECT * 导致大量回表考虑覆盖索引数据量太大即使走索引扫描行数还是很多考虑分表分库索引选择性差比如性别字段只有男/女索引效果很差服务器负载高磁盘IO打满CPU飙高再好的索引也白搭Q3慢查询日志记录了太多数据怎么过滤A可以通过配置过滤# 只记录超过10秒的慢查询 long_query_time 10 # 不记录管理语句如ALTER TABLE log_slow_admin_statements 0 # 不记录从库的慢查询 log_slow_slave_statements 0或者使用pt-query-digest的过滤参数# 只分析查询时间超过5秒的pt-query-digest--filter$event-{Query_time} 5/var/log/mysql/slow.log八、面试高频考点汇总考点1EXPLAIN中type列有哪些值性能排序是怎样的答案system const eq_ref ref range index ALLsystem/const主键或唯一索引性能最好eq_refJOIN时主键关联ref普通索引等值查询range索引范围扫描index全索引扫描ALL全表扫描性能最差考点2Extra列中Using filesort和Using temporary代表什么答案Using filesortMySQL无法利用索引完成排序需要额外排序操作。通常是因为ORDER BY字段没有索引或者不符合最左前缀。Using temporary需要创建临时表来保存中间结果。常见于GROUP BY、DISTINCT、UNION等操作。两者都是性能警告需要优化。考点3什么是覆盖索引答案覆盖索引是指查询的所有字段都在索引中不需要回表查数据。-- 索引idx_name_age (name, age)SELECTname,ageFROMuserWHEREname张三;-- 只需要查索引树就能拿到所有数据Extra显示Using index优点减少回表IO大幅提升查询性能。考点4索引失效的常见场景有哪些答案对索引字段做函数操作YEAR(create_time)隐式类型转换字符串字段传数字like前缀模糊%张三%不符合最左前缀原则索引字段参与计算id 1 100OR条件中部分字段无索引全表扫描比索引更快时数据量极小考点5如何分析一条慢SQL答案EXPLAIN分析执行计划看type、key、rows、Extra查看是否走索引没走索引就分析原因查看扫描行数rows是否过大查看Extra是否有filesort或temporary查看慢查询日志确认执行时间和频率对比优化前后用EXPLAIN验证效果九、模拟面试官提问和参考答案场景题1生产环境CPU飙高你如何判断是不是慢查询导致的参考答案先看监控确认CPU飙高的时间点登录MySQL执行SHOW PROCESSLIST看是否有大量 “Sending data”、“Sorting result” 状态的线程查看慢查询日志定位该时间段的慢查询用EXPLAIN分析可疑SQL的执行计划如果是慢查询导致临时杀掉慢查询线程KILL query_id然后长期优化SQL或加索引场景题2有个分页查询LIMIT 1000000, 10很慢怎么优化参考答案延迟关联先查id再JOIN取数据SELECT*FROMuseruJOIN(SELECTidFROMuserORDERBYidLIMIT1000000,10)tONu.idt.id;覆盖索引确保子查询只查索引字段业务限制不允许跳页太深最多翻到100页记录上次位置用WHERE id last_id LIMIT 10替代深度分页场景题3表有联合索引 (a, b, c)以下SQL能否用到索引WHEREa1ANDb2ANDc3;-- 能全部用到WHEREa1ANDb2;-- 能用到a,bWHEREa1ANDc3;-- 能只用到ac断了WHEREb2ANDc3;-- 不能最左前缀断了WHEREa1ANDb2ANDc3;-- 能用到a,bb是范围c用不到场景题4索引加了但EXPLAIN显示不走索引可能是什么原因参考答案统计信息过期 →ANALYZE TABLE更新数据量太小全表扫描更快查询条件用了函数或隐式转换导致索引失效索引选择性太差如性别字段查询范围太大回表成本高于全表扫描使用了!、、NOT IN等操作场景题5如何设计一套慢查询治理方案参考答案事前预防SQL Review EXPLAIN检查 索引规范事中监控PrometheusGrafana监控慢查询数量、执行时间事后分析每周慢查询TOP10报告持续跟踪优化效果应急机制自动告警 自动KILL超长查询 限流降级团队规范代码提交必须附带EXPLAIN结果 索引变更流程十、互动话题你在工作中遇到过最离谱的慢查询是什么排查了多久才找到原因欢迎在评论区分享你的翻车经历咱们一起复盘十一、参考资料MySQL官方文档 - EXPLAIN输出格式MySQL官方文档 - 慢查询日志