外键列上是否需要索引

其实这个问题应该算是老生常谈了。这两天看concept看到这里，于是就在说说这个问题。

外键列上缺少索引会带来两个问题，限制并发性、影响性能。而这两个问题中的任意一个都可能会造成严重性能问题。

无论是Oracle的官方文档，还是在Tom的书中都说明了两种情况下可以忽略外键上的索引。其实我认为不需要那么麻烦，与增加一个索引所带来的性能开销和磁盘空间开销相比，确实索引可能引发的问题要严重得多。因此，我会选择在所有的外键列上添加索引，虽然可能导致创建了部分多余的索引，但是这样相除了外键约束由于确实索引所带来的性能问题和并发性问题。

如果外键列上缺少索引，从主表关联子表的查询就只能对子表选择全表扫描的查询，这是显而易见的问题：

SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);

表已更改。

SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
'Vgi4}&Lm)L0  2  FOREIGN KEY (FID)ITPUB个人空间\iw)_h;ukU
  3  REFERENCES T_P (ID);

表已更改。

SQL> INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;

已创建884行。

SQL> INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 884) + 1, OBJECT_NAME
O4nm-Fh@1@b0  2  FROM ALL_OBJECTS;

已创建30339行。

SQL> COMMIT;

提交完成。

SQL> SELECT A.ID, A.NAME, B.NAMEITPUB个人空间1A)Lrzj Q Ew/y
  2  FROM T_P A, T_C BITPUB个人空间]R e6N/fQ-}$uJ
  3  WHERE A.ID = B.FIDITPUB个人空间$Ffm*z[
  4  AND A.ID = 880;

        ID NAME                           NAMEITPUB个人空间6F
`o'iQ
---------- ------------------------------ ------------------------------
l;OyO"R|Bk0       880 T_COMPRESS                     /eb2b6b5_Options1
3}"h7vb R1[)W$IC(v#T$i0       880 T_COMPRESS                     DATE
:puQ`$_&P*[EX0       880 T_COMPRESS                     DEF$_SCHEDULEITPUB个人空间%_8S|Zi2eM0hK(x
       880 T_COMPRESS                     GV_$SESSION_EVENT
%e}l?;~k0.
d-e-[(I(O Z0.ITPUB个人空间"V:kvS#o4T v
.ITPUB个人空间b
i]8SUnrfnng
       880 T_COMPRESS                     sun/io/ByteToCharCp1251
s?l6QR B nh4u0       880 T_COMPRESS                     /5ba3839f_DirStateFactoryResulITPUB个人空间 K{]n0Y!S!Y/tDY
       880 T_COMPRESS                     USER_INDEXTYPES

已选择34行。

Xt%b[)fp)Q*q+_0执行计划ITPUB个人空间 {\P|2?;v(U*D.K h
----------------------------------------------------------ITPUB个人空间.}oD.X!u/J z \q
   0      SELECT STATEMENT ptimizer=CHOOSE
n'BE;VakEV0   1    0   MERGE JOIN
bM'])bo.V'e5pL'`0   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_P'ITPUB个人空间xHh8R(~jjE%u
   3    2       INDEX (UNIQUE SCAN) OF 'SYS_C002964' (UNIQUE)
y%]R*kx0   4    1     FILTER
s)e `AFC*_M0   5    4       TABLE ACCESS (FULL) OF 'T_C'

5F{5B)M{laH0统计信息ITPUB个人空间Ao~3mGE-@l
----------------------------------------------------------
l)K-Jnn0          0  recursive calls
)ht-P0Q&B$?zn
G0          0  db block gets
SC|9L*b6aCse`S:iN
h0        190  consistent gets
0\3E+Y1ZD
~?0          0  physical readsITPUB个人空间i4WR8`@ s Wd oo
          0  redo sizeITPUB个人空间1N1FFGb-V q
       1829  bytes sent via SQL*Net to clientITPUB个人空间vC&s8{3N'Dt+wYA
        394  bytes received via SQL*Net from clientITPUB个人空间.e2A+?Ut]
          4  SQL*Net roundtrips to/from clientITPUB个人空间RJ*?
W{
          0  sorts (memory)ITPUB个人空间(}"m+T,v$wyz+l+E
          0  sorts (disk)ITPUB个人空间j{5f)GL/s6wh
         34  rows processed

由于缺少索引，上面的这个关联查询只能采用MERGE JOIN，而如果联立了外键列上的索引：

SQL> CREATE INDEX IND_T_C_FID ON T_C (FID);

索引已创建。

SQL> SELECT A.ID, A.NAME, B.NAMEITPUB个人空间 o6?;~3p;s
  2  FROM T_P A, T_C B
qfw5CIB5c0  3  WHERE A.ID = B.FIDITPUB个人空间a3[ \/U"v8h;p#v
  4  AND A.ID = 880;

        ID NAME                           NAMEITPUB个人空间:o4s_B$R8M
---------- ------------------------------ ------------------------------
dtHmv{b{ys0       880 T_COMPRESS                     /e1538703_EntryInfoImpl
U?
k;jx+rf0       880 T_COMPRESS                     /7b832daf_ObjectStreamClassComITPUB个人空间\ \8b-G;y6[Q%a1e
       880 T_COMPRESS                     java/awt/peer/ScrollbarPeerITPUB个人空间c,sT6K%t3IQ]DNH4KF
       880 T_COMPRESS                     /1982bd95_PermissionsEnumerato
Ek j Pt9udzox ~#?0.
0E&E
RR;xv0.ITPUB个人空间8g
l/w/s$I+o
.
$T;W!Qc:\K"^ Y0       880 T_COMPRESS                     /9ebda46b_GetInterfaceITPUB个人空间vH {Z0q6R
       880 T_COMPRESS                     /c71f85e7_DefaultPopupFactoryITPUB个人空间\f5xqL
       880 T_COMPRESS                     /7b549d81_DataFormatException

已选择34行。

O#|a:ShMC0执行计划ITPUB个人空间(q1cu|#m]!D8gq/o
----------------------------------------------------------ITPUB个人空间0d k O+w4de4RdH
   0      SELECT STATEMENT ptimizer=CHOOSEITPUB个人空间2qN\ m+DFh|
   1    0   NESTED LOOPS
%w TgV,U n0   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_P'ITPUB个人空间b+v8e5?@ ?@wBWh
   3    2       INDEX (UNIQUE SCAN) OF 'SYS_C002964' (UNIQUE)
BR H)_o
k6QqC0   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_C'
Lz\'D&S&Io8bv0   5    4       INDEX (RANGE SCAN) OF 'IND_T_C_FID' (NON-UNIQUE)

ITPUB个人空间*R8K&m!fJ`
统计信息ITPUB个人空间*R@!?)W],r
----------------------------------------------------------
&J4V0UA(~X0^*kN8_0          0  recursive calls
$wd.{_ jv0          0  db block gets
s@KK2J(WE`0         42  consistent gets
^q2RfP;X:e0          1  physical readsITPUB个人空间6P&Q6fT['| Hrk#{
          0  redo sizeITPUB个人空间2c'tJ+~/H't/P
       1829  bytes sent via SQL*Net to client
+F$iXVL F2ji"K0        394  bytes received via SQL*Net from clientITPUB个人空间`Ta3y+c9YQ'Y
          4  SQL*Net roundtrips to/from clientITPUB个人空间%Yz)e*kcDt6r
          0  sorts (memory)ITPUB个人空间~m-z
jc|2nA
          0  sorts (disk)
6Rq T%Zx0         34  rows processed

上面是影响性能的例子，下面看看外键列索引对并发性的影响：

SQL> SET AUTOT OFFITPUB个人空间j q@0pT:[X
SQL> SELECT * FROM T_P WHERE ID < 5;

        ID NAME
`%J"b'xNk,M5F0---------- ------------------------------ITPUB个人空间Ji+e)kH~&m
         1 SEG$
+M~EB([ \0         2 CLU$ITPUB个人空间d~DOWj
         3 OBJ$
SAt h5g8~Y+}0         4 FILE$

SQL> SELECT * FROM T_C WHERE ID < 5;

        ID        FID NAMEITPUB个人空间;_3BZ
_]0c
---------- ---------- ------------------------------ITPUB个人空间?,WIKp5[,V"o
         1          2 /1005bd30_LnkdConstantITPUB个人空间"I+P'M
H1k+L#M
?
         2          3 /10076b23_OraCustomDatumClosurITPUB个人空间-l"\
Ny*L)V(p4NQ
         3          4 /10297c91_SAXAttrList
-s Z9Q.e f^0         4          5 /103a2e73_DefaultEditorKitEndP

下面在另一个会话中删除子表的一条记录：

SQL> SET SQLP 'SQL2> 'ITPUB个人空间Y~g6x.{0v}
SQL2> DELETE T_C WHERE ID = 2;

已删除1行。

删除了一条为2的子表级联，其对应的主表记录ID为3，下面尝试在第一个会话新增一条ID为1000的记录，然后删除这条记录：

SQL> INSERT INTO T_P VALUES (1000, 'A');

已创建1行。

SQL> DELETE T_P WHERE ID = 1000;

已删除1行。

SQL> ROLLBACK;

回退已完成。

可以看到，并没有发生锁表的情况，这是因为子表外键列上有索引，删除主表的记录时，只会锁定子表参考主表的对应记录。

会话二回滚：

SQL2> ROLLBACK;

回退已完成。

下面删除外键索引：

SQL> DROP INDEX IND_T_C_FID;

索引已删除。

重复刚才的操作，在另一个会话执行删除操作：

SQL2> DELETE T_C WHERE ID = 2;

已删除1行。

在会话一重复插入和删除操作：

SQL> INSERT INTO T_P VALUES (1000, 'A');

已创建1行。

SQL> DELETE T_P WHERE ID = 1000;

这时会话被锁住，因为缺少了外键索引后，主表删除或更新记录会导致子表整个表被锁，而这会导致严重的系统并发问题。

SQL2> ROLLBACK;

回退已完成。

会话2回滚后，会话1的删除操作才可以继续执行：

ITPUB个人空间-j6U*|P.T
已删除1行。

SQL>

可能有些人会认为，系统中不存在删除而不会导致这个问题，其实不仅是删除，主键列的更新同样可以导致这个问题。

而且这种更新可能是工具帮你自动完成的，因为很多工具会自动生成SQL语句，而在这种生成的SQL语句中，UPDATE的列是表中的所有列，所以即使主键的值没有发生变化，但是仍然是被更新了：

SQL2> DELETE T_C WHERE ID = 2;

已删除1行。

还是删除这条ID为2的子表记录，下面在主表执行一个更新操作：

SQL> UPDATE T_P SET ID = 500 WHERE ID = 500;

可以看到，不管值是否发生了变化，只要主键列被更新，就会导致操作被锁定。

显而易见，不加索引的外键列会造成严重的性能问题，所以除非你有十分的把握，否则还是在外键列上添加索引吧。