MySQl高级篇-分库分表（为什么需要分表？什么时候分库分表）

陈震_

已于 2024-08-10 17:24:35 修改

阅读量1k

点赞数 25

文章标签： mysql oracle 数据库

于 2024-08-10 17:24:26 首次发布

本文链接：https://blog.csdn.net/ShockChen7/article/details/140753498

版权

一、什么是分库分表

分库分表是在海量数据下，由于单库、表数据量过大，导致数据库性能持续下降的问题，演变出的技术方案。

二、为什么分库分表

单机数据库的存储能力、连接数是有限的，它自身就很容易会成为系统的瓶颈。当单表数据量在百万以里时，我们还可以通过添加从库、优化索引提升性能。

一旦数据量朝着千万以上趋势增长，再怎么优化数据库，很多操作性能仍下降严重。为了减少数据库的负担，提升数据库响应速度，缩短查询时间，这时候就需要进行分库分表。

三、什么时候需要分库分表

以 MySQL 数据库为例，单表的数据量在达到亿条级别，通过加索引、SQL 调优等传统优化策略，性能提升依旧微乎其微时，就可以考虑做分库分表了。

阿里的开发手册中有条建议，单表行数超 500 万行或者单表容量超过 2GB，就推荐分库分表，然而理想和实现总是有差距的，阿里这种体量的公司不差钱当然可以这么用，实际上很多公司单表数据几千万、亿级别仍然不选择分库分表。

在这里插入图片描述

四、分库

分库是指将数据拆分到多个数据库中。通常有两种方式：

垂直分库：根据业务模块将不同的表分配到不同的数据库中。
- 例如，将用户信息表放在一个数据库中，将订单信息表放在另一个数据库中。
水平分库：将同一个表的数据按一定规则（如按用户ID分区）拆分到多个数据库中。
- 例如，用户ID为1-10000的数据放在数据库A中，用户ID为10001-20000的数据放在数据库B中。

五、分表

分表是指将一个大表拆分成多个小表。通常也有两种方式：

垂直分表：将一个表按列拆分成多个表。
- 例如，将用户基本信息和用户扩展信息分成两张表。
水平分表：将一个表按行拆分成多个表。
- 例如，按用户ID对用户表进行拆分，用户ID为1-1000的数据放在user_1表中，用户ID为1001-2000的数据放在user_2表中。
- 水平分表尽管拆分了表，但子表都还是在同一个数据库实例中，只是解决了单一表数据量过大的问题，并没有将拆分后的表分散到不同的机器上，还在竞争同一个物理机的 CPU、内存、网络 IO 等。要想进一步提升性能，就需要将拆分后的表分散到不同的数据库中，达到分布式的效果。

六、数据存在哪个库的表

分库分表以后会出现一个问题，一张表会出现在多个数据库里，到底该往哪个库的哪个表里存呢？

上边我们多次提到过一定规则，其实这个规则它是一种路由算法，决定了一条数据具体应该存在哪个数据库的哪张表里。

常见的有 取模算法、范围限定算法、范围+取模算法、预定义算法

1. 取模算法

取模算法是最常见的一种分片算法，通常基于主键（如用户ID）对数据库的数量取模来决定数据存储的位置。

公式：库号 = 主键 % 库的数量。例如，如果有4个数据库，用户ID为123的数据将被存储在123 % 4 = 3号库中。

优点：这种方式的优点是实现简单，数据分布相对比较均匀，不易出现请求都打到一个库上的情况。
缺点：扩容时需要重新分配数据，因为库的数量变化会影响取模结果。当某一台机器宕机，本应该落在该数据库的请求就无法得到处理，等这台机器恢复，用 user_id 作为条件查询用户数据就会少一部分。

2. 范围限定算法

范围限定算法根据一定的范围将数据分配到不同的库和表中。

例子：用户ID在1到10000之间的数据存储在数据库A，用户ID在10001到20000之间的数据存储在数据库B。

优点：同样是实现简单，并且数据迁移和拓展较容易控制，单表数据量是可控的
缺点：数据分布不均衡，可能导致某些库或表的负载过重。比如按时间字段分片时，如果某一段时间（双 11 等大促）订单骤增，存 11 月数据的表可能会被频繁的读写，其他分片表存储的历史数据则很少被查询，导致数据倾斜，数据库压力分摊不均匀。

3. 范围 + 取模算法

范围+取模算法结合了范围限定和取模算法的优点，先通过范围限定缩小数据的分布范围，再使用取模算法进一步确定具体的库和表。
例子：用户ID在1到10000之间的数据存储在数据库A，数据库A内按用户ID对4取模，分成4个表。用户ID在10001到20000之间的数据存储在数据库B，数据库B内按用户ID对4取模，分成4个表。

