Mysql分库分表

1、背景

Mysql作为做流行的开源数据库，在各大互联网公司被广泛使用。通常我们用一个库就可以满足需求，但是随着业务的增长，数据量和并发量迅速膨胀。Mysql单表数据量到百万以上的时候，查询效率就会受到影响，另外Mysql单库能承受的并发也有限。这个时候我们需要做分库分表，来提高数据库的性能和扩展性

2、为什么分库分表

2.1 分表

单表数据量太大会极大的影响sql执行效率，一般来说单表达到几百万的时候，性能就相对较差了，就需要分表了

2.2 分库

单个库一般最大支持到2000并发，超过就需要分库了，一个健康的单库并发值最好控制在1000左右

#	分库分表前	分库分表后
并发支撑情况	MySQL 单机部署，扛不住高并发	MySQL 从单机到多机，能承受的并发增加了多倍
磁盘使用情况	MySQL 单机磁盘容量几乎撑满	拆分为多个库，数据库服务器磁盘使用率大大降低
SQL 执行性能	单表数据量太大，SQL 越跑越慢	单表数据量减少，SQL 执行效率明显提升

数据量大，就分表；并发高，就分库。

2.3 带来的问题

• join 操作 ： 同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。
• 事务问题 ：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。
• 分布式 id ：分库之后， 数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。我们如何为不同的数据节点生成全局唯一主键呢？这个时候，我们就需要为我们的系统引入分布式 id 了。

3、不同分库中间件的优缺点

3.1 Sharding-jdbc

1、当当开源的，client层方案
2、支持分库分表，读写分离，分布式ID生成，柔性事物（最大努力送达型事物，TCC事物）
3、社区比较活跃

优点： 优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高
缺点： 各个系统都需要耦合Sharding-jdbc依赖，如果需要升级，各个系统需要重新部署

适合中小型公司

3.2 Mycat

1、proxy层方案
2、支持的功能完善
3、很流行，社区非常活跃

优点： 对各个项目透明，升级只需要升级中间件
缺点： 需要部署，自己运维一套中间件，运维成本高

适合大型公司

3.3 如何选择

推荐使用sharding-jdbc和mycat：

• 小型公司选用sharding-jdbc，client层方案轻便，而且维护成本低，不需要额外增派人手，而且中小型公司系统复杂度会低一些，项目也没那么多
• 中大型公司最好还是选用mycat这类proxy层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护mycat，然后大量项目直接透明使用即可

4、如何拆分

4.1、水平拆分（用的最多）

把一个表的数据拆分到多个表中，但是每个表的结构都一样，把数据均匀的放到不同的表里，用多个表来抗并发

根据特定字段来拆分

比如订单表根据订单ID来拆分，分到32个库，每个库32张表，那么orderID%32决定落在哪个库，
orderID/32%32决定落在那个表。

而且这儿还有两种分库分表的方式：

• 一种是按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了。
• 是按照某个字段 hash 一下均匀分散，这个较为常用。

各自优缺点：

• range 来分，好处在于说，扩容的时候很简单，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了；缺点，但是大部分的请求，都是访问最新的数据。实际生产用 range，要看场景。
• hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表。

4.2、垂直拆分

把一个有很多字段的表拆分到多个表或者多个库上去，把访问频率高的热点字段和访问频率低的非热点字段分开放到不同的表。因为数据库有缓存，热点字段越少，缓存里可以存更多的行

5、分库分表方案确定后，还需要解决以下问题

• 如何实现数据从单库单表到分库分表的迁移？
• 表和库的数量又遇到瓶颈怎么办，如何处理？
• 分库分表后的ID如何处理？
• 分库分表后的事务如何处理？

5.1 如何设计才可以让系统从未分库分表平滑的动态切换到分库分表上？

停机迁移

一种方式是直接停机迁移，需要再深夜没有流量的时候进行，把数据迁移到分库分表数据库上，然后重启系统就可以，这种方式需要停机，并不是平滑迁移。

双写迁移方案

这种方案不用停机。

简单来说，就是在线上系统里面，之前所有写库的地方，增删改操作，除了对老库增删改，都加上对新库的增删改，这就是所谓的双写，同时写俩库，老库和新库。

然后系统部署之后，新库数据差太远，用之前说的导数工具，跑起来读老库数据写新库，写的时候要根据 gmt_modified 这类字段判断这条数据最后修改的时间，除非是读出来的数据在新库里没有，或者是比新库的数据新才会写。简单来说，就是不允许用老数据覆盖新数据。

导完一轮之后，有可能数据还是存在不一致，那么就程序自动做一轮校验，比对新老库每个表的每条数据，接着如果有不一样的，就针对那些不一样的，从老库读数据再次写。反复循环，直到两个库每个表的数据都完全一致为止。

