mysql（7）- mysql数据库之分库分表

╰半夏微凉° 2022-04-17 03:58 195阅读 0赞

**知识点：**

1.  分库分表的概念及作用
2.  分库分表的实现
    
    1.  分表实现策略：数据库分表能够解决单表数据量很大的时候数据查询的效率问题
    2.  分库实现策略：分库可以解决单台数据库的并发访问压力问题
    3.  分库分表实现策略
3.  分库分表产生的问题
4.  分库分表总结
5.  总结
6.      数据库之互联网常用分库分表方案https://www.cnblogs.com/littlecharacter/p/9342129.html

### 一.缘由和概念 ###

随着业务规模的不断扩大，需要选择合适的方案去应对数据规模的增长，以应对逐渐增长的访问压力和数据量。

关于[数据库][Link 1]的扩展主要包括：业务拆分、主从复制，数据库分库与分表。这篇文章主要讲述数据库分库与分表

**（1）业务拆分**

业务起步初始，为了加快应用上线和快速迭代，很多应用都采用集中式的[架构][Link 2]。随着业务系统的扩大，系统变得越来越复杂，越来越难以维护，开发效率变得越来越低，并且对资源的消耗也变得越来越大，通过硬件提高系统性能的方式带来的成本也越来越高。

因此，在选型初期，一个优良的架构设计是后期系统进行扩展的重要保障。

例如：电商平台，包含了用户、商品、评价、订单等几大模块，最简单的做法就是在一个数据库中分别创建users、shops、comment、order四张表。

![这里写图片描述][SouthEast]

但是，随着业务规模的增大，访问量的增大，我们不得不对业务进行拆分。每一个模块都使用单独的数据库来进行存储，不同的业务访问不同的数据库，将原本对一个数据库的依赖拆分为对4个数据库的依赖，这样的话就变成了4个数据库同时承担压力，系统的吞吐量自然就提高了。

![这里写图片描述][SouthEast 1]

**（2）主从复制**

如何配置主从数据库，以及如何实现数据库的读写分离，这里不再赘述，有需要的可以看我以往的文章。

![这里写图片描述][SouthEast 2]

上图是网上的一张关于[MySQL][Link 1]的Master和Slave之间数据同步的过程图。

主要讲述了MySQL主从复制的原理：数据复制的实际就是Slave从Master获取Binary log文件，然后再本地镜像的执行日志中记录的操作。由于主从复制的过程是异步的，因此Slave和Master之间的数据有可能存在延迟的现象，此时只能保证数据最终的一致性。

**（3）数据库分库与分表**

我们知道每台机器无论配置多么好它都有自身的物理上限，所以当我们应用已经能触及或远远超出单台机器的某个上限的时候，我们惟有寻找别的机器的帮助或者继续升级的我们的硬件，但常见的方案还是通过添加更多的机器来共同承担压力。

我们还得考虑当我们的业务逻辑不断增长，我们的机器能不能通过线性增长就能满足需求？因此，使用数据库的分库分表，能够立竿见影的提升系统的性能，关于为什么要使用数据库的分库分表的其他原因这里不再赘述，主要讲具体的实现策略。请看下边章节。

### 二.分表实现策略 ###

关键字：用户ID、表容量

对于大部分数据库的设计和业务的操作基本都与用户的ID相关，因此使用用户ID是最常用的分库的路由策略。用户的ID可以作为贯穿整个系统用的重要字段。因此，使用用户的ID我们不仅可以方便我们的查询，还可以将数据平均的分配到不同的数据库中。（当然，还可以根据类别等进行分表操作，分表的路由策略还有很多方式）

接着上述电商平台假设，订单表order存放用户的订单数据，sql脚本如下（只是为了演示，省略部分细节）：

CREATE TABLE `order` (
      `order_id` bigint(32) primary key auto_increment,
      `user_id` bigint(32),
       ...
    )

当数据比较大的时候，对数据进行分表操作，首先要确定需要将数据平均分配到多少张表中，也就是：表容量。

