深入解析MySQL的EXPLAIN：指标详解与索引优化

baviya
215 阅读8分钟

MySQL 中的 EXPLAIN 语句是一个强大的工具，用于分析和优化 SQL 查询。通过 EXPLAIN，你可以了解 MySQL 查询优化器是如何执行你的查询的，以及是否有可以改进的地方。本文将详细讲解 EXPLAIN 输出的各项指标，并说明如何利用这些指标来优化索引结构和 SQL 语句。

一、EXPLAIN 的基本使用

EXPLAIN 语句用于分析 SELECT 语句的执行计划。当你在 SQL 语句前加上 EXPLAIN 关键字时，MySQL 会返回一张表格，显示查询的执行计划。执行计划揭示了 MySQL 如何访问表、如何使用索引，以及执行查询的顺序。

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

二、EXPLAIN 输出的关键指标

EXPLAIN 输出通常包含以下几个重要的列，每一列都代表一个关键的指标，用于解读查询的执行方式。

2.1 id

id 列表示查询中每个子查询或表的执行顺序。通常，id 值越大，执行的优先级越高。如果两个 id 相同，则意味着它们可以并行执行。

  • id 相同：表示可以并行执行的部分查询。
  • id 不同：表示执行顺序，id 越大，优先级越高。

2.2 select_type

select_type 列表示查询的类型，它描述了查询中每个 SELECT 子句的性质。常见的 select_type 值包括：

  • SIMPLE：简单查询，不包含子查询或联合查询。
  • PRIMARY：主查询，是最外层的查询。
  • SUBQUERY：子查询，出现在 SELECT 或 WHERE 子句中。
  • UNION：联合查询的一部分。
  • DEPENDENT SUBQUERY：依赖于外部查询的子查询。
  • DERIVED：派生表，即子查询中的临时表。

2.3 table

table 列表示查询中访问的表的名称或别名。如果查询涉及多个表，EXPLAIN 会显示它们的连接顺序。

2.4 type

type 列表示查询中表的访问方式，也称为访问类型。访问类型的性能从好到坏依次为：

  • system：表只有一行数据（系统表）。
  • const：查询某一特定行，性能最好，通常用于主键或唯一索引的查询。
  • eq_ref：对每个前驱记录，查询引擎从后继表中读取最多一条记录，通常用于主键或唯一索引连接。
  • ref：对前驱表的每个记录，查询引擎可能会找到多条匹配的行，通常用于非唯一索引连接。
  • range：通过索引范围扫描查找一部分行。
  • index：全索引扫描，即按索引顺序遍历整个索引，但不扫描表。
  • ALL：全表扫描，性能最差。

优化建议：目标是尽量避免 ALL 和 index 类型的访问方式，尽量使用 const、eq_ref、ref 或 range 类型的访问方式。

2.5 possible_keys

possible_keys 列显示查询中可能使用到的索引。这里列出的索引并不一定在查询中实际使用，但它们都是 MySQL 优化器可能考虑的索引。

优化建议：确保查询涉及的列上有合适的索引。如果 possible_keys 为空，表示没有可用的索引，这可能会导致全表扫描。

2.6 key

key 列表示查询实际使用的索引。如果该列为空，表示查询未使用索引，而是进行了全表扫描。

优化建议：确保关键查询使用了适当的索引。可以通过修改查询语句或调整索引结构来改善这一点。

2.7 key_len

key_len 列表示 MySQL 使用的索引长度。该值是由索引的定义和查询条件决定的。

优化建议：key_len 越小，查询越高效。在组合索引中，确保最常用的查询条件出现在索引的前缀部分，以减少 key_len 的长度。

2.8 ref

ref 列显示索引的哪些列被用于查询条件中。它描述了索引列与查询条件的关系。

优化建议：确保 ref 列的值与查询条件中的字段一致，特别是在多表连接中，以确保索引被正确使用。

2.9 rows

rows 列表示 MySQL 估算需要读取多少行才能找到查询的结果。这个值越小越好，表示查询更加高效。

优化建议：如果 rows 数量过大，说明可能需要重新设计查询或优化索引。

2.10 filtered

filtered 列表示在索引过滤之后，返回的记录占扫描到的总记录数的百分比。filtered 值越高，说明筛选条件越严格，数据过滤越充分。

优化建议：尽量提高 filtered 的比例，减少返回的无效数据。

2.11 Extra

Extra 列包含查询优化器的额外信息。常见的值有：

  • Using where：表示查询使用了 WHERE 过滤条件。
  • Using index：表示查询只使用了索引，不需要回表查询数据。
  • Using filesort：表示查询需要额外的排序操作，这是一个性能瓶颈。
  • Using temporary：表示查询使用了临时表，这是一个性能瓶颈。

