SQL.12 分頁的排序鍵與接續標記：同分的列、全序與 OFFSET 的代價

2026-09-02

分頁把一批列切成好幾次取回，而每一頁是一次獨立的查詢——這是無狀態介面帶進來的前提，不是 SQL 的性質。資料庫伺服器端開一個查詢結果的讀取物件（cursor）、一次執行逐批取回的時候，下面這一整篇的問題不會發生。把翻頁包在同一個交易裡則不夠：它擋得住的只有插入造成的位移，而且要 REPEATABLE READ 以上的隔離等級，同分的列在同一個交易裡逐頁取，先後照樣會變（原因在〈排序鍵不唯一時同分的列的先後〉）。

在每一頁各查一次的前提底下，兩次查詢之間伺服器不持有任何狀態，能拿來對齊兩頁的只有排序本身。

而 ORDER BY 只規定排序鍵分得出高下的列之間的先後；排序鍵的值相同的列（下面叫同分的列）彼此誰在前面，它沒有規定。那個規定怎麼運作、空值為什麼比不出大小，在 SQL.11 查詢結果的列序：沒有 ORDER BY 時的來源與 ORDER BY 的規定範圍。整批取回的時候，同分的列先後不定無害；逐頁取回的時候，它變成同一筆資料出現兩次、另一筆一次都不出現。

本篇處理三件事：那個機制、把順序補到全序（任兩列都比得出先後）的判斷標準，以及位置式與值式兩種接續標記（cursor）各自成立什麼。對外要選哪一種分頁還牽涉到跳頁與總筆數要不要放棄、接續標記怎麼編碼、以及一致性承諾怎麼寫進契約，那些不在本篇裡：分頁之爭：offset 與 keyset 的定位機制、接續標記的表示法與不透明性條款的〈選型的順序：跳頁與總數需求、定位機制、表示法〉一節給選型的三步（先問跳頁與總數、再選定位機制、最後定表示法）與各步的出口，〈不透明接續標記隱含的承諾與條款清單〉一節寫接續標記要對外承諾哪些條款，〈offset 與 keyset 定位機制的成本曲線與能力差〉一節另列出快照分頁與時間視窗這兩條本篇沒有的路；接續標記式的複雜度對照與 tiebreaker 設計在 Keyset Pagination。

本篇沿用 SQL.11 查詢結果的列序：沒有 ORDER BY 時的來源與 ORDER BY 的規定範圍那一批訂單：在共用資料庫上把 101 的金額改成 700、再加 103 到 105 三張，讓五張訂單裡有三張同樣是 500 元。這三張同分的訂單正是分頁要處理的對象。

1UPDATE 訂單 SET 金額 = 700 WHERE 訂單編號 = 101;
2INSERT INTO 訂單 (訂單編號, 金額) VALUES (103, 500), (104, 300), (105, 500);
3-- 101 是 700 元；102、103、105 是 500 元；104 是 300 元
4-- 新加的三張沒有填顧客與下單日，本篇的查詢只用到訂單編號與金額

排序鍵不唯一時同分的列的先後

ORDER BY 金額 DESC 說的是金額大的在前。三張 500 元的訂單彼此之間沒有被規定，所以它們的先後與沒有 ORDER BY 時一樣，跟著計畫走：

1-- SQLite 3.51，逐頁取一列：第 n 頁是 OFFSET n-1
2SELECT 訂單編號 FROM 訂單 ORDER BY 金額 DESC LIMIT 1 OFFSET 0;
3-- OFFSET 1 到 OFFSET 4 各跑一次同一段查詢
4-- 沒有索引：五頁依序是 101  102  103  105  104
5
6CREATE INDEX ix ON 訂單 (金額);
7-- 建了索引之後，同樣五段查詢依序是 101  105  103  102  104

整批看的時候這個差別無害——五張訂單一張不多一張不少，只是中間三張換了位置。分頁把它變成錯誤，因為每一頁是一次獨立的查詢：兩次查詢之間換了計畫的話，前一頁的最後一列可能在下一頁再出現一次，而另一列一次都不出現。計畫不換也會發生：PostgreSQL 依 LIMIT 加 OFFSET 的大小選排序方法，第一頁走 top-N heapsort、後面的頁走 quicksort，兩種方法對同分列給出不同的先後——一次 PostgreSQL 18 上的實測，一千列、同分很多，在同一個交易裡逐頁取完只拿到 757 個不同的列。

