通过事件以及存储过程，让mysql按指定间隔时间段建表，从而实现数据水平分表

一、业务场景最近博主在项目中遇到了一个棘手的数据问题目前项目运行过程中会实时新增数据到历史表频率是5s刷新一次。一次新增数据920条这样统计下一个月平均是4.7亿条数据。这数据量查询起来直接让mysql数据库崩溃。这边结合实际项目需求决定通过事件调用存储过程实现数据重构对数据进行水平按月分表。二、实现方案1、【分表建表语句】存储过程编写由于数据是按月分表我们表名以总表加上”_当前年月“格式命名。这边总表命名为 ’table‘,相应分表为 ’table_202303、table_202304类推‘CREATEDEFINERrootlocalhostPROCEDUREprocedure_name1()BEGIN#创建设备记录表setname2concat(CREATE TABLE IF NOT EXISTS table_,date_format(now(),%Y%m),( --table表建表语句DDL );PREPAREname2FROMname2;EXECUTEname2;END2、【总表数据重构】存储过程编写游标数据来源 查询出你想要合并的表名称、删除总表、重新建表并集CREATEDEFINERrootlocalhostPROCEDUREprocedure_name2()BEGINDECLAREdeviceallVARCHAR(500);#游标数据来源 查询出你想要合并的表名称SELECTGROUP_CONCAT(TABLE_NAME)intodeviceallFROMinformation_schema.tablesWHERETABLE_NAMELIKEtable_%;#删除总表DROPtableifexiststable;#重新建表并集、搜索引擎使用 MRG_MYISAMsetname2concat(CREATE TABLE IF NOT EXISTS table ( id int NOT NULL AUTO_INCREMENT, ...... PRIMARY KEY (id) USING BTREE ) ENGINE MRG_MYISAM AUTO_INCREMENT 1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci UNION (,deviceall,) insert_method last );PREPAREname2FROMname2;EXECUTEname2;END这里总表处理是每次运行事件后生成当月分表的同时也会删除总表去重建使得union语句实时关联到最新的分表。3、事件计划编写4、运行测试这样一来到了指定的时间就会生成新的分表我们在代码层面上,只需要根据总表建立相应的实体类等对象即可新建的总表如下由于我们对总表删除重建后选择的搜索引擎是选用 了MRG_MYISAM这边简单介绍下这类检索引擎在以前叫做MERGEMySQL5.7中叫做MRG_MYISAM他的内部没有数据真正的数据依然是MyIsam引擎的表中但是可以直接进行查询、删除更新等操作。即我们对总表进行新增操作时数据时插入到我们指定的分表上的总表一直是空表的状态具体插入哪张分表通过存储过程中的“insert_method last ”指令告诉MySQL把所有的INSERT语句都发送到合并表的最后一个表上。定义FIRST或LAST是控制插入数据位置的方式。这样一来我们的总表一直是一张空表里面查询到数据实际上是执行的UNION查寻关联具体的表会在并集选项体现。5、注意事项由于我们存储过程中对与新表的构建自增id都是从1开始的数据会存在多条id重复问题所以我们查询的时候建议不用id作筛选条件。三、尾言以上就是对于大数据通过事件、存储过程实现数据根据指定间隔水平分表的过程感谢阅读。补充当后期手动删除掉一些分表后需要手动执行重构总表函数同步分表信息