如何优化MySQL关联更新长耗时查询？

问题描述 #

我有一条运行耗时极长的MySQL查询，用于从assignment表同步商户分配字段到transaction表，具体SQL如下：

update transaction t
inner join assignment a on a.transaction_id = t.id
set
    t.merchant_name = a.name,
    t.merchant_org_id = a.merchant_org_id,
    t.category_id = a.category_id,
    t.assignment_pending = false
where t.id in ('...', '...', '...')

我已经为assignment表的transaction_id字段添加了唯一索引idx_assignment_transaction，但查询执行仍然很慢，请问还有其他优化方法吗？

相关表结构 #

transaction表 #

CREATE TABLE `transaction` (
  `id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `org_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `service_connection_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `amount` bigint NOT NULL,
  `currency_code` varchar(3) NOT NULL,
  `amount_usd` bigint NOT NULL DEFAULT '0',
  `merchant_name` varchar(255) NOT NULL,
  `mcc` char(4) DEFAULT NULL,
  `transaction_type` varchar(255) NOT NULL,
  `category_id` char(36) NOT NULL,
  `merchant_org_id` char(36) DEFAULT NULL,
  `user_id` char(36) DEFAULT NULL,
  `mask` varchar(255) DEFAULT NULL,
  `assignment_pending` tinyint(1) NOT NULL DEFAULT '0',
  `state` varchar(255) NOT NULL DEFAULT 'posted',
  `assignment_id` varchar(255) DEFAULT NULL,
  `epoch_authorized` bigint NOT NULL DEFAULT '0',
  `epoch_posted` bigint DEFAULT NULL,
  `raw_description` varchar(255) NOT NULL DEFAULT '',
  `created` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_provider_tx_id` (`org_id`,`provider_tx_id`),
  KEY `idx_tx_merchant` (`merchant_org_id`),
  KEY `idx_tx_assignment` (`assignment_id`),
  KEY `idx_tx_org_authorized` (`org_id`,`epoch_authorized`),
  KEY `idx_tx_org_posted` (`org_id`,`epoch_posted`),
  KEY `idx_tx_org_merchant` (`org_id`,`merchant_org_id`),
  KEY `idx_tx_org_amount_usd` (`org_id`,`amount_usd`),
  KEY `idx_tx_assignment_pending` (`org_id`,`assignment_pending`,`epoch_authorized`,`id`),
  KEY `idx_tx_org_vendor_by_range` (`org_id`,`merchant_org_id`,`epoch_authorized`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

assignment表 #

CREATE TABLE `assignment` (
  `id` char(36) NOT NULL,
  `org_id` char(36) NOT NULL,
  `user_id` char(36) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `date` date DEFAULT NULL,
  `factor_id` char(36) DEFAULT NULL,
  `merchant_org_id` char(36) DEFAULT NULL,
  `category_id` char(36) NOT NULL,
  `transaction_id` char(36) DEFAULT NULL,
  `assignment_method` varchar(255) NOT NULL,
  `created` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_assignment_transaction` (`transaction_id`),
  KEY `idx_org` (`org_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

执行计划 #

表t（transaction）的执行计划 #

表a（assignment）的执行计划 #

优化建议 #

• 调整查询语句，提前过滤数据集
  将IN子句的条件转移到assignment表的子查询中，先筛选出需要的记录再关联更新，减少不必要的关联操作：

  UPDATE transaction t
  INNER JOIN (
      SELECT transaction_id, name, merchant_org_id, category_id
      FROM assignment
      WHERE transaction_id IN ('...', '...', '...')
  ) a ON a.transaction_id = t.id
  SET
      t.merchant_name = a.name,
      t.merchant_org_id = a.merchant_org_id,
      t.category_id = a.category_id,
      t.assignment_pending = false;
  

• 拆分批量更新为小批次
  如果IN子句中的ID数量过大（如数千条），一次性更新会占用大量锁资源与IO。建议拆分为多个小批次（如每次100条）循环执行，降低单次操作的系统负载。

• 创建覆盖索引避免回表
  当前idx_assignment_transaction仅包含transaction_id，查询时需要回表获取其他字段。创建覆盖索引直接包含所需字段，避免回表开销：

  CREATE UNIQUE INDEX idx_assignment_transaction_covering ON assignment(transaction_id, name, merchant_org_id, category_id);
  

• 规避锁竞争
  若transaction表处于高并发场景，批量更新会长时间持有行锁导致阻塞。建议在业务低峰期执行更新，或调整事务隔离级别为READ COMMITTED以缩短锁持有时间。

• 用EXPLAIN ANALYZE分析实际执行情况
  执行计划中的Rows filtered仅为估算值，使用MySQL 8.0+支持的EXPLAIN ANALYZE可以查看实际执行的行数、耗时等细节，精准定位瓶颈。

内容的提问来源于stack exchange，提问作者thomasmost