当前我们组管理着一套审核系统，除了数据源是服务端提供的，其余后台管理都是由我们组在维护。
这个系统就是将APP中的各类社交信息送到后台，然后有专门的审核人员来判断信息是否合规，当然在送到后台之前已经让机器审核了一遍。
在去年8月份上线后，日积月累，有张数据表变得比较庞大，截止到目前将近5800W条，数据容量31.21G，每条记录大概是582B。
由于数据量庞大，在检索时也将模糊查询撤掉，并且为了便于查询，还加了很多索引，目前的索引容量都达到了12.2G，审核人员也经常反馈系统使用起来很卡。
一、制订优化方案
在了解到他们的诉求后，我们也展开了优化方案。
1）分表
首先想到的分表，第一种横向分表，也就是将数据以日或月为单位，目前一天的量在20W条左右，一个月的话在600W条左右。
但是这么分的话，在维护上就比较复杂，例如查询时，假设正好遇到跨天或跨月的条件，那么数据组织就比较繁琐了。
第二种分表是利用MySQL的语法：分区表，就是让MySQL来做分表这个粗活，对我们这些使用者来说该怎么查还是怎么查。
工作量都移交给了MySQL，听上去很不错，而且网络上教程一堆，下面是一种范围分区。
CREATE TABLE partition_table ( id INT, date DATETIME ) PARTITION BY RANGE (TO_DAYS(date) ) ( PARTITION p1 VALUES LESS THAN ( TO_DAYS('20211001') ), PARTITION p2 VALUES LESS THAN ( TO_DAYS('20211002') ), PARTITION p3 VALUES LESS THAN ( TO_DAYS('20211003') ), PARTITION p4 VALUES LESS THAN ( TO_DAYS('20211004') ), PARTITION p5 VALUES LESS THAN (MAXVALUE) );
但是有网友说，随着数据量的增加，分区表也会有性能问题，具体达到多少量会有显著的性能问题，我没有深入研究，但是量上去了，总归还是会有点问题的。
分表的方案就这么废弃了，然后想到将数据同步到 ElasticSearch 中，这样的话，检索就毫无压力了，不过数据是需要频繁的更新的，不知道会不会影响ES的性能。
并且改造成本也是巨大的，要改动很多地方，而目前最紧缺的就是人力资源了，即使我们花大力气改造好了，当前测试组也抽不出人手做质量保障。
匆忙上线势必会影响审核人员的使用，虽然系统有这个那个的小毛病，但至少还能稳定的在运行中，也就作罢了。
2）MySQL归档
在仔细思考后，又想到了另一个改造成本最小的方案：MySQL归档。所谓归档就是将大表中的一组数据迁移到另一张表中。
与审核人员一对一沟通后，了解到，其实他们会用到的数据也就是半个月内的，半个月之前的数据很少会用到。
也就是说表中存在很明显地冷热数据，并且冷数据被操作的概率非常低，几乎不会访问。
那我只要每天将这部分冷数据迁移出去，就能保障审核记录表的容量，也就能避免性能问题。
二、实践
我需要编写一个定时任务，在每天下午两点运行，之所以在白天运行是为了遇到问题时，能第一时间响应。
数据归档简单地说，就是先从源表中查询数据，再将数据插入到存量表中，最后删除源表中的数据。
为了保证数据不会误删和遗漏，并且还要保证SQL读写的性能，在编写代码时比较谨慎，预演了多种场景。
1）批量插入
根据审核人员反馈的情况，我会保留记录表两个月的数据，两个月之前的数据全部迁移。
每天的数据量是20W左右，每小时的数据量在1.5W左右，根据这个信息，我会每次取半个小时的数据，批量添加到另一张存量表中。
我采用的ORM系统是 Sequelize，其批量添加的语法采用的是 INSERT INTO VALUES，就是将多条 INSERT 语句合并成一条，我还特地将数据有序排列，提升插入性能。
INSERT INTO `demo_table` (`id`, `uid`, `content`) VALUES ('1', '1001', 'content0'), ('2', '1002', 'content1');
有网友做过实验，批量插入的效率比单条插入高的多，100W的数据量要快21倍左右，1000W的数据要快56倍左右。
还有另一种批量插入的语法是INSERT INTO SELECT，将查询表的结果复制到另一张表中，目标表中任何已存在的行都不会受影响。
insert into `demo_table` select * from `record` where create_time between '2020-08-01 00:00:00' and '2020-08-31 23:59:59';
在搜索文档时有个网友诉说了这种插入方式引起了一个严重的事故，以上面的SQL为例，由于没有为 create_time 配置索引，发生了全表扫描。
当数据量巨大时，数据库就挂起了，无法读写。
2）Sequelize的时间
如果要每次取半个小时的数据，那么就得有一对起始和结束时间，这个好弄，用 moment 库算一下就好了。
但是在使用时发现了问题，下面是采用Sequelize查询方法（find）时打印出的时间范围。
`create_time` >= '2020-08-06 04:00' AND `create_time` < '2020-08-06 05:00'
然后是在调用删除方法（destroy）时打印出的时间范围，可以明显的看出两个时间相差8个小时，也就是存在时区的问题。
`create_time` >= '2020-08-06 12:00' AND `create_time` < '2020-08-06 13:00'
查找相关资料后才得知，Sequelize 为了达到最好的兼容性，其 timezone（时区）默认是 +00:00，在将时间插入到数据库中时都会转换成UTC时间。
上海所在的地区是东八区，所以得到的UTC时间需要减去8小时。那按理说数据库中保存的时间都会减8小时，但是每次在数据库中查询时，显示的时间又是正确的。
这是因为表中的日期字段类型是 TIMESTAMP，它会自动转换成数据库时区的时间，而 DATETIME相当于一个常量，不会做自动转换。
继续回到刚刚的问题，下面是我的查询条件，在调用 find() 时会自动减去8，而 destroy() 就没有这步转换，就会导致查询出来的数据和删除的数据不匹配，出现误删的问题。
const where = { create_time: { $gte: '2020-08-06 12:00', $lt: '2020-08-06 13:00' } };
想到一个办法，那就是取当前时间段的最后一条记录，并且将其ID值作为删除条件，即删除条件改成小于等于指定的ID，但在后面的实践中发现一个隐患。
那就是当ID大的一个记录，如果它的时间比较小，那么就会被误删。延续最后一条记录的思路，将其创建时间作为删除条件，就能让两者匹配了。
顺便说下，为什么不用 ID 来作为区间，主要担心的一个问题是类型溢出。
下面的两条数字，第一条是调用Number.MAX_SAFE_INTEGER，而第二条是MySQL的bigint类型，两者都是所能表示的最大数据范围。
9007199254740991 9223372036854775807
后者要比前者多了三位，那么在Node中做简单的累加时，有可能出现溢出。顺便说一句，Sequelize在从数据库中读取到ID后，会将其作为字符串返回。
3）事务
为了保证先插入，后删除的顺序，引入了事务，保持原子性，一旦出现问题，就回滚。
Sequelize 提供的事务分为托管和非托管，就是手动调用 commit() 和 rollback() 的区别，我采用了非托管。
此处又遇到一个问题，在阿里云上做迁移数据表，运维说需要放到另一个库中，因为两者表名要相同，而sequelize的事务需要由数据库实例调用。
也就是说在完成插入和删除时需要分别创建两个不同的事务，两次commit()。
try { await t1.commit(); await t2.commit(); } catch (error) { await t1.rollback(); await t2.rollback(); }
在上面的代码中，假设 t1完成了提交，t2在提交时发生了问题，进入了 catch() 分支内，那么此处直接调用 t1.rollback() 很可能会报下面的错误。
Error: Transaction cannot be rolled back because it has been finished with state: commit
目前的做法是保证插入一定要成功，也就是保留一个事务，若删除失败，那么就发告警，先手动处理，但感觉这种情况应该也不多。
try { const t1 = await mysql.backend.transaction(); //将数据添批量加进备份表 INSERT INTO VALUES await services.report.insert(list, { transaction: t1 }); const { createTime } = list[list.length - 1]; //删除原表数据 await services.report.del({ createTime: { $lte: createTime } }); await t1.commit(); } catch (error) { // 回滚 await t1.rollback(); console.log(error); // 发送警告 TODO }
4）造数据
为了能模拟数据的批量插入和删除，记录表需要包含充足的数据，所以得写脚本实现。
本来的设想是塞入1000W条数据，每小时加2W条，如下所示，简单粗暴。
for (let i = 0; i < 500; i++) { const list = []; for (let j = 0; j < 20000; j++) { list.push({ createTime: moment("2020-10-01 00:00").add(i, "hours") }); } await services.report.savePatch(list); }
运行时就报栈溢出，只得温柔一点，降低数据量，只赛了150W条数据，每小时加1.5W条，这下终于可以了，可以继续后面的测试了。
FATAL ERROR: Reached heap limit Allocation failed - JavaScript heap out of memory
为了能保障质量，还特地将迁移逻辑包装成一个接口，让QA人员测试。
5）数据清理
在执行定时任务之前，我还会将原表中的数据只保留一个月，并且将表中原有的数据整体迁移至一张备份表中。
在通过 DELETE 命令清理数据时，发生了意外，我本来打算直接删除5000多W条数据，但是直接卡住没有反应，还把表给锁住了。
网上的方案基本都是将需要的数据移到临时表，然后再删除原表，最后修改临时表的名称，但是我的表不能删除，因为数据再不断的插入。
后面改成1000W一个批次，情况也不理想，再缩小，改成500W一批次，现在可以运行了，但是执行了将近半小时。
再缩小范围，改成100W一次删除，就能5分钟完成。
参考资料：
posted on
