MySQL分库分表

MySQL分库分表

什么是分库？

分库 就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

垂直分库 就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

举个例子：说你将数据库中的用户表、订单表和商品表分别单独拆分为用户数据库、订单数据库和商品数据库。

水平分库 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

举个例子：订单表数据量太大，你对订单表进行了水平切分（水平分表），然后将切分后的 2 张订单表分别放在两个不同的数据库。

什么是分表？

分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

垂直分表 是对数据表列的拆分，把一张列比较多的表拆分为多张表。

举个例子：我们可以将用户信息表中的一些列单独抽出来作为一个表。

水平分表 是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分只能解决单表数据量大的问题，为了提升性能，我们通常会选择将拆分后的多张表放在不同的数据库中。也就是说，水平分表通常和水平分库同时出现。

什么情况下需要分库分表？

遇到下面几种场景可以考虑分库分表：

• 单表的数据达到千万级别或者表容量2GB以上（也看每行数据的字节数），数据库读写速度比较缓慢。
• 数据库中的数据占用的空间越来越大，备份时间越来越长。
• 应用的并发量太大（应该优先考虑其他性能优化方法，而非分库分表）。

不过，分库分表的成本太高，如非必要尽量不要采用。而且，并不一定是单表千万级数据量就要分表，毕竟每张表包含的字段不同，它们在不错的性能下能够存放的数据量也不同，还是要具体情况具体分析。

之前看过一篇文章分析 “InnoDB 中高度为 3 的 B+ 树最多可以存多少数据”，写的挺不错，感兴趣的可以看看。

常见的分片算法有哪些？

分片算法主要解决了数据被水平分片之后，数据究竟该存放在哪个表的问题。

常见的分片算法有：

• 哈希分片：求指定分片键的哈希，然后根据哈希值确定数据应被放置在哪个表中。哈希分片比较适合随机读写的场景，不太适合经常需要范围查询的场景。哈希分片可以使每个表的数据分布相对均匀，但对动态伸缩（例如新增一个表或者库）不友好。
• 范围分片：按照特定的范围区间（比如时间区间、ID 区间）来分配数据，比如 将 id 为 1~299999 的记录分到第一个表， 300000~599999 的分到第二个表。范围分片适合需要经常进行范围查找且数据分布均匀的场景，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题）。
• 一致性哈希分片：将哈希空间组织成一个长度为2^32的环形哈希空间，将分片键和节点（数据库或表）都映射到这个环上，然后根据顺时针的规则确定数据或请求应该分配到哪个节点上，解决了传统哈希对动态伸缩不友好的问题。
• 地理位置分片：很多 NewSQL 数据库都支持地理位置分片算法，也就是根据地理位置（如城市、地域）来分配数据。
• 映射表分片：使用一个单独的表（称为映射表）来存储分片键和分片位置的对应关系。映射表分片策略可以支持任何类型的分片算法，如哈希分片、范围分片等。映射表分片策略是可以灵活地调整分片规则，不需要修改应用程序代码或重新分布数据。不过，这种方式需要维护额外的表，还增加了查询的开销和复杂度。
• 融合算法分片：灵活组合多种分片算法，比如将哈希分片和范围分片组合。

分片键如何选择？

分片键（Sharding Key）是数据分片的关键字段。分片键的选择非常重要，它关系着数据的分布和查询效率。一般来说，分片键应该具备以下特点：

• 具有共性，即能够覆盖绝大多数的查询场景，尽量减少单次查询所涉及的分片数量，降低数据库压力；
• 具有离散性，即能够将数据均匀地分散到各个分片上，避免数据倾斜和热点问题；
• 具有稳定性，即分片键的值不会发生变化，避免数据迁移和一致性问题；
• 具有扩展性，即能够支持分片的动态增加和减少，避免数据重新分片的开销。

实际项目中，分片键很难满足上面提到的所有特点，需要权衡一下。并且，分片键可以是表中多个字段的组合，例如取用户 ID 后四位作为订单 ID 后缀。

分库分表有没有什么比较推荐的方案？

Apache ShardingSphere 是一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。

ShardingSphere 项目（包括 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar）是当当捐入 Apache 的，目前主要由京东数科的一些巨佬维护。

ShardingSphere 绝对可以说是当前分库分表的首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理、影子库、数据加密和脱敏等功能。

ShardingSphere 提供的功能如下：

ShardingSphere 的优势如下（摘自 ShardingSphere 官方文档：https://shardingsphere.apache.org/document/current/cn/overview/）

