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

PostgreSQL 中文网

 
 
 

日志

 
 

使用降序组合索引优化一例  

2011-07-06 13:23:59|  分类: PG性能优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


       今天发现有个库负载很高, 达到10左右,经查有个SQL比较慢,执行时间在 1.5 秒左右,而且并发量
很大,SQL如下。

--1 低性能 SQL
select *
  from (select *
          from tbl_table
         where appid = 324016
           and status = 0
           and manual_status = 0) t where 1 = 1 and C8 = 1 and (C0 > 0 and C0 < 12)
 order by C0 desc, skyid desc offset 5 limit 5
 
--2 执行计划
------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
 Limit  (cost=56947.84..56947.85 rows=5 width=463) (actual time=1401.016..1401.018 rows=5 loops=1)
   ->  Sort  (cost=56947.83..57023.30 rows=30188 width=463) (actual time=1401.010..1401.011 rows=10 loops=1)
         Sort Key: tbl_table.c0, tbl_table.skyid
         Sort Method:  top-N heapsort  Memory: 30kB
         ->  Bitmap Heap Scan on tbl_table  (cost=22005.42..56295.47 rows=30188 width=463) (actual time=1129.283..1370.956 rows=57884 loops=1)
               Recheck Cond: ((c8 = 1) AND (appid = 324016) AND (c0 > 0) AND (c0 < 12))
               Filter: ((status = 0) AND (manual_status = 0))
               ->  BitmapAnd  (cost=22005.42..22005.42 rows=30190 width=0) (actual time=1126.349..1126.349 rows=0 loops=1)
                     ->  Bitmap Index Scan on tbl_table_c8_idx  (cost=0.00..3222.01 rows=100481 width=0) (actual time=213.694..213.694 rows=144600 loops=1)
                           Index Cond: (c8 = 1)
                     ->  Bitmap Index Scan on tbl_table_appid_key  (cost=0.00..9064.41 rows=343740 width=0) (actual time=481.639..481.639 rows=358735 loops=1)
                           Index Cond: (appid = 324016)
                     ->  Bitmap Index Scan on tbl_table_c0_idx  (cost=0.00..9695.86 rows=319749 width=0) (actual time=420.901..420.901 rows=324980 loops=1)
                           Index Cond: ((c0 > 0) AND (c0 < 12)) Total runtime: 1401.181 ms
(15 rows)

   备注:从PLAN可以看出,这个SQL执行时间为 1401 ms, 时间大部分花在  "Bitmap Heap Scan on tbl_table " 上,
             而且需要将三个"Bitmap Index Scan on" 的子集进行合并,所以执行时间比较长,大概 1.3 秒的时间花在这
             两步上。

--3 查表大小
skytf=> \dt+ tbl_table
                      List of relations
 Schema |    Name     | Type  | Owner |  Size  | Description
--------+-------------+-------+-------+--------+-------------
 skytf  | tbl_table | table | skytf | 583 MB | 角色信息表
(1 row)

--4 表结构
skytf=> \d tbl_table
                                           Table "skytf.tbl_table"
        Column        |            Type             |                        Modifiers                        
