查找表外键应始终被索引吗? [英] Should Lookup Table Foreign Keys Always be Indexed?

问题描述

如果我有一个查找表，其中的记录很少(例如，少于10条)，我是否应该麻烦将索引放在该表所连接的另一个表的外键上?为此，查找表是否甚至需要主键上的索引?

If I have a lookup table with very few records in it (say, less than ten), should I bother putting an index on the Foreign Key of another table to which it is attached? For that matter, does the lookup table even need an index on the Primary Key?

具体来说，是否有任何性能优势超过维护索引的开销?如果没有，除了速度以外还有其他好处吗?

Specifically, is there any performance benefit that outweighs the overhead of maintaining the indexes? If not, are there any benefits other than speed?

注意:查找表的示例可能是订单状态"，其中元组为:

Note: an example of a lookup table might be Order Status, where the tuples are:

1 - Order Received
2 - In Process
3 - Shipped
4 - Paid

推荐答案

在事务系统上，将索引放在这样的列(即低基数参考列)上可能没有明显的好处，因为查询优化程序可能会成功.不要用它.由于必须更新索引，因此也会在写入表时产生其他磁盘流量.因此，对于事务数据库上的低基数FK，通常最好不要对列进行索引.这尤其适用于大容量系统.

On a transactional system there may be no significant benefit to putting an index on such a column (i.e. a low cardinality reference column) as the query optimiser probably won't use it. It will also generate additional disk traffic on writes to the table as the indexes have to be updated. So for low cardinality FK's on a transactional database it is usually better not to index the columns. This particularly applies to high volume systems.

请注意，您可能仍希望FK具有参照完整性，并且在小参考表上进行FK查找可能不会产生I/O，因为查找表几乎总是被缓存.

Note that you may still want the FK for referential integrity and that the FK lookup on a small reference table will probably generate no I/O as the lookup table will almost always be cached.

但是，由于某些原因，您可能会发现希望将该列包括在复合索引中-也许是为常用查询创建覆盖索引.

However, you may find that you want to include the column in a composite index for some reason - perhaps to create a covering index for a commonly used query.

在经常批量加载的表(例如数据仓库)上，如果您有很多索引列，则索引写流量将比表加载量大得多.如果存在任何索引，则可能需要删除或禁用批量加载的FK和索引.

On a table that is frequently bulk-loaded (e.g. a data warehouse) the index write traffic will be much larger than that of the table load if you have many indexed columns. You will probably need to drop or disable the FKs and indexes for a bulk load if any indexes are present.

在 Star Schema 上，即使在SQL Server上，也可以通过索引低基数列而获得一些好处.如果您要进行高度选择性的查询(即查询优化程序确定返回的行集很小的查询)，则可以执行星型查询"计划，并使用称为索引交集的技术.

On a Star Schema you can get some benefit from indexing low cardinality columns, even on SQL Server. If you are doing a highly selective query (i.e. one where the query optimiser decides that the row set returned will be small) then it can do a 'star query' plan where it uses a technique known as index intersection.

通常，星型模式上的查询计划应基于事实表的表扫描或以事实表为书签，然后返回较小的一组行的高度选择性的过程.索引交集对于后一种类型的查询非常有效，因为可以在事实表上执行任何I/O之前解决选择问题.

Generally, query plans on a star schema should be based around a table scan of the fact table or a highly selective process that bookmarks the fact table and then returns a smaller set of rows. Index intersection is efficient for the latter type of query as the selection can be resolved before doing any I/O on the fact table.

对于支持基数的平台(例如Oracle)，低位索引列确实是赢家，但SQL Server不支持.即便如此，低基数索引仍然可以参与SQL Server上的星级查询计划.

Bitmap indexes are a real win for low cardinality columns on platforms such as Oracle that support them, but SQL Server does not. Even so, low cardinality indexes can still participate in star query plans on SQL Server.

这篇关于查找表外键应始终被索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！