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,默认走主键查询数据归档,这样即兼顾了性能也不会导致数据丢失。

 

 

 

Auto Copied

作者:Atlas

出处:Atlas的博客 http://www.cnblogs.com/gomysql

您的支持是对博主最大的鼓励,感谢您的认真阅读。本文版权归作者所有,欢迎转载,但请保留该声明。如果您需要技术支持,本人亦提供有偿服务。

posted @ 2021-06-25 15:15  yayun  阅读(192)  评论(0编辑  收藏  举报