PostgreSQL数据库SQL性能优化案例二则

零 背景与说明

数据库的性能会影响整个业务系统的性能,而数据库的性能问题,90%以上的几率都是由性能低下的SQL导致的。

一个即将上线的新系统,开发同事发过来2个SQL优化的请求。

PostgreSQL数据库SQL性能优化案例二则

一 SQL一优化分析处理思路

1 格式化原始SQL:

SELECT COUNT(b.code) AS totalCount, b.status AS status	
FROM t_ai_prd_bill b 
WHERE b.status != '900' AND b.is_deleted = FALSE 
GROUP BY b.status

2  原始SQL执行计划

ai=> explain analyze SELECT COUNT(b.code) AS totalCount, b.status AS status 
ai-> FROM t_ai_prd_bill b 
ai-> WHERE b.status != '900' AND b.is_deleted = FALSE 
ai-> GROUP BY b.status;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=3870.50..3870.51 rows=1 width=17) (actual time=12.660..12.661 rows=5 loops=1)
   Group Key: status
   ->  Seq Scan on t_ai_prd_bill b  (cost=0.00..3839.75 rows=6150 width=17) (actual time=0.013..11.139 rows=6153 loops=1)
         Filter: ((NOT is_deleted) AND (status <> '900'::bpchar))
         Rows Removed by Filter: 32227
 Planning time: 0.165 ms
 Execution time: 12.719 ms
(7 rows)

ai=>

3 获取t_ai_prd_bill表结构,数据量和数据分布

 
ai=> \d t_ai_prd_bill                                      
                         Table "ai.t_ai_prd_bill"
       Column       |              Type              |      Modifiers      
--------------------+--------------------------------+---------------------
 id                 | character varying(32)          | not null
 code               | character varying(12)          | 
 packet_code        | character varying(10)          | 
 status             | character(3)                   | 
 is_deleted         | boolean                        | default false
 classify_type      | character varying(1)           | 
 last_oper_time     | timestamp(6) without time zone | 
 template_no        | character varying(32)          | 
 oss_key            | character varying(255)         | 
 receive_time       | timestamp(6) without time zone | 
 is_question        | boolean                        | default false
 ocr_type           | character(1)                   | 
 cust_id            | character varying(32)          | 
 exception_category | character(1)                   | default '0'::bpchar
 exception_detail   | character varying(400)         | 
 is_suspended       | boolean                        | 
 is_urgent          | boolean                        | 
 raw_deadline       | timestamp(6) without time zone | 
 priority           | smallint                       | 
 deadline           | timestamp(6) without time zone | 
 company_id         | character varying(32)          | 
 company_name       | character varying(255)         | 
 cust_category      | character varying(32)          | 
 source             | character varying(32)          | 
 mode_code          | character varying(32)          | 
 deadline_category  | character varying(3)           | 
 location_id        | character varying(32)          | 
 cust_name          | character varying(128)         | 
 template_category  | character varying(32)          | 
 platform_id        | character varying(32)          | 
 country_name       | character varying(64)          | 
 is_sended          | boolean                        | default false
Indexes:
    "t_ai_prd_bill_pkey" PRIMARY KEY, btree (id)
    "idx_bill_code" btree (code)
    "idx_bill_create_time_status_deleted" btree (receive_time, status, is_deleted)
    "idx_bill_cust_id" btree (cust_id)
    "idx_bill_packet_code" btree (packet_code)
    "idx_bill_status" btree (status)

ai=> select status,count(*) from t_ai_prd_bill group by status;
 status | count 
--------+-------
 400    |  2511
 401    |   183
 500    |  3174
 600    |     1
 701    |   284
 900    | 32227
(6 rows)

ai=>

4 改写等价SQL

结合上述的t_ai_prd_bill表结构、index信息、以及数据量来分析其执行计划。

t_ai_prd_bill表中的status字段,表示业务状态。status=900表示已完成业务操作的票据,status=901表示理票失败的票据,且随着业务正常流转,该表中status=900的记录会越来越多,占据数据量的绝大多数,同时status=901的记录应该非常少。也就是说,这条SQL原本是想查询在业务上除理票已完成的所有其它状态票据信息,即(status != 900)。

这样的话,如果从业务端着手,考虑将status=901表示理票失败的票据,如改为status=899表示理票失败。则,SQL可以等价改造为:

SELECT COUNT(b.code) AS totalCount, b.status AS status	
FROM t_ai_prd_bill b 
WHERE b.status < '900' AND b.is_deleted = FALSE 
GROUP BY b.status

5 新SQL执行计划

ai=> explain analyze SELECT COUNT(b.code) AS totalCount, b.status AS status 
ai-> FROM t_ai_prd_bill b 
ai-> WHERE b.status < '900' AND b.is_deleted = FALSE ai-> GROUP BY b.status;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=3782.19..3782.20 rows=1 width=17) (actual time=5.423..5.424 rows=5 loops=1)
   Group Key: status
   ->  Bitmap Heap Scan on t_ai_prd_bill b  (cost=247.95..3751.44 rows=6150 width=17) (actual time=0.996..3.811 rows=6153 loops=1)
         Recheck Cond: (status < '900'::bpchar) Filter: (NOT is_deleted) Heap Blocks: exact=1213 ->  Bitmap Index Scan on idx_bill_status  (cost=0.00..246.41 rows=6150 width=0) (actual time=0.825..0.825 rows=6153 loops=1)
               Index Cond: (status < '900'::bpchar) 
Planning time: 0.156 ms 
Execution time: 5.480 ms 
(10 rows) 
ai=>

 

6 小结

通过改写SQL前后的执行计划对比,执行时间从12ms降低到5ms。且,随着业务的推进,t_ai_prd_bill表中数据量越来越多,优化带来的效果也会越来越好。

