• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL数据归档的几种操作方法介绍

MySQL 彭东稳 7年前 (2017-03-02) 38038次浏览 已收录 6个评论

使用MySQL的过程,经常会遇到一个问题,比如说某张”log”表,用于保存某种记录,随着时间的不断的累积数据,但是只有最新的一段时间的数据是有用的;这个时候会遇到性能和容量的瓶颈,需要将表中的历史数据进行归档。下面来说说几种常见的数据归档方式。

一、使用分区,再利用分区交换技术能够很好地把指定分区中的数据移动到指定表中,这个需要在项目之处就进行此操作。

具体可以看博客分区章节,这几不做介绍。

二、利用存储过程和事件来定期进行数据的导出删除操作。

1 、创建一个新表,表结构和索引与旧表一模一样

2 、新建存储过程,查询30天的数据并归档进新数据库,然后把30天前的旧数据从旧表里删除

3、创建EVENT,每天晚上凌晨00:00定时执行上面的存储过程

备注:第一次执行存储过程的时候因为历史数据过大, 可能发生意外让该次执行没有成功。重新执行时会遇到报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction,应急解决方案如下:

1、执行show full processlist;查看所有MySQL线程。

2、执行SELECT * FROM information_schema.INNODB_TRX\G; 查看是否有错误线程,线程id在show full processlist;的结果中状态为sleep。

3、kill进程id。

另外写存储过程的时候可以控制事务的大小,比如说可以根据时间字段每次归档一天或者更小时间段的数据,这样就不会有大事务的问题,里面还可以加入日志表,每次归档操作的行为都写入日志表,以后查起来也一目了然。

三、使用percona-toolkit的pt-archiver工具来进行历史数据归档,支持删除和不删除元数据的选择。

pt-archiver使用的场景:

1、清理线上过期数据。

2、清理过期数据,并把数据归档到本地归档表中,或者远端归档服务器。

3、两张表之间的数据不完全相同,希望合并。此时加上–ignore或–replace选项,可以轻松实现。

4、导出线上数据,到线下数据作处理。

其它作用:

1、用于清理过期数据purge

注意:--source后的DSN之间不能空格出现,否则会出错。 --where条件的值,有字符串的,要用引号括起来。--limit表示,每组一次删除多少条数据(注意:如果数据比较多时,也可以设置大一些,减少循环次数),最终的清理操作,还是通过Where pK=xx来处理的。

2、用于把数据导出文件,不用删除原表中数据

参数说明:

--statistics:结束的时候给出统计信息:开始的时间点,结束的时间点,查询的行数,归档的行数,删除的行数,以及各个阶段消耗的总的时间和比例,便于以此进行优化。

--where:给出表中要处理的数据的查询条件。

--progress:每处理progress指定的行数后,就打印一次信息。

--no-delete:表示不删除原来的数据,注意:如果不指定此参数,所有处理完成后,都会清理原表中的数据。

--limit:表示每次事务删除多少条数据,默认1条(注意:如果数据比较多时,也可以设置大一些,减少循环次数)。

--txn-size:每个事务提交的数据行数(包括读写操作),批量提交,增加该值可以提升归档性能。

--file:数据存放的文件,最好指定绝对路径,文件名可以灵活地组合(另外,我测试过写文件与不写文件速度几乎差不多,原本以为不写文件速度会快)。

注意字符集问题

Tips:如果你的数据库字符集是utf8的话,需要在运行pt-archive的机器上,在/etc/my.cnf文件中的[client]下面添加default-character-set = utf8,否则导出的文件内容中文会乱码,我就被这个问题坑了。

测试归档

首先压测10万数据。

创建一张归档表,表结构跟原表一样。

开始进行归档表操作,不删除原有表数据记录(如果想删除原表数据需要去掉--no-delete参数即可)

看一下最终处理结果:

从结果看,跟我们理解的有点偏差,少归档了一条数据。但是如果你把归档条件改为 <50000 ,那么结果就又是正确的了,如下展示。

这就要引入另外一个参数了:--safe-auto-increment

此参数的意思就是不归档最大AUTO_INCREMENT的行,默认为Yes。是为了防止在服务器重新启动时重新使用AUTO_INCREMENT值。如果你需要归档最大AUTO_INCREMENT的行,加上--no-safe-auto-increment参数即可。

生产环境中一般都是根据日期来归档数据,比如常见需求保留30天即可,此时where可以这么写 CreateTime <= date_add(now(), interval -30 day)

