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

一、业务场景

最近博主在项目中遇到了一个棘手的数据问题，目前项目运行过程中会实时新增数据到历史表，频率是5s刷新一次。一次新增数据920条！！这样统计下，一个月平均是4.7亿条数据。这数据量查询起来，直接让mysql数据库崩溃。这边结合实际项目需求决定通过事件调用存储过程实现数据重构，对数据进行水平按月分表。

二、实现方案

1、【分表建表语句】存储过程编写

由于数据是按月分表，我们表名以总表加上”_当前年月“格式命名。这边总表命名为 ’table‘,相应分表为 ’table_202303、table_202304类推‘

CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_name1`()
BEGIN
#创建设备记录表
set @name2 = concat(
'CREATE TABLE IF NOT EXISTS table_', date_format(now(),'%Y%m'),  
"(  
--table表建表语句DDL
");
PREPARE name2 FROM @name2;
EXECUTE name2;

END

2、【总表数据重构】存储过程编写

游标数据来源 查询出你想要合并的表名称、删除总表、重新建表并集

CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_name2`()
BEGIN
DECLARE deviceall VARCHAR(500);

#游标数据来源  查询出你想要合并的表名称
SELECT GROUP_CONCAT(TABLE_NAME) into deviceall FROM information_schema.tables WHERE TABLE_NAME LIKE 'table_%';
#删除总表
DROP table if exists table;
#重新建表并集、搜索引擎使用 MRG_MYISAM  
set @name2= concat(
"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 
");
PREPARE name2 FROM @name2;
EXECUTE name2;


END

这里总表处理是：每次运行事件后，生成当月分表的同时，也会删除总表去重建，使得union语句实时关联到最新的分表。

3、事件计划编写

4、运行测试

这样一来到了指定的时间，就会生成新的分表，我们在代码层面上,只需要根据总表建立相应的实体类等对象即可，新建的总表如下：

由于我们对总表删除重建后选择的搜索引擎是选用 了MRG_MYISAM，这边简单介绍下这类检索引擎：
在以前叫做MERGE，MySQL5.7中叫做MRG_MYISAM，他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。即我们对总表进行新增操作时，数据时插入到我们指定的分表上的，总表一直是空表的状态，具体插入哪张分表，通过存储过程中的“insert_method = last ”指令，告诉MySQL把所有的INSERT语句都发送到合并表的最后一个表上。定义FIRST或LAST是控制插入数据位置的方式。这样一来我们的总表一直是一张空表，里面查询到数据实际上是执行的UNION查寻，关联具体的表会在并集选项体现。

5、注意事项

由于我们存储过程中，对与新表的构建自增id都是从1开始的，数据会存在多条id重复问题，所以我们查询的时候建议不用id作筛选条件。

三、尾言

以上就是对于大数据通过事件、存储过程实现数据根据指定间隔水平分表的过程，感谢阅读。