这里,由于业务上将票据状态定义为’100,200,300,400,401,500,600,402,700,701,800,900,901’,每个代码表示每个不同的业务中间状态。该业务需求是想查询统计业务状态不成功的所有票据,程序员自然想到的是通过status !=’900’来过滤数据。却忽视了数据库index里,优化器对于不等于 !=的where条件,走不到index。

 

二 SQL二优化分析处理思路

1 原始SQL

select a.status, sum(case when b.status is null then 0 else 1 end),
        sum(case when b.cust_category = 'DZ001' then 1 else 0 end) as "zyd"
        , sum(case when b.cust_category = 'ZEJ001' then 1 else 0 end) as "zyj"
        , sum(case when b.cust_category = 'MY001' then 1 else 0 end) as "my"
        from (select regexp_split_to_table('100,200,300,400,401,500,600,402,700,701,800,901', ',') as status) a
        left join t_ai_prd_bill b on (b.status = a.status)
        WHERE b.is_deleted = FALSE
        group by a.status
        order by a.status;

2 原始SQL执行计划

ai=> explain analyze select a.status, sum(case when b.status is null then 0 else 1 end),
ai->         sum(case when b.cust_category = 'DZ001' then 1 else 0 end) as "zyd"
ai->         , sum(case when b.cust_category = 'ZEJ001' then 1 else 0 end) as "zyj"
ai->         , sum(case when b.cust_category = 'MY001' then 1 else 0 end) as "my"
ai->         from (select regexp_split_to_table('100,200,300,400,401,500,600,402,700,701,800,901', ',') as status) a
ai->         left join t_ai_prd_bill b on (b.status = a.status)
ai->         WHERE b.is_deleted = FALSE
ai->         group by a.status
ai->         order by a.status;
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=6328.61..15390.61 rows=200 width=42) (actual time=70.104..75.647 rows=5 loops=1)
   Group Key: (regexp_split_to_table('100,200,300,400,401,500,600,402,700,701,800,901'::text, ','::text))
   ->  Merge Join  (cost=6328.61..10560.61 rows=241400 width=42) (actual time=43.422..57.495 rows=35869 loops=1)
         Merge Cond: ((regexp_split_to_table('100,200,300,400,401,500,600,402,700,701,800,901'::text, ','::text)) = ((b.status)::text))
         ->  Sort  (cost=64.84..67.34 rows=1000 width=32) (actual time=0.117..0.119 rows=12 loops=1)
               Sort Key: (regexp_split_to_table('100,200,300,400,401,500,600,402,700,701,800,901'::text, ','::text))
               Sort Method: quicksort  Memory: 25kB
               ->  Result  (cost=0.00..5.01 rows=1000 width=0) (actual time=0.094..0.102 rows=12 loops=1)
         ->  Sort  (cost=6263.78..6384.48 rows=48280 width=10) (actual time=43.293..46.852 rows=48280 loops=1)
               Sort Key: ((b.status)::text)
               Sort Method: quicksort  Memory: 3629kB
               ->  Seq Scan on t_ai_prd_bill b  (cost=0.00..2507.80 rows=48280 width=10) (actual time=0.012..30.322 rows=48280 loops=1)
                     Filter: (NOT is_deleted)
 Planning time: 0.367 ms
 Execution time: 75.737 ms
(15 rows)

ai=>

3 分析SQL及其执行计划

原始SQL是想查询统计t_ai_prd_bill表,按照status字段来分组排序。这里,绕了一个弯路,通过引入函数regexp_split_to_table来构造一个单列的虚拟表a,再使其与t_ai_prd_bill表做一次left join,没有必要。同时,从执行计划里看到有个较大的内存排序,Sort Method: quicksort Memory: 3629kB。

4 尝试改写等价SQL

select  b.status,sum(case when b.status is null then 0 else 1 end),
        sum(case when b.cust_category = 'DZ001' then 1 else 0 end) as "zyd"
        , sum(case when b.cust_category = 'ZEJ001' then 1 else 0 end) as "zyj"
        , sum(case when b.cust_category = 'MY001' then 1 else 0 end) as "my"
        from t_ai_prd_bill b 
        WHERE b.is_deleted = FALSE
        group by b.status
        order by b.status;

5 改写后的SQL执行计划

ai=> explain analyze select  b.status,sum(case when b.status is null then 0 else 1 end),
ai->         sum(case when b.cust_category = 'DZ001' then 1 else 0 end) as "zyd"
ai->         , sum(case when b.cust_category = 'ZEJ001' then 1 else 0 end) as "zyj"
ai->         , sum(case when b.cust_category = 'MY001' then 1 else 0 end) as "my"
ai->         from t_ai_prd_bill b 
ai->         WHERE b.is_deleted = FALSE
ai->         group by b.status
ai->         order by b.status;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3473.54..3473.55 rows=6 width=10) (actual time=49.986..49.987 rows=6 loops=1)
   Sort Key: status
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=3473.40..3473.46 rows=6 width=10) (actual time=49.932..49.934 rows=6 loops=1)
         Group Key: status
         ->  Seq Scan on t_ai_prd_bill b  (cost=0.00..2507.80 rows=48280 width=10) (actual time=0.008..11.934 rows=48280 loops=1)
               Filter: (NOT is_deleted)
 Planning time: 0.109 ms
 Execution time: 50.060 ms
(9 rows)

ai=>

6 小结

通过分析,改写SQL,前后对比执行计划,发现减少了不必要的内存排序操作,进而让数据库执行SQL响应更快,提升效率。

 

发表评论

邮箱地址不会被公开。 必填项已用*标注