SQL 性能分析：从慢查询日志到执行计划的完整定位方法- 惊觉

适用环境：MySQL 8.0.x，InnoDB 存储引擎
解决什么问题：拿到一条慢 SQL，知道该看哪个指标、用哪个工具、按什么顺序排查
一句话结论：性能分析不是"看 EXPLAIN 有没有走索引"，而是用尽可能少的观测成本，把"慢"归因到具体的一层——实例、语句，还是执行计划。

前言

上一篇讲了索引的使用规则和失效场景，解决的是"为什么索引没被用上"。

但实际工作中更常见的困境是另一种：

监控告警说接口变慢了，但不知道是哪条 SQL；
慢查询日志里有几百条，不知道先优化哪条；
找到了慢 SQL，EXPLAIN 也看了，还是不知道瓶颈在哪；
优化完了，感觉快了，但说不出快了多少。

这些问题的共同点是：缺少一套从"现象"到"根因"的定位路径。单纯会看 EXPLAIN 不够——EXPLAIN 只回答"优化器打算怎么执行"，不回答"这条 SQL 值不值得优化"、“慢是因为 SQL 还是因为锁”。

本文约定
文中所有 EXPLAIN、EXPLAIN ANALYZE 输出及耗时数字均为演示数据，用于说明指标含义，不代表任何真实环境。执行计划的具体形态与数据分布强相关，请以自己环境中的实测输出为准。

一、性能分析的三个层级

排查慢 SQL 最常见的错误是跳级：一上来就 EXPLAIN 某条自己怀疑的 SQL。如果瓶颈其实在实例层（Buffer Pool 太小、连接数打满、锁等待），再怎么改 SQL 也没用。

正确的顺序是自顶向下，每一层只回答一个问题：

三层各自关注不同的指标：

层级	回答的问题	主要工具	典型指标
实例级	数据库整体累不累	`SHOW GLOBAL STATUS`、`sys.metrics`	QPS、TPS、`Threads_running`、Buffer Pool 命中率、锁等待数
语句级	哪条 SQL 消耗最多资源	慢查询日志、`events_statements_summary_by_digest`、`sys.statement_analysis`	总延迟、执行次数、平均延迟、扫描行数、返回行数
执行级	这条 SQL 的时间花在哪	`EXPLAIN`、`EXPLAIN ANALYZE`、`optimizer_trace`、`Handler_read_*`	`type`、`key`、`rows`、`filtered`、`actual time`、`loops`

关键认知：语句级分析中，“总延迟 = 平均延迟 × 执行次数”。一条 5ms 但每秒执行 3000 次的 SQL，对数据库的压力远大于一条 800ms 但每分钟执行 1 次的报表 SQL。只按单次耗时排序，会漏掉真正的元凶。

二、实例级：先判断整体是否健康

这一步的目标不是找 SQL，而是排除"不是 SQL 的问题"。

2.1 负载与并发

-- 当前连接与活跃线程
SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Threads_connected', 'Threads_running', 'Threads_created',
    'Max_used_connections', 'Aborted_connects'
);

-- 每秒查询/事务量（需间隔采样两次做差）
SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Questions', 'Com_select', 'Com_insert', 'Com_update', 'Com_delete',
    'Com_commit', 'Com_rollback'
);

读法：

Threads_running 持续高于 CPU 核数 → 有大量 SQL 在排队等 CPU，需要看语句级；
Threads_connected 接近 max_connections → 先查连接池配置和是否有连接泄漏，不是 SQL 问题；
Threads_created 增长快 → thread_cache_size 偏小，或短连接过多；
Com_select 远高于 Com_insert/update/delete → 读多写少，优化重点在查询侧。

注意：MySQL 8.0 已经移除了查询缓存（Query Cache），网上大量老文章用 Qcache_hits 判断性能，这个指标在 8.0 中已不存在，不要照搬。

2.2 Buffer Pool 命中率

SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Innodb_buffer_pool_read_requests',   -- 逻辑读次数
    'Innodb_buffer_pool_reads',           -- 物理读（穿透到磁盘）次数
    'Innodb_buffer_pool_pages_free',
    'Innodb_buffer_pool_pages_dirty'
);

命中率近似计算：

命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

长期低于 99%（OLTP 场景）→ Buffer Pool 偏小，或存在大量全表扫描把热数据挤出内存；
pages_free 接近 0 → Buffer Pool 已打满，属于正常现象（InnoDB 会尽量用满），但说明没有余量。

重要：命中率低是结果，不是原因。先确认是不是某条 SQL 在反复全表扫描，再考虑调大 innodb_buffer_pool_size。

2.3 临时表与排序

SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Created_tmp_tables',        -- 创建的内部临时表总数
    'Created_tmp_disk_tables',   -- 其中落到磁盘的
    'Sort_merge_passes',         -- 归并排序轮次，>0 说明 sort_buffer 不够
    'Select_full_join',          -- 无索引驱动的 JOIN 次数
    'Select_scan'                -- 全表扫描次数
);

两个高价值信号：

Created_tmp_disk_tables / Created_tmp_tables 比例高 → 存在大量带 GROUP BY、DISTINCT、UNION 或 ORDER BY 的查询，临时结果集超出内存；
Select_full_join > 0 且持续增长 → 存在关联字段无索引的 JOIN，这是最容易致命的性能问题之一，在数据量增长后会指数级恶化。

2.4 锁等待与长事务

-- 当前锁等待关系（基于 performance_schema.data_lock_waits）
SELECT * FROM sys.innodb_lock_waits\G

-- 当前持有锁的事务明细
SELECT
    ENGINE_TRANSACTION_ID AS trx_id,
    OBJECT_NAME           AS table_name,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM performance_schema.data_locks
ORDER BY ENGINE_TRANSACTION_ID;

如果 sys.innodb_lock_waits 有记录，说明已经有事务在等锁。此时任何 SQL 层面的优化都无效，必须先解决长事务：

-- 找出运行超过 60 秒的事务
SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_s,
    trx_rows_locked,
    trx_rows_modified,
    trx_mysql_thread_id
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started;

经验：线上"突然变慢"且伴随接口超时，先看 INNODB_TRX 和 data_locks，比看执行计划快得多。80% 的突发性慢，根因是长事务或未提交事务持有的行锁/间隙锁。

三、语句级：找出"哪条 SQL 值得优化"

确认实例整体健康后，进入语句级。目标是从成千上万条 SQL 中，筛出优化收益最高的那几条。

3.1 慢查询日志：最基础也最可靠的入口

开启方式（8.0 支持动态修改，无需重启）

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;              -- 单位秒，支持小数如 0.1
SET GLOBAL log_output = 'FILE,TABLE';        -- 同时写文件和 mysql.slow_log 表
SET GLOBAL log_queries_not_using_indexes = OFF;

几个容易踩坑的参数：

参数	建议	说明
`long_query_time`	生产 1s，压测 0.1s	设为 0 会记录所有 SQL，开销极大
`log_output`	`FILE` 或 `FILE,TABLE`	`TABLE` 便于用 SQL 分析，但写入 `mysql.slow_log` 有一定开销
`log_queries_not_using_indexes`	谨慎开启	记录未走索引的 SQL，但与慢无关的短查询也会被记录，容易"写爆"日志
`log_throttle_queries_not_using_indexes`	配合上一条使用	限制每分钟记录条数，防止日志膨胀
`min_examined_row_limit`	可设为 100	扫描行数低于该值的 SQL 不记录，过滤掉噪声

从 mysql.slow_log 表中直接分析

SELECT
    start_time,
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    db,
    LEFT(sql_text, 100) AS sql_preview
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 20;

生产环境更推荐 pt-query-digest（Percona Toolkit），它做的是"归一化聚合"——把参数不同的同类 SQL 合并统计：

pt-query-digest /var/lib/mysql/slow.log > slow_report.txt

输出会按**总耗时（而非单次耗时）**排序，直接告诉你 TOP 10 应该优化谁。这是慢日志分析的行业标准做法。

3.2 performance_schema：按 SQL 指纹聚合

如果不方便开慢日志，performance_schema 是更轻量的选择。它按 DIGEST（SQL 指纹，把常量归一化为 ?）聚合统计：

SELECT
    SCHEMA_NAME,
    LEFT(DIGEST_TEXT, 90)                                              AS digest_text,
    COUNT_STAR                                                         AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1e12, 2)                                    AS total_latency_s,
    ROUND(AVG_TIMER_WAIT / 1e9, 3)                                     AS avg_latency_ms,
    SUM_ROWS_EXAMINED                                                  AS rows_examined,
    SUM_ROWS_SENT                                                      AS rows_sent,
    ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0), 0), 1)  AS examined_per_sent,
    SUM_NO_INDEX_USED                                                  AS no_index_used,
    SUM_CREATED_TMP_DISK_TABLES                                        AS disk_tmp_tables,
    SUM_SORT_ROWS                                                      AS sorted_rows
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'demo'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

字段解读：

