当前我们组管理着一套审核系统，除了数据源是服务端提供的，其余后台管理都是由我们组在维护。

　　这个系统就是将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分钟完成。

参考资料：

MySQL 最佳实践 · 分区表基本类型

为什么MySQL不建议使用delete删除数据？

数据归档二三事儿

Insert into select语句引发的生产事故

大批量数据高效插入数据库表

mysql千万级数据分表迁移方案板

关于“时间”的一次探索