001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `employees`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (1 = 1) AND (`id` < '200000') ORDER BY `id` LIMIT 10000
SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `employees`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (1 = 1) AND (`id` < '200000') AND ((`id` >= ?)) ORDER BY `id` LIMIT 10000
DELETE FROM `employees`.`employees_ptarc` WHERE (((`id` >= ?))) AND (((`id` <= ?))) AND (1 = 1) LIMIT 10000
可以看出工具在执行查询归档数据的语句时，指定了条件id < 200000，而实际上表中是有id = 200000这条数据的，并且在归档操作时并没有指定条件排除这条语句，显然是pt-archiver工具自动添加的条件。
这样可以避免AUTO_INCREMENT属性的值在数据库重启后还可以重用，可以做如下测试说明：
-- 表employees_ptarc当前的AUTO_INCREMENT属性值
mysql admin@192.168.58.3:employees> select auto_increment from information_schema.tables where table_schema = 'employees' and table_name = 'employees_ptarc';
+----------------+
| auto_increment |
+----------------+
| 200001 |
+----------------+
1 row in set
Time: 0.048s
-- 需要清除的数据量
mysql admin@192.168.58.3:employees> select count(*) from employees_ptarc where id <=199990 or id > 199995;
+----------+
| count(*) |
+----------+
| 199995 |
+----------+
1 row in set
Time: 0.102s
-- 执行数据清除操作
# pt-archiver --source h=192.168.58.3,P=3306,u=admin,D=employees,t=employees_ptarc,A=utf8 --purge --charset=utf8 --where "id <= 199990 or id > 199995" --progress=50000 --txn-size=50000 --limit=50000 --statistics --bulk-delete --ask-pass
Enter password:
TIME ELAPSED COUNT
2019-04-16T10:47:59 0 0
2019-04-16T10:48:00 0 50000
2019-04-16T10:48:00 1 100000
2019-04-16T10:48:01 2 150000
2019-04-16T10:48:02 2 199994
Started at 2019-04-16T10:47:59, ended at 2019-04-16T10:48:02
Source: A=utf8,D=employees,P=3306,h=192.168.58.3,p=...,t=employees_ptarc,u=admin
SELECT 199994
INSERT 0
DELETE 199994
Action Count Time Pct
bulk_deleting 4 0.9030 28.23
select 5 0.2095 6.55
commit 4 0.1562 4.88
other 0 1.9298 60.33
可以看出只清除了199994行数据。
-- 查询未清除数据
mysql admin@192.168.58.3:employees> select * from employees_ptarc;
+--------+--------+---------------+----------------------+
| id | v_int | v_string | s_string |
+--------+--------+---------------+----------------------+
| 199991 | 60305 | 526ed | 0240a2d81e255c915b5a |
| 199992 | 546438 | a85b6a18d | 0bf1d636cd0e536eb044 |
| 199993 | 543327 | 1367a1c | 68908231ca18ed631907 |
| 199994 | 99632 | 2f | 5c10f8d106a30bb1ef95 |
| 199995 | 164172 | e57bba13eb3c1 | 3208ac758bd8c912c39f |
| 200000 | 108936 | 3bc1db70b | 079f744bf2800ad62a9b |
+--------+--------+---------------+----------------------+
6 rows in set
Time: 0.018s
-- 重启后，查询当前AUTO_INCREMENT属性值
mysql admin@192.168.58.3:employees> select auto_increment from information_schema.tables where table_schema = 'employees' and table_name = 'employees_ptarc';
+----------------+
| auto_increment |
+----------------+
| 200001 |
+----------------+
1 row in set
Time: 0.014s
为了不保护AUTO_INCREMENT属性最大值的数据行，工具提供了选项--no-safe-auto-increment，指定该选项后再进行测试：
-- 指定选项--dry-run执行清除操作
# pt-archiver --source h=192.168.58.3,P=3306,u=admin,D=employees,t=employees_ptarc,A=utf8 --purge --charset=utf8 --where "id <= 199990 or id > 199995" --progress=50000 --txn-size=50000 --limit=50000 --statistics --bulk-delete --no-safe-auto-increment --ask-pass --dry