Shulou(Shulou.com)06/01 Report--

Percona pt-archiver重构版--大表数据归档工具

相信很多小伙伴们，在日常对接开发时，有很多大表在业务上并没有采取任何形式的切分，数据不停地往一张表里灌入，迟早有一天，磁盘空间报警。作为一个DBA，侧重点是对数据库的操作性能（大表增加字段/索引，QPS等）和存储容量加以考虑，我们会建议开发对数据库里的大表进行数据归档处理，例如将3个月内的订单表保留在当前表，历史数据切分后保存在归档表中，之后归档表从主库上移走以便腾出磁盘空间，并将其迁移至备份机中（有条件的可以将其转换为TokuDB引擎），以便提供大数据部门抽取至HDFS上。

一张大表，我们姑且说1亿条记录，原表我要保存近7天的数据。Percona pt-archiver工具是这样做的，逐条把历史数据insert到归档表，同时删除原表数据。7天数据比如说只有10万行，那么原表会直接删除9990万行记录，操作成本太高，固需要考虑重构。

重构版是这样做的，提取你要保留的7天数据至临时表，然后老表和临时表交换名字，这样大大缩减了可用时间。

具体的工作原理：

1、如果表有触发器、或者表有外键、或者表没有主键或者主键字段默认不是id、或者binlog_format设置的值不是ROW格式，工具将直接退出，不予执行。

2、创建一个归档临时表和原表一样的空表结构。

CREATE TABLE IF NOT EXISTS ${mysql_table}_tmp like ${mysql_table};

3、在原表上创建增，删，改三个触发器将数据拷贝的过程中，原表产生的数据变更更新到临时表里。

DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_insert;CREATE TRIGGER pt_archiver_${mysql_database}_${mysql_table}_insert AFTER INSERT ON ${mysql_table} FOR EACH ROW REPLACE INTO ${mysql_database}.${mysql_table}_tmp ($column) VALUES ($new_column); DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_update;CREATE TRIGGER pt_archiver_${mysql_database}_${mysql_table}_update AFTER UPDATE ON ${mysql_table} FOR EACH ROW REPLACE INTO ${mysql_database}.${mysql_table}_tmp ($column) VALUES ($new_column); DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_delete;CREATE TRIGGER pt_archiver_${mysql_database}_${mysql_table}_delete AFTER DELETE ON ${mysql_table} FOR EACH ROW DELETE IGNORE FROM ${mysql_database}.${mysql_table}_tmp WHERE ${mysql_database}.${mysql_table}_tmp.id OLD.id;

这三个触发器分别对应于INSERT、UPDATE、DELETE三种操作：

（1）INSERT操作，所有的INSERT INTO转换为REPLACE INTO，当有新的记录插入到原表时，如果触发器还未把该记录同步到临时表，而这条记录之前因某种原因已经存在了，那么我们就可以利用REPLACE INTO进行覆盖，这样数据也是一致的;

（3）UPDATE操作，所有的UPDATE也转换为REPLACE INTO，如果临时表不存在原表更新的该记录，那么我们就直接插入该条记录;如果该记录已经同步到临时表了，那么直接进行覆盖插入即可，所有数据与原表也是一致的;

（1）DELETE操作，原表有删除操作，会触发至临时表执行删除。如果删除的记录还未同步到临时表，那么可以不在临时表执行，因为原表中该行的数据已经被删除了，这样数据也是一致的。

4、拷贝原表数据到临时表（默认1000条一批次插入并休眠1秒）

INSERT LOW_PRIORITY IGNORE INTO ${mysql_database}.${mysql_table}_tmp SELECT * FROM ${mysql_database}.${mysql_table} WHERE id>=".$begin_Id." AND id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.