ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 为什么子表外键列需要建立索引?(上)

为什么子表外键列需要建立索引?(上)

原创 Linux操作系统 作者:realkid4 时间:2011-07-10 15:05:08 0 删除 编辑

 

外键是我们经常使用的数据库约束方式。它保证了子表上的特定索引列取值一定与父表列(主键列)相一致对应。而且保证在子表没有对应子记录的情况下,父表数据才能删除。应该说,外键是实现数据库完整性、消除潜在脏数据风险的重要手段。

 

在使用外键的时候,我们经常会要求在子表外键列上建立索引。这个“江湖定则”有什么样的原因呢?本篇我们一起来进行探讨。还是使用组合实验的方式来进行研究。

 

 

1、 环境准备

 

我们在Oracle 11gR2版本上进行试验,构建初步的实验环境。

 

首先建立父子表关系。

 

 

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0    Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

--环境准备

SQL> create table master (id number, vname varchar2(10));

Table created

 

SQL> alter table master add constraint pk_master primary key (id);

Table altered

 

SQL> create table detail (did number, mid number, details varchar2(10));

Table created

 

SQL> alter table detail add constraint pk_detail primary key (did);

Table altered

 

SQL> alter table detail add constraint fk_detail_master_mid foreign key (mid) references master(id);

Table altered

 

 

上面建立了数据表master和detail,两个数据表分别以id和did作为主键。同时,detail的mid列作为与master表id列的外键关系,建立外键fk_detail_master_mid。

 

两个数据表对象的基础信息对应为:

 

 

SQL> select object_name,object_id from dba_objects where object_name in ('MASTER','DETAIL','IDX_DETAIL_MID');

 

OBJECT_NAME           OBJECT_ID

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

DETAIL                    75191

MASTER                    75189

 

 

基础数据准备:

 

 

SQL> select * from master;

        ID VNAME

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

         1 df

         2 dfdf

         3

         4(后加入该条记录)

 

SQL> select * from detail;

       DID        MID DETAILS

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

         1          1

         2          1 dkff

         3          2 fdkff

 

 

会话准备,作为并发操作实验,我们准备两个会话窗口。sid1和sid2,其中sid1为主操作会话窗口。sid1会话编号为135,sid2会话编号为18。另外,使用监控语句监控并发锁状态。

 

 

select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

 

2、外键关联下,DML操作锁的特殊性

 

首先我们探讨一下Oracle条件下外键关联影响DML操作锁的情况。在一般情况下,Oracle使用的是行锁机制,保证进行DML操作的数据行不会被其他会话影响,也不会影响到其他数据行的操作,更不会影响到其他数据表的问题。

 

在没有外键的情况下,我们观察一下Oracle锁情况(额外实验,非Oracle11g环境)。

 

 

SQL> select sid from v$mystat where rownum<2;

       SID

----------

       144

 

SQL> insert into t select * from dba_objects;

50367 rows inserted

 

SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=144;

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

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

       144 TM        54206          0          3          0          0

       144 TX       327716       1068          6          0          0

 

 

从这个案例中,可以看到Oracle进行DML操作的基本规范。在进行DML操作的时候,Oracle首先会尝试在数据表添加一个TM锁,级别是共享锁(lmode=3)。这个TM锁的id1参数为添加对象的object_id。这个锁的作用是保证在进行DML事务的时候,数据表不会被修改或者独占,体现在被添加3级锁以上级别的锁级别。这样,如果有其他会话要进行DML操作,在加入lmode=3锁的时候,是不会发生阻塞的。这样也就保证了对同一个数据表,是可以同时并发DML操作的。

 

其次,Oracle会独占使用数据行记录。就体现在添加的TX锁上,注意是lmode=6的级别锁。TX锁的参数信息,表示的是对应的事务段信息。

 

结论:在没有外键的作用下,Oracle进行的是最小粒度的锁控制。不会影响到其他数据表。

 

如果添加了外键,我们对数据表的DML操作后,将会有何种影响呢?

 

 

SQL> insert into master values (4,'dk');

1 row inserted

 

此时,监控结构。

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

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

       135 TM        75189          0          3          0          0

       135 TM        75191          0          3          0          0

       135 TX       655390       1000          6          0          0

 

 

我们对主表进行操作,除了对主表的共享锁之外,还额外对子表(object_id=75191)进行加共享锁机制

 

在主表操作上, delete主表操作不会带来对子表的连带锁操作。

 

 

SQL> delete master where id=3;

 

1 row deleted

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

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

       135 TM        75189          0          3          0          0

       135 TX        65564        963          6          0          0

 

 

update操作,也是不会影响到子表记录。

 

 

SQL> update master set vname='df' where id=3;

 

1 row updated

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

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

       135 TM        75189          0          3          0          0

       135 TX       262156        999          6          0          0

 

 

那么,我们对子表进行DML操作的时候,会连带影响到父表锁吗?下面进行DML操作实验。

 

 

SQL> insert into detail values (4,2,'d');

1 row inserted

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

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

       135 TM        75189          0          3          0          0

       135 TM        75191          0          3          0          0

       135 TX       262170       1001          6          0          0

 

SQL> update detail set details='dkl' where did=3;

1 row updated

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

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

       135 TM        75191          0          3          0          0

       135 TX       393236       1219          6          0          0

 

 

SQL> delete detail where did=3;

1 row deleted

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

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

       135 TM        75189          0          3          0          0

       135 TM        75191          0          3          0          0

       135 TX       327713       1218          6          0          0

 

 

子表操作的效果上,对子表进行insert和delete操作时候,也是会影响到父表锁结构的。

 

注意:我们上面的实验,是在子表无索引的情况下进行的。如果我们建立了索引结构,上面实验有什么不同的?

 

 

SQL> create index idx_detail_mid on detail(mid);

Index created

 

 

进行上述相同实验,由于篇幅原因,不加以累述,只是针对与无索引时候的差异进行阐述。

 

在对主表进行操作的时候,delete操作会引起连带的锁结构。

 

 

SQL> delete master where id=3;

1 row deleted

 

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

 

       SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

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

       135 TM        75189          0          3          0          0

       135 TM        75191          0          3          0          0

       135 TX       524319       1418          6          0          0

 

 

对子表进行操作的时候,有无索引引起的锁结构相同。下表表示时候有连带锁效应。

 

 

 

外键列无索引

外键列有索引

主表DML操作

INSERT

Y

Y

UPDATE

N

N

DELETE

N

Y

子表DML操作

INSERT

Y

Y

UPDATE

N

N

DELETE

Y

Y

 

下面我们从并发会话的角度,看无索引状态下会带来的问题。

 

PS:系列总索引:

 

《为什么子表外键列需要建立索引?(上)》

http://space.itpub.net/17203031/viewspace-701832

 

《为什么子表外键列需要建立索引?(中)》

http://space.itpub.net/17203031/viewspace-701833

 

为什么子表外键列需要建立索引?(下)

http://space.itpub.net/17203031/viewspace-701834

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-701832/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7619050