MySQL：创建无索引的外键

Question

在MySQL 5.6.34中是否可能有一个没有索引的外键？我希望如此，因为我在另一张表中创建了一个可为空的列，并在20M行上使用了一个外键。由于这是一个新功能，只有新的行可能会填充这个列的实际值，正如您所期望的那样，该索引的基数变得非常糟糕。因此，在大多数情况下，使用该索引实际上是一个坏主意。问题是：我有很多查询都共享这个相同的限制：

[...] 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“强制”要求外键附带索引：

1. 删除外键和索引。这是不好的，因为在应用程序出现错误的情况下，我们可能会破坏引用完整性。

2. FOREIGN_KEY_CHECKS=0; 删除索引; FOREIGN_KEY_CHECKS=1。这是不好的，因为即使外键仍然存在，MySQL也不再验证列以检查值是否实际存在。这是一个错误吗？

3. 在所有现有查询中使用查询提示，以确保我们只使用旧的高效的“tenant_id_index”。这是不好的，因为我必须寻找所有现有的查询，并在构建新查询时记住再次使用它。

那么，我怎么说：“MySQL，不要为此外键创建索引，但继续验证其内容与相关表匹配，该表已经由主键索引了呢？” 我错过了什么吗？到目前为止，最好的想法是删除外键并相信应用程序按预期工作，尽管这可能会引发关于在APP vs DATABASE中拥有约束的经典讨论。有什么想法吗？

- Israel Fonseca

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将会识别出这个索引适用于外键，不会创建任何其他的索引。

- Gordon Linoff

---

那将是我的建议。添加一个复合索引。+10 - spencer7593

你的建议确实起了作用！谢谢！但是，为什么反过来做会有好处呢？考虑到左侧列（nullable_foreign_key_with_index）的基数非常低？ - Israel Fonseca

@IsraelFonseca . . . 基数并不会有太大的影响。索引覆盖了 where 子句 -- 也就是说，所有的键都被使用了。 - Gordon Linoff

@GordonLinoff，我进行了使用两个索引的测试，出乎意料的是，它们都具有相同的性能。为什么会这样呢？不是应该使用最左边的列作为二分查找的参考吗？如果是这样的话，低基数几乎应该无效二分查找，有效地进行“全表扫描”。 我是不是对的？或者我在索引工作方式方面漏掉了什么？ - Israel Fonseca

@GordonLinoff，我已经回答了自己。如果查询中引用了这两列，则不重要。只有一个出现时才有影响。实际索引与所有选择的列“组合”在一起，因此如果where子句具有生成查找键的所有列，则二进制搜索可以在其上正常工作。 - Israel Fonseca

Answer 2

问：如何表达：“MySQL，不要为这个外键创建索引，但仍需在相关表中验证其内容，该表已通过主键建立索引”？

答：无法实现。InnoDB需要适当的索引来支持外键约束的执行。

考虑另一方面……如果我们要删除父表中的一行，则InnoDB需要检查外键约束。

这意味着InnoDB需要检查子表的内容，以查找具有特定外键列值的行。本质上相当于

SELECT ... FROM child_table c WHERE c.foreign_key_col = ? 

为了实现这一点，InnoDB 要求 child_table 上存在一个索引，且该索引的 leading column 是 foreign_key_col。建议中提到的选项（禁用或删除外键）之所以可行，是因为 InnoDB 不会再强制执行外键，但正如问题所述，这意味着外键不被执行，这违反了外键的目的。应用程序代码可能需要负责执行参照完整性，或者我们可以编写一些非常非常丑陋的触发器（不，我们不想走这条路）。正如 Gordon 在他（通常出色的）回答中已经指出的那样……问题实际上并不是删除外键列上的索引，而是执行计划效率低下的问题。最可能的解决方法是确保有更合适的索引。复合索引是正确的选择。像这样的索引：

... ON child_table (foreign_key_col,tenant_id,...)

为了满足外键的要求，需要使用以外键列为主导列的索引。同时删除只包含独立外键列的（现在已经冗余的）索引。

这个索引还可以用于满足使用可怕的索引合并访问计划的查询。（请使用EXPLAIN进行验证。）

此外，考虑将列（如foreign_key_col）添加到以tenant_id为主导列的索引中。

... ON child_table (tenant_id,...,foreign_key_col,...)

并删除单例 tenant_id 列上的冗余索引。

- spencer7593

---

关于复合索引建议，我创建了一个建议的 index_new(tenant_id, foreign_key_col) 索引，而没有删除任何其他索引...好吧，它起作用了。不是因为它使用了这个 index_new，而是因为它使用了旧的单列索引 index_tenant_id。我不明白这一点（但我很高兴这对我的问题足够了）。为什么拥有这个复合索引就足以让优化器放弃使用 index_merge 与 index_tenant_id 和 index_foreign_key_col 或者甚至使用 "糟糕的" index_foreign_key_col 的 index_new？ - Israel Fonseca

经过一些思考，我自己（部分地）回答了这个问题：使用复合索引会更好，因为它已经有了良好的基数列（tenant_id）和相关的“foreign_key_col”，这是“where语句”的一部分。这很有道理，但为什么优化器选择了“普通”的index_tenant_id呢？ - Israel Fonseca

@IsraelFonseca：优化器可能选择了较短（较小）的索引。优化器发现查询没有“覆盖”索引，因此访问计划无法避免访问底层表中的页面。如果tenant_id = const谓词是有选择性的，则使用索引。如果统计信息告诉优化器fk IS NULL不是非常有选择性，即满足该条件的行数超过10％或20％，则索引将无法减少对底层表的页面访问次数。（这不是具体的诊断结果，我只是提供猜测。） - spencer7593

@IsraelFonseca：已定义复合索引 on t (tenant_id,fk_col)，因此索引 on t (tenant_id)（单列）是多余的。如果我们删除多余的索引，则MySQL将使用复合索引。（任何可以利用单列 tenant_id 索引的查询也可以利用以 tenant_id 为主导列的复合索引。） - spencer7593

Answer 3

概述：几乎总是比依赖于“索引合并交集”更好地拥有一个复合索引。

如果两个列都使用=（或IS NULL）进行测试，则索引定义中列的顺序无关紧要。也就是说，基数是无关紧要的。

- Rick James