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

数据库日志

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 的详细信息可以从数据库日志得到。

格式化 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 确实比较糟糕。

优化前的执行计划

--------------------------------------------------------------------------------------------------------------------------------
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 中显示了排序步骤，接下来看下表结构。

表信息

表结构

      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 的定义。

查询字段 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 是唯一的，选择性好。

创建索引

1 2	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 字段上创建索引。

优化后的执行计划

                     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 左右，仅原来的百分之一。

索引引使用情况

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” 了。

参考

原创文章，作者：3628473679，如若转载，请注明出处：https://blog.ytso.com/237945.html

PostgreSQL：”ORDER BY Multiple Columns ” SQL 优化一例

数据库日志

格式化 SQL

优化前的执行计划

表信息

创建索引

优化后的执行计划

索引引使用情况

参考

发表回复

PostgreSQL：”ORDER BY Multiple Columns ” SQL 优化一例

数据库日志

格式化 SQL

优化前的执行计划

表信息

创建索引

优化后的执行计划

索引引使用情况

参考

相关推荐

发表回复