logo
简体中文

导航菜单

MySQL 面试精讲

EXPLAIN 执行计划

🟡 中等

题目描述

解释以下 EXPLAIN 输出中各字段的含义。如何判断一个查询是否使用了索引？

EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND age > 18;

-- 输出：
-- +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
-- | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
-- +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
-- |  1 | SIMPLE      | users | ref  | idx_name_age  | idx_name | 102     | const |    5 | Using where |
-- +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

提示

  • id：查询序号
  • select_type：查询类型
  • type：访问类型
  • key：实际使用的索引
  • rows：扫描行数
  • Extra：额外信息

解法

参考答案 (3 个标签)
EXPLAIN 执行计划 SQL优化

核心字段详解

1. id - 查询序号

-- 简单查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- id: 1

-- 子查询
EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- id: 1 (外层)
-- id: 2 (子查询)

-- UNION
EXPLAIN SELECT * FROM users WHERE id = 1 UNION SELECT * FROM users WHERE id = 2;
-- id: 1 (第一个 SELECT)
-- id: 2 (第二个 SELECT)
-- id: NULL (UNION 结果)

规则：

  • id 相同：从上往下执行
  • id 不同：id 越大越先执行（子查询）

2. select_type - 查询类型

类型说明示例
SIMPLE简单查询（无子查询、UNION）SELECT * FROM users
PRIMARY外层查询SELECT * FROM users WHERE id IN (...)
SUBQUERY子查询SELECT * FROM users WHERE id IN (SELECT ...)
DERIVED派生表（FROM 子查询）SELECT * FROM (SELECT ...)
UNIONUNION 的第二个 SELECTSELECT 1 UNION SELECT 2
UNION RESULTUNION 的结果-

3. type - 访问类型（重要）

类型性能说明
system极快表只有一行
const极快主键或唯一索引等值查询
eq_ref快连接时使用主键或唯一索引
ref较好非唯一索引等值查询
range一般范围查询（>、<、BETWEEN）
index较差扫描索引树
ALL最差全表扫描

示例：

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

-- ref：非唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
-- type: ref

-- range：范围查询
EXPLAIN SELECT * FROM users WHERE age > 18;
-- type: range

-- ALL：全表扫描
EXPLAIN SELECT * FROM users;
-- type: ALL

4. key - 实际使用的索引

-- NULL：未使用索引
EXPLAIN SELECT * FROM users WHERE age > 18;
-- key: NULL (假设 age 无索引)

-- idx_name：使用了索引
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
-- key: idx_name

-- PRIMARY：使用了主键
EXPLAIN SELECT * FROM users WHERE id = 1;
-- key: PRIMARY

5. rows - 扫描行数（估算）

-- rows: 1（理想情况）
EXPLAIN SELECT * FROM users WHERE id = 1;
-- rows: 1

-- rows: 1000（需要优化）
EXPLAIN SELECT * FROM users WHERE age > 18;
-- rows: 1000 (扫描 1000 行)

6. Extra - 额外信息

信息说明
Using index覆盖索引（不回表）✓
Using where需要回表或过滤
Using filesort需要外部排序 ✗
Using temporary使用临时表 ✗
Using index condition索引下推（ICP）

示例：

-- Using index：覆盖索引
EXPLAIN SELECT name, age FROM users WHERE name = 'Alice';
-- Extra: Using index

-- Using filesort：需要排序
EXPLAIN SELECT * FROM users WHERE age > 18 ORDER BY name;
-- Extra: Using filesort

-- Using temporary：使用临时表
EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name ORDER BY COUNT(*);
-- Extra: Using temporary; Using filesort

判断查询是否使用索引

判断标准

-- ✓ 使用索引
-- type: const, eq_ref, ref, range
-- key: 不是 NULL
-- rows: 较小

-- ✗ 未使用索引或效率低
-- type: ALL, index
-- key: NULL
-- rows: 很大

示例对比

优化前：全表扫描

EXPLAIN SELECT * FROM users WHERE age > 18;

-- 输出：
-- type: ALL
-- key: NULL
-- rows: 1000000

-- 结论：需要优化

优化后：使用索引

-- 添加索引
CREATE INDEX idx_age ON users(age);

EXPLAIN SELECT * FROM users WHERE age > 18;

-- 输出：
-- type: range
-- key: idx_age
-- rows: 500000

-- 结论：使用了索引，但仍需优化

最佳优化：覆盖索引

-- 添加联合索引
CREATE INDEX idx_age_name ON users(age, name);

EXPLAIN SELECT id, name, age FROM users WHERE age > 18;

-- 输出：
-- type: range
-- key: idx_age_name
-- rows: 500000
-- Extra: Using index

-- 结论：覆盖索引，不回表

优化案例

案例 1：避免 filesort

-- ❌ Using filesort
EXPLAIN SELECT * FROM users WHERE age > 18 ORDER BY name;
-- Extra: Using filesort

-- ✅ 创建索引
CREATE INDEX idx_age_name ON users(age, name);

EXPLAIN SELECT * FROM users WHERE age > 18 ORDER BY name;
-- Extra: (无 filesort)

案例 2：避免临时表

-- ❌ Using temporary
EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name;
-- Extra: Using temporary

-- ✅ 创建索引（可能有效）
CREATE INDEX idx_name ON users(name);

EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name;
-- Extra: Using index

案例 3：优化 JOIN

-- ❌ 驱动表选择不当
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- 可能导致大表驱动小表

-- ✅ 优化（小表驱动大表）
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- rows 更少

EXPLAIN 扩展

EXPLAIN FORMAT

-- 传统格式
EXPLAIN SELECT * FROM users WHERE id = 1;

-- JSON 格式（更详细）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;

-- TREE 格式（MySQL 8.0+）
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE id = 1;

EXPLAIN ANALYZE（MySQL 8.0+）

-- 实际执行并返回详细信息
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- 输出：
-- -> Filter: (users.id = 1)  (cost=0.35 rows=1)
--     -> Table scan on users  (cost=0.35 rows=1)
-- 实际执行时间
上一章节 最左前缀原则

搜索