Simple Nested-Loop Join

我們來看一下當進行 join 操作時，mysql是如何工作的。常見的 join 方式有哪些？

如圖，當我們進行連接操作時，左邊的表是驅(qū)動表，右邊的表是被驅(qū)動表

Simple Nested-Loop Join 這種連接操作是從驅(qū)動表中取出一條記錄然后逐條匹配被驅(qū)動表的記錄，如果條件匹配則將結果返回。然后接著取驅(qū)動表的下一條記錄進行匹配，直到驅(qū)動表的數(shù)據(jù)全都匹配完畢

因為每次從驅(qū)動表取數(shù)據(jù)比較耗時，所以MySQL并沒有采用這種算法來進行連接操作

Block Nested-Loop Join

既然每次從驅(qū)動表取數(shù)據(jù)比較耗時，那我們每次從驅(qū)動表取一批數(shù)據(jù)放到內(nèi)存中，然后對這一批數(shù)據(jù)進行匹配操作。這批數(shù)據(jù)匹配完畢，再從驅(qū)動表中取一批數(shù)據(jù)放到內(nèi)存中，直到驅(qū)動表的數(shù)據(jù)全都匹配完畢

批量取數(shù)據(jù)能減少很多IO操作，因此執(zhí)行效率比較高，這種連接操作也被MySQL采用

對了，這塊內(nèi)存在MySQ中有一個專有的名詞，叫做 join buffer，我們可以執(zhí)行如下語句查看 join buffer 的大小

show variables like '%join_buffer%'

把我們之前用的 single_table 表搬出來，基于 single_table 表創(chuàng)建2個表，每個表插入1w條隨機記錄

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

create table t1 like single_table;
create table t2 like single_table;

如果直接使用 join 語句，MySQL優(yōu)化器可能會選擇表 t1 或者 t2 作為驅(qū)動表，這樣會影響我們分析sql語句的過程，所以我們用 straight_join 讓mysql使用固定的連接方式執(zhí)行查詢

select * from t1 straight_join t2 on (t1.common_field = t2.common_field)

運行時間為0.035s

執(zhí)行計劃如下

在Extra列中看到了 Using join buffer ，說明連接操作是基于 Block Nested-Loop Join 算法

Index Nested-Loop Join

了解了 Block Nested-Loop Join 算法之后，可以看到驅(qū)動表的每條記錄會把被驅(qū)動表的所有記錄都匹配一遍，非常耗時，能不能提高一下被驅(qū)動表匹配的效率呢？

估計這種算法你也想到了，就是給被驅(qū)動表連接的列加上索引，這樣匹配的過程就非?？欤鐖D所示

我們來看一下基于索引列進行連接執(zhí)行查詢有多快？

select * from t1 straight_join t2 on (t1.id = t2.id)

執(zhí)行時間為0.001秒，可以看到比基于普通的列進行連接快了不止一個檔次

執(zhí)行計劃如下

驅(qū)動表的記錄并不是所有列都會被放到 join buffer，只有查詢列表中的列和過濾條件中的列才會被放入 join buffer，因此我們不要把 * 作為查詢列表，只需要把我們關心的列放到查詢列表就好了，這樣可以在 join buffer 中放置更多的記錄

如何選擇驅(qū)動表？

知道了 join 的具體實現(xiàn)，我們來聊一個常見的問題，即如何選擇驅(qū)動表？

如果是 Block Nested-Loop Join 算法：

• 當 join buffer 足夠大時，誰做驅(qū)動表沒有影響
• 當 join buffer 不夠大時，應該選擇小表做驅(qū)動表（小表數(shù)據(jù)量少，放入 join buffer 的次數(shù)少，減少表的掃描次數(shù)）

如果是 Index Nested-Loop Join 算法

假設驅(qū)動表的行數(shù)是M，因此需要掃描驅(qū)動表M行

被驅(qū)動表的行數(shù)是N，每次在被驅(qū)動表查一行數(shù)據(jù)，要先搜索索引a，再搜索主鍵索引。每次搜索一顆樹近似復雜度是以2為底N的對數(shù)，所以在被驅(qū)動表上查一行的時間復雜度是 2 ∗ l o g 2 N 2*log2^N 2∗log2N

驅(qū)動表的每一行數(shù)據(jù)都要到被驅(qū)動表上搜索一次，整個執(zhí)行過程近似復雜度為 M + M ∗ 2 ∗ l o g 2 N M + M*2*log2^N M+M∗2∗log2N

顯然M對掃描行數(shù)影響更大，因此應該讓小表做驅(qū)動表。當然這個結論的前提是可以使用被驅(qū)動表的索引

總而言之，我們讓小表做驅(qū)動表即可

當 join 語句執(zhí)行的比較慢時，我們可以通過如下方法來進行優(yōu)化

• 進行連接操作時，能使用被驅(qū)動表的索引
• 小表做驅(qū)動表
• 增大 join buffer 的大小
• 不要用 * 作為查詢列表，只返回需要的列

到此這篇關于MySQL中join語句怎么優(yōu)化的文章就介紹到這了,更多相關MySQL join語句優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！