索引通常被认为是万能的SQL性能调优时,和PostgreSQL支持不同类型的索引迎合不同的用例。我总是看到很多文章,讨论“调谐”讨论如何创建新索引加速SQL但很少的讨论删除它们。创建越来越多的索引的冲动被发现在许多系统造成严重的破坏。很多时候,删除索引是我们应该做的事情之前,先考虑任何新的索引,造福整个系统。惊讶吗?理解索引的后果和开销有助于做出明智的决定,可能拯救许多潜在的系统问题。
在一个非常基本的层面上,我们应该记住,索引不是免费的。的好处有一个成本的性能和资源消耗。下面是十个问题的列表/管理费用的过度使用会导致索引。这篇文章是关于PostgreSQL,但大多数的问题同样适用于其他数据库系统。
1。索引惩罚事务
我们可能会看到一个SELECT语句的性能的改善后,添加一个索引。但我们不应忘记的性能收益与成本交易相同的表。从概念上讲,e非常DML表需要更新所有表的索引。尽管有很多的优化减少写放大,这是一个相当大的开销。
例如,让我们假设有5个表上的索引;每个插入索引的表将导致一个插入记录这五个索引。从逻辑上讲,五个索引页也将被更新。所以有效,开销5 x。
2。内存使用情况
必须在内存中索引页,无论任何查询使用他们,因为他们需要通过交易得到更新。有效,可用内存的页表变得更少。索引越多,更多的有效的缓存内存的要求。如果我们不增加可用内存,这开始伤害整个系统的性能。
3所示。随机写道:更新索引更昂贵
与将新记录插入表、行不太可能被插入到相同的页面。索引更喜欢b -树索引是已知的导致随机写道。
4所示。比表索引需要更多的缓存
由于随机读和写,索引需要更多页面缓存。索引缓存要求通常远高于相关表。
5。细胞膜的一代
除了WAL表的记录更新,也会犯下记录索引。这有助于在崩溃恢复和复制。如果您正在使用任何等待事件分析工具/脚本pg_gather,犯下一代的开销将会清晰可见。实际的影响取决于索引类型。
这是一个合成的测试用例,但如果WAL-related等事件出现的最高等事件,这是一个关心事务系统的问题,我们应该采取每一步来解决它。
6。越来越多的I / O
不仅犯下记录生成;我们将有更多的页面被踩。索引页会被踩,他们必须写回到文件,导致更多的I / O几”DataFileWrite”等事件,如之前的截图。
另一个副作用是增加总索引Active-Dataset大小。“活跃的数据集,”我的意思是频繁的表和索引查询和使用。随着活动的数据集的大小增加,缓存效率变得越来越少。有效缓存导致更多的数据文件读取,所以读I / O增加。这是除了阅读I / O要求带来的额外的索引页存储特定的查询。
再次pg_gather报告中主要是选择的另一个系统查询显示了这个问题。随着Active-Dataset增加,PostgreSQL别无选择,只能把存储的页面。
一个更重大的比例”DataFileRead“持续较长的时间,表明Active-Dataset大得多,这不是可缓存。
7所示。影响真空/ AUTOVACUUM
插入或更新的开销不仅是索引页,与前面讨论的点。在维护开销,因为旧的元组引用的索引也需要清理。
我看到情况autovacuum工人一个表运行很长时间,因为表的大小,最重要的是,过度数量的表上的索引。事实上,它是广泛的,用户看到他们autovacuum职工“困”几个小时没有显示任何进展较长的时间。这是因为该指数由autovacuum清理autovacuum的不透明的阶段,通过观点是不可见的pg_stat_progress_vacuum除了真空阶段表示吸尘索引。
索引可以臃肿和随着时间的推移变得不那么有效。周期性的索引维护(重建索引)可能需要在许多系统。
8。隧道视野而调优
井蛙之见的损失是字段的视图。用户可能会专注于一个特定的SQL语句,以“优化”,决定创建索引。为调优查询创建一个索引,查询我们将更多的系统资源。那么可能会提供更多的性能,特别声明惩罚别人。
但当我们继续创建越来越多的索引优化其他查询,将再次转向其他的资源查询。这就导致了情况的努力优化每一个查询惩罚其他查询。最终,每个人都会受到伤害,只有失败者将会在这场战争中。有人试图调整应考虑系统的每一部分如何共存(业务价值最大化)而不是绝对最大某个特定查询的性能。
9。更大的存储要求
几乎每一天,我看到的情况下,采取更多的索引存储比平板电脑。
这可能听起来太愚蠢了那些有更多的钱花在存储,但我们应该记住,这有一个级联效应。总数据库大小的增长倍数的实际数据。显然,备份需要更多时间、存储和网络资源,然后同样的备份可以把更多的主机上的负载。这也会增加时间恢复备份和恢复。更大的数据库影响很多事情,包括更多的时间来建立备用实例。
10。索引更容易腐败
我不只是在谈论很少发生与索引相关的bug沉默的腐败指数PostgreSQL 14由于或索引腐败glibc排序变化,现在出现了,然后今天甚至影响了许多环境中。与数据库在几十年的工作,我已经观察到索引腐败更频繁地报道。(我希望任何参与PostgreSQL多年,看到数以百计的情况下都会同意我)。当我们增加索引的数量时,我们增加的概率。
我们应该做些什么呢?
一组关键问题应该伴随新的指数考虑:这个索引是必不可少的,或有必要加快查询的成本指数?有办法重写查询来获得更好的性能吗?可以抛弃小收益和没有索引?
现有索引也需要一个评论在一段时间内。所有未使用索引(索引idx_scan为零的pg_stat_user_indexes)应该考虑下降。脚本类似pgexperts可以做更多的分析。
即将到来的PostgreSQL 16日有一个更多的列pg_stat_user_indexes / pg_stat_all_indexes用这个名字last_idx_scan,它可以告诉我们上一次使用的指数(时间戳)。这将帮助我们消息灵通的看看所有的索引系统。
总结
摘要在简单的词语:索引是不便宜。有一个成本,成本也是多方面的。索引并不总是很好,而且顺序扫描并不总是坏的。我卑微的建议是为了避免寻找改善单个查询的第一步,因为这是一个滑坡。自顶向下的方法来调优系统会产生更好的效果从优化主机,操作系统,PostgreSQL参数,模式等。一个客观的“成本效益分析”是重要的在创建索引。
我们世界级的PostgreSQL的雷竞技公司电话培训告诉你所有关于索引:何时使用它们,当不使用它们的时候,它们如何影响您的系统,等来强化教师指导培训课程。雷竞技公司电话
