问题[pagination](dba)

语境：

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

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

• https://stackoverflow.com/a/58943917/4075313
• https://dba.stackexchange.com/a/205385/117466

问题：

由于数据是同时插入的，因此有几行具有相同的 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

我是数据库的新手。我想在大表上使用实现分页，rowid但我发现rowid可以是字母数字，但不一定按任何顺序排列，所以我想知道我们是否可以使用它们对表进行分页。我想从中获取分页记录的表是一个非常大的表，有超过 20 亿条记录，如果我们根据偏移量和长度执行查询，则会出现错误 Ora1652 无法扩展表空间临时中的临时段. 那么有没有办法使用rowid.

任何帮助将不胜感激提前谢谢你。

编辑：

假设我有一个表说LARGE_ROWS有ID两RANDOM列，而ID是主键列。

SELECT /*+ ordered use_nl(p s) */
    *
FROM
    (
        SELECT
            ROWNUM RN,
            RD
        FROM
            (
                SELECT
                    ROWID RD
                FROM
                    large_rows
                ORDER BY
                    large_rows.id
            ) T
        WHERE
            ROWNUM < 1000
    ) P,
    large_rows S
WHERE
    RN > 900
    AND P.RD = S.ROWID;

我正在实现基于游标的分页。

要求是按任意条件（例如描述）对数据集进行排序，并逐行返回N行X（其中X表示最后一页的最后一项）。

第一页很简单：

SELECT
  id
FROM probe
ORDER BY description ASC
LIMIT 5

假设此查询返回 IDs 4, 5, 2, 1, 3，那么下一个查询必须在id3 之后从同一个查询中产生 5 个下一个结果。

注意：我们不能LIMIT ... OFFSET在第一页之后使用，因为偏移量是相对于我们不知道的引用结果的位置。

我能想到解决这个问题的唯一方法是：

在数据集中查找 ID 3 的行号：

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  )
SELECT
  di1.row_number
FROM data_index di1
WHERE di1.id = 3;

然后使用位置来偏移数据集。

全部一起：

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  ) 
SELECT
  di1.id
FROM data_index di1
WHERE di1.row_number > (
  SELECT
    di1.row_number
  FROM data_index di1
  WHERE di1.id = 3
)
LIMIT 10;

这种方法的缺点是data_index需要将整个子查询加载到内存中。有没有更有效的方法？

我在这些表上实现了键集分页

CREATE TABLE profiles (
    id serial primary key,
    username varchar(255),
    followers bigint,
    tsv tsvector
);

CREATE TABLE profile_categories (
    username varchar(255),
    category varchar(255),
    primary key (username, category)
);

使用以下查询：

SELECT profiles.*, categories,
       ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers) AS rank
  FROM profiles
  JOIN plainto_tsquery($1) AS q ON tsv @@ q
  LEFT OUTER JOIN profile_categories ON profiles.username = profiles_categories.username
  WHERE followers IS NOT NULL AND
    followers > 0 AND
    (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers), -id) < ($2, $3)
  ORDER BY (
    ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers),
    -id
  ) DESC
  LIMIT 50;

这个想法是使用ts_rank_cd全文搜索查询对文档进行排名，并通过关注者的对数来缩放排名。

$2问题是，当我通过提供上一页的最后排名和 id (和)查询第二页时$3，我仍然从上一页得到最后一个结果作为第一个结果（所以我实际上只得到 49 个新结果） ！我不知道这是怎么可能的，因为我使用的是严格的不等式运算符。

编辑即使我decimal对相同的问题进行排名 - 我在一页的末尾和下一页的开头得到重复的结果：

