查詢分頁一般要最少要執(zhí)行兩條 SQL 語句:

SELECT COUNT(*) FROM tablename WHERE columnName = 'xx'

SELECT * FROM tablename WHERE columnName = 'xx' limit 0,100

正常情況下沒有問題，但是當數(shù)據(jù)量非常大的時候，首先 count(*) 會非常慢這是肯定的，其次分頁越多，limit 的效率就會越低。

比如 limit 200000, 10，這個等同于數(shù)據(jù)庫要掃描出 200010 條數(shù)據(jù)，然后再丟棄前面的 200000 條數(shù)據(jù)，返回剩下 10 條數(shù)據(jù)給用戶，這種取法很明顯越往后速度越慢，妥妥的慢 SQL。

《高性能 MySQL》中對這個問題有過說明：

分頁操作通常會使用 limit 加上偏移量的辦法實現(xiàn)，同時再加上合適的 order by 子句。但這會出現(xiàn)一個常見問題：當偏移量非常大的時候，它會導致 MySQL 掃描大量不需要的行然后再拋棄掉。

數(shù)據(jù)模擬

我們創(chuàng)建兩張表（部門表和員工表），并模擬插入 500w 條員工數(shù)據(jù)：

測試下分頁查詢員工的 SQL 執(zhí)行速度，先來看偏移量比較小的情況：

SELECT a.empno,a.empname,a.job,b.depno,b.depname
from emp a 
left join dep b 
on a.depno = b.depno 
order by a.id 
desc limit 100,25;

受影響的行: 0
時間: 0.001s

再來看下偏移量非常大的情況：

SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a 
left join dep b 
on a.depno = b.depno 
order by a.id 
desc limit 4800000,25;

受影響的行: 0
時間: 12.275s

可以很明顯的看出，偏移量很小的時候，查詢速度還是非?？斓?，當偏移量上到百萬量級，這個執(zhí)行時間已經(jīng)無法忍受了，一條查詢語句跑十幾秒這不直接給數(shù)據(jù)庫干阻塞了？

優(yōu)化方案

使用覆蓋索引 + 子查詢

偏移量之前的數(shù)據(jù)是沒有價值的，所以我們可以先在聚集索引中根據(jù)偏移量找到開始位置的 id 值，再根據(jù)這個 id 值去非聚集索引上查詢所需要的行數(shù)據(jù)，這樣就避免了大量的無用的回表查詢。

總結(jié)來說就是：利用子查詢獲取偏移 n 條的位置 id，基于這個位置再往后取

SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a 
left join dep b 
on a.depno = b.depno
where 
 a.id >= (select id from emp order by id limit 4800000,1)
order by a.id 
limit 25;

受影響的行: 0
時間: 1.541s

可以看見，執(zhí)行效率有顯著提升

記錄上次查找位置

這個應該是比較常見的解決手段了，就是記住上次查找結(jié)果的主鍵位置，從而避免使用偏移量。

比如存儲了上次分頁的最后一條數(shù)據(jù) id 是 4800000，SQL 就可以直接跳過4800000，從 4800001 開始掃描表

SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a 
left join dep b 
on a.depno = b.depno
where 
 a.id > 4800000
order by a.id 
limit 25;

受影響的行: 0
時間: 0.000s

這個效率是最好的，無論怎么分頁，耗時基本都是一致的，因為他執(zhí)行完條件之后，都只掃描了 25 條數(shù)據(jù)。

但這種方案只適合順序分頁（比如 Feeds 流場景），這樣才能記住前一個分頁的最后 id。如果用戶跳著分頁，比如剛剛刷完第 25 頁，馬上跳到 35 頁，使用這種方案的話，數(shù)據(jù)顯示的其實是 26 頁的數(shù)據(jù)，而不是 35 頁的。

降級

這種方案屬于兜底策略：為 limit 和 offset 設置一個最大值，超過這個最大值，分頁查詢接口就直接返回空數(shù)據(jù)或者返回錯誤碼。

從業(yè)務角度來說，可以認為超過這個最大值用戶已經(jīng)不是在分頁了，而是在刷數(shù)據(jù)，如果確實是要找某條數(shù)據(jù)，那么正常理解應該是輸入合適的條件來適當縮小范圍，而不是一頁一頁地分頁。