RDS PostgreSQL慢SQL问题

更新时间:2025-01-10 06:57:28

架构设计和库表索引设计会影响查询性能,设计不当会导致慢SQL。

查看慢SQL

  • 在慢SQL中查看

    1. 访问RDS实例列表,在上方选择地域,然后单击目标实例ID。

    2. 在左侧导航栏中,选择自治服务 > SQL。详细操作请参见SQL

      SQL功能支持SQL优化,您可以根据诊断结果、优化建议以及预期的优化收益来决定是否采纳相应建议。

  • 在日志管理中查看

    1. 访问RDS实例列表,在上方选择地域,然后单击目标实例ID。

    2. 在左侧导航栏中单击日志管理

    3. 日志管理页面选择查询慢日志明细,选择时间范围,单击查询

      系统记录7天内数据库中执行时间超过log_min_duration_statement参数值的SQL语句,并进行相似语句去重。

      说明

      log_min_duration_statement参数的作用是设定慢日志判定的阈值,单位为毫秒(ms)。其默认值为1000,意味着响应时间超过1秒(s)的SQL将被记录到慢SQL日志中。通过设置实例参数,可以修改该参数的取值。

通过EXPLAIN命令分析慢SQL

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

说明

可以通过设置实例参数启用参数auto_explain.log_analyze、auto_explain.log_buffersauto_explain.log_min_duration,以便在慢日志明细中查看慢SQL的执行计划。

EXPLAIN [ ( option [, ...] ) ] statement

EXPLAIN命令中的option可选参数如下:

参数

数据类型

说明

参数

数据类型

说明

ANALYZE

boolean

执行SQL语句并返回实际运行时间。默认值:FALSE

VERBOSE

boolean

显示关于计划的额外信息。默认值:FALSE

COST

boolean

显示执行计划中每一个计划节点的启动时间、总执行成本、预估行数以及每行的宽度。默认值:TRUE

BUFFERS

boolean

缓冲区使用的信息。例如:共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、临时块读取和写入的次数。默认值:FALSE

TIMING

boolean

显示实际启动时间以及在每个计划节点中花费的时间。默认值:TRUE

说明

仅当ANALYZETRUE时,此参数方可使用。

SUMMARY

boolean

在查询计划后面包含摘要信息(例如共计花费多长时间)。默认值:FALSE

说明

仅当ANALYZETRUE时,此参数方可使用。

FORMAT

指定输出格式,取值:

  • TEXT(默认值)

  • XML

  • JSON

  • YAML

例如,执行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(UPDATEINSERTDELETE),务必在事务中执行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 = 2Rows 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 ScanIndex 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,相比优化前提升了数千倍。

非最佳索引表的优化

  1. 创建表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);
  2. 执行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列的条件过滤,还有优化的空间。

  3. 通过CREATE INDEX on t2(id,name);t2表中的id列和name列各增加一个索引。

  4. 执行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优化示例
  • 无索引表的优化
  • 非最佳索引表的优化
  • 相关文档