MariaDB为何不使用新增联合索引？如何优化关联查询？

1. 新增联合索引未被自动选用的原因 #

MariaDB优化器选择索引时基于成本估算逻辑，以下是核心诱因：

• sportId基数过低：你提到sportId基数低，优化器判断：通过betIdFK单索引定位关联记录后，在内存中过滤sportId IN (50,55)的成本，比使用联合索引的成本更低。低基数字段带来的索引区分度提升有限，优化器会倾向于它认为“更高效”的执行路径。
• 统计信息过时：若BetSelection表的统计信息未及时更新，优化器无法准确评估联合索引的实际收益，会沿用旧的成本评估逻辑选择单索引。
• LIMIT子句的影响：查询末尾的LIMIT 100会让优化器优先考虑快速获取前100条结果的路径。它可能认为，先通过Bet表的placed索引取出符合时间范围的记录，再用BetSelection的betIdFK单索引逐行验证EXISTS条件，能更快返回前100条数据，无需遍历联合索引做额外过滤。
• EXISTS子查询的执行逻辑：对于EXISTS关联，优化器有时默认采用“嵌套循环”方式，此时单索引betIdFK的定位速度已足够，优化器觉得没必要启用联合索引。

2. 大表关联查询的最优优化策略 #

针对亿级规模的一对多表关联场景，按优先级尝试以下方案：

（1）更新统计信息，修正优化器判断 #

先执行统计信息更新，确保优化器能准确评估索引收益：

ANALYZE TABLE BetSelection;

更新后重新执行EXPLAIN查看索引是否被选用，这是最基础的优化步骤。

（2）调整联合索引顺序（针对性优化） #

如果sportId的过滤能大幅减少后续关联的数据量，可尝试将sportId放在联合索引前列：

CREATE INDEX sportId_betIdFK_idx ON BetSelection(sportId, betIdFK);

这种索引顺序能先快速筛选出sportId为50、55的记录，再通过betIdFK关联Bet表，直接缩小关联数据集。

（3）查询改写：反向关联+覆盖索引 #

将原查询从“Bet表出发关联BetSelection”改为“先从BetSelection筛选符合条件的记录，再关联Bet表的时间范围”，配合覆盖索引进一步优化：

SELECT bet.*
FROM (
    SELECT DISTINCT bs.betIdFK
    FROM BetSelection bs
    WHERE bs.sportId IN (50,55)
) bs
JOIN Bet bet ON bet.recordId = bs.betIdFK
WHERE bet.placed >= '2025-10-29T00:00:00'
  AND bet.placed <= '2025-10-29T23:59:59'
ORDER BY bet.placed DESC
LIMIT 100;

同时给BetSelection创建sportId, betIdFK的联合索引，给Bet表保留placed, recordId的覆盖索引（若未创建），这样子查询可直接通过索引获取betIdFK无需回表，关联Bet表时也能通过placed索引快速过滤并排序。

（4）合理使用强制索引（临时快速方案） #

若更新统计信息和调整索引后优化器仍不选择最优索引，可在查询中强制指定联合索引，这是快速见效的临时方案，但需注意后续数据分布变化或版本升级时可能需要调整：

SELECT bet.*
FROM Bet bet
WHERE bet.placed >= '2025-10-29T00:00:00'
  AND bet.placed <= '2025-10-29T23:59:59'
  AND EXISTS (
    SELECT 1 FROM BetSelection bs FORCE INDEX (betIdFK_sportId_idx)
    WHERE bs.betIdFK = bet.recordId
      AND bs.sportId IN (50, 55)
)
ORDER BY bet.placed DESC
LIMIT 100;

（5）分区表优化（长期架构方案） #

如果Bet和BetSelection表的数据按时间（如placed字段）有明显冷热区分，可考虑对表进行按时间分区，查询时只会扫描指定日期的分区，大幅减少数据扫描量，这是亿级大表长期性能优化的核心方向。

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