MySQL：15倍性能差距原因排查

“同一 SQL 在平台上比虚拟机慢 15 倍”来写一份能直接落地的排查+证明方案。目标是两件事：
1）快速定位主要耗时段（是计划差、I/O、临时表落盘、还是锁等待）；
2）产出可对外汇报的量化证据（“为何 15 倍”“改了什么、效果如何”）。

---

一、5 步快诊：30–60 分钟内定位“15×差距”的主因

1) 固定语义 & 展开视图（排除“看起来一样，实际不一样”）

-- 两边都执行
SHOW CREATE VIEW your_view\G  -- 记下 ALGORITHM（MERGE / TEMPTABLE）
-- 用视图定义把原语句“展开”为对基表的等价 SQL（不要留*，只选必要列）
EXPLAIN FORMAT=JSON <展开后的SQL>\G

判读要点

• ALGORITHM=TEMPTABLE 往往会物化，丢索引→临时表/排序易落盘，典型致慢。
• 展开后更容易看出是否有函数包裹/隐式类型转换导致“非 SARGable”。

2) 看真实执行路径（锁/临时表/排序/回表到底慢在哪）

-- 8.0.18+ 推荐：可看到每阶段实际行数/耗时
EXPLAIN ANALYZE <展开后的SQL>;

判读要点

• 哪个 step 耗时占比最高？常见：Iterator: Sort, Nested loop join, Temp table write/read。
• “预估行数 vs 实际行数”偏差是否极大（统计信息问题/选错索引）。

3) 抓运行期指标（一次执行前后各取一份）

-- 会话级：只影响当前连接
SHOW SESSION STATUS LIKE 'Created_tmp%';
SHOW SESSION STATUS LIKE 'Sort_merge_passes';
SHOW SESSION STATUS LIKE 'Handler_read%';
-- 实例级（对比缓冲命中/随机读）
SHOW GLOBAL  STATUS LIKE 'Innodb_buffer_pool_reads';

怎么用来“证明 15×”？

• Created_tmp_disk_tables 在平台侧显著增加 → 临时表落盘。
• Sort_merge_passes > 0 或增长多 → 排序溢出。
• Innodb_buffer_pool_reads 跑一次 SQL 就增长明显 → buffer pool 太小 / 冷缓存 / IOPS受限。
• Handler_read_next 爆高 → 扫描/回表多（索引设计或计划问题）。

4) 比系统参数（决定“是否落盘/是否能进缓存”的关键）

SELECT
@@version, @@version_comment,
@@optimizer_switch,
@@sql_mode, @@transaction_isolation,
@@innodb_buffer_pool_size, @@tmp_table_size, @@max_heap_table_size,
@@join_buffer_size, @@sort_buffer_size,
@@innodb_flush_log_at_trx_commit, @@sync_binlog;

判读要点

• tmp_table_size / max_heap_table_size 小 → 更易落盘。
• innodb_buffer_pool_size 小 → 更多磁盘读。
• optimizer_switch 有差异（如 derived_merge, hash_join, condition_pushdown_for_derived）→ 计划不同。
• transaction_isolation 更严格 + 长事务 → 一致性读跨度大。

5) 锁/长事务（80 秒可能在等锁）

SELECT * FROM performance_schema.data_locks\G;
SELECT * FROM information_schema.innodb_trx\G;

证据口径：如果 80 秒里大部分是 LOCK_WAIT，那是并发/事务问题，不是查询本身算慢。

---

二、典型“15×差距”根因 → 快速验证动作（带口径）

