MySQL大表数据归档的几种方法介绍 - 简书

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

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

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

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

create table table_new like table_old;

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

delimiter $

create procedure sp()

begin

insert into tb_new select * from table_old where rectime < NOW() - INTERVAL 30 DAY;

delete from db_smc.table_old where rectime < NOW() - INTERVAL 30 DAY;

end

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

create event if not exists event_temp

on schedule every 1 day

on completion preserve

do call sp();

备注：第一次执行存储过程的时候因为历史数据过大, 可能发生意外让该次执行没有成功。重新执行时会遇到报错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

$ pt-archiver --source h=10.99.73.9,P=3306,u=mha,p=123456,D=sbtest,t=sbtest \

--no-check-charset \

--where 'id<50000' \

--purge \

--limit=2 \

--statistics

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

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

$ pt-archiver --source h=10.99.73.9,P=3306,u=mha,p=123456,D=sbtest,t=sbtest \

--dest h=10.99.73.9,P=3306,u=mha,p=123456,D=sbtest,t=sbtest_201703 \

--no-check-charset \

--where 'id>50000' \

--progress 5000 \

--no-delete \

--file "/tmp/pt-archiver.dat" \

--limit=10000 \

--txn-size=10000 \

--statistics

参数说明：

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

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

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

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

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

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

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

%d Day of the month, numeric (01..31)

%H Hour (00..23)

%i Minutes, numeric (00..59)

%m Month, numeric (01..12)

%s Seconds (00..59)

%Y Year, numeric, four digits

%D Database name

%t Table name

注意字符集问题

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

测试归档

首先压测100万数据。

mysql> create database sbtest charset utf8;

$sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-socket=/var/lib/mysql/mysql.sock --mysql-db=sbtest --db-driver=mysql --tables=1 --table-size=100000 --report-interval=10 --threads=128 --time=120 prepare

mysql> select count(1) from sbtest1;

+----------+

| count(1) |

+----------+

|   100000 |

+----------+

1 row in set (0.04 sec)

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

mysql> show create table sbtest1\G

*************************** 1. row ***************************

       Table: sbtest

Create Table: CREATE TABLE `sbtest` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL DEFAULT '0',

  `c` char(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> CREATE TABLE `sbtest_2018` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`k` int(10) unsigned NOT NULL DEFAULT '0',

`c` char(120) NOT NULL DEFAULT '',

`pad` char(60) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `k` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

开始进行归档表操作，并且删除原有表数据记录（如果不删除原表数据需要加 --no-delete）

$pt-archiver --source h=localhost,u=root,D=sbtest,t=sbtest1 --dest h=localhost,u=root,D=sbtest,t=sbtest1_2018 --no-check-charset --where 'id>25000' --progress 5000 --file "/tmp/pt-archiver.dat" --limit=10000 --txn-size=10000 --statistics

TIME                ELAPSED  COUNT

2018-02-25T16:03:32      0      0

2018-02-25T16:03:34      2    5000

2018-02-25T16:03:37      4  10000

2018-02-25T16:03:39      6  15000

2018-02-25T16:03:41      8  20000

2018-02-25T16:03:43      10  25000

2018-02-25T16:03:43      10  25000

Started at 2018-02-25T16:03:32, ended at 2018-02-25T16:03:43

Source: D=sbtest,h=localhost,t=sbtest1,u=root

Dest:  D=sbtest,h=localhost,t=sbtest1_2018,u=root

SELECT 25000

INSERT 25000

DELETE 25000

Action          Count      Time        Pct

inserting      25000    4.1222      37.47

deleting        25000    3.7808      34.37

print_file      25000    0.1496      1.36

select              4    0.0590      0.54

commit              6    0.0397      0.36

other              0    2.8491      25.90

mysql> select count(*) from sbtest1;

+----------+

| count(*) |

+----------+

|    25001 |

+----------+

1 row in set (0.01 sec)

mysql> select count(*) from sbtest1_2018;

+----------+

| count(*) |

+----------+

|    25000 |

+----------+

1 row in set (0.01 sec)

可以看到数据已经归档完毕，并且删除数据完毕。生产环境中一般都是根据日期来归档数据，比如常见需求保留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在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

如：

select * from test_union1

union

select * from test_union2

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

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

select * from test_union1

union all

select * from test_union2

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

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

2、数据类型必须兼容。

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

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