MySQL 执行计划字段含义详解

在优化 MySQL 查询性能时，理解和分析执行计划（Execution Plan）是至关重要的。执行计划展示了 MySQL 如何执行查询，包括访问表的顺序、使用的索引、连接类型等。本文将详细介绍 MySQL 执行计划的各个字段，并通过真实案例进行分析。

一、什么是执行计划

执行计划是 MySQL 解析和执行 SQL 语句的步骤和策略。通过 EXPLAIN 关键字，可以查看查询的执行计划。执行计划帮助我们了解查询的执行过程，从而进行优化。

二、获取执行计划

使用 EXPLAIN 关键字可以获取查询的执行计划。例如：

EXPLAIN SELECT * FROM employees WHERE department_id = 1;

三、执行计划字段详解

执行计划的输出包含多个字段，每个字段都有特定的含义。以下是常见字段的详细解释：

1. id

id 字段表示查询中执行的步骤编号。id 值越大，优先级越高。id 相同的步骤按顺序执行，id 不同的步骤按从大到小的顺序执行。

2. select_type

select_type 字段表示查询的类型。常见的类型包括：

• SIMPLE：简单查询，不包含子查询或联合查询。

• PRIMARY：最外层的查询。

• SUBQUERY：子查询。

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

• UNION：联合查询的第二个或后续查询。

• UNION RESULT：联合查询的结果。

3. table

table 字段表示查询涉及的表名或别名。

4. type

type 字段表示访问类型，反映了 MySQL 如何查找表中的行。常见的访问类型按效率从高到低排序如下：

• system：表只有一行（系统表）。

• const：表最多有一行匹配（常量表）。

• eq_ref：对于每个来自前一个表的行，最多有一行匹配。

• ref：对于每个来自前一个表的行，有多行匹配。

• range：使用索引范围扫描。

• index：全索引扫描。

• ALL：全表扫描。

5. possible_keys

possible_keys 字段表示查询中可能使用的索引。

6. key

key 字段表示实际使用的索引。如果没有使用索引，则显示 NULL。

7. key_len

key_len 字段表示使用的索引键的长度。该值越小，查询效率越高。

8. ref

ref 字段表示索引列与哪些列或常量进行比较。

9. rows

rows 字段表示 MySQL 估计需要读取的行数。该值越小，查询效率越高。

10. filtered

filtered 字段表示返回的行数占读取行数的百分比。该值越大，查询效率越高。

11. Extra

Extra 字段包含额外的信息。常见的值包括：

• Using index：查询只使用索引，不需要访问表。

• Using where：查询使用了 WHERE 子句进行过滤。

• Using temporary：查询使用了临时表。

• Using filesort：查询使用了文件排序。

四、真实案例分析

案例一：简单查询

EXPLAIN SELECT * FROM employees WHERE department_id = 1;

输出结果：

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

|----|-------------|-----------|------|---------------|-------------|---------|------|------|----------|-------------|

| 1 | SIMPLE | employees | ref | dept_idx | dept_idx | 4 | const| 10 | 100.00 | Using where |

分析：

• id：查询只有一个步骤。

• select_type：简单查询。

• table：查询的表是 employees。

• type：访问类型是 ref，表示使用索引查找。

• possible_keys：可能使用的索引是 dept_idx。

• key：实际使用的索引是 dept_idx。

• key_len：索引键的长度是 4。

• ref：索引列与常量 1 进行比较。

• rows：估计需要读取 10 行。

• filtered：返回的行数占读取行数的百分比是 100%。

• Extra：使用了 WHERE 子句进行过滤。

案例二：联合查询

EXPLAIN SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.location = 'New York';

输出结果：

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

|----|-------------|-----------|------|---------------|-------------|---------|------|------|----------|-------------|

| 1 | SIMPLE | d | ref | location_idx | location_idx| 5 | const| 1 | 100.00 | Using where |

| 1 | SIMPLE | e | ref | dept_idx | dept_idx | 4 | d.id | 10 | 100.00 | Using where |

分析：

• id：查询有两个步骤，id 相同表示按顺序执行。

• select_type：简单查询。

• table：第一个步骤查询的表是 departments，第二个步骤查询的表是 employees。

• type：第一个步骤的访问类型是 ref，第二个步骤的访问类型也是 ref。

• possible_keys：第一个步骤可能使用的索引是 location_idx，第二个步骤可能使用的索引是 dept_idx。

• key：第一个步骤实际使用的索引是 location_idx，第二个步骤实际使用的索引是 dept_idx。

• key_len：第一个步骤的索引键长度是 5，第二个步骤的索引键长度是 4。

• ref：第一个步骤的索引列与常量 New York 进行比较，第二个步骤的索引列与 departments 表的 id 列进行比较。

• rows：第一个步骤估计需要读取 1 行，第二个步骤估计需要读取 10 行。

• filtered：第一个步骤返回的行数占读取行数的百分比是 100%，第二个步骤也是 100%。

• Extra：两个步骤都使用了 WHERE 子句进行过滤。

案例三：子查询

EXPLAIN SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

输出结果：

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

|----|-------------|-----------|------|---------------|-------------|---------|------|------|----------|-------------|

| 1 | PRIMARY | employees | ALL | dept_idx | NULL | NULL | NULL | 1000 | 10.00 | Using where |

| 2 | SUBQUERY | departments| ref | location_idx | location_idx| 5 | const| 1 | 100.00 | Using where |

分析：

• id：查询有两个步骤，id 为 1 的步骤是主查询，id 为 2 的步骤是子查询。

• select_type：主查询的类型是 PRIMARY，子查询的类型是 SUBQUERY。

• table：主查询的表是 employees，子查询的表是 departments。

• type：主查询的访问类型是 ALL，子查询的访问类型是 ref。

• possible_keys：主查询可能使用的索引是 dept_idx，子查询可能使用的索引是 location_idx。

• key：主查询没有使用索引，子查询实际使用的索引是 location_idx。

• key_len：主查询没有索引键长度，子查询的索引键长度是 5。

• ref：主查询没有索引列比较，子查询的索引列与常量 New York 进行比较。

• rows：主查询估计需要读取 1000 行，子查询估计需要读取 1 行。

• filtered：主查询返回的行数占读取行数的百分比是 10%，子查询是 100%。

• Extra：主查询和子查询都使用了 WHERE 子句进行过滤。

五、优化建议

1. 使用合适的索引

确保查询中使用了合适的索引。可以通过 EXPLAIN 查看 possible_keys 和 key 字段，确保查询使用了索引。

2. 避免全表扫描

尽量避免全表扫描（type 字段为 ALL）。可以通过创建索引或优化查询条件来减少全表扫描。

3. 优化子查询

对于复杂的子查询，可以考虑将子查询改写为 JOIN 查询，或者使用临时表存储子查询结果。

4. 使用覆盖索引

覆盖索引（Using index）可以提高查询性能。确保查询的字段都包含在索引中。

5. 减少返回的行数

通过优化查询条件，减少返回的行数。可以通过 filtered 字段查看返回的行数占读取行数的百分比。

六、总结

理解 MySQL 执行计划的各个字段含义，对于优化查询性能至关重要。通过 EXPLAIN 查看执行计划，可以了解查询的执行过程，从而进行优化。本文详细介绍了执行计划的各个字段，并通过真实案例进行了分析。希望本文能帮助您更好地理解和优化 MySQL 查询。