都2025年了，还不懂分库分表吗，一文看懂分库分表

一.什么是分库分表

分表：将一个表中的数据按照某种规则分拆到多张表中，降低锁粒度以及索引树，提升数据查询效率。分库：将一个数据库中的数据按照某种规则分拆到多个数据库中，以缓解单服务器的压力（CPU、内存、磁盘、IO）。

二.为什么要分库分表

性能角度：CPU、内存、磁盘、IO瓶颈

随着业务体量扩大，数据规模达到百万行，数据库索引树庞大，查询性能出现瓶颈。用户并发流量规模扩大，由于单库(单服务器)物理性能限制也无法承载大流量。

可用性角度：单机故障率影响面

如果是单库，数据库宕机会导致100%服务不可用，N库则可以将影响面降低N倍。

三. 什么时候才考虑分库分表呢？

4.1 什么时候分表？

如果你的系统处于快速发展时期，如果每天的订单流水都新增几十万，并且，订单表的查询效率明变慢时，就需要规划分库分表了。一般B+树索引高度是2~3层最佳，如果数据量千万级别，可能高度就变4层了，数据量就会明显变慢了。不过业界流传，一般500万数据就要考虑分表了。

4.2 什么时候分库

业务发展很快，还是多个服务共享一个单体数据库，数据库成为了性能瓶颈，就需要考虑分库了。比如订单、用户等，都可以抽取出来，新搞个应用（其实就是微服务思想），并且拆分数据库（订单库、用户库）。

四.拆分类型

3.1 垂直拆分

3.1.1 垂直分库

垂直拆库则在垂直拆表的基础上，将一个系统中的不同业务场景进行拆分。每个业务使用单独的库。好处：降低单数据库服务的压力（物理存储、内存、IO等）、降低单机故障的影响面

3.2.2 垂直分表

即大表拆小表，将一张表中数据不同”字段“分拆到多张表中，比如商品库将商品基本信息、商品库存、卖家信息等分拆到不同库表中。考虑因素有将不常用的，数据较大，长度较长（比如text类型字段）的拆分到“扩展表“，表和表之间通过”主键外键“进行关联。好处：降低表数据规模，提升查询效率，也避免查询时数据量太大造成的“跨页”问题。

3.2 水平拆分

操作：

将总体数据按照某种维度(时间、用户)等分拆到多个库中或者表中，典型特征不同的库和表结构完全一下，如订单按照(日期、用户ID、区域)分库分表。

水平拆表 :

将数据按照某种维度拆分为多张表，但是由于多张表还是从属于一个库，其降低锁粒度，一定程度提升查询性能，但是仍然会有IO性能瓶颈。

水平拆库

将数据按照某种维度分拆到多个库中，降低单机单库的压力，提升读写性能。

3.3 常见水平拆分方案

3.3.1 Range分库分表

说明：

按日期将不同月甚至是日的数据分散到不同的库（或者表）中；将userId为1-300w的记录分到第一个库（或者表），300w-600w的分到第二个库（或者表），以此类推。某种意义上，某些系统中使用的"冷热数据分离"，将一些使用较少的历史数据迁移到其他库中，业务功能上只提供热点数据的查询，也是类似的实践。

优点：

单表大小可控天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。

缺点：

热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

3.3.2 hash分库分表

hash取模策略：

指定的路由key（一般是user_id、order_id、customer_no作为key）对分表总数进行取模，把数据分散到各个表中。比如原始订单表信息，我们把它分成4张分表：

比如id=1，对4取模，就会得到1，就把它放到t_order_1;id=3，对4取模，就会得到3，就把它放到t_order_3;

一般，我们会取哈希值，再做取余：

Math.abs(orderId.hashCode()) % table_number

优点：

hash取模的方式，不会存在明显的热点问题。

缺点：

如果未来某个时候，表数据量又到瓶颈了，需要扩容，就比较麻烦。所以一般建议提前规划好，一次性分够。（可以考虑一致性哈希）

如果一开始按照hash取模分成4个表了，未来某个时候，表数据量又到瓶颈了，需要扩容，这就比较棘手了。比如你从4张表，又扩容成8张表，那之前id=5的数据是在（5%4=1，即第一张表），现在应该放到（5%8=5，即第5张表），也就是说历史数据要做迁移了。

3.3.3 range+hash取模混合

既然range存在热点数据问题，hash取模扩容迁移数据比较困难，我们可以综合两种方案一起嘛，取之之长，弃之之短。

比较简单的做法就是，在拆分库的时候，我们可以先用range范围方案，比如订单id在0-4000万的区间，划分为订单库1，id在4000万-8000万的数据，划分到订单库2，将来要扩容时，id在8000万~1.2亿的数据，划分到订单库3。然后订单库内，再用hash取模的策略，把不同订单划分到不同的表。

3.3.4 一致性hash

五.分库分表带来的问题

4.1 事务性问题

分库可能导致执行一次事务所需的数据分布在不同服务器上，数据库层面无法实现事务性操作，需要更上层业务引入分布式事务操作，难免会给业务带来一定复杂性，那么要想解决事务性问题一般有两种手段：

