Mysql跨庫分頁查詢方案總結

背景

上文說到，由於用戶表數據量過大，通過水平拆分用戶表到不同的資料庫實際上來提高資料庫性能。本文將分析如何解決不同資料庫實例上的數據分頁解決方案。

現假設有N個資料庫實例，按照時間time排序，查詢偏移量為X，查詢數據量為Y。

全局視野法

全局視野法是通過將每一個資料庫實例上的分頁數據查詢到內存中，然後在服務層進行內存排序，得到分頁數據。主要步驟如下：

將分頁語句order by time offset X limit Y，改寫成order by time offset 0 limit X+Y。服務層對得到的N*(X+Y)條數據進行內存排序，內存排序後再取偏移量X後的Y條記錄。此種方式能夠精確返回所需數據，但是方法隨著翻頁的進行數據的傳輸量和排序的數據量不會隨著不斷翻頁而導致性能下降，返回的數據量越來越大，性能越來越低。

翻頁查詢法

翻頁查詢法是通過折中業務需求，砍掉跳頁查詢的需求，只查詢下一頁數據。主要步驟如下：

用正常的方法取得第一頁數據，並得到第一頁記錄的time_max。每次翻頁，將分頁語句order by time offset X limit Y，改寫成order by time where time>$time_max limit Y。數據合併，內存排序，取第一頁數據。此種方式的數據的傳輸量和排序的數據量不會隨著不斷翻頁而導致性能下降，但是可以保證每次只返回一頁數據，性能為常量。

模糊查詢法

模糊查詢法是以業務上可以接受一定的數據偏差，允許數據精度損失為前提。具體步驟如下：

將分頁語句order by time offset X limit Y，改寫成order by time offset X/N limit Y/N。數據合併，內存排序。二次查詢法

二次查詢法是通過兩次查詢資料庫的方式得到分頁數據的方法。具體步驟如下：

將分頁語句order by time offset X limit Y，改寫成order by time offset X/N limit Y。合併數據，內存排序，找到最小值time_min。between二次查詢，order by time between $time_min and $time_i_max。設置虛擬time_min，找到time_min在各個分庫的offset，從而得到time_min在全局的offset。得到了time_min在全局的offset，自然得到了全局的offset X limit Y。二次查詢法能夠精確地返回業務所需數據，每次返回的數據量都非常小，不會隨著翻頁增加數據的返回量，但是每次都需要兩次查詢，對性能有一定的影響。

總結

實際跨庫分頁查詢方案的選擇應該根據實際業務來選擇，是側重於性能，還是側重於功能，作一定的取捨。

大數據量高並發資料庫優化總結

資料庫優化-數據表水平拆分

從Spring框架中學習設計模式

JAVA面試題：你在項目中使用多線程的場景？

聚合支付系統架構設計和二維碼生成原理