背景

從上周開始我就一直在做數據清洗的工作，這次算是體會到了什么叫做“拋開數據量談實現就是耍流氓”。

我設計方案和調試代碼連接的都是日常環境的數據庫，里面的單表數據量在百級，無論我怎么實現都是瞬間洗完。到了性能測試的時候用的就是性能庫，雙 11 之前@W君做性能測試的時候，往里面寫入了 2000W 的數據，足夠我戰個痛快。

深坑

一開始的時候，分頁查詢用的是 limit 子句，SQL 語句形態如下。

select * from table where xxx in (1,2,3) order by id limit #offset#, 200 

limit 子句的優點很明顯，簡單好用。缺點平時不顯著，數據量一大就暴露了。數據庫會完整掃描 offset 的行，然后繼續掃描 200 行之后才把結果集返回。 offset 在 400W 的時候，這樣的 SQL 做一次分頁查詢就已經至少耗時 5s 了。

掙扎

頓時感覺自己陷入了一個泥水坑，趕緊找老司機請教。@Y君給了我一個方案，不使用 limit，直接使用主鍵索引 + 左右范圍查找，SQL 語句形態如下。

select * from table where xxx in (1,2,3) and id >= #minId# and id < #maxId# 

其中 minId 和 maxId 由我的代碼給出，啟動時先算出當前表的 id 范圍，然后在此范圍內以 200 為步長分頁讀取，即 maxId - minId = 200，有可能讀不到數據，有可能讀到 200 條。

在數據庫里面試著跑了幾條這樣的查詢，果然效率高了很多。趕緊吭哧吭哧在代碼中實現這種分頁邏輯，信心滿滿想要性能飆升，結果。。。

在日常數據庫測試的時候就發現問題了。我的數據庫的 id 不是由 MySQL 自動生成的連續的自增主鍵，而是通過其他中間件產生的，從整體來看，整個 id 的分布比較離散，步長 200 的時候，一次查詢根本查不出幾條數據。如果整張表的 maxId 比 minId 大出很多很多，會產生很多次無意義的查詢。要想有比較好的命中率，還需要關心表里面 id 的分布，根據分布情況調整步長。

接下來我就在 limit 和 min-max-id 兩種分頁方案之間糾結，開始去分析線上表的數據分布，然后考慮把大表和小表區分對待，使用不同的分頁策略等等。

靈光

白天被分頁查詢的性能泥潭弄得心力憔悴，晚上回家的路上冷風吹著頭腦稍微清醒一些，想到一個條似乎可行的 SQL 語句。

select * from table where id >= #minId# and xxx in (1,2,3) limit 200 

回到家之后迫不及待開始寫代碼，先把這條 SQL 換著參數在數據庫里面一遍遍執行，感覺這種分頁方式完全符合我的要求，查詢使用的主鍵索引，雖然從執行計劃看影響行數在百萬級，但是實際執行的時候影響行數不過百級，還不需要考慮 id 的分布，每次都能實打實的給我撈出 200 條數據。

不過使用這樣的 SQL 語句做分頁，需要注意調整 minId 的值，掃表過程中需要做到不重不漏。一種可行的方案是將本次查出的結果集中的最大的 id，自增 1 后作為下次查詢的 minId。

max_id, min_id = select min(id), max(id) from table while min_id <= max_id:
    objs = select * from table where id >= min_id and xxx in (1,2,3) limit 200 max_id_in_page = max(map(lambda x: x.id, objs))
    min_id = max_id_in_page + 1 

整體來說，這種分頁方式避免了使用 limit 時候遍歷 offset 帶來的無謂的性能開銷，避免了對 id 使用左右范圍查詢時候 id 的離散分布對命中率的影響，代價是需要在內存中遍歷結果集獲取當前分頁中 id 的最大值，局限是只能在對全表唯一的字段做分頁時使用。

擴展

關于分頁查詢，不同的數據庫在做 offset 的時候語法也不太一樣，比如 M$ 家的語法就是 TOP n，MySQL 的語法就是 limit n。

網上的很多博客都提到一種利用子查詢來提高性能的做法，大體的意思是先使用普通的 offset 語法獲取目標分頁的記錄的 id 集合，再根據 id 集合去獲取完整的目標數據，SQL 語句形態如下。

