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