大數據分頁方案

來自：魅族科技開發團隊

作者：流星狂飆

軟體開發中，常用要用到分頁、計算總數，數據量超過千萬、上億的時候，往往的需要超過 1s 的執行時間，甚至 3-5s，對於一個追求性能的前沿團隊來說，這個不能忍啊！

為什麼會慢？

mysql 會對所有符合的條件做一次掃描。

如果 a=%d 的數據有 1000W 條，那麼資料庫就會掃描一次 1000W 條資料庫。如果不帶查詢條件，那這種全表掃描將更可怕。

count(*) 和 count(1)、count(0)Example 1:

Example 2:

怎麼解決？

MyISAM DB

MyISAM 引擎很容易獲得總行數的統計，查詢速度變得更快。因為 MyISAM 存儲引擎已經存儲了表的總行數。

MyISAM 會為每張表維護一個 row count 的計數器，每次新增加一行，這個計數器就加 1。但是如果有查詢條件，那麼 MyISAM 也 game over 了，MyISAM 引擎不支持條件緩存。

其他 DB 引擎

受到 MySIAM DB 的啟發，我們可以手動維護總數緩存在表的索引中了。

1、如果 ID 連續，且基本不會斷開。直接取最大值 ID

2、如果表中存在連續的數字列並設為索引，那麼通過頁碼即可計算出此欄位的範圍，直接作範圍查詢即可：

1、涉及到總數操作，專門維護一個總數。新增一個用戶，總數值加 1, 需要總數的時候直接拿這個總數, 比如分頁時。如果有多個條件，那麼就需要維護多個總數列。該方案的擴展性更好，隨著用戶表數量增大, 水平切分用戶表，要獲取用戶總數，直接查詢這個總數表即可。

分頁正反偏移

資料庫自帶的 skip 和 limit 的限制條件為我們創建了分頁的查詢方式，但是如果利用不對，性能會出現千倍萬倍差異。

簡單一點描述：limit 100000,20 的意思掃描滿足條件的 100020 行，扔掉前面的 100000 行，返回最後的 20 行，問題就在這裡。如果我反向查詢 oder by xx desc limit 0,20，那麼我只要索引 20 條數據。

Example 3

正向偏移查詢。超級浪費的查詢，需要先 skip 大量的符合條件的查詢。

反向偏移查詢。同樣的查詢結果，千差萬別的結果。

這兩條 sql 是為查詢最後一頁的翻頁 sql 查詢用的。由於一次翻頁往往只需要查詢較小的數據，如 10 條，但需要向後掃描大量的數據，也就是越往後的翻頁查詢，掃描的數據量會越多，查詢的速度也就越來越慢。

由於查詢的數據量大小是固定的，如果查詢速度不受翻頁的頁數影響，或者影響最低，那麼這樣是最佳的效果了（查詢最後最幾頁的速度和開始幾頁的速度一致）。

在翻頁的時候，往往需要對其中的某個欄位做排序（這個欄位在索引中），升序排序。那麼可不可以利用索引的有序性來解決上面遇到的問題。

比如有 10000 條數據需要做分頁，那麼前 5000 條做 asc 排序，後 5000 條 desc 排序，在 limit startnum，pagesize 參數中作出相應的調整。

但是這無疑給應用程序帶來複雜，這條 sql 是用於論壇回復帖子的 sql，往往用戶在看帖子的時候，一般都是查看前幾頁和最後幾頁，那麼在翻頁的時候最後幾頁的翻頁查詢採用 desc 的方式來實現翻頁，這樣就可以較好的提高性能。

游標：上一頁的最大值或者最小值

如果你知道上一頁和下一頁的臨界值，那麼翻頁查詢也是信手拈來了，直接就告訴了資料庫我的起始查詢在哪，也就沒有什麼性能問題了。我更願意稱這個東西為游標 (Cursor)。

如果做下拉刷新，那麼就直接避免掉分頁的問題了。根據上一頁的最後一個值去請求新數據。

緩存和不精準

數據量達到一定程度的時候，用戶根本就不關心精準的總數, 沒人關心差幾個。看看知乎、微博、微信訂閱號，不精準的統計到處都是。

如果每次點擊分頁的時候都進行一次 count 操作，那速度肯定不會快到哪裡去。他們一般也是採用計數器的辦法。每次新增加一個粉絲，就把值加 1，直接在用戶信息存儲一個總數，一段時間後重新查詢一次，更新該緩存。這樣分頁的時候直接拿這個總數進行分頁，顯示的時候直接顯示模糊之就行。

那為什麼微信公眾號的閱讀量只有 10W+ 這個量級呢？100W+ 級去哪了！

其他大神的建議

1、mysql 的數據查詢, 大小欄位要分開, 這個還是有必要的, 除非一點就是你查詢的都是索引內容而不是表內容, 比如只查詢 id 等等

2、查詢速度和索引有很大關係也就是索引的大小直接影響你的查詢效果, 但是查詢條件一定要建立索引, 這點上注意的是索引欄位不能太多，太多索引文件就會很大那樣搜索只能變慢,

3、查詢指定的記錄最好通過 Id 進行 in 查詢來獲得真實的數據. 其實不是最好而是必須，也就是你應該先查詢出複合的 ID 列表, 通過 in 查詢來獲得數據

4、mysql 千萬級別數據肯定是沒問題的, 畢竟現在的流向 web2.0 網站大部分是 mysql 的

5、合理分表也是必須的, 主要涉及橫向分表與縱向分表, 如把大小欄位分開, 或者每 100 萬條記錄在一張表中等等, 像上面的這個表可以考慮通過 uid 的範圍分表, 或者通過只建立索引表, 去掉相對大的欄位來處理.

6、count() 時間比較長, 但是本身是可以緩存在資料庫中或者緩存在程序中的, 因為我們當時使用在後台所以第一頁比較慢但是後面比較理想

7、SELECT id 相對 SELECT差距還是比較大的, 可以通過上面的方法來使用 SELECT id + SELECT… IN 查詢來提高性能

8、必要的索引是必須的, 還是要盡量返回 5%-20% 的結果級別其中小於 5% 最理想;

9、mysql 分頁的前面幾頁速度很快, 越向後性能越差, 可以考慮只帶上一頁, 下一頁不帶頁面跳轉的方法, 呵呵這個比較垃圾但是也算是個方案, 只要在前後多查一條就能解決了. 比如 100,10 你就差 99,12 呵呵，這樣看看前後是否有結果.

10、前台還是要通過其他手段來處理, 比如 lucene/Solr+mysql 結合返回翻頁結果集, 或者上面的分表

11、總數可能是存在內存中, 這樣分頁計算的時候速度很快。累加操作的時候將內存中的值加 1。總數這個值要持久化，還是要存到磁碟上的，也就是資料庫中 (可以是關係型資料庫，也可以是 mongdb 這樣的資料庫很適合存儲計數)。把總數放在內存中，只是避免頻繁的磁碟 i/0 操作 (操作資料庫就要涉及到磁碟讀寫)。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！