主页 / dba / 问题 / 267794
Accepted
Iman Tumorang
Asked: 2020-05-25 05:34:21 +0800 CST

如何使用 UUID v4 进行分页并在并发插入数据上创建时间？

  • 772

语境：

出于好奇，我正在为我的应用程序进行负载测试。然后结果发生了很多并发插入。

在对 create-endpoint 进行负载测试后，我正在尝试对 Fetch 端点进行负载测试，包括测试分页。对于分页，我结合了两列，id（带有 UUID v4 的 PK）和 created_time。此外，我添加了一个索引以加快排序。我从这里开始关注这些解决方案。

问题：

由于数据是同时插入的，因此有几行具有相同的 created_time，在我的情况下，同一时间戳中最多 100（行）。

这是我的表模式，一个例子

BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS "payment_with_uuid";

CREATE TABLE "payment_with_uuid" (
 id VARCHAR(255) PRIMARY KEY NOT NULL DEFAULT (uuid_generate_v4()),
 amount integer NULL,
 name varchar(255) default NULL,
 created_time TIMESTAMPTZ NOT NULL DEFAULT (now() AT TIME ZONE 'utc')
);

CREATE INDEX idx_payment_pagination ON payment_with_uuid (created_time, id);

COMMIT;

这是我的查询，

SELECT  * from payment_with_uuid ORDER BY  created_time DESC, id DESC LIMIT 10;

它将返回 10 行付款，假设数据看起来像这样，并假设时间戳相同，直到第 100 行 

+-------------------------------------+--------+------------+---------------------+
| id                                  | amount | name       | created_time        |
+-------------------------------------+--------+------------+---------------------+
| ffffa567-e95a-4c8b-826c-e2be6acaeb6d| 32003  | Allistair  | 2020-05-24 21:27:10 | 
| ffff2dd6-3872-4acc-afec-7a568935f729| 32003  | James      | 2020-05-24 21:27:10 | 
| fffe3477-1710-45c4-b554-b539a9ee8fa7| 32003  | Kane       | 2020-05-24 21:27:10 |

为了获取下一页，这是我的查询，

SELECT * FROM payment_with_uuid 
WHERE 
created_time <= '2020-05-24 21:27:10' :: timestamp
AND 
id <'fffe3477-1710-45c4-b554-b539a9ee8fa7' 
ORDER BY created_time DESC, id DESC LIMIT 10;

正因为如此，分页搞砸了，就像第一页上存在的一些记录一样，可能存在于第二、第三或任何页面上。有时记录会丢失。

问题和注意事项：

  • 有没有办法以更优雅的方式做到这一点？

  • 我知道使用自动增量可以解决这个问题，但是选择自动增量 ID 对我们来说不是一个选项，因为我们试图让微服务中的所有内容都保持一致，许多服务已经使用 UUID 作为 PK。

  • 使用偏移量和限制也可以解决这个问题，但据我所知，这不是一个好习惯，因为本文解释了https://use-the-index-luke.com/no-offset

  • 我正在使用 Postgres 11.4

postgresql pagination

1 个回答

  1. Best Answer
    SELECT * FROM payment_with_uuid 
WHERE 
created_time <= '2020-05-24 21:27:10' :: timestamp
AND 
id <'fffe3477-1710-45c4-b554-b539a9ee8fa7' 
ORDER BY created_time DESC, id DESC LIMIT 10;

    这是错误的，但它不应导致您指出同一行出现在第 1、2 等页上的问题。相反，它会导致大多数行根本无法显示，因为这两个过滤器是独立实现的. 您只需要在 created_time 过滤器的关系内实现 id 过滤器。我想优雅是一个见仁见智的问题，但在我看来，最优雅的解决方案是元组比较器，类似于您试图在原始问题中包含的内容。

    SELECT * FROM payment_with_uuid 
WHERE 
(created_time,id) < ('2020-05-24 21:27:10' :: timestamp, 'fffe3477-1710-45c4-b554-b539a9ee8fa7') 
ORDER BY created_time DESC, id DESC LIMIT 10;

    现在时间戳应该真的很准确，它看起来不像你的。它如何四舍五入到最接近的秒数？在我手中它看起来更像2020-05-25 09:16:29.380925-04

    如果由于某种原因您不想使用元组比较器，那么您需要包含两次时间戳，一次是小于，一次是等于：

    WHERE 
created_time < '2020-05-24 21:27:10' :: timestamp
OR  
(
    created_time = '2020-05-24 21:27:10' :: timestamp 
    AND 
    id <'fffe3477-1710-45c4-b554-b539a9ee8fa7' 
)

    除了不太优雅之外，这可能不会非常有效地使用索引。您可以使用布尔推理来重写它以避免顶级 OR，以便它可以使用索引，但是它会变得更难阅读和理解。

    • 6

相关问题