【fk_index】外來鍵中有無索引的區別

在外來鍵上加索引與沒有索引的區別：

主要有兩方面給，一、在DML操作時的阻塞問題，二 、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）中執行下面操作（先是沒有索引時）：

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，和行級鎖（X）

 

4.緊接著在會話2（session_id=113）中執行下面操作，看是否阻塞：

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鎖。但由於此時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; --阻塞

 

 

總結：外來鍵在無索引的情況下，更新主表外來鍵關聯欄位時，需要為子表加4級鎖(S)；在有索引的情況下，更新主表外來鍵關聯欄位時，需要為子表加2級鎖（RS）。在子表上本來就已有RX時，S鎖無法被相容，造成更新主表阻塞。如果子表上本來沒有鎖，更新主表的操作不被阻塞時（更新完後我們暫時不commit），此刻，如果外來鍵沒有索引，4級鎖（S）是“瞬間”加上的，然後就釋放不易觀察到；而在有索引的情況下，給子表加的2級鎖（RS）會一直存在，直到更新主表的會話回滾或提交。

無論在有無外來鍵索引的情況下，子表插入資料，需要給主表加2級鎖（RS），這個操作是否會被阻塞，要看主表中對應的記錄是否存在RX鎖。這裡是用delete進行的測試,其它DML語句情況相同。

 

二、DML操作時的速度問題

·         當使用ON DELETE CASCADE刪除父表中的記錄時，如果在子表中的外來鍵沒有使用索引則當執行該操作時會對子表進行全表的掃描，而事實上這個全表的掃描是不需要的。更壞的情況是，如果刪除多個父表中的記錄，每刪除一條記錄則會進行一次全表掃描，可想而知，對於效能的影響是多麼的大！

·         對於父表和子表的連線查詢，情況也是類似的。當進行這種連線查詢時，如果不對外來鍵使用索引則會發現查詢的速度大大降低。

 

由此可知，我們應該在外來鍵上建立索引。

 

ballontt

2013/4/12 

---The End---

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27425054/viewspace-758349/，如需轉載，請註明出處，否則將追究法律責任。