PostgreSQL索引带来的影响和开销

一、概述

索引通常被认为是SQL性能调优的灵丹妙药，PostgreSQL针对不同用例提供了不同类型的索引。经常看到许多关于“调优"的文章和讨论，讨论如何创建新索引来加快SQL的速度，但很少有人讨论删除它们。人们发现，创建越来越多索引的冲动在许多系统中造成了严重的破坏。很多时候，为了整个系统的利益，在考虑任何新索引之前，我们应该首先删除索引。惊讶吗?了解索引的结果和开销有助于做出明智的决策，并可能使系统避免许多潜在的问题。

二、影响和开销

从基础上来讲，我们应该记住索引是有成本的。索引带来了性能和资源消耗方面的成本。下面列出了过度使用索引可能导致的十个问题/开销。这篇文章是关于PostgreSQL的，但大多数问题也适用于其他数据库系统。

1.索引对事务的惩罚

添加索引后，可能会看到SELECT语句的性能有所提高。但是不要忘了，性能的提高是以同一表上的事务为代价的。从概念上讲，表上的每个DML都需要更新表的所有索引。尽管有很多优化可以减少写放大，但这是一个相当大的开销。

例如，假设一个表上有五个索引;对表的每次INSERT都会导致对这五个索引上的索引记录进行INSERT。逻辑上，五个索引页也将被更新。所以实际上，开销是5倍。

2.内存的使用

索引页必须在内存中，无论是否有查询使用它们，因为它们需要通过事务进行更新。这就导致表页可用的内存变得更少了。索引越多，有效缓存所需数据的内存就越多。如果我们不增加可用内存，就会开始损害系统的整体性能。

3.随机写：索引更新的成本很高

和向表中插入数据不同，索引行不太可能被插入到同一页中。众所周知，像B-Tree索引这样的索引会导致更多的随机写入。

4.索引比表需要更多的缓存

由于随机写和读，索引需要在缓存中存储更多的页面。索引的缓存需求通常比关联的表高得多。

5.WAL日志的生成

除了更新表生成WAL记录外，还会有用于索引的WAL记录。这有助于崩溃恢复和复制。如果你正在使用任何等待事件分析工具/脚本(如pg_gather)，则可以清楚地看到生成WAL的开销。实际影响取决于索引类型。

6.更多的I/O

除了生成WAL记录;也会有更多的脏页生成。当索引页成为脏页后，必须将它们写回文件，从而再次导致更多的I/O——"DataFileWrite"等待事件，如前面的屏幕截图所示。

另一个副作用是索引增加了活动数据集的总大小。"活动数据集(Active dataset)"，我指的是经常查询和使用的表和索引。随着活动数据集大小的增加，缓存变得越来越低效。效率较低的缓存导致更多的数据文件读取，因此读I/O增加。这是在为特定查询从存储中获取额外索引页所需的读I/O之外的。

另一个主要使用选择查询的系统的pg_gather报告再次显示了这个问题。随着活动数据集的增加，PostgreSQL别无选择，只能从存储中取出页面。

7.影响VACUUM/AUTOVACUUM

开销不仅仅是插入或更新索引页，正如前面所讨论的那样。由于索引还需要清理旧的元组引用，因此维护它也有开销。

我看到过这样的情况，由于表的大小，表上的索引数量过多，单个表上的autovacuum运行了很长时间。事实上，用户经常看到他们的autovacuum"卡住"了几个小时，没有任何进展，或持续时间更长。这是因为autovacuum的索引清理是autovacuum的不透明阶段，在pg_stat_progress_vacuum等视图中是不可见的，除非autovacuum被标示为vacuum索引。

随着时间的推移，索引会变得臃肿，效率会降低。在许多系统中可能需要定期索引维护(REINDEX)。

8.调优时的隧道视野(Tunnel vision while tuning)

用户可能专注于一个特定的SQL语句，试图"调优"并决定是否创建索引。通过为查询创建索引，我们将更多的系统资源转移到该查询上。然后，它可能会通过惩罚其他操作来更好地执行特定的查询。

随着我们不断为调优其他查询创建越来越多的索引，资源将再次转向其他查询。这将导致这样一种情况:调优每个查询的努力会损害其他查询。最终，每个人都会受到伤害，在这场战争中只有输家。试图调优的人应该考虑系统的每个部分如何共存(最大化业务价值)，而不是考虑特定查询的绝对最大性能。

9.存储需求

我几乎每天都看到索引比表占用更多存储空间的情况。

对于那些有更多钱花在存储上的人来说，这听起来可能太愚蠢了，但我们应该记住，这有一个级联效应的。数据库的总大小会增长到实际数据的很多倍。因此，备份显然会占用更多的时间、存储和网络资源，而且同样的备份会给主机带来更多的负载。这还会增加恢复备份和恢复备份的时间。更大的数据库会影响很多事情，包括花更多的时间来构建备用实例。

10.索引很容易损坏

这里说的不是类似bug：silent index corruption of PostgreSQL 14、或glibc collation change导致的问题。在数十年的数据库工作中，解除到索引损坏的案例也频繁。随着索引数的增加，概率也在增加。

三、如何正确的创建索引？

在考虑新索引时，应该考虑一系列关键问题:是否必须有这个索引，或者是否有必要以增加索引为代价来加快查询速度?是否有一种方法可以重写查询以获得更好的性能？放弃小收益，不创建索引可以吗?

现有的索引也需要在一段时间内进行严格的审查。应该考虑删除所有未使用的索引(那些在pg_stat_user_indexes中idx_scan为零的索引)。像pgexperts这样的脚本可以帮助进行更多的分析。

即将发布的PostgreSQL 16在pg_stat_user_indexes/pg_stat_all_indexes中增加了一个列，名为last_idx_scan，它可以告诉我们最后一次使用索引是什么时候(时间戳)。这将帮助我们全面了解系统中的所有索引。

简单来说就是:索引并不便宜，是有代价的，而且代价可能是多方面的。索引并不总是好的，顺序扫描也不总是坏的。我的建议是避免将改进个别查询作为调优的第一步，因为这是一个滑坡。自上而下的系统调优方法可以从主机、操作系统、PostgreSQL参数、Schema等开始调优，从而获得更好的结果。在创建索引之前，客观的"成本效益分析"很重要。