添加index_active_users索引后CREATE TEMPORARY TABLE查询变慢的原因

这种差异的核心是CREATE TEMPORARY TABLE场景下，优化器选择了低效的执行计划，结合临时表创建的额外特性，放大了索引带来的性能损耗，具体可拆解为以下几点：

• 执行计划的差异化选择
  单独执行SELECT时，优化器可能基于你的查询条件（比如筛选活跃用户的blocked_at IS NULL AND deleted_at IS NULL），判断使用主键索引或其他更适配的索引成本更低，因此能快速返回结果。但在CREATE TEMPORARY TABLE时，优化器可能错误评估了index_active_users的成本——比如认为该索引能快速过滤目标行，可实际上这个复合索引的字段顺序（blocked_at在前、deleted_at在后）不符合查询的过滤逻辑，或者需要大量回表操作（从索引叶子节点跳转回主键索引读取整行数据），进而产生大量随机IO，导致整体耗时飙升。而单独SELECT可能因Buffer Pool中已有热点数据缓存，掩盖了回表的真实开销。

• 临时表创建的额外开销被放大
  通过索引扫描生成的数据是按索引的物理顺序返回的，而非主键顺序。如果临时表使用InnoDB引擎（MariaDB 10.3默认临时表可能采用此引擎），插入无序数据会引发更多页分裂和缓冲池刷新操作；若使用MEMORY引擎，无序插入也会增加哈希冲突或排序开销。而移除索引后，优化器只能选择全表扫描，返回的数据按主键顺序排列，插入临时表时的写入效率更高，整体耗时自然更低。

• 统计信息偏差影响成本评估
  对于44万行的大表，若MariaDB的表统计信息过时，优化器在评估CREATE TEMPORARY TABLE的执行成本时，可能错误认为index_active_users能过滤掉大部分数据，实际却需要扫描接近全表的索引条目，再叠加回表操作，导致实际耗时远超预期。而单独SELECT因返回数据集较小，优化器的成本评估偏差影响有限。

• 索引本身的适配性不足
  如果你的查询目标是获取所有活跃用户（即blocked_at IS NULL AND deleted_at IS NULL），index_active_users这个复合索引的设计效率极低：因为blocked_at和deleted_at都是状态字段，大概率大部分值为NULL，索引区分度极差，扫描该索引的开销几乎和全表扫描持平，还要额外承担回表的IO成本。移除索引后，优化器选择全表扫描，反而避免了回表的额外损耗。

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