MySQL 的 explain 执行计划

EXPLAIN 基本概念

EXPLAIN 是 MySQL 提供的查询执行计划分析工具，它能够告诉我们 MySQL 是如何执行一条 SQL 语句的，帮助我们发现性能瓶颈并进行优化。

EXPLAIN 输出字段详解

完整字段结构图

核心字段详细解析

id - 执行顺序标识

-- 示例：复杂查询的执行顺序
EXPLAIN 
SELECT u.username, 
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count,
       p.product_name
FROM users u
JOIN (SELECT user_id, product_name FROM user_products WHERE status = 'active') p 
  ON u.id = p.user_id
WHERE u.status = 'active';

执行规律：

• id相同：从上到下顺序执行
• id不同：id值越大越先执行
• id为NULL：通常是UNION结果

select_type - 查询类型

实际示例：

-- SIMPLE：最好的情况
EXPLAIN SELECT * FROM users WHERE id = 1;

-- SUBQUERY：独立子查询，只执行一次
EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- DEPENDENT SUBQUERY：依赖外层查询，执行多次（性能较差）
EXPLAIN SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000);

type - 访问类型（性能关键指标）

各类型详解与示例：

-- const：通过主键或唯一索引查询单行（最快）
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const, rows: 1

-- eq_ref：唯一索引的关联查询
EXPLAIN SELECT u.*, p.* FROM users u JOIN profiles p ON u.id = p.user_id;
-- type: eq_ref（profiles表）

-- ref：非唯一索引查询
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- type: ref

-- range：范围查询
EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
-- type: range

-- index：全索引扫描
EXPLAIN SELECT id FROM users;
-- type: index（只需要扫描索引，不需要回表）

-- ALL：全表扫描（最慢，需要优化）
EXPLAIN SELECT * FROM users WHERE email LIKE '%gmail%';
-- type: ALL

key 和 possible_keys - 索引使用情况

实际案例分析：

-- 场景：电商订单查询
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    created_at DATETIME,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_user_status (user_id, status)
);

-- 查询：特定用户的已完成订单
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';

| possible_keys                              | key           | 解释
|-------------------------------------------|---------------|------------------
| idx_user_id,idx_status,idx_user_status    | idx_user_status| 选择复合索引最优

rows 和 filtered - 数据量评估

实际示例：

-- 示例：分析用户订单查询
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND total > 1000;

| rows  | filtered | 计算过程
|-------|----------|-------------------------
| 1000  | 25.00    | 扫描1000行，25%符合条件
|       |          | 最终处理：1000 × 25% = 250行

Extra - 额外信息（优化关键提示）

各种Extra详解：

-- Using index：覆盖索引（最佳性能）
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 123;
-- Extra: Using index（idx_user_status 覆盖了所有需要的字段）

-- Using filesort：需要排序（注意性能）
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY total;
-- Extra: Using filesort（total字段没有索引）

-- 优化方案：创建复合索引
CREATE INDEX idx_user_total ON orders(user_id, total);

-- Using temporary：使用临时表（性能较差）
EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id ORDER BY COUNT(*);
-- Extra: Using temporary; Using filesort

-- 优化方案：添加适当索引
CREATE INDEX idx_user_id_sorted ON orders(user_id);

完整的 EXPLAIN 分析流程

实战案例分析

案例1：电商网站商品搜索优化

原始查询：

-- 用户搜索：价格100-500元的手机，按销量排序
EXPLAIN SELECT * FROM products 
WHERE category = '手机' 
  AND price BETWEEN 100 AND 500 
ORDER BY sales_count DESC 
LIMIT 20;

原始执行计划：

| id | type | key  | rows  | Extra                    |
|----|------|------|-------|--------------------------|
| 1  | ALL  | NULL | 50000 | Using where; Using filesort |

问题分析：

• type: ALL - 全表扫描，性能很差
• key: NULL - 没有使用任何索引
• rows: 50000 - 扫描了5万行数据
• Extra: Using filesort - 需要额外的排序操作

优化方案：

-- 创建复合索引
CREATE INDEX idx_category_price_sales ON products(category, price, sales_count DESC);

-- 优化后的执行计划

| id | type  | key                    | rows | Extra       |
|----|-------|------------------------|------|-------------|
| 1  | range | idx_category_price_sales| 500  | Using index |

优化效果：

• 扫描行数从50000降到500（提升100倍）
• 使用了覆盖索引，避免回表查询
• 索引本身有序，避免了filesort

案例2：复杂关联查询优化

业务场景：查询活跃用户最近的订单信息

EXPLAIN SELECT u.username, u.email, o.order_no, o.total
FROM users u
JOIN orders o ON u.id = o.user_id  
WHERE u.last_login > '2024-01-01'
  AND o.status = 'completed'
  AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 10;

执行计划分析：

| id | table | type | key           | rows  | Extra                    |
|----|-------|------|---------------|-------|--------------------------|
| 1  | u     | ALL  | NULL          | 10000 | Using where; Using temporary |
| 1  | o     | ref  | idx_user_id   | 5     | Using where              |

优化策略：

-- 1. 为 users 表创建索引
CREATE INDEX idx_last_login ON users(last_login);

-- 2. 为 orders 表创建复合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 3. 如果经常需要 username, email，考虑覆盖索引
CREATE INDEX idx_last_login_covering ON users(last_login, id, username, email);

性能优化检查清单

进阶：EXPLAIN FORMAT=JSON

对于复杂查询，JSON格式提供更详细的信息：

EXPLAIN FORMAT=JSON 
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.total > 1000;

JSON输出包含更多细节：

• cost_info：查询成本估算
• used_columns：使用的具体列
• attached_condition：具体的过滤条件

总结

EXPLAIN 是 MySQL 性能优化的核心工具，关键是要关注：

1. type 字段：避免 ALL 和 index 类型
2. key 字段：确保使用了合适的索引
3. rows 字段：控制扫描行数在合理范围
4. Extra 字段：注意性能警告信息

记住：好的执行计划应该是 type=const/eq_ref/ref，key有值，rows较少，Extra信息积极正面。

通过系统性地分析这些字段，你就能快速定位查询性能瓶颈并制定针对性的优化方案！