字段	含义	怎么用
`COUNT_STAR`	执行次数	配合总延迟，判断是否有高频小 SQL
`SUM_TIMER_WAIT`	累计耗时（皮秒）	除以 `1e12` 得秒，这是排序的第一依据
`AVG_TIMER_WAIT`	平均耗时（皮秒）	除以 `1e9` 得毫秒
`SUM_ROWS_EXAMINED`	扫描行数总和	越大越可能是全表扫描或索引失效
`SUM_ROWS_SENT`	返回行数总和	与扫描行数对比，看索引效率
`examined_per_sent`	扫描/返回比	> 100 就值得警惕，> 1000 基本是索引问题
`SUM_NO_INDEX_USED`	未使用索引的次数	> 0 说明存在全表扫描
`SUM_CREATED_TMP_DISK_TABLES`	落盘临时表次数	对应 `GROUP BY`/`ORDER BY` 无法用索引

这是全文最重要的一个指标：rows_examined / rows_sent。它直接反映"为了返回 N 行，数据库翻了多少行"。比值接近 1 说明索引精准，比值上千说明在大范围扫描后过滤。优化 SQL 的本质，就是把这个比值压下来。

3.3 sys 库：现成的可读视图

sys 库把 performance_schema 的原始数据换算成了人类可读的单位（秒、毫秒）：

-- 按总延迟排序的语句分析
SELECT
    db,
    LEFT(query, 80)      AS query_preview,
    exec_count,
    total_latency,
    avg_latency,
    rows_examined_avg,
    rows_sent_avg,
    full_scan,            -- 是否发生全表扫描（* 表示是）
    tmp_disk_tables,
    rows_sorted
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

sys.statement_analysis 的 full_scan 列非常实用——它直接标出哪些 SQL 发生过全表扫描，省去逐条 EXPLAIN 的功夫。

其他常用视图：

视图	用途
`sys.statement_analysis`	语句级聚合（总延迟、扫描行数、临时表）
`sys.statements_with_full_table_scans`	只看全表扫描的语句
`sys.statements_with_temp_tables`	只看用了临时表的语句
`sys.statements_with_sorting`	只看发生了排序的语句
`sys.session`	当前活跃会话正在执行什么
`sys.schema_table_statistics`	按表统计 IO 与访问次数

3.4 分析正在执行的长 SQL

线上卡住时，“事后分析"来不及，需要看"当前正在跑什么”：

-- 当前活跃会话（已按耗时排序）
SELECT
    thd_id, conn_id, user, db, command, time, state,
    LEFT(current_statement, 100) AS stmt
FROM sys.session
WHERE command <> 'Sleep'
ORDER BY time DESC;

拿到 conn_id 后，可以直接查看这条连接正在用的执行计划，不需要重跑：

EXPLAIN FOR CONNECTION 12345;

EXPLAIN FOR CONNECTION 只对正在执行的语句有效。这是排查线上长查询最省事的工具，很多人不知道。

四、执行级：看清"这条 SQL 到底怎么跑的"

到这里已经锁定了目标 SQL。执行级分析要回答两个不同的问题：

优化器打算怎么执行？ → EXPLAIN
实际执行时发生了什么？ → EXPLAIN ANALYZE、Handler_read_*

这两者经常不一致，而不一致本身就是最重要的线索。

4.1 精读 EXPLAIN：三个必须一起看的字段

大多数人看 EXPLAIN 只看 type 和 key。但真正决定性能的是 rows × filtered 这个组合。

EXPLAIN
SELECT id, order_no, amount
FROM t_order
WHERE user_id = 10086
  AND status = 1
ORDER BY create_time DESC
LIMIT 20;

示例输出：

id	type	key	key_len	rows	filtered	Extra
1	ref	idx_user_status	8	12480	10.00	Using where; Using filesort

逐字段读：

key_len —— 判断联合索引用到了第几列

这是被严重低估的字段。以 idx_user_status (user_id BIGINT, status TINYINT) 为例：

BIGINT 占 8 字节（NOT NULL），允许 NULL 则 +1；
TINYINT 占 1 字节，允许 NULL 则 +1；
若 key_len = 8 → 只用到 user_id 一列；
若 key_len = 9 → 两列都用到（status 为 NOT NULL）。

rows × filtered / 100 —— 估算实际参与后续处理的行数

rows = 12480 表示存储引擎层预估要读 12480 行；
filtered = 10.00 表示其中约 10% 满足 WHERE 剩余条件，即约 1248 行进入排序；
最终只返回 20 行。

也就是说：为了 20 行结果，读了 12480 行、排了 1248 行。 examined_per_sent 高达 624，典型的索引设计不匹配。

Extra 里的 Using filesort —— 排序发生在索引之外

如果 ORDER BY create_time 而索引是 (user_id, status)，create_time 在索引中无序，MySQL 必须把 1248 行取出来再排序。这就是 Using filesort。

