MySQL执行计划查看和解读

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

如何开启执行计划查看

1. 使用EXPLAIN关键字：
   在你的SQL语句前加上EXPLAIN关键字即可查看执行计划。

2. 使用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';

执行计划输出的列说明

1. id: 查询块的编号，同一id表示一个SELECT操作，不同的id表示不同的SELECT操作。

2. select_type: 查询类型，常见的有：

   • SIMPLE: 简单表，没有其他表依赖。
   • PRIMARY: 最外层查询。
   • UNION: 第二个或之后的UNION查询。
   • DEPENDENT UNION: UNION中的第二个或之后的SELECT列表取决于外面的查询。
   • UNION RESULT: UNION的结果。
   • SUBQUERY: 子查询。
   • DEPENDENT SUBQUERY: 子查询依赖于外部查询。

3. table: 被查询的表名。

4. partitions: 当表使用分区时显示被访问的分区。

5. type: 连接类型，表示访问表的方式，常见的有：

   • ALL: 全表扫描，最慢。
   • index: 全索引扫描。
   • range: 索引范围扫描。
   • ref: 使用非唯一索引扫描。
   • eq_ref: 对唯一索引的扫描。
   • const: 对单行记录的索引扫描。
   • system: 对单行记录的索引扫描，与const相似但更快速。

6. possible_keys: 可能用于查询的索引。

7. key: 实际使用的索引。

8. key_len: 使用的索引字节长度。

9. ref: 使用的键值或常量。

10. rows: MySQL估计的需要检查的行数。

11. filtered: 表示经过WHERE条件过滤后的行百分比。

12. Extra: 额外信息，可能包含的信息有：

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

如何解读执行计划

1. 连接顺序:

   • 查看id和select_type来确定连接的顺序和类型。
   • 如果多个表的id相同，则按照列表中的顺序执行。

2. 连接类型:

   • 评估type列，优先使用const、eq_ref和ref，避免使用ALL。
   • 如果type为ALL，考虑添加索引来改善性能。

3. 索引使用:

   • 检查key和possible_keys，确认是否使用了合适的索引。
   • 如果key为空，表示没有使用索引。

4. 额外信息:

   • 注意Extra列中的提示，如Using filesort或Using temporary，这可能表明需要优化。
   • Using where通常表示MySQL在检索阶段使用了WHERE条件。

5. 行数估算:

   • 观察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表。

根据以上信息，我们可以得出结论：此查询首先对emp表使用了索引idx_name进行范围扫描，并应用WHERE条件进行过滤；然后通过dept表的主键进行等值匹配，连接两个表。