如何用explain分析sql语句

explain语句是MySQL用于分析 SQL 查询的工具，其核心用途是为了：

• 优化查询性能
• 诊断索引失效问题
• 分析多表关联效率

explain输出字段解释

id

查询的标识符。表示查询中执行 SELECT 子句或操作表的顺序，id值越大优先级越高，越先被执行。

• id 相同：执行顺序由上至下
• id 不同：id 值越大，优先级越高，越先执行
• id 有相同也有不同：分组执行，相同 id 为一组

select_type

• SIMPLE：简单的 SELECT 查询，不包含子查询或 UNION。

• PRIMARY：最外层的 SELECT（包含子查询的外层查询）。

• UNION：UNION 中的第二个或后续的 SELECT 查询。

• UNION RESULT：UNION 的结果。

• SUBQUERY：子查询中的第一个 SELECT。

• DERIVED：派生表（FROM 子句中的子查询的结果作为临时表）。

• DEPENDENT UNION：UNION 中的第二个或后续的 SELECT 查询，依赖于外部查询。

• DEPENDENT SUBQUERY：子查询，依赖于外部查询。

table

表示 explain的这一行正在访问哪个表

• 当 from 子句中有子查询时，如果table列是 格式，则表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询
• 当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id

partitions

如果该查询是基于分区表的查询，partitions字段会显示查询所访问的分区。

type

从优到差的常见类型：

• system：表仅有一行（系统表），性能最佳

• const：通过索引一次就找到数据（如主键或唯一索引）

  -- 查询主键为1的用户
  EXPLAIN SELECT * FROM users WHERE id = 1;
  -- type显示为const

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录匹配（常见于多表关联）

  -- 用户与订单关联查询
  EXPLAIN SELECT * FROM users u 
  JOIN orders o ON u.id = o.user_id;
  -- 若user_id是orders表的外键，type为eq_ref

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行

  -- 查询所有年龄为18的用户
  EXPLAIN SELECT * FROM users WHERE age = 18;
  -- 若age字段有索引，type为ref

• range：只检索给定范围的行，使用一个索引来选择行（如 WHERE 中的 BETWEEN、>、< 等操作）

  -- 查询年龄在18-25之间的用户
  EXPLAIN SELECT * FROM users WHERE age BETWEEN 18 AND 25;
  -- type为range

• index：Full Index Scan，遍历索引树

  -- 查询仅需要索引列的信息
  EXPLAIN SELECT user_id FROM users;
  -- 若user_id有索引，type为index

• ALL：Full Table Scan，遍历全量数据，性能最差

possible_keys（可能使用的索引）

• 显示可能应用在这张表上的索引，一个或多个
• 该列完全独立于 EXPLAIN 输出所示的表的读取顺序

指出MySQL可能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

key（实际使用的索引）

显示 MySQL 实际决定使用的索引，此索引必然被包含在possible_keys中。

如果没有选择索引，键是NULL。

可能出现这种情况，possible_keys有显示列，而key显示NULL的情况，这种情况是因为表中数据不多，Mysql优化器认为查询时走索引对此查询语句帮助不大，从而优化器会选择全表扫描（扫描聚簇索引），而不是走索引来查询。

key_len

索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

例如：VARCHAR (100) 且为 UTF8 编码，key_len 最大为 303（100*3+3）

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

显示 MySQL 认为它执行查询时必须检查的行数

多行之间的同组数据相乘可以估算要处理的行数，不同组的相加

filtered

该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

Extra

包含对执行计划非常重要的信息

• Using filesort：MySQL 需要额外的排序操作（如 ORDER BY），通常意味着需要优化索引

-- 查询用户并按年龄排序（若age无索引）
EXPLAIN SELECT * FROM users ORDER BY age;
-- Extra显示Using filesort

• Using temporary：MySQL 需要创建临时表来存储结果（如 GROUP BY 或 DISTINCT）

-- 按部门分组统计用户
EXPLAIN SELECT department, COUNT(*) FROM users GROUP BY department;
-- 若department无索引，Extra显示Using temporary

• Using index：表示相应的 SELECT 操作中使用了覆盖索引（Covering Index）

-- 查询仅需要索引列的信息
EXPLAIN SELECT user_id, username FROM users;
-- 若user_id和username组成复合索引，Extra显示Using index

• Using where：表示使用了 WHERE 子句进行过滤

EXPLAIN 案例

索引失效导致全表扫描

-- 原始查询
EXPLAIN SELECT * FROM orders 
WHERE order_date > '2023-01-01' 
AND amount > 1000;

-- 执行计划结果
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1  | SIMPLE      | orders| ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

-- 问题分析：type为ALL，全表扫描，未使用任何索引

-- 优化方案：创建复合索引
CREATE INDEX idx_order_date_amount ON orders (order_date, amount);

-- 优化后执行计划
+----+-------------+-------+-------+-----------------------+-----------------------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys         | key                   | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+-----------------------+-----------------------+---------+------+--------+-------------+
| 1  | SIMPLE      | orders| range | idx_order_date_amount | idx_order_date_amount | 5       | NULL | 5000   | Using where |
+----+-------------+-------+-------+-----------------------+-----------------------+---------+------+--------+-------------+

Using filesort 优化

-- 原始查询
EXPLAIN SELECT * FROM users 
ORDER BY create_time DESC 
LIMIT 10;

-- 执行计划结果
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| 1  | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+

-- 问题分析：无索引，排序操作通过文件排序实现

-- 优化方案：创建索引
CREATE INDEX idx_create_time ON users (create_time);

-- 优化后执行计划
+----+-------------+-------+-------+------------------+------------------+---------+------+--------+-------+
| id | select_type | table | type  | possible_keys    | key              | key_len | ref  | rows   | Extra |
+----+-------------+-------+-------+------------------+------------------+---------+------+--------+-------+
| 1  | SIMPLE      | users | index | NULL             | idx_create_time  | 5       | NULL | 10     | NULL  |
+----+-------------+-------+-------+------------------+------------------+---------+------+--------+-------+

EXPLAIN 使用技巧

索引优化：

• 确保 WHERE 子句中的条件列有索引
• 复合索引的顺序要根据最左前缀原则设计

关注 type 和 Extra 列：

• 优先优化 type 为 ALL、index 的查询
• Extra字段尽量避免出现 Using filesort 和 Using temporary

覆盖索引：

设计索引时，让索引包含所有需要查询的字段，避免回表

-- 查询仅需要索引列的信息
SELECT user_id, username FROM users;
-- 索引设计为 (user_id, username)，可触发Using index

子查询优化：

优先将子查询转换为 JOIN 操作，减少 DERIVED 类型的查询

-- 低效子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 优化为JOIN
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;