修正方向：把索引改成 (user_id, status, create_time)，让 WHERE 的等值列在前、ORDER BY 的列在后，可以同时消掉 filesort 和大范围扫描。

4.2 EXPLAIN 的三种格式

EXPLAIN FORMAT = TRADITIONAL SELECT ...;   -- 默认表格，适合快速扫读
EXPLAIN FORMAT = TREE        SELECT ...;   -- 树形，8.0 更推荐，能看清嵌套顺序
EXPLAIN FORMAT = JSON        SELECT ...;   -- 含 cost 估算，适合分析优化器决策

FORMAT=JSON 中最有价值的是成本信息：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3120.55"
    },
    "table": {
      "table_name": "t_order",
      "access_type": "ref",
      "key": "idx_user_status",
      "rows_examined_per_scan": 12480,
      "rows_produced_per_join": 1248,
      "filtered": "10.00",
      "cost_info": {
        "read_cost": "1100.20",
        "eval_cost": "124.80",
        "prefix_cost": "3120.55"
      }
    }
  }
}

query_cost 是优化器认为的执行代价。它的绝对值没有意义，但可以用于对比：改索引前后、改 SQL 前后，query_cost 下降通常意味着执行路径变优。

注意：cost 是基于统计信息估算的模型值，不是毫秒。成本低不代表一定快（比如成本估算只算 IO 和 CPU，不算锁等待），成本高也不代表一定慢（比如全部命中 Buffer Pool）。

4.3 EXPLAIN ANALYZE：看实际执行

EXPLAIN 是"计划"，EXPLAIN ANALYZE 是"计划的执行结果"（MySQL 8.0.18+ 支持）。

EXPLAIN ANALYZE
SELECT id, order_no, amount
FROM t_order
WHERE user_id = 10086
  AND status = 1
ORDER BY create_time DESC
LIMIT 20;

示例输出（简化）：

-> Limit: 20 row(s)  (actual time=214.5..214.6 rows=20 loops=1)
    -> Sort: create_time DESC, limit input to 20 row(s) per chunk
        (actual time=214.4..214.4 rows=20 loops=1)
        -> Filter: (t_order.status = 1)  (actual time=0.08..198.2 rows=1248 loops=1)
            -> Index lookup on t_order using idx_user_status
               (user_id=10086)  (actual time=0.07..180.3 rows=12480 loops=1)

三个必须看的数字：

指标	含义	诊断价值
`actual time=start..end`	该节点从开始到结束的实际耗时（毫秒）	找最耗时的节点，而不是看总耗时
`rows`	实际产出的行数	与 `EXPLAIN` 的 `rows` 对比，偏差大说明统计信息不准
`loops`	该节点被执行的次数	嵌套循环中被驱动表的 `loops` 等于驱动表行数，总耗时需乘以 loops

上面这个例子揭示了瓶颈所在：Index lookup 节点本身只花 180ms 读了 12480 行，但 Sort 节点又花了 34ms，而 Filter 到 Sort 之间只有 1248 行——时间主要花在"读太多行"和"额外排序"上，不是花在排序算法本身。

重要技巧：嵌套循环连接（Nested loop join）中，内层节点的 actual time 是单次循环的耗时。判断真实开销要算 actual time × loops。这是误读 EXPLAIN ANALYZE 最常见的地方——看到内层"只花了 0.5ms"就以为不是瓶颈，实际上 loops = 100000，总耗时 50 秒。

4.4 Handler 计数器：验证"到底读了多少行"

EXPLAIN 的 rows 是估算值，Handler_read_* 是真实读次数，这是最硬的证据：

FLUSH STATUS;   -- 重置当前会话的计数器

SELECT id FROM t_order
WHERE user_id = 10086 AND status = 1;

SHOW SESSION STATUS LIKE 'Handler_read%';

关键计数器：

变量	含义	判断依据
`Handler_read_key`	通过索引键定位的次数	走索引查询时该值应接近结果行数
`Handler_read_next`	按索引顺序读下一行的次数	范围扫描/全索引扫描时大量增长
`Handler_read_rnd_next`	按数据文件顺序读下一行的次数	全表扫描的标志，越大越糟
`Handler_read_rnd`	按固定位置读行（通常伴随排序）	出现 `filesort` 时增长

Handler_read_rnd_next 是判断"是否真的全表扫描"最可靠的指标——比 type=ALL 更可靠，因为 type=ALL 有时是覆盖索引的索引全扫描。

4.5 optimizer_trace：优化器为什么这么选

当"明明有更好的索引，优化器却不用"时，只能问优化器自己：

SET optimizer_trace = 'enabled=on';

