一 場(chǎng)景描述

對(duì)于訂單、交易流水之類的表，常見是應(yīng)用層會(huì)生成訂單號(hào)、交易流水號(hào)之類的唯一編號(hào)，dble則是以這個(gè)唯一編號(hào)分庫(kù)分表，而落到MySQL的物理表上，也是直接以這個(gè)編號(hào)字段作為表的主鍵。

在本文中，討論在符合以下所有條件的場(chǎng)景下，查詢的分頁(yè)技巧：

• dble的拆分列（sharding key）同時(shí)也是MySQL物理表的主鍵
• 連續(xù)翻頁(yè)
  • 每次查詢的頁(yè)只能是上一次查詢的前一頁(yè)或者后一頁(yè)
  • 第一次查詢必須為首頁(yè)

1. 表結(jié)構(gòu)

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'

2. 拆分方式

<!-- schema.xml -->
<schema name="testdb" >
    <table name="many_node_table" rule="hash_by_id" dataNode="dn$0-127" />
</schema>

<!-- rule.xml -->
<tableRule name="hash_by_id">
    <columns>id</column>
    <algorithm>hash_128_datanodes</algorithm>
</tableRule>id

<function name="hash_128_datanodes" class="Hash">
    <property name="length">1</property>
    <property name="count">128</property>
</function>

二 直接翻頁(yè)

MySQL語(yǔ)法支持LIMIT [start,] length語(yǔ)法來(lái)進(jìn)行翻頁(yè)，例如：

SELECT *
FROM many_node_table
WHERE 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 paging
LIMIT (n-1)*M, M

在獲取首頁(yè)（n=1）時(shí)，這個(gè)SQL的執(zhí)行計(jì)劃可優(yōu)化為“每個(gè)MySQL各自返回符合條件的局部top M記錄，然后dble對(duì)各個(gè)MySQL的局部top M記錄進(jìn)行進(jìn)一步篩選，得到全局top M記錄”。由于dble能夠下推計(jì)算給MySQL（讓各個(gè)MySQL計(jì)算局部top M），一方面，減少了dble需要處理的數(shù)據(jù)量，減少了對(duì)dble的空間占用和代價(jià)較高的網(wǎng)絡(luò)傳輸量，另一方面，MySQL數(shù)量多于dble，下推給MySQL的計(jì)算相當(dāng)于獲得了并行計(jì)算的好處。因此，獲取首頁(yè)的理論性能并不差。

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

三 最佳實(shí)踐

為了克服直接翻頁(yè)在頁(yè)數(shù)靠后時(shí)的性能劣化問(wèn)題，其中一種解決思路就是解決掉dble只能下推LIMIT 0, n*M的無(wú)奈。從操作上來(lái)說(shuō)，我們最終的目標(biāo)是讓LIMIT子句與頁(yè)碼n無(wú)關(guān)，最好是恒定為LIMIT 0, M(即LIMIT M)。

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

id NOT IN ( retrivedIds ... )這樣的WHERE條件，在頁(yè)碼增大時(shí)，會(huì)導(dǎo)致需要列舉的id過(guò)多，執(zhí)行效率低下，語(yǔ)句也很容易超出max_packet_size的限制。因此，我們應(yīng)該對(duì)結(jié)果集進(jìn)行基于id的排序，然后就能使用更為簡(jiǎn)潔的WHERE條件id > maxId來(lái)在MySQL層面過(guò)濾掉不需要的記錄了。

下面就是基于這個(gè)思路的實(shí)踐方法。

1. 獲取首頁(yè)

直接翻頁(yè)的語(yǔ)句獲取首頁(yè)的效率已是最高，直接使用直接翻頁(yè)的SQL，但對(duì)返回結(jié)果中，id字段的最小值和最大值分別記錄為minId和maxId，用于后面的翻頁(yè)動(dòng)作。

SELECT
  *
FROM many_node_table
WHERE 
  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 id
LIMIT M

2. 向后/向前翻頁(yè)

以向后翻頁(yè)為例。

替換以下SQL中的maxId后，交給dble執(zhí)行。返回的記錄本身按照id字段已經(jīng)有序，直接就是下一頁(yè)內(nèi)容。記得更新minId和maxId。

SELECT
  *
FROM many_node_table
WHERE 
  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 > maxId
ORDER BY id
LIMIT M

同樣道理，向前翻頁(yè)就是替換以下SQL中的minId后，交給dble執(zhí)行。千萬(wàn)要記得更新minId和maxId。

SELECT
  *
FROM many_node_table
WHERE 
  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 < minId
ORDER BY id DESC
LIMIT M

最佳實(shí)踐的限制與注意事項(xiàng)

沒(méi)有銀彈方案，最佳實(shí)踐由以下限制或注意事項(xiàng)：

• dble的拆分列（sharding key）同時(shí)也是MySQL物理表的主鍵
• 連續(xù)翻頁(yè)
  • 每次查詢的頁(yè)只能是上一次查詢的前一頁(yè)或者后一頁(yè)
  • 第一次查詢必須為首頁(yè)
• 翻頁(yè)SQL必須是單表SQL，因?yàn)閮蓚€(gè)表JOIN的時(shí)候，結(jié)果集里1條記錄的字段可能實(shí)際上來(lái)自不同的表，而導(dǎo)致記錄有多個(gè)拆分列值，無(wú)法按照本方法翻頁(yè)
• 翻頁(yè)SQL必須要有ORDER BY子句
• 翻頁(yè)SQL的ORDER BY后綴必須為拆分列，繼續(xù)上文的例子，可以是ORDER BY id、ORDER BY ts, id，但不能是ORDER BY id, ts
• 無(wú)論是“獲取首頁(yè)”還是“向后/向前翻頁(yè)”，其SQL一般都是廣播語(yǔ)句（需要查詢?cè)摫硭衐ataNode），廣播語(yǔ)句對(duì)MySQL的max_connections連接數(shù)消耗明顯，因此翻頁(yè)查詢應(yīng)該要算到廣播語(yǔ)句中，而廣播語(yǔ)句的并發(fā)量建議不要超過(guò)單個(gè)MySQ的max_connections的10%，例如MySQL的max_connections為512，則包含翻頁(yè)查詢?cè)趦?nèi)的所有廣播語(yǔ)句的并發(fā)量建議不要超過(guò)51條
• 從保護(hù)dble內(nèi)存出發(fā)，建議每頁(yè)最多記錄數(shù)M與邏輯分片數(shù)量dataNodeCount乘積不多于8000，即M * dataNode <= 8000
• 依賴dble的客戶端控制翻頁(yè)，增加了開發(fā)成本