关系型数据库MySql分库分表带来的问题以及解决方案

水平分表

水平分表是什么？

将一张表横向拆分为多张表，拆分的表，依然在同一个库中。
例如，user表有400w条记录，将user表拆分成4张表，每张表100w条记录。拆分后的表名，分别叫做user_0、user1、user2、user3。拆分

水平分表后有哪些问题？

水平分表规则以及扩展性问题

拆分规则一般可以根据分表键进行拆分，分表键一般可以选择表的id，比如用户id、商品id、订单id等，还可以根据时间、地区等。

range范围

范围划分策略。
例如，user表有400w条记录，将user表拆分成4张表，每张表100w条记录。拆分后的表名，分别叫做user_0、user_1、user2、user3。每张表存储的数据范围如下
user_0：0~100W；
user_1：100~200W；
user_2：200~300W；
user_3：300~400W；

优点：
利于扩容，每次需要进行扩容时，老表的数据不用迁移，比如现在要新增一张表，user_4，user0~user3表的数据不需要进行迁移。

缺点：
可能会产生数据热点问题，比如，对于订单表，当按照时间范围进行划分时，在双11期间，订单暴增，就产生了数据热点问题。
并且一般新数据被查询的次数很多，也就是刚扩容的新表的性能压力会很大，而老表的的性能压力比较小。

哈希取模

对分表键进行哈希函数计算hash(sharding_key)，然后使用hash(sharding_key)对数据库数量进行取模，当有四个分表时，则最终的数据库是：hash(sharding_key)%4。
需要注意的是，哈希函数一定要选好，可以参考Java中的HashMap的哈希函数，计算出哈希函数后，使用哈希结果的高16位和低16取位异或，最大程度让高16位和低16位都参与到哈希取模的过程中。

优点：
没有明显的性能热点问题。

缺点：
当需要二次扩容时，比较麻烦，比如现在由4张分表扩容成8张分表时，需要再次对所有的数据行记录的分表键重新计算哈希值，然后根据hash(sharding_key)%8，分配到最新的数据库中。举个简单的例子，hash(sharding_key)=7，只有4张表时，7%4=3，会分配到user_3表；当扩容到8张表时，7%8=7，需要将该数据迁移到user_7表。

一致性哈希

使用普通哈希取模的方式，在容量没有规划好的时候，当需要扩容或者缩容时，会遇到大量的数据需要重新迁移的问题。

一致性哈希可以在移除或者添加新的表时，尽可能小的改变已存在的数据和最终的分表之间的映射关系，解决了简单的哈希取模算法在扩缩容时的数据迁移问题。

我记得NoSql数据库Memcash在集群模式下对于键和数据库之间的对应关系，就是使用的一致性哈希来进行数据分配。

优点&缺点：
使用一致性哈希算法，优点和缺点属于一种折中，优点和缺点都不是很突出，也不是很明显。
数据热点问题相对较小，扩缩容时数据迁移相对较少。

主键id的唯一性问题

分表后，不能再依赖于分表后的每张表user_0、user_1等表的自增id来作为行记录的主键。
常见的方案有以下几种。

自增步长

根据水平节点的数量设置自增步长。
例如，单表拆成四张表，则每张表的自增步长是4，则每张表的id大概如下：
user_0：0、4、8…
user_1：1、5、9…
user_2：2、6、10…
user_3：3、7、11…

雪花算法

待研究。。。

利用第三方中间件来生成唯一主键

比如使用Redis的incr。

比如使用Zookeeper来生成分布式唯一id。

或者利用另外一个MySql库的一张表table_id，每次取当前需要插入的数据的行id时，就往table_id插入一条数据，然后该次插入数据会返回一个id，将该id作为新数据的id。不过这种方式可能会有单表的性能瓶颈，且会有单点风险。

数据分页问题

在没有分表时，可以按照如下sql，把需要的数据直接select出来：

sql_1：
select * from table order by field_1 limit offset, num_rows

分页之后，就不能直接使用sql_1。

有如下一些解决方案：

使用ES或中间表

每天凌晨将所有的分表的数据汇聚到ES中，我估计要按照order by的字段提前排序号之后再存储到ES中，比如，前端页面支持3个分页，则需要存储三份索引。
因为ES的数据时存储在磁盘中的，所以不会占用太多内存，我估计只有常用的数据才会存储在内存中，比如分页数据的首页。
后面学习了ES之后，再来进行补充。

当有新数据时，可以在插入的数据存储到MySql等关系型数据库之后，发送一条消息到消息队列，比如RabbitMq、Kafka、RocketMq，然后使用消费者将该消息插入到ES中。

