为什么MariaDB执行带子查询的UPDATE时会忽略索引？

有一张字段较多的InnoDB宽表my_data，需要更新符合条件的记录。预先计算出匹配项并创建临时表temp_data_updates存储待更新记录的主键，但执行UPDATE时速度极慢（数分钟后终止操作）。

执行EXPLAIN得到两个差异明显的结果：
单个ID更新的执行计划（瞬间完成）：

> explain extended UPDATE my_data set status = 'broken' where id = (select 1711016 as id);
select_type  |SIMPLE     
table        |my_data   
type         |range      
possible_keys|PRIMARY    
key          |PRIMARY    
key_len      |4          
ref          |           
rows         |1          
filtered     |100.0      
Extra        |Using where

使用临时表ID列表的更新执行计划（全表扫描，扫描行数等于表总行数9030625）：

> explain extended UPDATE my_data set status = 'broken' where id in (select id from temp_data_updates);
select_type  |PRIMARY    
table        |my_data   
type         |index      
possible_keys|           
key          |PRIMARY    
key_len      |4          
ref          |           
rows         |9030625    
filtered     |100.0      
Extra        |Using where

my_data表结构（截取核心部分）：

CREATE TABLE `my_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `field_a` int(10) unsigned NOT NULL,
  `field_b` int(10) unsigned DEFAULT NULL,
  `field_c` int(10) unsigned DEFAULT NULL,
  `field_d` varchar(64) DEFAULT NULL,
  `field_e` varchar(64) DEFAULT NULL,
  `field_f` varchar(45) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `field_g` timestamp NOT NULL DEFAULT current_timestamp(),
  `field_h` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `field_i` bigint(20) DEFAULT NULL,
  `field_j` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `field_k` bigint(20) DEFAULT NULL,
  `field_l` bigint(20) DEFAULT NULL,
  `field_m` bigint(20) DEFAULT NULL,
  `field_n` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `field_o` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `status` varchar(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `field_p` varchar(45) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `error` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  -- some more keys and constraints
) ENGINE=InnoDB AUTO_INCREMENT=11310160 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

临时表temp_data_updates仅存储少量主键ID（1-100条），但更新操作始终全表扫描，无法利用主键索引。

这是MySQL优化器对IN子查询的处理逻辑导致的：当IN子查询的数据源是临时表时，部分MySQL版本的优化器会将查询转换为全表扫描+逐行匹配的执行计划，而不是利用my_data的主键索引去匹配临时表中的ID。这种情况下，即使临时表数据量极小，优化器也会选择遍历my_data的所有行，导致性能急剧下降。

以下几种方法可以强制优化器使用主键索引，大幅提升更新速度：

1. 使用JOIN替代IN子查询 #

这是最直接有效的方法，通过JOIN让优化器明确利用主键索引匹配临时表的ID：

UPDATE my_data
JOIN temp_data_updates ON my_data.id = temp_data_updates.id
SET my_data.status = 'broken';

执行EXPLAIN验证，此时my_data会使用PRIMARY索引，仅扫描匹配的行数。

2. 给临时表的id字段添加主键/索引 #

临时表如果没有索引，优化器无法高效获取ID列表，给temp_data_updates的id字段添加主键或普通索引，帮助优化器生成更优的执行计划：

ALTER TABLE temp_data_updates ADD PRIMARY KEY (id);
-- 或者添加普通索引
ALTER TABLE temp_data_updates ADD INDEX idx_id (id);

之后再执行原IN子查询的UPDATE语句，优化器会优先使用my_data的主键索引进行匹配。

3. 使用EXISTS子查询替代IN #

部分场景下，EXISTS的执行计划优化比IN更合理，改写后的语句如下：

UPDATE my_data
SET status = 'broken'
WHERE EXISTS (
    SELECT 1 FROM temp_data_updates WHERE temp_data_updates.id = my_data.id
);

EXISTS会逐行检查my_data的ID是否存在于临时表中，但优化器会优先利用my_data的主键索引来加速匹配。

4. 升级MySQL版本（可选） #

如果使用的是较旧的MySQL版本（如5.6及以前），升级到5.7+或8.0版本，新版本的优化器对IN子查询的处理逻辑有较大改进，可能自动选择更优的执行计划。

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