SQL 搜尋方法或鍵集分頁 - Vlad Mihalcea

banq發表於2021-11-14
SQL

在本文中，我們將瞭解什麼是 SQL Seek搜尋方法或 Keyset Pagination鍵集分頁，以及為什麼在瀏覽大型結果集時應該考慮它。
分頁的目標是避免獲取大量資料。
 

偏移OFFSET 分頁
在討論鍵集分頁之前，讓我們看看預設的 偏移OFFSET 分頁在 SQL 中是如何工作的。
儘管關聯式資料庫系統長期以來一直提供限制查詢結果集的特定方法，但自 SQL:2008 以來，就有了標準的分頁語法。
因此，限制給定結果集的記錄數的TOP-N 查詢可以使用該FETCH FIRST N ROWS ONLY指令，如以下示例所示：

SELECT id
FROM post
ORDER BY created_on DESC
FETCH FIRST 50 ROWS ONLY


並且，跳過前 M 條記錄並獲取接下來的 N 條記錄的 NEXT-N 查詢如下所示：

SELECT id
FROM post
ORDER BY created_on DESC
OFFSET 150 ROWS
FETCH NEXT 50 ROWS ONLY


 

偏移分頁索引
由於分頁需要一個ORDER BY子句以保證一致的排序順序，因此索引排序標準是很常見的。
在我們的例子中，我們需要在created_on列上建立以下索引：

CREATE INDEX idx_post_created_on ON post (created_on DESC)


在執行 TOP-N 查詢時，我們可以看到idx_post_created_on正在使用的，並且只掃描了 50 條記錄：

SELECT id
FROM post
ORDER BY created_on DESC
FETCH FIRST 50 ROWS ONLY
 
Limit  (cost=0.28..2.51 rows=50 width=16)
       (actual time=0.013..0.022 rows=50 loops=1)
  ->  Index Scan using idx_post_created_on on post p 
         (cost=0.28..223.28 rows=5000 width=16)
         (actual time=0.013..0.019 rows=50 loops=1)
          
Planning time: 0.113 ms
Execution time: 0.055 ms


對於第二頁，我們可以看到idx_post_created_on必須掃描 100 條記錄，因為它需要跳過第一頁中包含的前 50 行，以便載入此查詢需要返回的接下來的 50 條記錄：

SELECT id
FROM post
ORDER BY created_on DESC
OFFSET 50 ROWS
FETCH NEXT 50 ROWS ONLY
 
Limit  (cost=2.51..4.74 rows=50 width=16)
       (actual time=0.032..0.044 rows=50 loops=1)
  ->  Index Scan using idx_post_created_on on post p 
         (cost=0.28..223.28 rows=5000 width=16)
         (actual time=0.022..0.040 rows=100 loops=1)
          
Planning time: 0.198 ms
Execution time: 0.071 ms


我們離第一頁越遠，為了跳過OFFSET子句指示的記錄，idx_post_created_on索引需要掃描的記錄就越多：

SELECT id
FROM post
ORDER BY created_on DESC
OFFSET 4950 ROWS
FETCH NEXT 50 ROWS ONLY
 
Limit  (cost=221.05..223.28 rows=50 width=16)
       (actual time=1.154..1.166 rows=50 loops=1)
  ->  Index Scan using idx_post_created_on on post p 
         (cost=0.28..223.28 rows=5000 width=16)
         (actual time=0.079..1.033 rows=5000 loops=1)
          
Planning time: 1.629 ms
Execution time: 1.190 ms

請注意，掃描整個idx_post_created_on索引比掃描單個頁面多 20 倍，這是初始 TOP-N 查詢的情況。
 

SQL Seek 方法或鍵集分頁
為了解決 OFFSET 分頁中固有的索引掃描問題，我們可以使用 Seek 方法或鍵集分頁 Keyset Pagination 技術。
TOP-N Keyset Pagination 查詢如下所示：

SELECT id, created_on
FROM post
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY

請注意，我們需要在 ORDER BY 子句中包含id ，因為created_on列值不是唯一的。因此，載入下一頁時我們需要傳遞最後處理的created_on和id。因此，這裡查詢投影也需要載入created_on列。
 Next-N 查詢將使用以前處理created_on和id列值來定位需要載入的記錄下一個頁面。

