MySQL:创建没有索引的外键

Question

在 MySQL 5.6.34 中是否可以使用没有索引的外键？我想要这样做是因为我在 20M 行中创建了一个可为空的列，其中包含指向另一个表的外键。由于这是一项新功能，因此只有新行可能会在该列中填充实际值，而且正如您所预料的那样，该索引的基数变得很糟糕。因此，在大多数情况下，使用该索引实际上是一个坏主意。问题:我有大量查询具有相同的限制:

[...] from large_table where tenant_id = ? and nullable_foreign_key_with_index is null and [...]

问题？ MySQL 认为使用index_merge/intersect 策略 进行查询解析是个好主意。在这种情况下，MySQL 将并行执行 2 个查询:一个使用 tenant_id(使用有效且良好的索引)，另一个使用 nullable_foreign_key_with_index，这很糟糕，几乎是“完整的”并行表扫描”，因为该索引的基数在具有 >20M 行的表中为 <1000。 here 中有关此“问题”的更多详细信息

那么，有哪些可能的解决方案？鉴于 MySQL“强制”外键附加索引:

1. 删除外键和索引。这很糟糕，因为如果应用中出现错误，我们可能会损害参照完整性。

2. FOREIGN_KEY_CHECKS=0;下降指数； FOREIGN_KEY_CHECKS=1。 这很糟糕，因为即使外键仍然存在，MySQL 也不再验证该列以检查该值是否确实存在。这是一个错误吗？

3. 在所有现有查询中使用查询提示以确保我们仅使用旧的且高效的“tenant_id_index”。这很糟糕，因为我必须搜索所有现有查询，并且还记得在构建新闻查询时再次使用它。

所以，我怎么说:“MySQL，不要为这个外键创建索引，而是继续验证它在相关表中的内容，无论如何它都是由主键索引的” .我错过了什么吗？到目前为止最好的想法是删除外键并相信该应用程序按预期工作，可能是这样，但这将开始关于在 APP 与 DATABASE 中具有约束的经典讨论。有什么想法吗？

Answer 1

对于这个查询:

from large_table
where tenant_id = ? and
      nullable_foreign_key_with_index is null and [...]

只需添加索引 large_table(tenant_id, nullable_foreign_key_with_index)。

MySQL 应该为表使用这个索引。

我很确定您可以向后执行此操作(如果比较对象不是 NULL，我会 100% 确定，但我很确定 MySQL 使用 NULL 也是。)

large_table(nullable_foreign_key_with_index, tenant_id)

并且 MySQL 将识别该索引适用于外键并且不会创建任何其他索引。