执行计划介绍

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

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

一、获取SQL执行计划

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

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

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

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

id

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

解释：

select_type

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

常见类型：

table

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

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

type

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

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

possible_keys

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

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

key

含义：实际使用的索引。

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

key_len

含义：使用索引的长度。

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

ref

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

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

rows

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

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

filtered

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

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

Extra

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

常见值：

三、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

SIMPLE

orders

range

user_id,order_date

order_date

NULL

500

20.00

Using index

分析：

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

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

table: 查询的是 orders 表。

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

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

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

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

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

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

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

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

四、优化SQL执行计划

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

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

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

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

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