• 极致性能：驱动程序端历经长年打磨，效率接近原生 JDBC，性能极致。
• 生态兼容：代理端支持任何通过 MySQL/PostgreSQL 协议的应用访问，驱动程序端可对接任意实现 JDBC 规范的数据库。
• 业务零侵入：面对数据库替换场景，ShardingSphere 可满足业务无需改造，实现平滑业务迁移。
• 运维低成本：在保留原技术栈不变前提下，对 DBA 学习、管理成本低，交互友好。
• 安全稳定：基于成熟数据库底座之上提供增量能力，兼顾安全性及稳定性。
• 弹性扩展：具备计算、存储平滑在线扩展能力，可满足业务多变的需求。
• 开放生态：通过多层次（内核、功能、生态）插件化能力，为用户提供可定制满足自身特殊需求的独有系统。

另外，ShardingSphere 的生态体系完善，社区活跃，文档完善，更新和发布比较频繁。

不过，还是要多提一句：现在很多公司都是用的类似于 TiDB 这种分布式关系型数据库，不需要我们手动进行分库分表（数据库层面已经帮我们做了），也不需要解决手动分库分表引入的各种问题，直接一步到位，内置很多实用的功能（如无感扩容和缩容、冷热存储分离），如果公司条件允许的话，个人也是比较推荐这种方式！

分库分表后，数据怎么迁移呢？

分库分表之后，我们如何将老库（单库单表）的数据迁移到新库（分库分表后的数据库系统）呢？

停机迁移

比较简单同时也是非常常用的方案就是停机迁移，写个脚本老库的数据写到新库中。比如你在凌晨 2 点，系统使用的人数非常少的时候，挂一个公告说系统要维护升级预计 1 小时。

1. 写一个脚本或使用数据迁移工具将老库的数据都同步到新库中
2. 将数据源切换到新库
3. 服务验证，验证通过后对外提供服务

数据迁移可能会设计新旧库地段不一致的情况，分为以下两种：

单库切换到单库

适合源库与目标库的表字段可以一对一映射，无需额外的开发工作量，这个时候代码层不用改造。ETL工具有很多
种:Flink CDC、Canal、DataX，目前比较主流的是Flink CDC，可以全量同步也可以增量同步。

单库切换到分片库

适合源库与目标库的表字段无法一对一映射，这个时候代码层需要改造，应用侧消费数据后，对数据进行加工处
理，写入目标库。建该在晚上12点以后低峰期操作，另外kafka处理数据的线程不要开的太大。

双写方案

MySQL亿级数据平滑迁移实战 - 简书 (jianshu.com)

1. 对写入测代码进行改造，我们对老库的更新操作（增删改），同时也要写入新库（双写）。
2. 使用数据库同步工具将老库的全量数据+增量数据同步到新库中。全量+增量同步时应选择不对外提供服务的离线从库作为数据源，避免主从延迟等问题对线上业务造成影响。
3. 当新库的进度追上老库后，进行一致性校验，此外还需要开启新老库查询结果对比开关，通过日志监控观察新老库的查询结果是否一致。
4. 关闭数据库同步工具，开启双写配置开关，停止数据同步和切换双写之间必然有时间差，如果先开启双写再停止数据同步，则可能出现插入重复数据或数据被覆盖的情况。所以这里选择先停止同步，再切换到双写，中间丢失的数据使用对比&补偿任务恢复
   • 先写老库再写新库：业务是否成功只取决于写老库是否成功，也就是说写老库成功但是写新库失败时业务侧仍应视为成功，不过要记录一条失败日志。
   • 写新库应该是异步的：保证不影响业务的响应时间。
5. 开启对比和补偿程序，补偿切换开关的过程中遗失的数据
6. 观察双写结果是否一致，确保一致后逐步切量读请求到新库
7. 关闭双写，读写都切换到新库
8. 开启反向补偿任务。反向补偿是从新库补偿数据到老库，由于该任务是定时执行，开启后，新库和老库的数据会有 1~2 分钟的延迟，万一写新库的逻辑有问题，可以切回老库。

数据库同步工具

想要在项目中实施双写还是比较麻烦的，很容易会出现问题。我们可以借助上面提到的数据库同步工具 Canal 做增量数据迁移，开发和维护成本较低。

数据一致性如何保证

通过对账程序自动对账，同时配置人工告警，防止对账失败，人工介入。具体步骤如下:

1. 考虑到数据量很大，我们通过大数据平台hive，创建对账任务，每天下半夜的凌晨1点对账新库、旧库表数据不一致，写入异常数据到差异表。同时配置人工告警，通知相关的研发人员
2. 通过ETL同步差异表数据到mysql库差异(一般差异表数据量比较小)
3. 应用侧通过定时任务定时读取mysql库差异表，更新新库
4. 持续自动对比数据，发现不一致，人工介入，及时定位原因修复上线，直到数据最终一致
5. 打开开新库开关，启用新库，下线旧库和相关旧代码