mysql分库分表理论及常用框架对比

什么是分库分表

​ 把存于一个库的数据分散到多个库中，把存于一个表的数据分散到多个表中。如果说读写分离是为了分散数据库读写操作压力，分库分表就是为了分散存储压力

分表

比如你单表都几千万数据了，你确定你能扛住么？绝对不行，单表数据量太大，会极大影响你的 sql执行的性能，到了后面你的 sql 可能就跑的很慢了。一般来说，就以我的经验来看，单表到几百万的时候，性能就会相对差一些了，你就得分表了。

分表是啥意思？就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。比如按照用户 id 来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在 200 万以内。

分库

分库是啥意思？就是你一个库一般我们经验而言，最多支撑到并发 2000，一定要扩容了，而且一个健康的单库并发值你最好保持在每秒 1000 左右，不要太大。那么你可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

这就是所谓的分库分表，为啥要分库分表？你明白了吧。

什么时候考虑切分

​ 并不是所有表都需要进行切分，主要还是看数据的增长速度。切分后会在某种程度上提升业务的复杂度，数据库除了承载数据的存储和查询外，协助业务更好的实现需求也是其重要工作之一。

​ 不到万不得已不用轻易使用分库分表这个大招，避免"过度设计"和"过早优化"。分库分表之前，不要为分而分，先尽力去做力所能及的事情，例如：升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。

分库分表的方式

垂直切分

适用场景：如果是因为表的个数多而让数据多，可以按照功能划分，把联系密切的表切分出来放在同一个库中（分库）；

垂直拆分是指：

①、分库：个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同
的数据库上面，这样也就将数据或者说压力分担到不同的库上面

②、分表：将一个属性较多，一行数据较大的表，将不同的属性拆分到不同的表中，以降低单库（表）大小，达到提升性能的目的的方法。

垂直切分后，各个库（表）的特点是：

（1）每个库（表）的结构都不一样

（2）一般来说，每个库（表）的属性至少有一列交集，一般是主键

（3）所有库（表）的并集是全量数据

优点：

• 拆分后业务清晰，拆分规则明确；
• 系统之间整合或扩展容易；
• 数据维护简单。

缺点：

• 部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度；
• 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高；
• 事务处理复杂。

水平切分

适用场景：如果是因为表中的数据量过于庞大，则可以采用水平切分，按照某种约定好的规则将数据切分到不同的数据库中；

水平切分是指：

①、分库：以某个字段为依据（例如uid），按照一定规则（例如取模），将一个库上的数据拆分到多个库上，以降低单库大小，达到提升性能的目的的方法。

②、分表：以某个字段为依据（例如uid），按照一定规则（例如取模），将一个表上的数据拆分到多个表上，以降低单表大小，达到提升性能的目的的方法。

水平切分后，各个库（表）的特点是：

（1）每个库（表）的结构都一样

（2）每个库（表）的数据都不一样，没有交集

（3）所有库（表）的并集是全量数据

优点 ：

• 拆分规则抽象好，join 操作基本可以数据库做；
• 不存在单库大数据，高并发的性能瓶颈；
• 应用端改造较少；
• 提高了系统的稳定性跟负载能力。

缺点 ：

• 拆分规则难以抽象；
• 分片事务一致性难以解决；
• 数据多次扩展难度跟维护量极大；
• 跨库 join 性能较差

几种常用的分库分表的策略

1、HASH取模

假设有用户表user,将其分成3个表user0,user1,user2.路由规则是对3取模,当uid=1时,对应到的是user1,uid=2时,对应的是user2.

好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表。

2、范围分片

从1-10000一个表,10001-20000一个表。

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

3、地理位置分片

华南区一个表,华北一个表。

4、时间分片

按月分片，按季度分片等等,可以做到冷热数据。

分库分表后引入的问题

分布式事务问题

如果我们做了垂直分库或者水平分库以后,就必然会涉及到跨库执行SQL的问题,这样就引发了互联网界的老大难问题-“分布式事务”。

那要如何解决这个问题呢？

• 使用分布式事务中间件
• 使用MySQL自带的针对跨库的事务一致性方案(XA),不过性能要比单库的慢10倍左右。
• 能否避免掉跨库操作(比如将用户和商品放在同一个库中)

跨库join的问题

分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

粗略的解决方法：

• 全局表：基础数据，所有库都拷贝一份。
• 字段冗余：这样有些字段就不用join去查询了。
• 系统层组装：分别查询出所有，然后组装起来，较复杂。

横向扩容的问题

当我们使用HASH取模做分表的时候,针对数据量的递增,可能需要动态的增加表,此时就需要考虑因为reHash导致数据迁移的问题。

结果集合并、排序的问题

因为我们是将数据分散存储到不同的库、表里的,当我们查询指定数据列表时,数据来源于不同的子库或者子表,就必然会引发结果集合并、排序的问题。如果每次查询都需要排序、合并等操作,性能肯定会受非常大的影响。走缓存可能一条路!