删除数据和插入新数据一致（后面验证）。

更新数据和插入数据一致（后面验证）。

使用大数据平台

我估计是使用实时流式计算，比如flink或者Spark Streaming（待验证）。

当有新数据时，可以在插入的数据存储到MySql等关系型数据库之后，发送一条消息到消息队列，比如RabbitMq、Kafka、RocketMq，然后使用Flink消费者消费该条数据，将该数据插入到目标表中，只是目前还不知道如何按照前端order by的字段进行排序，难道使用Flink将数据插入目标表之后，还要将目标表进行排序，插入到排序之后的表中？还是根据Flink插入的第一张表进行全局统一排序？（待学习Flink之后验证）。

优点：按照参考中的博主讲的，这是最佳的方案。对大数据实时技术不太了解，需要去研究。

缺点：需要构建完整的大数据中台，成本较高。

禁止跳页一：根据分表键进行分页

前端页面只能查看首页、当前页、下一页、上一页。

当前端没有指定order by的字段时，后端可以根据分表键（比如主键）排序。

最开始从user_0查询，当user_0中的数据被显示完时，去user_1中查询，当user_0中剩余3条数据，每页显示10条时，再从user_1中查询出7条数据。后续就从user_1中查询分页，直到所有的分表都被显示完。

当然，这种方案需要后端记录下当前请求的客户端查询到user_0还是其他分表了，可以使用cookie、session、redis缓存等技术来进行记录。

这种方案只能用于前端界面没有指定order by时，或者order by的字段恰好是分表键时。

当前端页面可以根据多个维度进行order by时，比如，对于订单表，前端页面可以选以下方式：

1、order by 订单id。
2、order by 卖家id。
3、order by 买家id。

据说淘宝的订单库是使用的这种方式。

如果还想使用禁止跳页的方式，则可以构建三个水平集群，每个集群的数据一模一样，不过三个水平集群的分表键不同，分别是订单id、卖家id、买家id。

优点：因为根据分表键进行查询，只需要到指定的分表中进行查询，使用的数据库连接比较少。
缺点：只能用于分表键，如果构建多个相同的数据集群用于多个分表键分页，则会导致成本较高

禁止跳页二：根据非分表键进行分页

前端页面只能查看首页、当前页、下一页、上一页。

比如当前是order by create_time asc limit 10，每个页面是10条数据。
从每个分表中拿到第一页数据，然后业务层汇聚，拿到第一页数据。
前端页面只能点下一页，并且把上一次最大的的create_time传过滤，后端拿到前端当前页的最大的cur_create_time，然后再去每个分表中，查询where create_time >= cur_create_time limit 10，然后再在业务层做汇聚，查出10条数据，但是需要前端把之前查出的数据的id都传到后端，后端需要把前端已经显示的数据剔除掉。
但是这种方式是有漏洞的，比如create_time重复的数据超过20条，有30条，就有3页的数据的create_time是一样的，但是前端只会把当前页的数据的id传到后端，后端有可能出现死循环，永远只显示这30条数据。所以需要前端记录当前的最大时间与当前最大时间显示过的数据的id，但是当同一create_time重复的数据很多时，会导致前端传递给后端的id很多，导致sql过长，可能会出现问题。

所以禁止跳页这种方式，最好只用在order by的字段没有重复的字段上，比如主键。

Sharding-JDBC分页修正

使用Sharding-JDBC改写sql。
比如，当要查询的sql如下：

分页为：每页10条数据，查询第4页。
sql为：跳过30条数据，查询出10条数据。

select * from user order by username limit 30, 10.

由于username不是分表键，这条sql会被Sharding-JDBC改写为如下

改写过后的sql，会到所有的分表中查询，user_0、user_1、user_2、user_3：
select * from user order by username limit 40.

使用改写过后的sql查询出数据后，然后再由Sharding-JDBC框架做汇聚，跳过30条数据，取10条数据。

为什么会这样呢？
因为数据被记录到不同的表中，只有将所有的分表中的前40条数据查询出来后，才能直到哪些是第4页的数据。

举个例子，现在有一张表student，被分成了2张表，student_0、student_1，有个属性，math_score，每张表的数据如下

student_0：80、90、100、110
student_1：75、85、95、105

上述数据，如果不分页，则student表中的数据为：

student：75、80、85、90、95、100、105、110

现在前端页面要查询

跳过1条数据，然后取2条数据。
select score from student order by score limit 1, 2

如果不分表，则查询的结果是：

跳过75，取以下将个结果：
80、85

分表后，使用Sharding-JDBC会将sql改写为

