使用Seek method做分頁時offset predicate的注意事項

Seek method pagination是最近流行的分頁概念。其核心思想是：不再依賴index作為偏移量，而是使用條件表達式作為分頁的依據。具體原理我在這里就不再廢話了，感興趣的朋友可以去搜一搜。

由于“條件表達式”成為了分頁依據，那么如何準備分頁條件表達式（seek predicate）就成為了關鍵。

首先，使用seek method pagination的前提條件是：必須是有序結果集。對應于SQL script即需要有order by子句。

下面就是組織seek predicate時需要注意的問題了：

1、order by的列（或多個列）中必須有一個包含全表唯一值的列（就是類似主鍵那樣的列，列中的值必須not null而且unique）。主鍵列就符合這一特征。

2、order by中出現(xiàn)的列，也必須出現(xiàn)在where子句中，而且排列順序不能錯。比如，order by A asc, B asc, C asc，那么where ... and (A, B, C) > (x, y, z)。where中可以出現(xiàn)其他與seek predicate無關的其他predicate，但要保證order by中出現(xiàn)的column都要出現(xiàn)在where子句中；

3、order by的列必須是同向的，不可以order by A asc, B desc, C asc；

4、(A, B, C, ...) > (x, y, z, ...) 的邏輯運算符必須與order by中的方向對應起來。規(guī)則：

如果你的結果集順序是ASC，那么，如果你想翻到下一頁，你的seek predicate應該用>作為邏輯操作符；如果你想翻到上一頁，seek predicate的操作符應該是<。

如果你的結果集順序是DESC，那么，如果你想翻到下一頁，seek predicate的操作符應該是<，反之，用>號。

Note1:

這里需要注意一件事情，就是null值問題。之前說過，order by中至少要有一列為not null unique，而order by中的其他列則沒有這個要求，列值可以為null，可以出現(xiàn)重復值。如果翻頁時，游標正好指在當前頁的最后一行，且這一行中的order by的某個列包含null值，那么就不能用>或<號來拼where條件子句了，要用is null 和 is not null.

現(xiàn)在where中的A、B、C、...都有了，那么每次翻頁時，如何找到對應的x,y,z呢？

首先，第一屏。第一屏需要一個batch size（假設為30），也就是每屏加載多少數據。由于第一屏你不知道具體的x,y,z值，所以直接從“頭”讀取30條記錄。獲得這30條記錄后，緊接著，把第30條記錄的A、B、C的值分別記下來，作為x, y, z(假設第一批30條記錄的最后一條記錄的A='Yan' B='Male' C=1200。下一批30條數據的where子句中的seek predicate就應該為：

select ...
from ...
where ... and (A, B, C) > ('Yan', 'Male', 1200)
order by A, B, C
limit 30;

執(zhí)行上面的SQL會得到下面一頁30條記錄（如果有30條的話），然后重復剛才采集條件值的操作，例如，第二屏30條記錄的最后一條記錄的A='Ye' B='Male' C=335。將獲得的新的x、y、z值重新拼成第三屏的SQL：

select ...
from ...
where ... and (A, B, C) > ('Ye', 'Male', 335)
order by A, B, C
limit 30;

執(zhí)行上面的SQL會得到下面一頁30條記錄（如果有30條的話）。如此往復，一直到數據全部加載完畢。

(A, B, C) > ('Ye', 'Male', 335)

這個東西是seek predicate，可以理解為condition offset。傳統(tǒng)分頁方式用的是index offset。傳統(tǒng)分頁方式下，你需要知道一共有多少條記錄，每屏顯示多少條（batch size），然后查詢出整個結果集（DBMS的緩存里）， 然后使用index offset偏移量skim offset行記錄后，開始讀取batch條記錄。全集查詢和skim的過程是很要命的。隨著你翻頁越往后，比如1000頁以后，skim速度就會越慢，內存開銷也會越大，數據庫負擔越重。

但是seek method分頁則不同，數據庫并不需要查詢出所有符合條件的數據，他只需要按照你的condition定位到符合condition的第一條記錄，然后往后讀取batch size條記錄，就完成了那個頁面的讀取。

目前很多數據庫都支持seek predicate的這種(A, B, C) op (x, y, z)寫法，而JPA還不支持。但是，我們可以根據所學的數學知識，將不等式變換一下方式，換成等效公式。

• (A, B) > (x, y) = A>x OR (A=x AND B>y)

• (A, B, C) > (x, y, z) = A>x OR (A=x AND (B>y OR (B=y AND C>z)))

以此類推，你可以寫個遞歸來自動分解拼裝(A, B, C, ...) > (x, y, z, ...)

Tips:

如果遇到order by的列值存在null值，那么，A>null OR (A=null AND B>y)。這種寫法在postgresql v9.4中是支持的。如果你的數據庫不支持，可以將>null寫成A is not null OR (A is null AND B>y)