PostgreSQL中的性能优化

编程/技术 2019-05-01 @ 08:34:32 浏览数: 148 净访问: 115 By: skyrover

本博客采用创作共用版权协议, 要求署名、非商业用途和保持一致. 转载本博客文章必须也遵循署名-非商业用途-保持一致的创作共用协议


最近又开始搞接口的性能优化了,我发现不论是别人还是我在一开始写接口的时候根本就没有关心性能的问题,只关心功能是否实现,所以随着接口的查询数据越来越多,性能便是一个大问题,而且从一开始并没有关心性能,导致后面性能优化是一个很耗精力的事情。

首先分析接口是慢在哪里,通过在CSAPP-优化程序性能文章中介绍的Python 程序性能分析相关的工具,可以对应加在接口上,获取到性能分析图,从而有选择性的对执行慢的部分进行优化。

在实际工作中,通常优化点在一下几个方面:

  1. 代码层面,是否使用了低效的算法,比如多重循环等
  2. SQL方面,如果是使用ORM,那么需要查看分析ORM翻译后的SQL
  3. 如果有大量数据查询,那么考虑使用直接SQL查询,而非ORM

其中第一个是比较重要的,毕竟SQL有数据库帮你优化,而代码没有。在这里将主要介绍的是第2方面,SQL及SQL优化方面的东西,在日常写代码中写SQL时候需要注意的地方(包括ORM的,其实两者是共通的),首先对于一个SQL,要知道怎么分析:

如何阅读EXPLAIN

Postgres中最有用也是最强大的工具就是EXPLAIN ANALYSE,它接收像SELECT...UPDATE...或者DELETE...语句,执行之后它会返回一个查询计划。

下面是官方文档的一个查询:

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2
ORDER BY t1.fivethous;