Sharding-JDBC改写为：
select score from student order by score limit 3

然后将以下数据查询出来

75、80、85、90、95、100

跳过75，然后从中取出

80、85

这样结果才是正确的。

优点：不需要我们自己做额外的sql，只需要使用Sharding-JDBC即可。

缺点：当分页靠后时，性能急剧下降，且性能下降与分页深度（靠后的分页）成正比。比如，当要查询第1000页，每页10条数据，则sql会被改写成limit 10000，需要从数据库中查询出10000条数据。

多维度查询问题

对于订单表order，被分成多张分表后，假如是order_id做分表键。
如果此时还需要根据买家id、卖家id、订单时间来做查询

where buyer = xxx;
where seller = xxx;
where create_time = xxx;

有以下几种方案。

所有分表查询，业务层汇聚

如果不使用任何其他技术方案，因为这些字段不是分表键，不知道直接去哪张分表中查询，就只能去所有分表中进行检索，然后再在业务层做汇聚。

缺点：因为需要查询每张分表，所以会消耗很多数据库资源，比如数据库连接、数据库IO资源。在不进行每张分表并行查询的情况下，查询时间还会根据分表数量呈线性上升。

通过ES、Redis等第三方中间件查询分表键

通过ES来维护buyer、seller、create_time和分表键之间的关系，查询到分表键之后，再根据分表键去指定的分表中查询。

具体方案实施，和前文分页场景中使用ES一样，不再赘述。

优点：可以根据ES、Redis直接定位到主键。
缺点：数据实时性可能会降低一点。

水平集群方案

建立多个水平集群，每个集群的数据完全一样，只是分表键不一样，集群数量和前端需要查询的字段数量一样。
和分页场景中的多个水平集群构建方案一致。

优点：查询快速，即使用户在界面需要根据不同的字段进行过滤，也可以快速定位到自己需要的数据。

缺点：成本高，需要构建多个集群。

聚合操作问题

分表后，grou by、sum、count等操作需要基于全部数据进行聚合，如何查询？
有以下方案。

所有分表查询，业务层汇聚

如果不使用任何其他技术方案，只能去所有分表中进行查询，然后再在业务层做汇聚。

缺点：group by操作的结果在每张分表中，结果集可能也会很大，对业务层的消耗会比较大。

优点：无须额外的技术方案，且sum、count操作，每张表的结果集也会比较小。

使用ES等一些第三方中间件

将数据导入到ES中，使用ES来做聚合操作，然后存储下来，后面的聚合操作从ES中进行查询。

定期跑脚本做group by、sum、count的数据统计，然后存储到Redis中

这种定期跑脚本，一般都是在业务的低峰期进行，比如半夜凌晨等时间。

缺点：由于定时脚本一般是一天一次，数据时效性比较低。

跨表join问题（作为被关联表时的问题）

假如用户表分表了，订单表想和用户表进行关联。
有以下方案。

所有分表关联，业务层汇聚

如果不使用任何其他技术方案，只能去所有分表中进行关联，然后再在业务层做汇聚。

缺点：会消耗很多数据库连接、数据库IO压力大、性能开销大。

优点：无须额外的技术方案。

字段冗余

如果关联后，只需要关联出的两三个字段，可以直接在订单表中将需要的字段做冗余，就不需要再关联表了。

根据分表规则定位分表

比如现在要查订单表的where order_id=xxx，但是需要跟user表关联，获取user表中的一些信息，根据User_id关联，则可以先把order表中的user_id先查出来，如果user_id是分表键，则可以根据user_id定位到具体的分表，然后直接去分表中查询。如果user_id不是分表键，可以根据多维度查询中的其他方案定位到分表，然后去具体的分表中进行查询。

水平分表之后，代码需要改变吗？

按照我的理解，大部分代码是可以通过中间件或者数据库代码解决的，由中间件的框架进行处理，但是有些业务代码多多少少需要改变一些。

水平分库

水平分库是什么？

将原来在一个库中的一张表的数据，水平拆分到多个数据库中。
例如，原来的订单表有如下数据：

order: order_1, order_2, order_3, order_4

拆分成两张表，order_0、order_1，分布到db_0与db_1中，db_0与db_1是不同的数据库：

db_0:
order_0: order_1, order_2

db_1
order_1: order_3, order_4

水平分库后有哪些问题？

除了具有水平分表的所有问题，水平分表的问题，请参考前文。
除了上述问题，还有以下问题。

数据落库问题

数据落库问题的解决方案和水平分表的数据落表问题的解决方案差不多，有range范围、哈希取模、一致性哈希等方案。

非分库键查询问题

