Oracle外键列上需要建索引

在外键列上缺少索引会带来两个严重的性能问题：限制并发性、影响性能

1、外键列上缺少索引，当主表关联子表的查询时会对子表进行全表扫描

如：

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
2 FOREIGN KEY (FID)
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
2 FROM ALL_OBJECTS;

已创建30339行。

SQL> COMMIT;

提交完成。

SQL> SELECT A.ID, A.NAME, B.NAME
2 FROM T_P A, T_C B
3 WHERE A.ID = B.FID
4 AND A.ID = 880;

ID NAME NAME
---------- ------------------------------ ------------------------------
880 T_COMPRESS /eb2b6b5_Options1
880 T_COMPRESS DATE
880 T_COMPRESS DEF$_SCHEDULE
880 T_COMPRESS GV_$SESSION_EVENT
.
.
.
880 T_COMPRESS sun/io/ByteToCharCp1251
880 T_COMPRESS /5ba3839f_DirStateFactoryResul
880 T_COMPRESS USER_INDEXTYPES

已选择34行。

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_P'
3 2 INDEX (UNIQUE SCAN) OF 'SYS_C002964' (UNIQUE)
4 1 FILTER
5 4 TABLE ACCESS (FULL) OF 'T_C'

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
190 consistent gets
0 physical reads
0 redo size
1829 bytes sent via SQL*Net to client
394 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34 rows processed
 

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

SQL> CREATE INDEX IND_T_C_FID ON T_C (FID);

索引已创建。

SQL> SELECT A.ID, A.NAME, B.NAME
2 FROM T_P A, T_C B
3 WHERE A.ID = B.FID
4 AND A.ID = 880;

ID NAME NAME
---------- ------------------------------ ------------------------------
880 T_COMPRESS /e1538703_EntryInfoImpl
880 T_COMPRESS /7b832daf_ObjectStreamClassCom
880 T_COMPRESS java/awt/peer/ScrollbarPeer
880 T_COMPRESS /1982bd95_PermissionsEnumerato
.
.
.
880 T_COMPRESS /9ebda46b_GetInterface
880 T_COMPRESS /c71f85e7_DefaultPopupFactory
880 T_COMPRESS /7b549d81_DataFormatException

已选择34行。

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_P'
3 2 INDEX (UNIQUE SCAN) OF 'SYS_C002964' (UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_C'
5 4 INDEX (RANGE SCAN) OF 'IND_T_C_FID' (NON-UNIQUE)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
42 consistent gets
1 physical reads
0 redo size
1829 bytes sent via SQL*Net to client
394 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34 rows processed

 

2、外键列索引对并发性的影响：

SQL> SELECT * FROM T_P WHERE ID < 5;

ID NAME
---------- ------------------------------
1 SEG$
2 CLU$
3 OBJ$
4 FILE$

SQL> SELECT * FROM T_C WHERE ID < 5;

ID FID NAME
---------- ---------- ------------------------------
1 2 /1005bd30_LnkdConstant
2 3 /10076b23_OraCustomDatumClosur
3 4 /10297c91_SAXAttrList
4 5 /103a2e73_DefaultEditorKitEndP
 

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

SQL> SET SQLP 'SQL2> '
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的删除操作才可以继续执行：

已删除 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;

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

    显而易见，不加索引的外键列会造成严重的性能问题