性能文章>实践了5千万的数据表和重建索引,学到了!>

实践了5千万的数据表和重建索引,学到了!原创

366919

背景

项目中有一张历史记录表,主要用于记录一些接口调用流水,因为该表的地位不是那么重要,当初的创建者并未对核心字段创建索引。

不知不觉这张表的数据已经有5千万数据了,由于没有索引,在排查问题时,发现这种表根本查不动。

于是,决定下手进行分表并建立索引。这张表在系统中只负责插入,影响范围极小,正好拿来练手。

解决思路

我们知道,在Mysql 5.5及之前版本,在运行的生成环境中对大表执行alter操作,会引起表的重建和锁表,影响业务正常运转。

从Mysql 5.6开始,Online DDL特性被引进,运行alter操作时同时允许运行select、insert、update、delete语句。

在数据量小于100w时,可以考虑直接修改表结构建立索引,正常几秒钟就可以完成。但当表的数据量超过百万,无论Mysql 5.6及之前版本的锁表行为、Mysql 5.6中因慢SQL引起的等待,都不允许直接在生产库中进行alter table操作。

目前,五千万的数据,直接修改表来建立索引,肯定是不可行的,弄不好还把数据库给弄崩了。只能想另外的方法。

解决方案

鉴于这张表本身的影响范围有限,想到的解决方案就分表。无论是将所有数据一个区间一个区间的拆分出去,还是将整个表都换成新表,然后再处理历史数据,基本上都要做拆分处理。

基本解决思路:

  • 第一步:创建一张数据结构一样的新表(补全索引),将业务切换至新表,这样新生成的数据便有了索引;
  • 第二步:对旧表数据进行备份,已被后续处理过程中有问题进行恢复;
  • 第三步:按照数据ID,1000万条数据拆分一个表,新拆分的表(补全索引);

对于分表的数据,数据库访问层并未使用,如果业务中有其他地方使用,则可考虑在数据库访问层根据请求时间区间或ID等来切换数据库表名。

基本操作

备份数据

数据库基于阿里云的云服务,导出数据有多种方式,比如直接copy出一张表、基于Navicat导出、基于mysqldump导出等。

copy出一张新表语句如下:

create table account_log_1 select * from account_log;

在测试环境上验证了一下,粗略估计该方式得1小时左右才能执行完数据的备份。

由于没有安装Mysql的linux生产服务器可用,就没采用mysqldump方式导致。

最终,采用在堡垒机上通过Navicat的导出功能,导出内容为SQL语句。

结果也很令人失望,5千万的数据:导出耗时1小时22分钟,导出SQL语句磁盘空间占用38.5G。还好在导出过程中,通过监控查看数据库的整体性能还比较平稳。

为了节省堡垒机的磁盘空间,又花费了十多分钟将38.5G的数据进行压缩,最终占用3.8G的存储空间。

Navicat与mysqldump性能对比

Navicat导出的数据是一条条的insert语句,每一行一条插入语句。

mysqldump导出的数据,多行数据合并成一行插入。批插入减少SQL语法词法解析,减少插事务(最大的开销),较少数据的传输;

数据分区

完成了数据备份,剩下的就是创建不同的新表,并安装分区将数据导入了。

复制表结构

执行表结构的copy:

create table account_log_1 like account_log;

创建一个结构一样的不带数据的表,并对表添加索引。然后再基于添加过索引的表,创建出account_log_2、account_log_3等表。

不同的表机构复制方式有所区别,复制完成之后,注意检查一下新表的主键、索引等是否存在。

由于该表并为具体的实际业务,而且表在设计时缺少创建时间字段,因此就以ID为区分,每1000w条数据一张表。

迁移数据

执行以下语句,直接将前1000w条数据插入到第一张表中:

INSERT INTO account_log_1 SELECT * FROM account_log WHERE id <= 10000000;

执行1000w条数据,用时205秒,大概3分钟25秒。粗略估算,5000万数据如果通过此种方式将全表数据备份,也只需要18分钟左右。

因此,上面到导出操作算是走的弯路,也见证了一下通过Navicat导出的性能问题。

验证数据

执行两条查询语句,验证一下导入新表的数据与原始数据的数据量是否一致:

select count(1) from account_log_1;

select count(1) from account_log WHERE id <= 10000000;

