SQL 执行计划简介

SQL 执行计划是数据库用来执行 SQL 查询的一系列步骤的描述，显示了 SQL 查询是如何被优化器解析和执行的。通过分析执行计划，可以了解 SQL 的执行效率，并找到优化的方向。

---

如何查看执行计划

使用 EXPLAIN 或 EXPLAIN ANALYZE 查看 SQL 查询的执行计划。

1. 基本使用

EXPLAIN SELECT * FROM table_name WHERE column = 'value';

2. 使用 EXPLAIN ANALYZE

EXPLAIN ANALYZE 会真正执行查询并提供实际执行时间（MySQL 8.0+）。

EXPLAIN ANALYZE SELECT * FROM table_name WHERE column = 'value';

---

执行计划的关键字段解读

字段名	含义
id	查询中操作的标识符，数字越大优先级越高。
select_type	查询类型，如SIMPLE、PRIMARY、SUBQUERY、UNION等。
table	查询涉及的表名。
type	访问类型，表示表的访问方式，性能从好到差依次为：system>const>eq_ref>ref>range>index>ALL。
possible_keys	查询可能使用的索引。
key	实际使用的索引。如果为空，表示没有使用索引。
key_len	使用的索引的长度（字节数）。
ref	使用的列或常量与索引的比较方式。
rows	预估扫描的行数，数值越小越好。
filtered	预估满足条件的记录比例（百分比）。
Extra	额外信息，提供优化建议或可能的问题，例如：
	-Using index：使用覆盖索引，无需回表。
	-Using temporary：使用了临时表，通常需要优化。
	-Using filesort：使用了外部排序，需要优化。

---

常见访问类型（****type 字段）

类型	说明
system	表仅有一行，效率最高。
const	表中最多只有一条符合条件的记录，例如通过主键或唯一索引查询。
eq_ref	每次从驱动表取一条记录，在被驱动表中最多匹配一条，通常用于主键或唯一索引的多表连接查询。
ref	非唯一索引扫描，返回匹配某一值的所有记录，效率较高。
range	索引范围扫描，常见于使用范围条件（如BETWEEN、<、>）的查询。
index	全索引扫描，类似于全表扫描，但扫描的是索引而非数据表。
ALL	全表扫描，性能最差。

---

Extra 字段的常见值及含义

值	含义
Using index	查询字段被索引覆盖，避免了回表操作。
Using where	使用了 WHERE 条件过滤数据。
Using temporary	查询使用了临时表，通常出现在排序和分组查询中。
Using filesort	查询使用了文件排序，通常需要优化（可用索引排序代替）。
Using index condition	索引条件下推优化，部分列过滤条件可通过索引完成，未完全覆盖。
Using join buffer	使用了连接缓冲区，通常表示没有索引或索引未被有效利用。

---

优化执行计划的建议

1. 选择合适的索引：

   • 确保查询条件中的列被正确索引。
   • 使用覆盖索引减少回表操作。

2. 优化查询语句：

   • 避免 SELECT *，明确指定所需列。
   • 避免使用函数或隐式转换（如 WHERE YEAR(column) = 2023）。
   • 尽量减少子查询，可使用 JOIN 替代。

3. 优化表结构：

   • 规范化设计，减少冗余数据。
   • 大表可以考虑分区或分库分表。

4. 避免临时表和文件排序：

   • 对于 GROUP BY 和 ORDER BY，确保相关字段有合适的索引。

5. 通过分析调整语句：

   • 使用 EXPLAIN 找到瓶颈点，根据执行计划优化查询逻辑。

通过执行计划的分析和调整，可以大幅度提升 SQL 的执行效率！

EXPLAIN 是 SQL 中用于分析查询执行计划的命令。通过 EXPLAIN，你可以了解数据库如何执行你的查询，包括表的访问顺序、使用的索引、连接方式等信息。这对于优化查询性能非常有帮助。

1. EXPLAIN 的基本用法