----------------------+-----------------------------+----------------------------------------------------------
 id                   | integer                     | not null default nextval('tbl_table_id_seq'::regclass)
 appid                | integer                     | not null
 skyid                | bigint                      | not null
 strength_value       | integer                     | default 1000
 win                  | integer                     | default 0
 lost                 | integer                     | default 0
 title                | character varying           |
 bitmap               | integer                     |
 attr1                | character varying           |
 character_type       | integer                     |
 character_ctrl       | integer                     | default 0
 c1                   | integer                     | default 0
 c2                   | integer                     | default 0
 c3                   | integer                     | default 0
 c4                   | integer                     | default 0
 c5                   | integer                     | default 0
 c6                   | integer                     | default 0
 c7                   | integer                     | default 0
 c8                   | integer                     | default 0
 c9                   | integer                     | default 0
 c10                  | integer                     | default 0
 c11                  | integer                     | default 0
 c12                  | integer                     | default 0
 c13                  | integer                     | default 0
 c14                  | integer                     | default 0
 c15                  | integer                     | default 0
 c16                  | integer                     | default 0
 c17                  | integer                     | default 0
 c18                  | integer                     | default 0
 c19                  | integer                     | default 0
 c0                   | integer                     | default 0
 custom_info          | bytea                       |
 date_win             | integer                     | default 0
 week_win             | integer                     | default 0
 date_win_modify_time | timestamp without time zone | default now()
 week_win_modify_time | timestamp without time zone | default now()
 create_time          | timestamp without time zone | default now()
 nick_name            | character varying           |
 str0                 | character varying           |
 str1                 | character varying           |
 str2                 | character varying           |
 str3                 | character varying           |
 str4                 | character varying           |
 status               | integer                     | default 0
 manual_status        | integer                     | default 0
Indexes:
    "pk_tbl_table" PRIMARY KEY, btree (id)
    "tbl_table_appid_key" UNIQUE, btree (appid, skyid)
    "character_appid" btree (appid)
    "character_create" btree (create_time DESC)
    "character_skyid" btree (skyid)
    "character_stength" btree (strength_value DESC)
    "idx_tbl_table_appid_status_manual" btree (appid, status, manual_status)
    "idx_tbl_table_c0_skyid" btree (c0 DESC, skyid DESC)
    "tbl_table_c0_idx" btree (c0 DESC)
    "tbl_table_c10_idx" btree (c10 DESC)
    "tbl_table_c11_idx" btree (c11 DESC)
    "tbl_table_c12_idx" btree (c12 DESC)
    "tbl_table_c13_idx" btree (c13 DESC)
    "tbl_table_c1_idx" btree (c1 DESC)
    "tbl_table_c2_idx" btree (c2 DESC)
    "tbl_table_c3_idx" btree (c3 DESC)
    "tbl_table_c4_idx" btree (c4 DESC)
    "tbl_table_c5_idx" btree (c5 DESC)
    "tbl_table_c6_idx" btree (c6 DESC)
    "tbl_table_c7_idx" btree (c7 DESC)
    "tbl_table_c8_idx" btree (c8 DESC)
    "tbl_table_c9_idx" btree (c9 DESC)
    "tbl_table_date_win_date_win_modify_time_idx" btree (date_win DESC, date_win_modify_time)
    "tbl_table_week_win_week_win_modify_time_idx" btree (week_win DESC, week_win_modify_time)
    "tbl_table_win_date_win_modify_time_idx" btree (win DESC, date_win_modify_time)


--5 在order by 字段上创建降序,组合索引
create index concurrently  idx_tbl_table_c0_skyid on tbl_table using btree ( C0 desc, skyid desc);

--6 优化后的PLAN
 Limit  (cost=25.59..51.17 rows=5 width=462) (actual time=0.069..0.090 rows=5 loops=1)
   ->  Index Scan using idx_tbl_table_c0_skyid on tbl_table  (cost=0.00..161833.67 rows=31625 width=462) (actual time=0.025..0.087 rows=10 loops=1)
         Index Cond: ((c0 > 0) AND (c0 < 12))
         Filter: ((appid = 324016) AND (status = 0) AND (manual_status = 0) AND (c8 = 1))
 Total runtime: 0.146 ms
(5 rows)

Time: 1.199 ms

     备注:创建索引后,这个查询只需要 1 ms, 速度提高了 1400 倍, 多么地神奇,仔细查看下这份 PLAN,
                这份PLAN先走索引"idx_tbl_table_c0_skyid" 将记录直接取出来, 然后根据过滤条件筛选,
                再 limit。
          
--7 总结
               
         优化后,系统负载降到 5 左右,负载下降了 50%, 负载5还是有点高的,是因为还有其它 SQL比较费CPU,
    需要优化,这里不再详述。

  评论这张
 
阅读(27575)| 评论(4)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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