架构设计和库表索引设计会影响查询性能,设计不当会导致慢SQL。
查看慢SQL
在慢SQL中查看
在日志管理中查看
通过EXPLAIN命令分析慢SQL
定位到慢SQL后,可以通过EXPLAIN命令查询该SQL的执行计划,以帮助您深入了解目标SQL的执行成本。
可以通过设置实例参数启用参数auto_explain.log_analyze、auto_explain.log_buffers和auto_explain.log_min_duration,以便在慢日志明细中查看慢SQL的执行计划。
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN命令中的option可选参数如下:
参数 | 数据类型 | 说明 |
参数 | 数据类型 | 说明 |
ANALYZE | boolean | 执行SQL语句并返回实际运行时间。默认值: |
VERBOSE | boolean | 显示关于计划的额外信息。默认值: |
COST | boolean | 显示执行计划中每一个计划节点的启动时间、总执行成本、预估行数以及每行的宽度。默认值: |
BUFFERS | boolean | 缓冲区使用的信息。例如:共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、临时块读取和写入的次数。默认值: |
TIMING | boolean | 显示实际启动时间以及在每个计划节点中花费的时间。默认值: 仅当ANALYZE为 |
SUMMARY | boolean | 在查询计划后面包含摘要信息(例如共计花费多长时间)。默认值: 仅当ANALYZE为 |
FORMAT | 无 | 指定输出格式,取值:
|
例如,执行EXPLAIN SELECT * FROM test;
返回的结果如下:
QUERY PLAN
----------------------------------------------------
Seq Scan on test (cost=0.00..1.01 rows=1 width=4)
(1 row)
上面的返回结果中,cost=0.00..1.01 rows=1 width=4
为估算出的SQL预计执行成本,其中0.00
为启动SQL语句到返回第一行数据所需的启动成本,1.01
为返回所有行所需的总成本。
EXPLAIN (ANALYZE) DML-SQL 注意事项
EXPLAIN (ANALYZE) 语句的工作方式类似于EXPLAIN,主要区别在于EXPLAIN (ANALYZE) 语句实际会执行SQL。如果SQL涉及数据变更,即DML SQL(UPDATE、INSERT或DELETE),务必在事务中执行EXPLAIN (ANALYZE),查看完成后再进行回滚。
命令示例:
BEGIN;
EXPLAIN (ANALYZE) <DML(UPDATE/INSERT/DELETE) SQL>;
ROLLBACK;
如何阅读执行计划
EXPLAIN命令的输出可以看作是一个树状结构,即查询计划树。树的每一个节点包含节点类型、作用对象及其他属性信息。其中节点类型分如下几大类:
控制节点(Control Node)
扫描节点(ScanNode)
物化节点(Materialization Node)
连接节点(Join Node)
其中扫描节点又包含多种扫描方式,本文列举常用的几个进行详细介绍:
Seq Scan:
顺序扫描全表,一般用在查询没有索引的表,例如
explain(ANALYZE,VERBOSE,BUFFERS) select * from class where st_no=2;
命令的返回结果如下:QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on public.class (cost=0.00..26.00 rows=1 width=35) (actual time=0.136..0.141 rows=1 loops=1) //Seq Scan on public.class表示这个节点的类型和作用对象,即在class表上进行全表扫描。 //(cost=0.00..26.00 rows=1 width=35)表示该节点的成本估计,0.00是节点启动成本,26.00为该节点的成本,rows是该节点输出行的估计值,width是该节点输出行的平均宽度。 //(actual time=0.136..0.141 rows=1 loops=1) 表示该节点的真实执行信息,0.136表示该节点启动时间,单位为ms。0.141表示该节点耗时,单位为ms。rows代表实际输出行。loops代表节点循环次数。 Output: st_no, name //代表SQL输出结果集的各个列,仅在EXPLAIN命令中的VERBOSE选项为on时才会显示。 Filter: (class.st_no = 2) //表示Seq Scan节点中Filter操作,即全表扫描时对每行记录进行过滤操作,过滤条件为class.st_no = 2。 Rows Removed by Filter: 1199 //表示过滤了多少行记录,属于Seq Scan节点的VERBOSE信息,仅在EXPLAIN命令中的VERBOSE选项为on时才会显示。 Buffers: shared hit=11 //表示在共享缓存中命中了11个数据块,属于Seq Scan节点的BUFFERS信息,仅在EXPLAIN命令中的BUFFERS选项为on时才会显示。 Planning time: 0.066 ms //表示生成查询计划的用时。 Execution time: 0.160 ms //表示实际的SQL执行用时,不包括生成查询计划的用时。
Index Scan:
索引扫描,主要用在WHERE条件中存在索引列的情况,例如,假设
st_no
列中存在索引,则EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM class WHERE st_no=2;
命令的返回结果如下:QUERY PLAN -------------------------------------------------------------------------------------------------- Index Scan using no_index on public.class (cost=0.28..8.29 rows=1 width=35) (actual time=0.022..0.023 rows=1 loops=1) //表示使用public.class表的no_index索引对表进行索引扫描。 Output: st_no, name Index Cond: (class.st_no = 2) //表示索引扫描的条件为class.st_no = 2。 Buffers: shared hit=3 Planning time: 0.119 ms Execution time: 0.060 ms (6 rows)
从上述结果可以看出,使用索引之后,在相同条件下对同一张表的扫描速度变快了。需要扫描的数据块少了之后,需要的成本变小了,速度也更快了。
Index Only Scan:
覆盖索引扫描,仅返回指定的索引列,例如
EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT st_no FROM class WHERE st_no=2;
命令的返回结果如下:QUERY PLAN -------------------------------------------------------------------------------------------------- Index Only Scan using no_index on public.class (cost=0.28..4.29 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1) //表示使用public.class表的no_index索引对表进行覆盖索引扫描。 Output: st_no Index Cond: (class.st_no = 2) Heap Fetches: 0 //表示需要扫描的数据块的个数。 Buffers: shared hit=3 Planning time: 0.058 ms Execution time: 0.036 ms (7 rows)
Bitmap Index Scan:
利用Bitmap结构扫描。Bitmap Index Scan与Index Scan都是基于索引的扫描,但Bitmap Index Scan节点返回的是一个位图而不是一个元组,位图中每位代表了一个扫描到的数据块。
Bitmap Heap Scan:
Bitmap Heap Scan一般作为Bitmap Index Scan的父节点,将Bitmap Index Scan返回的位图转换为对应的元组。相比Index Scan,Bitmap Index Scan将随机读转换成了按照数据块的物理顺序读取,这在数据量比较大的时候会极大地提升扫描性能。例如
EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM class WHERE st_no=2;
命令的返回结果如下:QUERY PLAN -------------------------------------------------------------------------------------------------- Bitmap Heap Scan on public.class (cost=4.29..8.30 rows=1 width=35) (actual time=0.025..0.025 rows=1 loops=1) Output: st_no, name //表示对public.class表进行Bitmap Heap扫描。 Recheck Cond: (class.st_no = 2) //表示Bitmap Heap Scan的Recheck操作的条件是class.st_no = 2。 Heap Blocks: exact=1 //表示准确扫描到的数据块个数是1。 Buffers: shared hit=3 -> Bitmap Index Scan on no_index (cost=0.00..4.29 rows=1 width=0) (actual time=0.019..0.019 rows=1 loops=1) //表示使用no_index索引进行位图索引扫描。 Index Cond: (class.st_no = 2) //表示位图索引的条件为class.st_no = 2。 Buffers: shared hit=2 Planning time: 0.088 ms Execution time: 0.063 ms (10 rows)
在大多数情况下,Index Scan要比Seq Scan快。
如果获取的结果集在所有数据中占比很大时,Index Scan因为要先扫描索引再读表数据,所以反而会比Seq Scan慢。
如果获取的结果集的占比比较小,但是元组数很多时,Bitmap Index Scan的性能要比Index Scan好。
如果获取的结果集能够被索引覆盖,则Index Only Scan因为仅需扫描索引,正常情况下性能最好。如果存在可见性映射表(Virtual Map)未生成等特殊情况,性能则会下降。
SQL优化示例
本节将列举两个示例,分别使用EXPLAIN命令对SQL优化前后的表进行分析,以对比优化前后的差异。
无索引表的优化
例如存在没有创建索引的表t1,可以通过CREATE TABLE ON t1(id);
创建优化SQL:
优化前:对表t1执行
EXPLAIN ANALYZE SELECT * FROM t1 WHERE id =1;
,结果显示全表扫描,总执行时间为302.381 ms。优化后:重新执行
EXPLAIN ANALYZE SELECT * FROM t1 WHERE id =1;
命令,结果显示总执行时间缩短至0.052 ms,相比优化前提升了数千倍。
非最佳索引表的优化
创建表t2,插入100万条随机数据,并对
id
列创建索引。CREATE TABLE t2(id int,name int); INSERT INTO t2 SELECT random()*(id/100),random()*(id/100) FROM generate_series(1,1000000) t(id); CREATE INDEX idx_t2_id ON t2(id);
执行
EXPLAIN ANALYZE SELECT * FROM t2 WHERE id=10 AND name=13;
命令,返回的结果如下。QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using t2_id_idx on t2 (cost=0.42..12.12 rows=1 width=8) (actual time=0.098..2.631 rows=88 loops=1) Index Cond: (id = 10) Filter: (name = 13) Rows Removed by Filter: 4461 Planning Time: 0.081 ms Execution Time: 2.655 ms (6 rows)
通过计划可以看出已经做了索引扫描,但是还存在
name
列的条件过滤,还有优化的空间。通过
CREATE INDEX on t2(id,name);
给t2表中的id
列和name
列各增加一个索引。执行
EXPLAIN ANALYZE SELECT * FROM t2 WHERE id=10 AND name=13;
命令,返回结果如下。QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Index Only Scan using t2_id_name_idx on t2 (cost=0.42..15.48 rows=18 width=8) (actual time=0.028..0.134 rows=88 loops=1) Index Cond: ((id = 10) AND (name = 13)) Heap Fetches: 88 Planning Time: 0.198 ms Execution Time: 0.157 ms (5 rows)
总执行时间缩短至
0.157ms
,优化成功。
相关文档
您可以开启自动SQL优化功能,在数据库实例出现慢SQL问题时,及时进行诊断和优化,帮助数据库系统运行在最佳状态。
- 本页导读 (1)
- 查看慢SQL
- 通过EXPLAIN命令分析慢SQL
- EXPLAIN (ANALYZE) DML-SQL 注意事项
- 如何阅读执行计划
- SQL优化示例
- 无索引表的优化
- 非最佳索引表的优化
- 相关文档