select * from table where id in (select id from table order by id limit #offset#, #size#) 

其實有些版本尤其低版本的 MySQL 是不支持直接對帶 limit 的子查詢的結果做 in 子句的，執行的時候會報錯，信息如下。

This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

針對這個問題，他們就有了一個改進之后的子查詢版本。

select * from table where id >= (select id from table order by id limit #offset#, 1) 

其實是先取出目標分頁的第一條記錄的id， 然后根據 id 做范圍查找和條數限制。這條 SQL 語句的效率在 offset 達到百萬級時相比直接 limit 有數倍的提升，但是注意到 MySQL 子查詢其實是一個坑，這條語句不但沒有避免遍歷 offset，還做了大量的無用重復工作。

本質上，我最終使用的分頁方案是對這條 SQL 語句的優化，借助 id 的有序性和唯一性，使用max(map(lambda x: x.id, objs)) + 1替代了子查詢。

有人為了繞過 MySQL 不支持對子查詢結果做 in 子句的限制，腦洞大開寫出了如下查詢。

select * from table where id in (select id from (select id from table order by id limit #offset#, #size#) as tmp) 

既然不允許直接對帶 limit 的子查詢做 in，那么干脆用子查詢套子查詢，也是醉了。這條 SQL 語句的效率還不如直接 limit。

還有一種改進方案就是傳說中的“查兩次”。第一次先查出目標分頁的 id 集合，因為只查 id，大部分情況可以直接命中索引然后返回，速度還是可以接受的。然后第二次直接根據 id 集合做 in 子句查詢，走的主鍵索引，這次就是秒出了。

其實查兩次對性能的影響需要具體到情景來分析，不能當做是萬金油。

我在最終使用的分頁方案，在我的應用場景下，性能是超過上面其余幾種分頁方案的。

現實

在現實世界的開發中，分頁遠比預料的要復雜得多。

如果 Web 頁面通過記錄的創建時間來分頁，那么很可能我最終使用的分頁方案就不能套用了，因為時間不是一個全表唯一的值，很難在不使用 limit 的情況下做到不重不漏。如果運氣不錯，使用的是數據庫的自增主鍵，那么可以認為主鍵的變化趨勢和創建時間的變化趨勢是等價的，可以將對時間的分頁映射成對主鍵的分頁。

在分頁的交互邏輯上，天朝的產品經理似乎偏好于電梯式的分頁控件。其實還是“拋開數據量談實現就是耍流氓”，在數據量小的時候，使用什么樣的分頁方式對系統性能都沒什么影響，但是在數據量大到一定程度的時候，電梯式的分頁對系統性能的大量消耗反而會傷害所謂的用戶體驗。

關于大數據量下的分頁實現，業界其實已經有了好幾種策略，基于不同的假設。

假設隨著時間的推移，越早產生的數據，價值越小。如果假設成立，我們可以認為絕大多數用戶是沒有這樣一個需求要去查看他在系統中產生的第一條數據的。如果第一條數據對用戶真的很重要，即使再困難他也會想辦法得到。

基于這樣的假設，在數據量足夠大的情況下，我們就可以對系統實現和交互體驗做出很多優化，比如不再提供精確的電梯式分頁，取而代之的是下一頁、上一頁；不再提供精確的總頁數，而是提供一個大概的條目總數，可以通過查看 SQL 的執行計劃得到。

之前看過一個關于上一頁、下一頁的實現技巧。假如每頁顯示 20 條數據，那么查詢數據庫的時候，用limit #offset#, 21取出 21 條記錄，頁面展現20條。如果取到了 21 條，說明下一頁還有數據，在頁面展示下一頁按鈕。如果結果集數量不足 21，說明已經到了最后一頁，無需顯示下一頁按鈕了。這種方式完全避免了在分頁查詢時對總條目數量的查詢。

還有一種策略是基于這樣的假設：用戶比較關心的是最近產生的一小部分數據。在用戶查詢的時候，我們可以一次性從數據庫查詢符合條件的 N 條數據緩存起來，足夠用戶翻個幾頁，這樣哪怕是使用電梯式分頁，在計算總頁數時也無需查詢數據庫了。

總結

被分頁問題坑了，其實還是說明一個問題：圖樣圖森破，姿勢水平有待提高。這次寫洗數據的代碼經歷好幾次方案變換和代碼重寫，才得到一個勉強拿得出手的版本，期間各種漲姿勢，好有收獲。

來自：http://www.jamespan.me/blog/2015/01/22/trick-of-paging-query/