ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 外键有无索引带来的影响学习与测试(转)

外键有无索引带来的影响学习与测试(转)

原创 Linux操作系统 作者:bbs159 时间:2011-07-11 18:44:06 0 删除 编辑


首先给出来自TOM<>P202中的一些结论:
在外键未加索引的情况下,可能带来的问题:

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

没有对父表的锁

结论:

以上测试的结果与<>中Data Integrity -> types of Integrity Constraint
->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/13824386/viewspace-701888/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-11

  • 博文量
    26
  • 访问量
    40753