MariaDB 10.3中带索引的UPDATE查询为何无法提速？

1. 单字段索引无法覆盖查询条件
   你的UPDATE语句WHERE子句同时用到了status和work_to_dt两个字段，但只有work_to_dt的单字段BTREE索引。如果status=1的数据在表中占比很高，或者work_to_dt < '2024-04-01 14:30:31'的结果集较大，MariaDB优化器会认为：通过work_to_dt索引找到数据后，还要回表查询每条数据的status值进行过滤，这个过程的IO开销反而比全表扫描更高，因此会直接选择全表扫描，导致索引没发挥作用。

2. UPDATE的核心开销不在数据定位
   你要更新的958条数据量不算大，但InnoDB执行UPDATE时，需要完成行锁申请、binlog写入、回滚段维护这些必要操作，这些步骤的耗时往往占据了总耗时的大部分。有没有索引对这部分操作的影响很小，所以有无索引总耗时差异不大。

• 创建联合索引
  针对WHERE条件的两个字段，建立联合索引(status, work_to_dt)。这样优化器可以直接通过索引筛选出符合status=1且work_to_dt满足条件的行，无需回表查询，能大幅降低IO开销。

• 调整事务隔离级别（业务允许时）
  如果当前使用的是REPEATABLE READ（RR）隔离级别，InnoDB会添加间隙锁防止幻读，可能增加锁等待时间。若业务能接受，可切换为READ COMMITTED（RC）级别，缩小锁的范围，减少锁竞争。

• 分批更新
  把更新拆分成多次小批量操作，避免长时间持有锁影响其他业务，比如每次更新100条：

  UPDATE subscription
  SET `status`= 2
  WHERE
      subscription.`status` = 1 AND
      subscription.work_to_dt IS NOT NULL AND
      subscription.work_to_dt < '2024-04-01 14:30:31'
  LIMIT 100;
  

  循环执行直到没有符合条件的数据即可。

• 优化binlog配置
  检查sync_binlog设置，如果值为1，每次事务提交都会强制刷盘，会增加耗时。若业务能接受一定的数据丢失风险（比如宕机时可能丢失少量未刷盘的binlog），可将sync_binlog调整为0或1000，减少刷盘次数。

• 整理表碎片
  如果表经过大量增删改操作产生了碎片，回表查询的IO效率会下降。可在业务低峰期执行OPTIMIZE TABLE subscription;整理碎片，但注意该操作会锁表，需提前评估影响。

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