主持人 > 博客 > 管理 > PostgreSQL 反模式：浏览注册表

PostgreSQL 反模式：浏览注册表

今天，SQL 中将不再有复杂的情况和复杂的算法。 一切都会非常简单，在 Captain Obvious 的水平上 - 让我们开始吧 查看事件注册表 按时间排序。

也就是数据库中有一个标志 events，她有一个领域 ts - 正是我们想要有序显示这些记录的时间：

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

很明显，我们不会有十几条记录，所以我们需要某种形式的 页面导航.

#0。 “我是我母亲的大屠杀犯”

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

这几乎不是一个笑话——它很罕见，但却是在野外发现的。 有时，在使用 ORM 后，可能很难切换到“直接”使用 SQL。

但让我们继续讨论更常见和不太明显的问题。

#1. 抵消

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

26这个数字是怎么来的？ 这是填满一个屏幕的大致条目数。 更准确地说，显示了 25 条记录，再加上 1 条，表明样本中至少还有其他内容，因此继续前进是有意义的。

当然，这个值不能“缝”到请求体中，而是通过参数传递。 但在这种情况下，PostgreSQL 调度程序将无法依赖记录相对较少的知识 - 并且很容易选择无效的计划。

在应用程序界面中，查看注册表是通过在可视“页面”之间切换来实现的，很长一段时间没有人注意到任何可疑之处。 直到那一刻，为了方便起见，UI/UX 决定将界面重新设计为“无限滚动”——也就是说，所有注册表项都绘制在用户可以上下滚动的单个列表中。

所以，在下一次测试中，你被抓住了 重复记录 在注册表中。 为什么呢，因为表有正常索引 (ts)，您的查询依赖于哪个？

正是因为你没有考虑到这一点 ts 不是唯一的密钥 在此表中。 事实上，并且 它的值并不唯一，就像现实条件下的任何“时间”一样 - 因此，由于对相同键值进行排序的框架内的最终顺序不同，两个相邻查询中的相同记录很容易从一个页面“跳转”到另一个页面。

事实上，这里还隐藏着第二个问题，这个问题更难被注意到—— 某些条目将不会显示 根本！ 毕竟，“重复”记录取代了别人的位置。 详细的解释和精美的图片可以找到 在这里阅读.

扩大索引

狡猾的开发人员知道索引键需要唯一，最简单的方法是用明显唯一的字段扩展它，PK 非常适合：

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

并且请求发生了变化：

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. 切换到“光标”

一段时间后，DBA 来找您，并对您的请求感到“高兴” 他们用 OFFSET 规则加载服务器，一般来说，是时候切换到 从最后显示的值开始导航。 您的查询再次发生变化：

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

直到它到来，你才松了一口气……

＃3。 清洁指标

因为有一天你的 DBA 读到 关于查找无效索引的文章 并意识到 “不是最新的”时间戳不好。 我又来找你了 - 现在我的想法是该索引仍然应该变回 (ts DESC).

但是如何解决页面之间“跳跃”记录的最初问题呢？...一切都很简单 - 您需要选择记录数量不固定的块！

一般来说，谁禁止我们读的不是“正好26”，而是“不少于26”？ 例如，在下一个块中有 具有明显不同含义的记录 ts - 那么就不会出现块之间“跳跃”记录的问题了！

以下是实现这一目标的方法：

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

这里发生了什么？

  1. 我们“向下”步进 25 条记录并得到“边界”值 ts.
  2. 如果那里已经没有任何内容，则将 NULL 值替换为 -infinity.
  3. 我们在接收到的值之间减去整个段的值 ts 以及从接口传递的 $1 参数（之前的“最后”渲染值）。
  4. 如果返回的块的记录少于 26 条，则它是最后一个。

或者同一张图片：
PostgreSQL 反模式：浏览注册表

因为现在我们有 样本没有任何特定的“开始”，那么没有什么可以阻止我们向相反的方向“扩展”这个请求，并实现从“参考点”两个方向（向下和向上）动态加载数据块。

备注

  1. 是的，在这种情况下，我们访问索引两次，但一切都是“纯粹通过索引”。 因此，子查询只会导致 到一项额外的仅索引扫描.
  2. 很明显，只有当你有价值观时才能使用这种技术 ts 只有偶然才能穿越，并且 其中很少有人。 如果您的典型情况是“00:00:00.000 有一百万条记录”，您不应该这样做。 我的意思是，你不应该允许这样的情况发生。 但如果发生这种情况，请使用带有扩展索引的选项。

来源： habr.com

添加评论