SELECT id, order_no FROM t_order
WHERE user_id = 10086 AND status = 1;

SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G

SET optimizer_trace = 'enabled=off';

TRACE 里能看到：

considered_execution_plans：优化器比较了哪些方案，各自成本多少；
ref_optimizer_key_uses：每个候选索引的估算行数；
rows_estimation：统计信息给出的基数（cardinality）；
reconsidering_access_paths_for_index_ordering：因 ORDER BY 而重新选择索引的过程。

警告
optimizer_trace 只对当前 session 有效，且输出可能非常大（受 optimizer_trace_max_mem_size 限制，超出会被截断）。
只应在排查问题时临时开启，绝不要在生产环境长期打开。

4.6 统计信息：估算不准的根源

优化器所有决策都基于统计信息。统计信息过期时，rows 估算会严重偏离实际：

-- 查看索引基数（cardinality）
SHOW INDEX FROM t_order;

-- 查看表统计信息
SELECT * FROM mysql.innodb_table_stats WHERE table_name = 't_order';
SELECT * FROM mysql.innodb_index_stats WHERE table_name = 't_order';

-- 手动更新统计信息
ANALYZE TABLE t_order;

cardinality 表示索引中不重复值的估算数量。如果 cardinality 明显小于真实值（如实际有 50 万个不同 user_id，却显示 2000），优化器会误判索引选择性，从而做出错误选择。

判断依据：EXPLAIN 的 rows 与 EXPLAIN ANALYZE 的 rows 相差 10 倍以上，基本可以断定统计信息不准，先 ANALYZE TABLE 再谈优化。

4.7 关于 SHOW PROFILE

SHOW PROFILE 能给出各阶段耗时（如 Sending data、Sorting result），但在 MySQL 8.0 中已被标记为弃用，官方建议改用 performance_schema：

-- 已弃用，仅作了解
SET profiling = 1;
SELECT ...;
SHOW PROFILE FOR QUERY 1;

替代方案是 EXPLAIN ANALYZE（更细，到算子级）或 performance_schema.events_stages_history。新项目不要再依赖 SHOW PROFILE。

五、六类高频性能问题与优化套路

前面是方法论，这一节是"对号入座"——看到某个特征，直接对应到优化方案。

5.1 全表扫描与索引失效

识别特征

type = ALL
Handler_read_rnd_next 大幅增长
sys.statement_analysis 中 full_scan 标记为 *
rows_examined / rows_sent 比值极高

优化方向

先按索引规则排查（函数、计算、隐式转换、前导 %、不满足最左前缀），再评估是否需要新增索引。具体判定方法见本专栏索引篇。

5.2 回表过多

识别特征

type = ref 或 range，key 正常，但 rows 很大
EXPLAIN ANALYZE 中 Index lookup 之后出现大量单行回表
查询返回字段多，且都不在索引中

优化方向

覆盖索引：把 SELECT 需要的字段放进索引，消掉回表。代价是索引变大、写放大；
延迟关联：先用索引查出主键，再关联取整行，把随机 IO 变成批量 IO（见 5.4）。

判断依据：如果查询从 12480 行里只返回 20 行，那么大部分回表是无用功。此时应该优化的是"让索引先过滤到 20 行"，而不是给更多字段建索引。

5.3 临时表与 filesort

识别特征

Extra 中出现 Using temporary / Using filesort
Created_tmp_disk_tables 增长
Sort_merge_passes > 0（说明排序数据超出 sort_buffer_size，发生了磁盘归并）

优化方向

让 ORDER BY / GROUP BY 的列成为索引的最左连续前缀，且与 WHERE 的等值列组合成 (等值列..., 排序列...)；
排序方向不一致（ORDER BY a ASC, b DESC）在 MySQL 8.0 中可用降序索引支持：INDEX idx (a ASC, b DESC)；
谨慎调大 sort_buffer_size：它是 per-connection 的，设成 8MB × 500 连接 = 4GB 内存，容易引发 OOM。

5.4 深分页

问题 SQL

SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;

这条 SQL 的代价不是"返回 20 行"，而是"读出 100 万零 20 行再丢弃前 100 万行"。

方案一：游标分页（推荐，适用于连续翻页）

SELECT * FROM t_order WHERE id > 1000000 ORDER BY id LIMIT 20;

要求：排序字段唯一且有序（通常是主键）、不支持跳页。

方案二：延迟关联（适用于必须跳页）

