postgres 索引_使用简单索引优化Postgres中的复杂排序

postgres 索引

建筑泡沫。 在这一系列文章中，我们的工程团队讨论了 Bubble 的内部工作原理， Bubble 是一种基于云的可视化编程语言，使每个人都可以进行编程。

Bubble面临的一大挑战是将用户编写的查询转换为高性能SQL。 用户可以定义自己的数据模式，并编写自定义搜索以对结果进行过滤和排序。 搜索通常嵌入在用户的应用程序逻辑中，因此它们需要快速闪电并且尊重我们的事务语义。

为此，我们将查询转换为SQL并在Postgres后端数据库上运行它们。 做到既正确又高效是一个巨大的挑战。 这篇文章是关于一个有趣的问题，我们最近遇到了一些我们试图加快速度的慢速查询，以及如何解决这个问题。

（我假设您至少在一般级别上熟悉使用索引来加快数据库搜索的速度。如果没有，这是一个很好的入门指南： 使用The Luke ）。

我们事先不知道Bubble用户如何计划搜索他们创建的数据。 因此，我们快速进行搜索的策略是在检测到模式时即时自动建立索引。 如果几次发现搜索缓慢，我们将尝试建立索引以加快搜索速度。

但是索引很昂贵。 表上的每个索引都会增加表占用的磁盘空间，并且更糟的是，每次写入表都会增加开销。 因为每个索引都需要更新，所以在具有一百个索引的表中插入新行就像执行一百次写入，而不仅仅是一次写入！

因此，我们要确保我们建立的每个索引都能发挥作用。 我们希望索引可以用于许多不同的搜索，而不仅仅是一个搜索。 我们的索引类型是单列b树索引。 B树索引是Postgres的默认树，它们可用于相等匹配，不相等匹配和排序：它们是索引世界的万能主力。

最近，我们注意到了一些奇怪的事情。 在WHERE子句中没有任何内容的大型表上进行自定义排序的搜索的效果确实很差，即使在我们正在排序的列上使用b树索引时也是如此。 有时要花几秒钟才能运行搜索，仅返回5-10个结果。

从某种意义上说，这并不奇怪。 在数据库中，排序是一项非常昂贵的操作，因为即使您只想返回十个结果，仍然需要检查每一行以找出前十个是哪一行。 因此，如果表中有一百万行，那么按排序顺序获得十个结果可能会永远花费。

但是，索引提供了一种处理此问题的方法。 假设我们有一个正在开发游戏的用户，并且有一个玩家在游戏中获得的得分表：

CREATE TABLE scores (
 uid bigserial PRIMARY KEY,
 player_id bigint,
 timestamp bigint,
 score int
);

并且用户想要显示前10个得分，以及获得得分的人以及他们发生的日期，我们可以使用此查询来做到这一点：

SELECT * FROM scores ORDER BY score DESC LIMIT 10;

没有索引，Postgres将需要进行全表扫描，读取每个分数，以找出最高的10个分数。

但是，通过在score列上使用b树索引，Postgres可以进行索引扫描：它可以从高到低依次读取得分索引，直到找到10个结果。 由于我们的搜索没有其他限制，因此索引中的前十个条目是Postgres将返回的行，从而使该查询非常快速。

到目前为止，一切都很好。 但是实际上，我们的查询实际上要复杂得多。 一方面，Bubble应用程序可在客户端中实时更新数据。 这意味着我们需要始终以一致的顺序返回结果，因为如果刷新搜索并且顺序更改，我们不希望事情在页面上随意跳动。 因此，我们需要一种以一致的方式打破联系的方式。 我们为此使用uid列，因为它保证是唯一的。

我们的用户可能也想打断关系。 例如，在高分图表中，您可能希望通过排名第一个获得更高分数的人来打破平局。 因此，我们的实际排序条件可能是：

SELECT * FROM scores ORDER BY score DESC, timestamp, uid LIMIT 10;

我们还需要担心分数为空的行。 在游戏得分表中，我们可能没有任何缺少得分的条目。 但是请记住，我们不知道用户实际上在尝试构建什么。 也许在某些情况下空值确实有意义：无论哪种方式，我们都需要为它们做好准备。 Bubble总是在搜索结束时对null进行排序，因为我们认为这是处理它们的最直观的方式。 Postgres对空值的排序高于其他所有空值：它们在升序搜索的末尾，但在降序搜索的开始。 因此，我们需要重写它以获得所需的行为：

SELECT * FROM scores ORDER BY score DESC NULLS LAST, timestamp, uid LIMIT 10;

因此，关键时刻。 我们建立可信赖的b树索引：

CREATE INDEX ON scores (score);

…在表格中添加一百万分：

INSERT INTO scores (player_id, timestamp, score) VALUES (17, 1527875748779, 235);
… and so on …

并运行我们的搜索！

测验 ：会发生什么？

答 ：Postgres扫描整个百万行表🙁🙁🙁

原因是因为Postgres很聪明，但不那么聪明。 它知道它可以读取b树索引以加快排序操作，并且知道如何向前和向后读取索引以进行升序和降序搜索。 但是，要使它使用索引来加快排序速度，就需要知道索引中条目的顺序将始终完全满足排序所请求的顺序。