方案一：在进行分库分表方案设计过程中，从业务角度出发，尽可能保证一个事务所操作的表分布在一个库中，从而实现数据库层面的事务保证。方案二：方式一无法实现的情况下，业务层引入分布式事务组件保证事务性

2PC3PCTCCSAGA本地消息表最大努力通知

4.2 主键(自增ID)唯一性问题

在数据库表设计时，经常会使用自增ID作为数据主键，这就导致后续在迁库迁表、或者分库分表操作时，会因为主键的变化或者主键不唯一产生冲突，要解决主键不唯一问题，有如下方案：

方案一：使用单独MySQL主键表产生自增id

方案二：Redis自增

4.3 跨库多表join问题

分库分表之后，两张表可能都不在同一个数据库中了，那么如何跨库join操作呢？

跨库Join的几种解决思路：

字段冗余：把需要关联的字段放入主表中，避免关联操作；比如订单表保存了卖家ID（sellerId），你把卖家名字sellerName也保存到订单表，这就不用去关联卖家表了。这是一种空间换时间的思想。全局表：比如系统中所有模块都可能会依赖到的一些基础表（即全局表），在每个数据库中均保存一份。数据抽象同步：比如A库中的a表和B库中的b表有关联，可以定时将指定的表做同步，将数据汇合聚集，生成新的表。一般可以借助ETL工具。应用层代码组装：分开多次查询，调用不同模块服务，获取到数据后，代码层进行字段计算拼装。

4.4 跨库聚合查询问题

4.4.1 分页查询

假设将订单表根据hash(uid%2+1)拆分成了两张表，如下图：

假设现在需要根据订单的时间进行排序分页查询（这里不讨论shardingKey路由，直接全表扫描），在单表中的SQL如下：

select * from t_order order by time asc limit 5 offset 5;

这条SQL非常容易理解，就是翻页查询第2页数据，每页查询5条数据，其中offest=5

假设现在t_order_1和t_order_2中的数据如下：

以上20条数据从小到大的排序如下：

t_order_1中对应的排序如下：

t_order_2中对应的排序如下：

那么单表结构下最终结果只需要查询一次，结果如下：

分表的架构下如何分页查询呢？下面介绍几种方案：

1. 全局查询法

在数据拆分之后，如果还是上述的语句，在两个表中直接执行，变成如下两条SQL：

select * from t_order_1 order by time asc limit 5 offset 5;

select * from t_order_2 order by time asc limit 5 offset 5;

将获取的数据然后在内存中再次进行排序，那么最终的结果如下：

可以看到上述的结果肯定是不对的。

所以正确的SQL改写成如下：

select * from t_order_1 order by time asc limit 0,10;

select * from t_order_2 order by time asc limit 0,10;

也就是说，要在每个表中将前两页的数据全部查询出来，然后在内存中再次重新排序，最后从中取出第二页的数据，这就是全局查询法

该方案的缺点非常明显：

随着页码的增加，每个节点返回的数据会增多，性能非常低服务层需要进行二次排序，增加了服务层的计算量，如果数据过大，对内存和CPU的要求也非常高

不过这种方案也有很多的优化方法，比如Sharding-JDBC中就对此种方案做出了优化，采用的是流式处理 + 归并排序的方式来避免内存的过量占用，有兴趣的可以自行去了解一下。

针对全局查询法的缺点，可以针对不同业务采取折中方案：

2.全局查询法折中方案一：禁止跳页查询法（适合下拉翻页）

数据量很大时，可以禁止跳页查询，只提供下一页的查询方法，比如APP或者小程序中的下拉翻页，这是一种业务折中的方案，但是却能极大的降低业务复杂度

比如第一页的排序数据如下：

那么查询第二页的时候可以将上一页的最大值1664088392作为查询条件，此时的两个表中的SQL改写如下：

select * from t_order_1 where time>1664088392 order by time asc limit 5;

select * from t_order_2 time>1664088392 order by time asc limit 5;

然后同样是需要在内存中再次进行重新排序，最后取出前5条数据

优点：不用返回前两页的全部数据了，只需要返回一页数据，在页数很大的情况下也是一样，在性能上的提升非常大缺点： 不能跳页查询，只能一页一页地查询，比如说从第一页直接跳到第五页，因为无法获取到第四页的最大值，所以这种跳页查询肯定是不行的。

3.全局查询法折中方案二：损失精度查询法（适合对精确性要求没那么高的业务）

数据库分库-数据均衡原理

使用patition key进行分库，在数据量较大，数据分布足够随机的情况下，各分库所有非patition key属性，在各个分库上的数据分布，统计概率情况是一致的。

例如，在uid随机的情况下，使用uid取模分两库，db0和db1：

（1）性别属性，如果db0库上的男性用户占比70%，则db1上男性用户占比也应为70%

（2）年龄属性，如果db0库上18-28岁少女用户比例占比15%，则db1上少女用户比例也应为15%

（3）时间属性，如果db0库上每天10:00之前登录的用户占比为20%，则db1上应该是相同的统计规律

