Percona pt-archiver 出现长事务

CREATE TABLE `operation_record` (
  `record_id` bigint NOT NULL COMMENT '主键',
  `enterprise_id` bigint DEFAULT NULL COMMENT '企业ID',
  `account_id` bigint DEFAULT NULL COMMENT '操作人ID',
  `user_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作人名称',
  `real_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作人名称',
  `handle_status` tinyint DEFAULT '1' COMMENT '处理状态. 1:成功 2:失败',
  `handle_status_desc` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '成功' COMMENT '处理状态. 1:成功 2:失败',
  `header` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作请求头',
  `in_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '操作请求内容',
  `out_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '操作响应内容',
  `business_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '业务单号',
  `business_id` bigint DEFAULT NULL COMMENT '业务ID',
  `message_id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'MQ消息ID',
  `service_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '服务名称',
  `module_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '模块名称',
  `method` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '接口名称',
  `remark` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '功能描述',
  `desc` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述信息',
  `source_type` int unsigned NOT NULL COMMENT '来源方式类型',
  `source_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '来源方式名称',
  `user_agent` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'User-Agent',
  `domain` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '域名',
  `source_ip` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '来源IP',
  `handle_ip` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '事件处理节点的IP',
  `trace_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '观测云traceId',
  `create_time` datetime(3) NOT NULL COMMENT '创建时间',
  `update_time` datetime(3) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`record_id`),
  KEY `idx_business_no` (`business_no`),
  KEY `idx_business_id` (`business_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='操作记录表';

归档命令

/development/percona-toolkit-2.2.17/bin/pt-archiver \
--source h=rm-xxx.mysql.rds.aliyuncs.com,P=3306,u=db_user,p='123456',D=db0,t=operation_record \
--where "create_time<=''2025-04-24 08:00:00'" \
--charset=utf8 \
--no-version-check \
--no-check-charset \
--progress 1000 \
--limit=100 \
--txn-size 500 \
--statistics \
--no-safe-auto-increment \
--bulk-delete \
--purge

满足条件的数据有 482 条

执行归档命令,被’卡住’, 执行过程出现了长事务, 捕获到的SQL如下

SELECT /*!40001 SQL_NO_CACHE */ `record_id`,`enterprise_id`,`account_id`,`user_name`,`real_name`,`handle_status`,`handle_status_desc`,`header`,`in_body`,`out_body`,`business_no`,`business_id`,`message_id`,`service_name`,`module_name`,`method`,`remark`,`desc`,`source_type`,`source_name`,`user_agent`,`domain`,`source_ip`,`handle_ip`,`trace_id`,`create_time`,`update_time` FROM `db0`.`operation_record` FORCE INDEX(`PRIMARY`) WHERE (create_time<='2025-04-24 08:00:00') AND ((`record_id` >= '614730460908331008')) ORDER BY `record_id` LIMIT 100

EXPLAIN 查看执行计划

rows = 5759686. 没有使用 idx_create_time 索引.

修改归档命令,手动指定索引 i=idx_create_time

/development/percona-toolkit-2.2.17/bin/pt-archiver \
--source h=rm-xxx.mysql.rds.aliyuncs.com,P=3306,u=db_user,p='123456',D=db0,t=operation_record,i=idx_create_time \
--where "create_time<=''2025-04-24 08:00:00'" \
--charset=utf8 \
--no-version-check \
--no-check-charset \
--progress 1000 \
--limit=100 \
--txn-size 500 \
--statistics \
--no-safe-auto-increment \
--bulk-delete \
--purge

调整之后, 执行的SQL语句举例如下

SELECT /*!40001 SQL_NO_CACHE */ `record_id`,`enterprise_id`,`account_id`,`user_name`,`real_name`,`handle_status`,`handle_status_desc`,`header`,`in_body`,`out_body`,`business_no`,`business_id`,`message_id`,`service_name`,`module_name`,`method`,`remark`,`desc`,`source_type`,`source_name`,`user_agent`,`domain`,`source_ip`,`handle_ip`,`trace_id`,`create_time`,`update_time` FROM `db0`.`operation_record` FORCE INDEX(`idx_create_time`) WHERE (create_time<='2025-04-31') ORDER BY `create_time` LIMIT 100"

从 FORCE INDEX(PRIMARY) 变成 FORCE INDEX(idx_create_time), 使用 WHERE 条件的索引, 归档速度加快了.