PostgreSQL 反模式：按名称迭代优化搜索或“来回优化”的故事

全国销售处数千名经理记录 我们的客户关系管理系统 每天有数以万计的联系人 — 与潜在或现有客户沟通的事实。 为此，您必须首先找到客户，而且最好是尽快找到客户。 这种情况最常发生在名字上。

因此，再次分析负载最重的数据库之一（我们自己的数据库）上的“繁重”查询也就不足为奇了 VLSI企业账户，我发现“在顶部” 请求按名称“快速”搜索 用于组织卡。

此外，进一步的调查发现了一个有趣的例子 先优化，后性能下降 请求经过多个团队的连续改进，每个团队的行为都是出于最好的意图。

0：用户想要什么？

[KDPV 故]

当用户谈论按名称进行“快速”搜索时，通常意味着什么？ 它几乎从来都不是对像这样的子字符串的“诚实”搜索 ... LIKE '%роза%' - 因为这样结果不仅包括 'Розалия' и 'Магазин Роза'但 'Гроза' 乃至 'Дом Деда Мороза'.

用户在日常生活中假设您将为他提供 按单词开头搜索 在标题中并使其更相关 开始于 进入。 你会做到的 几乎是瞬间 - 用于行间输入。

1：限制任务

更重要的是，一个人不会专门进入 'роз магаз'，因此您必须按前缀搜索每个单词。 不，对于用户来说，响应最后一个单词的快速提示比故意“低估”前面的单词要容易得多 - 看看搜索引擎是如何处理这个问题的。

完全没有 正确地 制定出问题的需求就已经解决了一半以上了。 有时仔细的用例分析 可以显着影响结果.

抽象开发人员做什么？

1.0：外部搜索引擎

哦，搜索很难，我根本不想做任何事情——让我们把它交给 devops！ 让他们在数据库外部部署一个搜索引擎：Sphinx、ElasticSearch……

一个可行的选择，尽管在同步和变化速度方面是劳动密集型的。 但在我们的例子中并非如此，因为搜索仅在每个客户的账户数据框架内进行。 并且数据具有相当高的可变性 - 如果经理现在已经输入卡 'Магазин Роза'，然后 5-10 秒后，他可能已经记得他忘记在那里标明他的电子邮件，并想要找到它并更正它。

因此 - 让我们 “直接在数据库中搜索”。 幸运的是，PostgreSQL 允许我们做到这一点，而且不仅仅是一种选择 - 我们将看看它们。

1.1：“诚实”子串

我们坚持使用“子串”这个词。 但是对于通过子字符串（甚至通过正则表达式！）进行索引搜索，有一个很好的方法 模块 pg_trgm！ 只有这样才需要正确排序。

我们尝试拿下图来简化模型：

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

我们在那里上传了 7.8 万条真实组织的记录并为其建立索引：

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

让我们查找行间搜索的前 10 条记录：

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[看看 explain.tensor.ru]

嗯，那...... 26 毫秒，31MB 读取数据和超过 1.7K 条过滤记录 - 10 个搜索记录。 管理费用太高了，难道就没有更有效的办法吗？

1.2：通过文字搜索？ 这是FTS！

确实，PostgreSQL 提供了非常强大的 全文搜索引擎 （全文搜索），包括前缀搜索的能力。 一个很好的选择，您甚至不需要安装扩展！ 咱们试试吧：

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

[看看 explain.tensor.ru]

这里查询执行的并行化对我们有一点帮助，将时间减少了一半 11毫秒。 我们总共需要阅读的内容减少了 1.5 倍 20MB。 但在这里，越少越好，因为我们读取的卷越大，缓存未命中的机会就越高，并且从磁盘读取的每一个额外的数据页都是请求的潜在“刹车”。

1.3：还喜欢吗？

前面的要求对大家都好，但是只有每天拉十万次才会来 2TB 读取数据。 在最好的情况下，从内存中，但如果你不幸运，那么从磁盘中。 因此，让我们尝试将其变小。

让我们记住用户想看到什么 第一个“以……开头”。 所以这是最纯粹的形式 前缀搜索 通过 text_pattern_ops！ 只有当我们“没有足够的”最多 10 条要查找的记录时，我们才必须使用 FTS 搜索来完成读取它们：

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[看看 explain.tensor.ru]

出色的表现 - 总计 0.05ms，略多于 100KB 读！ 只是我们忘记了 按名称分类这样用户就不会迷失在结果中：

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[看看 explain.tensor.ru]