SELECT o.*
FROM t_order o
JOIN (
    SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;

子查询只读主键（可走覆盖索引），大幅减少回表次数。

方案三：业务层限制

绝大多数后台系统的深分页是"假需求"——用户不会看到第 5 万页。直接限制最大可翻页数，或改成"按时间范围筛选"，比任何 SQL 技巧都有效。

5.5 隐式类型转换导致索引失效

识别特征

字段有索引，但 type = ALL 或 key = NULL
SHOW WARNINGS 中能看到转换提示

-- phone 是 VARCHAR，参数是数字
SELECT * FROM users WHERE phone = 13800138000;

原因：字符串列与数字比较时，MySQL 会把列转换为数字再比较（而不是把参数转成字符串），导致索引无法使用。

验证方法：

EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';

对比两次 key 是否为 NULL，即可确认。

5.6 锁等待与长事务

识别特征

SQL 单独执行很快，高并发下变慢
sys.innodb_lock_waits 有记录
lock_time 在慢日志中占比高

排查顺序

INNODB_TRX 找出长事务（见 2.4）；
performance_schema.data_locks 看锁类型（RECORD 行锁 / GAP 间隙锁 / NEXT-KEY）；
确认隔离级别：SELECT @@transaction_isolation;，REPEATABLE-READ 下范围条件会加间隙锁，容易与插入操作冲突；
缩小事务范围：把 SELECT ... FOR UPDATE 之后的外部调用（RPC、HTTP）移出事务。

经验：lock_time 高但 query_time 也高时，优先怀疑事务设计；lock_time 低而 query_time 高，才是 SQL 本身的问题。

六、完整案例：一条 3 秒的列表查询

6.1 现象

订单列表接口 P99 从 80ms 涨到 3000ms，数据量从 100 万增至 500 万。

6.2 定位过程

第一步：实例级排除

Threads_running 正常（约 8），innodb_lock_waits 无记录，Buffer Pool 命中率 99.7% → 不是实例层问题。

第二步：语句级定位

SELECT
    LEFT(DIGEST_TEXT, 90) AS digest_text,
    COUNT_STAR            AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_latency_s,
    SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'shop'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 3;

示例结果：

digest_text	exec_count	total_latency_s	rows_examined	rows_sent
`SELECT * FROM t_order WHERE user_id = ? AND status = ? ORDER BY create_time DESC LIMIT ?`	18200	1240.6	227,136,000	364,000

rows_examined / rows_sent = 624。单次平均 68ms，但累计 1240 秒，占库总耗时的绝大部分 → 确认是元凶。

第三步：执行级分析

EXPLAIN
SELECT * FROM t_order
WHERE user_id = 10086 AND status = 1
ORDER BY create_time DESC
LIMIT 20;

type	key	key_len	rows	filtered	Extra
ref	idx_user_status	8	12480	10.00	Using where; Using filesort

两个问题同时存在：

key_len = 8，只用到 user_id 一列，status 没参与索引定位（本应 9）；
create_time 不在索引中，1248 行需要额外排序（Using filesort）。

为什么 status 没用上？因为 status TINYINT NULL 允许 NULL，而 user_id BIGINT NOT NULL。若走两列，key_len 应为 8 + 1 + 1 = 10。这里只有 8，说明优化器只用了第一列——status 选择性太低（只有 0/1 两个值），用它定位收益不大，优化器主动放弃了。

第四步：改索引

-- 原索引
-- INDEX idx_user_status (user_id, status)

-- 新索引：等值列在前，排序列在后
ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time);

6.3 效果对比

对比项	优化前	优化后
`type`	`ref`	`ref`
`key`	`idx_user_status`	`idx_user_status_time`
`key_len`	8	10
`rows`	12480	20
`Extra`	`Using where; Using filesort`	`Using index condition`
`query_cost`（`FORMAT=JSON`）	3120.55	26.31
单次耗时（`EXPLAIN ANALYZE`）	214.6ms	0.42ms
`examined_per_sent`	624	1.0

关键变化：rows 从 12480 降到 20，filesort 消失。原因是新索引中 (user_id, status) 等值定位后，create_time 天然有序，可以直接取前 20 行。

6.4 一个必须注意的代价

新索引让读变快了，但带来了写放大：

每张表多一个索引，INSERT 需多维护一棵 B+Tree；
update_time 类字段若被索引，更新频率高的表要考虑维护成本；
索引宽度增加，Buffer Pool 能缓存的行数减少。

结论：索引优化永远是"读与写的权衡"。本例中读流量远大于写流量（18200 次/统计周期 vs 少量写入），所以值得。如果这是一张写入密集的日志表，就应该换思路（如异步汇总、冷热分离），而不是盲目加索引。

七、性能分析 Checklist

排查慢 SQL 时按顺序过一遍，能覆盖 90% 的场景。

□ 第一层：先排除非 SQL 因素

