MySQLEXPLAIN关键参数实战举例

一、前言

1.1 什么是EXPLAIN

EXPLAIN是MySQL提供的SQL执行计划分析命令，用于展示MySQL优化器如何执行SQL语句。通过EXPLAIN可以分析索引使用情况、表连接顺序、扫描行数等关键信息，是SQL性能优化的核心工具。

1.2 基础知识要求

二、EXPLAIN的使用方法

2.1 基本语法

-- 分析SELECT语句 EXPLAIN SELECT * FROM users WHERE id = 1; -- 分析DELETE/UPDATE/INSERT（MySQL 5.6+） EXPLAIN DELETE FROM users WHERE status = 'inactive'; -- 显示更详细信息（MySQL 8.0.18+） EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1; -- 实际执行并显示耗时 -- 输出JSON格式（便于程序解析） EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1; -- 查看连接中正在执行的SQL的执行计划 EXPLAIN FOR CONNECTION 123; -- 123为connection_id

2.2 EXPLAIN输出字段概览

EXPLAIN SELECT o.order_id, u.user_name FROM orders o LEFT JOIN users u ON o.user_id = u.user_id WHERE o.order_status = 'pending' AND o.created_at >= '2024-01-01' LIMIT 10\G

输出结果示例：

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: o partitions: NULL type: range possible_keys: idx_status_created,idx_created_at key: idx_status_created key_len: 102 ref: NULL rows: 185000 filtered: 100.00 Extra: Using index condition *************************** 2. row *************************** id: 1 select_type: SIMPLE table: u partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: test.o.user_id rows: 1 filtered: 100.00 Extra: NULL

三、核心参数详解

3.1 id：查询标识符

示例：

-- id相同：多表JOIN EXPLAIN SELECT * FROM orders o, users u WHERE o.user_id = u.user_id; -- 结果：两个id均为1 -- id不同：子查询 EXPLAIN SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders); -- 结果：子查询id=2，外层查询id=1（子查询先执行）

3.2 select_type：查询类型

重点关注：

-- 避免DEPENDENT SUBQUERY（子查询依赖外层） EXPLAIN SELECT * FROM users u WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) > 5; -- 优化：改为JOIN + GROUP BY -- 注意DERIVED临时表开销 EXPLAIN SELECT * FROM (SELECT * FROM orders WHERE status='pending') t; -- 建议：直接查询，或创建视图

3.3 type：访问类型（最重要指标之一）

性能从优到劣排序：

system > const > eq_ref > ref > range > index > ALL 类型说明示例优化目标system系统表，只有一行数据极少出现✅ 最优const主键或唯一索引等值查询，最多返回一行WHERE id = 1✅ 理想eq_ref连接查询时，被驱动表使用主键或唯一索引JOIN + 主键关联✅ 优秀ref使用非唯一索引等值查询WHERE status = 'pending'✅ 良好range索引范围扫描WHERE id > 100、BETWEEN、IN✅ 可接受index全索引扫描（扫描整个索引树）覆盖索引但无WHERE条件⚠️ 需优化ALL全表扫描无索引或索引失效❌ 必须优化

实战对比：

-- const：最优 EXPLAIN SELECT * FROM users WHERE user_id = 1; -- type=const -- ref：良好 EXPLAIN SELECT * FROM orders WHERE order_status = 'pending'; -- type=ref（假设status有索引） -- range：可接受 EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01'; -- type=range（假设created_at有索引） -- ALL：必须优化 EXPLAIN SELECT * FROM orders WHERE order_status = 'pending'; -- type=ALL（status无索引）

3.4 possible_keys：可能使用的索引

注意：possible_keys有值不代表实际使用，需结合key字段判断。

3.5 key：实际使用的索引

关键场景：

-- possible_keys有值但key为NULL：索引未被使用 EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01'; -- possible_keys: idx_created_at, key: NULL -- 原因：对索引字段使用了函数，索引失效 -- 实际使用了联合索引 EXPLAIN SELECT * FROM orders WHERE order_status = 'pending' AND created_at > '2024-01-01'; -- key: idx_status_created（联合索引）

3.6 key_len：使用的索引字节长度

作用：

计算规则（以InnoDB为例）：

实战分析：

-- 联合索引：idx_status_created (order_status VARCHAR(20), created_at DATETIME) SHOW INDEX FROM orders; -- 查看索引定义 EXPLAIN SELECT * FROM orders WHERE order_status = 'pending' AND created_at >= '2024-01-01'; -- key_len = 83 -- 计算：order_status(20*3+2=62) + created_at(8) + NULL标志(1) = 71? 实际83包含额外开销 -- 如果只使用第一个字段 EXPLAIN SELECT * FROM orders WHERE order_status = 'pending'; -- key_len = 62（仅使用了order_status字段）

3.7 ref：索引列的比较对象

