驅(qū)動(dòng)表與被驅(qū)動(dòng)表的含義

在MySQL中進(jìn)行多表聯(lián)合查詢(xún)時(shí)，MySQL會(huì)通過(guò)驅(qū)動(dòng)表的結(jié)果集作為基礎(chǔ)數(shù)據(jù)，在被驅(qū)動(dòng)表中匹配對(duì)應(yīng)的數(shù)據(jù)，匹配成功合并后的臨時(shí)表再作為驅(qū)動(dòng)表或被驅(qū)動(dòng)表繼續(xù)與第三張表進(jìn)行匹配合并，直到所有表都已匹配完畢，最后將結(jié)果返回出來(lái)。匹配算法：Nested-Loop Join（嵌套循環(huán)連接），在MySQL中有三種具體的實(shí)現(xiàn)算法：

• Simple Nested-Loop Join：簡(jiǎn)單嵌套循環(huán)連接
• Index Nested-Loop Join：索引嵌套循環(huán)鏈接
• Block Nested-Loop Join：緩存快嵌套循環(huán)鏈接

Simple Nested-Loop Join

簡(jiǎn)單嵌套循環(huán)連接實(shí)際上就是簡(jiǎn)單粗暴的嵌套循環(huán)，如果驅(qū)動(dòng)表有100條數(shù)據(jù)，被驅(qū)動(dòng)表有100條數(shù)據(jù)，那么在匹配時(shí)會(huì)將驅(qū)動(dòng)表的每一條數(shù)據(jù)作為匹配條件去被驅(qū)動(dòng)表中逐個(gè)比較，實(shí)際上就要比較100*100=10000次，可以想象這種比較效率是非常低下的。

Index Nested-Loop Join

索引嵌套循環(huán)連接是基于被驅(qū)動(dòng)表的索引進(jìn)行連接的算法，通過(guò)驅(qū)動(dòng)表的匹配條件與被驅(qū)動(dòng)表的索引進(jìn)行匹配，避免和每條記錄比較，從而利用索引的查詢(xún)減少匹配次數(shù)，提高查詢(xún)的性能。但要注意的是被驅(qū)動(dòng)表的關(guān)聯(lián)條件必須要有索引時(shí)才能用到Index Nested-Loop Join。另外由于用到索引，如果是非聚簇索引并且查詢(xún)的數(shù)據(jù)包含了被驅(qū)動(dòng)表的其他字段，則會(huì)回到被驅(qū)動(dòng)表再查詢(xún)一次對(duì)應(yīng)的數(shù)據(jù)，即回表，多了IO操作。

Block Nested-Loop Join

緩存嵌套循環(huán)連接通過(guò)一次性緩存多條驅(qū)動(dòng)表數(shù)據(jù)、參與查詢(xún)的列到Join Buffer里，然后拿Join Buffer里的數(shù)據(jù)批量與被驅(qū)動(dòng)表中的數(shù)據(jù)進(jìn)行比較，從而減少了循環(huán)匹配次數(shù)。

關(guān)于Join Buffer

Join Buffer會(huì)緩存所有參與查詢(xún)的列，而不是只有Join的匹配列

可以調(diào)整MySQL的join_buffer_size緩存大小，join_buffer_size的默認(rèn)值是256K,最大值在MySQL 5.1.22版本前是4G，而之后的版本才能在64位操作系統(tǒng)下申請(qǐng)大于4G的空間

要使用Block Nested-Loop Join算法需要開(kāi)啟優(yōu)化器管理配置的optimizer_switch的設(shè)置block_nested_loop為on，默認(rèn)為on

當(dāng)查詢(xún)優(yōu)化器不使用Index Nested-Loop Join算法的時(shí)候，默認(rèn)使用Block Nested-Loop Join算法。

聯(lián)合查詢(xún)的性能優(yōu)化原則

明白聯(lián)合查詢(xún)的原理是驅(qū)動(dòng)表與被驅(qū)動(dòng)表通過(guò)條件嵌套循環(huán)連接匹配后，查詢(xún)性能優(yōu)化的思路就是：減少循環(huán)比較次數(shù)?？梢酝ㄟ^(guò)以下幾個(gè)原則來(lái)進(jìn)行優(yōu)化。

1. 以數(shù)據(jù)量小的表作為驅(qū)動(dòng)表，數(shù)據(jù)量大的表作為被驅(qū)動(dòng)表。

