如何定位和优化慢 SQL
慢 SQL 是性能问题的头号元凶
数据库性能问题中，90% 的根源是慢 SQL，而不是数据库本身。学会定位和优化慢 SQL，是 DBA 和后端工程师的核心技能。
第 1 步：开启慢查询日志
配置
-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志（生产环境建议开启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的查询
使用 pt-query-digest 分析
# Percona 工具包分析慢查询
pt-query-digest /var/log/mysql/mysql-slow.log
# 输出包含：
# - 最耗时的 TOP SQL
# - 各 SQL 的执行次数、平均耗时、总耗时
# - 查询样本和表信息
第 2 步：使用 EXPLAIN 分析执行计划
拿到慢 SQL 后，用 EXPLAIN 分析其执行计划：
EXPLAIN SELECT u.name, o.order_no, o.amount
FROM user u
JOIN order o ON u.id = o.user_id
WHERE u.email = 'test@example.com'
ORDER BY o.create_time DESC
LIMIT 10;
EXPLAIN 关键字段解读
| 字段 | 关注点 | 好信号 | 坏信号 |
|---|---|---|---|
| type | 访问方式 | ref, eq_ref, const, system | ALL, index |
| key | 使用到的索引 | 存在 | NULL |
| rows | 预估扫描行数 | 小（< 1000） | 大（> 10000） |
| Extra | 额外信息 | Using index | Using filesort, Using temporary |
| key_len | 索引长度 | 匹配字段数 | 表示索引利用不充分 |
坏信号解读
type: ALL → 全表扫描，没有使用索引
Extra: Using temporary → 使用了临时表（通常因为 GROUP BY 或 ORDER BY 不同字段）
Extra: Using filesort → 排序无法使用索引
key: NULL → 没有可用索引
rows: 1000000 → 扫描了 100 万行
第 3 步：常见的慢 SQL 模式及优化
模式 1：全表扫描
-- 坏查询
SELECT * FROM user WHERE phone = '13800138000';
-- 如果 phone 上没有索引，将全表扫描
-- 优化：添加索引
ALTER TABLE user ADD INDEX idx_phone(phone);
模式 2：索引失效
-- 对索引列使用函数
SELECT * FROM user WHERE DATE(create_time) = '2024-01-01';
-- 优化：改成范围查询
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';
-- 隐式类型转换
SELECT * FROM user WHERE phone = 13800138000; -- phone 是字符串
-- 优化：使用正确类型
SELECT * FROM user WHERE phone = '13800138000';
模式 3：LIMIT 深分页
-- 坏查询（offset 很大时）
SELECT * FROM order ORDER BY id LIMIT 100000, 20;
-- 需要扫描 100020 行
-- 优化：游标分页
SELECT * FROM order WHERE id > 100000 ORDER BY id LIMIT 20;
模式 4：没有覆盖索引
-- 需要回表的查询
SELECT * FROM order WHERE status = 1 ORDER BY create_time DESC LIMIT 10;
-- 优化：使用覆盖索引
ALTER TABLE order ADD INDEX idx_status_time(status, create_time);
模式 5：大表 JOIN
-- 坏查询：两张千万级大表直接 JOIN
SELECT * FROM order o JOIN order_item oi ON o.id = oi.order_id
WHERE o.status = 1;
-- 优化：小表驱动大表，确保 JOIN 字段有索引
第 4 步：使用 Performance Schema
MySQL 5.7+ 有了性能更好的 Performance Schema：
-- 查询最耗时的 SQL（按总耗时排序）
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT,
AVG_TIMER_WAIT, SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;
-- 查询锁等待最多的 SQL
SELECT DIGEST_TEXT, COUNT_STAR,
SUM_LOCK_TIME, SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_LOCK_TIME DESC
LIMIT 10;
第 5 步：逐步优化方法论
定位慢 SQL（慢查询日志 / Performance Schema）
│
▼
EXPLAIN 分析执行计划
│
▼
诊断问题类型：全表扫描？索引失效？深分页？JOIN？
│
▼
选择优化方案
├── 索引优化：加索引 / 改索引 / 覆盖索引
├── SQL 改写：去函数 / 改 JOIN / 用 EXISTS
├── 架构调整：加缓存 / 读写分离 / 分库分表
│
▼
验证优化效果（对比前后的 EXPLAIN 和执行时间）
面试要点
- 定位慢 SQL 不是靠直觉，是靠工具（慢查询日志 + EXPLAIN + Performance Schema）
- 能熟练解读 EXPLAIN 中的 type、key、rows、Extra
- 能快速识别常见的慢 SQL 模式并给出优化方案
- 优化顺序：先改 SQL 和索引，再改架构
- 优化完成后一定要用 EXPLAIN 验证
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容