【fk_index】外键中有无索引的区别
在外键上加索引与没有索引的区别: 主要有两方面问题:一、在DML操作时的阻塞问题,二 、DML操作时的速度问题 一、阻塞问题 外键在无索引的情况下,更新主表外键关联字段时,需要为子表加4级锁(S);在有索引的情况下,更新主表外键关联字段时,需要为子表加2
在外键上加索引与没有索引的区别:
主要有两方面问题:一、在DML操作时的阻塞问题,二 、DML操作时的速度问题
一、阻塞问题
外键在无索引的情况下,更新主表外键关联字段时,需要为子表加4级锁(S);在有索引的情况下,更新主表外键关联字段时,需要为子表加2级锁(RS)。在子表上本来就已有RX时,S锁无法被兼容,造成更新主表阻塞。如果子表上本来没有锁,更新主表的操作不被阻塞时(更新完后我们暂时不commit),此刻,如果外键没有索引,4级锁(S)是“瞬间”加上的,然后就释放不易观察到;而在有索引的情况下,给子表加的2级锁(RS)会一直存在,直到更新主表的会话回滚或提交。
无论在有无外键索引的情况下,子表插入数据,需要给主表加2级锁(RS),这个操作是否会被阻塞,要看主表中对应的记录是否存在RX锁。这里是用delete进行的测试,其它DML语句情况相同。
1. 创建两张表并插入数据,模拟实验环境
BALLONTT@PROD> create table dept(deptno number,dname varchar2(10));
BALLONTT@PROD> alter table dept add constraint pk_dept primary key(deptno);
BALLONTT@PROD> create table emp(empno number,ename varchar2(10),deptno number);
BALLONTT@PROD> alter table emp add constraint fk_emp foreign key(deptno) references dept(deptno);
BALLONTT@PROD> insert into dept values(01,'aa');
BALLONTT@PROD> insert into dept values(02,'bb');
BALLONTT@PROD> insert into dept values(03,'cc');
BALLONTT@PROD> insert into dept values(04,'dd');
BALLONTT@PROD> commit;
Commit complete.
BALLONTT@PROD> insert into emp(empno,deptno) values(111,01);
BALLONTT@PROD> insert into emp(empno,deptno) values(222,02);
BALLONTT@PROD> commit;
Commit complete.
2. 确认表的信息
BALLONTT@PROD> select * from dept;
DEPTNO DNAME
---------- ----------
1 aa
2 bb
3 cc
4 dd
BALLONTT@PROD> select empno,deptno from emp;
EMPNO DEPTNO
---------- ----------
111 1
222 2
BALLONTT@PROD> select a.object_id,a.object_name,l.session_id from
2 all_objects a,v$locked_object l
3 where a.object_id=l.object_id;
no rows selected
3. 在会话1(session_id=125)中执行下面DML操作(此时emp表中没有索引时)
BALLONTT@PROD> insert into emp(empno,deptno) values(333,3);
1 row created.
查看被锁的对象信息
BALLONTT@PROD> select a.object_id,a.object_name,l.session_id from
2 all_objects a,v$locked_object l
3 where a.object_id=l.object_id;
OBJECT_ID OBJECT_NAME SESSION_ID
----------- --------------------- ----------------
9752 EMP 125
9750 DEPT 125
BALLONTT@PROD> select sid,type,id1,id2,lmode,request from v$lock where sid=125;
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ------ ---- ----------
125 TM 9750 0 2 0
125 TM 9752 0 3 0
125 TX 65558 105 6 0
上面对emp的插入操作,对dept(id:9750)加2模式表级锁(即RS锁),对EMP(id:9752)加表级锁RX(LMODE 3),和行级锁X(LMODE 6)
4. 紧着着在会话2(session_id=113)中对主键所在表进行DML,查看是否阻塞
BALLONTT@PROD> update dept set deptno=10 where deptno=3;---阻塞
查看锁的信息
BALLONTT@PROD> select sid,type,id1,id2,lmode,request from v$lock where sid in(113,125);
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
125 TM 9750 0 2 0
125 TM 9752 0 3 0
113 TM 9750 0 3 0
113 TM 9752 0 0 4
125 TX 65558 105 6 0
(9750代表dept,9752代表emp)对dept的更新需要在表dept上加表级锁RX,同时向EMP表申请S锁(REQUEST 4)。但由于此时EMP上有插入操作带来的RX锁,与S锁不兼容,所以因无法得到S锁而导致对DEPT的更新操作阻塞。
update dept set deptno=16 where deptno=4; --同阻塞,原因如上。
5. 终止会话2,回滚会话1,在EMP表的外键上加索引
BALLONTT@PROD> create index ind_emp on emp(deptno);
6.重复上面的3步骤,并在会话2中在执行下面语句(有外键索引,下面语句需要先对子表加RS锁,然后再去申请主表RX锁,是否会被阻塞取决于子表需要被加RS锁的记录上是否已有RX锁,主表需要被加RX锁的记录是否有RS锁)
BALLONTT@PROD> update dept set deptno=10 where deptno=4;--不阻塞
查看锁的的信息:
BALLONTT@PROD> select sid,type,id1,id2,lmode,request from v$lock where sid in(113,125);
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
125 TM 9750 0 2 0
125 TM 9752 0 3 0
113 TM 9750 0 3 0
113 TM 9752 0 2 0
113 TX 327726 105 6 0
125 TX 262156 107 6 0
6 rows selected.
会话1(sid:125)中对emp的插入操作形成了3个锁。(上文已说明)
会话2(sid:113)中对dept的更新操作也有三个锁,分别是在dept表上的常规更新带来的两个锁RX,和TX。第三锁为加在子表EMP上的RS锁。RS锁与EMP上已有的RX锁兼容,所以不会阻塞。
update dept set deptno=16 where deptno=3; --阻塞
二、DML操作时的速度问题·
当使用ON DELETE CASCADE删除父表中的记录时,如果在子表中的外键没有使用索引则当执行该操作时会对子表进行全表的扫描,而事实上这个全表的扫描是不需要的。更坏的情况是,如果删除多个父表中的记录,每删除一条记录则会进行一次全表扫描,可想而知,对于性能的影响是多么的大!
对于父表和子表的连接查询,情况也是类似的。当进行这种连接查询时,如果不对外键使用索引则会发现查询的速度大大降低。
由此可知,我们应该在外键上建立索引。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