另一種形態不必換計畫就會發生：一邊翻頁一邊有人寫入，排在前面的列多了或少了，後面每一列的位置就跟著移動。下面叫它翻頁途中的寫入造成的位移。

1-- ORDER BY 金額 DESC LIMIT 2
2第 1 頁（OFFSET 0）              101, 102
3   ← 這時插入 106，金額 900
4第 2 頁（OFFSET 2）              102, 103
5第 3 頁（OFFSET 4）              105, 104

102 出現在兩頁上。OFFSET 2 的意思是「跳過排好之後的前兩列」，而新插入的 106 排在最前面，把所有列往後推了一格。原本在第二個位置的 102 被推到第三個，於是它同時是第一頁的最後一列與第二頁的第一列。

翻完三頁之後讀者手上是 101、102、102、103、105、104——六列裡有一列重複，而金額最高的那一筆 106 一次都沒出現。兩種偏差都不帶任何訊號：每一頁單獨看都是正確的查詢結果。查詢照跑、結果形狀正確而答案錯了，這一類錯誤共同的性質在 SQL.13 查詢的合法性與答案的正確性：引擎檢查的範圍、答案錯掉的成因與查證方法的〈答案錯掉的成因分類與引擎不報錯的原因〉。

把排序鍵補到全序，以及用排序鍵的值當接續標記

補一個值不重複的欄位當末位排序鍵，同分的那幾列就有了規定：

1-- ORDER BY 金額 DESC, 訂單編號   全表順序：101,102,103,105,104
2OFFSET 0 → 101,102     OFFSET 2 → 103,105     OFFSET 4 → 104

同一段查詢跑幾次都是這個結果，換計畫也是。判斷標準是排序鍵的組合在「排序用的那條比較規則下」兩兩可分，這個性質叫全序：任兩列都比得出先後——「值不重複」還不夠，有兩種形態會讓不重複的鍵仍然排不出先後。一種是空值：一欄掛著 UNIQUE 而三列都是 NULL，約束成立而 NULL 之間比不出高下，SQLite 3.51 上建了索引之後同一段查詢回的順序就翻轉。另一種是比較規則把不同的值判成相等：MySQL 預設的 collation 底下 Anna／anna／ANNA／Ánna 四個值 COUNT(DISTINCT BINARY 姓名) 是 4 而 COUNT(DISTINCT 姓名) 是 1。看起來唯一的字串鍵因此可能不唯一，那條比較規則住在哪一層、各家預設是什麼，在 SQL.15 字串比較與 collation：相等、排序、LIKE 與索引可用性。

把主鍵接在排序鍵最後面之所以省事，是因為它同時避開了空值與比較規則這兩種形態——主鍵約束保證它不重複也不是 NULL，而數值型別的比較規則不會把兩個不同的數判成相等。SQLite 在這裡是例外：只有宣告成 INTEGER PRIMARY KEY 的那一欄擋得住 NULL，寫成 INT PRIMARY KEY 的主鍵照樣收得下好幾個 NULL，要當決勝鍵就得另外宣告 NOT NULL。所以排序鍵分不分得出高下，多半在寫 CREATE TABLE 的那一刻就定了：那一欄有沒有唯一約束、決勝鍵有沒有現成的欄位可用。backend 1.16 Schema 設計決定的查詢代價的「這個鍵唯不唯一」一節走那個決定，並示範補決勝鍵治得好同分的列造成的重複與遺漏、治不好翻頁途中的寫入造成的位移。

補上唯一鍵治好了同分的列先後不定，治不好翻頁途中的寫入造成的位移。OFFSET 數的是位置，而位置會被前面新增或刪除的列改變。要讓分頁不受排在接續標記前面的寫入影響，接續標記得從位置換成值——記下上一頁最後一列的排序鍵，下一頁從那個值之後接著取。（排在接續標記後面的新插入照樣會進到後面的頁上；要讓整趟翻頁看到同一個時點的資料是另一回事，那需要快照，分頁之爭的「定位機制的成本曲線與能力差」一節寫它。）

