mysql的EXPLAIN的讲解

EXPLAIN详解

理解 SQL 执行计划（Execution Plan）是优化 SQL 查询性能的关键步骤。执行计划详细描述了数据库如何执行查询，包括使用的索引、连接类型、扫描方式等。以下是如何解释和关注 SQL 执行计划的关键点：

1. 获取执行计划

在 MySQL 中，可以使用 EXPLAIN 关键字来获取查询的执行计划。

示例：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

2. 关注的关键字段

执行计划输出包含多个字段，每个字段提供了不同的信息。以下是需要重点关注的字段：

2.1 id

• 含义：查询的标识符，用于区分不同的查询块（如子查询）。
• 关注点：
  • 每个查询块都有一个唯一的 id。
  • 子查询的 id 通常大于外部查询。

2.2 select_type

• 含义：查询的类型。
• 常见值：
  • SIMPLE：简单查询，不包含子查询或 UNION。
  • PRIMARY：最外层的查询。
  • SUBQUERY：子查询中的第一个 SELECT。
  • DERIVED：派生表（FROM 子句中的子查询）。
  • UNION：UNION 中的第二个或后续的 SELECT。
  • UNION RESULT：UNION 的结果。
• 关注点：
  • 确认查询的复杂性。
  • 检查是否有不必要的子查询或复杂的查询结构。

2.3 table

• 含义：查询涉及的表。
• 关注点：
  • 确认查询涉及的表是否正确。
  • 检查是否有不必要的表连接。

2.4 partitions

• 含义：查询涉及的分区。
• 关注点：
  • 确认分区是否正确使用。
  • 检查是否有不必要的分区扫描。

2.5 type

• 含义：连接类型，表示 MySQL 如何查找表中的行。
• 常见值：
  • system：表中只有一行（系统表）。
  • const：通过唯一索引查找单行。
  • eq_ref：通过唯一索引或主键查找单行。
  • ref：通过非唯一索引查找多行。
  • range：使用索引范围查找。
  • index：全索引扫描。
  • ALL：全表扫描。
• 关注点：
  • ALL：表示全表扫描，性能较差，应尽量避免。
  • range, ref, eq_ref, const：表示使用索引，性能较好。
  • index：表示全索引扫描，虽然比全表扫描快，但仍需优化。

2.6 possible_keys

• 含义：可能使用的索引。
• 关注点：
  • 确认查询中使用的列是否有合适的索引。
  • 检查是否有未使用的索引，可能需要优化。

2.7 key

• 含义：实际使用的索引。
• 关注点：
  • 确认查询是否使用了预期的索引。
  • 如果 key 为 NULL，表示未使用索引，需要优化。

2.8 key_len

• 含义：使用的索引长度。
• 关注点：
  • 确认索引长度是否合理。
  • 较短的索引长度通常表示更高效的索引使用。

2.9 ref

• 含义：与索引比较的列或常量。
• 关注点：
  • 确认比较的列或常量是否合适。
  • 如果 ref 为 const 或 func，表示使用了常量或函数，可能影响索引使用。

2.10 rows

• 含义：估计要检查的行数。
• 关注点：
  • 较小的行数表示更高效的查询。
  • 较大的行数可能表示索引未正确使用或查询条件不准确。

2.11 filtered

• 含义：过滤后的行数百分比。
• 关注点：
  • 较高的百分比表示过滤效果较好。
  • 较低的百分比表示过滤效果不佳，可能需要优化查询条件。

2.12 Extra

• 含义：额外的信息。
• 常见值：
  • Using where：在存储引擎层过滤数据。
  • Using index：覆盖索引，只使用索引而不需要访问表。
  • Using temporary：使用临时表。
  • Using filesort：使用文件排序。
  • Using index condition：使用索引条件过滤。
• 关注点：
  • Using where：表示在存储引擎层进行过滤，可能需要优化。
  • Using index：表示覆盖索引，性能较好。
  • Using temporary 和 Using filesort：表示使用临时表和文件排序，性能较差，需要优化。
  • Using index condition：表示使用索引条件过滤，性能较好。

3. 示例分析

原始查询：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

执行计划输出：

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

分析：

• type: ALL 表示全表扫描，性能较差。
• key: NULL 表示未使用索引。
• Extra: Using where 表示在存储引擎层进行过滤。

优化：

• 添加复合索引：

  CREATE INDEX idx_customer_id_order_date ON orders(customer_id, order_date);
  

验证：

• 再次执行 EXPLAIN：

  EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';
  

优化后的执行计划输出：

+----+-------------+--------+------------+-------+--------------------+--------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys      | key                | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | range | idx_customer_id_order_date | idx_customer_id_order_date | 10      | const       |   10 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+-------------+------+----------+-------+

分析：

• type: range 表示使用索引范围查找。
• key: idx_customer_id_order_date 表示使用了预期的索引。
• Extra: NULL 表示没有额外的过滤或排序操作。

4. 其他注意事项

• 覆盖索引：

  • 如果查询的所有列都在索引中，可以使用覆盖索引（Using index），提高查询性能。
  • 示例：

    EXPLAIN SELECT customer_id, order_date FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';
    

• 避免全表扫描：

  • 确保查询条件使用索引，避免 type 为 ALL。
  • 示例：

    EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';
    

• 减少临时表和文件排序：

  • 避免 Using temporary 和 Using filesort，这些操作通常性能较差。
  • 示例：

    EXPLAIN SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date;
    

• 优化连接类型：

  • 尽量使用 const, eq_ref, ref 等高效的连接类型。
  • 示例：

    EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
    

总结

通过仔细分析 SQL 执行计划中的关键字段，可以识别查询中的性能瓶颈，并采取相应的优化措施。重点关注以下几点：

• type: 连接类型，确保使用索引。
• key: 实际使用的索引，确保使用预期的索引。
• rows: 估计要检查的行数，确保行数较少。
• Extra: 额外信息，避免 Using where, Using temporary, Using filesort 等。

通过这些步骤，可以有效地优化 SQL 查询，提高数据库性能。