然后它生成的查询计划是这样的:

                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
   Sort Key: t1.fivethous
   Sort Method: quicksort  Memory: 77kB
   ->  Hash Join  (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
         Hash Cond: (t2.unique2 = t1.unique2)
         ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
         ->  Hash  (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 28kB
               ->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
                     Recheck Cond: (unique1 < 100)
                     ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
                           Index Cond: (unique1 < 100)
 Planning time: 0.194 ms
 Execution time: 8.008 ms

Postgres创建了计划节点的树型结构用来代表采取的不同动作。根节点以及每个->指向的节点就是对应的动作,非->指向的是ANALYSE的详细分析内容,在一些情况下EXPLAIN ANALYSE提供了额外的执行统计,比如上面的SortHash。所以查询的树结构可以表示为这样:

Sort
└── Hash Join
    ├── Seq Scan
    └── Hash
        └── Bitmap Heap Scan
            └── Bitmap Index Scan

每个分支代表了子动作,而且你需要从内向外来看哪一个动作第一个发生(虽然在同一层的节点的执行顺序可能是不同的)

上面例子中第一个执行的是在tenk_unique1索引上的Bitmap Index Scan,即索引扫描

->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
      Index Cond: (unique1 < 100)

对应的SQL是WHERE t1.unique1 < 100。Postgres会找到匹配索引条件unique1 < 100的行的位置,查到的行不会在这里返回。花费估计(cost=0.00...5.04 rows=101 width=0)表示Postgres预期将会花费5.04个任意单位来找到这些值。0.00是这个节点开始工作的时间(这里就是查询的开始时间)。rows是这个索引扫描预计返回的行数,width是预计返回行数的bytes大小(0是因为我们只关心在哪个地方,而不是行的内容)

因为我们用ANALYSE选项执行的EXPLAIN命令,所以查询会被实际执行并且统计:(actual time=0.049..0.049 rows=100 loops=1)表示Index Scan被执行了一次,并且返回了100行。实际时间表示的是每次迭代的平均值,所以需要乘以loops来获得最终实际执行的时间。给出的实际执行时间是一个范围是因为显示了最小和最大花费时间。在这里我们可以计算出每个任意单位花费0.049ms/5.04units=0.01ms/unit

Index Scan的结果被传递到Bitmap Heap Scan里,在这个节点里,Postgres将会使用在tenk1表中相应行的位置取出对应的数据:

->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
      Recheck Cond: (unique1 < 100)

我们可以看到花费预计,因为每个任意单位花费时间0.01ms/unit,所以预计需要(229.20-5.07)*0.01=2.24ms,我们看到真实时间是每次循环0.526ms,大约是估计时间的1/4,主要原因是花费预计是上限,并不是所有行都需要读取,或者因为recheck condition总是true.

Heap Scan的结果被插入到一个in-memory Hash table里,我们可以看到这几乎不花费什么时间,真实时间为0.659*1ms

->  Hash  (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
      Buckets: 1024  Batches: 1  Memory Usage: 28kB

Hash节点包含了hash buckets和batches的信息,峰值内存使用信息。如果Batches>1,就会使用磁盘,但是不会显示出来。内存使用:100rows * 244bytes = 24.4kb,基本和预计差不多。

最后,Postgres读取了从tenk2表的所有的10000行,然后和tenk1在Hash Table的行进行Hash JoinHash Join意味着一个表的行被放到了in-memory hash,在另一个表扫描行并且将它的值在hash表中进行探测匹配。我们可以看到在第二行有匹配条件:Hash Cond: (t2.unique2 = t1.unique2),因为查询会选择从两个表来的值,所以在Hash Join每行宽度乘以2.

->  Hash Join  (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
      Hash Cond: (t2.unique2 = t1.unique2)
      ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)

现在所有符合我们条件的行都被选出来了,我们可以通过Sort Key: t1.fivethous进行排序

Sort  (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
  Sort Key: t1.fivethous
  Sort Method: quicksort  Memory: 77kB

对应会显示以下信息:Sort节点包含使用的排序算法quicksort,排序是在内存完成还是磁盘完成(会很大程度影响性能),还有内存或者磁盘使用量。

理解如何阅读查询计划会对优化查询很有帮助。比如:Seq Scan通常预示可以增加一个索引,这样将会加快查询。熟悉这些查询计划可以帮助你成为一个更好的数据库工程师。可以查看Using EXPLAIN来获取更多的例子。

下来我们了解一下数据库索引这一基本加快查询的方式

数据库索引

数据库索引是一种在数据库中加速操作(特别是行查询)的数据结构,可以在一张表中,也可以跨表。类似于书中索引的原理,假如你在users表中将users.id列设为索引,那么如果你这样查询数据库:

SELECT name FROM users WHERE id = 1;

数据库将会很快的查询到这条记录,因为有索引,所以不需要一条条查询users表。

那么对于一个应用来说,什么是你应该索引的呢?

Primary keys

大多数关系数据库都有主键的概念。如果存在主键则会自动创建在主键上的索引。一般在应用中就是id列,而且id列会自动获取一个索引。在展示视图比如/users/1这种查询上,是很重要的。因为有了索引,这样的查询就可以很快的返回。

最佳实践:在每个主键上索引

Foreign keys

现在如果用户有多条评论怎么办?一般会在comments表中包含一列user_id,这样就可以确定这条评论是哪个用户的了。你应该在每一个外键上建立索引。当你访问/users/1/comments就可以迅速返回了。

最佳实践:在每个外键上索引

需要作为参数的列

假如现在url需要按照用户名来查询,比如这样:/users/john/comments,那么和以前一样,需要在这一列上增加索引!

最佳实践:对每个在WHERE子句中使用的列进行索引

在联结模型上的组合键

如果多个用户可以一起写一个评论,我们需要存储一个timestamp来知道他们中谁最后一个修改了评论。我们可以使用Commentary联结模型,将users连接到comments,这个模型有一个user_idcomment_id列,以及一个last_edited_at列。

所以我们需要一个组合键(在多列上建立的索引),(user_id, comment_id)在这个联结模型上。这样,我们可以很快的查询一个用户是多少个评论的作者。

最佳实践:在联结模型上建立组合键

状态列

比如评论会有一列comments.publication_state来标志评论的发布状态,可能是用字符串存储的,那么我们就应该在这列上建立索引以帮助我们可以快速查询。

最佳实践:在每一个用作状态的(字符串/数字)列上创建索引

布尔列

比如用户是否为管理员,则会加上一个users.admin列,可能是一个TINYINT列,在这列上也应该加上索引,这样可以很快的查询出所有管理员。

最佳实践:在所有布尔列上加上索引

日期时间列

后面我们又在comments表上增加了评论发布时间列created_at,所以我们可以对评论进行排序。这时候也应该给时间列加上索引。

最佳实践:在每一个日期时间列上加上索引

使用在多条件联结上的列

比如下面这样的查询:

SELECT * FROM comments
INNER JOIN taggings
ON taggings.taggable_type = 'Comment' and taggings.taggable_id = '3'
INNER JOIN tags on taggings.tag_id = tags.id

那么你需要在taggings(taggable_type, taggable_id)对上建立组合索引,并且确保在外键taggings(tag_id)上建立外键索引。

最佳实践:在多条件联结表中的所有_type/_id对上索引

用来验证的列

比如一个唯一列,如果我们在上面增加索引将会相比较非索引变得更快。

比如users.email列,email应该是在整个表上唯一的。

最佳实践:在所有会从模型验证上产生查询的列上增加索引

但是我没有多少数据

与一般开发者所信仰的不同,数据库索引不只是为了速度(技术上是为了速度,但是他们一开始实现数据库索引不是为了解决问题),或者只是在你的数据到达一定大小时候才使用。当然了,如果你只在你本地开发的时候数据库有着不错的性能,但是你开发应用并不是只是这样使用吧?数据库索引不是用来解决速度问题或者数据增长痛苦的,他们是关系型数据库用来正常工作的基础构件。

缺点

  1. 如果给拥有大量数据的表重新建立索引,或者增加新索引,迁移过程会非常慢。
  2. 不可以给数据库每个表每个列上都建立索引,数据库维护索引是有一定的开销的。

那么对于Postgres都有哪些索引类型呢?

Postgres的索引类型

当使用CREATE INDEX,Postgres会默认创建一个B-Tree索引。一般情况下,B-Tree索引工作的很好,但是在一些特别情况下,其他一些类型的索引将会产生更好的结果。

Postgres提供了不同种类的索引类型,使用了不同的算法来加速不同的查询。下面会介绍这些索引类型的大致情况,以及使用场景。

使用USING子句来表明将创建什么类型的索引:

CREATE INDEX band_member_names ON bands USING GIN (member_names);

B-Tree

B-Tree使用了平衡树结构来加速所有数据类型上的相等或者范围查询。因为B-Tree索引项是排序的,它们有时候会用来排序,所以不需要在取出数据后手动排序。

Hash

Hash索引只能用来做相等比较。它们也不是事务安全的,不能做流式复制或者基于文件的赋值,在崩溃之后可能需要使用REINDEX手动重建索引,一般不推荐使用hash索引。

GIN

Generalized Inverted Indexes(通用倒排索引),用来索引某些列包含了不止一个值的情况,比如数组列,文本搜索文档(tsvector)和二进制json文档(jsonb)

GiST

GiST的意思是通用的搜索树(Generalized Search Tree)。 它是一种平衡的,树状结构的访问方法,在系统中起一个基础的模版,然后可以使用它实现任意索引模式。B+-trees,R-trees 和许多其它的索引模式都可以用 GiST 实现。

SP-GiST

SP-GiST是空间分割的(Space-Partitioned)GiST的省略语。 SP-GiST支持分区的搜索树,这有助于开发四叉树,KD树,基数树(radix tree)等范围广泛的不同的非平衡数据结构。 这些结构的共通特征是它们反复地把搜索空间划分成大小不必相等的分区。能很好的匹配分区规则的查询会非常快。

BRIN

BRIN代表块范围索引。BRIN 设计用于处理非常大的表,其中某些列与表中的物理位置具有一些自然相关性。 block range是一组在表中物理上相邻的页;对于每个块范围, 一些摘要信息由索引存储。例如,存储商店的销售订单的表可以具有放置每个订单的日期列, 并且大多数时间较早的订单的条目也会在表中较早出现; 存储ZIP编码列的表格可能会将城市的所有代码自然组合在一起。

上面是Postgres支持的一些索引类型,工作上常用的也就是B-TreeGIN,其他的说实话我也是今天才知道的。

Postgres优化要点

有许多变量允许一个DBA针对特定负载,磁盘类型和硬件来调整PostgresSQL数据库服务器。这些被称为GUCS(全局统一配置设置),你可以通过pg_settings来查看。另外你可以在你的应用中做一些事情来充分使用Postgres。

了解Postgres索引类型

默认CREATE INDEX会创建B-Tree索引,在一般情况下都会工作很好,通常用于比较相等,不相等以及范围操作符中。在一些几何操作中,比如点线,框等情况下可以使用GiST索引。

可以适时使用多列索引

如果在查询中使用到了不止一列,那么多列索引可能会有用。如果你在列a, b上创建了索引,那么这样的查询就可以利用索引:

WHERE a = 1
WHERE a = 1 AND b = 2

但是,下面这样的查询不会使用

WHERE a = 1 OR b = 2
WHERE b = 2

部分索引

一个部分索引是带有WHERE子句的索引。该索引只会对对应条件的行进行索引。比如,比有一个orders表和一个completed字段,标记是否完成,那么如果只对非完成订单进行查询的话,就可以建立下面的索引:

CREATE INDEX orders_incomplete_amount_index ON orders(amount) WHERE complete IS NOT TRUE;

以下形式的查询会使用到上面的索引:

SELECT * FROM orders WHERE amount > 100000 AND complete IS NOT TRUE;

不要过索引

保持一个健康的数据库一部分就是确保你没有任何没有使用的索引。记住你有越多的索引,INSERTs语句将会越慢,因为为了保持索引更新需要更多的操作。

定时更新统计信息

确保运行VACUUM ANALYSE来保持数据统计最新,同时恢复磁盘空间。Postgres会自动在后台执行,你应该阅读相关的频率设置以及使用特性。

确保在创建新索引的时候做ANALYSE(收集与数据库有关的统计信息,ANALYZE收集数据库中表内容的统计信息,然后把结果保存在系统表pg_statistic里。查询规划器就可以使用这些统计帮助判断查询的最佳规划。),否则Postgres不会分析数据,确保新索引可以帮助查询。

更多的使用联结

Postgres很好的支持在一个查询中联结多张表。在一个运行的应用中,5次联结的查询是可以接受的。在大多数情况下,联结是比子查询更好的解决方式,Postgres如果可能的话甚至会自己重写子查询,而使用联结,当然这样就会增加时间使用。所以尽可能的使用联结而不是子查询。

最好使用INNER JOINs

如果所有表的基数都保证和结果集一样的话,推荐使用INNER JOIN而不是LEFT OUTER JOIN。这些年大多数的研究都是来优化外链接。但是事实是特别是一个查询有更多的联结时,外联结限制了计划器优化连接顺序的能力。

知道怎么阅读EXPLAIN的输出

这个在本文一开始就做了介绍,大家可以翻回去看看。

有一个可视化的工具很好用,可以帮助你识别最花时间的节点:explain.depesz.com

下面是阅读查询计划的一些要点:

  • 查询预计和实际情况是基本一致还是有很大差异?一般这是没有做ANALYSE的标志
  • 是否有索引没有用到?计划器没有使用可能是有原因的
  • 是否有查询使用some_string LIKE pattern?如果有尽可能保证字符串匹配pattern前面是固定的,这样Postgres就可以使用索引了。这样:some_string LIKE 'pattern%,而不是some_string LIKE '%pattern%
  • 最近有做垃圾收集(VACUUME)吗?你索引外键了吗?
  • 是否有表的扫描应该使用索引而没有使用?注意全表扫描是很难接受的
  • 好的数据库设计会比好的查询计划更重要,学习一下数据库设计范式?

不要在开发机器上尝试优化查询

Postgres计划器会收集关于数据的信息来帮助确定对于一个查询最好的执行计划。事实上,不仅需要真实的数据,而且数据库的配置也很关键。所以最好在测试环境-拥有和生产环境相同的数据以及相同的配置,这样来优化查询才能得到更好的结果。

实验是关键

在更好的优化系统上没有严格的规则。最好的建议就是尝试不同的配置,可以使用来帮助寻找系统瓶颈所在。并且自由的去尝试不同的索引组合,找到在特定环境下可以产生最好结果的查询。

Postgres高级优化要点

你已经添加了索引,包括部分索引和整个索引。你已经进行了VACUUM ANALYSEd。你已经在一个查询里JOINed并且INNER JOINed所有东西了,但是仍然是花费了很长时间,那么是时候深入了解一下一些不为人知的SQL优化知识了!

解释(EXPLAIN)发生了什么

到现在为止,你已经很熟悉使用EXPLAINEXPLAIN ANALYSE来查看Postgres在执行查询时候的相关信息。你也知道了INDEX SCAN要比SEQ SCAN更好。但是你选择了Heap Scan作为联结条件

查询计划可能并不容易阅读,然而,它们携带了一些可以适合机器阅读的数据。

可视化问题

Postgres Explain Viewer (PEV)是一个可视化查询计划的工具。从这里可以看出哪些地方耗时最长,但是知道这些仅仅只是个开始,那么我们怎么加速这些查询呢?

实现你的期望

视图是一个存储部分查询的工具。也就是说,视图允许数据库存储一个已经解析后的查询,这样以后你可以将它作为一个表来使用。你可以SELECT(有时候也可以UPDATE或者DELETE)一个视图,查询语法基本上都是一样的。如果你需要重复进行一些复杂的查询,使用视图会很有用。

自由的使用视图是好的关系型数据库设计的关键方面。视图允许你将表的细节压缩进来,并且可能因为应用的变化而变化,但是会提供一致的接口。

Postgres Views文档

一个视图可以被物化,意味着CREATE MATERIALIZED VIEWREFRESH MATERIALIZED VIEW执行的结果会被Postgres存储下来。部分查询的开销就在这里,但是我们可以以后持久受益,特别是查询繁重的情况(在我的经历里,大部分情况都是读繁重情况)

物化视图(Materialized Views)在当你在一些列的子集中进行查询的时候非常有用,当你做同样的操作,比如COUNT或者SUM,甚至是从一个jsonb对象中提取一部分数据时候,并且联结了额外的表。当实际检索行时候,可以查询这些行以返回相关数据。执行通常比完成语句更快,通过在物化视图本身上添加索引,可以更加受益。(比如你联结的列上,或者在报告查询的数据列上)

CTE(公用表表达式)和子查询

公用表表达式是类似于WITH expression_name AS (...) SELECT ...,子查询是类似于SELECT ... FROM (SELECT ...) AS subquery_name是用来解开繁琐的SQL查询的工具,有时候也是唯一能实现目标的。CTE会比子查询更容易阅读,在Postgres中它们是优化界限,阻止了查询优化器通过将约束移入或者移出CTE重写查询,比如这个查询:

SELECT
  x, count
FROM (SELECT x, count(x) count FROM big_table GROUP BY x) aggregates
WHERE x = 42;

查询可以通过将WHERE子句移到子查询里面来避免对big_table每一行进行读取和count操作

SELECT
  x, count
FROM (SELECT x, count(x) count FROM big_table GROUP BY x WHERE x = 42) aggregates;

然而一个CTE可能会阻止优化,导致去读整个表,然后聚合,在条件筛选之前物化到磁盘上。

WITH aggregates AS (SELECT x, count(x) count FROM big_table GROUP BY x)
SELECT
  x, count
FROM aggregates
WHERE x = 42;

有时候优化壁垒也是有用的,比如当在WITH中使用数据操作语句(INSERT, UPDATE, 或者DELETE)。因为CTE只执行一次,所以当一个子查询被计划器允许多次调用产生的结果也是一样的,并且也不会返回删除或者更新的行的信息。

通用表表达式和子查询都是有用的,在特定环境下可能会工作的不错。上面是一个子查询会更好工作的例子,但是我发现CTE会比子查询更快而且更好,所以我大部分会使用CTE。使用EXPLAIN ANALYSE来分析并找到最适合你的方式。

控制你自己。只获取需要的

我们应该使用SELECT col1...colN而不是SELECT *是我们降低了网络传输。在大多数情况下,对于当前的数据库系统以及高速网络传输,这似乎不是个问题。在一个例子中,包含326/89k行,查询单个列和整个列的差异在10%

一个限制查询列数的更好的原因是索引查询。我们希望查询器通过索引查询而不是整表扫描。我们通常会在重要的列比如外键和主键来建立索引加快查询。

我们也可以使用全索引,它会包含对查询有帮助的特定的列以及表达式,用来存储相关信息。select *可能会导致查询不使用全索引,除非你索引了整个表。

Preparation is key

准备语句将解析,分析,重写,计划和执行语句工作分开,和物化视图分割准备部分查询的工作相同,而且不会被结果该表。当一个语句准备好的时候,Postgres解析,分析并且重写它。它通常会使用占位符在当执行的时候填充值。

PREPARE是一种在单个事务中同时执行多次同一语句的时候一种优化方式。准备语句不会再事务之间共享或者储存。

当一个会话要执行大量类似语句时,预备语句可能会有最大性能优势。如果该语句很复杂(难于规划或重写),例如,如果查询涉及很多表的连接或者要求应用多个规则,性能差异将会特别明显。如果语句相对比较容易规划和重写,但是执行起来开销相对较大,那么预备语句的性能优势就不那么显著了。

更多

查看Postgres文档

参考


点赞走一波😏


评论

提交评论
sufaith
2019-05-06 @ 09:24:23 中国 河南 郑州

互访, https://www.sufaith.com 顺便帮忙点下侧边栏广告哦~

Windows:10-Other Chrome:73.0.3683