通過(guò)上面的分析可以得知，MySQL在聯(lián)合查詢(xún)中是用驅(qū)動(dòng)表的數(shù)據(jù)作為篩選條件在被驅(qū)動(dòng)表中進(jìn)行匹配，所以假設(shè)table1作為驅(qū)動(dòng)表，數(shù)據(jù)有10000條，table2作為被驅(qū)動(dòng)表的數(shù)據(jù)有100條，并且被table2中有索引，那么用Index Nested-Loop Join算法進(jìn)行匹配時(shí)要進(jìn)行10000次的關(guān)聯(lián)操作。但如果反過(guò)來(lái)用table2作為驅(qū)動(dòng)表，table1作為被驅(qū)動(dòng)表，只需要進(jìn)行100次關(guān)聯(lián)即可完成匹配，效率也會(huì)大大提高，其他的連接算法也類(lèi)似。簡(jiǎn)單說(shuō)通常情況下要用小表驅(qū)動(dòng)大表。
但是這里的小表和大表是根據(jù)查詢(xún)條件相對(duì)而言的，大小的計(jì)算是要根據(jù)查詢(xún)條件和具體的字段進(jìn)行衡量，假如查詢(xún)條件指定了table1的搜索范圍，即table1滿(mǎn)足查詢(xún)條件的行數(shù)有90行，那么計(jì)算公式為：90乘以參與關(guān)聯(lián)查詢(xún)字段的大小總和，若結(jié)果小于table2滿(mǎn)足查詢(xún)條件后的行數(shù)乘以參與關(guān)聯(lián)查詢(xún)字段的大小，則table1為小表，否則table1為大表。

2. 為匹配的條件增加索引

匹配的條件字段列盡量使用有索引的，爭(zhēng)取使用Index Nested-Loop Join算法進(jìn)行關(guān)聯(lián)，減少被驅(qū)動(dòng)表的循環(huán)次數(shù)

3. 增大join_buffer_size的大小

當(dāng)使用Block Nested-Loop Join算法時(shí)，增大join_buffer_size的大小可以使驅(qū)動(dòng)表一次緩存更多的數(shù)據(jù)，從而減少總體循環(huán)匹配的次數(shù)

4. 減少不必要的字段查詢(xún)

• 當(dāng)用到Block Nested-Loop Join算法時(shí)，字段越少，join Buffer所緩存的數(shù)據(jù)就越多，那么循環(huán)的次數(shù)就越少。
• 當(dāng)用到Index Nested-Loop Join算法時(shí)，如果可以不回表查詢(xún)，即只查詢(xún)索引列，利用覆蓋索引則可能提升匹配效率

如何確定驅(qū)動(dòng)表與被驅(qū)動(dòng)表

• 在使用join連接并且無(wú)where條件時(shí)：

  left join左邊的表為驅(qū)動(dòng)表，右邊的為被驅(qū)動(dòng)表

  right join右邊的表為驅(qū)動(dòng)表，左邊的為被驅(qū)動(dòng)表

  使用join時(shí)，MySQL會(huì)自動(dòng)判斷左右兩邊哪邊是小表，哪邊是大表。小表作為驅(qū)動(dòng)表，大表作為被驅(qū)動(dòng)表，小表與大表的判斷原則為上面講到的根據(jù)行數(shù)和參與關(guān)聯(lián)的字段計(jì)算得出。

• 在使用in\exists時(shí)

  使用in時(shí)，驅(qū)動(dòng)表和被驅(qū)動(dòng)表由MySQL的執(zhí)行器根據(jù)表的大小自動(dòng)選擇

  使用exists時(shí)，外部表為驅(qū)動(dòng)表，內(nèi)部表為被驅(qū)動(dòng)表。無(wú)論加什么查詢(xún)條件都無(wú)法改變

使用join連接查詢(xún)時(shí)如果有where條件，則MySQL執(zhí)行器會(huì)根據(jù)查詢(xún)條件過(guò)濾后的結(jié)果自動(dòng)選擇驅(qū)動(dòng)表或被驅(qū)動(dòng)表。

到此這篇關(guān)于MySQL的驅(qū)動(dòng)表與被驅(qū)動(dòng)表的文章就介紹到這了,更多相關(guān)MySQL驅(qū)動(dòng)表與被驅(qū)動(dòng)表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論