在相同业务场景下,架构设计和库表索引设计会影响查询性能,良好的设计可以提高查询性能,反之会出现很多慢SQL(执行时间很长的SQL语句)。本文介绍导致慢SQL的原因和解决方案。

查看慢SQL

控制台的参数设置中,可以找到名为log_min_duration_statement的参数,该参数的作用是设置慢日志判定的阈值,单位为ms。默认值为1000,即响应时间超过1秒的SQL会被记录到慢SQL日志中。

参数设置通过查看控制台的日志管理中的慢日志明细页签,可以查看当前系统中存在的慢SQL。慢日志明细您只需在参数设置中开启auto_explain.log_analyzeauto_explain.log_buffersauto_explain.log_min_duration三个参数,即可在慢日志明细中查看慢SQL的执行计划。慢SQL执行计划

EXPLAIN命令

定位到慢SQL之后,可以通过EXPLAIN命令查询该SQL的执行计划,帮助您深入了解目标SQL的启动代价。

EXPLAIN [ ( option [, ...] ) ] statement
上面EXPLAIN命令中的option可选参数的说明如下:
  • ANALYZE [ boolean ]:执行SQL语句并返回实际运行时间。默认值:FALSE
  • VERBOSE [ boolean ]:显示关于计划的额外信息。默认值:FALSE
  • COST [ boolean ]:显示执行计划中每一个计划节点的启动时间、总执行代价、预估行数以及每行的宽度。默认值:TRUE
  • BUFFERS [ boolean ]:缓冲区使用的信息。例如:共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、临时块读取和写入的次数。默认值:FALSE
  • TIMING [ boolean ]:显示实际启动时间以及在每个计划节点中花费的时间。仅当ANALYZE为TRUE时可用。默认值:TRUE
  • SUMMARY [ boolean ]:在查询计划后面包含摘要信息(例如共计花费多长时间)。当ANALYZE为TRUE时自动开启。默认值:FALSE
  • FORMAT { TEXT | XML | JSON | YAML }:指定输出格式。默认值:TEXT
例如,执行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命令的输出可以看作是一个树状结构,即查询计划树。树的每一个节点包含节点类型、作用对象及其他属性信息。其中节点类型分如下几大类:
  • 控制节点(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因为仅需扫描索引,正常情况下性能最好。如果存在可见性映射表(Vitual Map)未生成等特殊情况,性能则会下降。

SQL优化示例

本节列举两个示例,分别使用EXPLAIN命令对SQL优化前后的表进行分析,对比一下前后的差距。

无索引表的优化

  • SQL优化前:对无索引的表T1执行explain analyze select * from t1 where id =1;命令的返回结果如下。
                                                        QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------
     Gather  (cost=1000.00..97331.31 rows=1 width=4) (actual time=0.217..302.316 rows=1 loops=1)
       Workers Planned: 2
       Workers Launched: 2
       ->  Parallel Seq Scan on t1  (cost=0.00..96331.21 rows=1 width=4) (actual time=191.208..289.240 rows=0 loops=3)
             Filter: (id = 1)
             Rows Removed by Filter: 3333333
     Planning Time: 0.030 ms
     Execution Time: 302.381 ms
    (8 rows)

    通过计划可以看出,SELECT针对T1表进行了全表扫描,过滤条件为id=1,总执行时间为302.381ms,效率不是很理想。

  • SQL优化后:通过create index on t1(id)给T1表中的id列创建索引,重新执行explain analyze select * from t1 where id =1;命令,返回结果如下。
                                                        QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------
     Index Only Scan using t1_id_idx on t1  (cost=0.43..2.45 rows=1 width=4) (actual time=0.035..0.036 rows=1 loops=1)
       Index Cond: (id = 1)
       Heap Fetches: 1
     Planning Time: 0.134 ms
     Execution Time: 0.052 ms
    (5 rows)

    总执行时间缩短至0.052ms,相比优化前的302.381ms提升了数千倍。

非最佳索引表的优化

创建一张名为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 on t2(id)
  • SQL优化前:执行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列的条件过滤,还有优化的空间。

  • SQL优化后:通过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,优化成功。