mysql执行计划详解

MySQL 的执行计划是数据库查询优化的重要部分，它展示了 MySQL 查询优化器如何执行 SQL 查询，并给出了该查询的执行路径、使用的索引、连接方式等详细信息。理解执行计划可以帮助我们识别性能瓶颈并进行优化。

1.获取执行计划的方式

要获取 MySQL 查询的执行计划，可以使用EXPLAIN
语句。基本的语法如下：

sql
EXPLAIN SELECT * FROM your_table WHERE condition;

执行后，MySQL 会返回一系列关于查询如何执行的详细信息。你还可以使用EXPLAIN ANALYZE
来获取执行计划的执行时间信息（MySQL 8.0+）。

2.EXPLAIN 输出的列详解

执行EXPLAIN
后，会返回如下字段：

2.1各个字段的详细解释

• id：当查询包含多个子查询或联合查询时，每个查询的id
  可能不同。id
  的数值越小，查询的优先级越高。子查询通常会有一个更高的id
  ，代表它的执行会在主查询之前。

• select_type：

  • SIMPLE
    
    ：简单的查询，没有使用UNION
    或子查询。
  • PRIMARY
    
    ：最外层的查询。
  • UNION
    
    ：UNION
    查询中的第二个及后续的查询。
  • DEPENDENT SUBQUERY
    
    ：依赖于外部查询的子查询（一般是子查询中使用了外部查询的列）。
  • SUBQUERY
    
    ：独立的子查询。

• table：这个列表示当前行正在处理的表的名称。如果你有多个表的连接或子查询，这里会列出不同的表。

• type：这是最关键的一列，表示 MySQL 在查询时采用了何种连接方式。常见的连接方式如下：

  • ALL
    
    ：全表扫描，通常是性能最差的，因为它会扫描整个表。
  • index
    
    ：索引扫描，扫描整个索引树，比全表扫描效率要高。
  • range
    
    ：范围扫描，查找索引中一段连续的范围，如BETWEEN
    、>
    ,<
    。
  • ref
    
    ：基于非唯一索引查找，通常用于连接操作。
  • eq_ref
    
    ：基于唯一索引查找，通常是通过主键或唯一索引查找。
  • const
    
    ：常量查找，表示这个查询能在执行时只返回一行数据，效率最高。
  • NULL
    
    ：对于优化器不进行查询的情况。

• possible_keys：查询可能使用的索引。如果没有索引，结果会为空。可以查看是否存在合适的索引来优化查询。

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

• key_len：表示 MySQL 使用的索引的长度，单位是字节。它告诉你索引的部分被用作查找数据。

• ref：这一列展示了用于查找索引的列。对于连接操作，它可能会显示你连接的条件列或常量。

• rows：预估的扫描行数，表示 MySQL 执行查询时要检查多少行数据。这是查询优化时需要关注的一个重要指标，行数越少通常性能越好。

• filtered：预计在 WHERE 子句中筛选出的行的比例。例如，如果某个条件能筛选出 50% 的数据，那么filtered
  的值是 50。

• Extra：提供了额外的查询执行信息。常见的Extra
  内容包括：

  • Using where
    
    ：表示 MySQL 在检索数据时应用了 WHERE 条件。
  • Using index
    
    ：表示查询只从索引中获取数据，而不需要读取表中的数据。
  • Using temporary
    
    ：表示 MySQL 创建了一个临时表来执行查询。
  • Using filesort
    
    ：表示 MySQL 使用了外部排序来执行查询（通常会导致性能问题）。
  • Distinct
    
    ：表示使用了DISTINCT
    操作。

3.执行计划的示例

假设你有如下的查询：

sql
EXPLAIN SELECT * FROM users WHERE age>30 ORDER BY name;

输出的执行计划可能类似于：

解释：

• id
  ：1
  ，表示这个查询是最外层查询。
• select_type
  ：SIMPLE
  ，表示没有子查询。
• table
  ：查询的表是users
  。
• type
  ：range
  ，表示使用了索引范围扫描，查询的条件是age > 30
  。
• possible_keys
  ：查询时，MySQL 可能会使用age_index
  索引来加速查询。
• key
  ：实际使用了age_index
  索引。
• key_len
  ：索引长度为 4 字节。
• rows
  ：预计要扫描 100 行。
• filtered
  ：经过 WHERE 条件筛选后，大约 50% 的行会符合条件。
• Extra
  ：使用了 WHERE 条件。

4.如何优化查询

通过查看EXPLAIN
的输出，你可以发现查询性能的瓶颈，并尝试进行优化。常见的优化方法包括：

1. 创建合适的索引：检查possible_keys
   和key
   列，确保查询可以使用合适的索引。如果没有使用索引，可以考虑为查询条件（如WHERE
   、JOIN
   和ORDER BY
   ）创建索引。

2. 避免全表扫描：如果type
   显示为ALL
   ，则查询在执行时进行了全表扫描，这是性能瓶颈的常见原因。可以通过创建索引来避免全表扫描。

3. 避免使用filesort
   ：如果Extra
   显示为Using filesort
   ，表示查询使用了外部排序操作。你可以优化ORDER BY
   子句，确保索引能够支持排序操作，从而避免filesort
   。

4. 优化连接操作：如果查询涉及多表连接，检查JOIN
   操作的类型（如type
   列）。使用eq_ref
   和ref
   连接通常比ALL
   或index
   更高效。

5.使用EXPLAIN ANALYZE
（MySQL 8.0+）

在 MySQL 8.0 中，EXPLAIN ANALYZE
提供了更详细的执行计划信息，包括每个步骤的实际执行时间。它能够帮助开发者深入分析查询的性能。

sql
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30 ORDER BY name;

EXPLAIN ANALYZE
会在原有的EXPLAIN
输出的基础上，附加实际的执行时间，帮助你识别哪些部分耗时较多。

总结

MySQL 的执行计划（EXPLAIN
）是了解查询性能的关键工具，它能够帮助你理解查询是如何执行的，并指导你如何优化查询。在分析执行计划时，关注以下几点：

• 查询的执行顺序和使用的索引。
• 连接类型和扫描类型。
• 影响性能的Extra
  信息（如Using filesort
  、Using temporary
  等）。
• 合理的索引和查询优化策略。

通过这些信息，你可以针对查询的瓶颈进行优化，从而提高数据库的性能。