MySQL执行计划查看和解读

在MySQL中，要查看SQL查询的执行计划，可以使用EXPLAIN关键字。这可以帮助你理解MySQL是如何处理你的查询的，包括它如何选择索引、表的连接顺序等。

如何开启执行计划查看

使用EXPLAIN关键字：
在你的SQL语句前加上EXPLAIN关键字即可查看执行计划。
使用EXPLAIN FORMAT选项：
MySQL还支持不同的输出格式，例如JSON或TRADITIONAL等。

基本语法

EXPLAIN [FORMAT={TRADITIONAL | JSON}] SELECT ...;

示例

假设有一个名为employees的表，并且你想查看一个特定查询的执行计划：

EXPLAIN SELECT * FROM employees WHERE department = 'sales';

或者如果你想查看输出为JSON格式：

EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE department = 'sales';

执行计划输出的列说明

id: 查询块的编号，同一id表示一个SELECT操作，不同的id表示不同的SELECT操作。
select_type: 查询类型，常见的有：
- SIMPLE: 简单表，没有其他表依赖。
- PRIMARY: 最外层查询。
- UNION: 第二个或之后的UNION查询。
- DEPENDENT UNION: UNION中的第二个或之后的SELECT列表取决于外面的查询。
- UNION RESULT: UNION的结果。
- SUBQUERY: 子查询。
- DEPENDENT SUBQUERY: 子查询依赖于外部查询。
table: 被查询的表名。
partitions: 当表使用分区时显示被访问的分区。
type: 连接类型，表示访问表的方式，常见的有：
- ALL: 全表扫描，最慢。
- index: 全索引扫描。
- range: 索引范围扫描。
- ref: 使用非唯一索引扫描。
- eq_ref: 对唯一索引的扫描。
- const: 对单行记录的索引扫描。
- system: 对单行记录的索引扫描，与const相似但更快速。
possible_keys: 可能用于查询的索引。
key: 实际使用的索引。
key_len: 使用的索引字节长度。
ref: 使用的键值或常量。
rows: MySQL估计的需要检查的行数。
filtered: 表示经过WHERE条件过滤后的行百分比。
Extra: 额外信息，可能包含的信息有：

Using where: 表示MySQL使用WHERE子句进行过滤。
Using index: 表示MySQL只使用索引中的信息而无需访问实际的表行。
Using temporary: 使用临时表存储中间结果。
Using filesort: 数据需要额外的排序操作。
Not exists: 用于优化LEFT JOIN。
Using join buffer: 使用连接缓冲区读取数据。
Using index condition: 使用索引条件推送（ICP）。

如何解读执行计划

连接顺序:
- 查看id和select_type来确定连接的顺序和类型。
- 如果多个表的id相同，则按照列表中的顺序执行。
连接类型:
- 评估type列，优先使用const、eq_ref和ref，避免使用ALL。
- 如果type为ALL，考虑添加索引来改善性能。
索引使用:
- 检查key和possible_keys，确认是否使用了合适的索引。
- 如果key为空，表示没有使用索引。
额外信息:
- 注意Extra列中的提示，如Using filesort或Using temporary，这可能表明需要优化。
- Using where通常表示MySQL在检索阶段使用了WHERE条件。
行数估算:
- 观察rows列来评估MySQL预计的扫描行数，如果过高可能意味着需要优化查询或索引。

示例解读

假设你有以下执行计划输出：

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | emp   | range  | idx_name      | idx_name| 10      | NULL             |    5 | Using where |
|  1 | SIMPLE      | dept  | eq_ref | PRIMARY       | PRIMARY | 4       | database.emp.dept|    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

解读:
- id: 1，表示这是一个简单的SELECT操作。
- select_type: SIMPLE，简单查询。
- table: emp 和 dept，查询涉及的两个表。
- type: range 和 eq_ref，emp表使用了范围索引扫描，dept表使用了唯一索引。
- possible_keys: idx_name，表示emp表可以使用idx_name索引。
- key: idx_name 和 PRIMARY，使用的索引。
- key_len: 10 和 4，索引的长度。
- ref: NULL 和 database.emp.dept，dept表的索引使用了emp表的dept字段作为参考。
- rows: 5 和 1，MySQL估计需要检查的行数。
- Extra: Using where，表示MySQL使用了WHERE条件来过滤emp表。