如何優(yōu)化MySQL中的join語句

在mysql中，join 主要有Nested Loop、Hash Join、Merge Join 這三種方式，我們今天來看一下最普遍 Nested Loop 循環(huán)連接方式，主要包括三種：

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

Simple Nested-Loop Join

我們來看一下當(dāng)進(jìn)行 join 操作時，mysql是如何工作的。常見的 join 方式有哪些？如圖，當(dāng)我們進(jìn)行連接操作時，左邊的表是 「驅(qū)動表」 ，右邊的表是**「被驅(qū)動表」**

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

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

Block Nested-Loop Join

既然每次從驅(qū)動表取數(shù)據(jù)比較耗時，那我們每次從驅(qū)動表取一批數(shù)據(jù)放到內(nèi)存中，然后對這一批數(shù)據(jù)進(jìn)行匹配操作。這批數(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條隨機(jī)記錄

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)

運(yùn)行時間為0.035s執(zhí)行計劃如下在Extra列中看到了 Using join buffer ，說明連接操作是基于 「Block Nested-Loop Join」 算法

Index Nested-Loop Join

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

估計這種算法你也想到了，就是給被驅(qū)動表連接的列加上索引，這樣匹配的過程就非?？?，如圖所示我們來看一下基于索引列進(jìn)行連接執(zhí)行查詢有多快？

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

執(zhí)行時間為0.001秒，可以看到比基于普通的列進(jìn)行連接快了不止一個檔次執(zhí)行計劃如下「驅(qū)動表的記錄并不是所有列都會被放到 join buffer，只有查詢列表中的列和過濾條件中的列才會被放入 join buffer，因此我們不要把 * 作為查詢列表，只需要把我們關(guān)心的列放到查詢列表就好了，這樣可以在 join buffer 中放置更多的記錄」

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

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

「如果是 Block Nested-Loop Join 算法：」

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

「如果是 Index Nested-Loop Join 算法」

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

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

驅(qū)動表的每一行數(shù)據(jù)都要到被驅(qū)動表上搜索一次，整個執(zhí)行過程近似復(fù)雜度為

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

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

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

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