Threads_running 是否持续高于 CPU 核数
Threads_connected 是否接近 max_connections（连接池问题）
sys.innodb_lock_waits 是否有锁等待
INNODB_TRX 是否有超过 60 秒的长事务
Buffer Pool 命中率是否正常
是否处于备份、DDL、大批量导入期间

□ 第二层：锁定目标 SQL

慢日志或 events_statements_summary_by_digest 中 TOP N 是谁
按总延迟排序，不只按单次耗时
rows_examined / rows_sent 是否大于 100
SUM_NO_INDEX_USED 是否大于 0
有没有"执行次数极高、单次很快"的 SQL 在累积消耗

□ 第三层：分析执行

type 是否为 ALL 或 index
key 是否符合预期，key_len 是否用满了联合索引
rows × filtered 估算的实际处理行数
Extra 是否有 Using filesort / Using temporary
EXPLAIN ANALYZE 中最耗时的节点是哪个，loops 是多少
EXPLAIN 的 rows 与实际 rows 偏差是否超过 10 倍（统计信息）
Handler_read_rnd_next 是否异常增长

□ 第四层：验证效果

query_cost 是否下降
实际耗时是否下降（不是"感觉快了"）
扫描行数是否下降
是否对其他 SQL 造成负面影响（新索引是否被其他查询误用）
写入性能是否可接受
上线后观察 24 小时的 P99 和慢查询数量

八、五个常见误区

误区 1：只看 `EXPLAIN` 的 `type`，不看实际耗时

type = ref 不代表快。ref 扫描 100 万行照样慢。type 只描述访问方式，不描述代价大小。

误区 2：认为 `Using where` 就是索引失效

Using where 只表示"存储引擎返回行之后，Server 层还要再判断条件"。走索引的查询同样可能出现 Using where。

真正要关注的是 Using index condition（ICP，好）和 Using filesort（额外排序，需评估）。

误区 3：按单次耗时优化慢日志

一条 2 秒的报表 SQL 每天跑 1 次，和一条 30ms 的查询每秒跑 2000 次，后者对数据库的压力是前者的 30 倍。永远按总延迟排序。

误区 4：认为优化器选错索引是"bug"

大多数"选错索引"其实是统计信息不准或成本估算的合理结果。先 ANALYZE TABLE，再看 optimizer_trace，最后才考虑 FORCE INDEX。FORCE INDEX 是临时止血手段，不应长期留在代码里——数据分布变化后它会变成新的隐患。

误区 5：只看 SQL 不看总量

SELECT * FROM t_order WHERE user_id = ? 单次 0.1ms，但如果代码里在循环中调用 1000 次（N+1 查询），总耗时就是 100ms。这类问题的解法在应用层（批量查询、IN 改写、缓存），不在 SQL 层。

九、面试高频问题

1. 一条 SQL 很慢，你的排查思路是什么？

先分层：实例级排除锁等待、连接数、Buffer Pool 问题；语句级用慢日志或 performance_schema 按总延迟定位目标 SQL；执行级用 EXPLAIN 看计划、EXPLAIN ANALYZE 看实际、Handler_read_* 验证真实读取量。最后改索引或 SQL，并对比优化前后的扫描行数与耗时。

2. EXPLAIN 里你最关注哪些字段？

type（访问方式）、key（实际用哪个索引）、key_len（联合索引用到第几列）、rows × filtered（实际参与处理的行数）、Extra（是否排序、是否临时表、是否覆盖索引）。

3. rows 和 filtered 是什么关系？

rows 是存储引擎预估需要读取的行数，filtered 是这些行中满足剩余 WHERE 条件的百分比。二者相乘才是真正进入下一步处理的行数。只看 rows 会高估或低估实际代价。

4. EXPLAIN 和 EXPLAIN ANALYZE 有什么区别？

EXPLAIN 只展示优化器的执行计划，不执行 SQL；EXPLAIN ANALYZE（8.0.18+）真实执行并给出每个节点的实际耗时、实际行数和循环次数。两者对比能发现统计信息不准的问题。

5. 为什么 EXPLAIN 显示走了索引，实际还是慢？

可能原因：走了索引但扫描范围过大；回表次数太多；Using filesort 产生额外排序；锁等待；实际返回行数远超预期；JOIN 驱动表选择错误导致内层循环执行次数巨大（loops 很大）。

6. 什么是覆盖索引？怎么判断用上了？

查询所需字段全部包含在索引中，无需回表。判断依据是 Extra 中出现 Using index。

7. 慢查询日志里 SQL 太多，怎么高效分析？

用 pt-query-digest 做归一化聚合，按总耗时排序取 TOP N；或直接查 performance_schema.events_statements_summary_by_digest，按 SUM_TIMER_WAIT 排序。避免逐条人工看日志。

