外來鍵有無索引帶來的影響學習與測試
首先給出來自TOM<
在外來鍵未加索引的情況下,可能帶來的問題:
1 如果更新了父表的主鍵,由於外來鍵上沒有索引,所以子表會鎖住.
2 如果刪除了父表中的一行,整個子表也會被鎖住.
3 如果有DELETE CASCADE,而且沒有對子表加索引.那麼刪除父表中的每一行時都會對子表做一次全表掃描.這個全表掃描可能是不必要的,而且
如果從父表中刪除多行,父表中每刪除一行就要掃描一次子表.
4 從父表查詢子表時,速度可能會很慢.以employee/depart為例,
select * from depart,employee where employee.deptno = depart.deptno and depart.deptno=10;
那麼什麼情況下不用對外來鍵加索引呢?
1 沒有從父表刪除行
2 沒有更新父表的唯一鍵或主鍵
3 沒有從父表聯結子表
測試
1 資料準備:
drop table employee;
drop table depart;
create table depart(deptno number primary key, departname varchar2(20));
insert into depart values(10, 'sales');
insert into depart values(11, 'hr');
commit;
create table employee(
id number, name varchar2(20),deptno number references depart(deptno)
on delete cascade);
insert into employee values(1,'lin',10);
insert into employee values(2,'li',10);
insert into employee values(3,'fang',11);
insert into employee values(4,'bo',11);
commit;
子表外來鍵沒有加索引的情況:
2 Delete父表資料.
delete from depart where deptno=10;
查詢當前的鎖情況:
select sid, type, id1,id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock
where sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- ---------- --------- ------------ ---------
7 TX 262162 168 X None non-block
7 TM 29311 0 RX None non-block
7 TM 29309 0 RX None non-block
select object_name,object_id from user_objects where object_id in (29311,29309);
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------- ----------
DEPART 29309
EMPLOYEE 29311
兩個TM鎖,分別是對父表depart和子表employee加的表鎖.鎖的模式是RX.表示其它會話可以對depart的其它行做DML操作,但不能對鎖定的行
deptno=10;可以對employee的其它行做DML操作,但不能對鎖定的行deptno=10;
SQL> update depart set departname=departname ||'a' where deptno <> 10;
1 row updated
SQL> update employee set name=name ||'a' where deptno <> 10;
2 rows updated
另一個會話:
SQL> update employee set name=name ||'a' where deptno = 10;
hangup....
rollback;
3 Insert 的情況
SQL> insert into depart values(12,'executive');
1 row inserted
查詢當前的鎖情況.
select sid, type, id1,id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock
where sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- ---------- --------- ------------ ---------
7 TX 589858 165 X None non-block
7 TM 29311 0 RS None non-block
7 TM 29309 0 RX None non-block
在子表上加的是RS模式的TM鎖
4 Update 父表的情況
SQL> update depart set departname=departname ||'a' where deptno=10;
1 row updated
SQL>select sid, type, id1,id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock
where sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- ---------- --------- ------------ ---------
7 TX 65565 187 X None non-block
7 TM 29309 0 RX None non-block
結論 1:在Employee表的deptno即外來鍵沒有索引的時候.
對父表的DML操作都會在父表上加X模式的TX鎖和RX模式的TM鎖
對父表的Delete操作會對子表加RX的TM鎖
對父表的Insert操作會對子表加RS的TM鎖
對父表的Update操作不會對子表任何的鎖
只有父表Delete,即對子表加了RX鎖時,才會鎖住子表中對應的行.
(Insert 操作不可能對應的行,所以不會鎖住)
SQL> delete from depart where deptno=10;
1 row deleted
另一個 session
SQL> delete from employee where deptno=10;
Hangup....
這與<
對子表Employee的Delete操作
SQL> delete from employee where deptno=10;
2 rows deleted
SQL>select sid, type, id1,id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock
where sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- ---------- --------- ------------ ---------
9 TX 393263 172 X None non-block
9 TM 29311 0 RX None non-block
9 TM 29309 0 RS None non-block
對子表的Insert操作
SQL> insert into employee values(5,'bo',11);
1 row inserted
SQL>select sid, type, id1,id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock
where sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- ---------- --------- ------------ ---------
9 TX 458763 166 X None non-block
9 TM 29311 0 RX None non-block
9 TM 29309 0 RS None non-block
與Delete操作一樣的鎖
對子表的Update操作
SQL> update employee set name=name ||'a' where deptno=10;
2 rows updated
select sid, type, id1, b.object_name, id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock, user_objects b
where b.object_id(+) = id1
and sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 OBJECT_NAME ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- --------------- ---------- --------- ------------ ---------
9 TM 29309 DEPART 0 RS None non-block
9 TM 29311 EMPLOYEE 0 RX None non-block
9 TX 458763 166 X None non-block
與Delete操作一樣的鎖
結論 2 :
對子表的Update/insert/delete操作,在父表和子表上加的鎖都一致.
#########################################################################
現在測試對子表外來鍵加索引的情況:
加什麼索引呢? 普通索引
SQL> create index ind_deptno on employee(deptno);
Index created
delete from depart where departno=10;
結論 3:
加索引後,對父表的DML操作所加的鎖與沒有加索引的情況下的操作是完全一樣的.
這裡明顯與TOM的結論不一致,TOM說加索引後,如果刪除父表中的一行,不會鎖住子表,實際看到的,同樣會對子表加RX模式的TM鎖.
這是怎麼回事呢?
加索引後,對子表的Delete, Insert操作所加的鎖與沒有加索引的情況下的操作是完全一樣的.
只有在Update的操作下有差別:
select sid, type, id1, b.object_name, id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock, user_objects b
where b.object_id(+) = id1
and sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 OBJECT_NAME ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- --------------- ---------- --------- ------------ ---------
9 TM 29311 EMPLOYEE 0 RX None non-block
9 TX 65573 187 X None non-block
沒有對父表的鎖
結論:
以上測試的結果與<
->Referential Integrity Constraints ->Concurrency Control, Indexes, and Foreign Keys中描述的不一致?
是哪裡出問題了?
1 加的索引不對.唯一索引是不能加的.因為子表中肯定會存在多行相同的deptno.
2 沒能真正理解阻塞的意思?
Notes:
1 查詢沒有加索引的外來鍵
select table_name, constraint_name,
cname1 || nvl2(cname2,','|| cname2,null) ||
nvl2(cname3,','|| cname3,null) || nvl2(cname4,','|| cname4,null) ||
nvl2(cname5,','|| cname5,null) || nvl2(cname6,','|| cname6,null) ||
nvl2(cname7,','|| cname7,null) || nvl2(cname8,','|| cname8,null)
columns
from (
select b.table_name,b.constraint_name,
max(decode(position,1,column_name,null)) cname1,
max(decode(position,2,column_name,null)) cname2,
max(decode(position,3,column_name,null)) cname3,
max(decode(position,4,column_name,null)) cname4,
max(decode(position,5,column_name,null)) cname5,
max(decode(position,6,column_name,null)) cname6,
max(decode(position,7,column_name,null)) cname7,
max(decode(position,8,column_name,null)) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name, 1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name ) cons
where col_cnt > all (
select count(*) from user_ind_columns i where i.table_name = cons.table_name
and i.column_name in( cname1, cname2,cname3,cname4,cname5,cname6,cname7,cname8)
and i.column_position < cons.col_cnt
group by i.index_name
);
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10248702/viewspace-624533/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 外來鍵索引影響阻塞問題Oracle索引
- 查詢沒有索引的外來鍵索引
- 【實驗】【外來鍵】小議外來鍵約束對應用程式的影響
- 檢查外來鍵是否有索引的指令碼索引指令碼
- 【fk_index】外來鍵中有無索引的區別Index索引
- 外來鍵沒有索引哪些DML操作會被阻塞索引
- 全域性索引失效帶來的幾個測試場景索引
- HTML中清除浮動帶來的影響HTML
- stopkey對索引掃描的影響測試TopK索引
- 父表修改與外來鍵的關係（主鍵DML與外來鍵的關係）
- indexedDB 內鍵與外來鍵Index
- 外來鍵缺索引檢查指令碼索引指令碼
- 無索引的外來鍵之主表子表DML操作實驗及結論索引
- 表外來鍵未加索引之處理索引
- 人工智慧與深度學習的未來：社會影響、應用與研究進展人工智慧深度學習
- .NET Framework開源給開發者帶來不同影響Framework
- SQL SERVER中找出拙劣的約束，索引，外來鍵SQLServer索引
- CSS3 transform 對HTML文件流帶來的影響CSSS3ORMHTML
- MYSQL的外來鍵MySql
- oracle查詢表資訊（索引，外來鍵，列等）Oracle索引
- 視訊遊戲未來：改變不良形象 帶來更多積極影響遊戲
- append提示與外來鍵還有ORA-12838APP
- enq: TM - contention解決之道——外來鍵無索引導致鎖爭用ENQ索引
- sqlserver外來鍵SQLServer
- 福布斯：區塊鏈——社會影響力未來的關鍵區塊鏈
- mysql中的外來鍵MySql
- enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（上）ENQ索引
- enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）ENQ索引
- 【Oracle】-【ROWNUM與索引】-索引對ROWNUM檢索的影響Oracle索引
- YOLO之父宣佈退出CV界，坦言無法忽視自己工作帶來的負面影響YOLO
- 享受軟體測試帶來的一切
- 外來鍵技術
- oracle外來鍵索引解決父表鎖定問題Oracle索引
- 軟體測試對軟體質量的影響有那些？
- 查詢(看)表的主鍵、外來鍵、唯一性約束和索引索引
- 批量修改欄位長度，考慮主鍵外來鍵索引的情況【轉】索引
- Mysql 5.7 Gtid內部學習(七) 總結binlog_gtid_simple_recovery引數帶來的影響MySql
- CMO Club：DTC品牌給B2C行業帶來的影響行業