pt-archiver归档数据丢失
pt-archiver相信大家都非常熟悉了，该工具可以按指定条件导出数据到文件，也可以按指定条件清理数据，同时也可以按条件把数据归档到另外一个表。最近在工作中无意发现在某种场景下会导致归档数据丢失。
下面建立表复现数据丢失的场景：
通过把表t1的数据归档到t2，条件是insert_time >= "2018-01-01 00:00:00" and insert_time <="2018-12-31 23:59:59"
CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `insert_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `insert_time` (`insert_time`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8
t1，t2表结构相同。其中t1的数据如下：
[root@localhost][yayun]> select * from t1; +----+---------------------+ | id | insert_time | +----+---------------------+ | 1 | 2017-10-01 14:00:00 | | 2 | 2018-01-01 14:00:00 | | 3 | 2018-01-01 14:00:00 | | 4 | 2018-02-01 14:00:00 | | 5 | 2018-02-01 14:00:00 | | 6 | 2018-03-01 14:00:00 | | 7 | 2018-03-01 14:00:00 | | 8 | 2018-04-01 14:00:00 | | 9 | 2018-04-01 14:00:00 | | 10 | 2018-05-01 14:00:00 | | 11 | 2018-05-01 14:00:00 | | 12 | 2018-06-01 14:00:00 | | 13 | 2018-06-01 14:00:00 | | 14 | 2018-07-01 14:00:00 | | 15 | 2018-07-01 14:00:00 | | 16 | 2018-08-01 14:00:00 | | 17 | 2018-08-01 14:00:00 | | 18 | 2018-09-01 14:00:00 | | 19 | 2018-09-01 14:00:00 | | 20 | 2019-06-01 14:00:00 | +----+---------------------+
可以看见符合insert_time >= "2018-01-01 00:00:00" and insert_time <="2018-12-31 23:59:59"这个条件的有18条数据。
使用命令进行归档【pt-archiver 3.3.1】：
pt-archiver --source h=127.0.0.1,P=3307,u=root,p=xx,D=yayun,t=t1,i=insert_time --dest h=127.0.0.1,P=3307,u=root,p=xx,D=yayun,t=t2 --no-safe-auto-increment --charset=utf8 --where 'insert_time >= "2018-01-01 00:00:00" and insert_time <="2018-12-31 23:59:59"' --progress 5000 --txn-size 10000 --local --no-delete --bulk-insert --limit=3 --statistics --why-quit
TIME ELAPSED COUNT 2021-06-25T14:24:12 0 0 2021-06-25T14:24:12 0 14 Started at 2021-06-25T14:24:12, ended at 2021-06-25T14:24:12 Source: A=utf8,D=yayun,P=3307,h=127.0.0.1,i=insert_time,p=...,t=t1,u=root Dest: A=utf8,D=yayun,P=3307,h=127.0.0.1,i=insert_time,p=...,t=t2,u=root SELECT 14 INSERT 14 DELETE 0 Action Count Time Pct bulk_inserting 5 0.0031 14.87 select 6 0.0025 11.95 commit 2 0.0005 2.21 print_bulkfile 14 -0.0000 -0.02 other 0 0.0147 71.00 Exiting because there are no more rows.
可以看见只归档了14条数据，这是啥情况。我们直接查看表看一下t2数据。
[root@localhost][yayun]> select * from t2; +----+---------------------+ | id | insert_time | +----+---------------------+ | 2 | 2018-01-01 14:00:00 | | 3 | 2018-01-01 14:00:00 | | 4 | 2018-02-01 14:00:00 | | 6 | 2018-03-01 14:00:00 | | 7 | 2018-03-01 14:00:00 | | 8 | 2018-04-01 14:00:00 | | 10 | 2018-05-01 14:00:00 | | 11 | 2018-05-01 14:00:00 | | 12 | 2018-06-01 14:00:00 | | 14 | 2018-07-01 14:00:00 | | 15 | 2018-07-01 14:00:00 | | 16 | 2018-08-01 14:00:00 | | 18 | 2018-09-01 14:00:00 | | 19 | 2018-09-01 14:00:00 | +----+---------------------+ 14 rows in set (0.00 sec)
可以看见确实只有14条数据。。竟然少了4条数据，这是什么情况。仔细发现主键id等于5,9,13,17的这4条数据丢失了。通过上面也能发现insert_time行数据不是唯一的，有数据重复。那么为何会发生数据丢呢？我们打开general_log一窥究竟。
id等于9的条数据的insert_time是2018-04-01 14:00:00。在仔细查看general_log以后发现了真相。先后这2条SQL我们执行看一下获取到的数据范围。
# User@Host: root[root] @ 127.0.0.1:34759 [] # Query_time: 0.000282 SELECT /*!40001 SQL_NO_CACHE */ `id`,`insert_time` FROM `yayun`.`t1` FORCE INDEX(`insert_time`) WHERE (insert_time >= "2018-01-01 00:00:00" and insert_time <="2018-12-31 23:59:59") AND ((`insert_time` > '2018-02-01 14:00:00')) ORDER BY `insert_time` LIMIT 3; # Time: 062521 14:24:12 # User@Host: root[root] @ 127.0.0.1:34759 [] # Query_time: 0.000265 SELECT /*!40001 SQL_NO_CACHE */ `id`,`insert_time` FROM `yayun`.`t1` FORCE INDEX(`insert_time`) WHERE (insert_time >= "2018-01-01 00:00:00" and insert_time <="2018-12-31 23:59:59") AND ((`insert_time` > '2018-04-01 14:00:00')) ORDER BY `insert_time` LIMIT 3;
SQL1获取到的数据范围的主键id是6,7,8
SQL2获取到的数据范围的主键id是10,11,12
刚好把主键ID等于9的这条数据漏掉了。。漏掉了。。为啥出现这种情况？仔细看SQL就知道了，因为 insert_time="2018-04-01 14:00:00"的数据有2条
[root@localhost][yayun]> select * from t1 where insert_time='2018-04-01 14:00:00'; +----+---------------------+ | id | insert_time | +----+---------------------+ | 8 | 2018-04-01 14:00:00 | | 9 | 2018-04-01 14:00:00 | +----+---------------------+ 2 rows in set (0.01 sec)
而SQ2的是这样的：
SELECT /*!40001 SQL_NO_CACHE */ `id`,`insert_time` FROM `yayun`.`t1` FORCE INDEX(`insert_time`) WHERE (insert_time >= "2018-01-01 00:00:00" and insert_time <="2018-12-31 23:59:59") AND ((`insert_time` > '2018-04-01 14:00:00')) ORDER BY `insert_time` LIMIT 3;
条件是 AND ((`insert_time` > '2018-04-01 14:00:00')) 刚好把这条数据遗漏。再回来看看我的归档命令：
pt-archiver --source h=127.0.0.1,P=3307,u=root,p=xx,D=yayun,t=t1,i=insert_time --dest h=127.0.0.1,P=3307,u=root,p=xx,D=yayun,t=t2 --no-safe-auto-increment --charset=utf8 --where 'insert_time >= "2018-01-01 00:00:00" and insert_time <="2018-12-31 23:59:59"' --progress 5000 --txn-size 10000 --local --no-delete --bulk-insert --limit=3 --statistics --why-quit
我这里的--source h=127.0.0.1,P=3307,u=root,p=xx,D=yayun,t=t1,i=insert_time，设置了强制走insert_time索引，所以看见SQL里面是INDEX(`insert_time`) 。那么我为什么要强制走insert_time索引？这是由于不加该参数，工具默认强制走主键，这在某些场景下会导致查询效率异常低下。默认走主键是不会导致数据丢失的，因为主键是不重复的。反推过来就是如果指定走某个索引，如果这个索引是唯一索引，那么不会导致数据丢失。反之就是普通的二级索引，就会有可能导致数据丢失。这还和你设置的limit有关系，我上面的测试把limit改成2，就不存在丢失数据。
看看默认走主键的SQL是怎样的。
SELECT /*!40001 SQL_NO_CACHE */ `id`,`insert_time` FROM `yayun`.`t1` FORCE INDEX(`PRIMARY`) WHERE (insert_time >= "2018-01-01 00:00:00" and insert_time <="2018-12-31 23:59:59") AND ((`id` > '7')) ORDER BY `id` LIMIT 3; SELECT /*!40001 SQL_NO_CACHE */ `id`,`insert_time` FROM `yayun`.`t1` FORCE INDEX(`PRIMARY`) WHERE (insert_time >= "2018-01-01 00:00:00" and insert_time <="2018-12-31 23:59:59") AND ((`id` > '10')) ORDER BY `id` LIMIT 3;
这个在数据少的情况下没问题，在数据量大的情况下存在性能问题。最佳实践就是加上最小主键id和最大主键id，默认走主键查询数据归档，这样即兼顾了性能也不会导致数据丢失。