利用这一原理，要查询全局100页数据，offset 9900 limit 100改写为offset 4950 limit 50，每个分库偏移4950（一半），获取50条数据（半页），得到的数据集的并集，基本能够认为，是全局数据的offset 9900 limit 100的数据，当然，这一页数据的精度，并不是精准的。

根据实际业务经验，用户都要查询第100页网页、帖子、邮件的数据了，这一页数据的精准性损失，业务上往往是可以接受的，但此时技术方案的复杂度便大大降低了，既不需要返回更多的数据，也不需要进行服务内存排序了。

4. 二次查找法

有没有一种方法既能满足业务要求，并且不需要折中，性能还高的方法呢？

因此方案相比前三个方案理解起来相对复杂点，为了方便说明，所以先单一DB说起，以下单一DB中保存用户年龄数据，1到30岁，总共30条，如果要查询

select * from T order by age limit 5 offset 10

当对多个数据库中的数据进行分页查询时，首先根据字段进行排序，一般是时间time，这里我们使用age进行排序（排序是二次查找法的关键点，保证最小值的offset一定小于全局offset）

那么会返回以下粉色标识数据，即【11-15】，请记住此结果，下面会讲解怎么分库查询以下结果。

把以上所有数据进行拆分打散存放到3个分库中，如下，注意下面数据只是用户属性年龄，不是分片键：

通过上文介绍，在单一DB中查询limit 5 offset 10,返回了【11-15】结果，那如果在以上三个分库全局查询limit 5 offset 10怎么做？

**关键点：找到各个分片结果集中的最小的一位在全局中的offset **

第一步：语句改写

将 select _ from T order by age limit 5 offset 10 改写为 select _ from T order by age limit 5 offset 3 , 并投递给所有的分库，注意，这个 offset 的 3，来自于全局offset的总偏移量 10，除以水平切分数据库个数 3。

执行select * from T order by age limit 5 offset 3，结果如下（粉色标识数据），为了便于理解用青黄色标识库表前三条数据：

第二步：找到返回数据的最小值

第一个库，5 条数据的 age 最小值是10；第二个库，5 条数据的 age 最小值是 6；第三个库，5 条数据的 age 最小值是 12;

故，三页数据中，age最小值来自第二个库，age_min=6，这个过程只需要比较各个分库第一条数据，时间复杂度很低

第三步：查询二次改写

第一次改写的SQL语句是select * from T order by age limit 5 offset 3 第二次要改写成一个between语句，between的起点是age_min，between的终点是原来每个分库各自返回数据的最大值：

第一个分库，第一次返回数据的最大值是22 所以查询改写为select * from T order by age where age between age_min and 22

第二个分库，第一次返回数据的最大值是20 所以查询改写为select * from T order by age where age between age_min and 20

第三个分库，第一次返回数据的最大值是25 所以查询改写为select * from T order by age where age between age_min and 25

相对第一次查询，第二次查询条件放宽了，故第二次查询会返回比第一次查询结果集更多的数据，假设这三个分库返回的数据如下：

可以看到：

分库一的结果集，比第一次多返回了1条数据，上图中深蓝色记录7

由于age_min来自原来的分库二，所以分库二的返回结果集和第一次查询相同,其实这次查询可以省掉

分库三的结果集，比第一次多返回了3条数据，上图中深蓝色记录8,9,11

第四步：找到age_min在全局的offset

在每个结果集中虚拟一个age_min记录，找到age_min在全局的offset

因为查询语句为 limit 5 offset 3 ，所以查询结果集中每个分库的第一条数据offset为4；

分库一中，根据第一次查询条件得出的10的offset是4，查询又返回了一条数据向前推进一位索引，故虚拟age_min在第一个库的offset是2

分库二没有数据变化所以age_min的offset=4

分库三中，根据第一次查询条件得出的12的offset是4，查询又返回了三条数据向前推进三位索引，故虚拟age_min在第三个库的offset是0

因此age_min的全局offset为：2+4+0=6

既然得到了age_min在全局的offset为6，就有了全局视野，根据第二次的结果集，就能够得到全局limit 5 offset 10的记录（下图黄色标识数据）， 具体计算如下，各分库二次查询结果如下：

分库1：7、10、14、16、21、22

分库2：6、13、17、19、20

分库3：8、9、11、12、15、18、23、25

统一放到list排序后：【6、7、8、9、10、11、12、13、14、15、16、17、18、19、20、21、22、23、25】，得知最小值全局offset为6，最终结果要取offset 10 limit 5,那就10-6=4，把排序后结果，向后推移4位，然后再取5位，那就是【11、12、13、14、15】

优点：可以精确地返回业务所需数据，每次返回的数据量都非常小，不会随着翻页增加数据的返回量、

缺点：需要进行两次查询

4.4.2 order by,group by等聚合函数问题

跨节点的count,order by,group by以及聚合函数等问题，都是一类的问题，它们一般都需要基于全部数据集合进行计算。可以分别在各个节点上得到结果后，再在应用程序端进行合并。

在使用Max、Min、Sum、Count之类的函数进行计算的时候，也需要先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终将结果返回。