文章目录
MySQL 执行计划
Oracle 执行计划
SQL Server 执行计划
PostgreSQL 执行计划
SQLite 执行计划
执行计划(execution plan,也叫查询计划或者解释计划)是数据库执行 SQL 语句的具体步骤,例如通过索引还是全表扫描访问表中的数据,连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想,我们首先应该查看它的执行计划。本文主要介绍如何在各种数据库中获取和理解执行计划,并给出进一步深入分析的参考文档。
现在许多管理和开发工具都提供了查看图形化执行计划的功能,例如 MySQL Workbench、Oracle SQL Developer、SQL Server Management Studio、DBeaver 等;不过我们不打算使用这类工具,而是介绍利用数据库提供的命令查看执行计划。
我们先给出在各种数据库中查看执行计划的一个简单汇总:本文使用的示例表和数据可以点击链接《SQL 入门教程》示例数据库。
MySQL 执行计划
MySQL 中获取执行计划的方法很简单,就是在 SQL 语句的前面加上EXPLAIN关键字:
EXPLAIN
SELECT e.first_name,e.last_name,e.salary,d.department_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
WHERE e.salary > 15000;
执行该语句将会返回一个表格形式的执行计划,包含了 12 列信息:
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | e | ALL | emp_department_ix | 107 | 33.33 | Using where | ||||
1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | hrdb.e.department_id | 1 | 100 |
MySQL 中的EXPLAIN支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。
接下来,我们要做的就是理解执行计划中这些字段的含义。下表列出了 MySQL 执行计划中的各个字段的作用:对于上面的示例,只有一个 SELECT 子句,id 都为 1;首先对 employees 表执行全表扫描(type = ALL),处理了 107 行数据,使用 WHERE 条件过滤后预计剩下 33.33% 的数据(估计不准确);然后针对这些数据,依次使用 departments 表的主键(key = PRIMARY)查找一行匹配的数据(type = eq_ref、rows = 1)。
使用 MySQL 8.0 新增的 ANALYZE 选项可以显示实际执行时间等额外的信息:
EXPLAIN ANALYZE
SELECT e.first_name,e.last_name,e.salary,d.department_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
WHERE e.salary > 15000;
-> Nested loop inner join (cost=23.43 rows=36) (actual time=0.325…1.287 rows=3 loops=1)
-> Filter: ((e.salary > 15000.00) and (e.department_id is not null)) (cost=10.95 rows=36) (actual time=0.281…1.194 rows=3 loops=1)
-> Table scan on e (cost=10.95 rows=107) (actual time=0.266…0.716 rows=107 loops=1)
-> Single-row index lookup on d using PRIMARY (department_id=e.department_id) (cost=0.25 rows=1) (actual time=0.013…0.015 rows=1 loops=3)
其中,Nested loop inner join 表示使用嵌套循环连接的方式连接两个表,employees 为驱动表。cost 表示估算的代价,rows 表示估计返回的行数;actual time 显示了返回第一行和所有数据行花费的实际时间,后面的 rows 表示迭代器返回的行数,loops 表示迭代器循环的次数。
关于 MySQL EXPLAIN 命令的使用和参数,可以参考 MySQL 官方文档 EXPLAIN 语句。
关于 MySQL 执行计划的输出信息,可以参考 MySQL 官方文档理解查询执行计划。
Oracle 执行计划
Oracle 中提供了多种查看执行计划的方法,本文使用以下方式:
使用EXPLAIN PLAN FOR命令生成并保存执行计划;
显示保存的执行计划。
首先,生成执行计划:
EXPLAIN PLAN FOR
SELECT e.first_name,e.last_name,e.salary,d.department_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
WHERE e.salary > 15000;
EXPLAIN PLAN FOR命令不会运行 SQL 语句,因此创建的执行计划不一定与执行该语句时的实际计划相同。
该命令会将生成的执行计划保存到全局的临时表 PLAN_TABLE 中,然后使用系统包 DBMS_XPLAN 中的存储过程格式化显示该表中的执行计划。以下语句可以查看当前会话中的最后一个执行计划:
SELECT * FROM TABLE(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT |
---|
Plan hash value: 1343509718 |
|
--------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
--------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 44 | 1672 | 6 (17)| 00:00:01 ||
| 1 | MERGE JOIN | | 44 | 1672 | 6 (17)| 00:00:01 ||
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 ||
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 ||
|* 4 | SORT JOIN | | 44 | 968 | 4 (25)| 00:00:01 ||
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 44 | 968 | 3 (0)| 00:00:01 ||
--------------------------------------------------------------------------------------------|
|
|
4 - access(“E”.“DEPARTMENT_ID”=“D”.“DEPARTMENT_ID”) |
filter(“E”.“DEPARTMENT_ID”=“D”.“DEPARTMENT_ID”) |
5 - filter(“E”.“SALARY”>15000) |
Oracle 中的EXPLAIN PLAN FOR支持 SELECT、UPDATE、INSERT 以及 DELETE 语句。
接下来,我们同样需要理解执行计划中各种信息的含义: