Home Database Mysql Tutorial 【fk_index】外键中有无索引的区别

【fk_index】外键中有无索引的区别

Jun 07, 2016 pm 03:54 PM
index the difference foreign key 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删除父表中的记录时,如果在子表中的外键没有使用索引则当执行该操作时会对子表进行全表的扫描,而事实上这个全表的扫描是不需要的。更坏的情况是,如果删除多个父表中的记录,每删除一条记录则会进行一次全表扫描,可想而知,对于性能的影响是多么的大!

对于父表和子表的连接查询,情况也是类似的。当进行这种连接查询时,如果不对外键使用索引则会发现查询的速度大大降低。

由此可知,我们应该在外键上建立索引。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

deepseek What is the difference between r1 and v3 version deepseek What is the difference between r1 and v3 version Feb 19, 2025 pm 03:24 PM

DeepSeek: In-depth comparison between R1 and V3 versions helps you choose the best AI assistant! DeepSeek already has tens of millions of users, and its AI dialogue function has been well received. But are you confused when facing the R1 and V3 versions? This article will explain the differences between the two in detail to help you choose the most suitable version. The core difference between DeepSeekR1 and V3 version: Features The design goal of the V3 version focuses on complex problem reasoning, deep logic analysis, multi-functional large language model, focusing on scalability and efficiency architecture and parameter reinforcement learning optimization architecture, parameter scale 1.5 billion to 70 billion MoE hybrid Expert architecture, total parameters are as high as 671 billion, each token is activated by 37 billion

Summary of FAQs for DeepSeek usage Summary of FAQs for DeepSeek usage Feb 19, 2025 pm 03:45 PM

DeepSeekAI Tool User Guide and FAQ DeepSeek is a powerful AI intelligent tool. This article will answer some common usage questions to help you get started quickly. FAQ: The difference between different access methods: There is no difference in function between web version, App version and API calls, and App is just a wrapper for web version. The local deployment uses a distillation model, which is slightly inferior to the full version of DeepSeek-R1, but the 32-bit model theoretically has 90% full version capability. What is a tavern? SillyTavern is a front-end interface that requires calling the AI ​​model through API or Ollama. What is breaking limit

Does Bitcoin have stocks? Does Bitcoin have equity? Does Bitcoin have stocks? Does Bitcoin have equity? Mar 03, 2025 pm 06:42 PM

The cryptocurrency market is booming, and Bitcoin, as a leader, has attracted the attention of many investors. Many people are curious: Do Bitcoin have stocks? The answer is no. Bitcoin itself is not a stock, but investors can indirectly invest in Bitcoin-related assets through various channels, which will be explained in detail in this article. Alternatives to Bitcoin Investment: Instead of investing directly in Bitcoin, investors can participate in the Bitcoin market by: Bitcoin ETF: This is a fund traded on the stock trading market, whose asset portfolio contains Bitcoin or Bitcoin futures contracts. This is a relatively convenient option for investors who are accustomed to stock investments, without having to hold Bitcoin directly. Bitcoin Mining Company Stocks: These companies' business is Bitcoin mining and holding Bitcoin

What is the difference between pre-market and after-market trading? Detailed explanation of the differences between pre-market and after-market trading What is the difference between pre-market and after-market trading? Detailed explanation of the differences between pre-market and after-market trading Mar 03, 2025 pm 11:54 PM

In traditional financial markets, pre-market and after-market trading refers to trading activities outside the regular trading period. Although the cryptocurrency market is trading around the clock, trading platforms like Bitget also offer similar features, especially some comprehensive platforms that trade stocks and cryptocurrencies at the same time. This article will clarify the differences in pre-market and after-market trading and explore its impact on currency price. Four major differences between pre-market and after-market trading: The main differences between pre-market and after-market trading and regular trading periods are in four aspects: trading time, liquidity, price fluctuations and trading volume: Trading time: Pre-market trading occurs before the official trading starts, and after-market trading is carried out after the regular trading ends. Liquidity: The liquidity of pre- and after-hours trading is low, there are few traders, and the bid and offer price difference is large; while the liquidity is high during the regular trading period, the price is

Why is Bittensor said to be the 'bitcoin' in the AI ​​track? Why is Bittensor said to be the 'bitcoin' in the AI ​​track? Mar 04, 2025 pm 04:06 PM

Original title: Bittensor=AIBitcoin? Original author: S4mmyEth, Decentralized AI Research Original translation: zhouzhou, BlockBeats Editor's note: This article discusses Bittensor, a decentralized AI platform, hoping to break the monopoly of centralized AI companies through blockchain technology and promote an open and collaborative AI ecosystem. Bittensor adopts a subnet model that allows the emergence of different AI solutions and inspires innovation through TAO tokens. Although the AI ​​market is mature, Bittensor faces competitive risks and may be subject to other open source

Is there any difference between South Korean Bitcoin and domestic Bitcoin? Is there any difference between South Korean Bitcoin and domestic Bitcoin? Mar 05, 2025 pm 06:51 PM

The Bitcoin investment boom continues to heat up. As the world's first decentralized digital asset, Bitcoin has attracted much attention on its decentralization and global liquidity. Although China was once the largest market for Bitcoin, policy impacts have led to transaction restrictions. Today, South Korea has become one of the major Bitcoin markets in the world, causing investors to question the differences between it and its domestic Bitcoin. This article will conduct in-depth analysis of the differences between the Bitcoin markets of the two countries. Analysis of the differences between South Korea and China Bitcoin markets. The main differences between South Korea and China’s Bitcoin markets are reflected in prices, market supply and demand, exchange rates, regulatory supervision, market liquidity and trading platforms. Price difference: South Korea’s Bitcoin price is usually higher than China, and this phenomenon is called “Kimchi Premium.” For example, in late October 2024, the price of Bitcoin in South Korea was once

Vertical proxy: Application scenarios and interpretation of disruptive potential of encryption native proxy Vertical proxy: Application scenarios and interpretation of disruptive potential of encryption native proxy Mar 04, 2025 am 10:21 AM

Artificial intelligence agents (AIAgents) are rapidly integrating into daily operations of enterprises, from large companies to small businesses, almost all areas have begun to be used, including sales, marketing, finance, law, IT, project management, logistics, customer service and workflow automation. We are moving from an era of manual processing of data, performing repetitive tasks, and using Excel tables to an era of autonomous operation by AI agents around the clock, which not only improves efficiency but also significantly reduces costs. Application case of AI agents in Web2: YCombinator's Perspective Apten: A sales and marketing optimization tool combining AI and SMS technology. BildAI: A model that can read architectural blueprints,

Pepe bought and sold out in a big way, is MUTM a smarter investment in 2025? Pepe bought and sold out in a big way, is MUTM a smarter investment in 2025? Mar 03, 2025 pm 07:09 PM

After the surge in PEPE, can MUTM become a more stable investment choice in 2025? PEPE (PEPE) has made early investors profitable, but its violent price fluctuations have also made many people question its long-term prospects. As the meme currency market continues to turbulently, traders are beginning to focus on projects with more fundamental advantages, and MutuumFinance (MUTM) is one of them. This is a decentralized lending platform focusing on practical financial applications. Unlike PEPE, which relies on speculative speculation, MUTM builds a structured DeFi ecosystem where users can borrow and earn passive income. Its pre-sale has exceeded one million US dollars, the first phase of token sales rate exceeds 97%, early investment

See all articles