【MySQL深入详解】第20篇：慢查询分析——从EXPLAIN开始优化开篇引入 "这个查询太慢了！" 优化查询的第一步是

开篇引入

"这个查询太慢了！"

优化查询的第一步是什么？不是加索引，不是改配置，而是先搞清楚它为什么慢。

EXPLAIN是MySQL内置的查询分析工具，它告诉你MySQL打算怎么执行这个查询。从执行计划里，你能看出查询有没有走索引、扫描了多少行、要不要排序。

《高性能MySQL》第8章开篇就讲慢查询分析，这篇文章帮你把EXPLAIN用透。

EXPLAIN basics

基本用法

-- 分析一个查询
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- MySQL 8.0+: 详细输出
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;
-- 会显示实际执行时间

EXPLAIN输出列

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

列名	含义
id	查询中SELECT的序号
select_type	SELECT类型
table	涉及的表
partitions	匹配的分区
type	访问类型
possible_keys	可用的索引
key	实际使用的索引
key_len	索引长度
ref	与索引比较的列
rows	预计扫描的行数
filtered	过滤后剩余的百分比
Extra	额外信息

type列：访问类型详解

从差到好的排序：

-- ALL: 全表扫描（最差）
EXPLAIN SELECT * FROM orders;  -- 无WHERE条件
-- type: ALL

-- index: 全索引扫描
EXPLAIN SELECT id FROM orders;  -- 只查主键
-- type: index

-- range: 索引范围扫描
EXPLAIN SELECT * FROM orders WHERE id > 100;
-- type: range

-- ref: 索引等值查询
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- type: ref

-- eq_ref: 联接时使用主键或唯一索引
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- type: eq_ref

-- const: 常量引用（主键或唯一索引）
EXPLAIN SELECT * FROM orders WHERE id = 1;
-- type: const

理想的查询应该是ref或更好的类型。

Extra列：关键信息

Extra列告诉你MySQL做了什么额外操作：

Using index（覆盖索引）

-- 不用回表，直接从索引返回数据
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 1;
-- Extra: Using index

Using where

-- 需要在服务器层额外过滤
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- Extra: Using where

Using index condition

-- 使用了索引条件下推（ICP）
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status LIKE '%paid%';
-- Extra: Using index condition

Using MRR（Multi-Range Read）

-- 使用了优化：减少随机IO
EXPLAIN SELECT * FROM orders WHERE user_id IN (1, 2, 3);
-- Extra: Using MRR

Using filesort（需要排序）

-- 无法使用索引排序，需要文件排序
EXPLAIN SELECT * FROM orders ORDER BY created_at DESC;
-- Extra: Using filesort

Using temporary（使用临时表）

-- 需要创建临时表存储中间结果
EXPLAIN SELECT DISTINCT status FROM orders;
-- Extra: Using temporary

Using index for skip scan

-- 跳过索引的前导列
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- 假设索引是(user_id, status)
-- Extra: Using index for skip scan

常见慢查询分析

案例1：全表扫描

-- 问题查询
EXPLAIN SELECT * FROM orders WHERE status = 'paid';

-- 结果分析：
-- type: ALL  ← 全表扫描
-- rows: 100000  ← 扫描10万行
-- key: NULL  ← 没有用索引

-- 解决方案：添加索引
CREATE INDEX idx_status ON orders(status);

案例2：没有利用索引

-- 问题查询
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- 结果分析：
-- type: ALL  ← 全表扫描
-- 原因：索引列参与了函数运算

-- 解决方案：改写查询
EXPLAIN SELECT * FROM orders 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';

案例3：大偏移量分页

-- 问题查询
EXPLAIN SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 10;

-- 结果分析：
-- type: ALL 或 index
-- rows: 100010  ← 还是要扫描10万+行
-- Extra: Using filesort

-- 解决方案1：记录上次ID
SELECT * FROM orders WHERE id < 100000 ORDER BY id DESC LIMIT 10;

-- 解决方案2：使用覆盖索引
SELECT o.* FROM orders o 
JOIN (SELECT id FROM orders ORDER BY id DESC LIMIT 100000, 10) t 
ON o.id = t.id;

案例4：JOIN顺序不当

-- 问题查询
EXPLAIN SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id 
JOIN products p ON o.product_id = p.id;

-- 结果分析：
-- 先扫描products（10万行）
-- 再匹配orders
-- ...

-- 解决方案：调整JOIN顺序
EXPLAIN SELECT * FROM orders o 
STRAIGHT_JOIN users u ON o.user_id = u.id 
STRAIGHT_JOIN products p ON o.product_id = p.id;
-- 告诉MySQL按写的顺序执行