分库分表实现方法

分库分表的实现主要有2种方案，第一种是代码层封装，比如使用Sharding-JDBC，本文的主要就是通过Sharding-JDBC来进行分库分表。第二种是通过中间件代理，比如使用mycat。

分库分表方案

分库分表最主要有几个配置：

1.有多少个数据源
2.每张表的逻辑表名和所有物理表名
3.用什么列进行分库以及分库算法
4.用什么列进行分表以及分表算法

用过哪些分库分表中间件？不同的分库分表中间件都有什么优点和缺点？

这个其实就是看看你了解哪些分库分表的中间件，各个中间件的优缺点是啥？然后你用过哪些分库分表的中间件。

比较常见的包括：

• cobar

• TDDL

• atlas

• sharding-jdbc

• mycat

cobar

阿里 b2b 团队开发和开源的，属于 proxy 层方案，就是介于应用服务器和数据库服务器之间。应用程序通过 JDBC 驱动访问 cobar 集群，cobar 根据 SQL 和分库规则对 SQL 做分解，然后分发到 MySQL 集群不同的数据库实例上执行。早些年还可以用，但是最近几年都没更新了，基本没啥人用，差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库 join 和分页等操作。

TDDL

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

atlas

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

sharding-jdbc

当当开源的，属于 client 层方案。确实之前用的还比较多一些，因为 SQL 语法支持也比较多，没有太多限制，而且目前推出到了 2.0 版本，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。而且确实之前使用的公司会比较多一些（这个在官网有登记使用的公司，可以看到从 2017 年一直到现在，是有不少公司在用的），目前社区也还一直在开发和维护，还算是比较活跃，个人认为算是一个现在也可以选择的方案。

mycat

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

总结

综上，现在其实建议考量的，就是 sharding-jdbc 和 mycat，这两个都可以去考虑使用。

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

mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。

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

Mycat

什么是Mycat

它是一个开源的分布式数据库系统，是一个实现了 MySQL 协议的的Server，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用MySQL 原生（Native）协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为 N 个小表，存储在后端 MySQL 服务器里或者其他数据库里。

Macat常见应用场景:

• 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换；
• 分表分库，对于超过 1000 万的表进行分片，最大支持 1000 亿的单表分片；
• 多租户应用，每个应用一个库，但应用程序只连接 Mycat，从而不改造程序本身，实现多租户化；
• 报表系统，借助于 Mycat 的分表能力，处理大规模报表的统计； 替代 Hbase，分析大数据；
• 作为海量数据实时查询的一种简单有效方案，比如 100 亿条频繁查询的记录需要在 3 秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时 Mycat 可能是最简单有效的选

实例参考资料：https://www.jianshu.com/p/f81422b1c915

Sharding-JDBC

官网介绍

Sharding-JDBC是一个开源的分布式数据库中间件，它无需额外部署和依赖，完全兼容JDBC和各种ORM框架。Sharding-JDBC作为面向开发的微服务云原生基础类库，完整实现了分库分表、读写分离和分布式主键功能，并初步实现了柔性事务。

中文文档地址：https://shardingsphere.apache.org/document/current/cn/overview/

sharding-jdbc后续发展为Sharding-Sphere，包含sharding-jdbc、Sharding-Proxy、Sharding-Sidecar

/	Sharding-JDBC	Sharding-Proxy	Sharding-Sidecar
Database	Any	MySQL/PostgreSQL	MySQL/PostgreSQL
Connections Count Cost	High	Low	High
Supported Languages	Java Only	Any	Any
PerformanceLow lossRelatively	High	lossLow	loss
Decentralization	Yes	No	No
Static Entry	No	Yes	No

	Sharding-JDBC	Sharding-Proxy	Sharding-Sidecar
数据库	任意	MySQL	MySQL
连接消耗数	高	低	高
异构语言	仅Java	任意	任意
性能	损耗低	损耗略高	损耗低
无中心化	是	否	是
静态入口	无	有	无

Mycat和Sharding-jdbc的区别

ShardingSphere在中小企业需要分库分表的时候用的会比较多，因为它维护成本低，不需要额外增派人手;而且目前社区也还一直在开发和维护，还算是比较活跃。

但是中大型公司一般会选择选用 Mycat 这类 proxy 层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护 Mycat，然后大量项目直接透明使用即可。

区别

1）、mycat 是一个基于第三方应用中间件的数据库代理框架，客户端所有的 jdbc 请求都必须要先交给 mycat ，再由 mycat 转发到具本的真实服务器中。
sharding-jdbc 是一个jar形式，在本地应用层重写的 jdbc 原生的方法，实现数据库分片形式。

2）、使用mycat时不需要改代码，而使用sharding-jdbc时需要修改代码

3）、mycat 属于 服务器端 的数据库中间件，而 sharding-jdbc 是一个 本地 数据库中间件框架。

Mycat(proxy中间件层):

Sharding-jdbc(TDDL为代表的应用层):