(二十三)MySQL分表篇：该如何将月增上亿条数据的单表处理方案优雅落地？

引言

本文为社区首发签约文章，14天内制止转载，14天后未获授权制止转载，侵权必究！

前面《分库分表的正确姿势》、《分库分表的后患问题》两篇中，对数据库的分库分表技能进行了全面论述，但前两篇大多归于办法论，并不存在详细的实战实操，而只要理论没有实践的技能永远都属坐而论道，所以接下来会再开几个单章对分库分表各类计划进行落地。

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

尤其是关于库内分表这块的共享，当你去搜索单表数据添加过快该怎么处理时，一般都会推荐你做分表处理，但你简直找不到较为全面的实操教学，网上叙述分表技能更多是停留在外表的理论概念层次做论述，而本章中则会结合自身之前触摸的一个项目事务，再对库内分表技能进行全面论述~

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类型，由于这个字段或许会呈现许多的信息。
• ③界说了一个和表没有关系的自增字段作为主键，用于维护聚簇索引树的结构。

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

索引字段	索引类型	索引作用
month_bills_id	主键索引	首要作用便是用来维护聚簇索引树
serial_number	仅有索引	当需求依据流水号查询数据时运用
bill_date	仅有联合索引	当需求依据日期查询数据时运用

到这儿就有了最基本的建表句子，首要是用来创立第一张月份账单表，假如想要完结动态依照每月建表的话，还需求用到存储进程来完结，接着来写一个存储进程，但如若关于存储进程语法还不了解的各位小伙伴，这儿就不再做基础解说，可自行阅览之前的《全解MySQL存储进程》。

终究编撰出的存储进程如下：