除了用pt-archiver归档之外,还有一个特别大的用处,我给称之为“无锁导入数据”。在数据归档中还有一种需求(我经常遇到),为了不影响业务在某些情况下会对一些日志表或者其他表做归档,当表特别大时第一次处理此表就不太好处理,并且就算把表数据删除了后而表文件还是无法缩小。这个时候就可以用MySQL的神奇rename命令对表进行重命名,当然是业务允许情况下,如rename table Deal to Deal_201801, Deal_2018 to Deal,此操作是一个原子操作且特别快。做完这个动作之后,一般还会有一个需求就是把原表中某一段时间的数据导入到新的表中,可能是业务跑批需要或者后台查询需要。导数据该怎么弄呢?很自然可能想到使用insert into Deal select * from Deal_201801 where ...导入操作,但是不好意思,在导入数据的时候可能无法对新表进行操作,会导致业务异常。

如果换其他方式呢?写Python或Shell把数据读出来写入到文件,然后再从文件读出循环插入到新表,这当然是可以的。但当数据特别多时,也需要写多线程了。其实这个时候就可以借助pt-archiver进行数据导入了,从老的表读出来然后直接插入到新的表,他的原理与我们上面说的方式类似,但是它更友好,且更快。

四、使用union或union all来进行结果合并

当历史数据进行归档后,这个时候就有需求了。当需要查看历史数据和现有表数据时有没有什么好的方法呢?其实可以使用union或union all来进行多表结果合并操作。

在数据库中,union和union all关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同。union在进行表联接时会筛选掉重复的记录,包括左表去重(会给左表所有字段创建为一个主键),然后再返回结果。

这个语句的执行流程是这样的:

1. 创建一个内存临时表,这个临时表会存入左表字段,创建主键。

2. 执行右表,并存入临时表中。在存入临时表时,如果已经存在了相同条目就会违反唯一性约束,所以插入失败;然后继续执行插入。

3. 临时表中按行取出数据,返回结果,并删除临时表。

可以看到,这里的内存临时表起到了暂存数据的作用,而且计算过程还用上了临时表主键的唯一性约束,实现了 union 的语义。如果表数据量大的话可能会导致用磁盘进行排序。

而union all只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。 从效率上说,union all要比union快很多,也不需要临时表了。所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用union all,语法如下:

使用union组合查询的结果集有两个最基本的规则:

1. 所有查询中的列数和列的顺序必须相同。

2. 数据类型必须兼容。

虽然这个可以简便解决数据查询问题,但是还是需要代码层面的调整。

union还有一个地方可能会用到,如web项目中经常会碰到整站搜索的问题,即客户希望在网站的搜索框中输入一个词语,然后在整个网站中只要包含这个词的页面都要出现在搜索结果中。由于一个web项目不可能用一张表就全部搞定的,所以这里一般都是要用union联合搜索来解决整个问题的。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (21)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!

(6)个小伙伴在吐槽
  1. 再来分享一下昨晚的踩坑经历。MySQL 5.5,将某表(innodb)中大约1G的数据导入到了按月分区的归档表(MyISAM)中,然后直接写了delete语句删除原表中的1G数据,发现极其缓慢,运维在数据库中kill掉这个进程,然后重启数据库,各种操作折腾到晚上9点半。经验:删除MyISAM表数据,可以一次性删除很多数据,但是删除innodb表的数据,建议加limit参数,网上一般加limit 10000,我今天会继续尝试下。
    duohappy2018-01-31 10:02 Windows 10 | Chrome 64.0.3282.119
    • 加limit 100。基本上秒删,写Python脚本循环删除数据
      duohappy2018-02-01 10:11 Windows 10 | Chrome 64.0.3282.119
  2. 讲一下我遇到的业务问题和处理方式,供大家讨论一下 问题:有一个张表,每日新插入数据量是百万级别,业务查询时间需求是近30天 解决方案:保留近30天的数据再原来的表中,建立索引,供业务查询,其余数据放到归档表中,为了保重以后查询归档表不太慢,将归档表按月分区。写Python脚本,每日导出原表数据到归档表,并删除原表相应的数据。 这样做的问题还在于查询归档表时,还是有点慢,基本在1分钟以上,想建立索引,但是没有太好的思路。
    duohappy2018-01-30 10:46 Windows 10 | Chrome 64.0.3282.119
    • 如果能用好索引,并且SQL不太复杂情况时,好像没什么太好的办法了。可以试试别的思路,比如把数据流入到分布式数据库(Greenplum,TiDB)处理试试。
      彭东稳2018-01-30 11:37 Mac OS X | Chrome 63.0.3239.132
      • 业务需要使用的表,建立几个联合索引,查询上也没有很大问题了。但是如果查询归档表,我现在仅仅能做到的就是让查询条件落在某个分区。对分区表建立索引,我在网上搜索了一下,好像必须要包含分区列。数据库用的是MySQL5.5
        duohappy2018-01-30 12:28 Windows 10 | Chrome 64.0.3282.119
      • 用分布式数据库,这个我决定不了 :sad: 。说实话,我仅仅是一名数据分析师,只是觉得查询太慢了,才开始学习建索引,写能利用索引的sql,建立分区。
        duohappy2018-01-30 12:30 Windows 10 | Chrome 64.0.3282.119