Mysql S优化联接更新的最佳方式是什么

我有一个长期运行的MySQL查询，它从一个表中的相应记录更新另一个表中的记录.在这种情况下，将派生的商户分配记录中的字段设置到原始交易记录中:

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 ('...', '...', '...')

此查询正在进行...很长一段时间.我有一份作业(job)transaction_id栏的索引...但是，有没有其他方法来优化这个查询呢？

以下是相关方案和解释方案:

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`)
)

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;

Explain Plan