MySQL该如何将月增上亿条数据的单表处理方案优雅落地？

落日映苍穹つ 2024-03-25 12:47 78阅读 0赞

### 引言 ###

> 分库分表实战内容基本上很少有人去分享，在网上能够搜出来的也大多属于一些方法论，但大部分技术开发真正缺少的恰恰是这些实操经验，所以后续的内容多以实践为主，携手诸位真正彻底悟透分库分表相关的技术。

尤其是对于库内分表这块的分享，当你去搜索单表数据增长过快该如何处理时，一般都会推荐你做分表处理，但你几乎找不到较为全面的实操教学，网上讲述分表技术更多是停留在表面的理论概念层次做阐述，而本章中则会结合自身之前接触的一个项目业务，再对库内分表技术进行全面阐述~

*PS：虽然当时负责的项目并未达到月增上亿条数据的规模，但处理这种单表数据过大的方案都是一致的，将本文看完最后，无论单月数据增长多少，几百万条、几千万条、甚至几亿条....，相信诸位都能具备处理这类业务的能力！*

### 一、源自于软硬结合的特殊业务 ###

在讲本次主题之前，先来聊聊之前碰到的这个业务，这个业务比较特殊，相信很多小伙伴从未碰到过，这种业务本身用户量不大，甚至可以说用户量非常非常少，因为业务的起源来自于一款硬件设备，但具体的设备类型由于某些缘故就不透露了，可以理解成是下面这个东东：

![format_png][]

虽然当时的硬件设备并不是这个，但也和它很类似，相信大家但凡在超市购过物都认识它，也就是超市收银台的收银机，当时我们是对外提供了一千台设备，这种设备通常一台只有一个用户，所以当时整个系统上线后所有的用户加起来，涵盖后台管理员、超级管理员账号在内，也不过`1200`个用户，这个用户规模相较于常见业务而言属实不多。

> 而当时我们需要负责的就是：为这些设备开发一个操作系统，这里不是指`Windows、Linux、Mac`这类嵌入式的底层系统，而是给机器的操作员开发一个操作界面，就类似于诸位在超市购物时，超市收银员用手操作的那个界面。

因为这些机器本身会安装一个带`UI`的系统，里面也支持安装一些软件，我们的软件会以`GUI`的形式嵌入这些设备，当时我要干的就是直接开发`API`接口，然后提供给`GUI`界面界面调用。本质上就属一个前后端分离的项目，只不过前端从原本的`Web`界面变成了`GUI`界面。

> 大家听起来这个项目是不是特别容易完成，用户量又少代表不需要考虑并发，也不会存在太大的流量冲击，性能要求也不会太高，似乎就是一个简简单单的单体增删改查项目呀？但事情远没有表面这么简单，诸位请接着往下看。

#### 1.1、项目的难点 ####

起初当我收到通知要负责这个需求时，从表面浅显的想了一下，似乎发现也不是太难，就是一个单体项目的`CRUD`工作，以我这手出神入化的`CV`大法，`Hlod`住它简直轻轻松松，因此当时也没想太多就直接接手了，项目初期由于团队每位成员经验都很丰富，各自凭借着个人的`Copy`神功，项目的开发进度可谓是一骑千里，但慢慢的问题来了，而且这个问题还不小！

> 当时大概对外预计分发`1000`台机器，每台机器正式投入运营后，预估单日会产生`500~600`条数据的产出，套到前面的举例中，也就是大概会向几百个超市投放共计`1000`台收银机，每个收银台平均下来之后，大概单日内会有`500~600`个顾客结账！

这里咱们做个数学题：现在有`1000`台机器，每台机器单日就算产生`500`条数据：`1000 * 500 = 500000`，这也就意味着单日的账单表中会新增`50W`条流水数据，单月整个账单表的数据增长量为：`50W * 30 = 1500W`！

> 单月数据增长`1500W`的概念不言而喻，这也就代表着一年的数据增长量为`1500W * 12 = 1.8E`，这批机器投入后预估最少会运行三年起步，甚至十年乃至更久，同时第一批次就要投入`1000`台，后面可能还会有第二批次、第三批次.....的投入。

`50W`只是最低的账单流水数据量，后续正式运营后可能数据量更大，此时架构的设计就成了难题！

#### 1.2、方案的探讨 ####

基本上当时团队的成员中，没人在此之前碰过这类需求，因此开了一个研讨会，去决定该如何将具体的方案落地，这里有人也许会说，数据量这么大，快上分布式/微服务啊！但实则解决不了这个问题，`Why`？因为项目整体的用户量并不大，最多同一时刻也才`1000`并发请求，就算这个并发量再增大几个级别，这里用单体架构优化好了也能够抗住，所以问题并不在业务系统的架构上面，而是在数据落库这方面。

