今天发现有个库负载很高, 达到10左右，经查有个SQL比较慢，执行时间在 1.5 秒左右，而且并发量很大，SQL如下。
低性能 SQL
1
2
3
4
5
6
7
|
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
|
执行计划
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
----------------------------------------------------------------------------------------------------------------------------
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 秒的时间花在这两步上。
查看表大小
1
2
3
4
5
6
|
skytf=> /dt+ tbl_table
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------+-------+-------+--------+-------------
skytf | tbl_table | table | skytf | 583 MB | 角色信息表
(1 row)
|
表结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
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)
|
创建组合索引
1
|
create index concurrently idx_tbl_table_c0_skyid on tbl_table using btree ( C0 desc, skyid desc);
|
优化后的PLAN
1
2
3
4
5
6
7
|
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。
总结
优化后，系统负载降到 5 左右，负载下降了 50%, 负载5还是有点高的，是因为还有其它 SQL比较费CPU，需要优化，这里不再详述。
原创文章，作者：bd101bd101，如若转载，请注明出处：https://blog.ytso.com/236412.html
