作者：卢军好2602912493 | 来源：互联网 | 2023-09-25 11:33
经常有人问我如何看懂执行计划，如何才能抓住计划中的瓶颈点，要完全搞懂一个执行计划还是需要丰富的背景知识，但是对于测试或开发并没有完备的数据库知识，那么怎样看懂PostgreSQL的执行计划呢，这里介绍一个网站，以图形和数据分析的方式解释每个执行计划，小白也能轻松掌握，抓住瓶颈优化SQL。
构造测试数据
postgres=# SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
--创建100个班级
postgres=# CREATE TABLE t_class(classid INT,classname VARCHAR(30),createtime TIMESTAMP(0) NOT NULL DEFAULT NOW());
CREATE TABLE
postgres=# INSERT INTO t_class(classid,classname) SELECT t.id,'一年级' || t.id || '班' FROM (SELECT generate_series(1,100) AS id) t;
INSERT 0 100
--每个班级50个学生
postgres=# CREATE TABLE t_student(studnetid INT,studentname VARCHAR(30),classid INT,createtime TIMESTAMP(0) NOT NULL DEFAULT NOW());
CREATE TABLE
postgres=# INSERT INTO t_student(studnetid,studentname,classid)
postgres-# SELECT row_number() over(),substr(md5(random()::text),1,5),c.classid FROM t_class c,(SELECT generate_series(1,50) AS id) t;
INSERT 0 5000
postgres=# TABLE t_class LIMIT 10;
classid | classname | createtime
---------+------------+---------------------
1 | 一年级1班 | 2021-07-21 10:15:44
2 | 一年级2班 | 2021-07-21 10:15:44
3 | 一年级3班 | 2021-07-21 10:15:44
4 | 一年级4班 | 2021-07-21 10:15:44
5 | 一年级5班 | 2021-07-21 10:15:44
6 | 一年级6班 | 2021-07-21 10:15:44
7 | 一年级7班 | 2021-07-21 10:15:44
8 | 一年级8班 | 2021-07-21 10:15:44
9 | 一年级9班 | 2021-07-21 10:15:44
10 | 一年级10班 | 2021-07-21 10:15:44
(10 rows)
postgres=# TABLE t_student LIMIT 10;
studnetid | studentname | classid | createtime
-----------+-------------+---------+---------------------
1 | 9a443 | 1 | 2021-07-21 10:15:44
2 | 1fee5 | 1 | 2021-07-21 10:15:44
3 | 244dd | 1 | 2021-07-21 10:15:44
4 | 99fee | 1 | 2021-07-21 10:15:44
5 | aa69b | 1 | 2021-07-21 10:15:44
6 | a6b51 | 1 | 2021-07-21 10:15:44
7 | bec83 | 1 | 2021-07-21 10:15:44
8 | 084ef | 1 | 2021-07-21 10:15:44
9 | 8ceae | 1 | 2021-07-21 10:15:44
10 | e6827 | 1 | 2021-07-21 10:15:44
(10 rows)
postgres=# VACUUM ANALYZE t_class,t_student;
VACUUM
--生成JSON格式的计划
postgres=# EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
postgres-# SELECT * FROM t_class c,t_student s WHERE c.classid = s.classid AND s.studentname = '8ceae';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Nested Loop", +
"Parallel Aware": false, +
"Join Type": "Inner", +
"Startup Cost": 0.00, +
"Total Cost": 97.75, +
"Plan Rows": 1, +
"Plan Width": 48, ...计划太长，此处省略一万字
分析执行计划
访问 https://explain.dalibo.com/ 网站，把以上生成的执行计划贴到此网站，提交即可
这里就可以看到图形化的执行计划了，页面信息非常丰富，应有尽有
可以点开各个节点展开详细信息（节点中的选项卡可以切换General\IO&Buffers\Output\Workers\Misc等）
这里是执行计划的文本信息
这里是查询语句
这个页面查看执行计划的统计信息，包括每个表的统计信息，节点统计信息，索引统计信息等，这里可以清晰的分析出性能的瓶颈，这里很明显顺序扫描t_student表时间占比达到了92%（1.28ms），在查询条件上面应该建一个索引
postgres=# CREATE INDEX idx_studentname_t_student ON t_student(studentname);
CREATE INDEX
postgres=# VACUUM ANALYZE t_student;
VACUUM
查看建索引后的执行计划，执行时间从1.38毫秒下降到了0.168毫秒，对t_student表的扫描占比下降到了21%（0.036ms）
总结
以上是个很简单的例子，不借助上面的网站也容易分析，但是如果对于那种成百上千行的SQL语句，即使是老司机文本分析也不是那么简单，有这么好的工具我们应该多加利用来提高生产力。
京公网安备 11010802041100号