索引外键列
在所有外键列上放置索引是否是经验法则？看来它们经常用于表连接，并且会从索引中受益。
如果 FK 列只有 2 或 3 个可能值怎么办？例如，如果它引用状态表。是否仍然建议在 FK 字段上放置索引？
Is it a rule of thumb to put an index on all foreign key columns? It seems they would often be used in table joins and would beneifit from the index.
What if the FK column only has 2 or 3 possible values? For example if it was referencing a status table. Would it still be advisable to put an index on the FK field?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我强烈不同意 Eelke 的观点 - 大多数 SQL Server 专家会建议在外键列上放置非聚集索引，我同意。
有关这些指数的最佳原因总结和最佳论证，请参阅 Kimberly Tripp 的优秀博客文章 SQL Server 何时停止在外键上放置索引？ - 答案是：从不 - 从来没有（这是一个）许多持久的城市神话 - 但它仍然是一个神话）。
她的核心信息是：
I would strongly disagree with Eelke - most SQL Server gurus will recommend to put a non-clustered index on foreign key columns, and I agree.
For the best summary of reasons and the best argumentation for those indices, see Kimberly Tripp's excellent blog post When did SQL Server stop putting indexes on foreign keys? - the answer is: never - it never did (that is one of many persistent urban myths - but it's still a myth).
Her core message is:
（这里有两个问题：机器效率和 DBA 效率。）
根据经验，查询性能受益于 JOIN 子句和 WHERE 子句中使用的列上的索引。当然，插入、更新和删除性能也会受到这些相同索引的影响。 （因为索引必须与表一起更新。）
如果列的值很少，并且有充分的理由避免将实际值存储在表中，我更喜欢将人类可读的代码作为外部的目标关键参考。例如，ISO 5218 指定了这些代表人类性别的代码。
我不希望人们记住9意味着不适用。如果这就是我所拥有的一切，那么每次我需要生成输出供人们阅读时，我都必须加入这个表。
但我可以向该表添加一列，声明它唯一，并将其用作外键引用的目标。大多数时候我不必加入这张桌子。哎呀，我可能永远都不需要加入它。避免连接与机器效率有关。
所有这三列都应声明为唯一。在大多数平台上，这意味着这三列中的每一列都会有一个索引。我也会对引用 sex_code 的列建立索引，尽管选择性会很低。
为什么？我有比立即重新评估是否添加索引更好的事情要做，因为此版本中的优化器足够聪明，可以利用它。这与 DBA 的效率有关。
(Two issues come together here: machine efficiency and DBA efficiency.)
As a rule of thumb, query performance benefits from indexes on columns used in JOIN clauses and in WHERE clauses. And, of course, insert, update, and delete performance suffers from those same indexes. (Because the index has to be updated along with the table.)
If a column has few values, and there's a compelling reason to avoid storing the actual value in the table, I prefer a human-readable code to be the target of a foreign key reference. For example, ISO 5218 specifies these codes for representing human sexes.
I don't expect people to remember that 9 means not applicable. If this were all I had, I'd have to join this table every time I needed to produce output for people to read.
But I can add a column to that table, declare it unique, and use it as the target for foreign key references. I won't have to join this table most of the time. Heck, I might never have to join it. Avoiding a join has to do with machine efficiency.
All three of those columns should be declared unique. And on most platforms, that means each of those three columns will get an index. I'd index the columns that reference sex_code, too, even though selectivity will be low.
Why? I have better things to do than to re-evaluate whether to add an index now, because the optimizer in this release is smart enough to take advantage of it. This has to do with DBA efficiency.
不，不应该。索引只应在有用时使用。仅当表的大小足够（例如至少三个块）时，索引才有用。对于短行来说，这很容易达到数百行。
No it shouldn't be. Indexes should only be used when they are of use. Indexes only are of use when the table is of sufficient size say at least three blocks. That can easily be hundreds of rows for short rows.