2、mysql数据分库表实践

当我们聊数据存储方案改进的时候，我们应该考虑哪些问题？

• 我们为什么要分库表？（结合实际业务场景，当前数据体量B+树索引是否能满足查询性能要求，当前数据库的架构还能否满足可用性要求，DB数据超过1TB的情况下数据备份和恢复的运维成本）
• 分库表应该采用什么策略？（垂直切分、水平切分的维度，和CRUD数据路由的考量）
• 如何进行分库表的平滑迁移？（dba处理&业务应用处理&其他方案）
• 数据分库表后会带来哪些使用上的问题？（配置方面、sql语法方面[分页、排序、范围、聚合、分组、跨节点join等查询]、本地事务变跨库分布式事务）

我们为什么要分库表？

1、实际业务中的考虑

        当前某业务服务系统Mysql数据存量：单表 A产品线1000w+B产品线8000w，数据增量大概在4w/pd；该数库另一表单各产品线累计10亿的存量数据，增量在10w/pd；数据DB采用跨机房QMHA架构，本身是能满足一定量级的读写性能要求的；但是实际业务中，作为一张用户基础热点数据表，它的查询流量总是指数级的增长的；以数据库 单表慢查询量、查询耗时、数据体量(单一数据库实例的数据的阈值在 1TB 之内，是比较合理的范围)做判定依据，so....

 2、性能&可用性&运维成本考虑

传统的将数据集中存储至单一数据节点的解决方案，在性能、可用性和运维成本这三方面已经难于满足互联网的海量数据场景。

• 查询性能：mysql关系型数据库采用的是B+作为索引结构，在数据量超过阈值的情况下，索引深度的增加也将使得磁盘访问的 IO 次数增加，进而导致查询性能的下降。
• 可用性：从可用性的方面来讲，服务化的无状态型，能够达到较小成本的随意扩容，这必然导致系统的最终压力都落在数据库之上。而单一的数据节点，或者简单的主从架构，已经越来越难以承担。数据库的可用性，已成为整个系统的关键。
• 运维成本：当一个数据库实例中的数据达到阈值以上，对于 DBA 的运维压力就会增大。数据备份和恢复的时间成本都将随着数据量的大小而愈发不可控。一般来讲，单一数据库实例的数据的阈值在 1TB 之内，是比较合理的范围。

结论：

       通过分库和分表进行数据的拆分来使得各个表的数据量保持在阈值以下，以及对流量进行疏导应对高访问量，是应对高并发和海量数据系统的有效手段。分库表其实就是数据分片，指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中以达到提升性能瓶颈以及可用性的效果。数据分片的有效手段是对关系型数据库进行分库和分表。

如何进行分库表？

数据分片的拆分方式又分为垂直分片和水平分片。

1、垂直拆分

垂直拆分是一种按照业务拆分的方式，它的核心理念是专库专用、专表专用；在拆分之前，一个数据库包含多个业务的数据表or一个业务表对应着不同的子业务；而拆分之后，则是按照业务将表进行归类，分布到不同的数据库或者数据表中，从而将压力分散至不同的数据库or数据表。

垂直拆分的优点：

• 库表职责单一，复杂度降低，易于维护。
• 单库或单表压力降低。 相互之间的影响也会降低。

垂直拆分的缺点：

• 部分表关联无法在数据库级别完成，需要在程序中完成。
• 单表大数据量仍然存在性能瓶颈。
• 单表或单库高热点访问依旧对 DB 压力非常大。
• 事务处理相对更为复杂，需要分布式事务的介入。
• 拆分达到一定程度之后，扩展性会遇到限制。

2、水平拆分

又称横向拆分，不再是通过业务逻辑进行拆分了，而是通过某个字段（或某几个字段），根据某种规则将数据分散至多个库或表中，每个分片仅包含数据的一部分。

水平拆分的优点：

• 解决单表单库大数据量和高热点访问性能遇到瓶颈的问题。
• 应用程序端整体架构改动相对较少。
• 事务处理相对简单。
• 只要切分规则能够定义好，基本上较难遇到扩展性限制。

水平拆分缺点：

• 拆分规则相对更复杂，很难抽象出一个能够满足整个数据库的切分规则。
• 后期数据的维护难度有所增加，人为手工定位数据更困难。
• 产品逻辑将变复杂。比如按年来进行历史数据归档拆分，这个时候在页面设计上就需要约束用户必须要先选择年，然后才能进行查询。

总结：

• 数据库垂直拆分，业务功能拆分，解决的是单库的业务复杂度（例如，订单表和用户表分库）；数据表的垂直拆分，解决的是单表的业务复杂度；
• 数据库水平拆分，解决单库性能问题；数据表水平分表，解决单表大数据量问题；

分片键如何选择？

分库分表后，分片键的选择非常重要。一般来说是这样的：

• 信息表，使用 id 进行分片。例如说，文章、商品信息等等。
• 业务表，使用 user_id 进行分片。例如说，订单表、支付表等等。
• 日志表，使用 create_time 进行分片。例如说，访问日志、登陆日志等等。

选择好分片键之后，还需要考虑分片算法。一般来说，有如下两种：

• 取余分片算法。例如说，有四个库，那么 user_id 为 10 时，分到第 10 % 4 = 2 个库。
  • 当然，如果分片键是字符串，则需要先进行 hash 的方式，转换成整形，这样才可以取余。
  • 当然，如果分片键是整数，也可以使用 hash 的方式。
• 范围算法。
  • 例如说，时间范围。

上述两种算法，各有优缺点。

对于取余来说：

• 好处，可以平均分配每个库的数据量和请求压力。
• 坏处，在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表。（所以为了避免扩容要一次性分配相对充足的库表量）