这里假设有100张表进行存储，则我们在进行存储数据的时候，首先对用户ID进行取模操作，根据 `user_id%100` 获取对应的表进行存储查询操作，示意图如下：  
![这里写图片描述][SouthEast 3]

例如，`user_id = 101` 那么，我们在获取值的时候的操作，可以通过下边的sql语句：

select * from order_1 where user_id= 101；

其中，`order_1`是根据 `101%100` 计算所得，表示分表之后的第1张order表。

注意：

在实际的开发中，如果你使用MyBatis做持久层的话，MyBatis已经提供了很好得支持数据库分表的功能，例如上述sql用MyBatis实现的话应该是：

接口定义：

/**
      * 获取用户相关的订单详细信息
      * @param tableNum 具体某一个表的编号
      * @param userId 用户ID
      * @return 订单列表
      */
    public List<Order> getOrder(@Param("tableNum") int tableNum,@Param("userId") int userId);

xml配置映射文件：

<select id="getOrder" resultMap="BaseResultMap">
        select * from order_${tableNum}
        where user_id = #{userId}
    </select>

其中`${tableNum}` 含义是直接让参数加入到sql中，这是MyBatis支持的特性。

注意：另外，在实际的开发中，我们的用户ID更多的可能是通过UUID生成的，这样的话，我们可以首先将UUID进行hash获取到整数值，然后在进行取模操作。

### 三.分库实现策略 ###

数据库分表能够解决单表数据量很大的时候数据查询的效率问题，但是无法给数据库的并发操作带来效率上的提高，因为分表的实质还是在一个数据库上进行的操作，很容易受数据库IO性能的限制。

因此，如何将数据库IO性能的问题平均分配出来，很显然将数据进行分库操作可以很好地解决单台数据库的性能问题。

分库策略与分表策略的实现很相似，最简单的都是可以通过取模的方式进行路由。

还是上例，将用户ID进行取模操作，这样的话获取到具体的某一个数据库，同样关键字有：

用户ID、库容量

路由的示意图如下：

![这里写图片描述][SouthEast 4]

上图中库容量为100。

同样，如果用户ID为UUID请先hash然后在进行取模。

### 四.分库分表实现策略 ###

上述的配置中，数据库分表可以解决单表海量数据的查询性能问题，分库可以解决单台数据库的并发访问压力问题。

有时候，我们需要同时考虑这两个问题，因此，我们既需要对单表进行分表操作，还需要进行分库操作，以便同时扩展系统的并发处理能力和提升单表的查询性能，就是我们使用到的分库分表。

分库分表的策略相对于前边两种复杂一些，一种常见的路由策略如下：

1.  中间变量　＝ user\_id%（库数量\*每个库的表数量）
2.  库序号　＝　取整（中间变量／每个库的表数量）
3.  表序号　＝　中间变量％每个库的表数量

例如：数据库有256 个，每一个库中有1024个数据表，用户的user\_id＝262145，按照上述的路由策略，可得：

1.  中间变量　＝ 262145%（256\*1024）= 1;
2.  库序号　＝　取整（1／1024）= 0;
3.  表序号　＝　1％1024 = 1

这样的话，对于user\_id＝262145，将被路由到第０个数据库的第１个表中。

示意图如下：

![这里写图片描述][SouthEast 5]

### 四.分库分表产生的问题  ###

已经谈到了数据库集群之主从集群也就是读写分离，也提到了读写分离其实只是分担了访问的压力，但是存储的压力没有解决。

存储的压力说白了就是随着系统的演化，需求的增加，可能表的数量会逐渐增多，比如一段时间上个新功能就得加个表。并且随着用户量的增多类似用户表的行数肯定会增多，订单表的数据肯定会随着时间而增多，当这种数据量达到千万甚至上亿的时候，读写分离就已经满足不了，读写性能下降严重。

也就是一台服务器的资源例如CPU、内存、IO、磁盘等是有限的，所以这时候分库分表就上啦！

分库