接着当数据完全一致了，就 ok 了，基于仅仅使用分库分表的最新代码，重新部署一次，不就仅仅基于分库分表在操作了么，还没有几个小时的停机时间，很稳。所以现在基本玩儿数据迁移之类的，都是这么干的。

这个方法的缺点就是代码里需要同时支持两个库的过度阶段，稍微麻烦一点，但是不需要停机，可以平滑迁移。

5.2 动态扩容缩容的分库分表方案？

分库分表完成后，如果数据继续增加，原来的方案不能满足业务了，需要继续扩大更多库更多表，那么还要再来一次分库分表吗？这样处理会很麻烦，因为后续可能还不够，分库分表数据迁移非常费劲。那么我们可以采取一次性分足够多的库和表，这样避免了后续迁移数据的问题。

一开始分库一次性分够，32库*32表，一共1024张表，根据某个 id 先根据 32 取模路由到库，再根据 32 取模路由到库里的表。

orderId	id % 32 (库)	id / 32 % 32 (表)
259	3	8
1189	5	5
352	0	11
4593	17	15

刚开始的时候，这个库可能就是逻辑库，建在一个数据库上的，就是一个 MySQL 服务器可能建了 n 个库，比如 32 个库。后面如果要拆分，就是不断在库和 MySQL 服务器之间做迁移就可以了。然后系统配合改一下配置即可。

比如说最多可以扩展到 32 个数据库服务器，每个数据库服务器是一个库。如果还是不够？最多可以扩展到 1024 个数据库服务器，每个数据库服务器上面一个库一个表。因为最多是 1024 个表。

这么搞，是不用自己写代码做数据迁移的，都交给 DBA 来搞好了，但是 DBA 确实是需要做一些库表迁移的工作，但是总比你自己写代码，然后抽数据导数据来的效率高得多吧。

这里对步骤做一个总结：

1. 设定好几台数据库服务器，每台服务器上几个库，每个库多少个表，推荐是 32 库 * 32 表，对于大部分公司来说，可能几年都够了。
2. 路由的规则，orderId 模 32 = 库，orderId / 32 模 32 = 表
3. 扩容的时候，申请增加更多的数据库服务器，装好 MySQL，呈倍数扩容，4 台服务器，扩到 8 台服务器，再到 16 台服务器。
4. 由 DBA 负责将原先数据库服务器的库，迁移到新的数据库服务器上去，库迁移是有一些便捷的工具的。
5. 我们这边就是修改一下配置，调整迁移的库所在数据库服务器的地址。
6. 重新发布系统，上线，原先的路由规则变都不用变，直接可以基于 n 倍的数据库服务器的资源，继续进行线上系统的提供服务。

6、分库分表之后，id 主键如何处理？

6.1、设置数据库 sequence 或者表自增字段步长

适合的场景：在用户防止产生的 ID 重复时，这种方案实现起来比较简单，也能达到性能目标。
但是服务节点固定，步长也固定，将来如果还要增加服务节点，就不好搞了。

6.2、UUID

不具有有序性，作为ID对索引不友好，会导致 B+ 树索引在写的时候有过多的随机写操作（连续的 ID 可以产生部分顺序写），不适合做主键

适合的场景：如果你是要随机生成个什么文件名、编号之类的，你可以用 UUID，但是作为主键是不能用 UUID 的。

6.3、获取当前系统时间， 并发高的时候可能会重复

适合的场景：一般如果用这个方案，是将当前时间跟很多其他的业务字段拼接起来，作为一个 id，如果业务上你觉得可以接受，那么也是可以的。你可以将别的业务字段值跟当前时间拼接起来，组成一个全局唯一的编号。

6.4、snowflake 算法

snowflake 算法是 twitter 开源的分布式 id 生成算法，采用 Scala 语言实现，是把一个 64 位的 long 型的 id，1 个 bit 是不用的，用其中的 41 bits 作为毫秒数，用 10 bits 作为工作机器 id，12 bits 作为序列号。

7、分库分表后，事务如何处理？

分库分表以后，可能会出现跨库事务，这种事务怎么处理呢？

7.1 优化方案避免出现跨库事务

首先我们做分库分表的时候，尽量避免出现跨库的事务的场景，比如订单表，分库的时候根据订单ID开拆分，那么跟订单相关的其他表也按订单ID来拆分，这样针对一个订单的事务就可以控制在一个库中，避免了跨库事务。

7.2 跨库事务的处理

如果无法避免，分库分表一般是单系统对应多个库，那么数据库的XA事务正好可以处理这种场景，sharding-jdbc可以支持数据库的XA事务，通过@DSTransactional注解就可以实现跨库事务。
Mycat也能支持XA事务。