注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL:"ORDER BY multi columns " SQL 优化一例  

2013-03-12 15:56:01|  分类: PG性能优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


       今天监控数据库时,一慢 SQL 引起了我的注意,这个 SQL 运行时间在 474 ms 左右,经分析,
创建合适的索引后,最终将 SQL 优化到 4 ms 左右,以下是优化过程,记录下。


--1 数据库日志 
 2013-03-12 14:14:15.266 CST,"francs","francs",10729,"192.168.100.114:32996",513e742a.29e9,759,"SELECT",2013-03-12 08:17:46 CST,1424/3734,0,LOG,00000,"duration: 474.781 ms  execute <unnamed>: select this_.id as id0_0_, this_.create_time as create2_0_0_, this_.create_user as create3_0_0_, this_.deleted as deleted0_0_, this_.modify_time as modify5_0_0_, this_.modify_user as modify6_0_0_, this_.apk_id as apk7_0_0_, this_.apk_md5 as apk8_0_0_, this_.apk_size as apk9_0_0_, this_.app_class_id as app10_0_0_, this_.app_id as app11_0_0_, this_.package as package0_0_, this_.app_show_ver as app13_0_0_, this_.app_type as app14_0_0_, this_.app_ver as app15_0_0_, this_.authentic as authentic0_0_, this_.cn_name as cn17_0_0_, this_.content_provider as content18_0_0_, this_.eng_name as eng19_0_0_, this_.on_off as on20_0_0_, this_.promotion as promotion0_0_, this_.sdk_ver as sdk22_0_0_, this_.visible as visible0_0_ from tbl_test this_ where this_.app_class_id in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) and this_.app_type in ($14, $15) and this_.on_off=$16 and this_.visible=$17 and this_.deleted=$18 order by this_.app_type desc, this_.create_time desc limit $19","parameters: $1 = '3', $2 = '4', $3 = '5', $4 = '6', $5 = '7', $6 = '8', $7 = '9', $8 = '10', $9 = '11', $10 = '12', $11 = '13', $12 = '14', $13 = '15', $14 = '0', $15 = '1', $16 = '1', $17 = '1', $18 = '0', $19 = '80'",,,,,,,"exec_execute_message, postgres.c:2025","" 
 备注:SQL 的详细信息可以从数据库日志得到。


--2 格式化 SQL
 select this_.id               as id0_0_,
       this_.create_time      as create2_0_0_,
       this_.create_user      as create3_0_0_,
       this_.deleted          as deleted0_0_,
       this_.modify_time      as modify5_0_0_,
       this_.modify_user      as modify6_0_0_,
       this_.app_type         as app14_0_0_,
       this_.app_ver          as app15_0_0_,
       this_.authentic        as authentic0_0_,
       this_.cn_name          as cn17_0_0_,
       this_.content_provider as content18_0_0_,
       this_.eng_name         as eng19_0_0_,
       this_.on_off           as on20_0_0_,
       this_.promotion        as promotion0_0_,
       this_.sdk_ver          as sdk22_0_0_,
       this_.visible          as visible0_0_
       ....省略部分字段
  from tbl_test this_
 where this_.app_class_id in
       (3,4,5,6,7,8,9,10,11,12,13,14,15)
   and this_.app_type in (0, 1)
   and this_.on_off = 1
   and this_.visible = 1
   and this_.deleted = '0'
 order by this_.app_type desc, this_.create_time desc limit 80;
 备注:这个 SQL 非常简单,没有关联查询,运行时间在 474  ms 确实比较糟糕。


--3 执行计划 (优化前)
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=32253.40..32253.60 rows=80 width=449) (actual time=500.618..500.638 rows=80 loops=1)
   ->  Sort  (cost=32253.40..32484.00 rows=92241 width=449) (actual time=500.617..500.624 rows=80 loops=1)
         Sort Key: app_type, create_time
         Sort Method: top-N heapsort  Memory: 46kB
         ->  Bitmap Heap Scan on tbl_test this_  (cost=1998.46..28876.49 rows=92241 width=449) (actual time=69.868..360.386 rows=125997 loops=1)
               Recheck Cond: ((app_class_id = ANY ('{3,4,5,6,7,8,9,10,11,12,13,14,15}'::numeric[])) AND (on_off = 1::numeric) AND (app_type = ANY ('{0,1}'::numeric[])))
               Filter: ((visible = 1::numeric) AND (deleted = 0::numeric))
               ->  Bitmap Index Scan on idx_tbl_test_muti  (cost=0.00..1975.40 rows=112718 width=0) (actual time=67.843..67.843 rows=152951 loops=1)
                     Index Cond: ((app_class_id = ANY ('{3,4,5,6,7,8,9,10,11,12,13,14,15}'::numeric[])) AND (on_off = 1::numeric) ANY (app_type = ANY ('{0,1}'::numeric[])))
 Total runtime: 500.717 ms
