jpa 每次启动都要创建外键索引_为什么子表外键列需要建立索引?（下）

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 TYPEID1ID2LMODEREQUESTBLOCK

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

135 TM751890300

135 TM751910300

18 TM751890300

18 TM751910300

135 TX6553901000600

18 TX3276971217600

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 TYPEID1ID2LMODEREQUESTBLOCK

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

135 TM751890300

135 TM751910300

18 TM751890300

18 TM751910300

135 TX6553901000600

18 TX5898491172600

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 TYPEID1ID2LMODEREQUESTBLOCK

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

135 TM751890300

135 TM751910300

18 TM751890300

18 TM751910300

135 TX6553741001600

18 TX3932381219600

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 TYPEID1ID2LMODEREQUESTBLOCK

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

135 TM751910300

18 TM751890300

18 TM751910300

135 TX2621561000600

18 TX3277021213600

阻塞现象消失。

ü无索引问题场景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 TYPEID1ID2LMODEREQUESTBLOCK

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

135 TM751890300

135 TM751910300

18 TM751890300

18 TM751910300

135 TX3277001219600

18 TX3932341219600

6 rows selected

阻塞现象消失。

5、结论

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

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

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

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

PS：系列总索引：