【fk_index】外键中有无索引的区别

在外键上加索引与没有索引的区别：

主要有两方面问题：一、在DML操作时的阻塞问题，二 、DML操作时的速度问题

一、阻塞问题

外键在无索引的情况下，更新主表外键关联字段时，需要为子表加4级锁(S)；在有索引的情况下，更新主表外键关联字段时，需要为子表加2级锁（RS）。在子表上本来就已有RX时，S锁无法被兼容，造成更新主表阻塞。如果子表上本来没有锁，更新主表的操作不被阻塞时（更新完后我们暂时不commit），此刻，如果外键没有索引，4级锁（S）是“瞬间”加上的，然后就释放不易观察到；而在有索引的情况下，给子表加的2级锁（RS）会一直存在，直到更新主表的会话回滚或提交。

无论在有无外键索引的情况下，子表插入数据，需要给主表加2级锁（RS），这个操作是否会被阻塞，要看主表中对应的记录是否存在RX锁。这里是用delete进行的测试,其它DML语句情况相同。

1. 创建两张表并插入数据，模拟实验环境

BALLONTT@PROD> create table dept(deptno number,dname varchar2(10));

BALLONTT@PROD> alter table dept add constraint pk_dept primary key(deptno);

 

BALLONTT@PROD> create table emp(empno number,ename varchar2(10),deptno number);

BALLONTT@PROD> alter table emp add constraint fk_emp foreign key(deptno) references dept(deptno);

 

BALLONTT@PROD> insert into dept values(01,'aa');

BALLONTT@PROD> insert into dept values(02,'bb');

BALLONTT@PROD> insert into dept values(03,'cc');

BALLONTT@PROD> insert into dept values(04,'dd');

BALLONTT@PROD> commit;

Commit complete.

 

BALLONTT@PROD> insert into emp(empno,deptno) values(111,01);

BALLONTT@PROD> insert into emp(empno,deptno) values(222,02);

BALLONTT@PROD> commit;

Commit complete.

 

2. 确认表的信息

BALLONTT@PROD> select * from dept;

    DEPTNO DNAME

---------- ----------

         1 aa

         2 bb

         3 cc

         4 dd

BALLONTT@PROD> select empno,deptno from emp;

     EMPNO     DEPTNO

---------- ----------

       111          1

       222          2

BALLONTT@PROD> select a.object_id,a.object_name,l.session_id from

  2  all_objects a,v$locked_object l

  3  where a.object_id=l.object_id;

no rows selected

 

3. 在会话1（session_id=125）中执行下面DML操作(此时emp表中没有索引时)

BALLONTT@PROD> insert into emp(empno,deptno) values(333,3);

1 row created.

查看被锁的对象信息

BALLONTT@PROD> select a.object_id,a.object_name,l.session_id from

  2  all_objects a,v$locked_object l

  3  where a.object_id=l.object_id;

 OBJECT_ID     OBJECT_NAME                    SESSION_ID

-----------  ---------------------        ----------------

      9752   EMP                               125

      9750   DEPT                              125

 

BALLONTT@PROD> select sid,type,id1,id2,lmode,request from v$lock where sid=125;

       SID TY        ID1        ID2        LMODE    REQUEST

---------- --    ----------     ------     ----      ----------  

       125 TM       9750          0          2          0

       125 TM       9752          0          3          0

       125 TX      65558        105          6          0

上面对emp的插入操作，对dept（id:9750）加2模式表级锁（即RS锁），对EMP（id:9752）加表级锁RX（LMODE 3），和行级锁X(LMODE 6)

 

4. 紧着着在会话2(session_id=113)中对主键所在表进行DML，查看是否阻塞

BALLONTT@PROD> update dept set deptno=10 where deptno=3;---阻塞

查看锁的信息

BALLONTT@PROD> select sid,type,id1,id2,lmode,request from v$lock where sid in(113,125);

       SID TY        ID1        ID2      LMODE    REQUEST

---------- -- ---------- ---------- ---------- ----------

       125 TM       9750          0          2          0

       125 TM       9752          0          3          0

       113 TM       9750          0          3          0

       113 TM       9752          0          0          4

       125 TX      65558        105          6          0

（9750代表dept,9752代表emp）对dept的更新需要在表dept上加表级锁RX，同时向EMP表申请S锁（REQUEST 4）。但由于此时EMP上有插入操作带来的RX锁，与S锁不兼容，所以因无法得到S锁而导致对DEPT的更新操作阻塞。

update dept set deptno=16 where deptno=4; --同阻塞，原因如上。

5. 终止会话2，回滚会话1，在EMP表的外键上加索引

BALLONTT@PROD> create index ind_emp on emp(deptno);

 

6.重复上面的3步骤，并在会话2中在执行下面语句（有外键索引，下面语句需要先对子表加RS锁，然后再去申请主表RX锁，是否会被阻塞取决于子表需要被加RS锁的记录上是否已有RX锁，主表需要被加RX锁的记录是否有RS锁）

BALLONTT@PROD> update dept set deptno=10 where deptno=4;--不阻塞

查看锁的的信息：        

BALLONTT@PROD> select sid,type,id1,id2,lmode,request from v$lock where sid in(113,125);

       SID TY        ID1        ID2      LMODE    REQUEST

---------- -- ---------- ---------- ---------- ----------

       125 TM       9750          0          2          0

       125 TM       9752          0          3          0

       113 TM       9750          0          3          0

       113 TM       9752          0          2          0

       113 TX     327726        105          6          0

       125 TX     262156        107          6          0

6 rows selected.

会话1（sid:125）中对emp的插入操作形成了3个锁。（上文已说明）

会话2（sid:113）中对dept的更新操作也有三个锁，分别是在dept表上的常规更新带来的两个锁RX,和TX。第三锁为加在子表EMP上的RS锁。RS锁与EMP上已有的RX锁兼容，所以不会阻塞。

update dept set deptno=16 where deptno=3; --阻塞

二、DML操作时的速度问题·         

当使用ON DELETE CASCADE删除父表中的记录时，如果在子表中的外键没有使用索引则当执行该操作时会对子表进行全表的扫描，而事实上这个全表的扫描是不需要的。更坏的情况是，如果删除多个父表中的记录，每删除一条记录则会进行一次全表扫描，可想而知，对于性能的影响是多么的大！

对于父表和子表的连接查询，情况也是类似的。当进行这种连接查询时，如果不对外键使用索引则会发现查询的速度大大降低。

由此可知，我们应该在外键上建立索引。

ballontt
2014/03/26

---The End---

微博：weibo.com/ballontt

如需转载，请标明出处和链接，谢谢！