水平分表的时候，数据都还在单库中，无论怎样的操作，都可以获取到数据库连接，大不了遍历所有的表，这些操作都还是在单库中。
水平分库还是有点差别，对相应的数据进行操作时，必须要知道获取哪个库的数据库连接，所以必须由分库键获取到数据库连接，否则都不知道连接哪个数据库。

这个问题的解决方案，跟水平分表中涉及的问题的解决方案差不多，一般有通过ES等中间件，维护非分库键和分库键的对应关系。

如果通过ES中间件的方式性能不好，还可以做水平库集群，每个集群的数据一样，但是分库键不一样。

分布式事务问题

原来在一个数据库中做增删改，可以使用本地事务，现在在多个库中做增删改，需要使用分布式事务

使用seata框架

seata框架的XA、AT、TCC、SAGA事务模式，几乎可以解决我们遇到的所有分布式事务场景了，这几种分布式事务模式的对比如下
在这里插入图片描述

使用事务消息

RocketMQ的事务消息也可以解决分布式事务的问题。

垂直分表

垂直分表是什么？

一般是一张表中的字段太多，按照业务，垂直切分为多张表，但是拆分后的表还是在一个数据库中。
例如，原来的一张表中的字段如下

table1:
field1, field2, field3, field4.

垂直切分为两张表，如下

table2:
field1, field2

table3:
field3, field4

垂直分表后有哪些问题？

对一张表的操作需要改为对多张表的操作

sql需要改

对单张表的查询，需要改为对拆分后的多张表的查询。

业务代码

需要改，删除数据时，需要删除多张表。

需要开启事务操作

增删改时，对于多张表，都需要开启事务操作。

垂直分库

垂直分库是什么？

按照业务属性，将不同的表拆分到不同的数据库中。

一般垂直分库还会伴随着业务层的微服务之间的拆分，一个微服务会拆分成多个微服务，按照我的理解，一般是多个微服务对应着一个数据库，跨库之间的数据库调用，最好是通过对应的服务进行数据的存取，这样才能有更好的扩展性和可维护性。

例如，原来在用户、商品、购物车、订单相关的表都在一个数据库中，但是单库的性能到了瓶颈，需要将订单相关的表拆分出去。

垂直分库后有哪些问题？

垂直分库后，有以下问题与解决方案。

跨库join问题

原来在一个数据库中进行连表，垂直分库之后，需要在多个数据库之间进行联合查询。
有以下解决方案。

在Java系统中进行组合

如果需要跨库join，可以通过对应的数据库的API将需要的数据查询出来，然后在Java代码中将数据组装起来，然后返回给最开始的调用者。

字段冗余

如果跨库join之后，需要的字段不多，就两三个字段，可以在左表中做字段冗余，避免跨库join。

分布式事务问题

在进行增删改时，如何涉及到拆分后的多个数据库，单库的本地事务不够用了，需要使用分布式事务。

具体可以参考水平分库章节中的分布式事务。

分库之后如何访问数据库&常用的分库分表中间件

编码层：在代码中通过框架提供的数据源动态切换类实现，如Spring.AbstractRoutingDataSource类。
框架层：一般的ORM框架也会提供切换数据源的实现类，比如MyBatis.Interceptor接口拦截SQL实现。
驱动层：在JDBC驱动层拦截SQL语句，然后改写SQL实现，如Sharding-JDBC框架的原理就是如此。
代理层：所有使用数据库的业务服务都连接代理中间件，由中间件来决定落库位置，如MyCat实现。
服务层：如今较为流行的分布式数据库，基本上都自带分库分表功能，如TiDB、OceanBase，其中的OceanBase是金融级的数据库，可以自动做分表分库，数据备份和冗余，但是我了解到OceanBase有商业版，需要付费，不知道有没有社区版，社区版提供了哪些功能。

一般编码层或框架层都无法单独实现数据源的切换，两者必须配合起来完成，用MyBatis.Interceptor接口拦截SQL语句，然后根据SQL中的路由键做运算，最终再通过Spring.AbstractRoutingDataSource类去动态切换数据源。但这种方案的工程量很大，实现过程也较为繁杂，所以下面直接来看一些成熟的方案，如下：
工程（依赖、Jar包、不需要独立部署，可集成在业务项目中）：
淘宝网：TDDL
蘑菇街：TSharding
当当网：Sharding-Sphere-JDBC

进程（中间件、需要独立部署的第三方进程）
早年最热门、基于阿里Cobar二开的MyCat
阿里B2B：Cobar
奇虎360：Atlas
58同城：Oceanus
谷歌开源：Vitess
当当网：Sharding-Sphere-Proxy