外键列上是否需要索引

男娘i 2022-05-21 10:58 113阅读 0赞

外键列上是否需要索引

其实这个问题应该算是老生常谈了。这两天看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  
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 ptimizer=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 ptimizer=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

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

SQL> SET AUTOT OFF  
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;

回退已完成。

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

会话二回滚：