MySQL分库分表实现

1. 为什么需要分表？

单表存储数据量在超过500万行或存储超过2GB（相对值，具体看表结构设计、MySQL版本、物理机情况等）时，数据写入和读取性能下降，磁盘I/O增多，可能会导致很多问题；因此需要对单表进行分表操作。因MySql InnoDB引擎使用B+树设计索引，当表行数越多时，索引树层级越多，索引使用存储空间越多，写入时耗费的时间越久；当存储达到一定量时，内存无法存放单表的所有索引，就会从磁盘获取索引，增加磁盘I/O，进而影响查询性能。

2. 为什么需要分库？

当数据库的并发请求、存储、连接数等超过一定量时，读写性能下降，需要考虑分库。分库可以分散负载，提高系统的并发处理能力；可以解决单个数据库实例无法有效存储和管理大数据量问题等。

3. 什么场景下使用分表和分库？

数据库的主从结构解决了高可用、读扩展，但单机容量不变，单机写性能无法解决。将数据库进行分库分表操作，可以提高写性能。例如，数据库A原存储在节点1上，写入数据库A的性能瓶颈由节点1决定；当实现数据A分库分表操作后，将数据库A划分为数据库A1、A2、A3和数据库B1、B2、B3，这些数据库可以被存储在节点1、2、3、4、5、6上，原来数据A的写入性能从1个节点提升到6个节点；整体写入容量提升。
分库分表一般是跟随公司业务发展走，业务发展越好，用户越多，数据量越大，请求量越大，单数据库读写性能慢慢下降。单表数据量太大，会极大影响 SQL执行性能。具体也会根据实际情况，做分表、分库操作：

• 分表场景
  某些业务场景，数据存储量高但请求量少，这时可以考虑只做分表操作。
• 分库场景
  某些业务场景，数据存储量不高但并发请求高，这时可以考虑只分库操作。
• 分库分表场景
  某些业务场景，数据存储量和并发请求高，这时可以考虑分库分表操作。

4. 分库分表有哪些方式？

4.1 水平分表

水平分表是指将原本存储在单个数据表中的数据分散存储到多个数据表中。实现水平分表的方法一般有2种：

• 按数据范围分表：根据数据的某个字段取值范围，将数据分散到不同的数据表中。比如，用户ID的奇偶性将数据分散到两个数据表中；
• 按哈希算法分表：根据数据的哈希值，将数据均匀地分散到多个数据表中。这种分表方式可以保证数据在各个表中的分布相对均匀。

4.2 垂直分表

垂直分表是将原本存在单个数据库表中的字段根据字段的不同特性分散到多个数据表中。实现垂直分表的方法一般有2种：

• 按类型分表：将不同类型的字段分散到不同的数据表中。比如，将用户的基本信息和扩展信息分别存储在不同的数据表中。
• 按字段关系分表：将字段之间有关联的数据分散存储到不同的数据表中。比如，将用户的基本信息和订单信息分别存储在不同的数据表中。

4.3 水平分库

水平分库是指将原本属于一个数据库中的数据分散到多个独立的数据库中。实现水平分库的方法一般有2种：

• 按数据范围分库：根据数据的某个字段的取值范围，将数据分散到不同的数据库中；比如，根据用户ID的奇偶性将数据分散发到两个数据库中。
• 按哈希算法分库：根据数据的哈希值，将数据均匀地分散到多个数据库中。这种分库方式可以保证数据在各个库中分布相对均衡。

4.4 垂直分库

垂直分库是指将原本存储在一个数据库中的数据根据表的关系分散到多个数据库中。实现垂直分库的方法一般有2种：

• 按功能分库：将不同功能的数据分散到不同的数据库中。比如，将用户信息和订单信息分别存储在不同的数据库。
• 按表的关系分库：将数据库中的表按照其关系划分到不同的数据库中。比如，将用户表和订单表分别存储在不同的数据库中。

5. 分表实现

5.1 垂直分表

垂直分表基本上是针对业务功能，根据表结构拆分出不同属性或特征的表。

1. 大字段：单独将大字段建在另外的表中，提高基础表的访问性能，原则上在性能关键的应用中应当避免数据库的大字段
2. 按用途：例如企业物料属性，可以按照基本属性、销售属性、采购属性、生产制造属性、财务会计属性等用途垂直切分
3. 按访问频率：例如电子商务、Web 2.0系统中，如果用户属性设置非常多，可以将基本、使用频繁的属性和不常用的属性垂直切分开

5.2 水平切分

水平分表主要针对随时间业务数据量逐渐累积的情况。

• 比如在线电子商务网站，订单表数据量过大，按照年度、月度水平切分
• 网站注册用户、在线活跃用户过多，按照用户ID范围等方式，将相关用户以及该用户紧密关联的表做水平切分
• 论坛的置顶帖，因为涉及到分页问题，每页都需显示置顶贴，这种情况可以把置顶贴水平切分开来，避免取置顶帖子时从所有帖子的表中读取
  下面主要介绍按时间分表的使用：