对于 range 来说：

• 好处，扩容的时候很简单，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了。
• 缺点，但是大部分的请求，都是访问最新的数据。实际生产用 range，要看场景。

用了分库分表之后，有哪些常见问题？

1、查询数据应该从哪个库、哪个表中去获取

这个问题其实分两种情况，一种情况是查询的是分库分表键的数据，一种查询的不是分库表键的数据；

分库表键数据：则按照分片算法进行库表路由

非分库表键数据：例如，我们按照userId去进行库表切分，但某些场景却只使用orderNo订单号获取数据；

（1）、这个时候我们可以维护一张映射表，包含user_id和 orderNo等各种查询维度的映射关系；查询订单orderNo信息时，先去映射表查orderNo对应的分片键userId，再去分片键分片算法路由的数据库表查询数据；

（2）、分库表基因：假如通过 user_id 分库，分为 8 个表，采用 user_id % 8 的方式进行路由，此时是由 user_id 的最后 3bit 来决定这行 User 数据具体落到哪个库上，那么这 3bit 可以看为分表基因。那么，如果我们将这 3 bit 参考类似 Snowflake 的方式，融入进入到 id字段中 。通过基因 id ，就可以知道数据所在的库表，这样就不用维护映射表了；

参考：大众点评基于基因ID的分库表实践

2、单库单表的sql语句在多分片中不能运行

问题：

• 分表导致表名称的修改，或者分页、排序、聚合分组、范围等操作的不正确处理。
• 跨节点 join 的问题。

解决这些语法问题，一般都需要将这些sql操作在每一个库表实例中执行后，对结果再次进行查询；一些中间件做了很多的处理；

3、引入了跨库事务处理的复杂性

目前市面上，分布式事务的解决方案还是蛮多的，但是都是基于一个前提，需要保证本地事务。那么，就对我们在分库分表时，就有相应的要求：数据在分库分表时，需要保证一个逻辑中，能够形成本地事务。

例如说，将同一个用户的订单主表，和订单明细表放到同一个库，那么在创建订单时，还是可以使用相同本地事务

4、分布式全局唯一 ID 

• 在单库单表的情况下，直接使用数据库自增特性来生成主键ID，这样确实比较简单。
• 在分库分表的环境中，数据分布在不同的分表上，不能再借助数据库自增长特性。需要使用全局唯一 ID，例如 UUID、GUID等 

如何迁移到分库分表？

一般来说，会有三种方式：

• 1、停止部署法。
• 2、双写部署法，基于业务层。
• 3、双写部署法，基于 binlog 。

【双写部署法，基于业务层】详细过程：

• 双写 ，老库为主。读操作还是读老库老表，写操作是双写到新老表。
• 历史数据迁移 dts + 新数据对账校验（job） + 历史数据校验。
• 切读：读写以新表为主，新表成功就成功了。
• 观察几天，下掉写老库操作。

具体的详细方案，可以看看如下几篇文章：

• 《数据库分库分表后，如何部署上线？》
• 《【面试宝典】如何把单库数据迁移到分库分表？》
• 《分库分表的面试题3》
• 《不停机分库分表迁移》
• 《如何设计可以动态扩容缩容的分库分表方案？》 
• 「如何迁移到分库分表？」 

市面有哪些分库分表中间件？

分库表中间件目标是尽量透明化分库分表所带来的影响，让使用方尽量像使用一个数据库一样使用水平分片之后的数据库集群；

目前，市面上提供的分库分表的中间件，主要有两种实现方式：

• Client 模式
• Proxy 模式

分库分表中间件？比较常见的包括：

• Cobar
• MyCAT
• Atlas
• TDDL
• Sharding Sphere

1）Cobar

阿里 b2b 团队开发和开源的，属于 Proxy 层方案。

早些年还可以用，但是最近几年都没更新了，基本没啥人用，差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库 join 和分页等操作。

2）MyCAT

基于 Cobar 改造的，属于 Proxy 层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了。但是确实相比于 Sharding Sphere 来说，年轻一些，经历的锤炼少一些。

3）Atlas

360 开源的，属于 Proxy 层方案，以前是有一些公司在用的，但是确实有一个很大的问题就是社区最新的维护都在 5 年前了。所以，现在用的公司基本也很少了。

4）TDDL

淘宝团队开发的，属于 client 层方案。支持基本的 crud 语法和读写分离，但不支持 join、多表查询等语法。目前使用的也不多，因为还依赖淘宝的 diamond 配置管理系统。

5）Sharding Sphere

Sharding Sphere ，可能是目前最好的开源的分库分表解决方案，目前已经进入 Apache 孵化。

Sharding Sphere 提供三种模式：

关于每一种模式的介绍，可以看看 《ShardingSphere > 概览》

• Sharding-JDBC
• Sharding-Proxy
• Sharding-Sidecar 计划开发中。

其中，Sharding-JDBC 属于 client 层方案，被大量互联网公司所采用。例如，当当、京东金融、中国移动等等。

如何选择？

综上，现在其实建议考量的，就是 Sharding Sphere ，这个可以满足我们的诉求。

Sharding Sphere 的 Sharding-JDBC 方案，这种 Client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合 sharding-jdbc 的依赖。

例如阿里、美团内部，更多使用的是 Client 模式。

Sharding Sphere 的 Sharding-Proxy 方案，这种 Proxy 层方案，可以解决我们平时查询数据库的需求。我们只需要连接一个 Sharding-Proxy ，就可以查询分库分表中的数据。另外，如果我们有跨语言的需求，例如 PHP、GO 等，也可以使用它。