数据库大表中基于Keyset分页对大偏移量分页的优化

场景和问题

最近把主力数据库都全部切换成了PostgreSQL，并用于它开发了物料管理系统demo。为了测试大量数据对性能的影响，我给本地的库存表灌入了几十万种物料和80万行库存项。在关键词模糊搜索、多表联查等环节，我对其性能表现都很满意；但是当我在盘库凭证中添加抽盘项，而分页查询库存的页码又超大时，在前端拿到分页结果竟然要1.2s左右。毫无疑问，这个时间是有优化空间的。

查询库存的SQL语句类似于：

SELECT t."Id", t."Amount", t."Batch", t."CreatedAt", t."ExpiredAt", t."ExternalCode", t."IsLocked", t."Locker", t."MaterialCode", t."MaterialId", t."Note", t."ProducedAt", t.xmin, t."Scope", t."Slot", t."StockKind", t."WarehouseId", m0."Id", m0."Code", m0."MeasureUnitId", m0."ShortName", m0."Specification", m0."Status", m1."Id", m1."Abbrev", m1."Description", m1."Name", m2."Id", m2."Code", m2."Name", m2."PlantId"
      FROM (
          SELECT m."Id", m."Amount", m."Batch", m."CreatedAt", m."ExpiredAt", m."ExternalCode", m."IsLocked", m."Locker", m."MaterialCode", m."MaterialId", m."Note", m."ProducedAt", m.xmin, m."Scope", m."Slot", m."StockKind", m."WarehouseId"
          FROM mm_inventory AS m
          WHERE m."StockKind" IN (1, 2, 3) 
          ORDER BY m."Id"
          LIMIT 10 OFFSET 790000
      ) AS t
      INNER JOIN mm_material AS m0 ON t."MaterialId" = m0."Id"
      INNER JOIN mm_material_measureunit AS m1 ON m0."MeasureUnitId" = m1."Id"
      INNER JOIN mm_warehouse AS m2 ON t."WarehouseId" = m2."Id"
      ORDER BY t."Id"

上面这条查询语句稍显啰嗦，不过通过分析，很容易发现瓶颈出现在最内层的子查询上：

"Nested Loop  (cost=82947.67..83048.16 rows=10 width=481) (actual time=1175.072..1177.488 rows=10 loops=1)"
"  ->  Nested Loop  (cost=82947.37..83032.90 rows=10 width=466) (actual time=1174.950..1177.358 rows=10 loops=1)"
"        ->  Nested Loop  (cost=82947.23..83032.38 rows=10 width=138) (actual time=1174.721..1177.113 rows=10 loops=1)"
"              ->  Limit  (cost=82946.80..82947.85 rows=10 width=99) (actual time=1174.373..1174.396 rows=10 loops=1)"
"                    ->  Index Scan using ""PK_mm_inventory"" on mm_inventory m  (cost=0.42..83996.86 rows=800001 width=99) (actual time=0.011..1150.575 rows=790010 loops=1)"
"                          Filter: (""StockKind"" = ANY ('{1,2,3}'::integer[]))"
"                          Rows Removed by Filter: 1"
"              ->  Index Scan using ""PK_mm_material"" on mm_material m0  (cost=0.42..8.44 rows=1 width=39) (actual time=0.270..0.270 rows=1 loops=10)"
"                    Index Cond: (""Id"" = m.""MaterialId"")"
"        ->  Memoize  (cost=0.15..0.16 rows=1 width=328) (actual time=0.023..0.023 rows=1 loops=10)"
"              Cache Key: m0.""MeasureUnitId"""
"              Cache Mode: logical"
"              Hits: 9  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB"
"              ->  Index Scan using ""PK_mm_material_measureunit"" on mm_material_measureunit m1  (cost=0.14..0.15 rows=1 width=328) (actual time=0.221..0.221 rows=1 loops=1)"
"                    Index Cond: (""Id"" = m0.""MeasureUnitId"")"
"  ->  Memoize  (cost=0.30..7.91 rows=1 width=15) (actual time=0.012..0.012 rows=1 loops=10)"
"        Cache Key: m.""WarehouseId"""
"        Cache Mode: logical"
"        Hits: 9  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB"
"        ->  Index Scan using ""PK_mm_warehouse"" on mm_warehouse m2  (cost=0.29..7.90 rows=1 width=15) (actual time=0.117..0.117 rows=1 loops=1)"
"              Index Cond: (""Id"" = m.""WarehouseId"")"
"Planning Time: 1.985 ms"
"Execution Time: 1177.532 ms"

