Howto: PostgreSQL 查询分析和优化

Whale Mo
Page content

故事开始：公司的服务在周末高峰期，跪了喵……

通过分析日志，发现数据库是瓶颈所在，导致了API服务和Nginx都被阻塞以至于无法提供服务……

于是，一边通过启动更大的RDS实例来承载负载，一边开始着手分析慢查询并调优。

第一步：打开 PostgreSQL 的 查询统计扩展

pg 带有一个 pg_stat_statements 扩展，但是默认并没有启用，而启用该扩展需要重启数据库。参照了这篇文档 在 Amazon RDS 上开启了该扩展：

  1. 新建一个数据库参数组；

  2. 修改该参数组设置：

    参数pg95default.postgres9.5
    pg_stat_statements.max5000<engine-default>
    pg_stat_statements.save1<engine-default>
    pg_stat_statements.trackTOP<engine-default>
    pg_stat_statements.track_utility1<engine-default>
    shared_preload_librariespg_stat_statements<system>

  3. 选中需要分析的RDS实例，操作->修改->应用刚刚创建的参数组；

  4. 重启RDS实例，注意这个会导致服务暂时不可用，请小心操作；

  5. 等待重启完毕后，连接到数据库，选中需要分析的数据库，执行 CREATE EXTENSION IF NOT EXISTS pg_stat_statements; 来启用扩展；

第二步：查询&分析统计数据

pg_stat_statements 扩展启用后，保持服务运作或执行压力测试的情况下，过一段时间执行查询:

SELECT calls, total_time,max_time, mean_time, rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20;

即可获得查询时间最长的相关SQL语句，同时可以调整 ORDER BY 为 mean_time 和 max_time 来查看平均时间最长和单次爆发最长的相关查询，将这些得分最高的QUERY都记录下来喵～

第三步：优化查询

使用 pg 自带的 EXPLAIN 查询来分析查询到底使用了什么方式执行，有没有使用索引，有没有扫表操作等等，来分析这条语句为什么这么慢喵～

比如，在上一步发现了这么一条语句查询次数&耗时都非常高：

SELECT "contributions".* FROM "contributions" WHERE (created_at > ?) ORDER BY like_times desc LIMIT ?;

我们使用 EXPLAIN 并构造一个查询来看它到底发生了什么：

db=> EXPLAIN SELECT  "contributions".* FROM "contributions" WHERE (created_at > '2016-01-01')  ORDER BY like_times desc LIMIT 1000;                                                                                    
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Limit  (cost=19020.45..19022.95 rows=1000 width=99)
   ->  Sort  (cost=19020.45..19113.68 rows=37291 width=99)
         Sort Key: like_times DESC
         ->  Seq Scan on contributions  (cost=0.00..16975.83 rows=37291 width=99)
               Filter: (created_at > '2016-01-01 00:00:00'::timestamp without time zone)
(5 rows)

发现原来在执行筛选的时候，查询执行了扫表操作，那么进一步查看这张表已有索引情况：

SELECT * FROM pg_indexes WHERE tablename='contributions';

原来这张表的 created_at 列没有任何索引，于是创建索引：

CREATE INDEX index_contributions_on_created_at ON contributions USING btree (created_at);

然后再次执行 EXPLAIN :

db=> EXPLAIN SELECT "contributions".* FROM "contributions" WHERE (created_at > '2016-01-01')  ORDER BY like_times desc LIMIT 1000;
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5469.78..5472.28 rows=1000 width=99)
   ->  Sort  (cost=5469.78..5563.54 rows=37505 width=99)
         Sort Key: like_times DESC
         ->  Index Scan using index_contributions_on_created_at on contributions  (cost=0.42..3413.42 rows=37505 width=99)
               Index Cond: (created_at > '2016-01-01 00:00:00'::timestamp without time zone)
(5 rows)

发现执行该SQL的时候已经自动启用了 index 并大大缩短了预计时间，此时我们需要重置统计数据来观察优化结果： SELECT pg_stat_statements_reset();

重复步骤二和步骤三，直到所有的常用查询基本都在 2ms 以下，然后进行下一步操作：

第四步：关闭查询统计扩展

一定时间的分析和优化之后，我们可以选择关闭扩展来释放统计带来的额外数据库消耗：

DROP EXTENSION IF EXISTS pg_stat_statements;

同时将 RDS 参数组设置为未开启该扩展的参数组，并重启 pg 实例;

备注：本文所有操作都基于 PostgreSQL 9.5，基本可以适用于 9.x 的数据库版本，但是更高版本不保证一定可行喵～