ITPub博客

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

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

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

继续前篇的讨论(http://space.itpub.net/17203031/viewspace-701833)。 

 

 

4、有索引情况下,DML操作实验

 

当我们加入索引结构之后,进行相同的实验。下面列出一个正常场景和无索引是出现问题的四个场景实验结果。全部实验报告请参见下载文件区内容。

 

ü        正常实验场景

 

sid1进行主表插入操作,sid2进行主表插入操作。

 

--sid1

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

1 row inserted

 

--sid2

SQL> insert into master values (6,null);

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

        18 TM        75189          0          3          0          0

        18 TM        75191          0          3          0          0

       135 TX       655390       1000          6          0          0

        18 TX       327697       1217          6          0          0

 

6 rows selected

 

 

正常加锁,共享锁实现共享结构。

 

ü        无索引问题场景1

 

sid1进行主表insert操作,同时sid2进行主表的delete操作。

 

--sid1

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

1 row inserted

 

--sid2

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

        18 TM        75189          0          3          0          0

        18 TM        75191          0          3          0          0

       135 TX       655390       1000          6          0          0

        18 TX       589849       1172          6          0          0

 

6 rows selected

 

 

阻塞现象消失。

 

ü        无索引问题场景2

 

sid1对子表进行insert操作,sid2会话对主表进行delete操作。

 

--sid1

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

1 row inserted

 

--sid2

SQL> delete master where id=4;

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

        18 TM        75189          0          3          0          0

        18 TM        75191          0          3          0          0

       135 TX       655374       1001          6          0          0

        18 TX       393238       1219          6          0          0

 

6 rows selected

 

 

无阻塞现象。

 

ü        无索引问题场景3

 

sid1进行子表update操作的时候,sid2尝试对主表进行删除操作。

 

-sid1

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

1 row updated

 

--sid2

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        75191          0          3          0          0

        18 TM        75189          0          3          0          0

        18 TM        75191          0          3          0          0

       135 TX       262156       1000          6          0          0

        18 TX       327702       1213          6          0          0

 

 

阻塞现象消失。

 

ü        无索引问题场景4

 

sid1进行子表删除操作,sid2进行主表delete操作。

 

--sid1

SQL> delete detail where did=3;

1 row deleted

 

--sid2

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

        18 TM        75189          0          3          0          0

        18 TM        75191          0          3          0          0

       135 TX       327700       1219          6          0          0

        18 TX       393234       1219          6          0          0

 

6 rows selected

 

 

阻塞现象消失。

 

 

5、结论

 

经过上面的实验,我们证明了子表外键列索引的重要性。结论如下:

 

ü        当建立主外键关联关系之后,对两者之一进行DML操作,非常容易形成连带锁结构,形成对两个表的锁定。所以,在DML操作很多的系统中,主子表形成锁是非常频繁的;

ü        在子表外键列上没有建立索引的时候,在对主表进行DML操作时,容易并发引起锁请求升级的情况,进而造成阻塞;

ü        如果确定建立主外键关联,一定要连带建立索引结构。除了避免阻塞之外,还有进行关联查询提高效率好处。所以,要不就不建立外键,建立外键一定要建立索引;

 

 

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-701834/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7690301