在大多数 SQL 数据库中，你可以在查询语句前加上 EXPLAIN 来查看执行计划。例如：

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

2. EXPLAIN 的输出内容

不同数据库的 EXPLAIN 输出可能有所不同，但通常包含以下几个关键信息：

• id：查询的标识符，用于区分不同的查询部分。

• select_type：查询的类型，如 SIMPLE、PRIMARY、SUBQUERY、DERIVED 等。

• table：当前操作的表。

• type：连接类型，表示 MySQL 查找表中行的方式。常见的类型包括：

  • ALL：全表扫描，性能最差。
  • index：索引全扫描，性能较差。
  • range：使用索引范围扫描，性能较好。
  • ref：使用非唯一索引扫描，性能较好。
  • eq_ref：使用唯一索引扫描，性能最佳。
  • const：常量访问，性能最佳。

• possible_keys：可能使用的索引。

• key：实际使用的索引。

• key_len：使用的索引长度。

• ref：与索引比较的列或常量。

• rows：预计需要扫描的行数。

• Extra：附加信息，如是否使用了临时表、文件排序等。

3. 示例

假设有一个 employees 表，结构如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    INDEX idx_department (department_id)
);

执行以下查询并使用 EXPLAIN 分析：

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

输出可能如下：

+----+-------------+-----------+-------+---------------+-----------------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key             | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+-----------------+---------+------+------+-------------+
| 1  | SIMPLE      | employees | ref   | idx_department| idx_department  | 4       | const| 100  | NULL        |
+----+-------------+-----------+-------+---------------+-----------------+祯的4个字节的长度，加上 `department_id` 的 4 个字节，总共是 8 个字节。因此，`key_len` 为 8。

- **ref**：显示了哪些列或常量与索引一起使用。在这个例子中，`ref` 是 `const`，表示查询条件中的 `department_id` 是一个常量值（即 5）。如果是基于另一列的比较，这里会显示那列的名字。

- **rows**：估计需要读取的行数。在这个例子中，MySQL 估计需要读取 100 行来找到匹配的记录。这个数字越小，查询通常越快。

- **Extra**：提供了一些额外的信息，帮助进一步分析查询性能。`NULL` 表示没有额外的信息。其他可能的值包括：
  - `Using where`：表示 MySQL 服务器层面对结果进行了过滤。
  - `Using index`：表示查询只使用了索引树中的信息，而没有回表查询数据行，这通常发生在覆盖索引的情况下。
  - `Using temporary`：表示 MySQL 需要创建一个临时表来处理查询结果，这通常发生在复杂的 `GROUP BY` 或 `ORDER BY` 操作中。
  - `Using filesort`：表示 MySQL 需要进行额外的排序操作，这可能会影响查询性能。

### 4. 优化建议

通过 `EXPLAIN` 的输出，你可以发现查询的性能瓶颈，并采取相应的优化措施：

- **全表扫描 (`ALL`)**：尽量避免全表扫描，可以通过添加合适的索引来优化。
- **索引未使用 (`NULL`)**：检查查询条件是否使用了索引，如果没有，可以考虑添加索引。
- **临时表和文件排序 (`Using temporary` 和 `Using filesort`)**：这些通常表示查询性能较差，可以通过优化查询语句或添加索引来解决。

### 5. 其他数据库的 `EXPLAIN`

不同的数据库系统可能有不同的 `EXPLAIN` 输出格式和选项。例如：

- **PostgreSQL**：使用 `EXPLAIN` 或 `EXPLAIN ANALYZE` 来查看执行计划。
- **SQL Server**：使用 `SET SHOWPLAN_ALL ON` 或 `SET STATISTICS IO ON` 来查看执行计划。
- **Oracle**：使用 `EXPLAIN PLAN FOR` 和 `DBMS_XPLAN.DISPLAY` 来查看执行计划。

了解并熟练使用 `EXPLAIN` 是数据库性能调优的重要工具之一。通过分析执行计划，你可以更好地理解查询的性能瓶颈，并采取相应的优化措施。