我们的索引可能与排序顺序不完全相同。 一方面，我们要求空值排序低于数字，但是索引将它们存储为高于数字。 事情可能不匹配的另一个可能原因是联系：如果两个条目的分数相同，我们要先按时间戳记断开联系，然后按uid断开联系。 但是，索引是单列：如果两个条目在得分上并列，则Postgres不保证它们的存储顺序。

从人的角度来看，这些差异很小。 该索引基本上已经处于正确的顺序中，只需要进行一些细微的调整就可以为我们提供所需的确切信息。 但是，从Postgres的角度来看，这些差异是全部的热门。 它没有代码可以即时进行调整，因此根本无法使用索引。 如此之近……而到目前为止。

一种解决方案是建立一个与搜索完全匹配的索引。 例如：

CREATE INDEX ON scores (score DESC NULLS LAST, timestamp, uid);

但这是一个丑陋，可悲的解决方案。 我们的用户经常以不同的方式对同一个表进行排序。 我们是否真的想为排序顺序的每种可能组合建立索引，并为每种排序付出性能损失？

同时，我们在分数列上有一个非常可爱的索引，无论如何，我们可能都需要创建索引以进行有效的分数比较，只是坐在那里，差不多，但还不够好。

Postgres可能不够聪明，无法使用我们的得分指数，但是没有理由我们不能比我们的数据库更聪明。

我们知道以下事实：

• 我们将在结尾处对null进行排序，因此在到达搜索结尾之前，我们无需担心null
• 除并列分数外，索引将以正确的顺序排列。 这意味着即使获得该得分的用户不同，该索引中的第10个最高得分也与我们结果中的第10个最高得分具有相同的得分数

所以，我们要做的就是这个。 在进行实数搜索之前，我们进行初步搜索以找到第十高的分数：

SELECT score FROM scores WHERE score IS NOT NULL ORDER BY score DESC OFFSET 9 LIMIT 1

Postgres 将对此查询进行索引扫描。 我们已经从搜索中完全排除了空值，并删除了“ NULLS LAST”排序。 现在，我们仅对单个列进行排序。 因此，索引的顺序现在完全可以满足查询要求。 这种搜索非常快：我们只需要读取索引中的前十个条目，然后从最后一个中返回分数即可。

如果此初步查询未返回任何结果，则意味着我们基本上是在尝试获取整个表（除非有100万个空条目，在这种情况下，得分指数无论如何都不会帮助我们）。 因此，在这种情况下，我们只需执行原始查询就无需进行任何修改，因为在这种情况下，全表扫描可能是我们最好的选择。

另一方面，如果我们确实获得了10分，我们会将其作为不平等约束添加到我们的原始查询中。 因此，例如，如果第十个分数是534，我们将原始查询更改为：

SELECT * FROM scores WHERE score >= 534 ORDER BY score DESC NULLS LAST, data, uid LIMIT 10

这里的窍门：Postgres的是不够聪明使用索引来进行排序的帮助，但它是足够聪明，做我们的分数索引扫描，以评估不平等！ 如果在此查询上运行EXPLAIN以查看Postgres如何执行该查询，您将看到它首先对得分> = 534进行索引扫描过滤，然后使用我们的完整排序顺序对该扫描的输出进行排序，然后应用10行限制。

这比整个表扫描要快得多。 如果并列第10位，我们的“> = 534”条件可能会返回10行以上，但是除非您有一百万次并列，否则第一次索引扫描返回的总行数可能接近10。手动排序，但只需要排序那些10位数的行，而无需排序原始的一百万行。

因此，此修改后的查询的性能几乎与我们为该特定搜索量身定制的高度特定的索引一样好，但是我们使用通用的简单索引来进行此操作，我们也可以将其用于其他一系列搜索。 胜利！！

如果您想在实践中使用此技术，请注意以下几点：如果在第一个查询中获取第十个分数的数据与第二个查询中获取最终结果之间的数据发生了变化（例如，如果您删除了较高的分数），则“> =”不等式可能会导致返回少于10个结果。 为了避免这种情况的发生，两个搜索必须在同一个事务中，并且该事务需要以REPEATABLE READ或更高的隔离模式运行，而不是Postgres的默认READ COMMITTED模式。

（有关隔离模式的更多信息，请访问： https ://www.postgresql.org/docs/9.6/static/transaction-iso.html。tl; dr是默认情况下，同一事务中的两个SELECT可以看到不同快照的快照数据库，但在“可重复读取”模式下，他们将看到数据库的相同快照）。

我们实际上是在用plv8编写的存储过程中运行两个查询，以最大程度地减少从服务器到数据库的往返延迟。 在生产中运行plv8本身就是一个有趣的话题：也许是以后发布的主题！

发现这个有趣吗？ 我们一直在寻找优秀的工程师加入我们 ！

最初于 2018 年6月4日 发布在 blog.bubble.is 。

翻译自: https://hackernoon.com/using-simple-indexes-to-optimize-complicated-sorts-in-postgres-1761b097d97c

postgres 索引