前言
随着线上业务的不断发展，不断扩张，DB中的数据量同时也在飞速增长。
无状态的web服务可以无限水平扩张不会成为瓶颈，而有状态且脆弱的DB却会成为业务发展的瓶颈点。我们遇到这种情况应该怎么解决它呢？
在项目发展过程中，我也遇到了这样的问题，线上MySQL中的数据表出现了这样一个大家伙。
为了保证线上业务能够正常响应，不得不对这个大表开开刀，于是就有了这次实践过程。
下面会按照以下三个步骤，来对这次分库分表进行完整叙述。
- 思考
- 方案选型
- 落地
一. 为什么要拆分
1. 磁盘IO次数增加
为了保证数据的持久性，最终都要将数据落入到磁盘中，那么表中的数据量过大以后会存在什么样的问题呢？
先一起从磁盘的数据读取 & B+树的查找来完整回忆一下，一次查找数据的过程是怎样的。
1.1 一次磁盘IO
机械硬盘硬盘具有价格便宜，耐用，空间大的优点，在性能要求不那么高的情况下，多数场景下还是会选择它。
本节就拿机械硬盘的数据读取过程来进行举例说明，首先看一下各类磁盘随机IO性能指标
可以看到随机IO时，HDD的IOPS非常低，io延迟需要大概8ms。
那么为什么一次随机io需要这么多时间呢，其实磁盘的响应时间由四部分组成：
- IO排队时间
- 寻址时间
- 磁盘旋转时间
- 数据传输时间
其中，主要耗时的部分就是2和3。寻址时间一般是几个毫秒，而磁盘的旋转时间和硬盘的转动速度RPM有关，平均下来也需要几毫秒。
由此可见每多一次io交互，成本就多了很多。
故MySQL设计的理念也是能用内存就用内存，尽量减少和磁盘的交互
1.2 B+树的结构
再来回顾一下Innodb使用的B+树的结构是什么样
B+树结构
B+树是一个N叉树，非叶子节点仅存储索引数据项，叶子结点存储完整数据（如果是二级索引存储的是主键索引字段）
为了便于进行范围查询，非根节点的每一层的结点之间存在指针，形成一个双向链表。
1.3 B+树的查找过程
这里假设我们要查找图中5这项数据，那么数据流程就应该是：
tips：通过索引查找定位数据的算法是二分法
- 读取根节点，判断出，下一个加载的节点是
page 5 - 读取
page 5，判断数据在page 8上， - 读取
page 8，在通过页上的索引数据，定位到5这个数据所在的位置 - 读取数据后返回
通过上述流程我们可以知道，查找数据的过程，是从树的根节点一层一层的向下查找，一直找到到叶子节点上，进行数据定位读取。
如果要找的page不在buffer pool中，就需要和磁盘进行一次io，把page读到内存中。
1.4 为什么说2000w数据是分水岭？
相信很多伙伴们在面试的时候都被问过这样一个问题，那么为什么会有这个说法呢，我们来分析一下。
首先说明，B+树的树高取决于三个因素，带着这三个因素继续向下看
- page的大小
- 非叶子节点中索引记录的大小
- 完整数据记录的大小
innodb中默认每个page的大小是16kb，在MySQL5.7版本及之后，page的大小可以设置大于16k了，详细见官方文档 。
每个页会存一些元数据信息大概128字节。 这些我们都先忽略，直接按照16kb去算，但是脑海里要知道实际能用的空间要比16k小一些。
假设每个非叶子节点（page）的空间中 每个的索引记录大小 = 索引字段大小 + 叶子节点page指针大小
其实每个user record空间中的记录中也会记录一些额外的信息，这里也先忽略
假设主键为bigint 8字节，那么指针innodb中6字节，一条记录算占14字节
(16k x 1024) / 14b = 1170
所以两层树高（一层非叶子节点，一层叶子节点）时，能容纳的索引数据量为1170，即能够挂载的叶子节点数量为1170。
1170 * 1170 = 1368900
三层树高（两层非叶子节点，一层叶子节点）能容纳的索引数据量为1368900
这个1368900也就是能够挂载的叶子节点（page）指针的数量，假设每条完整记录占用1kb，每个叶子节点的page能容纳16条记录，可得：
1368900 * (16k / 1k) = 21902400
就是说当树高是三层时，能容纳数据量的上限是2000w左右。
假如每条完整记录占2k，那么三层树能容纳的数据量就是1000w左右了。
从上述推论中可知，树高取决于三个因素
- page的大小
- 索引字段 + 指针的大小
- 完整数据记录的大小
1和2共同决定了非叶子节点page能存储记录的N，也就是能挂载的叶子节点page数量的上限。
3决定了每个叶子节点page能够记录完整数据的条数。
小结一下：
通过假定计算，当树高为三层时，可以预估出存储上限为2000w左右，再多就会变成四层，也就会多一次磁盘io。
而我们在进行查询时，多数情况用到的不是主键索引，而是二级索引，这样就需要再回表通过主键索引进行查询，可能多出来的io就不止一次了。
那么为了避免这种情况发生，我们就需要考虑进行分库分表了。
2. DDL操作困难
当我们有了一个新的需求，需要对大表进行增加字段时，这个DDL操作就是一个非常困难的事情了。
下面讲述一下几种DDL操作流程和存在的问题
2.1 MySQL 5.6之前
具体流程如下
- 建一个新表A
- 从表B按照主键id递增的顺序，逐行取出数据插入表A中
- 表A替换表B
- 删除旧表
DDL操作还不是online的，在步骤2操作期间的写入都会丢失，因此在步骤2的时候表中不能有更新。
2.2 MySQL5.6及之后
引入了online DDL，流程如下
- 建立临时文件，扫描表A主键的所有数据页
- 用数据页表A的记录生成B+树，存储到临时文件中
- 生成临时文件过程中，将对A的操作记录在一个日志文件row log中
- 临时文件生成后，将日志文件操作应用到临时文件，即得到一个和表A相同的数据文件
- 临时文件替换表A的数据文件
可以看到第3步中，通过row log的方式将数据复制期间的操作记录下来，再和全量数据进行合并后，即得到一份完整数据。
但是在做DDL之前，语句需要先拿到MDL写锁，假如这个表的操作很频繁，就会一直拿不到这个锁。
而DDL操作还会一直阻塞后续的DML语句，如果业务端存在超时重试的话，重试再建立一个新的请求，这个库的线程很快就会爆满。
虽然mariaDB支持了DDL NOWAIT/WAIT n的语法可以进行一个等待时间的限定，但是innodb还是不可以的，会发生一直拿不到锁block住后续DML操作的情况。
2.3 pt工具
我所在的公司对于数据表增加字段的场景，会使用pt工具进行修改，具体流程如下
- 创建一个原始表A一样结构的空表表B，后缀默认是new
- 在表B执行alter table 语句
- 在表A中创建触发器3个触发器分别对应insert,update,delete操作。
- 以一定块大小从原表拷贝数据到临时表
- 将表A 表B rename交换表名
- 默认最后将旧原表删除
这种方式和MySQL5.6之前的操作有点类似，但是通过触发器的形式，将拷贝过程中表A进行的写操作更新到新建的表B（Replace操作）
这种方案解决了数据丢失和MDL锁抢占的问题，不过数据量和耗时成正比，随着数据量不断增长，时间也会越来越长。
曾经提工单给大表加字段，加了好几个小时
3. 分库分表的目的
通过上述章节，了解了表数据过大以后存在着「磁盘io增加」和「DDL操作困难」的问题。
在进行数据拆分前，我们来看一下分库分表的目的都是什么，再来看是否真的既需要分库，又需要分表。
分表是为了什么？
分表是要对数据表进行一个水平拆分，通过降低单表数据量的方式，来降低CURD时所耗费的时间。
当然上面也讲过，三层树能存储的数据量同样与单条记录的大小有关。
当单表字段过多时，可以先进行一个表的垂直拆分，将一些非强关联字段拆分到另外一个表中，通过id进行关联。以这种方式提升能够存储数据的上限。
分库是为了什么？
每个MySQL单实例能够承受的并发量都是有限的，如果在进行数据拆分的同时，我们也想去提高能够接收请求的并发量，这时就同时需要用到分库的手段了。
如果并发量不是瓶颈问题，可以仅做分表，后续有需要再进行分库
二. 方案选择
1. 选择分片键
数据表拆分后，都需要一个路由规则来进行分表位置的寻找，这个路由规则中也就需要用到我们的分片键。
分片键要结合使用场景来选择确定，举例说明
- 电商的订单使用订单号查询，那么就需要用订单号hash取模拆分
- 用户表以用户id进行查询，就通过用户id通过hash取模拆分
- 报表表以日期进行查询，可以通过日期字段range拆分
下面会结合分片算法，来进行具体的场景示例。
2. 选择分片算法
2.1 hash取模
- 301 % 4 = 1 即存入t_order_1
- 302 % 4 = 2 即存入t_order_2
如图所示，即通过分片键对分表后的总数量进行取模运算，通过余数来确定应该存哪张表。
而确定要分多少张表，需要提前对数据量进行一个预估
例如当前数据量1600w，数据的增长速度是40w每月，希望拆分三年内每张表数据量不超过1000w。
增量数据：40w * 36 = 1440w
总数据量：1440 + 1600 = 3040w
我们再预留出来一些增长buffer，就可以分成四张表。
优点
数据和请求会均匀的打散到每张表中，不存在单表热度过高的问题。
热点问题，即多数的请求都打到了同一张表中，其他表处理的请求很少
缺点
由于分表公式为分片id % 总表数，表的数量直接会影响到数据请求的分发，在二次扩容时操作比较复杂。
2.2 range
range即根据分片键区间进行分表
例如：
- 以id为分片键，id 1~1000w一个表，1000w+1 ~ 2000w一个表
- 以日期为分片键，每个月或者每天一个表
优点
- 能够自然的控制每个分表的数据量大小，有利于后续扩容
- 对访问方式友好，SQL根据分片键的范围，能快速确定分表位置
缺点
根据局部性原理可知存在热点问题，最近产生的数据会被访问的可能性更高，也就是说热点都会集中在最近的几张表中。
适合数据量大，但是访问量不大的情况，例如B端系统。
2.3 查表法
举个比较经典的例子就是redis cluster的slot设计，放到我们MySQL表拆分也同样适用。
通过引入一层虚拟的slot，建立slot与实际机器的关联，来避免机器扩容/缩容时产生的影响。
操作数据时，先根据slot分配的元数据信息来确定目标实例位置，再进行实际的操作。
优点
分表方式和扩容更加灵活
缺点
需要二次查询，实现会复杂一些，性能方面可以通过缓存来避免
需要控制分表信息的元数据大小，避免成为瓶颈
2.4 hash取模 + 查表法 + range
这种方式是设定一个逻辑group，根据range范围确定group，hash取模确定表名，再通过查表法确定表所在的库，流程有点复杂，我来举个例子
- 假设group0存储id
0 ~ 6000w的数据，group1存储id6000w+1 ~ 12000w的数据 - 每个六个表，每个表存储1000w的数据
- group0的表和库的分布元数据就按图中的来
- 操作的数据id为10000002
那么流程就是
- 根据id判断group在group0的存储中
- 10000002 % 6 = 0 得出表名为t_order_0
- 查表法得知t_order_0在db0中
- 最终确定数据在db0的t_order_0中
这里库和表的对应关系之所以使用了查表法，是因为有可能各台机器的性能不一样，性能高的就多分配一些表，这样通过查表法可以灵活的进行分配。
优点
- 数据通过hash方式平均的被打散到各个db的表中，解决了热点问题
- 数据超过一个group的容纳量以后，只需要在添加一个group即可，扩容很方便
缺点
整个方案实现起来复杂度很高
3. 选择技术方案
3.1 smart client
将实际分库分表的逻辑封装在sdk当中，通过使客户端接入sdk，保持对调用透明。
这个方式是比较常用的一种方式，但是存在的问题是和语言强关联，每个语言都要有一份sdk。
可选方案
- tddl
- sharding-jdbc
- zebra
3.2 proxy
顾名思义，就是在数据库和客户端之间引入一个中间人做proxy。
对业务端代码零侵入，只需要修改一下db的host，由proxy负责分库分表的操作以及聚合select查询结果。
proxy内部具体流程为
- 根据配置文件确定分表路由规则
- SQL解析成语法树
- 根据路由规则和语法树，得到改写的sql
- 并行执行改写sql
- 合并结果集
缺点
- 增加了网络链路的长度，耗时会上升
- 需要保证HA
- 对SQL语法支持不是百分百
优点
- 对客户端零侵入，完全透明
- 功能强大，支持权限检测，读写分离，监控报警等
可选开源组件
- mysql-proxy
- cobar
- altas
- mycat
- sharding-proxy
4. 方案确定
我的业务场景是数据量大但是访问量不大的B端系统，需要拆分的是一个根据时间查询的报表表。
所以最终选择了range的分表算法，没有进行分库，所有分表在同一个单独的库中。
由于当时使用的是php作为开发语言，没什么可用的sdk，自己从0开发成本太高，smart client方案被pass掉了。
通过对比功能、迭代速度、社区活跃度等方面的因素，最终决定选用sharding-proxy.
这里对各个组件的评估不再详细展开，感兴趣的可以自行查看下
三. 实际落地
1. 结构搭建
选定了使用proxy的方式后，进行整体结构的设计，如下图所示
由于公司不提供四层的负载均衡，这里用openresty搭建了两个反向代理节点，域名使用A记录的方式进行了绑定。
两个proxy节点负责实际分库分表工作
proxy后面就是公司提供的mysql集群了，集群内部由dba同学进行运维保障。
2. 效果测试
测试机器
1台物理机(10.136.199.209) 32核(E5,2.4ghz) 128g内存 千兆网卡,CentOS6.6 glibc2.12 load average: 0.32, 0.44, 0.41
测试工具
sysbench(1.1.0)
case1
每张10w行表10张,32个线程随机读写
/home/work/ben/sysbench/bin/sysbench /home/work/ben/sysbench/share/sysbench/oltp_read_write.lua --mysql-user=${user} --mysql-password=${password} --mysql-host=${host} --mysql-port=${prot} --mysql-db=${databaseName} --threads=32 --time=120 --report-interval=5 --db-ps-mode=disable --tables=10 --table_size=100000 run
case1结果
SQL statistics:
queries performed:
read: 152922
write: 43692
other: 21846
total: 218460
transactions: 10923 (90.74 per sec.)
queries: 218460 (1814.85 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 90.7426
time elapsed: 120.3735s
total number of events: 10923
Latency (ms):
min: 238.92
avg: 352.06
max: 843.11
95th percentile: 493.24
sum: 3845572.04
Threads fairness:
events (avg/stddev): 341.3438/34.48
execution time (avg/stddev): 120.1741/0.09
网卡IO:
em1 docker0 tunl0 tunnat
KB/s in KB/s out KB/s in KB/s out KB/s in KB/s out KB/s in KB/s out
2801.45 2761.01 0.00 0.00 0.00 0.00 0.00 0.00
2941.96 2923.37 0.00 0.00 0.00 0.00 0.00 0.00
2525.08 2506.50 0.00 0.00 0.00 0.00 0.00 0.00
2567.90 2550.28 0.00 0.00 0.00 0.00 0.00 0.00
2442.06 2420.66 0.00 0.00 0.00 0.00 0.00 0.00
CPU:
load average: 6.61, 7.37, 10.12
case 2
每张10w行表10张,128个线程随机读写
/home/work/ben/sysbench/bin/sysbench /home/work/ben/sysbench/share/sysbench/oltp_read_write.lua --mysql-user=${user} --mysql-password=${password} --mysql-host=${host} --mysql-port=${prot} --mysql-db=${databaseName} --threads=128 --time=120 --report-interval=5 --db-ps-mode=disable --tables=10 --table_size=100000 run
case 2结果
SQL statistics:
queries performed:
read: 191786
write: 54796
other: 27398
total: 273980
transactions: 13699 (113.56 per sec.)
queries: 273980 (2271.20 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 113.5601
time elapsed: 120.6321s
total number of events: 13699
Latency (ms):
min: 515.84
avg: 1124.46
max: 2109.86
95th percentile: 1376.60
sum: 15403925.46
Threads fairness:
events (avg/stddev): 107.0234/4.24
execution time (avg/stddev): 120.3432/0.17
网卡IO:
em1 docker0 tunl0 tunnat
KB/s in KB/s out KB/s in KB/s out KB/s in KB/s out KB/s in KB/s out
3525.66 3590.34 0.00 0.00 0.00 0.00 0.00 0.00
3439.98 3518.39 0.00 0.00 0.00 0.00 0.00 0.00
3872.25 3946.87 0.00 0.00 0.00 0.00 0.00 0.00
3664.53 3750.76 0.00 0.00 0.00 0.00 0.00 0.00
3503.96 3565.15 0.00 0.00 0.00 0.00 0.00 0.00
CPU:
load average: 39.87, 16.19, 12.70
测试总结
基准测试是对单张表进行的读写测试，单纯体现的是数据库硬件性能。
从结果数据可以看出,对比两次32线程与128线程随机读写测试结果发现,带宽占用上升不明显,tps/qps上升不明显,在1700(w=1200,r=500)~2100(w=1600,r=500)之间,而cpu剧烈飙升5.0->38.0,95%请求时长随之飙升400ms->1300ms。
从而可以推断出正常使用情况下该代理承载qps2000左右,cpu load在1.0-5.0之间波动为合理使用情况。
其他同学想要进行业务测试的话，可以使用mysqlslap这个工具
业务场景不同，结果差异也会很大，所以这里没有贴出我的业务测试结论。
四. 数据迁移
在对线上表数据从旧表迁移到新表的过程中，最重要的就是要做到用户无感知，不能影响线上业务。
所以其中任何一步出现问题的情况下，能够进行回滚。
1. 准备工作
在进行数据迁移之前，先把流程中需要的一些工具进行设计准备，具体有以下几个。
1.1 同步程序
同步程序的目的是把旧库全量数据同步到新库，并保证增量数据也不断同步至新库。
1.2 写开关
写开关，分为三种状态
- 写旧库
- 双写
- 写新库
1.3 读开关
读开关，分为三种状态
- 全读旧库
- 按比例部分读新库
- 全读新库
可以设置为一个0-100的数字，0走第一种，100走第3种，其余按照百分比走新库
1.4 补偿脚本
用于在停掉同步程序后，通过脚本的方式校验新旧两库中的数据一致性。
我拆分的表数据时间性强，写入之后基本不会变动，校验比较好做。
如果场景是更新操作频繁的情况，可以滚动固定大小的时间窗口数据，进行对比补偿，补偿方式直接用旧库数据去覆盖新库。
2. 迁移步骤
- 准备数据同步程序，保证旧库全量数据同步到新库
- 上线双写开关（先写旧库，再写新库，新库失败记录日志，不影响流程）
- 同时停止同步程序
- 校验数据，进行脚本补偿
- 保持数据同步后，逐步灰度读至全量
- 停止写旧库，下掉旧库
按照上述六个步骤进行了平滑迁移，切记每个步骤都需要观察一段时间，确定没有问题后再进行下一个步骤。
总结
本文讲述了当线上MySQL数据表不断膨胀过大时，会带来哪些问题。
为了解决这些问题，从拆分原因的思考，到拆分的方案的选择，再到最后实际落地的一次完整实操。
其实为了解决MySQL内部的问题，而引入一个中间件去解决它内部的问题，其实是不太优雅和合理的一种解决方案。
这里借用左耳朵耗子叔文章中的一段话
真正解决数据结点调度的方案应该是底层的数据结点。在它们上面做这个事才是真正有效和优雅的。而像阿里的用于分库分表的数据库中间件 TDDL 或是别的公司叫什么 DAL 之类的这样的中间件都会成为过渡技术。
在后续工作中，我将另外一个大表进行了迁移TiDB中，下篇文章也会围绕着TiDB的迁移思考，进行一次总结。
ps：如果有哪里叙述的不清晰，逻辑有问题，流程有疑点，欢迎大家留言和我进行探讨。
如果觉得文章对你有帮助，希望能留下一个赞，感谢！
