mysql查询优化explain命令是怎样的

发布时间：2021-11-11 17:19:37 作者：柒染
来源：亿速云阅读：154

# MySQL查询优化EXPLN命令是怎样的

## 引言

在数据库性能优化领域，EXPLN命令是MySQL开发者最重要的工具之一。这个看似简单的命令能够揭示SQL查询背后的执行计划，帮助开发者理解查询是如何被数据库引擎处理的。本文将深入探讨EXPLN命令的各个方面，包括其输出解读、关键指标分析以及实际优化案例。

## 一、EXPLN基础

### 1.1 什么是EXPLN命令

EXPLN是MySQL提供的用于分析SELECT查询执行计划的命令。通过在SELECT语句前添加EXPLN关键字，MySQL会返回该查询的执行计划而非实际执行结果。

```sql
EXPLN SELECT * FROM users WHERE age > 30;

1.2 EXPLN的多种形式

MySQL提供了几种EXPLN的变体：

传统EXPLN：基本执行计划分析
EXPLN FORMAT=JSON：以JSON格式提供更详细的信息
EXPLN ANALYZE（MySQL 8.0+）：提供实际执行统计信息

-- JSON格式
EXPLN FORMAT=JSON SELECT * FROM orders;

-- 实际执行分析(MySQL 8.0+)
EXPLN ANALYZE SELECT * FROM products;

二、EXPLN输出详解

2.1 核心输出列

EXPLN命令返回的结果包含多列重要信息：

列名	描述
id	查询标识符
select_type	查询类型
table	访问的表
partitions	匹配的分区
type	访问类型
possible_keys	可能使用的索引
key	实际使用的索引
key_len	使用的索引长度
ref	与索引比较的列
rows	预估需要检查的行数
filtered	表条件过滤的百分比
Extra	额外信息

2.2 关键指标深度解析

type列（访问类型）

这是判断查询效率最重要的指标之一，按性能从优到劣排序：

system：系统表，只有一行记录
const：通过主键或唯一索引访问
eq_ref：关联查询中使用的索引查找
ref：非唯一索引查找
range：索引范围扫描
index：全索引扫描
ALL：全表扫描（需避免）

Extra列常见值

Using index：覆盖索引（良好）
Using where：服务器在存储引擎检索后过滤
Using temporary：使用临时表（需警惕）
Using filesort：额外排序操作（需优化）
Using join buffer：使用连接缓冲

三、EXPLN实战分析

3.1 简单查询分析

EXPLN SELECT * FROM employees WHERE last_name = 'Smith';

假设输出：

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ref  | idx_last_name | idx_last_name | 102 | const | 5      | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

解读： - 使用了idx_last_name索引 - 类型为ref（非唯一索引查找） - 预估检查5行数据 - 使用了WHERE条件过滤

3.2 复杂查询分析

EXPLN 
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.hire_date > '2020-01-01'
ORDER BY e.last_name;

可能输出：

+----+-------------+-------+--------+---------------+---------+---------+----------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+---------------------------------+
|  1 | SIMPLE      | e     | range  | idx_dept,idx_hire | idx_hire | 3       | NULL           | 100  | Using where; Using filesort      |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 4       | company.e.dept_id | 1    | NULL                            |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+---------------------------------+

优化建议： 1. 为hire_date和last_name创建复合索引 2. 考虑添加覆盖索引避免回表

四、高级EXPLN技巧

4.1 EXPLN FORMAT=JSON

JSON格式提供更详细的信息，包括成本估算：

EXPLN FORMAT=JSON 
SELECT * FROM large_table WHERE category = 'books' LIMIT 100;

输出包含： - query_cost：查询总成本估计 - table：详细的表访问信息 - used_columns：实际使用的列 - optimized_away：被优化的部分

4.2 MySQL 8.0的EXPLN ANALYZE

提供实际执行统计信息：

EXPLN ANALYZE 
SELECT * FROM orders 
WHERE customer_id = 100 AND order_date > NOW() - INTERVAL 30 DAY;

输出包括： - 实际执行时间 - 循环次数 - 各步骤耗时占比 - 内存使用情况

五、常见优化模式

5.1 索引优化案例

问题查询：

EXPLN SELECT * FROM products 
WHERE category = 'electronics' AND price > 1000
ORDER BY created_at DESC;

优化方案： 1. 创建复合索引：(category, price, created_at) 2. 考虑使用覆盖索引包含查询的所有列

5.2 分页查询优化

低效分页：

EXPLN SELECT * FROM logs 
ORDER BY timestamp DESC LIMIT 10000, 20;

优化方案： 1. 使用索引覆盖+延迟关联 2. 记录上次查询的最大ID

SELECT * FROM logs l
JOIN (SELECT id FROM logs ORDER BY timestamp DESC LIMIT 10000, 20) tmp
ON l.id = tmp.id;

六、EXPLN的限制与注意事项

预估而非实际：EXPLN显示的是预估执行计划
不执行查询：不会实际执行查询或子查询
存储引擎差异：不同存储引擎的输出可能不同
版本差异：不同MySQL版本功能有差异
复杂查询限制：对某些复杂查询可能不准确

七、总结

EXPLN命令是MySQL查询优化的基石工具。通过本文的详细解析，我们了解到：

如何解读EXPLN的各个输出列
识别关键性能指标如type、Extra等
应用EXPLN分析实际查询案例
使用高级技巧如JSON格式和ANALYZE选项
基于EXPLN结果实施常见优化模式

掌握EXPLN命令需要理论与实践相结合。建议开发者在日常工作中养成分析重要查询执行计划的习惯，逐步积累优化经验，最终提升整个应用的数据库性能。