Get best of the week

PostgreSQL反模式：注册表导航

今天，将没有复杂的情况和复杂的SQL算法。在船长的级别上，一切都将非常简单证据-我们对事件记录进行了审查，并按时间进行了排序。

也就是说，基座中有一个板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个条目，则为最后一个。

或同一张图片：


从现在开始，我们的示例没有任何明确的“开始”，因此没有任何东西可以阻止我们在相反方向上“逆转”此请求，并实现从“参考点”两个方向（上下）动态加载数据块。

评论


  1. , , « ». Index Only Scan.
  2. , , ts , . — « 00:00:00.000», . , . , .

More articles:


All Articles