示例：

EXPLAIN SELECT * FROM orders o, users u WHERE o.user_id = u.user_id AND o.order_status = 'pending'; -- table=o 的 ref: const (order_status='pending') -- table=u 的 ref: test.o.user_id (关联到orders表的user_id)

3.8 rows：预估扫描行数

含义：MySQL优化器预估需要读取的行数（非精确值）

重要性：

实战：

-- 全表扫描：rows ≈ 表总行数 EXPLAIN SELECT * FROM orders WHERE order_status = 'pending'; -- rows: 5,000,000（全表） -- 添加索引后：rows大幅下降 EXPLAIN SELECT * FROM orders WHERE order_status = 'pending'; -- rows: 250,000（索引过滤后）

3.9 filtered：过滤后剩余行数百分比

含义：满足WHERE条件的行数占rows的预估百分比

计算：实际返回行数 ≈ rows × filtered%

重要性：

示例：

EXPLAIN SELECT * FROM orders WHERE order_status = 'pending' AND user_id = 100; -- rows: 250000, filtered: 1.00 -- 实际返回行数 ≈ 2500（过滤掉了99%的数据） -- 优化：创建(status, user_id)联合索引 -- 优化后 rows: 50, filtered: 100.00

3.10 Extra：额外信息（重要优化线索）

重点优化场景：

场景1：Using filesort

-- 问题SQL EXPLAIN SELECT * FROM orders WHERE status='pending' ORDER BY created_at DESC; -- Extra: Using where; Using filesort -- 优化：添加(status, created_at)联合索引 CREATE INDEX idx_status_created ON orders(status, created_at); -- 优化后Extra不再出现filesort

场景2：Using temporary

-- 问题SQL EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*); -- Extra: Using temporary; Using filesort -- 优化：拆分为两个查询，或调整GROUP BY/ORDER BY顺序

场景3：Using index（覆盖索引）

-- 覆盖索引：查询字段都在索引中 EXPLAIN SELECT order_id, user_id FROM orders WHERE order_id = 100; -- Extra: Using index（主键索引覆盖） -- 创建覆盖索引示例 CREATE INDEX idx_cover ON orders(status, created_at, order_id); EXPLAIN SELECT status, created_at, order_id FROM orders WHERE status='pending'; -- Extra: Using index（无需回表）

四、实战分析流程

4.1 标准分析步骤

1. 执行EXPLAIN，获取执行计划 ↓ 2. 检查type：是否为ALL或index？ ↓ 是 → 添加索引 ↓ 否 3. 检查key：是否为NULL？ ↓ 是 → 分析索引失效原因 ↓ 否 4. 检查rows：是否过大？ ↓ 是 → 优化索引选择性 ↓ 否 5. 检查Extra：是否有Using filesort/Using temporary？ ↓ 是 → 优化排序/分组索引 ↓ 否 6. 性能良好

4.2 综合案例分析

案例：订单报表查询慢

EXPLAIN SELECT DATE(o.created_at) as order_date, u.user_level, COUNT(*) as order_count, SUM(o.order_amount) as total_amount FROM orders o LEFT JOIN users u ON o.user_id = u.user_id WHERE o.created_at >= '2024-01-01' AND o.created_at < '2024-02-01' AND o.order_status IN ('paid', 'shipped') GROUP BY DATE(o.created_at), u.user_level ORDER BY order_date DESC, u.user_level;

EXPLAIN结果：

问题诊断：

优化方案：

-- 1. 创建联合索引 CREATE INDEX idx_status_created ON orders(order_status, created_at); -- 2. 改写SQL，避免DATE()函数 SELECT DATE(o.created_at) as order_date, u.user_level, COUNT(*) as order_count, SUM(o.order_amount) as total_amount FROM orders o LEFT JOIN users u ON o.user_id = u.user_id WHERE o.created_at >= '2024-01-01' AND o.created_at < '2024-02-01' AND o.order_status IN ('paid', 'shipped') GROUP BY DATE(o.created_at), u.user_level ORDER BY order_date DESC, u.user_level; -- 3. 考虑使用汇总表（物化视图）预处理

五、EXPLAIN ANALYZE（MySQL 8.0.18+）

5.1 功能说明

实际执行SQL并返回详细的执行统计信息，包括实际耗时、实际行数等，比EXPLAIN更精确。

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_status = 'pending'\G

输出示例：

-> Filter: (orders.order_status = 'pending') (cost=101.23 rows=1850) (actual time=0.123..0.456 rows=1234 loops=1) -> Index lookup on orders using idx_status (order_status='pending') (cost=101.23 rows=1850) (actual time=0.098..0.234 rows=1234 loops=1)

关键信息：

六、优化检查清单

七、学习建议

到此这篇关于MySQL EXPLAIN 关键参数详细解释的文章就介绍到这了,