症状（可量化）	高概率根因	快速验证	话术/口径（对外）
ALGORITHM=TEMPTABLE 或 EXPLAIN 显示 using_tmp_table; using_filesort，Created_tmp_disk_tables 上升明显	视图物化 + 临时表落盘	改为 ALGORITHM=MERGE 或改写成可合并子查询；临时调大 tmp_table_size/max_heap_table_size 到 512MB 再测	“平台侧发生临时表落盘，I/O 放大致整体慢 ~15×；扩大内存并改写/合并后恢复到 ~7–10s”
EXPLAIN ANALYZE 显示排序/哈希阶段耗时集中；Sort_merge_passes>0	排序溢出/多次归并	临时将 sort_buffer_size 调大、或让 ORDER BY 复用联合索引	“排序溢出触发多次归并；通过索引/缓冲优化，排序阶段从 60s → 4s”
Innodb_buffer_pool_reads 单次执行暴涨，平台 @@innodb_buffer_pool_size 明显偏小或存储 IOPS 限制	缓存不足 + 慢 I/O	暂时放大 buffer pool 或改到本地 NVMe/更高配盘上复测	“缓存与 IOPS 限额导致页读放大，CPU 空转等 I/O；扩大内存或放宽 IOPS 后与虚机一致”
EXPLAIN 选了次优索引/全表扫；FORCE INDEX 立刻变快	统计信息陈旧/索引选择错误	ANALYZE TABLE 后复测；或临时 FORCE INDEX/STRAIGHT_JOIN 证明	“统计信息偏差使优化器选错路径；更新统计或固定索引后恢复”
data_locks/innodb_trx 可见等待链，EXPLAIN ANALYZE 显示大段 wait	锁等待/长事务	在业务低峰或加 READ COMMITTED 重跑对比	“锁冲突而非算力问题；调整隔离级或拆交易范围后恢复”
WHERE/JOIN 列被函数包裹或发生隐式转换；attached_condition 中有 type_conv	非 SARGable / 隐式转换	改写谓词使可走索引；统一字段类型/字符集	“隐式转换导致索引失效；改写后 I/O 降两量级”
不同 optimizer_switch（如 derived_merge=off）	计划行为差异	与虚机对齐 optimizer_switch，或升级到一致小版本	“优化器开关差异触发物化/次优 join 顺序；参数对齐后恢复”

---

三、详细排查脚本（复制即用）

1) 视图/SQL 定影

SHOW CREATE VIEW your_view\G;
-- 记录 view 的 ALGORITHM 与定义

-- 把最终执行 SQL（展开视图、显式列名）固化在一个文件里，后续所有对比都用它
EXPLAIN FORMAT=JSON <final_sql>\G;
EXPLAIN ANALYZE      <final_sql>;

2) 运行期指标采样（一次查询前后）

-- BEFORE
SHOW SESSION STATUS LIKE 'Created_tmp%';
SHOW SESSION STATUS LIKE 'Sort_merge_passes';
SHOW SESSION STATUS LIKE 'Handler_read%';
SHOW GLOBAL  STATUS LIKE 'Innodb_buffer_pool_reads';

-- 执行 SQL
SET profiling=1; -- 8.0 移除了 profiling，可忽略这句
<final_sql>;

-- AFTER
SHOW SESSION STATUS LIKE 'Created_tmp%';
SHOW SESSION STATUS LIKE 'Sort_merge_passes';
SHOW SESSION STATUS LIKE 'Handler_read%';
SHOW GLOBAL  STATUS LIKE 'Innodb_buffer_pool_reads';

把前后差值贴到表里，就是你汇报里的“证据 A、B、C”。

3) 统计信息与索引试验

ANALYZE TABLE t1, t2, ...;            -- 先刷新统计
EXPLAIN ANALYZE <final_sql>;          -- 观察计划是否回正

-- 如果怀疑索引选错，快速验证：
SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */ ...
-- 或者
SELECT /*+ NO_RANGE_OPTIMIZATION(t1) */ ...
-- 或者直接
SELECT /* test */ ... FROM t1 FORCE INDEX(ix_needed) ...;

4) 临时表/排序溢出验证

-- 会话级放大，验证是否因阈值导致落盘
SET SESSION tmp_table_size=536870912;      -- 512MB
SET SESSION max_heap_table_size=536870912; -- 512MB
SET SESSION sort_buffer_size=16777216;     -- 16MB 视场景
EXPLAIN ANALYZE <final_sql>;

5) 锁与长事务

SELECT trx_id, trx_started, trx_state, trx_tables_locked
FROM information_schema.innodb_trx\G;

SELECT *
FROM performance_schema.data_locks
ORDER BY THREAD_ID,LOCK_MODE\G;

6) Optimizer Trace（当计划反复选错）

