《你不知道的 JAVA》💘 送給 Offset & Limit 的告別氣球詳情 - java,springboot,springsecurity,docker,gradle Chuck1sn 動態日志

工程思維落地

《你不知道的 Java 》系列博客的工程理念與設計模式，已落地成一款全新設計的 Java 腳手架，可與博客配套使用。

前情提要

https://segmentfault.com/a/1190000046021595
前文我們已經領略了 JOOQ 在分頁查詢和 Simple CRUD 時的風采。今來學習一個更加打破常規的概念：你可能並不需要 Offset & Limit 來分頁。

Limit & Offset

首先虛構一張玩家分數表：

| first_name | last_name | score | game_id |
|------------|-----------|-------|---------|
| Mary       | Paige     | 1098  | 42      |
| Tracey     | Howard    | 1087  | 42      |
| Jasmine    | Butler    | 1053  | 42      |
| Zoe        | Piper     | 1002  | 42      |
| Leonard    | Peters    | 983   | 42      |
| Jonathan   | Hart      | 978   | 42      |
| Adam       | Morrison  | 976   | 42      |
| Amanda     | Gibson    | 967   | 42      |
| Alison     | Wright    | 958   | 42      |
| Jack       | Harris    | 949   | 42      |

對這張表使用常規的 Limit & Offset 進行分頁：

SELECT first_name, last_name, score
FROM players
WHERE game_id = 42
ORDER BY score DESC
LIMIT 5;

這個查詢的執行速度很快，因為這裏隱含了 Offest 1 的語法。通常來説這樣的分頁沒有什麼問題。但是同樣的 SQL 使用不同的 Offset 效果就完全不同了，比如：

SELECT first_name, last_name, score
FROM players
WHERE game_id = 42
ORDER BY score DESC
LIMIT 5
OFFSET 100000;

這個查詢的速度就不那麼理想。因為對於 Limit & Offset 來説，大多數供應商的實現方案是全量查詢數據然後丟棄前10000條，這嚴重拖慢了數據的查詢速度。

常見的 Limit & Offset 優化

網上有很多關於 Limit & Offset 的優化方案，在這裏我再簡略闡述一下。

二級索引

凡事涉及到數據庫的查詢性能，馬上就能映入腦海的優化方式就是利用索引。針對上面的查詢，可以針對 game_id 與 score 建立二級索引。

CREATE INDEX idx_game_id_score ON players(game_id, score DESC);

利用索引我們避免了全表掃描，查詢速度果然快了不少。

覆蓋索引

使用二級索引的缺點是會導致索引回表掃描。此時有經驗的玩家可能會選擇把查詢字段first_name last_name score都包含在索引中，這樣就能直接從索引中獲取數據，而不需要回表掃描，進一步提高性能。

CREATE INDEX idx_game_id_score_covering ON players(game_id, score DESC, first_name, last_name);

索引無法解決的問題

利用索引進行優化的本質，實際是對「查詢操作」的優化。也就是説無論查詢語句是否分頁，都可以利用索引提高性能。但此處面臨的問題，「分頁查詢」性能，光優化「查詢操作」的性能還無法達到我們的預期。也就是説，利用索引優化的方案無法解決前10000條數據被 abandon 的問題。

🤔 看來要徹底優化分頁查詢的性能，得想辦法換一種思路另闢蹊徑。

重新思考

讓我們去 google 上找點靈感。當我們使用 google 搜索信息時，google 採取的分頁設計是你可以在 1-10 頁之間跳轉，並提供「上下一頁」的導航。

這樣的設計初看沒什麼大不了的，但是請注意這裏並沒有一個「跳轉到 n 頁」供你使用。谷歌為什麼這樣做？理由很簡單，因為用户並不需要跳轉到 998 頁去查找什麼信息，通常都是逐頁瀏覽。當需要特定條件的信息時，指定篩選條件比跳轉到 900 多頁去模糊查找更加有效率。

初看有點懵，彆着急，給自己點時間想想是不是這樣？

重新設計

既然重新確立了正確的需求方向，現在就需要重新設計實現方案。好消息是當不需要「跳轉到 n 頁」以後，Offset 關鍵字就用不上了。

現在來考慮一張記錄了玩家分數的表，並計劃 5 條記錄為一頁來作為後續分頁的對象。

| first_name | last_name | score | game_id |
|------------|-----------|-------|---------|
| Mary       | Paige     | 1098  | 42      | <=== page1 start
| Tracey     | Howard    | 1087  | 42      |
| Jasmine    | Butler    | 1053  | 42      |
| Zoe        | Piper     | 1002  | 42      |
| Leonard    | Peters    | 983   | 42      | <=== page1 end
| Jonathan   | Hart      | 978   | 42      | <=== page2 start
| Adam       | Morrison  | 976   | 42      |
| Amanda     | Gibson    | 967   | 42      |
| Alison     | Wright    | 958   | 42      |
| Jack       | Harris    | 949   | 42      | <=== page2 end

首先寫一個 SQL 來實現第一頁的查詢。

SELECT first_name, last_name, score
FROM players
WHERE game_id = 42
ORDER BY score DESC
LIMIT 5;

當查詢下一頁時，把上一頁的最後一條數據作為標誌位，查詢所有分數小於 983 的記錄。

SELECT first_name, last_name, score
FROM players
WHERE game_id = 42
AND score < 983
ORDER BY score DESC
LIMIT 5;

這真是令人驚訝🫢！使用如此簡單的手法，我們就獲取到了「下一頁」的數據。

| first_name | last_name | score | game_id |
|------------|-----------|-------|---------|
| Jonathan   | Hart      | 978   | 42      |
| Adam       | Morrison  | 976   | 42      |
| Amanda     | Gibson    | 967   | 42      |
| Alison     | Wright    | 958   | 42      |
| Jack       | Harris    | 949   | 42      |

通過反覆使用這樣的手法，我們就復刻了一個谷歌版的分頁，這簡直是太棒了！

不過別高興的太早，要我説的話這裏還有一個小小的缺陷，就是 AND score < 983 這樣的查詢條件相比 Offset n 來説閲讀起來不太直觀。

是 JOOQ 出手的時候了

Offset n 之所以易讀，是因為他是一個包含了自然語義的語法。而 AND score < 983 AND ORDER BY score DESC 是一種邏輯表達，包含理解成本。為了消除這種理解成本，我們可以設計一種新式的表達方式，它就叫 Seek Method 。

DSL.using(configuration)
   .select(PLAYERS.PLAYER_ID,
           PLAYERS.FIRST_NAME,
           PLAYERS.LAST_NAME,
           PLAYERS.SCORE)
   .from(PLAYERS)
   .where(PLAYERS.GAME_ID.eq(42))
   .orderBy(PLAYERS.SCORE.desc())
   .seek(983) // (!)
   .limit(5)
   .fetch();

使用 Seek 來改造後的 SQL 如上所示：我們通過 game_id=42 這個條件查詢 PLAYERS 這張表，並按照 SCORE 的順序向後「尋址」 5 條記錄。當你習慣了這樣的表達後，理解他的成本從需要大腦思考邏輯轉變為了依靠直覺——這就是生產力的提升。

由於 Seek Method 並不是一個 SQL 標準語法，JOOQ 為他提供了模擬支持，並抹平了不同供應商之間的差異。這就是JOOQ 的優勢：對於 SQL 與數據庫極其深入的理解與細緻入微的語法支持。