Oracle外鍵要建立索引的原理和實驗

Oracle外鍵要建立索引的原理和實驗

項目中，我們要求凡是有主子關係的表都要使用外鍵約束，來保證主子表之間關係的正確，不推薦由應用自己控制這種關係。

但發現有時開發人員提交SQL語句時未必會注意外鍵列需要定義索引，或者不清楚為什麼外鍵列需要建立索引，網上一些所謂的“寶典”也會將外鍵列建索引作為其中的一條，包括TOM大師，曾說過：

導致死結的頭號原因是外鍵未加索引（第二號原因是表上的位元影像索引遭到並發更新）。在以下兩種情況下，Oracle在修改父表後會對子表加一個全表鎖：
1）如果更新了父表的主鍵（倘若遵循關聯式資料庫的原則，即主鍵應當是不可變的，這種情況就很少見），由於外鍵上沒有索引，所以子表會被鎖住。
2）如果刪除了父表中的一行，整個子表也會被鎖住（由於外鍵上沒有索引）。

因此，無論從什麼角度看，都有必要從原理上好好理解外鍵為何需要建立索引，或者說外鍵不建立索引會有什麼問題？

首先我們看下Concept官方是怎麼描述這個問題。
(引用：E11882_01/server.112/e40540/consist.htm#CNCPT88978)

Indexes and Foreign Keys
As a rule, foreign keys should be indexed. The only exception is when the matching unique or primary key is never updated or deleted. Indexing the foreign keys in child tables provides the following benefits:
(1) Prevents a full table lock on the child table. Instead, the database acquires a row lock on the index.
(2) Removes the need for a full table scan of the child table. As an illustration, assume that a user removes the record for department 10 from the departments table. If employees.department_id is not indexed, then the database must scan employees to see if any employees exist in department 10.
Locks and Foreign Keys
Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.
Locks and Unindexed Foreign Keys
When both of the following conditions are true, the database acquires a full table lock on the child table:
(1) No index exists on the foreign key column of the child table.
(2) A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.
Suppose that hr.departments table is a parent of hr.employees, which contains the unindexed foreign key department_id. Figure 9-3 shows a session modifying the primary key attributes of department 60 in the departments table.
In Figure 9-3, the database acquires a full table lock on employees during the primary key modification of department 60. This lock enables other sessions to query but not update the employees table. For example, employee phone numbers cannot be updated. The table lock on employees releases immediately after the primary key modification on the departments table completes. If multiple rows in departments undergo primary key modifications, then a table lock on employees is obtained and released once for each row that is modified in departments.
Locks and Indexed Foreign Keys
When both of the following conditions are true, the database does not acquire a full table lock on the child table:
(1) A foreign key column in the child table is indexed.
(2) A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table.
A lock on the parent table prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or child table during the primary key modification. This situation is preferable if primary key modifications occur on the parent table while updates occur on the child table.
Figure 9-4 shows child table employees with an indexed department_id column. A transaction deletes department 280 from departments. This deletion does not cause the database to acquire a full table lock on the employees table as in the scenario described in “Locks and Unindexed Foreign Keys”.
If the child table specifies ON DELETE CASCADE, then deletions from the parent table can result in deletions from the child table. For example, the deletion of department 280 can cause the deletion of records from employees for employees in the deleted department. In this case, waiting and locking rules are the same as if you deleted rows from the child table after deleting rows from the parent table.

按照官方文檔的說明，

只有當唯一鍵或主鍵不被更新或刪除的情況下，才不需要為外鍵建立索引。為子表外鍵建立索引可以有兩個好處： 
(1) 避免子表上有表鎖，取而代之的是，資料庫會擷取索引上的行鎖。 
(2) 避免子表上的全表掃描。假設刪除departments主表id=10的記錄，如果employees子表的department_id外鍵沒有索引，那麼就會全表掃描employees子表，以確認是否存在department id=10的記錄。
當滿足以下兩個條件時，會擷取子表的表鎖： 
(1) 子表外鍵不存在索引。 
(2) 修改主表的主鍵(例如，刪除一行記錄或者修改主索引值)或者合并主表的多行記錄。向主表插入記錄不會擷取子表的表鎖。 
只有當主索引值修改完成，子表的表鎖才會被放開。

效果是這樣嗎？我們是用實驗來驗證。

建立測試表

SQL> create table t1  2  (id number,     3   name varchar2(1)  4  );Table created.SQL> alter table t1 add constraint pk_t1 primary key (id);Table altered.SQL> create table t2  2  (id number,  3   t1_id number,  4   name varchar2(1)  5  );Table created.SQL> alter table t2 add constraint pk_t2 primary key (id);Table altered.SQL> alter table t2 add constraint fk_t2 foreign key (t1_id) references t1(id);Table altered.SQL> insert into t1 values(1, 'a');1 row created.SQL> insert into t1 values(2, 'b');1 row created.SQL> insert into t2 values(1, 1, 'c');1 row created.SQL> commit;Commit complete.SQL> select * from t1;ID N---------- - 1 a 2 bSQL> select * from t2;ID  T1_ID N---------- ---------- - 1  1 c 

t1是主表，t2是子表，t2的t1_id列是外鍵，參考t1表的id主鍵列。

實驗1

session 1刪除主表id=2的記錄：

SQL> delete from t1 where id = 2;1 row deleted. 

session 2刪除子表id=1的記錄：

SQL> delete from t2 where id = 1;1 row deleted. 

發現好像並沒有像文檔中描述的，刪除主表一行記錄，就會鎖住子表整張表，這是為什嗎？我們先繼續看實驗2。

實驗2

session 1刪除子表id=1的記錄(sid是150)：

SQL> delete from t2 where id = 1;1 row deleted.或update t2 set name = 'c' where id = 1; 

session 2刪除主表id=2的記錄(sid是144)：

SQL> delete from t1 where id = 2; 

這個session處於hang的狀態。

SQL> select object_name from dba_objects where object_id = 76828;OBJECT_NAMET1SQL> select object_name from dba_objects where object_id = 76830;OBJECT_NAMET2 

我們可以看出session 1有兩個TM鎖，一個TX鎖，session 2有兩個TM鎖。

這裡隱含的知識點就是v$lock視圖中ID1和ID2列的含義，Reference手冊中有介紹，但基本沒什麼用。

參考secooler老師的文章(http://m.blog.itpub.net/519536/viewspace-693689/)，參閱MOS：29787.1，得知了ID1和ID2更詳細的說明：

對於TM鎖來說，ID1表示表對象，ID2一般是0。

對於TX鎖來說，ID1表示Decimal RBS & slot，當前事務的復原段編號和槽位號(十進位，RBS和slot的組合，根據0xRRRRSSSS RRRR = RBS number, SSSS = slot的定義，高16位表示RBS值，對應於VTRANSACTION中的XIDUSN欄位，低16位表示slot值，對應於VTRANSACTION中的XIDSLOT欄位)，ID2表示Decimal WRAP number，序號。

因此兩個TM鎖，其中object_id=76828對應的是T1表，object_id=76830對應的是T2表。

對於TX這行記錄，確實能夠按照如下計算得到當前事務的復原段編號、槽位號以及序號。

SQL> select trunc(589843/65536) from dual;TRUNC(589843/65536)  9SQL> select mod(589843,65536) from dual;MOD(589843,65536)   19SQL> select XIDUSN,XIDSLOT,XIDSQN from V$TRANSACTION where XIDSQN=715;XIDUSN  XIDSLOT   XIDSQN 9            19               715 

進一步，我們從處於hang的session 2，即執行刪除主表操作的10046事件中還可以發現一些細節：

PARSING IN CURSOR #11135800 len=25 dep=0 uid=90 oct=7 lid=90 tim=1458405691531128 hv=2708121416 ad='526137f8' sqlid='9kjcfz6hqp9u8'delete from t1 where id=2WAIT #11135800: nam='enq: TM - contention' ela= 4202458 name|mode=1414332420 object #=76830 table/partition=0 obj#=76830 tim=1458405695733683PARSING IN CURSOR #11130048 len=68 dep=1 uid=0 oct=3 lid=0 tim=1458405695736681 hv=4142137182 ad='57d9d9f0' sqlid='cpbvjc7vf7zuy' select /*+ all_rows */ count(1) from "TEST"."T2" where "T1_ID" = :1END OF STMTPARSE #11130048:c=2000,e=2221,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1458405695736680STAT #11128324 id=2 cnt=1 pid=1 pos=1 obj=76830 op='TABLE ACCESS FULL T2 (cr=7 pr=0 pw=0 time=111 us cost=2 size=0 card=82)' 

從這塊可以得出兩個結論：

(1) 此時刪除主表記錄時，會出現一個TM鎖爭用，需要說明的是，trc檔案中這條資訊是在session 1執行commit或rollback釋放子表之後才寫入的，我們看到session 1的執行，導致session 2執行需要請求T2表的TM鎖時出現了等待。

(2) 刪除主表記錄的過程中，會根據外鍵欄位檢索子表記錄，select /+ all_rows / count(1) from “TEST”.”T2” where “T1_ID” = :1，這就意味著，如果外鍵沒有索引，則這條SQL語句會執行全表掃描，從後面的TABLE ACCESS FULL T2也證明了。

實驗3

和實驗2操作過程相同，只是session 2從delete操作換為了update主鍵操作：update t1 set id=3 where id=2

效果和實驗2相同，session 2處於hang，且從10046事件看，和實驗2還是相同：

PARSING IN CURSOR #11123668 len=29 dep=0 uid=90 oct=6 lid=90 tim=1458407400035758 hv=1728227981 ad='5278e0fc' sqlid='43bqtdxmh5and' 97 update t1 set id=3 where id=2PARSING IN CURSOR #11109876 len=68 dep=1 uid=0 oct=3 lid=0 tim=1458407409907499 hv=4142137182 ad='57d9d9f0' sqlid='cpbvjc7vf7zuy'105  select /*+ all_rows */ count(1) from "TEST"."T2" where "T1_ID" = :1 

實驗4

session 1刪除子表id=1的記錄：

SQL> delete from t2 where id = 1;1 row deleted. 

session 2更新主表id=2記錄的非主鍵欄位：

SQL> update t1 set name = 'c' where id = 2;1 row updated. 

沒有出現hang，可以操作。

實驗5

session 1插入子表新記錄：

SQL> insert into t2 values(2, 1, 'c');1 row created. 

session 2插入主表新記錄：

SQL> insert into t1 values(3, 'c');1 row created. 

沒有出現hang，可以操作。

實驗6

建立外鍵索引，看下效果，

SQL> create index idx_t2_id on t2(t1_id);Index created. 

session 1刪除子表id=1的記錄：

SQL> delete from t2 where id = 1;1 row deleted. 

session 2刪除主表id=2的記錄：

SQL> delete from t1 where id = 2; 

此時沒有hang，

確實沒有鎖：

查看10046事件：

PARSING IN CURSOR #12183444 len=27 dep=0 uid=90 oct=7 lid=90 tim=1458479427981508 hv=3481522657 ad='57ded014' sqlid='87pqrfv7s7ng1'delete from t1 where id = 2END OF STMTPARSE #12183444:c=1999,e=2469,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1636297587,tim=1458479427981505EXEC #12183444:c=0,e=365,p=0,cr=1,cu=6,mis=0,r=1,dep=0,og=1,plh=1636297587,tim=1458479427982059STAT #12183444 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  T1 (cr=1 pr=0 pw=0 time=298 us)'STAT #12183444 id=2 cnt=1 pid=1 pos=1 obj=76829 op='INDEX UNIQUE SCAN PK_T1 (cr=1 pr=0 pw=0 time=26 us cost=0 size=3 card=1)'WAIT #12183444: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1458479427983901 

確實沒有產生鎖爭用的現象。

總結：

(1) 外鍵沒有索引，確實可能導致子表產生表鎖，但是有前提：
a. 子表有刪改操作。
b. 主表有刪操作，或者更新主鍵的操作。
滿足以上兩個條件才會出現主表操作hang狀態。

(2) 外鍵不建索引，則刪除主表記錄或主子表關聯查詢，都會進行子表的全表掃描。

(3) 主子表任何插入操作，無論順序，不會產生鎖或hang狀態。

(4) 只有外鍵建立索引，(1)中的操作才不會出現鎖或hang狀態，(2)中的操作才有可能使用索引。

通過以上實驗，至少對外鍵不建立索引產生的影響，有了一些感性的認識，對外鍵為何要建立索引，應該有了更深入的理解。