分库讲白了就是比如现在你有一个数据库服务器，数据库中有两张表分别是用户表和订单表。如果要分库的话现在你需要买两台机子，搞两个数据库分别放在两台机子上，并且一个数据库放用户表，一个数据库放订单表

![format_png][]

这样存储压力就分担到两个服务器上了，但是会带来新的问题，所以东西变复杂了都会有新的问题产生。

1、联表查询问题

也就是join了，之前在一个数据库里面可以用上join用一条sql语句就可以联表查询得到想要的结果，但是现在分为多个数据库了，所以join用不上了。就比如现在要查注册时间在2019年之后用户的订单信息，你就需要先去数据库A中用户表查询注册在2019年之后的信息，然后得到用户id,再拿这些id去数据库B订单表中查找订单信息，然后再拼接这些信息返回。所以等于得多写一些代码了。

2、事务问题

搞数据库基本上都离不开事务，但是现在不同的数据库事务就不是以前那个简单的本地事务了，而是分布式事务了，而引入分布式事务也提高了系统的复杂性，并且有些效率不高还会影响性能例如Mysql XA。还有基于消息中间件实现分布式事务的等等这里不展开讲述。

分表

我们已经做了分库了，但是现在情况是我们的表里面的数据太多了，就一不小心你的公司的产品火了，像抖音这种，所有用户如果就存在一张表里吃不消，所以这时候得分表。分别又分垂直分表和水平分表。

1、垂直分表

垂直分表的意思形象点就像坐标轴的y轴，把x轴切成了两半，对应到我们的表就是比如我们表有10列，现在一刀切下去，分成了两张表，其中一张表3列，另一张表7列。

这个一刀切下去让两个表分别有几列不是固定的，垂直分表适合表中存在不常用并且占用了大量空间的表拆分出去。

就拿头条的用户信息，比如用户表只有用户id、昵称、手机号、个人简介这4个字段。但是手机号和个人简介这种信息就属于不太常用的，占用的空间也不小，个人简介有些人写了一坨。所以就把手机号和个人简介这两列拆分出去。

那垂直分表影响就是之前只要一个查询的，现在需要两次查询才能拿到分表之前的完整用户表信息。

2、水平分表

水平分表的意思形象点就像坐标轴的x轴，把y轴切成了两半(当然不仅限于切一刀，可以切好几份)。也拿用户表来说比如现在用户表有5000万行数据，我们切5刀，分成5个表，每个表1000万行数据。

水平分表就适合用户表行数很多的情况下，一般单表行数超过5000万就得分表，如果单表的数据比较复杂那可能2000万甚至1000万就得分了，这个得看实际情况有些表很简单可能一亿行都不用分。所以当一个表行数超过千万级别的时候关注一下，如果没有性能问题就可以再等等看，不要急着分表，因为分表会是带来很多问题。

水平分表的问题比垂直分表就更烦了。

要考虑怎么切，讲的高级点就叫路由

1、按id也就是范围路由

比如id 值1-999万的放一张表，1000万-1999万放一张表，一次类推。这个得试的，因为范围分的大了，可能性能还有问题，范围分的小了。。那表不得多死。

这种分法的好处就是容易切啊，简单粗暴，以后新增的数据分表都不会影响到之前的数据，之前的数据都不需要移动。

2、哈希路由

就是取几列哈希一下看看数据哪个库，比如拿id来做哈希，1500取余8等于4，所以这条记录就放在user\_4这个表中，2011取余8等于3，所以这条记录就放在user\_3中。这种分法好处就是分的很均匀，基本上每个表的数据都差不多，但是以后新增数据又得分表了咋办，以前的数据都得动，比较烦！

3、搞一张表来存储路由关系

还是拿用户表来说，就是弄一个路由表，里面存userId和表编号，表示这个userId是这张user表的的。这种方式也简单，之后又要分表了之后改改路由表，迁移一部分数据。但是这种方法导致每次查询都得查两次，并且如果路由表太大了，那路由表又成为瓶颈了！

