Simple Nested-Loop Join

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

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

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

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

Block Nested-Loop Join

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

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

對了，這塊內存在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 作為驅動表，這樣會影響我們分析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 算法之后，可以看到驅動表的每條記錄會把被驅動表的所有記錄都匹配一遍，非常耗時，能不能提高一下被驅動表匹配的效率呢？

估計這種算法你也想到了，就是給被驅動表連接的列加上索引，這樣匹配的過程就非常快，如圖所示

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

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

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

執(zhí)行計劃如下

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

如何選擇驅動表？

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

如果是 Block Nested-Loop Join 算法：

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

如果是 Index Nested-Loop Join 算法

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

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

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

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

總而言之，我們讓小表做驅動表即可

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

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

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

最新評論