我们把上面查询语句简化一下：

SELECT m."Id", m."Amount", m."Batch", m."CreatedAt", m."ExpiredAt", m."ExternalCode", m."IsLocked", m."Locker", m."MaterialCode", m."MaterialId", m."Note", m."ProducedAt", m.xmin, m."Scope", m."Slot", m."StockKind", m."WarehouseId"
FROM mm_inventory AS m
WHERE m."StockKind" IN (1, 2, 3) 
ORDER BY m."Id"
LIMIT 10 OFFSET 790000

其执行计划为：

"Limit  (cost=82946.80..82947.85 rows=10 width=99) (actual time=1166.522..1166.529 rows=10 loops=1)"
"  ->  Index Scan using ""PK_mm_inventory"" on mm_inventory m  (cost=0.42..83996.86 rows=800001 width=99) (actual time=0.422..1142.905 rows=790010 loops=1)"
"        Filter: (""StockKind"" = ANY ('{1,2,3}'::integer[]))"
"        Rows Removed by Filter: 1"
"Planning Time: 0.108 ms"
"Execution Time: 1166.547 ms"

可以看到，这里Limit耗时达到惊人的1166.547 ms，而同样的语句在OFFSET 100的情况下，查询耗时只有0.709ms：

"Limit  (cost=10.92..11.97 rows=10 width=99) (actual time=0.681..0.692 rows=10 loops=1)"
"  ->  Index Scan using ""PK_mm_inventory"" on mm_inventory m  (cost=0.42..83996.86 rows=800001 width=99) (actual time=0.572..0.685 rows=110 loops=1)"
"        Filter: (""StockKind"" = ANY ('{1,2,3}'::integer[]))"
"Planning Time: 0.102 ms"
"Execution Time: 0.709 ms"

其原理是，数据库在筛选最终结果时，虽然只返回了10条数据，但是前N条数据是扎扎实实要去读取然后抛弃的——这部分意义不大的读取工作占据了大部分时间。

事实上，在很多场景，客户关心的只是其中一小部分数据，比如在80万条记录的情况下（每页10条），客户不太可能逐页翻页到最后一页——客户往往会输入一部分查询条件（比如关键词），而后我们可以基于查询条件把整个结果集约束到比较小的规模，然后分页的OFFSET值就不太可能变得很大，于是这种场景就不会有性能问题。

但是在全盘场景或者抽盘翻页到7万多页时，offset会达到70万，这时候浪费的时间就很客观了。

Keyset 分页

搞清楚了问题根源，下面的问题是：如何优化？一种优化思路是采用 Keyset分页，可以参见StackOverflow上这个问题和回答。

简单的说，我们不再使用传统的页码来分页，也不给客户的选择排序的自由，而是假定会按某个索引列进行排序（比如id列），然后在客户端使用一个last_id来记录最后一次从服务端返回的行id。从某种程度上，这类似于“游标”的功能。服务端查询时直接使用id条件取代Offset跳过天量的数据行。

比如这个 OFFSET 790000 查询：

SELECT t."Id", t."Amount", t."Batch", t."CreatedAt", t."ExpiredAt", t."ExternalCode", t."IsLocked", t."Locker", t."MaterialCode", t."MaterialId", t."Note", t."ProducedAt", t.xmin, t."Scope", t."Slot", t."StockKind", t."WarehouseId", m0."Id", m0."Code", m0."MeasureUnitId", m0."ShortName", m0."Specification", m0."Status", m1."Id", m1."Abbrev", m1."Description", m1."Name", m2."Id", m2."Code", m2."Name", m2."PlantId"
      FROM (
          SELECT m."Id", m."Amount", m."Batch", m."CreatedAt", m."ExpiredAt", m."ExternalCode", m."IsLocked", m."Locker", m."MaterialCode", m."MaterialId", m."Note", m."ProducedAt", m.xmin, m."Scope", m."Slot", m."StockKind", m."WarehouseId"
          FROM mm_inventory AS m
          WHERE m."StockKind" IN (1, 2, 3) 
          ORDER BY m."Id"
          LIMIT 10 OFFSET 790000
      ) AS t
      INNER JOIN mm_material AS m0 ON t."MaterialId" = m0."Id"
      INNER JOIN mm_material_measureunit AS m1 ON m0."MeasureUnitId" = m1."Id"
      INNER JOIN mm_warehouse AS m2 ON t."WarehouseId" = m2."Id"
      ORDER BY t."Id"

