likes
comments
collection
share

MySQL的SQL执行计划分析及【关键指标】讲解执行计划介绍 在MySQL中，SQL执行计划（Execution Pla

作者站长头像
站长
· 阅读数 150

执行计划介绍

在MySQL中，SQL执行计划（Execution Plan）是了解查询如何被优化器执行的关键工具。通过分析执行计划，我们可以了解数据库在执行SQL查询时使用的索引、连接顺序、扫描方式等信息，从而优化查询性能。MySQL提供的 EXPLAIN 命令是获取SQL执行计划的主要方式。

以下是详细讲解SQL执行计划分析的步骤和关键字段：

一、获取SQL执行计划

在执行查询之前，可以使用 EXPLAIN 来查看MySQL执行该查询的计划。语法为：

EXPLAIN SELECT ... FROM ... WHERE ...

返回的结果是一张表，其中包含了多列字段，代表了MySQL执行该查询时的各个阶段的详细信息。

二、执行计划中的主要字段解释

  1. id

    1. 含义：查询的执行顺序标识符。每个查询的 id 表示查询执行的顺序，通常从上到下执行。

    2. 解释：

      • 若 id 相同，表示这些步骤是并行执行的。

      • 若 id 不同，数字越大，优先级越低（即越晚执行）。

  2. select_type

    1. 含义：表示查询的类型，区分简单查询、复杂查询（如子查询、联接查询）。

    2. 常见类型：

      • SIMPLE：简单查询，没有子查询或联合查询。

      • PRIMARY：主查询（最外层的查询）。

      • SUBQUERY：子查询。

      • DERIVED：派生表（即子查询生成的临时表）。

      • UNION：联合查询的第二个或后续查询。

      • DEPENDENT SUBQUERY：依赖外部查询的子查询。

  3. table

    1. 含义：正在被访问的表名。

    2. 解释：显示当前查询正在处理哪个表。

  4. type

    1. 含义：访问数据的方式，代表了表访问的类型。

    2. 常见类型（按性能从高到低排序） ：

      • system：表只有一行记录，效率极高。

      • const：表中只有一条记录匹配，效率非常高。

      • eq_ref：对于每个结果，表中只有一条匹配。

      • ref：使用索引访问数据，但可能匹配多条记录。

      • range：使用索引的范围查找（如 BETWEEN、<、>）。

      • index：全索引扫描，通常比全表扫描快。

      • ALL：全表扫描，效率最低。

  5. possible_keys

    1. 含义：查询中可能使用的索引。

    2. 解释：显示MySQL优化器认为可以使用的所有索引。

  6. key

    1. 含义：实际使用的索引。

    2. 解释：该字段显示了MySQL最终决定使用的索引。如果为 NULL，表示未使用索引，可能存在优化空间。

  7. key_len

    1. 含义：使用索引的长度。

    2. 解释：该字段显示了MySQL在查询中使用的索引字段的字节长度，数值越小表示越少数据被扫描。

  8. ref

    1. 含义：显示索引比较的列。

    2. 解释：显示了通过索引列与哪些字段进行比较。

  9. rows

    1. 含义：MySQL预计需要扫描的行数。

    2. 解释：此字段的值越小，查询性能越好。这个数字是MySQL基于表统计信息的估算，不是实际扫描的行数。

  10. filtered

    1. 含义：表示查询条件过滤后剩余的数据行比例，百分比形式。

    2. 解释：例如，filtered=50 表示大约50%的记录符合查询条件。

  11. Extra

    1. 含义：额外的信息，说明查询的执行过程中的特殊操作。

    2. 常见值：

      • Using index：表示查询只使用了索引，不需要访问实际表的数据行（非常好）。

      • Using where：表示查询使用了 WHERE 条件进行过滤。

      • Using temporary：查询过程中使用了临时表，通常意味着查询性能较差。

      • Using filesort：需要进行文件排序，可能会影响性能。

三、SQL执行计划分析示例

假设有如下表结构和SQL查询：

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  order_date DATE,
  amount DECIMAL(10, 2),
  INDEX (user_id),
  INDEX (order_date)
);

EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

使用 EXPLAIN 得到的执行计划：

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEordersrangeuser_id,order_dateorder_date3NULL50020.00Using index
分析：

  1. id: 该查询只有一个简单的 id，表示是一个SIMPLE查询。

  2. select_type: 显示为 SIMPLE，表示该查询不涉及子查询或派生表。

  3. table: 查询的是 orders 表。

  4. type: range 表示MySQL正在使用范围扫描来处理 order_date 条件。

  5. possible_keys: 显示 user_id 和 order_date 两个索引都是可能使用的索引。

  6. key: 实际使用的是 order_date 索引，MySQL决定根据 order_date 进行范围扫描。

  7. key_len: key_len=3，表示MySQL使用了3个字节来处理这个索引。

  8. ref: 显示为 NULL，意味着不是对索引字段进行等值比较。

  9. rows: MySQL预估需要扫描500行。

  10. filtered: 过滤后大约有20%的数据符合查询条件。

  11. Extra: 显示 Using index，表示整个查询只使用了索引，不需要额外访问表数据。

四、优化SQL执行计划

通过分析SQL执行计划，我们可以进行以下优化：

  1. 确保索引被正确使用：例如，如果执行计划显示没有使用索引，可以考虑为查询条件创建合适的索引。

  2. 减少全表扫描：如果执行计划显示 type=ALL，意味着进行了全表扫描。此时应考虑优化查询条件，添加索引。

  3. 避免使用临时表和文件排序：执行计划中的 Extra 字段出现 Using temporary 或 Using filesort 时，可以考虑优化查询，避免不必要的排序和临时表创建。

  4. 合理使用覆盖索引：通过 Extra 字段中的 Using index，我们可以知道是否只通过索引完成查询，避免访问实际表数据，提升性能。

