MySQL EXPLAIN 执行计划详解：从入门到精通的 SQL 性能分析指南

EXPLAIN SELECT * FROM users WHERE id = 1;

-- 传统表格格式（默认）
EXPLAIN SELECT * FROM users;

-- JSON 格式，信息更详细
EXPLAIN FORMAT=JSON SELECT * FROM users;

-- 树形格式，展示执行顺序
EXPLAIN FORMAT=TREE SELECT * FROM users;

-- 实际执行并显示运行时统计（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users;

-- 示例：子查询
EXPLAIN
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- SIMPLE 示例
EXPLAIN SELECT * FROM users WHERE id = 1;

-- SUBQUERY 示例
EXPLAIN SELECT * FROM orders
WHERE user_id = (SELECT id FROM users WHERE name = 'test');

-- DERIVED 示例
EXPLAIN SELECT * FROM (SELECT * FROM users WHERE status = 1) AS t;

system > const > eq_ref > ref > fulltext > ref_or_null >
index_merge > unique_subquery > index_subquery > range > index > ALL

-- const：主键等值查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const

-- ref：普通索引查询
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- type: ref（假设 email 有普通索引）

-- range：范围查询
EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01';
-- type: range（假设 created_at 有索引）

-- ALL：全表扫描
EXPLAIN SELECT * FROM users WHERE name LIKE '%test%';
-- type: ALL（前缀模糊查询无法使用索引）

-- 假设有复合索引 idx_name_age (name, age)
-- name VARCHAR(50), age INT

EXPLAIN SELECT * FROM users WHERE name = 'test';
-- key_len: 152 (50*3+2，VARCHAR 使用 3 倍字符长度 + 2)

EXPLAIN SELECT * FROM users WHERE name = 'test' AND age = 25;
-- key_len: 157 (152+4+1，额外使用了 INT 的 4 字节，NULL 标志 1 字节)

-- 如果 rows = 1000, filtered = 10.00
-- 预计最终返回约 100 行

-- Using index（覆盖索引）
EXPLAIN SELECT id, name FROM users WHERE name = 'test';
-- 假设有索引 idx_name(name)，且只查询 id 和 name

-- Using filesort
EXPLAIN SELECT * FROM orders ORDER BY amount;
-- 假设 amount 列没有索引

-- Using temporary; Using filesort
EXPLAIN SELECT department, COUNT(*) FROM employees
GROUP BY department ORDER BY COUNT(*) DESC;

-- 问题 SQL
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- type: ALL, rows: 100000

-- 添加索引后
ALTER TABLE orders ADD INDEX idx_status(status);

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- type: ref, rows: 500

-- 问题 SQL
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at;
-- Extra: Using filesort

-- 创建复合索引
ALTER TABLE orders ADD INDEX idx_user_created(user_id, created_at);

EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at;
-- Extra: Using index condition

-- 原 SQL（需要回表）
EXPLAIN SELECT id, name, email FROM users WHERE name = 'test';
-- Extra: NULL

-- 创建覆盖索引
ALTER TABLE users ADD INDEX idx_name_email(name, email);

EXPLAIN SELECT id, name, email FROM users WHERE name = 'test';
-- Extra: Using index

EXPLAIN
SELECT o.id, c.name, c.email
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2024-01-01';

EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3);
SHOW WARNINGS;

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1\G

EXPLAIN ANALYZE SELECT * FROM users WHERE status = 1;