哦，有些东西不再那么漂亮了——似乎有一个索引，但排序却飞过去了……当然，它已经比以前的选项有效很多倍了，但是……

1.4：“用文件完成”

但是有一个索引可以让你按范围搜索并仍然正常使用排序 - 常规B树!

CREATE INDEX ON firms(lower(name));

只有请求才需要“手动收集”：

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[看看 explain.tensor.ru]

非常好 - 排序工作正常，并且资源消耗仍然“微观”， 比“纯”FTS 有效数千倍！ 剩下的就是将其组合成一个请求：

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

注意执行的是第二个子查询 仅当第一个返回值低于预期时 最后一个 LIMIT 行数。 我说的是这种查询优化的方法 之前已经写过.

所以，是的，我们现在桌上有 btree 和 gin，但从统计数据来看，事实证明 不到 10% 的请求到达第二个块的执行。 也就是说，通过提前了解任务的此类典型限制，我们能够将服务器资源的总消耗减少近千倍！

1.5*：我们可以不用文件

更高 LIKE 我们被阻止使用不正确的排序。 但可以通过指定 USING 运算符来“设置在正确的路径上”：

默认情况下假设 ASC。 此外，您可以在子句中指定特定排序运算符的名称 USING。 排序运算符必须是某些 B 树运算符系列的小于或大于的成员。 ASC 通常等价 USING < и DESC 通常等价 USING >.

在我们的例子中，“少”是 ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[看看 explain.tensor.ru]

2：请求如何变坏

现在，我们将请求“酝酿”六个月或一年，我们惊讶地发现它再次“位于顶部”，并带有每日总“泵送”内存的指标（缓冲区共享命中）在 5.5TB ——也就是说，甚至比原来还要多。

不，当然，我们的业务增长了，我们的工作量增加了，但幅度不一样！ 这意味着这里有些可疑——让我们找出答案。

2.1：分页的诞生

在某个时候，另一个开发团队希望能够从快速下标搜索“跳转”到注册表，并获得相同但扩展的结果。 没有页面导航的注册表是什么？ 让我们搞砸吧！

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

现在可以通过“逐页”加载来显示搜索结果的注册表，而不会给开发人员带来任何压力。

当然，事实上， 对于每个后续数据页，读取的数据越来越多 （所有来自上一次的，我们将丢弃，加上必要的“尾巴”） - 也就是说，这是一个明显的反模式。 但是，在下一次迭代时从界面中存储的密钥开始搜索会更正确，但大约是另一次。

2.2：我想要一些异国情调的东西

在某些时候，开发人员想要 用数据使结果样本多样化 来自另一个表，之前的整个请求已发送到 CTE：

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

即便如此，这也不错，因为子查询仅针对 10 个返回的记录进行评估，如果不是的话......

2.3：DISTINCT是无意义和无情的

从第二个子查询演变过程中的某个地方 丢失 NOT LIKE 条件。 很明显，在这之后 UNION ALL 开始返回 有些条目两次 - 首先在行的开头找到，然后再次 - 在该行的第一个单词的开头找到。 在限制范围内，第二个子查询的所有记录都可以与第一个子查询的记录匹配。

开发人员不寻找原因会做什么？...毫无疑问！

• 双倍大小 原始样品
• 应用不同仅获取每行的单个实例

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

也就是说，很明显，最终的结果是完全相同的，但是“飞”到第二个 CTE 子查询的机会变得更高，即使没有这个， 显然更具可读性.

但这还不是最悲伤的事情。 由于开发商要求选择 DISTINCT 不是针对特定领域，而是同时针对所有领域 记录，然后 sub_query 字段（子查询的结果）会自动包含在那里。 现在，执行 DISTINCT，数据库必须已经执行 不是 10 个子查询，而是全部 <2 * N> + 10!

2.4：合作高于一切！

因此，开发人员继续生活 - 他们没有打扰，因为用户显然没有足够的耐心将注册表“调整”到显着的 N 值，并且接收每个后续“页面”的速度长期减慢。

直到其他部门的开发人员找到他们，想要使用这么方便的方法 用于迭代搜索 - 也就是说，我们从某个样本中取出一块，通过附加条件对其进行过滤，绘制结果，然后绘制下一块（在我们的例子中是通过增加 N 来实现的），依此类推，直到填满屏幕。

一般来说，在捕获的标本中 N达到了接近17K的值，仅一天之内，“沿链”执行了至少 4K 个此类请求。 最后一个被大胆地扫描了 每次迭代 1GB 内存...

在总

来源： habr.com