8. 如何判断是 SQL 问题还是锁问题？

对比 query_time 与 lock_time。lock_time 占比高说明在等锁，去查 INNODB_TRX 和 data_locks；query_time 高而 lock_time 低，才是 SQL 执行本身慢。

9. 统计信息不准会有什么表现？怎么处理？

EXPLAIN 的 rows 与实际相差数倍到数十倍，优化器可能选错索引或选错 JOIN 顺序。处理方式：ANALYZE TABLE 更新统计信息，检查 innodb_stats_persistent 相关配置，必要时调整 innodb_stats_persistent_sample_pages 提高采样精度。

10. 深分页怎么优化？

三种方案：游标分页（WHERE id > last_id，最优但只支持顺序翻页）、延迟关联（子查询先取主键再关联，支持跳页）、业务层限制最大页数。核心都是避免读出大量无用行。

十、总结

SQL 性能分析的本质，是用最小成本把"慢"归因到正确的一层。

1. 分层排查，不要跳级

实例 → 语句 → 执行。先排除锁、连接数、Buffer Pool 这类"不是 SQL 的问题"，再谈优化。

2. 按总延迟排序，不按单次耗时

SUM_TIMER_WAIT = 平均耗时 × 执行次数。高频小 SQL 的危害常被低估。

3. rows_examined / rows_sent 是核心指标

这个比值直接反映索引效率。优化 SQL 的过程，就是把它从几百、几千压到接近 1 的过程。

4. EXPLAIN 看计划，EXPLAIN ANALYZE 看事实

两者不一致时，优先怀疑统计信息。loops 要乘进耗时，这是最容易误判的地方。

5. 有索引不等于快，没索引不等于慢

索引是否被选中，取决于成本估算；而成本取决于数据分布、选择性和返回比例。理解这一点，就不会陷入"加了索引为什么还慢"的困惑。

最后记住一句话
性能分析的目标不是"让 EXPLAIN 好看"，而是让数据库为同样的结果读更少的行、做更少的排序、等更少的锁。

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/2301_78530027/article/details/170780578

SQL 性能分析：从慢查询日志到执行计划的完整定位方法

前言

一、性能分析的三个层级

二、实例级：先判断整体是否健康

2.1 负载与并发

2.2 Buffer Pool 命中率

2.3 临时表与排序

2.4 锁等待与长事务

三、语句级：找出"哪条 SQL 值得优化"

3.1 慢查询日志：最基础也最可靠的入口

3.2 performance_schema：按 SQL 指纹聚合

3.3 sys 库：现成的可读视图

3.4 分析正在执行的长 SQL

四、执行级：看清"这条 SQL 到底怎么跑的"

4.1 精读 EXPLAIN：三个必须一起看的字段

4.2 EXPLAIN 的三种格式

4.3 EXPLAIN ANALYZE：看实际执行

4.4 Handler 计数器：验证"到底读了多少行"

4.5 optimizer_trace：优化器为什么这么选

4.6 统计信息：估算不准的根源

4.7 关于 SHOW PROFILE

五、六类高频性能问题与优化套路

5.1 全表扫描与索引失效

5.2 回表过多

5.3 临时表与 filesort

5.4 深分页

5.5 隐式类型转换导致索引失效

5.6 锁等待与长事务

六、完整案例：一条 3 秒的列表查询

6.1 现象

6.2 定位过程

6.3 效果对比

6.4 一个必须注意的代价

七、性能分析 Checklist

八、五个常见误区

误区 1：只看 EXPLAIN 的 type，不看实际耗时

误区 2：认为 Using where 就是索引失效

误区 3：按单次耗时优化慢日志

误区 4：认为优化器选错索引是"bug"

误区 5：只看 SQL 不看总量

九、面试高频问题

1. 一条 SQL 很慢，你的排查思路是什么？

2. EXPLAIN 里你最关注哪些字段？

3. rows 和 filtered 是什么关系？

4. EXPLAIN 和 EXPLAIN ANALYZE 有什么区别？

5. 为什么 EXPLAIN 显示走了索引，实际还是慢？

6. 什么是覆盖索引？怎么判断用上了？

7. 慢查询日志里 SQL 太多，怎么高效分析？

8. 如何判断是 SQL 问题还是锁问题？

9. 统计信息不准会有什么表现？怎么处理？

10. 深分页怎么优化？

十、总结

评论

评论列表

微信小程序

QQ小程序

关于作者

误区 1：只看 `EXPLAIN` 的 `type`，不看实际耗时

误区 2：认为 `Using where` 就是索引失效