MariaDB创建外键自动生成的索引无法生效问题咨询

这个现象不属于操作误解，同时属于MariaDB 10.3版本的已知设计缺陷，不算核心功能bug，核心原因涉及两个层面：InnoDB索引统计信息的更新机制、MariaDB对重复索引的处理逻辑。

1. 外键自动生成索引基数不准的原因 #

• InnoDB的索引基数（cardinality）是采样统计的估算值，不是实时精确值。默认配置下，只有当表中数据变动超过10%的时候才会触发异步自动重新统计。创建外键自动生成索引时，默认不会同步触发全量采样统计，很可能只用了极少的采样页计算基数，才会出现只有2的极端错误值，优化器判定走索引收益低于全表扫描，所以不会命中该索引。
• OPTIMIZE TABLE对InnoDB引擎的默认行为是重建表并整理碎片，不会主动触发索引统计信息更新，这也是执行优化后问题没有解决的原因，正确的刷新统计信息的命令是ANALYZE TABLE 表名。

2. 同列新建索引无警告的原因 #

MariaDB/MySQL本身就允许在相同的列（或列组合）上创建多个名称不同的重复索引，这个是设计如此，不会抛出错误或警告。新建索引时会触发实时的索引统计采样，所以新索引的基数计算正常为154，优化器可以正确识别到索引的收益，查询执行计划自然恢复正常。

3. 版本相关说明 #

你遇到的自动生成外键索引后统计信息未触发重新计算的问题，是MariaDB 10.3.x系列的已知问题，10.4及后续版本已经优化了索引创建（包括自动创建外键索引）后的统计信息触发逻辑，不会再出现这类极端的基数计算错误。

推荐处理方案 #

• 不需要手动创建重复索引，外键创建完成后执行一次ANALYZE TABLE 你的表名即可刷新索引统计信息
• 可以调整配置参数提升统计信息准确性：
  • 开启innodb_stats_auto_recalc = ON，数据变动达到阈值后自动刷新统计
  • 调大innodb_stats_persistent_sample_pages = 20（默认值为8），增加采样页数提升基数计算的准确度
• 定期清理不必要的重复索引，避免增加写入操作的额外开销

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