关键指标的关注【最佳实践】

在MySQL执行计划中，有几个关键属性对理解和优化查询性能尤为重要。虽然每个属性都有其特定的作用，但在优化查询时，以下属性是最重要的，需要重点关注：

1. type（访问类型）

type 属性表示MySQL访问表数据的方式，是查询性能优化的核心属性之一。不同的访问类型有不同的效率，以下是常见的 type 值，按效率从高到低排序：

  • system：只有一行记录的表，效率极高。

  • const：针对主键或唯一索引进行查询，表中的某一条记录匹配，性能极高。

  • eq_ref：对外键或唯一索引执行的精确查询，通常涉及联接，效率高。

  • ref：使用普通索引的查询，可以匹配多条记录，性能中等。

  • range：范围扫描，通常用于 BETWEEN、<、> 等范围查询。

  • index：索引扫描，扫描整个索引而不是表，效率相对较低。

  • ALL：全表扫描，效率最低，通常意味着性能问题。

优化建议：理想情况下，应尽量避免 ALL 全表扫描，使用 index 或更高效的类型如 range、ref、eq_ref。

2. key（实际使用的索引）

key 属性表示MySQL在查询中实际使用的索引。使用正确的索引对于查询性能至关重要。如果查询没有使用索引（key 为 NULL），则MySQL可能会进行全表扫描。

优化建议：确保查询使用了合适的索引。如果 key 字段为 NULL，考虑创建适合查询的索引。可以通过分析 possible_keys 来检查MySQL认为可以使用的索引。

3. rows（扫描行数）

rows 属性表示MySQL预计需要扫描的行数。该值是基于表统计信息的估计，不是精确值。一般来说，rows 的值越小，查询的性能越好。

优化建议：对于大型表，尽量减少 rows 的数量，确保查询通过索引缩小了数据范围。如果 rows 值较大，可能需要优化查询条件或添加索引。

4. Extra（额外信息）

Extra 字段包含了许多执行查询时的额外信息，其中一些值对性能有重要提示。常见的 Extra 信息及其含义如下：

  • Using index：查询只使用索引，说明优化良好，因为不需要访问实际数据行。

  • Using where：查询在使用 WHERE 条件进行过滤。

  • Using temporary：表示查询过程中使用了临时表，通常意味着性能不佳，尤其在 GROUP BY 或 ORDER BY 操作中。

  • Using filesort：表示MySQL无法通过索引排序，而是使用文件排序。文件排序通常比索引排序慢，尤其在大数据量时影响明显。

优化建议：理想情况下，Extra 字段中应尽量看到 Using index，而避免 Using temporary 和 Using filesort。如果发现有这些不理想的信息，可以尝试优化查询，减少临时表的使用或通过索引进行排序。

5. select_type（查询类型）

select_type 用于描述查询的类型，帮助识别查询的复杂程度。常见的 select_type 有：

  • SIMPLE：简单的SELECT查询，没有子查询或UNION。

  • PRIMARY：主查询，最外层的查询。

  • SUBQUERY：子查询，通常可能导致性能问题。

  • DERIVED：派生表（子查询中的临时表），可能导致临时表创建。

  • UNION：联合查询，可能引入多个查询合并操作。

优化建议：在查询中尽量避免复杂的 SUBQUERY 或 DERIVED，可以通过将子查询转换为JOIN或优化其他结构来提高效率。

6. filtered（过滤后的数据比例）

filtered 属性表示通过查询条件过滤后的数据比例（百分比）。如果 filtered 值较低，意味着大部分数据被过滤掉，可能存在优化空间。该值是MySQL的估计值，而非精确值。

优化建议：提高 filtered 比例，尽量让过滤条件更为精确，减少需要处理的数据行。

7. possible_keys（可能使用的索引）

possible_keys 属性列出MySQL认为查询可以使用的索引。它列出了所有可能适合该查询的索引，但不一定最终使用这些索引。

优化建议：如果 possible_keys 字段为空，表示MySQL认为当前查询没有可以使用的索引。这通常是查询性能低下的原因之一，此时应该检查表结构，创建合适的索引。

【关键】总结

最重要的SQL执行计划属性包括：

  1. type：决定了MySQL如何访问数据，是查询效率的关键。

  2. key：实际使用的索引，确保查询使用了合适的索引可以显著提升性能。

  3. rows：MySQL预计扫描的行数，越少越好。

  4. Extra：提供了查询过程中是否使用临时表、文件排序等信息，是优化提示的重要来源。

  5. select_type：帮助理解查询的复杂程度，特别是当涉及子查询和派生表时。

  6. filtered：表示过滤后的数据比例，能帮助评估过滤条件的效率。

通过结合这些属性进行分析，可以深入了解查询的执行方式，找到优化的方向，从而提升MySQL的查询性能。

举例演示

type

type的优先级是：system、const、eq_ref、ref、index_merge、range、index、all。

EXPLAIN SELECT id FROM `approvalhead` WHERE `sysId` = 'order' LIMIT 0,1000

MySQL的SQL执行计划分析及【关键指标】讲解执行计划介绍 在MySQL中，SQL执行计划（Execution Pla

EXPLAIN SELECT id FROM `approvalhead` WHERE `sysId` like '%order' LIMIT 0,1000

MySQL的SQL执行计划分析及【关键指标】讲解执行计划介绍 在MySQL中，SQL执行计划（Execution Pla

EXPLAIN SELECT id FROM `approvalhead` WHERE `acceptTime` like '%order' LIMIT 0,1000

MySQL的SQL执行计划分析及【关键指标】讲解执行计划介绍 在MySQL中，SQL执行计划（Execution Pla

转载自:https://juejin.cn/post/7416848881407295526
评论
请登录