关于Oracle外键列上是否需要索引问题

向右看齐 2021-06-24 13:59 352阅读 0赞

外键列上缺少索引会带来两个问题，限制并发性、影响性能。而这两个问题中的任意一个都可能会造成严重性能问题。虽然在oracle的高版本中对这个已经有了改善，但还是强烈建议加上。( 在mysql数据库中，外键必须要有索引 ) 这样， 虽然可能导致创建了部分多余的索引，但是这样相除了外键约束由于确实索引所带来的性能问题和并发性问题。

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 FOREIGN KEY (FID) 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 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

...

已选择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

...

已选择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> 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;

回退已完成。

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

会话二回滚：

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;

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

SQL2> ROLLBACK;

回退已完成。

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

已删除 1 行。

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

而且这种更新可能是工具帮你自动完成的，因为很多工具会自动生成SQL语句，而在这种生成的SQL语句中，UPDATE的列是表中的所有

列，所以即使主键的值没有发生变化，但是仍然是被更新了：

SQL2> DELETE T\_C WHERE ID = 2;

已删除 1 行。

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

SQL> UPDATE T\_P SET ID = 500 WHERE ID = 500;

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

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

设置级关更新/删除

Oracle 级联删除使用格参考式：

CREATE TABLE A001（ID INT PRIMAY KEY，NAME VARCHAR2（20））

CREATE TABLE A002（ID INT REFERENCES A001（ID）ON DELETE CASCADE，AGE NUMBER（2，0））

建立这个表的时候用到ON DELETE CASCADE或ON UPDATE CASCADE。

[Link 1]: http://www.2cto.com/os/

关于Oracle外键列上是否需要索引问题

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 ORALCE 表的一般操作添加列、删除列、修改列添加外键、删除外键

相关 oracle 数据库查看外键引用列上是否有索引

相关分析oracle索引空间使用情况，以及索引是否需要重建

相关 MongoDB 单键(列)索引

相关 ORACLE中关于外键缺少索引的探讨和总结

相关外键列上是否需要索引

相关关于联合外键

相关为什么需要数据库外键

相关 Oracle 外键约束

相关关于Oracle外键列上是否需要索引问题