数据条数一致,验证无误。

删除历史数据

已经导入新表的历史数据(备份数据)是可以进行删除的,提升续查询速度。当然,如果该表已经不使用,则也可以暂时保留。

删除语句:

delete from account_log where id <= 10000000; 

这里就暂时不删除了;

循环执行导入

后续操作就是循环执行导入操作了,将id的条件区间进行扩展:

INSERT INTO account_log_2 SELECT * FROM account_log WHERE id <= 20000000 and id > 10000000;

然后循环进行验证、删除等操作,直至整个大表被拆分完毕。

在循环查询插入的时候发现:未删除数据记录的情况下,处于中间部分的数据迁移耗时最长,主要原因就是查询时索引的特性决定的

性能验证

验证count语句耗时:

select count(1) from account_log_2;

耗时,1.8秒查出结果;

顺便验证了一下count(id)、count(*)的查询,发现在1000w数据的情况下,性能差别并不明显。

select count(*) from account_log_2;
select count(id) from account_log_2;

在实验的过程中发现,Mysql可能进行了缓存处理,在第一次查的时候时间较长,后续再查就比较快了。

后续有验证了根据索引查询的效率,1000w数据中查询记录,800毫秒能能查询出结果来,提升效率非常显著;

大表数据迁移思考

经过此次大表数据迁移的实践,对大表迁移有了新的认知和直观感受。单纯的只看技术文章,感觉一切都轻而易举可以实施,但真正实践时才会发现有很懂可提升和改进的地方。

学到和一些值得思考的问题:

  • 大表导出不仅要考虑导出的时间问题,还需要考虑导出数据的空间问题,以及衍生出来的存储和传输问题;
  • 大数据读取与插入是否会造成表的死锁。一般,导出数据没有表锁,导出会对表加锁;
  • 监控导出操作是否会对服务器实例的IO、带宽、内存造成影响,造成内存溢出等。
  • 迁移的数据特殊类型例如(blob)会不会在导入的时候丢失;
  • 不同的引擎之间是否会对导入数据有影响;

通过本篇文章你学到了什么?了解到了什么不曾知道的点?如果有那么一些启发,给点个赞吧。

▲  按关 注”程序新视界“,洞察技术内幕
请先登录,再评论

这可以说是数据库小伙伴必然会经历的一个过程吧!

4月前

为你推荐

5G时代,如何彻底搞定海量数据库的设计与实践
5G时代,业务数据越来越丰富,业务使用MySQL数据库作为后台存储,存储引擎使用InnoDB,会带来哪些挑战?如何针对公司业务特点及MySQL数据库特性,制定若干数据库使用规范供一线RD在设计业务时参
MySQL之KEY分区引发的血案
需求背景业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一。image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id:业务表t
Prometheus时序数据库-数据的插入
前言在之前的文章里,笔者详细的阐述了Prometheus时序数据库在内存和磁盘中的存储结构。有了前面的铺垫,笔者就可以在本篇文章阐述下数据的插入过程。 监控数据的插入在这里,笔者并不会去讨论Promt
Prometheus时序数据库-数据的查询
前言在之前的博客里,笔者详细阐述了Prometheus数据的插入过程。但我们最常见的打交道的是数据的查询。Prometheus提供了强大的Promql来满足我们千变万化的查询需求。在这篇文章里面,笔者
解Bug之路-主从切换"未成功"?
前言数据库主从切换是个非常有意思的话题。能够稳定的处理主从切换是保证业务连续性的必要条件。今天笔者就来讲讲主从切换过程中一个小小的问题。 故障场景最近线上进行主从切换,大部分应用都切过去了,但是某些应
MySQL 死锁套路:一次诡异的批量插入死锁问题分析
线上最近出现了批量insert的死锁,百思不得解。死锁记录如下:```2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB: (1) TRANSACTI
MySQL 死锁套路:唯一索引下批量插入顺序不一致
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。先来做几个小实验,简化的表结构如下:```CREATE TABL
MySQL 死锁套路:再来看一例走不同索引更新的例子
前面有文章介绍了利用调试MySQL源码的方式来调试锁相关的信息,这里利用这个工具来解决一个比较简单的问题,线上的表字段较多,这里简单成为了一个表:```CREATE TABLE `t3` ( `id