mysql之pt-archiver工具导致全表扫描的问题
pt-archiver有一个参数--dry-run, 它的含义是试运行,输出计划SQL,但并不真正执行任何SQL。
示例：
$ pt-archiver --source h=127.0.0.1,P=3306,D=zhen_test,t=user_test,u=root,p=123456 --where "create_time < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 16 DAY),\"%Y-%m-%d\")" --dest t=user_test_his --purge --charset=utf8mb4 --limit=1000 --replace --sleep=1 --nosafe-auto-increment --noversion-check --why-quit --progress=10000 --sentinel=/tmp/pt-zhenxi_test-1 --dry-run
SELECT /*!40001 SQL_NO_CACHE */ `id`,`login_name`,`email`,`phonenumber`,`sex`,`password`,`create_time`,`update_time` FROM `zhen_test`.`user_test` FORCE INDEX(`PRIMARY`) WHERE (create_time < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 16 DAY),"%Y-%m-%d")) ORDER BY `id` LIMIT 1000
SELECT /*!40001 SQL_NO_CACHE */ `id`,`login_name`,`email`,`phonenumber`,`sex`,`password`,`create_time`,`update_time` FROM `zhen_test`.`user_test` FORCE INDEX(`PRIMARY`) WHERE (create_time < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 16 DAY),"%Y-%m-%d")) AND ((`id` >= ?)) ORDER BY `id` LIMIT 1000
DELETE FROM `zhenxi_test`.`user_test` WHERE (`id` = ?)
REPLACE INTO `zhenxi_test`.`user_test_his`(`id`,`login_name`,`email`,`phonenumber`,`sex`,`password`,`create_time`,`update_time`) VALUES (?,?,?,?,?,?,?,?)
我们可以根据其输出的SQL,查看执行计划，评估pt-archiver所执行的SQL的影响。
根据--dry-run的输出，我们可以大体推论pt-archiver的设计思路：
pt-archiver的思想是分批次查询归档，在查询语句中加入了Force index(primary)强制使用主键进行查询， 同时在语句末尾加入了order by id limit n。在每批次查询时记录当前归档数据的max_id，并在下一批次查询时，将max_id加入到查询条件中。用于提高查询性能。
这里就有个问题，因为select强制使用主键索引，所以服务器会根据id排序后再通过limit n取出n条数据，同时记录max id。如果归档条件包含非主键字段，同时根据id顺序查询的数据无法满足非id字段时，查询就会持续向后扫描，持续寻找满足条件的数据，直到找到满足条件的n条数据为止。如果根据where条件中的数据一直无法查到满足条件的数据，SQL就变成了全表扫描, 当表的数据量比较大时，这个SQL将会非常耗时，影响服务器性能。
如下图所示, user_test表总行数3678466，使用查询条件create_time > NOW()，同时使用Force index(primary)强制使用主键进行查询，并且加order by id limit 1000，查询耗时达1.67s。
怎么解决？
针对pt-archiver的这一问题，我们可以在使用pt-archiver之前，可以进行一次校验，在归档前连接到数据库根据指定的归档条件检索出max Id，如果不存在max id(where条件匹配不到任何数据）直接返回，存在则将max_id带入到归档参数中，设置id<max_id。用于防止SQL扫描行数过多。
限制：这方法仅对主键是单列时有效，联合主键不行。
参考文档：http://www.dbarun.com/mysql/antdeck-schedule-and-mysql-archiver/