DELIMITER //
DROP PROCEDURE IF EXISTS create_table_by_month //
CREATE PROCEDURE `create_table_by_month`()
BEGIN
    -- 用于记载下一个月份是多久
    DECLARE nextMonth varchar(20);
    -- 用于记载创立表的SQL句子
    DECLARE createTableSQL varchar(5210);
    -- 履行创立表的SQL句子后，获取表的数量
    DECLARE tableCount int;
    -- 用于记载要生成的表名
    DECLARE tableName varchar(20);
    -- 用于记载表的前缀
    DECLARE table_prefix varchar(20);
  -- 获取下个月的日期并赋值给nextMonth变量
  SELECT SUBSTR(
    replace(
        DATE_ADD(CURDATE(), INTERVAL 1 MONTH),
    '-', ''),
  1, 6) INTO @nextMonth;
  -- 设置表前缀变量值为td_user_banks_log_
  set @table_prefix = 'month_bills_';
  -- 界说表的称号=表前缀+月份，即 month_bills_2022112 这个格局
  SET @tableName = CONCAT(@table_prefix, @nextMonth);
  -- 界说创立表的SQL句子
  set @createTableSQL=concat("create table if not exists ",@tableName,"(
				`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 DEFAULT now() 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;");
  -- 运用 PREPARE 关键字来创立一个预备履行的SQL体
  PREPARE create_stmt from @createTableSQL; 
  -- 运用 EXECUTE 关键字来履行上面的预备SQL体：create_stmt
  EXECUTE create_stmt;
  -- 释放掉前面创立的SQL体（削减内存占用）
  DEALLOCATE PREPARE create_stmt;
  -- 履行完建表句子后，查询表数量并保存再 tableCount 变量中
  SELECT 
    COUNT(1) INTO @tableCount 
  FROM 
    information_schema.`TABLES` 
  WHERE TABLE_NAME = @tableName;
  -- 查询一下对应的表是否已存在
  SELECT @tableCount 'tableCount';
END //
delimiter ;

上述这个存储进程比较长，但基本上都写好了注释，所以阅览起来应该仍是比较轻松的，也包括该存储进程在MySQL5.1、8.0版别中都测验过，所以我们也能够直接用，首要拆解一下里边较尴尬了解的一句SQL，如下：

SELECT SUBSTR(
    replace(
        DATE_ADD(CURDATE(), INTERVAL 1 MONTH),
    '-', ''),
1, 6) INTO @nextMonth;

这条句子履行之后会生成一个202212这样的月份数字，首要用来作为表名的后缀，以此来区别不同的表，但里边用了几个函数组合出了该作用，下面做一下拆解，如下：

-- 在当时日期的基础上添加一个月，如2022-11-12 23:46:11，会得到2022-12-12 23:46:11
select DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
-- 运用空字符替代日期中的 - 符号，得到 20221212 23:46:11 这样的作用
select replace('2022-12-12 23:46:11', '-', '');
-- 对字符串做截取，获取第一位到第六位，得到 202212 这样的作用
select SUBSTR("20221212 23:46:11",1,6);

经过上述拆解之后我们应该能看的很清楚，终究每次调用该存储进程时，都会依据当时数据库的时刻，然后向后添加一个月，一同将格局转化为YYYYMM格局，接下来调用该存储进程，如下：

call create_table_by_month();
+------------+
| tableCount |
+------------+
|        1   |
+------------+

当回来的值为1而并非0时，就表示现已在数据库中查到了前面经过存储进程创立的表，即表示动态创立表的存储进程能够收效！接着为了能够每月守时触发，能够在MySQL中注册一个每月履行一次的守时事件，如下：

create EVENT
    `create_table_by_month_event`  -- 创立一个守时器
ON SCHEDULE EVERY 
    1 MONTH -- 每间隔一个月履行一次
STARTS
    '2022-11-28 00:00:00' -- 从2022-11-28 00:00:00后开端
ON COMPLETION
    PRESERVE ENABLE  -- 履行完结之后不删去守时器
DO 
    call create_table_by_month(); -- 每次触发守时器时履行的句子

MySQL5.1版别中除开引进了存储进程/函数、触发器的支撑外，还引进了守时器的技能，也便是支撑守时履行一条SQL，此刻我们可借助MySQL自带的守时器来守时调用之前的存储进程，终究完结按月守时创立表的需求！

但守时器在运用之前，需求先查看守时器是否敞开，如下：
show variables like 'event_scheduler';
假如是OFF封闭状况，需求经过set global event_scheduler = 1 | on;指令敞开。
假如想要永久收效，MySQL8.0以下的版别可找到my.ini/my.conf文件，然后找到[mysqld]的区域，再里边多加入一行event_scheduler = ON的装备即可。

这儿再附上一些办理守时器的指令：

-- 查看创立的守时器
show events;
select * from mysql.event;
select * from information_schema.EVENTS;
-- 删去一个守时器
drop event 守时器称号;
-- 封闭一个守时器任务
alter event 守时器称号 on COMPLETION PRESERVE DISABLE;  
-- 敞开一个守时器任务
alter event 守时器称号 on COMPLETION PRESERVE ENABLE; 

经过上述几步后，就能够让MySQL自己按月创立表了，但为啥我会将守时器的时刻设置为2022-11-28 00:00:00这个时刻后开端呢？由于202211这张表我现已手动树立了，不将树立表的作业放在月初一号履行，这是由于前面的存储进程是创立下月表，而不是创立当月表，一同月底提早创立下月表，还能进步容错率，在MySQL守时器毛病的情况下，能预留人工介入的时刻。

2.2、写入数据时能够依据月份刺进对应表

作为一个后端项目，必定还需求树立客户端，这儿用SpringBoot+MyBatis来快速构建一个单体项目（终究会给出完好源码），这儿需求注意，月份账单表对应的实体类中要多出一个targetTable字段，如下：

public class MonthBills {
    // 月份账单表ID
    private Integer monthBillsId;
    // 账单流水号
    private String serialNumber;
    // 付出金额
    private BigDecimal payMoney;
    // 收银机器
    private String machineSerialNo;
    // 账单日期
    private Date billDate;
    // 账单概况
    private String billsInfo;
    // 账单补白
    private String billComment;
    // 要操作的方针表
    private String targetTable;
    // 省掉构造办法和Get/Set办法.....
}

上述的实体类与之前的表字段结构简直完全相同，但会多出一个targetTable特点，后续会用来记载要操作的方针表，接着再编撰一个东西类，如下：

public class TableTimeUtils {
    /*
     * 运用ThreadLocal来确保线程安全，或许能够运用Java8新引进的DateTimeFormatter类：
     *      monthTL：担任将一个日期处理成 YYYYMM 格局
    */
    private static ThreadLocal<SimpleDateFormat> monthTL =
            ThreadLocal.withInitial(() ->
                    new SimpleDateFormat("YYYYMM"));
    // 表的前缀
    private static String tablePrefix = "month_bills_";
    // 将一个日期格局化为YYYYMM格局
    public static String getYearMonth(Date date) {
        return monthTL.get().format(date);
    }
    // 获取方针数据的表名（操作单条数据公用的办法：增删改查）
    public static void getDataByTable(MonthBills monthBills){
        // 获取传入目标的时刻
        Date billDate = monthBills.getBillDate();
        // 依据该目标中的时刻，计算出要操作的表名后缀
        String yearMonth = getYearMonth(billDate);
        // 将表前缀和后缀拼接，得到完好的表名，如：month_bills_202211
        monthBills.setTargetTable(tablePrefix + yearMonth);
    }
}

这个东西类首要担任处理日期的时刻格局，以及用来定位要操作的方针表名，关于日期格局化类：SimpleDateFormat由所以线程不安全的，所以运用ThreadLocal来确保线程安全！上述东西类中首要供给了两个基础办法：

• getYearMonth()：将一个日期格局化成YYYYMM格局。
• getDataByTable()：获取单条数据操作时的表名。

有了东西类后，接着来编撰Dao、Mapper层的代码，如下：

@Mapper
@Repository
public interface MonthBillsMapper {
    int deleteByPrimaryKey(Integer monthBillsId);
    int insertSelective(MonthBills record);
    MonthBills selectByPrimaryKey(Integer monthBillsId);
    int updateByPrimaryKeySelective(MonthBills record);
}

上述是月份账单表对应的Dao/Mapper接口，由于我这儿是经过MyBatis的逆向工程文件主动生成的，所以名字便是上面那样，我这边未成更改，接着来看看对应的xml文件，如下：

<insert id="insertSelective" parameterType="com.zhuzi.dbMachineSubmeter.entity.MonthBills">
insert into ${targetTable}
<trim prefix="(" suffix=")" suffixOverrides="," >
  <if test="monthBillsId != null" >
    month_bills_id,
  </if>
  <if test="serialNumber != null" >
    serial_number,
  </if>
  <if test="payMoney != null" >
    pay_money,
  </if>
  <if test="machineSerialNo != null" >
    machine_serial_no,
  </if>
  <if test="billDate != null" >
    bill_date,
  </if>
  <if test="billComment != null" >
    bill_comment,
  </if>
  <if test="billsInfo != null" >
    bills_info,
  </if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
  <if test="monthBillsId != null" >
    #{monthBillsId,jdbcType=INTEGER},
  </if>
  <if test="serialNumber != null" >
    #{serialNumber,jdbcType=VARCHAR},
  </if>
  <if test="payMoney != null" >
    #{payMoney,jdbcType=DECIMAL},
  </if>
  <if test="machineSerialNo != null" >
    #{machineSerialNo,jdbcType=VARCHAR},
  </if>
  <if test="billDate != null" >
    #{billDate,jdbcType=TIMESTAMP},
  </if>
  <if test="billComment != null" >
    #{billComment,jdbcType=VARCHAR},
  </if>
  <if test="billsInfo != null" >
    #{billsInfo,jdbcType=LONGVARCHAR},
  </if>
</trim>
</insert>

上述这么大一长串，其实也不是俺手敲的，仍旧是MyBatis逆向工程生成的代码，但我对其间的一处稍微做了改动，如下：

-- 本来生成的代码是：
insert into month_bills_202211
-- 然后被我改成了：
insert into ${targetTable}

还记得最开端的实体类中，我们多添加的那个targetTable特点嘛？在这儿会依据该字段的值动态的去操作不同月份的表，接着来写一下Service层的接口和完结类，如下：

// Service接口（现在里边只要一个办法）
public interface IMonthBillsService {
    int insert(MonthBills monthBills);
}
// Service完结类
@Service
public class MonthBillsServiceImpl implements IMonthBillsService {
    @Autowired
    private MonthBillsMapper billsMapper;
    @Override
    public int insert(MonthBills monthBills) {
        // 获取要刺进数据的表名
        TableTimeUtils.getDataByTable(monthBills);
        // 回来刺进数据的状况
        return billsMapper.insertSelective(monthBills);
    }
}

在service层现在仅完结了一个刺进数据的办法，其间的逻辑也十分简略，仅仅在调用Dao层的刺进办法之前，获取了一下当时这条数据要刺进的表名，终究来看看Controller/API层，如下：

@RestController
@RequestMapping("/bills")
public class MonthBillsAPI {
    @Autowired
    private IMonthBillsService billsService;
    // 账单结算的API
    @RequestMapping("/settleUp")
    public String settleUp(MonthBills monthBills){
        // 设置账单买卖时刻为当时时刻
        monthBills.setBillDate(new Date(System.currentTimeMillis()));
        // 运用UUID随机生成一个流水号
        monthBills.setSerialNumber(monthBills.getMachineSerialNo() 
            + System.currentTimeMillis());
        // 调用新增账单数据的service办法
        if (billsService.insert(monthBills) > 0){
            return ">>>>账单结算成功<<<<";
        }
        return ">>>>账单结算失败<<<<";
    }
}

在API层首要对外供给了一个账单结算的接口，这儿为了便利测验，所以关于请求办法的处理就没那么严谨了，在调用该接口后，会先获取一下当时体系时刻作为账单时刻，接着会随机生成一个UUID作为流水号，终究就会调用service层的insert()办法。

到这儿停止就树立出了一个最简略的WEB接口，接着来做一个小小的测验，这儿为了便利就不用专门的PostMan东西了，就经过浏览器简略的调试一下，接口如下：
http://localhost:8080/bills/settleUp?billsInfo=白玉竹子*3：9999.999&payMoney=9999.999&machineSerialNo=NF-002-X

终究测验作用图如下：

作用很明显，的确做到了我们需求的作用，接着来看看控制台输出的SQL日志，如下：

首要能够调查到，本来xml中的动态表名，终究会依据月份被替换为详细的表名，终究再来看看数据库中的表是否真实刺进了数据，如下：

由于之前测验过一次，因而表中早有了一条数据，首要调查第二条，的确是我们刚刚测验时刺进的数据，这也就意味着我们按月动态刺进的需求现已完结。

但看到这儿估量绝大部分小伙伴稍微有些懵，毕竟一通代码下来看起来，尤其是不在IDEA东西里边，没那么便利调试，因而终究画一个履行流程图，供给给诸位来整理全体思路！

• ①客户端调用结算接口，传入相关的账单数据，即账单概况、账单金额、收银机器。
• ②API层会先获取当时体系时刻作为账单买卖的时刻，然后调用Service层的刺进办法。
• ③Service层会先依据账单买卖时刻，获取到数据详细要刺进的表名，接着调用Dao层接口。
• ④Dao层会依据上层传递过来的表名，生成详细的SQL句子，然后履行刺进数据的操作。

三、按月分表后要解决的问题

上述现已将最基础的需求做了简略完结，那么接着再剖析一下这些月份账单表还会有哪些需求呢？

• ①除去最基本的新增操作外，还会有删去、修正、查询账单的需求。
• ②一般账单表中的流水数据，都会支撑按时刻进行规模查询操作。

上述这两个需求会是账单表中还会存在的操作，关于第一点也比较简略完结，便是要求客户端在修正、删去、查询数据时，都有必要携带上对应的时刻，一般客户端的修正、删去操作都是依据先查询出数据的基础之上的，而一般查询数据都会依照月份进行查询，或许依据流水号进行查询。

3.1、依据流水号查询数据

还记得前面关于流水号的规划嘛？前面没有太过说明，这儿我们独自拧出来聊一聊：

setSerialNumber(monthBills.getMachineSerialNo()+System.currentTimeMillis());

这儿运用了收银机器序列号+时刻戳作为账单流水号，由于同一台机器在同一时刻内，绝对只能对一个账单进行结算，所以再结合递加的时刻戳，就能够得到一个大局仅有的流水号。System.currentTimeMillis()获取到的时刻戳是13位数字，会放在机器序列号的后边，那接下来假如客户端要依据流水号查询账单数据，又该怎么定位详细的表呢？首要需求在东西类中编撰一个新的办法：

// 依据流水号得到表名
public static void getTableBySerialNumber(MonthBills monthBills){
    // 获取流水号的后13位（时刻戳）
    String timeMillis = monthBills.getSerialNumber().
            substring(monthBills.getSerialNumber().length() - 13);
    // 将字符串类型的时刻戳转换为long类型
    long millis = Long.parseLong(timeMillis);
    // 调用getYearMonth()办法获取时刻戳中的年月
    String yearMonth = getYearMonth(new Date(millis));
    // 用表的前缀名拼接年月，得到终究要操作的表名
    monthBills.setTargetTable(tablePrefix + yearMonth);
}

上面这个办法实践上很简略，便是先解析流水号中的时刻戳，然后依据时刻戳得到详细的年月，终究拼接表的前缀名，得到终究需求操作的表名，接着来写一下Dao层代码，如下：

<!-- 在MonthBillsMapper中多界说一个接口： -->
<!-- MonthBills selectBySerialNumber(MonthBills record); -->
<!-- 界说回来的成果集 -->
<resultMap id="ResultMapMonthBills" type="com.zhuzi.dbMachineSubmeter.entity.MonthBills" >
<constructor >
  <idArg column="month_bills_id" jdbcType="INTEGER" javaType="java.lang.Integer" />
  <arg column="serial_number" jdbcType="VARCHAR" javaType="java.lang.String" />
  <arg column="pay_money" jdbcType="DECIMAL" javaType="java.math.BigDecimal" />
  <arg column="machine_serial_no" jdbcType="VARCHAR" javaType="java.lang.String" />
  <arg column="bill_date" jdbcType="TIMESTAMP" javaType="java.util.Date" />
  <arg column="bill_comment" jdbcType="VARCHAR" javaType="java.lang.String" />
  <arg column="bills_info" jdbcType="LONGVARCHAR" javaType="java.lang.String" />
</constructor>
</resultMap>
<!-- 界说字段列表 -->
<sql id="Base_Column_List" >
    month_bills_id, serial_number, bills_info, pay_money, machine_serial_no,
        bill_date, bill_comment
</sql>
<!-- 编写对应的查询句子,这儿仍旧是经过 ${targetTable} 动态表名做查询 -->
<select id="selectBySerialNumber" resultMap="ResultMapMonthBills"
    parameterType="com.zhuzi.dbMachineSubmeter.entity.MonthBills" >
select 
<include refid="Base_Column_List" />
from ${targetTable}
where serial_number = #{serial_number,jdbcType=VARCHAR}
</select>

接着来写一下Service层的代码，如下：

// 在IMonthBillsService接口中多界说一个办法
MonthBills selectBySerialNumber(MonthBills monthBills);
// 在MonthBillsServiceImpl完结类中编撰详细的完结
@Override
public MonthBills selectBySerialNumber(MonthBills monthBills) {
    // 依据流水号获取要查询数据的详细表名
    TableTimeUtils.getTableBySerialNumber(monthBills);
    // 调用Dao层依据流水号查询数据的办法
    return billsMapper.selectBySerialNumber(monthBills);
}

这儿的完结尤为简略，仅调用了一下前面写的东西类办法，获取了一下要查询数据的动态表名，接着再来写一下API层的接口，如下：

// 依据流水号查询数据的API
@RequestMapping("/selectBySerialNumber")
public String selectBySerialNumber(MonthBills monthBills){
    // 调用Service层依据流水号查询数据的办法
    MonthBills result = billsService.selectBySerialNumber(monthBills);
    if (result != null){
        return result.toString();
    }
    return ">>>>未查询到流水号对应的数据<<<<";
}

接着来做一下测验，调用地址如下：

• http://localhost:8080/bills/selectBySerialNumber?serialNumber=NF-002-X1668494222684

测验作用图如下：

此刻会发现，依据流水号查询数据的作用就完结啦，这儿首要是得规划好流水号的组成，其间一定要包括一个时刻戳在内，这样就能够经过解析流水号的办法，得到详细要查询数据的表名，否则依据流水号查询数据的动作将与众不同的困难，由于需求把全部表扫描一次才能得到数据。

规划好依据流水号查询数据后，关于修正和删去的操作则不再重复编撰啦！由于进程也大致相同，便是在修正、删去时，同样先依据流水号定位到详细要操作的表，接着再去对应表中做相应操作即可。

3.2、按时刻规模查询数据

按时刻规模查询账单的流水数据，这是一切后台办理体系中都支撑的功用，在这个项目中也不例外，但想要完结这个功用，则有必要要有先完结两个功用：

• ①能够依据用户输入的两个时刻规模，得到两个日期之间的一切表名。
• ②能够依据第①步中得到的表名，生成对应的查询句子，能够在单张表、多张表中通用。

上述这两个需求实践上完结起来也并不难，接着来一同做一下！

3.2.1、得到两个日期之间的一切表名

想要完结这个功用，那必定需求再在东西类中编撰一个办法，如下：

// 获取按时刻规模查询时，两个日期之间，一切月份账单表的表名
public static List<String> getRangeQueryByTables(String startTime, String endTime){
    // 声明一个日期格局化类
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
    // 声明保存表名的集合
    List<String> tables = new ArrayList<>();
    try {
        // 将两个传入的字符日期转换成日期类型
        Date startDate = sdf.parse(startTime);
        Date endDate = sdf.parse(endTime);
        //用 Calendar 进行日期比较判别
        Calendar calendar = Calendar.getInstance();
        while (startDate.getTime() <= endDate.getTime()){
            // 把生成的月份拼接表前缀名，加入到集合中
            tables.add(tablePrefix + monthTL.get().format(startDate));
            // 设置日期，并把比对器的日期添加一月
            calendar.setTime(startDate);
            calendar.add(Calendar.MONTH, 1);
            // 获取添加后的日期
            startDate = calendar.getTime();
        }
    } catch (ParseException e) {
        e.printStackTrace();
    }
    // 回来两个日期之间的一切表名
    return tables;
}

该办法需求传入两个参数，即两个字符串类型的时刻，接着会经过Calendar东西类，对两个日期的大小做判别，当开端日期小于结束日期时，则会直接将表前缀名与年月拼接，得到一张月份账单表的表名，接着会对开端日期加一个月，然后持续重复上一步……，直至得到两日期之间的一切表名。

3.2.2、依据表名集合生成对应的SQL句子

想要完结这个功用其实也十分简略，只需求做一堆判别即可，再在东西类中写一个办法：

// 依据日期生成SQL句子的办法
public static String getRangeQuerySQL(String startTime, String endTime){
    // 先获取两个日期之间的一切表名
    List<String> tables = getRangeQueryByTables(startTime, endTime);
    // 提早创立一个字符串目标保存SQL句子
    StringBuffer sql = new StringBuffer();
    // 假如查询的两个日期是同一张表，则直接生成 BETWEEN AND 的SQL句子
    if (tables.size() == 1){
        sql.append("select * from ")
                .append(tables.get(0))
                .append(" where bill_date BETWEEN '")
                .append(startTime)
                .append("' AND '")
                .append(endTime)
                .append("';");
        // 假如本次规模查询的两个日期之间有多张表
    }else {
        // 则用for循环遍历一切表名
        for (String table : tables) {
            // 关于第一张表则只需求查询开端日期之后的数据
            if (table.equals(tables.get(0))){
                sql.append("select * from ")
                        .append(table)
                        .append(" where bill_date > '")
                        .append(startTime)
                        .append("' union all ");
            }
            // 关于终究一张表只需求查询结束日期之前的数据
            else if (table.equals(tables.get(tables.size()-1))){
                sql.append("select * from ")
                        .append(table)
                        .append(" where bill_date < '")
                        .append(endTime)
                        .append("';");
                // 关于其他表则获取一切数据
            } else {
                sql.append("select * from ")
                        .append(table)
                        .append("' union all ");
            }
        }
    }
    // 回来终究生成的SQL句子
    return sql.toString();
}

这个办法看起来好像有些长，但其实功用也十分简略，如下：

• ①假如两个日期在一个月内，则生成BETWEEN AND的查询句子。
• 假如两个日期间隔了多月，则用for循环遍历前面得到的表名：
  • 假如是第一张表，则只需求查询开端日期之后的数据，再用union all拼接后边的句子。
  • 假如是终究一张表，则只需求查询结束日期之前的数据，以;分号结束即可。
  • 假如是中间的表，则查询对应的一切数据，接着持续用union all拼接其他句子。

接着做个简略的小测验，作用如下：

很明显，经过这两个办法，能够完结最初我们提出的两个需求，完结这两个基础功用后，接着套入到前面的项目中~

3.2.3、完结按时刻做规模查询的API接口

仍旧依照之前的过程，先界说Dao层的接口和.xml文件，如下：

// 界说一个回来多条数据的接口
List<MonthBills> rangeQueryByDate(@Param("sql") String sql);

<select id="rangeQueryByDate" resultMap="ResultMapMonthBills"
    parameterType="java.lang.String" >
    ${sql}
  </select>

首要调查xml文件中的代码，由于这儿需求完结自界说SQL的履行，所以将SQL句子的生成作业放在了外部完结，在xml中仅需将对应的SQL句子发给MySQL履行，并接收回来成果即可，接着来写一下Service层的接口和完结：

// 在IMonthBillsService接口中多界说一个办法
List<MonthBills> rangeQueryByDate(String startTime, String endTime);
// 在MonthBillsServiceImpl完结类中编撰详细的完结
@Override
public List<MonthBills> rangeQueryByDate(String startTime, String endTime) {
    // 获取规模查询时的SQL句子
    String sql = TableTimeUtils.getRangeQuerySQL(startTime,endTime);
    return billsMapper.rangeQueryByDate(sql);
}

其实中心作业现已在之前的东西类中完结了，这儿仅需调用东西类中，生成两个日期之间的查询句子即可，接着再写一下API层的对外接口，就功德圆满啦！如下：

// 依照规模查询两个日期之间的一切账单数据
@RequestMapping("/rangeQueryByTime")
public String rangeQueryByTime(@RequestParam("start") String start,
                               @RequestParam("end")String end){
    // 调用Service层依据流水号查询数据的办法
    List<MonthBills> bills = billsService.rangeQueryByDate(start, end);
    if (bills != null){
        return bills.toString();
    }
    return ">>>>指定的日期中没有账单数据<<<<";
}

在这儿边仅仅只是调用了Service层的办法罢了，接下来测验一下，测验地址为：

• localhost:8080/bills/rangeQueryByTime?start=2022-11-01&end=2022-11-30

终究作用如下：

由于我表中就两条数据，所以就做了一个单月表的测验，这儿单月账单表的数据查询无误，我们也能够再树立一张其他月份的账单表，作用也是照样没有问题的~

3.2.4、按时刻规模查询数据小结

其实这儿的做法仅仅只是为了给我们演示作用，之前的实践事务中远比这愈加复杂，由于每张月份账单表会有上千万条数据，不或许一次性查询几张、几十张的月份账单表，这样关于网络、资源的开支太大。

实践事务中，一方面会限制查询的日期规模，最多只允许客户查询近六月的账单流水。另一方面还会结合数据分页，也便是每页仅显现20条数据，随着用户的翻页动作触发后，才会对每张不同的月份账单表做查询。

关于这种会批量查询一切账单表的事务，基本上是查询一些流水买卖金额的统计数据，而且也仅是供给给后台体系操作，用于守时跑批去生成统计数据，如近一周、一月、一季、半年、一年的买卖金额、账单总量…..等这类需求。

这儿给我们完结这个需求的目的在于：让我们了解按月做了水平分表后，该怎么查询多张表的数据。

四、库内分表篇总结

看到这儿，关于库内分表篇的内容也接近了结尾，有小伙伴或许会疑问：那假如我每月的数据量更大怎么办呢？比方前面的例子中，假如再投入了多批机器怎么办？每月的数据量到达3000W、6000W.....乃至上亿怎么办？

如若你存在这块的顾忌，其实大可不必担心，由于我们已然能够按月分表，那能否按半月为周期分表呢？能否按星期分表呢？能否以三天、一天为一个维度分表呢？答案显然是能够的，所以数据量不管有多大，都或许按不同的周期来区分表。

不过一般关于库内分表的场景会很少用到，毕竟库中只要某些表的数据量较大时，才会选用这种计划，假如整库的数据量较大、拜访压力较高，则会直接选用分库计划（不过本篇的内容，关于一些身处东南亚的朋友，应该用的仍是比较频频的~）。

本章内容归于我们简直很少见到的库内分表落地实践，其他一些解说分表技能的材料/文章，基本上仅停留在概念层次的共享，所以如若你看完后对你有少许收获，那请不要忘了点赞支撑哦~，终究也附上项目的《源码地址》，感兴趣的也可自己down下来玩一玩（里边也附带了对应的SQL代码）！

其实库内分表除开本文解说的办法外，我们经过整合Sharding-JDBC框架来完结会愈加轻松，但那样会导致依靠变多，所以假如你项目中不需求用到太多的分表，则可选用本文这种办法完结。