一、啥是执行计划和 EXPLAIN
咱先来说说执行计划是个啥。简单来讲，当你写了一条 SQL 语句让 PostgreSQL 去执行的时候，数据库不会马上就开始干活，而是会先规划一下怎么执行这条语句，就好像你出门旅游前要先规划个路线一样。这个规划出来的内容就是执行计划，它会告诉数据库先干啥、后干啥，怎么把你的 SQL 语句高效地执行完。
那 EXPLAIN 又是啥呢？它就是 PostgreSQL 提供给咱们开发者的一个工具，能让我们看到这个执行计划到底长啥样。通过 EXPLAIN，我们就能知道数据库是怎么执行我们写的 SQL 语句的，从而找出可能存在的性能问题，然后对 SQL 语句进行优化。
二、EXPLAIN 的基本使用
简单示例
下面咱们来看看 EXPLAIN 怎么用。假设我们有一个表叫 employees，里面存着员工的信息，有 id、name、department 这些字段。现在我们想找出所有在 “技术部” 工作的员工。
-- 技术栈：PostgreSQL
-- 创建 employees 表
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(100)
);
-- 插入一些示例数据
INSERT INTO employees (name, department) VALUES ('张三', '技术部');
INSERT INTO employees (name, department) VALUES ('李四', '市场部');
INSERT INTO employees (name, department) VALUES ('王五', '技术部');
-- 使用 EXPLAIN 查看查询的执行计划
EXPLAIN SELECT * FROM employees WHERE department = '技术部';
在这个示例里，我们先用 CREATE TABLE 语句创建了 employees 表，然后用 INSERT INTO 插入了几条示例数据。最后，用 EXPLAIN 关键字加上我们的查询语句，就能看到这条查询语句的执行计划了。
执行计划的结果解读
当我们执行上面的 EXPLAIN 语句后，会得到类似下面这样的结果：
QUERY PLAN
--------------------------------------------------------------
Seq Scan on employees (cost=0.00..15.50 rows=1 width=44)
Filter: (department = '技术部'::text)
这里面，Seq Scan 表示顺序扫描，也就是说数据库会一行一行地去扫描 employees 表，看看哪一行的 department 字段是 “技术部”。cost=0.00..15.50 是这个操作的成本估算，rows=1 表示估计会返回 1 行数据，width=44 是返回数据的每行大概的字节数。Filter: (department = '技术部'::text) 说明在扫描的过程中会用这个条件来过滤数据。
三、通过执行计划优化 SQL 语句性能
索引优化
从上面的执行计划我们可以看到，数据库用的是顺序扫描，当表的数据量很大的时候，这种方式会很慢。这时候我们就可以考虑用索引来优化查询。
-- 技术栈：PostgreSQL
-- 创建 department 字段的索引
CREATE INDEX idx_employees_department ON employees (department);
-- 再次使用 EXPLAIN 查看查询的执行计划
EXPLAIN SELECT * FROM employees WHERE department = '技术部';
执行上面的代码后，我们再看执行计划，可能会得到类似这样的结果：
QUERY PLAN
------------------------------------------------------------------
Bitmap Heap Scan on employees (cost=4.00..12.25 rows=1 width=44)
Recheck Cond: (department = '技术部'::text)
-> Bitmap Index Scan on idx_employees_department (cost=0.00..4.00 rows=1 width=0)
Index Cond: (department = '技术部'::text)
现在数据库用了 Bitmap Heap Scan 和 Bitmap Index Scan，这是利用索引来查找数据的方式，比顺序扫描要快很多。
避免全表扫描
有时候我们写的 SQL 语句可能会不小心触发全表扫描，导致性能很差。比如下面这个例子：
-- 技术栈：PostgreSQL
-- 这个查询可能会触发全表扫描
EXPLAIN SELECT * FROM employees WHERE UPPER(department) = '技术部';
在这个查询里，我们用了 UPPER 函数对 department 字段进行了转换，这样数据库就没办法用索引来查找数据了，只能进行全表扫描。我们可以改成下面这样：
-- 技术栈：PostgreSQL
-- 优化后的查询，避免全表扫描
EXPLAIN SELECT * FROM employees WHERE department = UPPER('技术部');
这样就可以继续利用索引，提高查询性能。
四、应用场景
日常开发
在日常开发中，我们经常需要写各种 SQL 查询语句。当我们发现某个查询语句执行很慢的时候，就可以用 EXPLAIN 来看看执行计划，找出性能瓶颈，然后进行优化。比如说，我们在开发一个员工管理系统，需要根据员工的部门和职位来查询员工信息，这时候就可以用 EXPLAIN 来优化这个复杂的查询。
-- 技术栈：PostgreSQL
-- 复杂查询示例
EXPLAIN SELECT * FROM employees
JOIN departments ON employees.department_id = departments.id
WHERE departments.name = '技术部' AND employees.position = '开发工程师';
数据库优化
对于数据库管理员来说，定期检查数据库中查询语句的执行计划是很有必要的。通过分析执行计划，可以找出那些经常被执行但性能很差的查询语句，然后对数据库的索引、表结构等进行优化，提高整个数据库的性能。
五、技术优缺点
优点
- 直观：通过 EXPLAIN 我们可以很直观地看到数据库是怎么执行我们的 SQL 语句的，能清楚地知道每个操作的成本和执行顺序。
- 便于优化：根据执行计划，我们可以很容易地找出性能瓶颈，然后针对性地对 SQL 语句、索引等进行优化。
- 免费：PostgreSQL 是开源的，EXPLAIN 作为它的一个功能，我们可以免费使用。
缺点
- 成本估算不准确：执行计划里的成本估算只是一个大概的数值，实际执行的成本可能会因为数据分布、硬件环境等因素而有所不同。
- 学习成本：对于初学者来说，解读执行计划可能会有一定的难度，需要花时间去学习和理解各种操作符和术语。
六、注意事项
执行计划会变
执行计划不是一成不变的，它会随着数据库的数据量、数据分布、索引情况等因素的变化而变化。所以我们要定期检查执行计划，确保查询语句一直保持良好的性能。
不要过度依赖索引
虽然索引可以提高查询性能，但也不是索引越多越好。过多的索引会占用更多的存储空间，而且在插入、更新、删除数据的时候，数据库需要维护这些索引，会影响这些操作的性能。
考虑硬件环境
执行计划的成本估算和实际执行性能会受到硬件环境的影响。在性能测试和优化的时候，要尽量在和生产环境相似的硬件环境中进行。
七、文章总结
通过这篇文章，我们了解了 PostgreSQL 执行计划和 EXPLAIN 的基本概念，学会了如何使用 EXPLAIN 来查看查询语句的执行计划，以及怎么通过执行计划来优化 SQL 语句的性能。同时，我们也知道了 EXPLAIN 在日常开发和数据库优化中的应用场景，以及它的优缺点和使用时的注意事项。希望大家在以后的开发工作中，能充分利用 EXPLAIN 这个工具，写出高性能的 SQL 语句。
评论