关系型数据库全表扫描分片详解-wx5c2da66615f74的博客

导读：数据总线（DBus）专注于数据的实时采集与实时分发，可以对IT系统在业务流程中产生的数据进行汇聚，经过转换处理后成为统一JSON的数据格式（UMS），提供给不同数据使用方订阅和消费，充当数仓平台、大数据分析平台、实时报表和实时营销等业务的数据源。

本文从数据分片的角度出发，具体介绍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 首先，根据上面的分片进行数据的拉取，有卡死情况。

• 无错误输出，但全量抽取进程输出一部分分片后卡死，无任何输出
• 经过检查，发现30秒后， storm worker被莫名其妙重启了？

• 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异常。

• 显示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 '<‘

• 什么是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。
• 从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 拉取总数不对

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

• 没有错误，全量抽取完成，但数量不对，整个表只有300万，实际抽取了500万？

• 程序并没有错，存在重复数据
• 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类型字符串分片列的分片，也有了很好的支持。

作者：尹宏春

来源：宜信技术学院