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

引言

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

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

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

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

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

虽然当时的硬件设备并不是这个，但也和它很类似，相信大家但凡在超市购过物都认识它，也就是超市收银台的收银机，当时我们是对外提供了一千台设备，这种设备通常一台只有一个用户，所以当时整个系统上线后所有的用户加起来，涵盖后台管理员、超级管理员账号在内，也不过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的表分区技术》，这里可以按月份对流水表做分区呀！乍一听似乎像那么一回事，但依旧不行，因为第一批机器投入后，单月预计就会产生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;
复制代码

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

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

其中注意的几个小细节：

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

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