我有一个表 A 和一个表 B . A 在 B 的主键 B_ID 上有 B 的外键 .
A
B
B_ID
由于某种原因(我知道有正当理由)当我在密钥上加入这两个表时它没有使用索引 .
我是否需要在 A.B_ID 上单独创建索引,还是应该存在外键?
A.B_ID
创建外键不会自动在A.B_ID上创建索引 . 因此,从查询性能角度来看,在A.B_ID上创建单独的索引通常是有意义的 .
如果你删除了B中的行,你肯定希望将A.B_ID编入索引 . 否则,每次从B中删除一行时,Oracle都必须对A进行全表扫描,以确保没有孤立的记录(取决于Oracle版本,可能还有其他锁定含义,但这些都会减少)在最近的Oracle版本中) .
仅外键约束不提供索引 - 必须(并且应该)创建索引 .
更多信息:Oracle不会自动创建索引(就像它对唯一约束一样),因为(a)不需要强制执行约束,(b)在某些情况下,您不需要一个 .
但是,大多数情况下,您需要创建一个索引(实际上,在Oracle Apex中有一个"unindexed foreign keys"的报告) .
每当应用程序需要能够删除父表中的行或更新PK值(这是罕见的)时,如果不存在索引,则DML将受到影响,因为它必须锁定整个子表 .
我通常选择不添加索引的情况是FK指向定义列的域的"static data"表(例如状态代码表),其中父表上的更新和删除永远不会由应用 . 但是,如果在列上添加索引可以为应用程序中的重要查询带来好处,那么索引仍然是个好主意 .
SQL Server从未将索引自动放入外键列 - 请查看Kim Tripp的excellent blog post关于这个都市神话的背景和历史 .
但是,通常最好将外键列索引 - 所以是的,我建议确保每个FK列都由索引备份;不一定只在那一列上 - 也许在两列或三列上创建索引是有意义的,其中FK列是第一列 . 取决于您的方案和数据 .
出于性能原因,应创建索引 . 用于主表上的删除操作(用于检查您正在删除的记录是否未使用)以及通常涉及外键的连接 . 只有少数表(我不在日志中创建它们)可能不需要索引,但可能在这种情况下,您可能也不需要外键约束 .
但
有些数据库已经自动在外键上创建索引 . Jet Engine(微软Access文件)Firebird MySQL
当然
SQL Server Oracle
才不是
与任何与性能有关的内容一样,它取决于许多因素,并且没有任何因素,例如在非常高的活动环境中,指数的维持可能是不可接受的 .
这里最突出的似乎是选择性:如果索引中的值高度重复,那么它可以提供更好的性能来删除索引(如果可能)并允许表扫描 .
6 回答
创建外键不会自动在A.B_ID上创建索引 . 因此,从查询性能角度来看,在A.B_ID上创建单独的索引通常是有意义的 .
如果你删除了B中的行,你肯定希望将A.B_ID编入索引 . 否则,每次从B中删除一行时,Oracle都必须对A进行全表扫描,以确保没有孤立的记录(取决于Oracle版本,可能还有其他锁定含义,但这些都会减少)在最近的Oracle版本中) .
仅外键约束不提供索引 - 必须(并且应该)创建索引 .
更多信息:Oracle不会自动创建索引(就像它对唯一约束一样),因为(a)不需要强制执行约束,(b)在某些情况下,您不需要一个 .
但是,大多数情况下,您需要创建一个索引(实际上,在Oracle Apex中有一个"unindexed foreign keys"的报告) .
每当应用程序需要能够删除父表中的行或更新PK值(这是罕见的)时,如果不存在索引,则DML将受到影响,因为它必须锁定整个子表 .
我通常选择不添加索引的情况是FK指向定义列的域的"static data"表(例如状态代码表),其中父表上的更新和删除永远不会由应用 . 但是,如果在列上添加索引可以为应用程序中的重要查询带来好处,那么索引仍然是个好主意 .
SQL Server从未将索引自动放入外键列 - 请查看Kim Tripp的excellent blog post关于这个都市神话的背景和历史 .
但是,通常最好将外键列索引 - 所以是的,我建议确保每个FK列都由索引备份;不一定只在那一列上 - 也许在两列或三列上创建索引是有意义的,其中FK列是第一列 . 取决于您的方案和数据 .
出于性能原因,应创建索引 . 用于主表上的删除操作(用于检查您正在删除的记录是否未使用)以及通常涉及外键的连接 . 只有少数表(我不在日志中创建它们)可能不需要索引,但可能在这种情况下,您可能也不需要外键约束 .
但
有些数据库已经自动在外键上创建索引 . Jet Engine(微软Access文件)Firebird MySQL
当然
SQL Server Oracle
才不是
与任何与性能有关的内容一样,它取决于许多因素,并且没有任何因素,例如在非常高的活动环境中,指数的维持可能是不可接受的 .
这里最突出的似乎是选择性:如果索引中的值高度重复,那么它可以提供更好的性能来删除索引(如果可能)并允许表扫描 .