返回的数据行类似于：

| Id                                   |         Amount |  Batch         | ...
|"fcc3bce9-8049-469c-b622-8837dc4332dc"|    396481    | "batch20241120"
|"fcc3be1b-8e6f-40b4-978b-e618c74dbfac"|    207562    | "batch20241120"
|"fcc3ce2e-973e-4a9d-a241-63435c7011e8"|    325624    | "batch20241120"
|"fcc3ceb8-f8db-480a-a48d-8a0f50c67f92"|    783912    | "batch20241120"
|"fcc3cf02-b230-461b-9915-ebfc56d769a4"|    502474    | "batch20241120"
|"fcc3cf59-07c0-4940-91b0-73e8540a73e8"|    38443    | "batch20241120"
|"fcc3ee0e-9d92-4bfc-9cd9-3d5b96ed1cc6"|    576889    | "batch20241120"
|"fcc41cd6-a3d1-4197-b017-6322d427531a"|    547153    | "batch20241120"
|"fcc42949-a248-4c00-872b-265e9d803ccb"|    206696    | "batch20241120"
|"fcc445f5-eb06-45ec-ab8b-a1ee441a9526"|    222163    | "batch20241120"

我们可以在客户端记录"fcc445f5-eb06-45ec-ab8b-a1ee441a9526"作为last_id，下一次查询时只要where Id > last_id就可以跳过天量读取操作。比如：

SELECT t."Id", t."Amount", t."Batch", t."CreatedAt", t."ExpiredAt", t."ExternalCode", t."IsLocked", t."Locker", t."MaterialCode", t."MaterialId", t."Note", t."ProducedAt", t.xmin, t."Scope", t."Slot", t."StockKind", t."WarehouseId", m0."Id", m0."Code", m0."MeasureUnitId", m0."ShortName", m0."Specification", m0."Status", m1."Id", m1."Abbrev", m1."Description", m1."Name", m2."Id", m2."Code", m2."Name", m2."PlantId"
      FROM (
          SELECT m."Id", m."Amount", m."Batch", m."CreatedAt", m."ExpiredAt", m."ExternalCode", m."IsLocked", m."Locker", m."MaterialCode", m."MaterialId", m."Note", m."ProducedAt", m.xmin, m."Scope", m."Slot", m."StockKind", m."WarehouseId"
          FROM mm_inventory AS m
          WHERE m."StockKind" IN (1, 2, 3) 
          and m."Id" > 'fcc445f5-eb06-45ec-ab8b-a1ee441a9526'
          ORDER BY m."Id"
          LIMIT 10
      ) AS t
      INNER JOIN mm_material AS m0 ON t."MaterialId" = m0."Id"
      INNER JOIN mm_material_measureunit AS m1 ON m0."MeasureUnitId" = m1."Id"
      INNER JOIN mm_warehouse AS m2 ON t."WarehouseId" = m2."Id"
      ORDER BY t."Id"

会返回：

"fcc48df1-dc8a-405b-b51d-c8f740a5e849"    484781    "batch20241120"
"fcc48f9f-d9aa-4894-9713-ccd46b8a88cf"    752182    "batch20241120"
"fcc4ad86-ae85-4bb5-b270-5c8fae9b0392"    639850    "batch20241120"
"fcc4d64a-71d9-4fa4-981b-cddeae1aaab2"    179474    "batch20241120"
"fcc4da22-4d0a-4c27-9368-05bf5d1654d9"    421730    "batch20241120"
"fcc4f42a-cb5d-490c-abd0-e5bc1387319e"    213320    "batch20241120"
"fcc4fbca-fd03-4be7-abad-747bd4902217"    740926    "batch20241120"
"fcc529ea-0d80-4102-89c6-57043072c10b"    731827    "batch20241120"
"fcc55cbe-d822-432d-be36-ebcf7f9b125d"    713246    "batch20241120"
"fcc5a071-4a12-4377-8eb1-df5ed5a713eb"    542780    "batch20241120"

这和下面这种使用页码查询的效果是一样的：

