MySQL查询因索引使用问题执行过久并锁表，请求验证单索引优化假设

你的假设完全合理，这正是解决当前问题的核心方向，下面具体分析原因和优化逻辑：

为什么之前的查询慢且锁表严重？ #

从你给出的EXPLAIN结果来看，MySQL采用了**索引合并（Intersect）**的执行方式，同时扫描payout_operation_update和cycle_id_index两个索引后取交集，这是问题的根源：

• 你的数据库中cycle_id大部分为NULL，意味着cycle_id_index几乎包含了表中所有行。当MySQL需要合并“仅少量行的payout_operation_update索引结果”和“几乎全表行的cycle_id_index索引结果”时，要进行海量的交集计算，直接导致查询耗时长达2小时。
• 更关键的是FOR UPDATE的影响：这个长事务会持续持有锁2小时。虽然InnoDB是行级锁，但事务持有锁的时间越久，后续操作等待锁的概率就越高，最终出现你遇到的“其他操作无法更新任何行”的情况——本质是长事务占锁不释放，导致大量操作排队阻塞。

为什么只使用payout_operation_update索引会更快？ #

你提到shop_id,currency,status的值在库中是唯一的，这意味着payout_operation_update索引可以直接定位到极少量甚至唯一的行。此时查询的执行流程会变成：

1. 通过payout_operation_update索引快速锁定符合shop_id,currency,status条件的行。
2. 仅对这些行检查cycle_id IS NULL的条件（若索引包含cycle_id则无需回表，效率更高）。
3. 只对最终符合所有条件的行加FOR UPDATE锁，事务执行时间会被压缩到毫秒级。

这种方式既避免了全表级别的索引扫描，又极大缩短了锁的持有时间，从根源上解决查询慢和锁表的问题。

如何确保MySQL只使用目标索引？ #

你可以通过FORCE INDEX语法强制查询使用payout_operation_update索引，快速验证效果：

SELECT COALESCE(sum(COALESCE(amount, 0)), 0) as sum_amount,
       COALESCE(sum(COALESCE(fees_amount, 0)), 0) as sum_fees_amount
FROM incoming_operation FORCE INDEX (payout_operation_update)
WHERE status = 'CLEARED' 
  and merchant_id = ? 
  and shop_id IN (?) 
  and currency = ? 
  and cycle_id is null
FOR UPDATE

更进一步的优化建议 #

如果想彻底避免索引合并的可能，你可以创建一个覆盖型联合索引，把cycle_id加入到payout_operation_update索引的末尾：

CREATE INDEX optimized_payout_index ON incoming_operation(shop_id,currency,status,cycle_id);

这个索引可以让MySQL直接通过索引完成所有条件过滤（无需回表查询原数据），查询效率会达到最优。

备注：内容来源于stack exchange，提问作者yuria