快一年没写博客了,最近闹肺炎,在家宅了数日,百无聊赖,趁这个机会也可以给自己充充电,今天刚好学习执行计划部分,简单的做下总结

在平时的开发中,使用explain坐来显示SQL的执行计划,可以定位慢SQL的问题原因。

explain 命令

官方给出的explain命令格式如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:
-- ANALYZE选项通过实际执行的SQL来获得相应的执行计划。因为它真正被执行,所以可以看到执行计划每一步花掉了多少时间,以及它实际返回的行数。
ANALYZE [ boolean ]
-- VERBOSE用于显示计划的附加信息。这些附加信息有:计划树中每个节点输出的各个列
VERBOSE [ boolean ]
-- COSTS选项显示每个计划节点的启动成本和总成本,以及估计行数和每行宽度。该选项默认值为TRUE
COSTS [ boolean ]
SETTINGS [ boolean ]
-- BUFFERS选项显示关于缓冲区使用的信息。
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
-- FORMAT 选项指定输出格式,默认为TEXT
FORMAT { TEXT | XML | JSON | YAML }

输出结果解析

1
2
3
explain select * from xxx;
-- OUTPUT
-- Seq Scan on public.xxx (cost=0.00..8184.00 rows=500000 width=18)

结果中,“Seq Scan on public.xxx 表示顺序扫描表 “xxx”,顺序扫描也是全表扫描,即从头到尾地扫描表。后面的内容“(cost=0.00..8184.00 rows=500000 width=18)” 可分为三个部分

  • cost=0.00..8184.00 “cost=”的值有两个数字,中间以 “…”分隔,第一个数字为”0.00”表示启动的成本,也就是说返回第一行需要多少个cost值;第二个数字表示返回所有的数据的成本
  • rows=500000 表示返回500000行
  • width=18 表示每行平均宽度为18字节

输出为JSON的格式为“

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
explain (FORMAT JSON) select * from xxx;
-- OUTPUT
-- --[
-- {
-- "Plan": {
-- "Node Type": "Seq Scan",
-- "Parallel Aware": false,
-- "Relation Name": "xxx",
-- "Alias": "xxx",
-- "Startup Cost": 0.00,
-- "Total Cost": 8184.00,
-- "Plan Rows": 500000,
-- "Plan Width": 18
-- }
-- }
-- ]

cost

cost 成本 “cost” 描述一个SQL执行的代价是多少,默认情况下不同的操作其“cost”的值如下:

  • 顺序扫描一个数据块,cost值定为1
  • 随机扫描一个数据块,cost值定为4
  • 处理一个数据行的CPU,cost为0.01
  • 处理一个索引行的CPU,cost为0.005
  • 每个操作符的CPU的代价为0.0025
几种扫描方式
  • 全表扫描
    全表扫描在PostgreSQL也称为顺序扫描(seq scan), 全表扫描就是把表的所有数据块从头到尾读一遍,然后从数据块中找到符合条件的数据块。
    全表扫描在EXPLAIN命令输出的结果中用“Seq Scan”表示
  • 索引扫描
    索引通常是为了加快查询数据的速度而增加的。索引扫描,就是在索引中找出需要的数据行的无力位置,然后再到表的数据块中把相应的数据读出来的过程。
    索引扫描在EXPLAIN命令输出中用“Index Scan”表示
  • 位图扫描
    位图扫描也是走索引的一种方式。方法是扫描索引,把满足条件的行或块在内存中见一个位图,扫描完索引后,再根据位图到表的数据文件中把相应的数据读出来。如果走了两个索引,可以把两个索引形成的位图进行“and”或“or”计算,合并成一个位图,再到表的数据文件中把数据读出来。
    位图扫描在EXPLAIN命令中用“Bitmap Index Scan”和 “BitmapOr”

    参考