开发者社区> 咖啡机(K.F.J)> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:   当前我们组管理着一套审核系统,除了数据源是服务端提供的,其余后台管理都是由我们组在维护。   这个系统就是将APP中的各类社交信息送到后台,然后有专门的审核人员来判断信息是否合规,当然在送到后台之前已经让机器审核了一遍。   在去年8月份上线后,日积月累,有张数据表变得比较庞大,截止到目前将近5800W条,数据容量31.21G,每条记录大概是582B。   由于数据量庞大,在检索时也将模糊查询撤掉,并且为了便于查询,还加了很多索引,目前的索引容量都达到了12.2G,审核人员也经常反馈系统使用起来很卡。
+关注继续查看

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

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



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
史上最全的Java容器集合之equals 和 hashCode
前言 文本已收录至我的GitHub仓库,欢迎Star:github.com/bin39232820… 种一棵树最好的时间是十年前,其次是现在
10 0
教你node.js 模拟自动发送邮件验证码
我们在开发网站时,发送验证码的功能是必定会遇到的,但发送短信验证码是需要付费的,那么邮箱验证码就是一个白嫖的好办法,今天就来教大家用node如何自动发送邮箱验证码。 接下来跟着我的步骤走,就能轻松完成邮箱的自动发送
53 0
Node.js躬行记(12)——BFF
  BFF字面意思是服务于前端的后端,我的理解就是数据聚合层。我们组在维护一个后台管理系统,会频繁的与数据库交互。   过去为了增删改查会写大量的对应接口,并且还需要在Model、Service、Router三层写不同的代码逻辑,吃力不讨好。   为了节约开发时间,构思通用接口,并付诸于实际项目中。虽然简化了Router和Service部分,但其实就是将该部分的代码迁移到了前端页面中。
54 0
Node + Express + Mysql的CMS小结
因为之前用过上述的组合完成过很多系统,而这一次是为了实现一个帮助系统的静态网页发布。因为很久不写,重点说遇到的几个坑: 1、库版本的问题 比如mysql连接数据库一直报错,因为系统重装过,所以重新安装了最新的Node和Mysql,结果死活连接不上,折腾了半天最后发现需要升级一个node-mysq...
1061 0
node.js
关于node.js的误会      http://www.cnblogs.com/dolphinX/p/3475090.html 让我欲罢不能的node.js  http://www.cnblogs.
537 0
node.js模块的坑
在写一个工具的时候,需要将xml转为json方便处理,以前电脑上装的node.js的版本为0.8,结果我再安装node-xml2json时提示版本过低,然后我又重装安装了最新版本。 然后再次尝试安装,首先报python版本不对,不支持3.0,然后再降级,再然后说windows系统需要先安装node-expat,装这个模块又需要先装node-gyp,好吧然后我一直安装失败…   最后我使用了这个模块: node-xml2json   根据thomasfrank大师写的 XML to JSON 改造的,唯一要注意的是如果xml中的节点属性有大写,转换之后全部成小写了。
880 0
Node.js调用mysql的存储过程
例子仅在windows下测试通过,没有放在linux下测试。如有问题,可以电邮给我~ 1、安装node.js、mysql,此处略(自行搜索吧)…; 2、创建一个名为test的数据库,然后建一张名为user_info的表(仅供测试)… 这里假定mysql使用的用户名为root,密码为123456 相应...
1017 0
+关注
咖啡机(K.F.J)
每天进步一点点 研磨生活的香甜
350
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载