Oracle（71）什么是执行计划（Execution Plan）？

执行计划（Execution Plan）是数据库管理系统（DBMS）用来描述SQL查询执行步骤的详细说明。它揭示了查询优化器（Query Optimizer）如何决定访问和处理数据的最佳路径。执行计划有助于了解查询的性能瓶颈，并提供优化的依据。

执行计划的组成部分

执行计划通常包括以下组成部分：

1. 操作类型（Operation Type）：如全表扫描（Full Table Scan）、索引扫描（Index Scan）、嵌套循环（Nested Loop）等。
2. 对象（Objects）：如表或索引。
3. 访问方法（Access Methods）：如全表扫描、索引范围扫描等。
4. 行数估计（Rows Estimate）：查询优化器估计的返回行数。
5. 成本（Cost）：执行查询的相对成本。
6. 过滤条件（Filter Conditions）：用于筛选数据的条件。

如何生成执行计划

不同的DBMS有不同的方法来生成和查看执行计划。以下是一些常见的DBMS的示例代码：

Oracle

在Oracle中，可以使用EXPLAIN PLAN语句生成执行计划，并使用DBMS_XPLAN包查看执行计划。

-- 生成执行计划
EXPLAIN PLAN FOR
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

MySQL

在MySQL中，可以使用EXPLAIN语句生成和查看执行计划。

-- 生成并查看执行计划
EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

PostgreSQL

在PostgreSQL中，可以使用EXPLAIN语句生成执行计划，并使用ANALYZE选项查看实际执行的详细信息。

-- 生成执行计划
EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

-- 生成并查看实际执行计划
EXPLAIN ANALYZE
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

示例和分析

假设有一个名为employees的表，其结构如下：

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50),
    emp_department VARCHAR(50)
);

INSERT INTO employees (emp_name, emp_department)
VALUES ('Alice', 'IT'), ('Bob', 'HR'), ('Charlie', 'IT'), ('David', 'Finance');

我们为emp_department列创建一个索引：

CREATE INDEX idx_emp_department ON employees(emp_department);

然后，我们执行以下查询：

SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

Oracle示例

生成并查看执行计划：

EXPLAIN PLAN FOR
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

假设输出如下：

-----------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     2 |    40 |     3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     2 |    40 |     3 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP_DEPARTMENT  |     2 |       |     1 |
-----------------------------------------------------------------------------------

分析：

• Operation：表示执行操作类型。
• Name：表示操作对象，如表或索引。
• Rows：表示查询优化器估计的返回行数。
• Cost：表示执行查询的相对成本。
• Filter Conditions：在索引范围扫描中使用的过滤条件。

MySQL示例

生成并查看执行计划：

EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

假设输出如下：

+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_emp_department   | idx_emp_department   | 53      | const |    2 |   100.00 | Using where |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+

分析：

• id：查询中每个SELECT的标识符。
• select_type：查询类型，如SIMPLE、PRIMARY、UNION等。
• table：访问的表。
• type：连接类型，如ALL、index、range等。
• possible_keys：查询可能使用的索引。
• key：实际使用的索引。
• key_len：使用的索引长度。
• ref：列与索引用作比较。
• rows：查询优化器估计的返回行数。
• filtered：应用表条件过滤后的行百分比。
• Extra：附加信息，如Using where、Using index等。

PostgreSQL示例

生成并查看执行计划：

EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

假设输出如下：

                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using idx_emp_department on employees  (cost=0.12..8.14 rows=2 width=36)
   Index Cond: (emp_department = 'IT'::text)

分析：

• QUERY PLAN：显示查询的执行计划。
• Index Scan：表示使用索引扫描。
• cost：从启动到获取所有结果行的代价范围。
• rows：查询优化器估计的返回行数。
• width：每行的平均字节数。
• Index Cond：索引扫描使用的条件。

总结

执行计划是理解和优化SQL查询性能的重要工具。通过分析执行计划，可以识别查询的性能瓶颈，并采取相应的优化措施，如创建索引、调整查询结构等。不同的DBMS有不同的生成和查看执行计划的方法，但基本原理是相似的。掌握执行计划的分析方法，可以显著提高数据库查询的性能。