EXPLAIN ANALYZE（MySQL 8.0+）

EXPLAIN ANALYZE
SELECT o.*, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 10;

-- 输出示例：
-> Limit: 10 row(s)  (cost=1234.56 rows=10) (actual time=0.05..0.08 rows=10 loops=1)
    -> Nested loop inner join  (cost=1234.56 rows=1000) (actual time=0.04..0.07 rows=50 loops=1)
        -> Index lookup on o using idx_status (status='paid')  (cost=500.00 rows=1000) (actual time=0.02..0.05 rows=50 loops=1)
        -> Index lookup on u using PRIMARY (id=o.user_id)  (cost=0.25 rows=1) (actual time=0.001..0.002 rows=1 loops=50)

关键信息：

cost: 优化器估算的成本
actual time: 实际执行时间（first row..last row）
rows: 实际返回行数
loops: 该步骤执行次数

分步分析复杂查询

分解子查询

-- 原始查询
EXPLAIN SELECT * FROM orders 
WHERE user_id IN (
    SELECT id FROM users WHERE status = 1
);

-- 分步分析
EXPLAIN SELECT id FROM users WHERE status = 1;
-- 先看子查询用没用索引

EXPLAIN SELECT * FROM orders WHERE user_id IN (1, 2, 3);
-- 再看主查询

分析联接查询

EXPLAIN SELECT o.*, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2024-01-01';

-- 查看执行顺序（id大的先执行）
-- id=1: users表（驱动表）
-- id=2: orders表（被驱动表）

慢查询日志

开启慢查询日志

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启
SET GLOBAL slow_query_log = 'ON';

-- 设置阈值（秒）
SET GLOBAL long_query_time = 2;

-- 设置日志文件
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

my.cnf配置

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

分析慢查询日志

# 使用mysqldumpslow
mysqldumpslow -s t /var/log/mysql/slow.log

# 使用pt-query-digest（Percona Toolkit）
pt-query-digest /var/log/mysql/slow.log

优化检查清单

-- 检查EXPLAIN输出：

-- 1. type列
-- ✗ ALL（全表扫描）→ 需要添加索引
-- ✓ range以上 → 良好

-- 2. key列
-- NULL → 没有使用索引 → 检查possible_keys
-- 有值 → 使用了某个索引

-- 3. rows列
-- 太大（成千上万）→ 可能需要更好的索引
-- 很小（几十几百）→ 良好

-- 4. Extra列
-- Using filesort → 考虑添加排序列到索引
-- Using temporary → 考虑改写查询或用覆盖索引
-- Using where → 正常
-- Using index → 覆盖索引，最佳

常见优化模式

模式1：用索引覆盖避免回表

-- 原始查询（需要回表）
EXPLAIN SELECT name, email FROM users WHERE name = 'Alice';
-- 需要查name索引，再回表查users主键

-- 优化：添加覆盖索引
CREATE INDEX idx_name_covering ON users(name, email);
-- 索引包含所有查询列，不需要回表

模式2：避免排序

-- 原始查询（需要filesort）
EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Extra: Using filesort

-- 优化：使用索引排序
-- 创建以created_at DESC为顺序的索引
ALTER TABLE orders ADD INDEX idx_created (created_at DESC);
-- 查询可以直接从索引读取已排序的数据

模式3：减少扫描行数

-- 原始查询
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- rows: 50000

-- 优化1：添加过滤条件
EXPLAIN SELECT * FROM orders WHERE status = 1 AND user_id = 1;
-- rows: 500

-- 优化2：使用覆盖索引
CREATE INDEX idx_status_user ON orders(status, user_id, created_at);

小结

EXPLAIN是优化起点：先分析执行计划
type列看访问类型：ALL最差，ref/eq_ref/const最佳
Extra列是关键：Using filesort/temporary都是问题信号
rows列反映扫描量：越大说明效率越低
EXPLAIN ANALYZE：MySQL 8.0+显示实际执行时间
慢查询日志：记录真实的慢查询
覆盖索引：减少回表，提升性能
分步分析：复杂查询拆开看每个部分

EXPLAIN分析是MySQL优化的基本功。优化之前，先搞清楚MySQL在做什么。

延伸阅读

《高性能MySQL》第8章查询性能优化
MySQL 8.0 Reference Manual: EXPLAIN Statement
MySQL EXPLAIN Output Format: dev.mysql.com/doc/refman/…