MySQL慢SQL瓶颈定位
一、整体分析思路
遵循「耗时拆分 → 执行计划定位 → 资源/锁/IO排查 → 语句逻辑分析」四步，先分清是扫描多、排序/分组、锁等待、IO高、网络/返回数据量大哪一类瓶颈。
二、第一步：拆分SQL总耗时（区分时间去向）
一条SQL总耗时 =执行耗时 + 锁等待耗时 + IO耗时 + 网络传输耗时
先通过日志/状态判断时间花在哪。
1. 从慢查询日志提取核心指标
慢日志单条记录关键字段：
Query_time：总执行时间（核心）Lock_time：等待表/行锁的时间Rows_sent：返回给客户端的行数Rows_examined：MySQL实际扫描的行数Rows_read：读取数据行数
快速判断瓶颈类型
- Lock_time 占比高→ 瓶颈：锁竞争（并发高、事务长、行锁升级为表锁）
- Rows_examined 远大于 Rows_sent→ 瓶颈：无效扫描（索引失效/无索引）
- Query_time 高、行数不多、出现 filesort/temporary→ 瓶颈：排序/临时表
- Rows_sent 极大→ 瓶颈：返回数据过多、网络传输慢
- 磁盘IO飙升、CPU高 → 瓶颈：全表扫描/大量随机IO
三、第二步：EXPLAIN 深度分析执行计划（核心瓶颈定位）
EXPLAIN是定位SQL内部瓶颈的核心，按字段逐一判断问题点，按优先级从高到低排查。
1. 访问类型 type（最关键，代表数据读取方式）
优先级：system > const > eq_ref > ref > range > index > ALL
| type 值 | 瓶颈说明 | 严重程度 |
|---|---|---|
| ALL | 全表扫描，逐行遍历整张表，CPU+IO双重消耗 | 严重，必须优化 |
| index | 全索引扫描，比全表扫描略好，但依然遍历整个索引树 | 高，需优化 |
| range | 范围查询（> < between in like 前缀匹配），范围过大则性能差 | 中等，看扫描范围 |
| ref/eq_ref | 正常索引命中，无明显瓶颈 | 正常 |
典型场景：type=ALL基本可以确定是缺失索引或索引失效。
2. key / possible_keys（索引使用情况）
possible_keys：理论可用索引key=NULL：未使用任何索引，主瓶颈就是索引问题key有值但type=ALL：索引存在但被SQL逻辑规避，索引失效
3. rows（预估扫描行数）
MySQL预估需要扫描的行数，数值越大，IO/CPU开销越高。
- 单表查询：几十万+行扫描 = 严重瓶颈
- 多表JOIN：驱动表rows大，会导致后续表循环匹配次数暴增
4. Extra 额外信息（隐藏瓶颈重灾区）
这是排序、分组、临时表、回表等隐性瓶颈的主要来源：
Using filesort
- 含义：无法利用索引排序，MySQL在内存/磁盘做文件排序
- 瓶颈：排序消耗CPU+临时IO，数据量越大越慢
- 触发：
ORDER BY字段无索引、排序字段不在联合索引末尾、多字段排序顺序不一致
Using temporary
- 含义：MySQL创建临时表存放中间结果
- 瓶颈：临时表涉及内存/磁盘读写，并发下性能暴跌
- 触发：
GROUP BY、DISTINCT、UNION、子查询、多表聚合无索引
Using index
- 含义：覆盖索引，无需回表查询数据，性能最优，无瓶颈
Using where
- 正常：索引过滤后再行条件判断；
- 异常：配合
type=ALL代表全表扫描后逐行过滤，开销极大。
Using join buffer (Block Nested Loop)
- 含义：JOIN 关联字段无索引，使用连接缓冲区做嵌套循环
- 瓶颈：多表关联严重低效，属于JOIN索引缺失问题。
5. id & select_type（子查询/关联查询瓶颈）
针对多表、子查询、UNION语句：
DERIVED：派生表（子查询生成临时表），高频出现则是瓶颈SUBQUERY：无法优化的子查询，执行多次，叠加耗时- 多表id混乱、执行顺序不合理：JOIN 驱动表选择错误（大表驱动小表）
四、第三步：区分四大类典型性能瓶颈+根因分析
结合日志 + Explain，把问题归为4大类，精准定位根因。
类别1：索引类瓶颈（最高发）
现象
- Explain：
type=ALL/index、key=NULL - 慢日志：
Rows_examined极大，Lock_time正常
根因
- Where/Order by/Group by 字段未建索引
- 索引失效：隐式类型转换、索引列运算/函数、
like %xxx、or跨索引、is not null/not in - 索引区分度太低（低基数索引，如性别、状态），MySQL放弃使用
- 联合索引顺序不合理（未遵循最左匹配）
类别2：排序/分组瓶颈（filesort + temporary）
现象
- Explain：
Using filesort/Using temporary - 慢日志：扫描行数不多，但 Query_time 偏高
根因
ORDER BY/GROUP BY字段未纳入联合索引- 聚合、去重操作（DISTINCT、UNION）数据量过大
- 内存排序空间不足，排序落地到磁盘文件
类别3：锁/事务瓶颈（并发场景高发）
现象
- 慢日志：
Lock_time占总耗时 30% 以上 - 实时
show processlist：大量会话处于Locked状态
根因
- 事务执行时间过长，行锁迟迟不释放
- 批量更新/删除无索引，行锁升级为表锁
- 并发争抢同一行数据，锁等待队列堆积
- 死锁（会话互相等待）
类别4：数据量/IO/网络瓶颈
现象1：大结果集返回
- 慢日志：
Rows_sent成千上万行 - 现象：查询本身很快，但客户端接收数据慢
- 根因：
SELECT *、未分页、一次性返回全量数据，网络传输+客户端解析耗时高
现象2：大分页瓶颈
- 语句：
LIMIT 100000, 10 - 根因：MySQL需要先扫描前10万行再丢弃，偏移量越大扫描行数越多
现象3：磁盘IO瓶颈
- 服务器监控：磁盘读IO 100%、CPU 偏高
- 根因：频繁全表扫描、无覆盖索引导致大量回表查询、临时表/排序落地磁盘
五、第四步：实时状态辅助分析（线上运行中SQL）
适合排查正在执行的慢SQL，补充静态日志的不足。
1. show full processlist
- 状态
Copying to tmp table：正在创建临时表 → 临时表瓶颈 - 状态
Sorting result：正在排序 → filesort 瓶颈 - 状态
Locked：被锁阻塞 → 锁瓶颈 - 状态
Sending data：正在传输结果集 → 网络/返回数据量大
2. 系统状态变量（全局负载）
-- 查看全表扫描次数showglobalstatuslike'Handler_read%';-- 临时表统计（磁盘临时表多=严重瓶颈）showglobalstatuslike'Created_tmp%';-- 文件排序次数showglobalstatuslike'Sort%';Created_tmp_disk_tables持续上涨：大量临时表落地磁盘Sort_merge_passes高：排序内存不足，多次合并排序文件
六、快速分析流程（实战速用）
- 看慢日志：对比
Lock_time/Rows_examined/Rows_sent，初步划分瓶颈大类 - 执行
EXPLAIN：优先看type→key→Extra，确认索引、排序、临时表问题 - 结合
processlist+ 服务器监控：判断是否锁、IO、CPU 资源瓶颈 - 回看SQL语法：检查是否分页不当、
SELECT *、索引失效写法、不合理子查询/JOIN
七、补充：常见误区
- 只看执行时间，不看扫描行数：有些SQL耗时1秒，但扫描百万行，并发后直接雪崩
- 有索引就万事大吉：索引失效、联合索引顺序错误依然会慢
- 忽略锁等待：并发场景下，锁等待比SQL本身执行更耗时
- 轻视临时表/文件排序：数据量上涨后，这两类问题会指数级变慢