淺談mysql join底層原理

join算法

mysql只支持一種join算法：Nested-Loop Join（嵌套循環(huán)連接），但Nested-Loop Join有三種變種：

• Simple Nested-Loop Join，簡單嵌套
• Index Nested-Loop Join，索引嵌套
• Block Nested-Loop Join ，join buffer緩沖區(qū)嵌套（臨時(shí)表）

驅(qū)動(dòng)表和非驅(qū)動(dòng)表的區(qū)別

驅(qū)動(dòng)表就是主表，非驅(qū)動(dòng)表就是從表，看以下sql就知道了，A就是驅(qū)動(dòng)表，B就是非驅(qū)動(dòng)表。

select * from A left join B

A join B 連表時(shí)，一定先查A表再查B表嗎？

答案是不一定，因?yàn)閙ysql內(nèi)部有一個(gè)優(yōu)化器，它會(huì)根據(jù)你查詢語句做一些優(yōu)化，先查哪張表也是由優(yōu)化器決定的，但可以肯定的是，先查詢的那張表就是驅(qū)動(dòng)表，反之就是非驅(qū)動(dòng)表；關(guān)于是那張表先查的問題，我們通過看執(zhí)行計(jì)劃來得出結(jié)果；在前面加上explain關(guān)鍵字即可;

explain select * from A join B;

1、Simple Nested-Loop Join，簡單嵌套-無索引的情況

A left join B： ，每次都是全表匹配，A表的每行數(shù)據(jù)都全表匹配一次B表，也就是說，假如我A表有10條數(shù)據(jù)，B表有1000條數(shù)據(jù)，那么查詢的時(shí)候掃描次數(shù)就是10*1000，也就說查詢時(shí)需要掃描10000遍才能得出數(shù)據(jù)；

2、Index Nested-Loop Join-有索引的情況

select * from A join B on A.id=B.id where A.id = 1：在查詢時(shí)，驅(qū)動(dòng)表A會(huì)根據(jù)關(guān)聯(lián)字段的索引進(jìn)行查找，當(dāng)在索引上找到符合的值，在回表進(jìn)行查詢，也就是說得匹配到索引后才會(huì)才會(huì)進(jìn)行回表查詢；
如果非驅(qū)動(dòng)表B的關(guān)聯(lián)關(guān)聯(lián)字段B.id是主鍵的話，性能會(huì)非常高，如果不是主鍵，會(huì)進(jìn)行多次回表查詢，先關(guān)聯(lián)索引，然后根據(jù)二級(jí)索引的主鍵id進(jìn)行回表查詢，性能上比主鍵要慢；

3、Block Nested-Loop Join ，join buffer緩沖區(qū)

如果有索引，會(huì)選取Index Nested-Loop Join進(jìn)行連表，如果join列沒有索引，就會(huì)采用Block Nested-Loop Join ，join buffer，在驅(qū)動(dòng)表和非驅(qū)動(dòng)表中間有個(gè)buffer的緩沖區(qū)，在查詢時(shí)先將驅(qū)動(dòng)表的數(shù)據(jù)緩存到buffer緩沖區(qū)內(nèi)，然后批量與非驅(qū)動(dòng)表進(jìn)行匹配，這是一種將多次比較合并為一次比較的優(yōu)化方案，注意：這里緩存的不只是關(guān)聯(lián)表的列，select 后面的列也會(huì)緩存起來；

緩沖區(qū)大小

默認(rèn)情況下buffer緩沖區(qū)join_biffer_size的容量為256k，如果說你的數(shù)據(jù)空間大于256k，就無法使用緩沖區(qū)了，轉(zhuǎn)為最簡單的循環(huán)嵌套Simple Nested-Loop Join，但是我們可以手動(dòng)調(diào)整緩沖區(qū)大小來裝入大容量的數(shù)據(jù)；查看join_biffer_size的sql：show variables like '%join_biffer_size%'

數(shù)據(jù)量大的表和數(shù)據(jù)量小的表如何選擇連接順序

最好由小表去連接大表，這樣會(huì)減少掃描次數(shù)；比如大表有1000條數(shù)據(jù)，小表只有10條數(shù)據(jù)，那么最好的連接方式為：小表 join 大表；為什么要這么做呢？

• 如果是大表 join 小表，假如我們的數(shù)據(jù)在大表的第999行，那么查詢數(shù)據(jù)的時(shí)候就至少得掃描999次才能查出來；
• 如果是小表 join 大表，假如我們的數(shù)據(jù)在小表的第9行，

細(xì)節(jié)

• 連表查詢的時(shí)候，最好不要超過三張表，因?yàn)樾枰猨oin的字段，數(shù)據(jù)類型必須一致
• 優(yōu)先使用內(nèi)連接來連表，外連接查詢數(shù)據(jù)消耗的性能比內(nèi)連接要高；
• 確保關(guān)聯(lián)查詢中on 后面的列或者 using()中的字段帶有索引，連表時(shí)可加快數(shù)據(jù)訪問
  

到此這篇關(guān)于淺談mysql join底層原理的文章就介紹到這了,更多相關(guān)mysql join底層原理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！