MySQL执行计划详解

概念

MySQL执行计划（Execution Plan）是数据库管理系统对SQL查询语句进行解析、优化、选择最优执行路径，并最终执行的详细步骤和策略的描述。

为什么需要

  1. 性能优化：通过分析执行计划，可以了解查询的具体执行过程，识别性能瓶颈，优化查询语句和数据库结构。
  2. 调试与诊断：执行计划帮助开发者和DBA识别查询中的问题，如未使用索引、全表扫描等，进行有效的调试和诊断。
  3. 资源管理：通过执行计划，可以合理分配和管理数据库资源，避免不必要的资源浪费，提升系统整体性能。

怎么使用

MySQL提供了EXPLAIN语句来生成执行计划，以下是基本使用方法：

基本用法：

1
EXPLAIN SELECT * FROM table_name WHERE condition;

输出结果解读：

EXPLAIN的输出包含多个字段，每个字段都提供了执行计划的关键信息：

  • id：查询的标识符，标识查询执行的顺序。
  • select_type：查询类型，如SIMPLE、PRIMARY、UNION等。
  • table：查询涉及的表。
  • type：连接类型，如ALL、index、range、ref、eq_ref、const等，表示查询的扫描方式。
  • possible_keys：查询可能使用的索引。
  • key：实际使用的索引。
  • key_len：使用索引的长度。
  • ref：使用索引的列。
  • rows：MySQL估计的要读取的行数。
  • Extra：额外信息，如Using where、Using index、Using temporary、Using filesort等。

type 和 Extra 需要重点关注

type类型解读：

总的来看，速度排序如下：

ALL < index < index_merge < range < ref < eq_ref < const < system

ALL（全表扫描）
  • 描述：MySQL对表的所有行进行扫描，找出满足查询条件的行。
  • 性能：这是最慢的扫描方式，通常出现在没有索引或索引无法使用的情况下。
  • 优化建议：尽量避免全表扫描，可以通过创建合适的索引来优化查询。
index（索引全扫描）
  • 描述：MySQL扫描整个索引来获取数据，而不是扫描整个表。这种方式比全表扫描快，因为索引通常比表小。
  • 性能：较慢，通常出现在查询仅访问索引中的字段时。
  • 优化建议：如果可能，将查询条件限定在索引列上以减少扫描范围。
range（范围扫描）
  • 描述：MySQL通过索引范围扫描来检索特定范围内的行。
  • 性能：效率较高，适用于范围查询，如使用BETWEEN、<、>、IN等运算符的查询。
  • 优化建议：确保查询条件中的列上有适当的索引。
ref（非唯一索引扫描）
  • 描述：MySQL使用非唯一索引扫描来查找匹配某个单一值的所有行。这种类型通常用于外键或联合索引的前导列。
  • 性能：效率高，通常出现在关联查询中。
  • 优化建议：确保适当的索引存在，并优化表结构以支持此类扫描。
eq_ref（唯一索引扫描）
  • 描述：MySQL通过唯一索引扫描来检索匹配单一值的行。这种类型通常用于主键或唯一索引。
  • 性能：效率非常高，每次只返回一行。
  • 优化建议：确保主键或唯一索引的查询条件存在，并尽量使用这种扫描方式。
const（常量）
  • 描述：MySQL将表视为常量，意味着表中最多只有一行匹配查询条件。适用于通过主键或唯一索引进行查询时。
  • 性能：效率最高，通常用于优化常量值的查询。
  • 优化建议：确保主键或唯一索引条件的查询能利用这种扫描方式。
system（系统表）
  • 描述：MySQL将表视为系统表，表示表中只有一行数据。这种类型与const类似，但仅适用于系统表。
  • 性能：效率最高，通常用于系统表查询。
  • 优化建议：无须优化，系统表通常为系统内部使用。
index_merge（索引合并）
  • 描述：MySQL使用多个索引合并结果集来满足查询条件。通常出现在多个独立索引的条件组合查询中。
  • 性能：效率较高，但不如单一索引扫描。
  • 优化建议：在多个条件查询中，使用联合索引替代多个独立索引。

Extra类型解读：

在MySQL执行计划的输出中，Extra列提供了有关查询的额外信息。这些信息有助于理解查询的执行细节和潜在的性能问题。以下是常见的Extra类型及其解读：

Using where
  • 描述：表示MySQL在存储引擎返回数据后再应用WHERE条件进行过滤。
  • 影响：一般来说，这种情况比较常见，但如果出现没有使用索引的查询，可以考虑优化索引。
  • 示例：
    1
    EXPLAIN SELECT * FROM employees WHERE age > 30;
    1
    Extra: Using where
Using index
  • 描述：表示查询使用了覆盖索引（Covering Index），即查询只访问索引而不访问数据行。
  • 影响：通常是好事，因为这减少了I/O操作，提高了查询性能。
  • 示例：
    1
    EXPLAIN SELECT name FROM employees WHERE age > 30;
    1
    Extra: Using where; Using index
