關係型資料庫全表掃描分片詳解

導讀：資料匯流排（DBus）專注於資料的實時採集與實時分發，可以對IT系統在業務流程中產生的資料進行匯聚，經過轉換處理後成為統一JSON的資料格式（UMS），提供給不同資料使用方訂閱和消費，充當數倉平臺、大資料分析平臺、實時報表和實時營銷等業務的資料來源。

在上一篇 關於DBus的文章（ DBus資料庫表結構變更處理方案 ）中 ，我們主要介紹了在DBus的設計中，表結構變更及其帶來的各種問題是如何處理的。本文則是從資料分片的角度出發，具體介紹DBus在資料採集的過程中，運用了什麼樣的分片策略和分片原理，以及過程中遇到的問題及解決方案。

一、分片策略

對於傳統的關係型資料庫，DBus透過提供全量資料拉取和增量資料採集兩種途徑滿足使用者資料採集需求。DBus資料抽取流程如下圖所示(以mysql為例)：

全量資料採集的主要原理是：根據主鍵、唯一索引、索引等資訊，確定分片列。之所以分片列要根據主鍵、唯一索引、索引等選擇，是因為這些列的資料在庫裡建立了良好索引，能提升資料掃描的效率。

根據選定的分片列，對資料進行拆片，確定每片資料的上下界，然後根據每片上下界，以6~8左右的併發度，進行資料拉取。（6~8左右的併發度是經大量測試獲得的經驗值。實驗顯示，6~8左右的併發度既不會對源庫形成過高壓力，又能最大限度提升全量資料拉取的效率。）

DBus分片策略示意圖：

DBus拉取策略示意圖：

那麼，DBus支援什麼型別的列作為分片列？不同型別的分片列，分片策略如何呢?

分片策略這塊，DBus借鑑了Sqoop的分片設計，支援以下型別的列作為分片列：

• BigDecimal/numeric
• Boolean
• Date/time/timestamp
• Float/double
• Integer/smallint/long
• Char/Varchar/Text/NText

拆片原理大體一致，都是根據分片列的最大最小值，以及設定的每片大小，進行每一分片上下界的計算和確定。但具體實現細節差異很大。尤其是Text/NText型別，借鑑、應用的過程中發現一些問題，我們進行了一些調整和最佳化。

本文主要和大家分享一下遇到的坑和我們的解決辦法。

二、分片原理

2.1 數字型別分片列

讓我們先以最簡單、明瞭的數字型別分片列為例介紹分片原理。

如前所述，我們會按照主鍵->唯一索引->索引的優先順序確定分片列。如果表有主鍵，我們以主鍵列為分片列；如果沒有主鍵，有唯一索引，我們以唯一索引列為分片列……以此類推。如果找到的鍵或索引是聯合主鍵或聯合索引，我取其中的第一列作為分片列。如果沒有找到任何合適的列作為分片列，則不分片，所有資料作一片進行拉取（無法享受併發拉取帶來的效率提升）。

首先要根據一定的規則選取某一列作為分片列，然後根據分片列的最大最小值，以及設定的每片大小，進行每一分片上下界的計算和確定：

1)獲取切分欄位的MIN()和MAX()

• "SELECT MIN(" + qualifiedName + "),
• MAX(" + qualifiedName + ") FROM (" + query + ") AS " + alias

2)根據MIN和MAX不同的型別採用不同的切分方式

• 支援有Date, Text, Float, Integer，Boolean, NText, BigDecimal等等。
• 以數字為例子：
• 步長=（最大值-最小值）/mapper個數
• 生成的區間為
• [最小值，最小值+步長)
• [最小值+步長，最小值+2*步長)
• ...
• [最大值-步長，最大值]
• 生成的condition類似：
• splitcol >= min and splitcol < min+splitsize

實現程式碼片段如下：

2.2 字串型別分片列

對於分片列型別為數字型別的情況，很好理解。

如果分片列型別為char/varchar等字串型別呢？每一片的上下界該如何計算？

原理還是一樣的：查出該列的最小、最大值，根據每片大小，計算每片分界點，生成每一片的上下界。

技術細節上不一樣的地方是：每片分界點/上下界的計算。

分片列型別為int，min 為2 ，max為10， shard size為3，分片很好理解：

Split[2,5)
Split[5,8)
Split[8,10]

如果分片列型別為varchar(128), min 為abc，max為 xyz，怎麼計算拆片點呢？

Sqoop的分片機制是透過將“字串”對映為“數字”，根據數字計算出分片上下界，然後將以數字表達的分片上下界對映回字串，以此字串作為分片的上/下界。如下所示：

• 字串對映為數值 （a/65536 + b/65536^2 + c/65536^3）
• 數值split 計算分割點，生成插值
• 插值對映回會字串