> 这里直接用分库可以吗？答案是也不行，`Why`？因为整个项目中只有账单表才有这么大的数据量，其他的用户表、系统表、功能菜单表、后台表......，基本上不会有太大的数据量，所以直接做分库也没必要，属实有些浪费资源。

有小伙伴可能想到了！在之前的文章中好像聊过[《MySQL的表分区技术》][MySQL]，这里可以按月份对流水表做分区呀！乍一听似乎像那么一回事，但依旧不行，因为第一批机器投入后，单月预计就会产生`1500W`条数据，后续可能会增加机器数量，因此单月的数据量达到`2000W、3000W.....`都有可能，如果按月做表分区，每个分区里面都有几千万条数据，一张账单表的流水随着时间推移，数据量甚至会达到几十亿！

> 一张表中存储几十亿条数据，这基本上不现实，虽然`InnoDB`在数据页为`16KB`尺寸下，单表最多能存储`64TB`数据，有可能这几十亿条数据真的能存下去，但查询时的性能简直令人头大，并且最关键的是不方便后续对数据做维护、管理、备份和迁移工作。

因此经过一番探讨后，最后决定选择了表分区技术的进阶版实现，即单库内做水平分表，按月份对数据做分表，也就是将账单表分为`month_bills_202210、month_bills_202211、month_bills_202212.......`以月份结尾的多张表，每个月的账单流水数据最终都会插入到各自的月份表中。

> 最终架构定型为：业务系统使用单体架构 + 数据库使用单库 + 流水表按月份做水平分表。

### 二、按月分表方案的落地实践 ###

在上一阶段中已经决定好了具体的方案，但又该如何将方案落地呢？首先咱们先把方案落地的思路捋清楚：

*  ①能够自动按月创建一张月份账单表，从而将每月的流水数据写入进去。
 *  ②写入数据时，能够根据当前的日期，选择对应的月份账单表并插入数据。

实现了上面两个需求后，整个方案近乎落地了一半，但接下来该如何去实现相应功能呢？咱们一点点来动手实现。

#### 2.1、利用存储过程实现按月动态创建表 ####

创建表的`SQL`语句大家都不陌生，按月份创建表之前，自然也需要一份原生创建表的`DDL`语句，如下：

CREATE TABLE `month_bills_202211`  (
        `month_bills_id` int(8) NOT NULL AUTO_INCREMENT COMMENT '账单ID',
        `serial_number` varchar(50) NOT NULL COMMENT '流水号',
        `bills_info` text NOT NULL COMMENT '账单详情',
        `pay_money` decimal(10,3) NOT NULL COMMENT '支付金额',
        `machine_serial_no` varchar(20) NOT NULL COMMENT '收银机器',
        `bill_date` timestamp NOT NULL COMMENT '账单日期',
        `bill_comment` varchar(100) NULL DEFAULT '无' COMMENT '账单备注',
        PRIMARY KEY (`month_bills_id`) USING BTREE,
    	UNIQUE `serial_number` (`serial_number`),
    	KEY `bill_date` (`bill_date`)
    )
    ENGINE = InnoDB 
    CHARACTER SET = utf8 
    COLLATE = utf8_general_ci 
    ROW_FORMAT = Compact;
    复制代码

上述的语句会创建一张月份账单表，这张表主要包含七个字段，如下：

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>简介</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>month_bills_id</code></td> 
   <td>月份账单ID</td> 
   <td>主要作为月份账单表的主键字段</td> 
  </tr> 
  <tr> 
   <td><code>serial_number</code></td> 
   <td>流水号</td> 
   <td>所有账单流水数据的唯一流水号</td> 
  </tr> 
  <tr> 
   <td><code>bills_info</code></td> 
   <td>账单详情</td> 
   <td>顾客本次订单中，购买的所有商品详情数据</td> 
  </tr> 
  <tr> 
   <td><code>pay_money</code></td> 
   <td>支付金额</td> 
   <td>本次顾客共计消费的总金额</td> 
  </tr> 
  <tr> 
   <td><code>machine_serial_no</code></td> 
   <td>收银机器</td> 
   <td>负责结算顾客订单的收银机器</td> 
  </tr> 
  <tr> 
   <td><code>bill_date</code></td> 
   <td>账单日期</td> 
   <td>本次账单的结算日期</td> 
  </tr> 
  <tr> 
   <td><code>bill_comment</code></td> 
   <td>账单备注</td> 
   <td>账单的额外备注</td> 
  </tr> 
 </tbody> 
</table>

其中注意的几个小细节：

*  ①日期字段使用的是`timestamp`类型，而并非`datetime`，因为前者更省空间。
 *  ②账单详情字段用的是`text`类型，因为这个字段可能会出现很多的信息。
 *  ③定义了一个和表没有关系的自增字段作为主键，用于维护聚簇索引树的结构。

除开有上述七个字段外，还有三个索引：

[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c2ec8e8c49ce4e738a6ca66e86c8cc4c.png
[MySQL]: https://juejin.cn/post/7166500122166034469