关系型数据库全表扫描分片详解

共计 4168 个字符，预计需要花费 11 分钟才能阅读完成。

本文从数据分片的角度出发，具体介绍 DBus 在数据采集的过程中，运用了什么样的分片策略和分片原理，以及过程中遇到的问题及解决方案。

导读：数据总线（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 类型，借鉴、应用的过程中发现一些问题，我们进行了一些调整和优化。

本文主要和大家分享一下遇到的坑和我们的解决办法。

让我们先以最简单、明了的数字类型分片列为例介绍分片原理。

如前所述，我们会按照主键 -> 唯一索引 -> 索引的优先级确定分片列。如果表有主键，我们以主键列为分片列；如果没有主键，有唯一索引，我们以唯一索引列为分片列……以此类推。如果找到的键或索引是联合主键或联合索引，我取其中的第一列作为分片列。如果没有找到任何合适的列作为分片列，则不分片，所有数据作一片进行拉取（无法享受并发拉取带来的效率提升）。

首先要根据一定的规则选取某一列作为分片列，然后根据分片列的最大最小值，以及设定的每片大小，进行每一分片上下界的计算和确定：

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

实现代码片段如下：

对于分片列类型为数字类型的情况，很好理解。

如果分片列类型为 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 计算分割点，生成插值
插值映射回会字符串

然而，在实际应用中，上述分片机制碰到各种问题，下面将我们碰到和解决这一系列问题的经验分享如下。

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 <？

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 区间很小，影响不大

↓↓↓

解决字符集乱码问题后，能正常拉取数据，但总数不对。

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 类型字符串分片列的分片，也有了很好的支持。

作者：尹宏春

来源：宜信技术学院

一、分片策略

二、分片原理

2.1 数字类型分片列

2.2 字符串类型分片列

三、分片经验

3.1 首先，根据上面的分片进行数据的拉取，有卡死情况。

3.2 更新后碰到新问题，报 Illegal mix of collations 异常。

3.3 拉取总数不对

Just My Socks（注册教程内含优惠码）

关系型数据库全表扫描分片详解

一、分片策略

二、分片原理

2.1 数字类型分片列

2.2 字符串类型分片列

三、分片经验

3.1 首先，根据上面的分片进行数据的拉取，有卡死情况。

3.2 更新后碰到新问题，报 Illegal mix of collations 异常。

3.3 拉取总数不对

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）