SELECT id, created_on
FROM post
WHERE
  (created_on, id) < ('2019-10-02 21:00:00.0', 4951)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY


(created_on, id) < ('2019-10-02 21:00:00.0', 4951) 行值表示式是等效於：

created_on < '2019-10-02 21:00:00.0' OR
(
    (created_on = '2019-10-02 21:00:00.0') AND
    (id < 4951)
)

 

SQL Seek 方法或鍵集分頁索引
因為 Seek 方法在子句中同時使用 thecreated_on和 the idcolumns ORDER BY，我們可以在這兩列上建立索引idx_post_created_on：

CREATE INDEX idx_post_created_on ON post (created_on DESC, id DESC)


現在，當執行 TOP-N Keyset Pagination 查詢時，我們可以看到它使用了idx_post_created_on索引，並且只掃描了 50 條記錄：

SELECT id, created_on
FROM post
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY
 
Limit  (cost=0.28..1.91 rows=50 width=16)
       (actual time=0.104..0.110 rows=50 loops=1)
  ->  Index Only Scan using idx_post_created_on_id on post 
        (cost=0.28..163.28 rows=5000 width=16)
        (actual time=0.102..0.107 rows=50 loops=1)
        Heap Fetches: 0
         
Planning Time: 0.882 ms
Execution Time: 0.129 ms

Next-N Keyset Pagination 查詢也使用idx_post_created_on索引，並且與 OFFSET分頁Pagination 不同，這次只掃描了 50 行：

SELECT id, created_on
FROM post
WHERE
  (created_on, id) < ('2019-10-02 21:00:00.0', 4951)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY
 
Limit  (cost=0.28..3.40 rows=50 width=32)
       (actual time=0.029..0.063 rows=50 loops=1)
  ->  Index Scan using idx_post_created_on_id on post 
        (cost=0.28..308.58 rows=4950 width=32)
        (actual time=0.027..0.057 rows=50 loops=1)
        Index Cond: (
          created_on <=
          '2020-04-24 06:00:00'::timestamp without time zone
        )
        Filter: (
          ROW(created_on, (id)::numeric) <
          ROW('2020-04-24 06:00:00'::timestamp without time zone, '4951'::numeric)
        )
        Rows Removed by Filter: 2
        Heap Fetches: 52
 
Planning Time: 0.806 ms
Execution Time: 0.158 ms

而且，載入最後一頁也會很快，因為 Keyset分頁Pagination 不需要掃描整個索引來跳過 OFFSET 記錄：

SELECT id, created_on
FROM post
WHERE
  (created_on, id) < ('2019-10-03 02:00:00.0', 51)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY
 
Limit  (cost=48.82..48.83 rows=1 width=16)
       (actual time=0.168..0.175 rows=50 loops=1)
  ->  Sort  (cost=48.82..48.83 rows=1 width=16)
            (actual time=0.166..0.170 rows=50 loops=1)
        Sort Key: created_on DESC, id DESC
        Sort Method: quicksort  Memory: 27kB
        ->  Bitmap Heap Scan on post 
              (cost=4.76..48.81 rows=1 width=16)
              (actual time=0.071..0.085 rows=50 loops=1)
              Recheck Cond: (created_on <= '2019-10-03 02:00:00'::timestamp without time zone)
              Filter: (
                (created_on < '2019-10-03 02:00:00'::timestamp without time zone) OR
                (
                  (created_on = '2019-10-03 02:00:00'::timestamp without time zone) AND
                  (id < '51'::bigint)
                )
              )
              Rows Removed by Filter: 2
              Heap Blocks: exact=1
              ->  Bitmap Index Scan on idx_post_created_on_id 
                  (cost=0.00..4.75 rows=63 width=0)
                  (actual time=0.061..0.062 rows=52 loops=1)
                    Index Cond: (created_on <= '2019-10-03 02:00:00'::timestamp without time zone)
                     
Planning Time: 0.676 ms
Execution Time: 0.279 ms


 

相關文章