优点：可以兼顾数据分布的均衡性和扩展的灵活性。
缺点：实现和管理的复杂性较高。

七、分库分表后的问题

显然，系统的数据存储架构演变到现在已经非常复杂了。与拆分前的单库单表相比，现在我们面临着一系列具有挑战性的问题，例如：

1. 事务一致性

分库分表后由于表分布在不同库中，不可避免会带来跨库事务问题。

2. 全局唯一的主键

分库分表后数据库表的主键 ID 业务意义就不大了，因为无法在标识唯一一条记录，例如：多张表 t_order_1、t_order_2 的主键 ID 全部从 1 开始会重复，此时我们需要主动为一条记录分配一个 ID，这个全局唯一的 ID 就叫分布式ID，发放这个 ID 的系统通常被叫发号器。

3. 如何对多数据库进行高效治理？

对多个数据库以及库内大量分片表的高效治理，是非常有必要，因为像某宝这种大厂一次大促下来，订单表可能会被拆分成成千上万个 t_order_n 表，如果没有高效的管理方案，手动建表、排查问题是一件很恐怖的事。

4. 历史数据迁移？

分库分表架构落地以后，首要的问题就是如何平滑的迁移历史数据，增量数据和全量数据迁移，这又是一个比较麻烦的事情，后边详细讲。

5. 如何进行跨节点关联查询？

6. 如何实现跨节点的分页和排序操作？

如果没有很好的工具来支持数据的存储和访问，那么数据一致性将很难得到保障，这就是各种分库分表开发框架的价值所在。

八、分库分表架构模式

分库分表架构主要有两种模式：client 客户端模式和 proxy 代理模式

client 客户端模式

指分库分表的逻辑都在你的系统应用内部进行控制，应用会将拆分后的 SQL 直连多个数据库进行操作，然后本地进行数据的合并汇总等操作。

优点：

客户端实现分片逻辑，分片算法和数据路由逻辑直接在应用程序中实现。
应用程序直接与各个分库分表交互，没有中间代理层。
由于没有中间代理层，减少了网络开销，性能较高。开发者可以根据具体业务需求定制分片逻辑。

缺点：

开发复杂度高：需要在应用程序中实现复杂的分片和路由逻辑。
客户端代码耦合：数据库分片逻辑与应用程序代码耦合度高，维护难度大。
扩展困难：如果需要增加新的分片或调整分片规则，需要修改和重新部署应用程序代码。

在具体实现上，我们通常会将分片规则的处理逻辑打包成一个公共JAR包，其他业务开发人员只需要在代码工程中引入这个JAR包即可。针对这种方案，因为没有独立的服务器组件，所以也不需要专门维护某一个具体的中间件。

然而，这种直接在业务代码中嵌入分片组件的方法也有明显的缺点。一方面，因为分片逻辑侵入到了业务代码中，业务开发人员在理解业务的基础上还需要掌握分片规则的处理方式，增加了开发和维护成本。而且，一旦出现问题，也只能依赖业务开发人员通过分析代码来找到原因，而无法把这部分工作抽离出来让专门的中间件团队进行完成。

基于以上分析，客户端分片在实现上通常会进行进一步的抽象，把分片规则的管理工作从业务代码中剥离出来形成单独演进的一套体系。一种思路是重写JDBC协议，也就是说在JDBC协议层面嵌入分片规则。这样，业务开发人员还是使用与JDBC规范完全兼容的一套API来操作数据库，但这套API的背后却自动完成了分片操作，从而实现对业务代码的零侵入

对于客户端分片，典型的中间件包括阿里巴巴的TDDL以及Apache顶级项目ShardingSphere。因为TDDL并没有开源，所以我们无法判断其使用了哪种客户端分片方案。而对于ShardingSphere而言，它是重写JDBC规范以实现客户端分片的典型实现框架。

proxy 代理模式

代理模式是在应用程序与数据库之间增加一个代理层，分库分表的逻辑由代理层来处理。应用程序只需与代理层交互，而代理层负责将请求转发到相应的数据库和表中。

proxy 代理模式将应用程序与 MySQL 数据库隔离，业务方的应用不在需要直连数据库，而是连接 proxy 代理服务，代理服务实现了 MySQL 的协议，对业务方来说代理服务就是数据库，它会将 SQL 分发到具体的数据库进行执行，并返回结果。该服务内有分库分表的配置，根据配置自动创建分片表。这样，应用层的业务开发人员就不用关心具体的分片规则，而只需要完成业务逻辑的实现。

优点：