当前位置：首页 > news >正文

《MySQL 技术内幕（第5版）》逐章精华笔记第七章

news 2025/6/26 8:28:36

第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 存储引擎架构深度解析，包括：