第7章：性能诊断与慢SQL分析（完整版）
🎯 本章目标
熟练使用慢查询日志、EXPLAIN、performance_schema 等工具
掌握分析 SQL 执行瓶颈的流程
学会识别并改写典型慢 SQL
一、慢查询日志简介
MySQL 提供慢查询日志机制，记录执行时间超过阈值的 SQL 语句。
🔧 开启方式
-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
-- 设置慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 1;
-- 是否记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 1;
📦 慢日志默认写入文件：/var/lib/mysql/hostname-slow.log
📌 线上分析第一步：先开启日志 → 再用工具分析
二、常用分析工具
| 工具 | 用途 |
|---|---|
| mysqldumpslow | 官方工具，分析慢日志出现频次 |
| pt-query-digest | 更强大的开源工具（推荐） |
| performance_schema | 内部视图，可实时追踪 SQL 执行耗时 |
✅ 使用 pt-query-digest 示例：
pt-query-digest /var/lib/mysql/mysql-slow.log
可输出：出现次数、平均执行时间、SQL 模板等
三、SQL 执行耗时构成
一条 SQL 的慢不仅可能是“索引没命中”，还有其他组件瓶颈。
📊 一条 SQL 耗时可能分为：
解析时间（parse）
优化器选择执行计划时间
存储引擎取数据时间
网络传输时间
客户端处理慢（分页导致过多数据传输）
📌 不能只看“SQL 写得对不对”，也要看上下文环境
四、EXPLAIN 联合分析慢 SQL
样例慢 SQL：
SELECT * FROM orders WHERE customer_id = 1 ORDER BY create_time DESC LIMIT 100000, 10;
EXPLAIN 可能提示：
type = index
rows = 100000
Extra = Using filesort
📌 原因：大 OFFSET 会扫描大量无效数据 + 排序成本高
✅ 改写方式：使用“游标式分页”
SELECT * FROM orders
WHERE customer_id = 1 AND id > 上一页最大ID
ORDER BY id ASC LIMIT 10;
五、performance_schema 实战
performance_schema是 MySQL 内置的性能分析系统
开启并使用：
-- 查看开启情况
SHOW VARIABLES LIKE 'performance_schema';
-- 查询 SQL 语句耗时统计
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC LIMIT 5;
📌 可用于分析“哪类语句最慢”、“哪些语句最频繁”
六、典型慢 SQL 改写案例
1️⃣ 模糊匹配慢
-- 慢：不能用索引
SELECT * FROM user WHERE name LIKE '%张';
-- 优：使用倒排索引或全文索引
ALTER TABLE user ADD FULLTEXT(name);
SELECT * FROM user WHERE MATCH(name) AGAINST('张');
2️⃣ or 条件未命中索引
-- 慢：
SELECT * FROM t WHERE a = 1 OR b = 2;
-- 优：
SELECT * FROM t WHERE a = 1
UNION
SELECT * FROM t WHERE b = 2;
3️⃣ 函数操作字段导致索引失效
-- 慢：
SELECT * FROM t WHERE DATE(create_time) = '2024-01-01';
-- 优：
SELECT * FROM t WHERE create_time BETWEEN '2024-01-01 00:00:00'
AND '2024-01-01 23:59:59';
七、实战练习题
🧪 练习1：开启慢日志并模拟慢 SQL
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0;
-- 执行一个大分页慢 SQL
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
✅ 自测题
慢查询日志记录什么内容？如何查看？
使用 pt-query-digest 可以分析出哪些指标？
OFFSET 越大，SQL 越慢的本质原因是什么？
如何用 performance_schema 排查数据库热点 SQL？
八、图解 SQL 慢的常见根因
[慢SQL] → 排查方向：
↳ 未命中索引？ → EXPLAIN
↳ 扫描行太多？ → rows 字段
↳ 排序文件大？ → Using filesort
↳ 网络传输慢？ → 客户端限制分页大小
↳ 后台阻塞严重？ → SHOW PROCESSLIST
↳ SQL 太多？ → performance_schema 分析 top SQL
📌 不要盲目改 SQL，要“基于证据”定位问题！
📣 下一章我们将进入 第8章：InnoDB 存储引擎架构深度解析，包括：
Buffer Pool 缓存机制
自适应哈希索引
页刷新与写入策略
InnoDB 的多线程架构设计