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

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

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

Simple Nested-Loop Join

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

Index Nested-Loop Join

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

Block Nested-Loop Join

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

關(guān)于Join Buffer

Join Buffer會緩存所有參與查詢的列，而不是只有Join的匹配列

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

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

當查詢優(yōu)化器不使用Index Nested-Loop Join算法的時候，默認使用Block Nested-Loop Join算法。

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

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

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

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

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

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

3. 增大join_buffer_size的大小

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

4. 減少不必要的字段查詢

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

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

• 在使用join連接并且無where條件時：

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

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

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

• 在使用in\exists時

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

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

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

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

最新評論