优化建议：尽量避免 Using filesort 和 Using temporary，可以通过调整查询语句、增加索引或优化表结构来消除这些性能瓶颈。

三、使用EXPLAIN进行索引优化

3.1 分析查询计划

通过 EXPLAIN 输出的 type、key、rows 等列，可以分析查询的执行计划。如果发现使用了全表扫描 (ALL)，或者 rows 数量过大，说明查询可能有优化空间。

3.2 索引覆盖与优化

  • 覆盖索引：在 EXPLAIN 输出中，如果 Extra 列显示 Using index，说明查询已经使用了覆盖索引，可以避免回表查询，提高查询效率。为了实现覆盖索引，可以在查询中选择包含索引的列，或者在创建索引时覆盖更多查询条件。
  • 组合索引：对于涉及多个列的查询，可以创建组合索引，并确保最常用的查询条件放在组合索引的前缀部分。组合索引的顺序对查询的效率影响很大。

3.3 调整查询语句

  • 简化查询：避免复杂的子查询，尽量将子查询转化为连接查询。在 EXPLAIN 中查看 select_type 是否包含 SUBQUERY 或 DEPENDENT SUBQUERY，如果有，可以考虑重写查询语句。
  • 避免排序与临时表：通过索引排序或提前筛选数据，避免 Using filesort 和 Using temporary。例如，可以在需要排序的列上建立索引，或者通过限制查询范围来减少排序操作。

3.4 使用分析工具

MySQL 提供了一些工具，可以帮助分析 EXPLAIN 输出并优化查询。例如：

  • ANALYZE TABLE：分析表中的索引统计信息，帮助优化器做出更好的查询计划。
  • OPTIMIZE TABLE：重新整理表数据，减少碎片，提高查询效率。
  • SHOW PROFILE：详细分析查询执行过程，定位性能瓶颈。

四、实战案例：EXPLAIN 优化实例

假设我们有一个电商数据库，包含以下两个表：orders 和 customers。我们要优化以下查询：

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
AND c.customer_name LIKE 'A%';

4.1 初步分析

EXPLAIN SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
AND c.customer_name LIKE 'A%';

假设 EXPLAIN 输出如下：

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEorangeorder_dateorder_date5NULL500100.00Using where
1SIMPLEcALLPRIMARYNULLNULLNULL1000010.00Using where; Using join buffer (Block Nested Loop)

4.2 问题分析

  • 表 orders：使用了 order_date 索引，type 为 range，表示通过索引范围扫描找到匹配的订单，rows 列显示了需要扫描 500 行。这部分的执行效率相对较好。
  • 表 customers：采用了全表扫描 (ALL)，并且使用了连接缓冲区 (join buffer)，表明未有效使用索引。rows 列显示了需要扫描 10000 行，这是性能瓶颈所在。

4.3 优化策略

  1. 优化 customers 表的查询：

    • 针对 customer_name LIKE 'A%' 的查询条件，考虑创建 customer_name 列的索引。因为 LIKE 查询中使用的前缀是固定字符（A%），因此可以利用索引来加速查询。

    • 创建索引的 SQL 语句：

      CREATE INDEX idx_customer_name ON customers(customer_name);

  2. 重新执行 EXPLAIN 并验证结果：

    • 再次使用 EXPLAIN 分析查询语句，确保 customers 表不再使用全表扫描，且 key 列显示为 idx_customer_name。

    假设优化后的 EXPLAIN 输出如下：

    idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEorangeorder_dateorder_date5NULL500100.00Using where
    1SIMPLEcrefidx_customer_nameidx_customer_name62const100100.00Using where

    经过优化后，customers 表的访问类型变为 ref，并使用了新的索引 idx_customer_name，查询行数大幅减少，性能得到了明显提升。

五、总结

MySQL 的 EXPLAIN 工具提供了一个清晰的窗口，让你可以窥探查询优化器的决策过程。通过详细分析 EXPLAIN 输出的各项指标，你可以识别出性能瓶颈，并采取适当的措施优化索引结构和 SQL 语句。关键在于：

  1. 理解各项指标的含义：了解 type、key、rows、filtered 等列的作用和表现。
  2. 合理设计索引：根据查询模式优化索引结构，使用覆盖索引、组合索引等技术提高查询效率。
  3. 避免常见的性能陷阱：如全表扫描、排序操作、临时表的使用等，通过优化查询和表结构来消除这些瓶颈。

通过有效利用 EXPLAIN，你可以显著提升 MySQL 数据库的查询性能，为应用程序提供更加流畅的用户体验

avatar
文章
阅读
粉丝