(10 rows)         
    备注:重新运行这个 SQL ,花了 500 ms 左右,时间主要花在通过索引 “ idx_tbl_test_muti ” 读取记录环节,
              并且 PLAN 中显示了排序步骤,接下来看下表结构。


--4 表结构
                     Table "francs.tbl_test"
      Column      |            Type             |       Modifiers        
------------------+-----------------------------+------------------------
 id               | numeric(19,0)               | not null
 apk_id           | numeric(19,0)               | not null
 app_ver          | numeric(19,0)               | not null
 app_show_ver     | character varying(32)       | not null
 cn_name          | character varying(128)      | not null
 authentic        | numeric(10,0)               | not null
 app_class_id     | numeric(19,0)               | not null
 description      | character varying(1024)     | 
 create_user      | character varying(32)       | not null
 create_time      | timestamp without time zone | not null default now()
 deleted          | numeric(5,0)                | not null default 0
 on_off           | numeric(1,0)                | not null default 1
 app_type         | numeric(2,0)                | not null default 0
 visible          | numeric(2,0)                | not null default 1
..... 
部分字段略
 Indexes:
    "pk_op_app" PRIMARY KEY, btree (id)
    "idx_tbl_test_muti" btree (app_class_id, app_type DESC, create_time DESC), tablespace "tbs_francs_03"
    "tbl_test_app_type_index" btree (app_type), tablespace "tbs_francs_idx"
    "tbl_test_create_time_index" btree (create_time), tablespace "tbs_francs_idx"
   备注:重点看下索引 idx_tbl_test_muti 的定义。
 
 
--5 查询字段 distinct 值
 francs=> select tablename,attname,n_distinct from pg_stats where tablename='tbl_test' 
francs-> and attname in ('app_class_id','app_type','on_off','visible','deleted','create_time');
 tablename  |   attname    | n_distinct 
------------+--------------+------------
 tbl_test | app_class_id |         21
 tbl_test | create_time  |         -1
 tbl_test | deleted      |          2
 tbl_test | on_off       |          2
 tbl_test | app_type     |          6
 tbl_test | visible      |          2
   备注:pg_stats 视图可以查看表字段的统计信息,这里查看字段的 distinct 值,从上面看出,
             where 条件的几个字段的选择性都非常不好,但 order by 字段的 create_time 的 
              n_distinct 为 -1 ,表示字段 create_time 是唯一的,选择性好。
 

--6 创建新索引
 set default_tablespace='tbs_francs_03';
create index concurrently idx_tbl_test_muti2 on tbl_test using btree (app_type desc,create_time desc );
   
备注:尝试仅在 order by 字段上创建索引。
  

--7 再次查看执行计划 (优化后)
                                                                                                     QUERY PLAN                      
                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..28.83 rows=80 width=448) (actual time=2.342..4.065 rows=80 loops=1)
   ->  Index Scan using idx_tbl_test_muti2 on tbl_test this_  (cost=0.00..33145.06 rows=91966 width=448) (actual time=2.342..4.051 rows=80 loops=1)
         Filter: ((app_type = ANY ('{0,1}'::numeric[])) AND (on_off = 1::numeric) AND (visible = 1::numeric) AND (deleted = 0::numeric) AND (app_class_id = ANY ('{3,4,5,6,7,8,9,10,11,12,13,14,15}'::numeric[])))
 Total runtime: 4.128 ms
(4 rows)
备注:优化后,执行时间下降到 4 ms 左右,仅原来的百分之一。
 
 
--8 查询表 tbl_test 索引使用情况
 francs=> select relname,indexrelname ,idx_scan from pg_stat_user_indexes where relname='tbl_test' order by idx_scan;
  relname   |         indexrelname         | idx_scan 
------------+------------------------------+----------
 tbl_test | idx_tbl_test_muti2         |       48
 tbl_test | tbl_test_app_type_index    |     1205
 tbl_test | tbl_test_create_time_index |    13688
 tbl_test | pk_op_app                    |    66520 
   备注:上面显示索引 "idx_tbl_test_muti2" 已经被使用了,这时可以删除老索引 "idx_tbl_test_muti" 了。
 
--9 参考
http://www.postgresql.org/docs/9.1/static/indexes-ordering.html
http://www.postgresql.org/docs/9.1/static/catalog-pg-statistic.html
http://francs3.blog.163.com/blog/static/4057672720125218561360/
  评论这张
 
阅读(9002)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016