然而，在實際應用中，上述分片機制碰到各種問題，下面將我們碰到和解決這一系列問題的經驗分享如下。

三、分片經驗

3.1 首先，根據上面的分片進行資料的拉取，有卡死情況。

1)現象

• 無錯誤輸出，但全量抽取程式輸出一部分分片後卡死，無任何輸出
• 經過檢查，發現30秒後， storm worker被莫名其妙重啟了？

2)分析

• nimbus.task.timeout.secs的預設時間為30秒，nimbus發現worker無響應，就重啟動worker
• 為什麼worker無響應？
• 字串的插值是任意可能的，例如：
• splitcol >= ‘abc’ and splitcol < ‘fxxx’xx’

3)解決辦法

• 使用binding變數方式，而不是拼接字串方式
• Select * from T splitcol >= ？and splitcol < ？

3.2 更新後碰到新問題，報Illegal mix of collations異常。

1)現象

• 顯示exception：[ERROR] Illegal mix of collations (utf8_general -_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '<'
• java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '<‘

2)分析

• 什麼是Utf8和utf8mb4？
• utf8 是 Mysql 中的一種字符集，只支援最長三個位元組的 UTF-8字元
• 三個位元組的全部編碼空間： 000000~ 00FFFF
• MySQL在5.5.3之後增加了這個utf8mb4的編碼，mb4就是most bytes 4的意思，專門用來相容四位元組的unicode
• 四個位元組新增的編碼空間：010000~10FFFF
• 似乎生成了utf8mb4的碼的字串， splitcol和生成的插值字串，屬於不同的字符集,無法進行比較，Splitcol屬於utf8字符集，而插值屬於utf8mb4字符集

3)檢查發現

• character_set_server：utf8mb4
• character_set_database/table : utf8
• Connection url: utf8 = utf8mb4

4)Unicode

• 程式碼空間：總共有1,114,112個程式碼點，編號從0x0到0x10FFFF
• 程式碼平面：Unicode分成了17個程式碼平面（Code Plane），編號為#0到#16。每個程式碼平面65,536個程式碼點

5)UTF16

• 從U+0000至U+FFFF基本多語言平面（BMP）
• 包含了最常用的字元
• 實際字元需要除去代理區，也就是從U+0000至U+D7FF 和 U+E000 至U+FFFF。
• UTF8
• 從U+D800到U+DFFF的碼位（代理區）
• Unicode標準規定U+D800..U+DFFF的值不對應於任何字元

• 從U+10000到U+10FFFF的補充平面（Supplementary Planes）
• 在UTF-16中被編碼為一對16位元長的碼元（即32bit，4Bytes），稱作 code units called a 代理對（surrogate pair）
• 第一個WORD的高6位是110110，第二個WORD的高6位是110111。可見，
• 第一個WORD的取值範圍（二進位制）是11011000 00000000到11011011 11111111，即0xD800-0xDBFF。

• 第二個WORD的取值範圍（二進位制）是11011100 00000000到11011111 11111111，即0xDC00-0xDFFF。

• Emoji字元的例子：

• 對應Unicode 是\u1F601
• 對應的utf16 碼是2個word，即：0xd83d,　0xde01，對應java string length為2.

根據上述字符集只是，我們找到了問題癥結所在：

• bigDecimalToString()生成的插值:
• 無法保證是否會落入U+D800到U+DFFF的代理區
• 無法保證連續兩個word滿足代理對的標準，可能會被認定為亂碼
• 代理區間佔整個U+FFFF區間很小

6)解決方案

• 迴避生成在代理區的字元，用合法的BMP區字元替代
• if (0xD800 <= codePoint && codePoint <= 0xDFFF) {
• codePoint = 0xD3FF;
• }
• 可能的缺點是：分片不那麼均勻，但由於代理區佔整個U+FFFF區間很小，影響不大

↓↓↓  

3.3 拉取總數不對

解決字符集亂碼問題後，能正常拉取資料，但總數不對。

1)現象

• 沒有錯誤，全量抽取完成，但數量不對，整個表只有300萬，實際抽取了500萬？

2)分析

• 程式並沒有錯，存在重複資料
• utf8_genera_ci不區分大小寫，ci為case insensitive的縮寫，即大小寫不敏感
• utf8_bin將字串中的每一個字元用二進位制資料儲存，區分大小寫
• 例如：SELECT * FROM table WHERE txt = 'a'
• 那麼在utf8_bin中你就找不到 txt = 'A', 而 utf8_general_ci 則可以.

3)解決方案

• 應該使用utf8_bin進行查詢

類似： SELECT * FROM tableName WHERE binary columnName = 'a';

至此，對char、varchar型別字串分片列的分片，也有了很好的支援。

作者：尹宏春

來源：宜信技術學院