MariaDB相似结构同索引表性能差异排查求助

核心原因排查方向 #

• 盯死执行计划的中间块细节：重点看是否出现Using filesort或Using temporary标记。这两个操作是中间块耗时飙升的常见原因——哪怕是小表，一旦触发磁盘排序或临时表，性能会断崖式下跌。
• 验证索引匹配逻辑：虽然你加了(position_id, valid_from)复合索引，但要确认查询的过滤、排序、关联顺序是否符合最左前缀原则。比如如果查询是先按valid_from过滤再关联position_id，当前索引顺序完全反了，根本不会被用到；反之如果是先过滤position_id再按valid_from排序，索引才会生效。
• 更新表统计信息：执行ANALYZE TABLE position_info;。MariaDB对小表的统计信息容易过时，优化器可能基于错误的行数估算，选了全表扫描+临时表排序的低效计划，而非走索引。

针对性索引调优 #

• 调整索引顺序（按需）：根据查询的实际过滤/排序优先级调整复合索引字段顺序。比如查询逻辑是WHERE valid_from >= ? AND valid_from <= ? ORDER BY position_id，就把索引改成(valid_from, position_id)；如果是WHERE position_id IN (?) ORDER BY valid_from DESC，保持(position_id, valid_from DESC)（注意排序方向要和索引一致）。
• 构建覆盖索引：把查询中所有需要返回的字段都加到复合索引里，避免回表查询。比如如果查询需要position_id, valid_from, position_name, is_active，就建(position_id, valid_from, position_name, is_active)的复合索引，让优化器直接从索引里拿数据，不用访问主表。
• 排查隐式类型转换：确认查询中传入的position_id参数是INT类型，而非字符串。如果参数类型不匹配，MariaDB会做隐式转换，导致索引失效，被迫全表扫描。

额外验证步骤 #

• 对比两张表的执行计划：把aircraft_assignment的执行计划和position_info的放在一起对比，看后者是否在关联、排序步骤上有明显差异（比如前者用了索引排序，后者用了文件排序）。
• 检查临时表配置：执行SHOW VARIABLES LIKE 'tmp_table_size';和SHOW VARIABLES LIKE 'max_heap_table_size';，如果查询生成的临时表超过阈值，会切换到磁盘临时表，哪怕小表也会变慢。可以临时调大这两个值测试是否有改善。

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