执行计划(Execution Plan)是数据库管理系统(DBMS)用来描述SQL查询执行步骤的详细说明。它揭示了查询优化器(Query Optimizer)如何决定访问和处理数据的最佳路径。执行计划有助于了解查询的性能瓶颈,并提供优化的依据。
执行计划的组成部分
执行计划通常包括以下组成部分:
- 操作类型(Operation Type):如全表扫描(Full Table Scan)、索引扫描(Index Scan)、嵌套循环(Nested Loop)等。
- 对象(Objects):如表或索引。
- 访问方法(Access Methods):如全表扫描、索引范围扫描等。
- 行数估计(Rows Estimate):查询优化器估计的返回行数。
- 成本(Cost):执行查询的相对成本。
- 过滤条件(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有不同的生成和查看执行计划的方法,但基本原理是相似的。掌握执行计划的分析方法,可以显著提高数据库查询的性能。