Javascript

Node.js躬行记(13)——MySQL归档

本文主要是介绍Node.js躬行记(13)——MySQL归档,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

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

  这个系统就是将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千万级数据分表迁移方案板

关于“时间”的一次探索

 

这篇关于Node.js躬行记(13)——MySQL归档的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!