• 表名按照日期划分table_prefix_date；如创建数据时间为2024-07-06, 按照月划分，表名为：table_20247
• 设计定时任务，创建月表：创建新表table_prefix_date
• 查询、修改、删除时，传入创建时间，根据传入的时间定位到表，再进行查询
• 范围查询时，指定查询范围上限：比如最多查询6个月数据等
• 分页查询时：
  • 1. 设置默认查询上限，比如最近6个月；
  • 2）设置查询默认limit上限，当存在limit超过默认时，要拆分进行多次查询（某些场景，比如数据导出，可能limit为10w\30w\50w\100w等，就需要拆分为几次查询并逐步写入到文件里，最后再返回给用于下载链接并自动下载）；
  • 3）服务内设置缓存，保存最近6个月表对应数据总量（如：{table_20246: total: 10w; table_20245: total: 15w}），在每次翻页查询时根据缓存数据快速定位到需要查询的表和表查询的起始范围；查询完表后在服务内拼接好数据并返回；
  • 4）对于有条件的分页查询，热点查询类可以上 redis\memche等缓存，提高查询效率
    优缺点
• 优点：方便扩容，数据按照连续的字段切分，方便扩容。
• 缺点：1）对于突发节点，可能导致大量数据写入到同一个表中，导致单表的数据量暴增；

6. 分库实现

6.1 垂直拆分（拆库）

对于服务不可用或数据库连接数不够用情况，可以使用垂直分库技术。将一个数据库，拆分成多个提供不同业务数据处理能力的数据库，关注点在于业务相关性。例如拆分所有订单的数据和产品的数据，变成两个独立的库，数据结构发生了变化，SQL 和关联关系也必随之改变。
原来一个复杂 SQL 直接把一批订单和相关的产品都查了出来，现在得改写 SQL 和程序。

• 先查询订单库数据，拿到这批订单对应的所有产品 id
• 再根据产品 id list去产品库查询所有产品信息
• 最后再业务代码里进行组装把一个有很多字段的表给拆分成多个表或库
  每个库表的结构都不一样，每个库表都包含部分字段。
  一般将较少的访问频率很高的字段放到一个表，然后将较多的访问频率很低的字段放到另外一个表。
  因为数据库有缓存，访问频率高的行字段越少，可在缓存里缓存更多行，性能就越好。这个一般在表这个层面做的较多。
  现有中间件都可实现分库分表后，根据你指定的某个字段值，比如userid，自动路由到对应库，然后再自动路由到对应表。

6.2 水平拆分

把一个表的数据给弄到多个库的多个表里，但每个库的表结构都一样，只不过每个库中表放的数据不同，所有库表的数据加起来就是全部数据。将数据均匀地放到更多的库，可以用多个库提高并发量和存储容量。
水平拆表实现有：按range、按字段hash等方式划分表。
按range分表，主要是利用表中存在的连续数据的字段进行划分，比如创建时间；这种方式划分的好处是 方便扩容，但可能存在场景请求都是最新的数据，导致最新时间划分的数据库请求并发量不能降低，无法体现分库带来的并发性能提升；一般range分表，适用于对划分的连续数据字段请求不敏感的或请求比较均匀的表。
按字符按hash分表，能够将数据均匀到不同的数据库，但当数据库容量或并发达到上限时，很难扩容。数据库扩容存在数据迁移的问题，这个过程比较耗时且可能会影响线上业务、也会带来运维的工作量。

7. 分库分表实现

对业务进行分库分表操作后，常见的方式是引入“分库分表中间件”。因为分库分表操作后，涉及数据的编辑、查询、删除等操作时，需要跨数据库、跨表操作，会增加开发的工作量，此时一般会选择引入中间件来解决：

sharding-jdbc（shardingsphere）

最初由当当开源，client层方案。 SQL语法支持较多，支持分库分表、读写分离、分布式id生成、柔性事务（最大努力送达型事务、TCC事务）。被大量公司使用，我司也在用。现在已经升级为Apache组织的项目。
这种client层方案的优点：

• 不用部署，运维成本低，无需代理层的二次转发请求，性能很高
• 但遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合sharding-jdbc的依赖。

Mycat

基于cobar改造，proxy层方案，支持的功能非常完善，社区活跃。但相比sharding jdbc年轻一些。

• proxy层方案的缺点：
  需要部署，自己及运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。

选型

推荐使用sharding-jdbc和mycat：

• 小型公司选用sharding-jdbc，client层方案轻便，而且维护成本低，不需要额外增派人手，而且中小型公司系统复杂度会低一些，项目也没那么多
• 中大型公司最好还是选用mycat这类proxy层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护mycat，然后大量项目直接透明使用即可

参考资料

MySQL 单表不要超过 2000W 行，靠谱吗？
大厂原来都这么对MySQL分库分表!
MySQL分库分表怎么实现？| 详细教程与实例
再有人问你什么是分库分表，直接把这篇文章发给他
Mycat基础知识和运用总结
Mycat-Server