数据库分库分表如何实现，MySQL实现分库分表

最新推荐文章于 2024-04-22 15:16:37 发布

程序员柳

最新推荐文章于 2024-04-22 15:16:37 发布

阅读量392

点赞数

分类专栏：数据库文章标签：数据库分布式 java

本文链接：https://blog.csdn.net/2301_76484015/article/details/129331250

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

分库分表

为什么要分表？

以mysql的主键索引为例，使用的是B+树，根据B+树的特点，它的叶子节点存储的是实际的数据，非叶子节点存储索引和指针，mysql将B+树的一个节点大小控制为一页大小（把BTree树的一个节点大小设计为一个存储单元即为一页（16K）），每一次IO读取的数据刚好为一页，即每次磁盘IO刚好读取B+树的一个节点内容，那么一个3层的B+树需要3次磁盘IO才能查找到对应的数据，而磁盘IO的成本是比较高的，为了有较好的性能，我们肯定也希望IO次数越少越好，所以我们以3层的B+树为例（3层及以下都有较高的性能），那么它能存储多少数据量呢？

假设我们的表中一条记录大小为1K，那么B+树一个叶子节点可以存储，16K/1K=16条记录，而非叶子节点，只存储索引和指针，以主键bigint为例长度8字，指针大小为6字节，共14字节，一页大小 16K = 16384字节，所以一个非叶子节点可以存储索引数为 16384/14 = 1170 ，则一个3层的B+数可以存放 1170 * 1170 * 16 = 21902400条记录，也就是说2000万以下任意的数据量，3层的B+树都需要3次IO，性能没多大区别，如果层次越深，IO次数越多，性能也就越低。

当然单表的数据量2190W 是理论数据量，实际应用中一条记录的大小可能不止1K，那数据量可能就会更少。

就以我个人的经验来说，单表的数据量达到百万级以上，性能就会有所下降（当然这只是我个人在开发中遇到过这样的问题）

随着数据量的增长，当单表使用主键查询的时候都很慢的情况下，我们就不得不考虑分库分表了。

分库和分表并非要同时存在，有的时候我们可能只需要分表就够了，有的时候我们可能只需要分库，比如读写分离库等。

那么为什么要分库呢？

单表有写入或者查询等性能问题需要分表，那么分库又是为了什么呢？

其实，就像我们的应用系统一样，为什么要把应用系统部署多个服务器，那是因为单个服务器的性能不够，并发请求量高的时候已经无法满足了，数据库也有同样的问题，毕竟它也是部署在服务器上的，当并发请求数高的时候，单个数据库已经不能不能满足更高的并发请求时，就不得不进行分库了，最直观的比如数据库连接数，mysql服务允许的最大连接数为16384.虽然可以通过连接池可以一定程序上优化连接，但是当数据量过大时，无法再拿到数据库连接时，也就无法访问到数据库了，这个时候无论表的性能怎么样，数据库都不能提供服务了，那就只能分库了。

分表

即把单表的数据拆分到多张表中，表的结构是相同的。像我们的业务系统十几年前就遇到了单表查询性能问题，当时的做法是每到新的月份时，建一张历史表（表名+月份明白），然后将当前表中的数据复制到历史表中去，查询分为当前数据查询和历史数据查询，好处是没有查询性能问题了，坏处是业务变的复杂了，还有就是打开数据库你会看到几百张相同前缀的表。

分库

分库，两个库可以数据完全相同比如读写分离库，写的时候在一个库，读的时候从另外一个库读，这种情况要求两个库的数据完全一致。另外也可以用作分割数据，比如把一部分数据存在这个库上，另一部分数据放在另一个库上等。

不管是分库还是分表，最终的结果无非就是数据的切分，数据切分分为两种方式：

1. 垂直切分（又称纵向切分）

垂直分库：将原来的单一系统，按照不同的业务，分成各个小服务，每个服务有自己的数据库等，与"微服务治理"的做法相似，每个微服务使用单独的一个数据库

垂直分表：将单个大表，分成多个小表，即大表拆小表。一般是表中的字段较多，将不常用的，数据较大，长度较长（比如text类型字段）的拆分到“扩展表“，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能

2. 水平切分（又称横向切分）

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了

水平分表：针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈

水平分库分表：将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

分库分表中间件

中间件	优点	缺点
Cobar	提供MySQL分布式服务的proxy 层方案，对应用保持透明（对应用来说它看上还是一个数据库）	不支持读写分离、存储过程、跨库 join 和分页等操作，项目已停止维护
TDDL	client 层方案，主要用于解决分库分表场景下的访问路由（持久层与数据访问层的配合）以及异构数据库之间的数据同步，它是一个基于集中式配置的JDBC DataSource实现，具有分库分表、Master/Salve、动态数据源配置、主从动态切换等功能	不支持 join、多表查询等语法，依赖淘宝的 diamond 配置管理系统
Sharding-jdbc（ShardingSphere）	client 层方案，基于SQL进行拦截、解析、改写等，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）等，支持mysql、postgresql、oracle等多种数据库，它是一个jar包在应用中配置即可使用，不用额外部署，运维成本低，不需要代理层的二次转发请求，性能很高	支持有限的SQL语法、官方虽然有文档，但是版本较多，不同的版本配置又不同，给人带来困扰，不支持动态扩容，需要一开始就规划好库和表的数量等
Mycat	基于 Cobar的二次开发产物，proxy 层方案，任意跨库跨表join查询、支持跨库跨表非关联子查询、支持跨库跨表关联子查询等，支持mysql、postgresql、oracle等多种数据库	因为mycat是一个服务，需要安装它，那么就需要额外的服务器，同时性能相对较差

目前主要就ShardingSphere和Mycat 两种选择，根据自己公司的情况选择不同的方案

分库分表后面临的问题

1. 事务一致性问题

如果分库，就不可避免会带来跨库事务问题。

解决方案就是在上一篇中[cap、base 理论，分布式事务解决方案等](https://gitee.com/javajov/java-senior-engineer-interview)介绍的那样，采用不同的分布式事务。

当然，分布式事务也有一些问题，分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点，推后了提交事务的时间点，延长了事务的执行时间。导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节点的增多，这种趋势会越来越严重，从而成为系统在数据库层面上水平扩展的枷锁。

2. 跨库关联查询 join 问题

分库后数据可能分布在不同的节点上，此时join带来的问题就比较麻烦了（虽然上面的一些中间件也支持join查询，但支持的有限）

解决方案：

全局表 ：如果join的是所有模块都可能依赖的一些表，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。

字段冗余设计：为了性能而避免join查询，将要join的字段添加到当前表中做字段冗余设计，比如用户姓名字段，查询订单时为了避免join用户表查询姓名，直接将姓名存到订单表中，当然这样设计也会有问题，比如用户姓名修改后，订单表中的数据也要随之修改等等一系列问题。

由业务系统组装：还是以订单表为例，比如先将订单数据查出来，再去遍历每条数据，找到根据用户ID查询对应的名称等，缺点是数据库查询的次数变多了。

ER分片 ：将那些存在关联关系的表记录存放在同一个分片上，那么就能较好的避免跨分片join问题

3. 跨库分页、排序等问题

跨节点多库进行查询时，会出现limit分页、order by排序等问题。分页需要按照指定字段进行排序，当排序字段就是分片字段时，通过分片规则就比较容易定位到指定的分片；当排序字段非分片字段时，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给用户