SELECT profiles.*, categories,
       (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal AS rank
  FROM profiles
  JOIN plainto_tsquery($1) AS q ON tsv @@ q
  WHERE followers IS NOT NULL AND
    followers > 0 AND
    ((ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal, -id) < ($2, $3)
  ORDER BY (
    (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal,
    -id
  ) DESC
  LIMIT 50

我有两个表 api_user 和 api_user，其中 api_user 具有用户表的外键。这两个表的架构如下所列。

                                 Table "public.api_user"
   Column    |           Type           |                       Modifiers                       
--------------+--------------------------+-------------------------------------------------------
 id           | integer                  | not null default nextval('api_user_id_seq'::regclass)
 is_admin     | boolean                  | not null
 is_agent     | boolean                  | not null
 is_guide     | boolean                  | not null
Indexes:
    "api_user_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "api_userprofile" CONSTRAINT "api_userprofile_user_id_5a1c1c92_fk_api_user_id" FOREIGN KEY (user_id) REFERENCES api_user(id) DEFERRABLE INITIALLY DEFERRED


                                         Table "public.api_userprofile"
         Column         |          Type           |                          Modifiers                           
------------------------+-------------------------+--------------------------------------------------------------
 id                     | integer                 | not null default nextval('api_userprofile_id_seq'::regclass)
 percent_complete       | numeric(3,0)            | not null
 display_name           | character varying(128)  | not null
 city                   | character varying(64)   | not null
 user_id                | integer                 | not null
Indexes:
    "api_userprofile_pkey" PRIMARY KEY, btree (id)
    "api_userprofile_user_id_key" UNIQUE CONSTRAINT, btree (user_id)
Foreign-key constraints:
    "api_userprofile_user_id_5a1c1c92_fk_api_user_id" FOREIGN KEY (user_id) REFERENCES api_user(id) DEFERRABLE INITIALLY DEFERRED

当我运行以下查询时：

select 
    api_user.id, 
    api_userprofile.display_name, 
    api_userprofile.city
FROM "api_user" 
LEFT OUTER JOIN "api_userprofile" ON ("api_user"."id" = "api_userprofile"."user_id") 
WHERE ((("api_user"."is_admin" = false 
    AND "api_userprofile"."percent_complete" >= 60.0 
    AND "api_userprofile"."id" IS NOT NULL)) 
    AND "api_user"."is_guide" = true)
ORDER BY "api_userprofile"."city" ASC LIMIT 20;

它返回：

id  |       display_name        |           city           
-----+---------------------------+--------------------------
 299 | Mohsin Khan               | Agra
  93 | Rizwan Mohd               | Agra
 126 | Abdhesh Sharma            | Agra
  39 | Rashid Ahmed              | Agra
 244 | Nishkam Sharma            | Ajmer
  42 | Parminder Mahla           | Amritsar
 131 | Prashant Hullatti         | Ballry
 241 | Pankaj Anand              | Bangalore
  89 | Niraj K. Singh            | Bodhgaya, Nalanda, Patna
 204 | Ravi Rocks                | Bokaro
  19 | Ian Lotriet               | Cape Town
  15 | Ivy Almacin               | Cape Town
  38 | Dr Brahm Prakaah Tripathi | Delhi
 130 | Virendra Singh            | Delhi
 271 | Satish Jain               | Delhi
 110 | Vikas Agarwal             | Delhi
 114 | Devi Singh Rathore        | Delhi
  58 | Dilip Singh Chanpawat     | Delhi
  95 | Anam Kumar Dhasmana       | Delhi
  51 | Gopal Sharma              | Delhi

使用偏移量 20 返回再次运行查询：

 id  |       display_name        |    city    
-----+---------------------------+------------
  95 | Anam Kumar Dhasmana       | Delhi
 114 | Devi Singh Rathore        | Delhi
 252 | Tarun Pratap Singh        | Delhi
 258 | Rajesh Kumar Pal          | Delhi
 255 | Chandan Singh Shekhawat   | Delhi
 268 | Amit Kumar                | Delhi
 100 | Ketan Mehra               | Delhi
 286 | Vikash Poonia             | Delhi
  61 | Belinda Schempers         | Durban
  67 | Pieter Janse Van Rensburg | Hoedspruit
 140 | Dr Hari Krishna Somanchi  | Hyderabad
 197 | Sstya Prabha              | Hyderabad
 118 | Dalpat Jodha              | Jaipur
 253 | Yash Shekhawat            | Jaipur
 120 | Govind Sharma             | Jaipur
 257 | Abhimanyu Singh           | Jaipur
  99 | Ghanshyam Singh           | Jaisalmer
 308 | Nitin Lobo                | Jodhpur
 124 | Rajendra Singh            | Jodhpur
  55 | Umed Gehlot               | Jodhpur

从输出中可以看出，在第一个查询和下一个偏移量为 20 的查询中都返回了一些重复项（参见 ID 为 114 的用户）。

使用 distinct 似乎工作正常，但为什么它在按相关表（用户配置文件）上的字段排序时返回重复项？

显然，如果我按 user.id 订购，那么它似乎也能正常工作并且不会返回重复项。

这里 user 和 userprofile 之间的关系是一对一的，没有 user.id 在 userprofile.user_id 中被多次引用（由框架（django）强制执行）。

我有一个非常大的数据库，大约 100 GB。我正在执行查询：

select * from <table_name>;

我只想显示第 100 到第 200 行。

我想了解这是如何在内部发生的。数据库是否将所有记录从磁盘提取到内存并将第 100 到第 400 行发送回查询客户端？或者是否存在任何机制，以便仅从数据库中获取那些记录（第 100 条 -200 条） - 通过使用 B 树等索引机制？

我发现这与分页概念有关，但我无法准确找到它在数据库级别内部是如何发生的。

假设我有一张包含这些数据的表格：

我想通过 createdAt 列 DESC 和 id 列 DESC 来订购它

select id, createdAt from post order by createdAt desc, id desc

现在看起来像这样：

我想用每页 2 个项目对其进行分页并且不想使用offset，只是limit：

select id, createdAt from post order by createdAt desc, id desc limit 2

要获取接下来的 2 项，我使用此查询：

SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2

我可以这样继续下去。获取最后一项createdAt，id然后将其用于next页面查询。

但我试图制定previous page查询近两天，但还没有找到办法。

这是我已经尝试过的：

从当前结果中获取第一项（而不是最后一项）使用它的id和createdAt字段并反转查询（createdAt >=, id >, created at >）中的条件。但是这个查询总是给我前两个结果（这是正常的，因为提供这个条件的行是前两个）。

我已经没有想法了。我需要帮助。谢谢。

我有一个包含三列的表：HashUID1、HashUID2、Address_Name（这是一个文本电子邮件地址，前两个哈希列是一些疯狂的创建，用于将事件参与者表链接到电子邮件地址。它很丑，几乎没有用我的控制。关注 address_name 索引）

它有 7800 万行。排序不正确。无论如何，该索引被拆分到许多快速 LUN 上并执行非常快速的索引查找。

我需要创建一系列查询以一次仅提取 20,000 个“每页行”，但要避免冲突或重复。由于没有标识列或易于排序的列，是否有一种简单的方法来选择所有内容并翻页？

我说的是否正确，如果我从 hugetablewithemails 中执行 select * from hugetablewithemails 到临时表中，然后通过 row_number 选择该表在事务期间保留在内存中，这对我来说是过多的内存资源？这似乎是首选的分页方法。我宁愿按统计百分比分页。:(

有一个索引按顺序维护 address_name 电子邮件地址，并且维护良好。在过去的一周里，我一直想通过花一些时间来研究构建一个基于窗口函数吐出范围的过程来帮助其他开发人员，该函数基于统计数据（我不擅长，但这个查询真的让我感兴趣）到提供从 1 到（变量）LEFT LIKE 字符的索引范围，满足 20,000 行——但我什至没有时间开始查询……

几个问题：

1. 有什么建议么？不是在寻找实际代码，只是根据经验寻找一些提示或建议，也许是警告。我想避免在初始扫描后进行额外的索引扫描。

2. 这是正确的方法吗？

3. 我正在考虑打破所有电子邮件地址的索引总和，收集行数 (*)、/20,000，并将其用作窗口函数，根据总行数的百分比对最小/最大子字符串 (1,5) 值进行分组建立分组范围。想法？

这是针对无法修改源数据库的 ETL 过程。

我希望通过一次完整的索引扫描我可以做到：

• 查询以根据索引使用情况（按字母顺序排序）获取直方图，并使用 min/max 将其分解（窗口化）以创建这样的范围，以便轻松查找所需的索引：

• A-> AAAX，（例如 20k 行）AAA-Z，B->（另外 20k），B->BAAR -> BAAR-> CDEFG -> CDEFH > FAAH，等等。

我们为此 ETL 过程在这些数据库中运行读取提交。我们只是试图将它分批处理成 20k 行，因为 DBA 说我们通过抓取完整的表使用了太多的网络资源。如果数据发生变化（这是一个问题），我们会即时更新 DW 和暂存表。

我很想使用临时表，但如果我这样做了，我会溢出到 tempdb 并通过电子邮件从 DBA 那里得到关于它的抨击，而且数据库太大了。