Using temporary

  • 描述：表示MySQL在执行查询时使用了临时表，通常出现在复杂的查询（如GROUP BY、ORDER BY）中。

  • 影响：使用临时表可能会导致性能下降，尤其是在内存不够用时，MySQL会使用磁盘临时表。

  • 示例：

    1
    EXPLAIN SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
    1
    Extra: Using temporary
Using filesort
  • 描述：表示MySQL在执行查询时使用了文件排序，通常出现在ORDER BY或GROUP BY无法利用索引时。
  • 影响：文件排序是一种代价较高的操作，可能会显著影响性能。
  • 示例：
    1
    EXPLAIN SELECT * FROM employees ORDER BY age;
    1
    Extra: Using filesort
Using join buffer (Block Nested Loop)
  • 描述：表示MySQL在执行连接操作时使用了连接缓冲区。通常出现在没有适当索引的连接查询中。
  • 影响：这可能会导致性能问题，建议检查连接条件和索引。
  • 示例：
    1
    EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id;
    1
    Extra: Using join buffer (Block Nested Loop)
Impossible WHERE
  • 描述：表示WHERE条件永远不可能为真，查询不会返回任何行。
  • 影响：这通常表示查询条件存在逻辑错误。
  • 示例：
    1
    EXPLAIN SELECT * FROM employees WHERE 1 = 0;
    1
    Extra: Impossible WHERE
Distinct
  • 描述：表示MySQL在发现第一条匹配的行后停止搜索。通常出现在使用DISTINCT关键字时。
  • 影响：一般是性能优化，减少了不必要的搜索。
  • 示例：
    1
    EXPLAIN SELECT DISTINCT department_id FROM employees;
    1
    Extra: Distinct
Using MRR (Multi-Range Read)
  • 描述：表示MySQL使用多范围读取优化技术来减少随机I/O操作。
  • 影响：通常是积极的，可以提高性能。
  • 示例：
    1
    EXPLAIN SELECT * FROM employees WHERE age BETWEEN 30 AND 40;
    1
    Extra: Using MRR
Using index condition
  • 描述：表示MySQL在存储引擎中使用了索引条件下推（Index Condition Pushdown, ICP）来过滤数据。
  • 影响：通常有助于减少返回的数据量，提高查询性能。
  • 示例：
    1
    EXPLAIN SELECT * FROM employees WHERE age > 30 AND name LIKE 'J%';
    1
    Extra: Using index condition
Using where with pushed condition
  • 描述：表示MySQL将WHERE条件推送到存储引擎层以提高性能。
  • 影响：通常有助于减少返回的数据量，提高查询性能。
  • 示例：
    1
    EXPLAIN SELECT * FROM employees WHERE age > 30 AND department_id = 5;
    1
    Extra: Using where with pushed condition

结合案例分析

全表扫描（type: ALL）：

1
EXPLAIN SELECT * FROM employees;

输出：

1
2
3
4
5
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 10000 |             |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+

分析：执行计划显示查询对employees表进行了全表扫描（type为ALL），这通常是性能最低的扫描方式，尤其是在大表上。

使用索引（type: ref）：

1
EXPLAIN SELECT * FROM employees WHERE department_id = 5;

输出：

1
2
3
4
5
+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | employees | ref  | dept_index    | dept_index  | 4       | const | 100  | Using where |
+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+

分析：执行计划显示查询使用了dept_index索引（type为ref），Using where 表明数据是查出来再过滤，如果我们只要查department_id，那么大概率会是Using Index,会走覆盖索引

联合索引（type: index_merge）：

1
EXPLAIN SELECT * FROM employees WHERE age > 30 AND salary > 50000;

输出：

1
2
3
4
5
+----+-------------+-----------+------+---------------+-------------+---------+------+-------+----------------------------------+
| id | select_type | table     | type | possible_keys | key         | key_len | ref  | rows  | Extra                            |
+----+-------------+-----------+------+---------------+-------------+---------+------+-------+----------------------------------+
|  1 | SIMPLE      | employees | index_merge | age_index,salary_index | NULL | NULL | 1000  | Using union(age_index,salary_index) |
+----+-------------+-----------+------+---------------+-------------+---------+------+-------+----------------------------------+

分析：查询使用了index_merge策略，结合了age_index和salary_index索引，提高了查询效率。

总结

MySQL执行计划是优化SQL查询性能的重要工具。通过EXPLAIN语句，开发者和DBA可以详细了解查询的执行过程，识别潜在的性能问题，并进行相应的优化。

理解执行计划的各个字段和含义，有助于更好地优化数据库查询，提升系统性能。有效利用索引、避免全表扫描、优化查询条件等都是提升查询性能的重要策略。