SELECT t."Id", t."Amount", t."Batch", t."CreatedAt", t."ExpiredAt", t."ExternalCode", t."IsLocked", t."Locker", t."MaterialCode", t."MaterialId", t."Note", t."ProducedAt", t.xmin, t."Scope", t."Slot", t."StockKind", t."WarehouseId", m0."Id", m0."Code", m0."MeasureUnitId", m0."ShortName", m0."Specification", m0."Status", m1."Id", m1."Abbrev", m1."Description", m1."Name", m2."Id", m2."Code", m2."Name", m2."PlantId"
      FROM (
          SELECT m."Id", m."Amount", m."Batch", m."CreatedAt", m."ExpiredAt", m."ExternalCode", m."IsLocked", m."Locker", m."MaterialCode", m."MaterialId", m."Note", m."ProducedAt", m.xmin, m."Scope", m."Slot", m."StockKind", m."WarehouseId"
          FROM mm_inventory AS m
          WHERE m."StockKind" IN (1, 2, 3) 
          ORDER BY m."Id"
          LIMIT 10 OFFSET 790010
      ) AS t
      INNER JOIN mm_material AS m0 ON t."MaterialId" = m0."Id"
      INNER JOIN mm_material_measureunit AS m1 ON m0."MeasureUnitId" = m1."Id"
      INNER JOIN mm_warehouse AS m2 ON t."WarehouseId" = m2."Id"
      ORDER BY t."Id"

但是keyset分页使用主键跳过了索引，其查询时间极快，只有0.176 ms：

"Sort  (cost=114.17..114.20 rows=10 width=481) (actual time=0.111..0.113 rows=10 loops=1)"
"  Sort Key: m.""Id"""
"  Sort Method: quicksort  Memory: 28kB"
"  ->  Nested Loop  (cost=29.44..114.01 rows=10 width=481) (actual time=0.055..0.103 rows=10 loops=1)"
"        ->  Nested Loop  (cost=29.30..113.48 rows=10 width=153) (actual time=0.050..0.089 rows=10 loops=1)"
"              ->  Merge Join  (cost=28.87..29.06 rows=10 width=114) (actual time=0.043..0.050 rows=10 loops=1)"
"                    Merge Cond: (m2.""Id"" = m.""WarehouseId"")"
"                    ->  Index Scan using ""PK_mm_warehouse"" on mm_warehouse m2  (cost=0.29..321.81 rows=9902 width=15) (actual time=0.007..0.008 rows=3 loops=1)"
"                    ->  Sort  (cost=28.56..28.58 rows=10 width=99) (actual time=0.032..0.033 rows=10 loops=1)"
"                          Sort Key: m.""WarehouseId"""
"                          Sort Method: quicksort  Memory: 26kB"
"                          ->  Limit  (cost=0.42..28.29 rows=10 width=99) (actual time=0.011..0.024 rows=10 loops=1)"
"                                ->  Index Scan using ""PK_mm_inventory"" on mm_inventory m  (cost=0.42..33437.11 rows=12000 width=99) (actual time=0.011..0.023 rows=10 loops=1)"
"                                      Index Cond: (""Id"" > 'fcc445f5-eb06-45ec-ab8b-a1ee441a9526'::uuid)"
"                                      Filter: (""StockKind"" = ANY ('{1,2,3}'::integer[]))"
"              ->  Index Scan using ""PK_mm_material"" on mm_material m0  (cost=0.42..8.44 rows=1 width=39) (actual time=0.003..0.003 rows=1 loops=10)"
"                    Index Cond: (""Id"" = m.""MaterialId"")"
"        ->  Memoize  (cost=0.15..0.16 rows=1 width=328) (actual time=0.001..0.001 rows=1 loops=10)"
"              Cache Key: m0.""MeasureUnitId"""
"              Cache Mode: logical"
"              Hits: 9  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB"
"              ->  Index Scan using ""PK_mm_material_measureunit"" on mm_material_measureunit m1  (cost=0.14..0.15 rows=1 width=328) (actual time=0.002..0.003 rows=1 loops=1)"
"                    Index Cond: (""Id"" = m0.""MeasureUnitId"")"
"Planning Time: 0.476 ms"
"Execution Time: 0.176 ms"

不足和解决思路

那么代价是什么呢？我们放弃了可以指定页码来分页的功能，只能逐页获取——这类似于如今各大网站的信息流的方式，通过下拉滚动的方式加载下一页。

不过如果真有这种需求，一种方案是可以通过前端缓存的方式，在分页组件初始化的时候，构建一个字典，记录页码和相应的last_id。如此一来，只需要在前端分页时向后端传递相应的last_id即可。

数据库大表中基于Keyset分页对大偏移量分页的优化

场景和问题

Keyset 分页

不足和解决思路

添加新评论

最新文章

最近回复

分类

归档

其它