I have a table A and a table B. A has a foreign key to B on B's primary key, B_ID.
我有一个表a和一个表B。a在B的主键B_ID上有一个外键B。
For some reason (I know there are legitimate reasons) it is not using an index when I join these two tables on the key.
出于某种原因(我知道有合理的原因)，当我在键上连接这两个表时，它没有使用索引。
Do I need to separately create an index on A.B_ID or should the existence of a foreign key provide that?
我需要在A上单独创建索引吗?B_ID或者外键的存在应该提供这个吗?
6 个解决方案
#1
112
The foreign key constraint alone does not provide the index - one must (and should) be created.
外键约束本身并不提供索引——必须(而且应该)创建索引。
#2
37
Creating a foreign key does not automatically create an index on A.B_ID. So it would generally make sense from a query performance perspective to create a separate index on A.B_ID.
创建外键不会自动在A.B_ID上创建索引。因此，从查询性能的角度来看，在A.B_ID上创建一个单独的索引通常是有意义的。
If you ever delete rows in B, you definitely want A.B_ID to be indexed. Otherwise, Oracle will have to do a full table scan on A every time you delete a row from B to make sure that there are no orphaned records (depending on the Oracle version, there may be additional locking implications as well, but those are diminished in more recent Oracle versions).
如果你删除了B中的行，你肯定想要A。B_ID索引。否则,Oracle将不得不做一个全表扫描你每次删除一行从B到确保没有孤儿记录(根据Oracle版本,可能会有额外的锁的影响,但这些都是在最近减少Oracle版本)。
#3
22
Just for more info: Oracle doesn't create an index automatically (as it does for unique constraints) because (a) it is not required to enforce the constraint, and (b) in some cases you don't need one.
更多信息:Oracle不会自动创建索引(就像它对唯一约束那样)，因为(a)不需要强制约束，(b)在某些情况下不需要约束。
Most of the time, however, you will want to create an index (in fact, in Oracle Apex there's a report of "unindexed foreign keys").
然而，大多数时候，您将希望创建一个索引(实际上，在Oracle Apex中有一个“未索引的外键”报告)。
Whenever the application needs to be able to delete a row in the parent table, or update the PK value (which is rarer), the DML will suffer if no index exists, because it will have to lock the entire child table.
每当应用程序需要能够删除父表中的一行或更新PK值(更少见)时，如果没有索引存在，DML将会受到影响，因为它必须锁定整个子表。
A case where I usually choose not to add an index is where the FK is to a "static data" table that defines the domain of a column (e.g. a table of status codes), where updates and deletes on the parent table are never done directly by the application. However, if adding an index on the column gives benefits to important queries in the application, then the index will still be a good idea.
我通常选择不添加索引的情况是，FK位于定义列(例如状态码表)域的“静态数据”表中，而父表上的更新和删除从不直接由应用程序完成。但是，如果在列上添加索引对应用程序中的重要查询有好处，那么索引仍然是一个好主意。
#4
12
SQL Server has never put indexes onto foreign key columns automatically - check out Kim Tripp's excellent blog post on the background and history of this urban myth.
SQL Server从来没有自动将索引放在国外的关键列上——看看Kim Tripp关于这个城市神话的背景和历史的优秀博客文章。
It's usually a good idea to index your foreign key columns, however - so yes, I would recommend making sure each FK column is backed up by an index; not necessarily on that one column alone - maybe it can make sense to create an index on two or three columns with the FK column as the first one in there. Depends on your scenario and your data.
通常来说，索引外键列是一个好主意，但是——因此，我建议确保每个FK列都有一个索引作为支持;不一定只在这一列上——也许在两或三列上创建索引，FK列作为第一列是有意义的。取决于您的场景和数据。
#5
4
For performance reasons an index should be created. Is used in delete operations on primary table (to check that the record you are deleting is not used) and in joins that usually a foreign key is involved. Only few tables (I do not create them in logs) could be that do not need the index but probably, in this cases probably you don't need the foreign key constraint as well.
出于性能原因，应该创建索引。用于主表上的删除操作(检查正在删除的记录未被使用)和连接(通常涉及外键)。只有很少的表(我没有在日志中创建它们)不需要索引，但是在这种情况下，可能也不需要外键约束。
BUT
但
There are some databases that already automatically create indexes on foreign Keys. Jet Engine (Microsoft Access Files) Firebird MySQL
有些数据库已经自动地在外键上创建索引。喷气引擎(Microsoft Access Files) Firebird MySQL
FOR SURE
可以肯定的是
SQL Server Oracle
SQL Server甲骨文
DOES NOT
不
#6
1
As with anything relating to performance, it depends on many factors and there is no silve bullet e.g. in a very high activilty environment the maintainance of an index may be unacceptable.
与任何与性能相关的东西一样，它依赖于许多因素，并且没有子项目。例如，在非常活跃的环境中，索引的维护可能是不可接受的。
Most salient here would seem to be selectivity: if the values in the index would be highly duplicated then it may give better performance to drop the index (if possible) and allow a table scan.
这里最突出的似乎是选择性:如果索引中的值将被高度重复，那么删除索引(如果可能的话)并允许表扫描可能会带来更好的性能。
#1
112
The foreign key constraint alone does not provide the index - one must (and should) be created.
外键约束本身并不提供索引——必须(而且应该)创建索引。
#2
37
Creating a foreign key does not automatically create an index on A.B_ID. So it would generally make sense from a query performance perspective to create a separate index on A.B_ID.
创建外键不会自动在A.B_ID上创建索引。因此，从查询性能的角度来看，在A.B_ID上创建一个单独的索引通常是有意义的。
If you ever delete rows in B, you definitely want A.B_ID to be indexed. Otherwise, Oracle will have to do a full table scan on A every time you delete a row from B to make sure that there are no orphaned records (depending on the Oracle version, there may be additional locking implications as well, but those are diminished in more recent Oracle versions).
如果你删除了B中的行，你肯定想要A。B_ID索引。否则,Oracle将不得不做一个全表扫描你每次删除一行从B到确保没有孤儿记录(根据Oracle版本,可能会有额外的锁的影响,但这些都是在最近减少Oracle版本)。
#3
22
Just for more info: Oracle doesn't create an index automatically (as it does for unique constraints) because (a) it is not required to enforce the constraint, and (b) in some cases you don't need one.
更多信息:Oracle不会自动创建索引(就像它对唯一约束那样)，因为(a)不需要强制约束，(b)在某些情况下不需要约束。
Most of the time, however, you will want to create an index (in fact, in Oracle Apex there's a report of "unindexed foreign keys").
然而，大多数时候，您将希望创建一个索引(实际上，在Oracle Apex中有一个“未索引的外键”报告)。
Whenever the application needs to be able to delete a row in the parent table, or update the PK value (which is rarer), the DML will suffer if no index exists, because it will have to lock the entire child table.
每当应用程序需要能够删除父表中的一行或更新PK值(更少见)时，如果没有索引存在，DML将会受到影响，因为它必须锁定整个子表。
A case where I usually choose not to add an index is where the FK is to a "static data" table that defines the domain of a column (e.g. a table of status codes), where updates and deletes on the parent table are never done directly by the application. However, if adding an index on the column gives benefits to important queries in the application, then the index will still be a good idea.
我通常选择不添加索引的情况是，FK位于定义列(例如状态码表)域的“静态数据”表中，而父表上的更新和删除从不直接由应用程序完成。但是，如果在列上添加索引对应用程序中的重要查询有好处，那么索引仍然是一个好主意。
#4
12
SQL Server has never put indexes onto foreign key columns automatically - check out Kim Tripp's excellent blog post on the background and history of this urban myth.
SQL Server从来没有自动将索引放在国外的关键列上——看看Kim Tripp关于这个城市神话的背景和历史的优秀博客文章。
It's usually a good idea to index your foreign key columns, however - so yes, I would recommend making sure each FK column is backed up by an index; not necessarily on that one column alone - maybe it can make sense to create an index on two or three columns with the FK column as the first one in there. Depends on your scenario and your data.
通常来说，索引外键列是一个好主意，但是——因此，我建议确保每个FK列都有一个索引作为支持;不一定只在这一列上——也许在两或三列上创建索引，FK列作为第一列是有意义的。取决于您的场景和数据。
#5
4
For performance reasons an index should be created. Is used in delete operations on primary table (to check that the record you are deleting is not used) and in joins that usually a foreign key is involved. Only few tables (I do not create them in logs) could be that do not need the index but probably, in this cases probably you don't need the foreign key constraint as well.
出于性能原因，应该创建索引。用于主表上的删除操作(检查正在删除的记录未被使用)和连接(通常涉及外键)。只有很少的表(我没有在日志中创建它们)不需要索引，但是在这种情况下，可能也不需要外键约束。
BUT
但
There are some databases that already automatically create indexes on foreign Keys. Jet Engine (Microsoft Access Files) Firebird MySQL
有些数据库已经自动地在外键上创建索引。喷气引擎(Microsoft Access Files) Firebird MySQL
FOR SURE
可以肯定的是
SQL Server Oracle
SQL Server甲骨文
DOES NOT
不
#6
1
As with anything relating to performance, it depends on many factors and there is no silve bullet e.g. in a very high activilty environment the maintainance of an index may be unacceptable.
与任何与性能相关的东西一样，它依赖于许多因素，并且没有子项目。例如，在非常活跃的环境中，索引的维护可能是不可接受的。
Most salient here would seem to be selectivity: if the values in the index would be highly duplicated then it may give better performance to drop the index (if possible) and allow a table scan.
这里最突出的似乎是选择性:如果索引中的值将被高度重复，那么删除索引(如果可能的话)并允许表扫描可能会带来更好的性能。