Mysql服务不停止迁移分表数据

一、问题产生原因

1. 随着业务数据的增长，原有的表，单表数据量增加过快。
2. 数据增长超出初期预测数据量，且初期没考虑分表
3. 生产环境业务在持续运行，需要热切到分表

二、解决方案

1.借助mysql触发器的特性，在逻辑表（即原表）上建立触发器，当表内数据触发插入后，同时插入到物理分表中。
备注：如果逻辑表还有删除（更新）操作，保证数据准确性，可再配合更新触发器
2.将触发器生效后，之前逻辑表中数据迁移到分表中，可以借助市面上etl工具，或者线下通过程序导入。
3.修改项目分表相关规则，目前使用的是基于jdbc层的shadingsphere，所以配置分表规则即可。上线程序。
4.待上线后，数据不再落到逻辑表中后，即可删除逻辑表触发器。

流程图如下：

 

 

 

 

 三、实战

接下来，进入实施流程：

1. 准备分表

可以参考之前的随笔存储过程实现。

传送门：复制表存储过程

2. 建立触发器

CREATE TRIGGER `trg_paper_question_insert` AFTER INSERT ON `qs_qd_paper_question` FOR EACH ROW begin
    set @shard := mod(new.paper_id, 2);
    case @shard
    when 0 then insert into qs_qd_paper_question0 (`paper_question_id`, `paper_part_id`, `paper_id`, `question_id`, `parent_id`, `question_score`, `sequence`, `is_valid`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) values (new.paper_question_id, new.paper_part_id, new.paper_id, new.question_id, new.parent_id, new.question_score, new.sequence, new.is_valid, new.create_by, new.create_time, new.update_by, new.update_time, new.remark);
    when 1 then insert into qs_qd_paper_question1 (`paper_question_id`, `paper_part_id`, `paper_id`, `question_id`, `parent_id`, `question_score`, `sequence`, `is_valid`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) values (new.paper_question_id, new.paper_part_id, new.paper_id, new.question_id, new.parent_id, new.question_score, new.sequence, new.is_valid, new.create_by, new.create_time, new.update_by, new.update_time, new.remark);
END CASE;
end;

补充说明：

mysql触发器new old:"NEW . column_name"或者"OLD . column_name".在技术上处理（NEW | OLD . column_name）新和旧的列名属于创建了过渡变量（"transition variables"）。

对于INSERT语句,只有NEW是合法的；

对于DELETE语句，只有OLD才合法；

而UPDATE语句可以在和NEW以及OLD同时使用。

3. (非主流程)更新触发器

如果有更新需求，可以再配合更新触发器

CREATE TRIGGER `trg_paper_question_update` AFTER UPDATE ON `qs_qd_paper_question` FOR EACH ROW begin
    select paper_part_id, paper_id, question_id, parent_id, question_score, sequence, is_valid, create_by, create_time, update_by, update_time, remark into @paperPartId, @paperId, @questionId, @parentId, @questionScore, @Sequence, @isValid, @createBy, @createTime, @updateBy, @updateTime, @remark from qs_qd_paper_question where paper_question_id = old.paper_question_id;
    set @shard := mod(@paperId, 32);
    case @shard
    when 0 then update qs_qd_paper_question0 set paper_part_id = @paperPartId, paper_id = @paperId, question_id = @questionId, parent_id = @parentId, question_score = @questionScore, sequence = @Sequence, is_valid = @isValid, create_by = @createBy, create_time = @createTime, update_by = @updateBy, update_time = @updateTime, remark = @remark where paper_question_id = old.paper_question_id;
    when 1 then update qs_qd_paper_question1 set paper_part_id = @paperPartId, paper_id = @paperId, question_id = @questionId, parent_id = @parentId, question_score = @questionScore, sequence = @Sequence, is_valid = @isValid, create_by = @createBy, create_time = @createTime, update_by = @updateBy, update_time = @updateTime, remark = @remark where paper_question_id = old.paper_question_id;
END CASE;
end;

4. 迁移历史数据 

将触发器生效后，之前逻辑表中数据迁移到分表中，可以借助市面上etl工具，或者线下通过程序导入（此流程开发着可自行安排）

5.上线集成分表后的程序

修改项目分表相关规则，目前使用的是基于jdbc层的shadingsphere，所以配置分表规则即可。上线程序

 

 

---

 

以上适用于场景：

1. 热切分库分表

2. 不锁表增加、修改表字段

 

后续在博客发现pt-osc与自己的思路类似，后续可以了解一下，毕竟对于不经常写触发器的开发来说，自己实现，较为复杂，易出错。