專案裡面的一個分表用到了sharding-jdbc
當時糾結過是用mycat還是用sharding-jdbc的, 但是最終還是用了sharding-jdbc, 原因如下:
1. mycat比較重, 相對於sharding-jdbc只需匯入jar包就行, mycat還需要部署維護一箇中介軟體服務.由於我們只有一個表需要分表, 直接用輕量級的sharding-jdbc即可.
2. mycat作為一箇中間代理服務, 難免有效能損耗
3. 其他組用mycat的時候出現過生產BUG
複製程式碼然而sharding-jdbc也同樣是坑坑窪窪不斷的, 我們從2.x版本改成4.x版本, 又從4.x版本降到了3.x版本,每一個版本都踩到了坑(有些是sharding-jdbc的, 有些是由於我們專案依賴的), 最終不得已改動了一下原始碼才趟過去(其實就是註釋了一行程式碼).
今天就來聊一下其中的一個坑--分表分頁
問題描述
背景
CREATE TABLE `order_00` (
`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '邏輯主鍵',
`orderId` varchar(32) NOT NULL COMMENT '訂單ID',
`CREATE_TM` datetime DEFAULT NULL COMMENT '訂單建立時間',
PRIMARY KEY (`ID`) USING BTREE,
UNIQUE KEY `IDX_ORDER_POSTID` (`orderId`) USING BTREE,
KEY `IDX_ORDER_CREATE_TM` (`CREATE_TM`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='訂單表';
CREATE TABLE `order_01` (
`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '邏輯主鍵',
`orderId` varchar(32) NOT NULL COMMENT '訂單ID',
`CREATE_TM` datetime DEFAULT NULL COMMENT '訂單建立時間',
PRIMARY KEY (`ID`) USING BTREE,
UNIQUE KEY `IDX_ORDER_POSTID` (`orderId`) USING BTREE,
KEY `IDX_ORDER_CREATE_TM` (`CREATE_TM`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='訂單表';
CREATE TABLE `order_02` (
`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '邏輯主鍵',
`orderId` varchar(32) NOT NULL COMMENT '訂單ID',
`CREATE_TM` datetime DEFAULT NULL COMMENT '訂單建立時間',
PRIMARY KEY (`ID`) USING BTREE,
UNIQUE KEY `IDX_ORDER_POSTID` (`orderId`) USING BTREE,
KEY `IDX_ORDER_CREATE_TM` (`CREATE_TM`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='訂單表';
複製程式碼假設有以上三個分表, 分表邏輯用orderId取模, 即orderId=0的寫到order_00,orderId=1的寫到order_01,orderId=2的寫到order_02.
備註: 這裡為啥不用時間分表而用orderId做hash, 當時也是頗有爭議的. 理論上訂單表更適合使用時間做分表, 這樣一來時間越老的資料訪問的頻率越小, 舊的分表逐漸就會成為冷表, 不再被訪問到. 當時負責人的說法是, 由於這個表讀寫頻率都高(而且場景中經常需要讀主庫), 用orderId分表可以均衡寫負載和讀負載. 雖然是有點牽強, 但也有一定道理, 就先這麼實現了
業務上需要根據orderId或CREATE_TM進行分頁查詢, 即查詢sql的mybatis寫法大概如下:
<select id="queryPage" parameterType="xxx" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from ORDER
<if test="orderId !=null and orderId !='' ">
AND orderId=#{orderId , jdbcType=VARCHAR}
</if>
<if test="createTmStartStr!=null and createTmStartStr!='' ">
AND create_tm >= concat(#{createTmStartStr, jdbcType=VARCHAR},' 00:00:00')
</if>
<if test="createTmEndStr!=null and createTmEndStr!='' ">
AND create_tm <= concat(#{createTmEndStr, jdbcType=VARCHAR},' 23:59:59')
</if>
limit #{page.begin}, #{page.pageSize}
</select>
複製程式碼用過sharding-jdbc的都知道, sharding-jdbc一共有5種分片策略,如下圖所示. 沒用過的可以參考官網
除了Hint分片策略, 其他的分片策略都要求sql的where條件需要包含分片列(在我們的表中是orderId), 很明顯我們的業務場景中不能保證sql的where條件中一定會包含有orderId, 所以我們只能使用HintShardingStrategy,將頁面的查詢條件傳遞給分片策略演算法中, 再判斷查詢哪個表, 大概程式碼如下
public class OrderHintShardingAlgorithm implements HintShardingAlgorithm {
public static final String ORDER_TABLE = "ORDER";
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ShardingValue shardingValue) {
ListShardingValue<String> listShardingValue = (ListShardingValue<String>) shardingValue;
List<String> list = Lists.newArrayList(listShardingValue.getValues());
List<String> actualTable = Lists.newArrayList();
// 頁面上的查詢條件會以json的方式傳到shardingValue變數中
String json = list.get(0);
OrderQueryCondition req = JSON.parseObject(json, OrderQueryCondition.class);
String orderId = req.getOrderId();
// 查詢條件沒有orderId, 要查所有的分表
if(StringUtils.isEmpty(orderId)){
// 所有的分表
for(int i = 0 ; i< 3; i++){
actualTable.add(ORDER_TABLE + "_0" + i);
}
}else{
// 如果指定了orderId, 只查orderId所在的分表即可
long tableSuffix = ShardingUtils.getHashInteger(orderId);
actualTable.add(ORDER_TABLE + "_0" + tableSuffix);
}
// actualTable中包含sharding-jdbc實際會查詢的表
return actualTable;
}
}
複製程式碼這樣子, 如果我們根據orderId來查詢的話, sharding-jdbc最終執行的sql就是(假設每頁10條):
select * from ORDER_XX where orderId = ? limit 0 ,10
複製程式碼如果查詢條件沒有orderId, 那麼最終執行的sql就是3條(假設每頁10條):
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 ,10 ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 ,10 ;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 ,10 ;
複製程式碼注意在有多個分表的情況下, 每個表都取前10條資料出來(一共30條), 然後再排序取前10條, 這樣的邏輯是不對的. sharding-jdbc給了個例子, 如果下圖:
圖中的例子中,想要取得兩個表中共同的按照分數排序的第2條和第3條資料，應該是95和90。 由於執行的SQL只能從每個表中獲取第2條和第3條資料，即從t_score_0表中獲取的是90和80；從t_score_0表中獲取的是85和75。 因此進行結果歸併時，只能從獲取的90，80，85和75之中進行歸併，那麼結果歸併無論怎麼實現，都不可能獲得正確的結果.
那怎麼辦呢?
sharding-jdbc的做法就改寫我們的sql, 先查出來所有的資料, 再做歸併排序
例如查詢第2頁時
原sql是:
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 10 ,10 ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 10 ,10 ;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 10 ,10 ;
會被改寫成:
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 ,20 ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 ,20 ;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 ,20 ;
複製程式碼查詢第3頁時
原sql是:
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 20 ,10 ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 20 ,10 ;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 20 ,10 ;
會被改寫成:
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 ,30 ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 ,30 ;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 ,30 ;
複製程式碼當然, 大家肯定會覺得這樣處理效能會很差, 其實事實上也的確是, 不過sharing-jdbc是在這個基礎上做了優化的,就是上面提到的"歸併", 具體歸併過程可以戳這裡檢視官網的說明.篇幅比較長, 我這裡就不再貼出來了
大概的邏輯就是先查出所有頁的資料, 然後通過流式處理跳過前面的頁,只取最終需要的頁,最終達到分頁的目的
踩坑
既然sharding-jdbc都已經優化好了, 那麼我們踩到的坑到底是什麼呢?
聽我慢慢道來
在io.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement#getResultSet()中有個邏輯,
如果查詢的分表數只有一個的話, 就不會做歸併的邏輯(然而就算只查一個分表, sql的limit子句也會被改寫了), 如圖:
回到我們的業務場景, 如果查詢條件包含了orderId的話, 因為可以定位到具體的表, 所以最終需要查詢的分表就只有一個.
那麼問題就來了, 由於sharding-jdbc把我們的sql的limit子句給改寫了, 後面卻由於只查一個分表而沒有做歸併(也就是沒有跳過前面的頁),所以最終不管是查詢第幾頁,執行的sql都是(假設頁大小是10000):
select * from ORDER_XX where orderId = ? limit 0 ,10000
select * from ORDER_XX where orderId = ? limit 0 ,20000
select * from ORDER_XX where orderId = ? limit 0 ,30000
select * from ORDER_XX where orderId = ? limit 0 ,40000
......
複製程式碼這樣就導致了一個問題, 不管我傳的頁碼是什麼, sharding-jdbc都會給我返回同一條資料. 很明顯這樣是不對的.
當然, 心細的朋友可能會發現了, 由於orderId是個唯一索引, 所以肯定只有一條資料, 所以永遠不會存在查詢第二頁的情況.
正常來說的確是這樣, 然而在我們的程式碼裡面, 還有個老邏輯: 匯出查詢結果(就是匯出所有頁的資料)時, 會非同步地在後臺一頁一頁地 匯出, 直到匯出了所有的頁或者達到了查詢次數上限(假設是查詢1萬次).
所以在根據orderId匯出的時候, 因為每一頁都返回相同的資料, 所以判斷不了什麼時候是"導完了所有的頁", 所以正確結果本應該是隻有一條資料的, 但是在sharding-jdbc下卻執行了一萬次, 匯出了一萬條相同的資料, 你說這個是不是坑呢?
知道問題所在, 那解決就簡單了. 但是本文並不是想聊怎麼解決這個問題的, 而是想聊聊通過這個問題引起的思考:
在mysql分表環境下, 如何高效地做分頁查詢?
複製程式碼對mysql分頁的思考
limit 優化
在討論分表環境下的分頁效能之前, 我們先來看一下單表環境下應該實現分頁.
眾所周知, 在mysql裡面實現分頁只需要使用limit子句即可, 即
select * from order limit (pageNo-1) * pageSize, pageSize
複製程式碼由於在mysql的實現裡面, limit offset, size是先掃描跳過前面的offset條資料,再取size條資料. 當pageNo越大的時候, offset也會越大, mysql掃描的資料也越大, 所以效能會急劇下降.
因此, 分頁第一個要解決的問題就是當pageNo過大時, 怎麼優化效能.
第一個方案是這篇文章介紹的索引覆蓋的方案. 總結來說就是把sql改寫成這樣:
select * from order where id >= (select id from order limit (pageNo-1) * pageSize, 1) limit pageSize
複製程式碼利用索引覆蓋的原理, 先直接定位當前頁的第一條資料的最小id, 然後再取需要的資料.
這樣的確可以提高效能, 但是我認為還是沒有徹底解決問題, 因為當pageNo過大的時候, mysql還是會需要掃描很多的行來找到最小的id. 而掃描的那些行都是沒有意義.
scroll 遊標查詢
遊標查詢是elasticSearch裡面的一個術語, 但是我這裡並不是指真正的scroll查詢, 而是借鑑ES裡面的思想來實現mysql的分頁查詢.
所謂的scroll就是滾動, 一頁一頁地查. 大概的思想如下:
1. 查詢第1頁
select * from order limit 0, pageSize;
2. 記錄第1頁的最大id: maxId
3. 查詢第2頁
select * from order where id > maxId limit pageSize
4. 把maxId更新為第2頁的最大id
... 以此類推
複製程式碼可以看到這種演算法對於mysql來說是毫無壓力的, 因為每次都只需要掃描pageSize條資料就能達到目的. 相對於上面的索引覆蓋的方案, 可以極大地提高查詢效能.
當然它也有它的侷限性:
1. 效能的提高帶來的代價是程式碼邏輯的複雜度提高. 這個分頁邏輯實現起來比較複雜.
2. 這個演算法對業務資料是有要求的, 例如id必須是單調遞增的,而且查詢的結果需要是用Id排序的.
如果查詢的結果需要按其他欄位(例如createTime)排序, 那就要求createTime也是單調的, 並把演算法中的id替換成createTime.
有某些排序的場景下, 這種演算法會不適用.
3. 這個演算法是需要業務上做妥協的, 你必須說服你的產品經理放棄"跳轉到特定頁"的功能, 只能通過點選"下一頁"來進行翻頁.
(這才是scroll的含義, 在手機或平板上,只能通過滾動來翻頁,而無法直接跳轉到特定頁)
複製程式碼分表環境下的分頁查詢
如上面討論, 在單表環境下, 想要實現高效的分頁, 還是相對比較簡單的.
那如果在分表環境下, 分頁的實現會有什麼不同呢?
正如上面提到的, sharding-jdbc中已經論證過了, 分表環境的分頁查詢, 如果不把
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit (pageNo-1) * pageSize ,pageSize ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit (pageNo-1) * pageSize ,pageSize;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit (pageNo-1) * pageSize ,pageSize ;
複製程式碼改寫成
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 , (pageNo-1) * pageSize + pageSize ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 , (pageNo-1) * pageSize + pageSize;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 , (pageNo-1) * pageSize + pageSize ;
複製程式碼那麼最終查出來的資料, 很有可能不是正確的資料. 所以在分表環境下, 上面所說的"索引覆蓋法"和"遊標查詢法"肯定是都不適用了的. 因為必須查出所有節點的資料,再進行歸併, 那才是正確的資料.
因此, 要在分表環境下實現分頁功能, 基本上是要對limit子句進行改寫了的.
先來看sharing-jdbc的解決方案, 改寫後的limit 0 , (pageNo-1) * pageSize + pageSize 和原來的limit (pageNo-1) * pageSize, pageSize對比, 資料庫端的查詢壓力都是差不多的, 因為都是要差不多要 掃描(pageNo-1) * pageSize 行才能取得到資料. 不同的是改寫sql後, 客戶端的記憶體消耗和網路消耗變大了.
sharding-jdbc巧妙地利用流式處理和優先順序佇列結合的方式, 消除了客戶端記憶體消耗的壓力, 但是網路消耗的影響依然是無法消除.
所以真的沒有更好的方案了?
那肯定是有的, 在業界難題-“跨庫分頁”的四種方案這篇文章中, 作者提到了一種"二次查詢法", 就非常巧妙地解決了這個分頁查詢的難題. 大家可以參考一下.
但是仔細思考一下, 還是有一定的侷限性的:
1. 當分表數為N時, 查一頁資料要執行N*2條sql.(這個無解, 只要分表了就必須這樣)
2. 當offset很大的時候, 第一次查詢中掃描offset行資料依然會非常的慢, 如果只分表不分庫的話, 那麼一次查詢會在一個庫中產生N條慢sql
3. 演算法實現起來程式碼邏輯應該不簡單, 如果為了一個分頁功能寫這麼複雜的邏輯, 是不是划不來,
而且後期也不好維護
複製程式碼如果演算法原作者看到我這裡的雞蛋挑骨頭, 會不會有點想鄙視我~~
其實我想表達的意思是, 既然分表環境下的分頁查詢沒有完美的解決方案的話,或者實現起來成本過大的話, 那是不是可以認為: 分表環境下就不應該做分頁查詢?
離線計算+有損服務
上面說到, 其實分表環境下就不適宜再做分頁查詢的功能. 但是業務上的需求並不是說去掉就去掉的, 很多情況下分頁功能是必須的, 然而分頁查詢的存在通常也是為了保護資料庫, 去掉了分頁功能, 資料庫的壓力反而更大.
所以分表和分頁只能二選一?
不, 我全都要, 分表我要, 分頁我也要!
但是分頁功能不在分表環境裡面做, 而是在另外一張彙總表裡面做分頁查詢的功能.
大概的方案就是:
1. 正常的業務讀寫分表
2. 根據具體的業務需求,例如實時計算/離線計算技術(spark, hadoop,hive, kafka等)生成各分表的一張彙總表
3. 分頁查詢的介面直接查詢彙總表
複製程式碼另外還要注意這個方案對業務來說肯定是有損的, 具體表現為:
1. 不管是離線計算還是實時計算, 都不能保證實時性, 查詢結果肯定是有時延的
2. 由於彙總表是不可能包含分表的所有資料的, 所以彙總表肯定是隻包含部分資料的,例如只有一個月內的,具體根據業務場景而定
複製程式碼總的來說, 就是報表系統的資料由資料倉儲系統來生成, 但只能生成使用者非要不可的資料,其他的都去掉.
寫這篇總結在找資料的時候, 看到一句話:
其實分表的根本目的是分攤寫負載, 而不是分攤讀負載
複製程式碼其實是有一定道理的, 如果讀負載過高, 我們可以增加快取, 增加資料節點等很多方法, 而寫負載過高的話, 分表基本就是勢在必行了.
從這個理論來說, 分頁是一個讀操作, 根本就沒有必要去讀取分表, 從其他地方讀取(我們這裡是資料倉儲)即可
不分表(分割槽 tidb mongoDb ES)
其實大多數mysql的表都沒有必要分表的
在mysql5.5之前, 表數量大概在在500W之後就要進行優化, 在mysql5.5之後, 表數量在1KW到2KW左右才需要做優化. 在這個效能拐點之前, 可以認為mysql是完全有能力扛得住的.當然, 具體還要看qps以及讀寫衝突等的頻率的.
到了效能拐點之後呢? 那就要考慮對mysql的表進行拆分了. 表拆分的手段可以是分表分庫, 或者就簡單的分割槽.
基本來說, 分割槽和分錶帶來的效能提升是一樣的, 由於分割槽實際上就可以認為是mysql底層來幫我們實現分表的邏輯了, 所以相對來說分表會比分割槽帶來更高的編碼複雜度(分割槽就根本不用考慮多表分頁查詢的問題了). 從這個角度來說, 一般的業務直接分割槽就可以了.
當然, 選擇分割槽還是分表還是需要做一點權衡的:
1. 表中的資料只有部分熱點資料經常訪問, 其他的不常訪問的話, 適合用分割槽表
2. 分割槽表相對容易維護, 可以針對單獨一個分割槽進行檢查,優化, 批量刪除大量資料時, 分割槽表會比一般的表更快
3. 分割槽表可以分佈在不同的物理裝置上, 從而可以高效地利用多個硬碟
4. 如果查詢條件不包含partition key的話, 分割槽表不一定有分表效率高
5. 如果分割槽表中絕對的熱點資料, 每一條資料都有可能被訪問到, 也不太適合分割槽
6. 如果資料量超大, 由於mysql只能分1024個分割槽, 如果1024個分割槽的資料都是千萬以上, 那肯定是也不適合分割槽的了
複製程式碼綜上所述, 如果分割槽表就足夠滿足我們的話, 那其實就沒有必要進行分表了增加程式設計的複雜度了.
另外, 如果不想將資料表進行拆分, 而表的資料量又的確很大的話, nosql也是一個替代方案. 特別是那些不需要強事務的表操作, 就很適合放在nosql, 從而可以避免程式設計的複雜度, 同時效能上也沒有過多的損耗.
nosql的方案也有很多:
1. mongoDb
2. hbase
3. tidb
4. elasticSearch
複製程式碼當然也可以使用mysql+nosql結合的方式, 例如常規讀寫操作mysql, 分頁查詢走ES等等.
今天就先寫到這, 有機會再寫寫mysql和nosql