SET optimizer_trace='enabled=on';
SET optimizer_trace_max_mem_size=10000000;
<final_sql>;
SELECT * FROM information_schema.optimizer_trace\G;

看 chosen_access_method / considered_execution_plans 为何放弃好索引或不合并派生表。

---

四、视图导致的“隐性落盘/去索引”详解（最常见 15× 根因）

1. 视图内含 DISTINCT/GROUP BY/ORDER BY/LIMIT 或 ALGORITHM=TEMPTABLE → 引擎可能先把视图结果物化到临时表，索引全部丢失；
2. 外层再对临时表过滤/排序 → 临时表二次落盘 + filesort；
3. 平台侧内存阈值更小或 IOPS 受限，放大倍数就出来了。
   对策优先级：

• 能合并就合并：将视图改成 ALGORITHM=MERGE（或把计算拆到 CTE/子查询，确保 SARGable，能把条件下推到基表）；
• 为 JOIN + GROUP/ORDER 设计复合索引，尽量让排序“用上索引顺序”；
• 把常用聚合做成增量汇总表（任务/触发器维护），查询走直取。

---

五、对外“汇报口径”模板（直接拿去写 PPT）

标题：同一业务 SQL 在平台侧较虚机侧延迟高 15×的原因与优化结果
时间：2025-10-20（示例）

复现场景

• 语句：<final_sql 摘要>（视图已展开，保证语义一致）
• 虚机：7s；平台：80s（平均 3 次）

关键证据

1. SHOW CREATE VIEW：平台视图 ALGORITHM=TEMPTABLE；

2. EXPLAIN ANALYZE：平台在 Sort 与 Temp table 阶段耗时占比 86%（虚机为 12%）；

3. 运行期指标差值：

   • Created_tmp_disk_tables：平台 +3（虚机 +0）
   • Sort_merge_passes：平台 +2（虚机 +0）
   • Innodb_buffer_pool_reads：平台 +18,000（虚机 +1,200）

4. 参数差异：平台 tmp_table_size=64MB / max_heap_table_size=64MB，虚机 512MB；平台 innodb_buffer_pool_size 仅为数据量的 0.5×。

根因判定

• 视图被物化 + 平台内存阈值/IOPS 限制 → 临时表落盘 + 多次归并排序 → 端到端 15×。

优化动作（一周内完成）

• 改写视图为可合并子查询（或显式 ALGORITHM=MERGE）；
• 为 X(a,b)、Y(b,c) 增加复合索引，复用 ORDER BY 顺序；
• 会话/实例将 tmp_table_size/max_heap_table_size 调整至 512MB；
• ANALYZE TABLE 刷新统计，将 optimizer_switch.derived_merge=on 与虚机对齐。

复测结果

• 平台 80s → 8.9s（P95 9.4s），与虚机同量级；
• 指标归零：Sort_merge_passes=0、Created_tmp_disk_tables=0。

后续建议

• 将复杂分析从视图迁移为日级汇总表；
• 统一参数基线（buffer pool ≥ 数据热集 1.5×；tmp table ≥ 256–512MB）；
• 上线前压测并固化 EXPLAIN ANALYZE 基线。

---

六、你可以直接照抄的“排查顺序清单”

1. SHOW CREATE VIEW：确认 ALGORITHM 与定义；
2. 固化展开后 SQL，两边 EXPLAIN FORMAT=JSON 对比；
3. 平台上跑 EXPLAIN ANALYZE，记下最慢阶段；
4. 采样 SESSION STATUS 与 GLOBAL STATUS 前后差值（tmp/sort/handler/buffer_pool_reads）；
5. 比对关键 @@variables（tmp_table_size、buffer pool、optimizer_switch、隔离级、版本）；
6. ANALYZE TABLE → 再跑；必要时 FORCE INDEX/STRAIGHT_JOIN 试验；
7. 会话放大 tmp_table_size/max_heap_table_size → 观察落盘是否消失；
8. 若仍慢：去视图化（MERGE/子查询/CTE）、补复合索引；
9. 若卡在 wait：查锁/长事务，必要时降隔离级或缩窄事务范围；
10. 输出证据表格 + 优化前后对比图作为结案材料。

---