就職於某大型國有銀行，多年從事MySQL和分佈式中間件的方案設計與實施工作；資深MySQL數據庫專家，架構師；DBLE開源項目積極貢獻者。

一、場景描述

對於訂單、交易流水之類的表，常見是應用層會生成訂單號、交易流水號之類的唯一編號，dble 則是以這個唯一編號分庫分表，而落到 MySQL 的物理表上，也是直接以這個編號字段作為表的主鍵。

在本文中，討論在符合以下所有條件的場景下，查詢的分頁技巧：

• dble 的拆分列（sharding key）同時也是 MySQL 物理表的主鍵
• 連續翻頁∘ 每次查詢的頁只能是上一次查詢的前一頁或者後一頁∘ 第一次查詢必須為首頁

1. 表結構

<code>CREATE TABLE many_node_table (    id CHAR(128) PRIMARY KEY,    ts TIMESTAMP NOT NULL,    branchId CHAR(5) NOT NULL,    departId CHAR(10) NOT NULL,    opType VARCHAR(20) NOT NULL,    operator VARCHAR(20) NOT NULL,    INDEX idx_ts (ts),    INDEX idx_branchId_departId (branchId, departId)) COMMENT 'This is an order table'/<code>

2. 拆分方式

<code><schema>    <table>/<schema>/<code>

<code><tablerule>    <columns>id    <algorithm>hash_128_datanodes/<algorithm>/<columns>/<tablerule>id<function>    <property>1/<property>    <property>128/<property>/<function>/<code> 

二、直接翻頁

MySQL 語法支持 LIMIT [start,] length 語法來進行翻頁，例如：

<code>SELECT *FROM many_node_tableWHERE ts BETWEEN TIMESTAMP('2019-01-01 00:00:00') AND TIMESTAMP('2019-03-31 23:59:59')  AND branchId = 'user_specified_bratop Mch'  Atop MD departId = 'user_specified_department'ORDER BY id-- n is the page number-- M is the page size which means the max records of one page, should not change during the pagingLIMIT (n-1)*M, M/<code>

在獲取首頁（n=1）時，這個 SQL 的執行計劃可優化為“每個 MySQL 各自返回符合條件的局部 top M 記錄，然後 dble 對各個 MySQL 的局部 top M 記錄進行進一步篩選，得到全局 top M 記錄”。由於 dble 能夠下推計算給 MySQL（讓各個 MySQL 計算局部 top M），一方面，減少了 dble 需要處理的數據量，減少了對 dble 的空間佔用和代價較高的網絡傳輸量，另一方面， MySQL 數量多於 dble，下推給 MySQL 的計算相當於獲得了並行計算的好處。因此，獲取首頁的理論性能並不差。

但是，在獲取後續的頁面時，該 SQL 的執行性能隨著頁碼增大（n 趨向於 ＋∞）而不斷劣化。原因在於此時現階段的 dble 無法下推計算給 MySQL。以獲取第 2 頁（n=2）為例，dble 無法直接否定“第一頁和第二頁數據都在同一個 dataNode 上”這種場景，所以 dble 交給 MySQL 的 LIMIT 子句為了照顧這種場景，假設頁體積為 100，那麼實際下推的只能是 LIMIT 0, 200，以此類推，由於從第一頁到第n頁數據都在同一個 dataNode 上的牽制，dble 為了保證執行計劃的安全，只能讓 MySQL 執行 LIMIT 0, n*M，導致頁碼 n 越往後，dble 要處理的數據量就越大，從而性能每況愈下。

三、最佳實踐

為了克服直接翻頁在頁數靠後時的性能劣化問題，其中一種解決思路就是解決掉 dble 只能下推 LIMIT 0, n*M 的無奈。從操作上來說，我們最終的目標是讓 LIMIT 子句與頁碼 n 無關，最好是恆定為 LIMIT 0, M（即 LIMIT M）。