The difference between multithreading and asynchronous is that multithreading executes multiple threads at the same time, while asynchronously performs operations without blocking the current thread. Multithreading is used for compute-intensive tasks, while asynchronously is used for user interaction. The advantage of multi-threading is to improve computing performance, while the advantage of asynchronous is to not block UI threads. Choosing multithreading or asynchronous depends on the nature of the task: Computation-intensive tasks use multithreading, tasks that interact with external resources and need to keep UI responsiveness use asynchronous.

In C language, the main difference between char and wchar_t is character encoding: char uses ASCII or extends ASCII, wchar_t uses Unicode; char takes up 1-2 bytes, wchar_t takes up 2-4 bytes; char is suitable for English text, wchar_t is suitable for multilingual text; char is widely supported, wchar_t depends on whether the compiler and operating system support Unicode; char is limited in character range, wchar_t has a larger character range, and special functions are used for arithmetic operations.

There is no built-in sum function in C language, so it needs to be written by yourself. Sum can be achieved by traversing the array and accumulating elements: Loop version: Sum is calculated using for loop and array length. Pointer version: Use pointers to point to array elements, and efficient summing is achieved through self-increment pointers. Dynamically allocate array version: Dynamically allocate arrays and manage memory yourself, ensuring that allocated memory is freed to prevent memory leaks.

An application that converts XML directly to PDF cannot be found because they are two fundamentally different formats. XML is used to store data, while PDF is used to display documents. To complete the transformation, you can use programming languages and libraries such as Python and ReportLab to parse XML data and generate PDF documents.

C language functions are the basis for code modularization and program building. They consist of declarations (function headers) and definitions (function bodies). C language uses values to pass parameters by default, but external variables can also be modified using address pass. Functions can have or have no return value, and the return value type must be consistent with the declaration. Function naming should be clear and easy to understand, using camel or underscore nomenclature. Follow the single responsibility principle and keep the function simplicity to improve maintainability and readability.

Two ways to define structures in Go language: the difference between var and type keywords. When defining structures, Go language often sees two different ways of writing: First...

Detailed explanation of XPath search method under DOM nodes In JavaScript, we often need to find specific nodes from the DOM tree based on XPath expressions. If you need to...

Although C and C# have similarities, they are completely different: C is a process-oriented, manual memory management, and platform-dependent language used for system programming; C# is an object-oriented, garbage collection, and platform-independent language used for desktop, web application and game development.