1-- 上一頁最後一列是 (金額 500, 訂單編號 102)
2SELECT 訂單編號 FROM 訂單
3WHERE 金額 < 500 OR (金額 = 500 AND 訂單編號 > 102)
4ORDER BY 金額 DESC, 訂單編號 LIMIT 2;
5-- 103,105

這個條件描述的是「排在那一列後面」，而它由排序鍵自己構成，所以中間插進來的 900 元訂單不影響它——那一列排在接續標記前面，本來就不該出現在後面的頁上。

條件要跟著排序方向寫。 SQL 有一種列值比較的簡寫 WHERE (金額, 訂單編號) < (500, 102)，它只在所有排序鍵同方向時與上面那段等價。金額遞減而訂單編號遞增的時候，同一批資料上兩種寫法回不同的列：

1展開式  WHERE 金額 < 500 OR (金額 = 500 AND 訂單編號 > 102)   → 103,105
2列值式  WHERE (金額, 訂單編號) < (500, 102)                    → 104

列值式回的是另一頁，而它照樣執行、照樣回一批形狀正確的列。兩個排序鍵都寫成遞減的時候列值式才對得上：

1-- 兩個排序鍵都遞減，全表順序是 101,105,103,102,104
2-- 上一頁最後一列是 (金額 500, 訂單編號 105)
3SELECT 訂單編號 FROM 訂單
4WHERE (金額, 訂單編號) < (500, 105)
5ORDER BY 金額 DESC, 訂單編號 DESC LIMIT 2;
6-- 103,102，與同一個排序下 OFFSET 2 LIMIT 2 取到的那一頁相同

OFFSET 的代價與頁數的關係

OFFSET n 要引擎按順序產出前 n 列再把它們丟掉，所以翻得越後面做的功越多。貴的是「用序數指位置」這個介面形狀，不是「找到第九十萬零一列」這件事——下面那張表的最後一列就是同一個深度用值定位，耗時與第一頁相同。一百萬列的表、排序鍵上有覆蓋索引，每次取二十列：

1CREATE TABLE t (id INTEGER PRIMARY KEY, k INTEGER);
2CREATE INDEX ix_k ON t (k, id);   -- 排序鍵 k, id 整組都在索引裡，取 id 不必回表
3
4-- OFFSET 式：先走過並丟掉排在前面的 n 列
5SELECT id FROM t ORDER BY k, id LIMIT 20 OFFSET 900000;
6
7-- 接續標記式：括號裡填上一頁最後一列的 (k, id)；兩個排序鍵同方向，列值比較可以直接用
8SELECT id FROM t WHERE (k, id) > (:上一頁的k, :上一頁的id) ORDER BY k, id LIMIT 20;

1-- SQLite 3.51，五次的實測，單位秒
2OFFSET 0        0.001  0.000  0.001  0.000  0.001
3OFFSET 100000   0.002  0.002  0.002  0.003  0.001
4OFFSET 500000   0.007  0.005  0.005  0.005  0.005
5OFFSET 900000   0.009  0.010  0.012  0.009  0.008
6接續標記式（深度 900000） 0.000  0.000  0.000  0.000  0.001

絕對值在這個規模上都很小，要看的是形狀：OFFSET 那一欄隨深度線性成長，接續標記式那一列與深度無關——它直接在索引上找到起點。所以「第一頁很快，翻到後面越來越慢」這個症狀有一個不必量測就成立的解釋：OFFSET 要先走過並丟掉排在前面的每一列，頁數越深要丟的越多。而它與索引建得好不好無關——上面那張表就是在索引已經覆蓋排序鍵的前提下量的。

OFFSET 的成長是代價問題的一個實例，而代價的一般形狀由資料與索引決定、不由寫法決定。代價的一般形狀在 SQL.17 查詢的代價，而 OFFSET 這一條特別之處在於它的成長與索引建得好不好無關。

#sql #pagination #limit #offset #keyset #order-by