MySQL5.6.34中有没有可能没有索引就有外键？我之所以这样做，是因为我在20m行中创建了一个可为null的列，其中外键指向另一个表。因为这是一个新特性，所以只有新行可能会用实际值填充此列，而且正如您所料，索引的基数会变得可怕。所以，大多数时候，使用这个索引实际上是个坏主意。问题是：我有很多查询都有相同的限制：
[...] from large_table where tenant_id = ? and nullable_foreign_key_with_index is null and [...]问题是什么？mysql认为使用索引合并/相交策略来解决查询是个好主意。在这种情况下，mysql将并行执行两个查询：一个是 tenant_id （使用有效且良好的索引）和另一个 nullable_foreign_key_with_index 这很糟糕，几乎是“并行全表扫描”，因为在一个行数大于20m的表中，这个索引的基数小于1000。关于这个“问题”的更多细节在这里
那么，什么是可行的解决方案呢？假设mysql“强制”外键附加索引：
删除外键和索引。这是不好的，因为在应用程序中出现错误的情况下，我们可能会破坏引用完整性。
外键检查=0；跌落指数；外键检查=1。这是不好的，因为即使外键仍然存在，mysql也不再验证列以检查值是否确实存在。那是虫子吗？
在所有现有的查询中使用查询提示，以确保我们只使用旧的和有效的“租户id\u索引”。这是不好的，因为我必须查找所有现有的查询，还记得在构建新闻查询时再次使用它。
所以，我怎么能说：“mysql，不要为这个外键创建索引，但是要在相关表中验证它的内容，不管怎样，这个表是由主键索引的”。我错过什么了吗？到目前为止，最好的办法是删除外键，并相信应用程序按预期工作，这很可能是，但这将开始一个关于在app vs database中设置约束的经典讨论。有什么想法吗？
3条答案
按热度按时间dba5bblo1#
总结：几乎总是有一个复合索引，而不是依赖于“索引合并相交”。
如果两个柱都用
=（或IS NULL)，则列在索引定义中的顺序无关紧要。也就是说，基数是无关紧要的。pxiryf3j2#
对于此查询：
只需添加索引
large_table(tenant_id, nullable_foreign_key_with_index).mysql应该对表使用这个索引。
我很肯定你可以倒过来做（如果这个比较是和其他的比较，我会百分之百肯定）
NULL，但我非常肯定mysql在NULL也是。）mysql将认识到这个索引对外键有效，而不会创建任何其他索引。
kmbjn2e33#
问：我怎么能说：“mysql，不要为这个外键创建索引，但要在相关表中不断验证它的内容，反正是由主键索引的”
a:不行。innodb需要一个合适的索引来支持外键约束的实施。
想想它的另一面。。。如果我们要删除父表中的一行，那么innodb需要检查外键约束。
这意味着innodb需要检查子表的内容，以便在外键列中找到具有特定值的行。本质上等同于
为此，innodb要求子表上有一个索引
foreign_key_col作为第一列。问题中建议的选项（禁用或删除外键）将起作用，因为这样innodb就不会强制执行外键。但正如问题中提到的，这意味着外键没有强制执行。它破坏了外键的作用。应用程序代码可以负责执行引用完整性，或者我们可以编写一些uggghhly触发器（不，我们不想去那里）。
正如戈登在他（一如既往的出色）的回答中所指出的。。。问题并不是在外键列上删除索引。实际问题是执行计划效率低下。最可能的解决办法是确保有一个更合适的索引可用。
综合指数是一条出路。像这样的索引：
将满足外键的要求，外键列作为前导列的索引。并将索引（现在是冗余的）放到单例外键列上。
这个索引还可以用来满足使用可怕的索引合并访问计划的查询(通过解释进行验证。）
另外，考虑将列（如foreign\u key\u col）添加到以tenant\u id作为前导列的索引中
并删除singleton tenant\u id列上的冗余索引。