MySQL执行计划详解

MySQL 执行计划是 MySQL 数据库在执行 SQL 查询时所采用的操作步骤和策略的详细描述。通过分析执行计划，我们可以深入了解数据库是如何处理查询的，从而找出潜在的性能瓶颈并进行优化。以下是对 MySQL 执行计划的详细解析：

查看执行计划

在 MySQL 中，可以使用EXPLAIN或EXPLAIN ANALYZE语句来查看执行计划。

-- 基本的EXPLAIN语句
EXPLAIN SELECT * FROM users JOIN orders ON users.id = orders.user_id WHERE users.age > 20;

-- EXPLAIN ANALYZE可以提供更详细的执行信息
EXPLAIN ANALYZE SELECT * FROM users JOIN orders ON users.id = orders.user_id WHERE users.age > 20;

执行计划各列含义

1. id

• 含义：查询的标识符，是查询执行的顺序编号。如果id相同，则从上往下依次执行；如果id不同，id值越大越先执行。
• 示例：

EXPLAIN SELECT * 
FROM (SELECT id FROM users WHERE age > 20) subquery
JOIN orders ON subquery.id = orders.user_id;

这里子查询的id值可能会比外层查询大，意味着子查询会先执行。

2. select_type

• 含义：表示查询的类型，常见的类型有：
  • SIMPLE：简单查询，不包含子查询或 UNION 操作。
  • PRIMARY：主查询，包含子查询的外层查询。
  • SUBQUERY：子查询。
  • DERIVED：派生表查询，即使用子查询作为临时表的查询。
  • UNION：UNION 操作中的第二个及后续查询。
  • UNION RESULT：UNION 操作的结果集。
• 示例：

EXPLAIN SELECT * 
FROM users 
WHERE id IN (SELECT user_id FROM orders);

这里主查询的select_type为PRIMARY，子查询的select_type为SUBQUERY。

3. table

• 含义：表示当前执行步骤所涉及的表名，如果是子查询或派生表，会显示别名。
• 示例：在上述查询中，会分别显示users和orders表名。

4. partitions

• 含义：如果表进行了分区，显示查询会访问的分区。如果表未分区，该列值为NULL。

5. type

• 含义：表示表的访问类型，从好到坏依次为：
  • system：表中只有一行记录，是const类型的特例。
  • const：通过索引一次就找到匹配的记录，通常用于主键或唯一索引的等值查询。
  • eq_ref：多表连接时，对于前一个表的每一行，后一个表只通过索引查找一条记录，常用于主键或唯一索引的连接查询。
  • ref：使用非唯一索引或索引前缀进行查找，返回匹配某个单独值的所有行。
  • range：只检索给定范围的行，使用索引来选择行，常见于WHERE子句中的BETWEEN、>、<等操作。
  • index：全索引扫描，扫描整个索引树来获取数据。
  • ALL：全表扫描，性能最差。
• 示例：

EXPLAIN SELECT * FROM users WHERE id = 1;  -- 可能是const类型
EXPLAIN SELECT * FROM users WHERE age > 20; -- 可能是range类型

6. possible_keys

• 含义：表示 MySQL 在执行查询时可能使用的索引。可能会列出多个索引，但实际不一定会使用。

7. key

• 含义：表示 MySQL 实际使用的索引。如果为NULL，则表示没有使用索引。

8. key_len

• 含义：表示 MySQL 使用的索引的长度，长度越短越好。它可以帮助我们判断索引是否被充分利用。

9. ref

• 含义：表示哪些列或常量被用于查找索引列上的值。

10. rows

• 含义：表示 MySQL 估计要扫描的行数，这个值是一个预估值，并不一定准确，但可以帮助我们大致了解查询的成本。

11. filtered

• 含义：表示查询条件过滤后剩余记录的百分比。值越大，说明过滤效果越好。

12. Extra

• 含义：包含一些额外的信息，常见的信息有：
  • Using where：表示使用了WHERE子句进行过滤。
  • Using index：表示使用了覆盖索引，即查询只需要访问索引，不需要访问表的数据行。
  • Using temporary：表示 MySQL 需要使用临时表来存储中间结果，常见于GROUP BY和ORDER BY操作。
  • Using filesort：表示 MySQL 需要使用文件排序，这通常意味着查询的性能可能会受到影响。

执行计划的分析与优化

• 索引优化：根据possible_keys和key列的信息，检查是否使用了合适的索引。如果key为NULL，可能需要创建索引来提高查询性能。
• 减少全表扫描：尽量避免type为ALL的情况，可以通过创建索引、优化查询条件等方式来减少全表扫描。
• 避免使用临时表和文件排序：如果Extra列中出现Using temporary或Using filesort，可以通过调整查询语句、创建合适的索引等方式来避免。

通过对 MySQL 执行计划的详细分析，我们可以深入了解数据库的查询执行过程，找出性能瓶颈并进行针对性的优化，从而提高数据库的查询效率。