至此，解決思路就很明顯了：讓 dble 下推 SQL 給 MySQL 時，告知 MySQL 不要返回已經拿到過了的記錄就好了。

id NOT IN ( retrivedIds ... )這樣的 WHERE 條件，在頁碼增大時，會導致需要列舉的 id 過多，執行效率低下，語句也很容易超出 max_packet_size 的限制。因此，我們應該對結果集進行基於 id 的排序，然後就能使用更為簡潔的 WHERE 條件 id > maxId 來在 MySQL 層面過濾掉不需要的記錄了。

下面就是基於這個思路的實踐方法。

1. 獲取首頁

直接翻頁的語句獲取首頁的效率已是最高，直接使用直接翻頁的 SQL，但對返回結果中，id 字段的最小值和最大值分別記錄為 minId 和 maxId，用於後面的翻頁動作。

<code>SELECT  *FROM many_node_tableWHERE   ts BETWEEN TIMESTAMP('2019-01-01 00:00:00') AND TIMESTAMP('2019-03-31 23:59:59')  AND branchId = 'user_specified_branch'  AND departId = 'user_specified_department'ORDER BY idLIMIT M/<code> 

2. 向後/向前翻頁

以向後翻頁為例。

替換以下 SQL 中的 maxId 後，交 給dble 執行。返回的記錄本身按照 id 字段已經有序，直接就是下一頁內容。記得更新 minId 和 maxId。

<code>SELECT  *FROM many_node_tableWHERE   ts BETWEEN TIMESTAMP('2019-01-01 00:00:00') AND TIMESTAMP('2019-03-31 23:59:59')  AND branchId = 'user_specified_branch'  AND departId = 'user_specified_department'  -- tell MySQL do not return retrived rows --  id > maxIdORDER BY idLIMIT M/<code>

同樣道理，向前翻頁就是替換以下 SQL 中的 minId 後，交給 dble 執行。千萬要記得更新 minId 和 maxId。

<code>SELECT  *FROM many_node_tableWHERE   ts BETWEEN TIMESTAMP('2019-01-01 00:00:00') AND TIMESTAMP('2019-03-31 23:59:59')  AND branchId = 'user_specified_branch'  AND departId = 'user_specified_department'  -- tell MySQL do not return retrived rows --  id < minIdORDER BY id DESCLIMIT M/<code>

最佳實踐的限制與注意事項

• 沒有銀彈方案，最佳實踐由以下限制或注意事項：
• dble 的拆分列（sharding key）同時也是 MySQL 物理表的主鍵
• 連續翻頁∘ 每次查詢的頁只能是上一次查詢的前一頁或者後一頁∘ 第一次查詢必須為首頁
• 翻頁 SQL 必須是單表 SQL，因為兩個表 JOIN 的時候，結果集裡1條記錄的字段可能實際上來自不同的表，而導致記錄有多個拆分列值，無法按照本方法翻頁
• 翻頁 SQL 必須要有 ORDER BY 子句
• 翻頁 SQL 的 ORDER BY 後綴必須為拆分列，繼續上文的例子，可以是 ORDER BY id、ORDER BY ts, id，但不能是 ORDER BY id, ts
• 無論是“獲取首頁”還是“向後/向前翻頁”，其 SQL 一般都是廣播語句（需要查詢該表所有 dataNode），廣播語句對 MySQL 的 max_connections 連接數消耗明顯，因此翻頁查詢應該要算到廣播語句中，而廣播語句的併發量建議不要超過單個 MySQL 的 max_connections 的 10%，例如 MySQL 的 max_connections 為 512，則包含翻頁查詢在內的所有廣播語句的併發量建議不要超過 51 條。
• 從保護 dble 內存出發，建議每頁最多記錄數 M 與邏輯分片數量 dataNodeCount 乘積不多於 8000，即 M * dataNode <= 8000
• 依賴 dble 的客戶端控制翻頁，增加了開發成本。

閱讀更多 愛可生 的文章

關鍵字: 注意事項 MySQL 銀彈