[...] from large_table where tenant_id = ? and nullable_foreign_key_with_index is null and [...]
问题出在哪里?MySQL认为使用索引合并/交集策略来解决查询是一个好主意。在这种情况下,MySQL会同时执行两个查询:一个带有tenant_id(使用有效和良好的索引),另一个带有nullable_foreign_key_with_index,后者不好,几乎相当于“全表扫描并行”,因为该索引的基数小于1000,而表中却有超过20M行。有关此“问题”的更多详细信息,请参见here。
那么,有什么可能的解决方案呢?鉴于MySQL“强制”要求外键附带索引:
删除外键和索引。这是不好的,因为在应用程序出现错误的情况下,我们可能会破坏引用完整性。
FOREIGN_KEY_CHECKS=0; 删除索引; FOREIGN_KEY_CHECKS=1。这是不好的,因为即使外键仍然存在,MySQL也不再验证列以检查值是否实际存在。这是一个错误吗?
在所有现有查询中使用查询提示,以确保我们只使用旧的高效的“tenant_id_index”。这是不好的,因为我必须寻找所有现有的查询,并在构建新查询时记住再次使用它。
那么,我怎么说:“MySQL,不要为此外键创建索引,但继续验证其内容与相关表匹配,该表已经由主键索引了呢?” 我错过了什么吗?到目前为止,最好的想法是删除外键并相信应用程序按预期工作,尽管这可能会引发关于在APP vs DATABASE中拥有约束的经典讨论。有什么想法吗?
where
子句 -- 也就是说,所有的键都被使用了。 - Gordon Linoff