再说说查询时候的问题。

比如你要查注册时间最早的前100名用户，这就等于你得在水平分的每一张表都order by 一下注册时间并且取100个，然后再把每个表的100个结果对比一下得到最终的结果。首先操作变麻烦了，以前一个order by就搞定的事情现在变的复杂了，而且还得考虑一个因素就是时间的问题，如果你拆成了20个表，那你得执行20个order by，如果是串行执行的话，这个时间开销也是个问题！

分库分表的实现

具体实现也分为程序代码封装、数据库中间件封装。实现难度会比读写分离更大，至于两种封装的比较在讲读写分离时候已经说了，这里不再赘述。

总结

说了这么多好像分库分表一点都不好啊，没错会引入很多问题，所以在架构设计要遵循演化原则，任何东西都不是一蹴而就的，在不同场景适配不同的架构，架构只有合适的，没有一个架构可以适配任何场景。

在软件中简单够用就是好的，技术没有贵贱，不是用了分布式就牛逼，越复杂的系统维护的成本和难度越高，出现问题的几率越大。这种架构的演化往往都是被用户所驱动的，可以说是"不得已而为之"。

基本上单机数据库可以支撑10万用户量级别。所以一般情况下像数据库吃不消就升级硬件，优化数据库配置、优化代码、引入redis等。只有在真的不行了才上这些更复杂的东西。

### 五.分库分表总结 ###

关于分库分表策略的选择有很多种，上文中根据用户ID应该是比较简单的一种。其他方式比如使用号段进行分区或者直接使用hash进行路由等。有兴趣的可以自行查找学习。

关于上文中提到的，如果用户的ID是通过UUID的方式生成的话，我们需要单独的进行一次hash操作，然后在进行取模操作等，其实hash本身就是一种分库分表的策略，使用hash进行路由策略的时候，我们需要知道的是，也就是hash路由策略的优缺点，优点是：数据分布均匀；缺点是：数据迁移的时候麻烦，不能按照机器性能分摊数据。

上述的分库和分表操作，查询性能和并发能力都得到了提高，但是还有一些需要注意的就是，例如：原本跨表的事物变成了分布式事物；由于记录被切分到不同的数据库和不同的数据表中，难以进行多表关联查询，并且不能不指定路由字段对数据进行查询。分库分表之后，如果我们需要对系统进行进一步的扩阵容（路由策略变更），将变得非常不方便，需要我们重新进行数据迁移。

最后需要指出的是，分库分表目前有很多的中间件可供选择，最常见的是使用淘宝的中间件mycat。

## 六、总结 ##

上述中，我们学到了如何进行数据库的读写分离和分库分表，那么，是不是可以实现一个可扩展、高性能、高并发的网站那？很显然还不可以!一个大型的网站使用到的技术远不止这些，可以说，这些都是其中的最基础的一个环节，因为还有很多具体的细节我们没有掌握到，比如：数据库的集群控制，集群的负载均衡，灾难恢复，故障自动切换，事务管理等等技术。因此，还有很多需要去学习去研究的地方。

此文转载于其他博主，因未找到其原创博主就不放原文链接了。

[Link 1]: http://lib.csdn.net/base/mysql
[Link 2]: http://lib.csdn.net/base/architecture
[SouthEast]: /images/20220417/0cba99cb0c03407e8fb42e1d930cf90c.png
[SouthEast 1]: /images/20220417/9139c66e376f4a7781aeadcb9649e9ed.png
[SouthEast 2]: /images/20220417/0c18d0d92e4d4d07bfce1266a9dd1492.png
[SouthEast 3]: /images/20220417/8e940c16892d46bcbf6c9733f8d4b918.png
[SouthEast 4]: /images/20220417/309ad0893c9a4f8794236677c190f4fa.png
[SouthEast 5]: /images/20220417/1c34b5c3acc54afeb59e649367798a54.png
[format_png]: /images/20220417/d3ba9b05d70449bdb4e2626850e68240.png