我們準(zhǔn)備如下兩個表，并插入數(shù)據(jù)。

#分類
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#圖書
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

左外連接

首先我們分析SQL如下，type為驅(qū)動表（內(nèi)表），book為被驅(qū)動表（外表）。

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

每次從type中獲取一條數(shù)據(jù)然后后book中的數(shù)據(jù)進(jìn)行對比（全表掃描），這個過程要要重復(fù)20次（type 表有20條數(shù)據(jù)）。

這里可以看到，type均為all。另外還可以看到MySQL幫我們做了一個優(yōu)化，使用了join buffer進(jìn)行緩存。

我們?yōu)楸或?qū)動表 book.card 添加索引優(yōu)化

CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

這里能夠看到，雖然type表仍舊是要處理20次，但是拿著type的數(shù)據(jù)去book中尋找時，走的是索引。對于B+樹來講，其時間復(fù)雜度為logN，相比前面的全表掃描要快很多。

也就是對于左外連接來講，如果只能添加一個索引，那么一定添加到被驅(qū)動表上。

當(dāng)然，給type的card頁創(chuàng)建索引也是可以的。

CREATE INDEX X ON `type`(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

如果索引只加在了驅(qū)動表（左表）呢？

DROP INDEX Y ON book;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

可以看到，同樣使用了join buffer。而對于驅(qū)動表來講，即使用到了索引也要做一個整體的遍歷（無非這時走的是索引文件）。而被驅(qū)動表沒有索引，那么性能會相對較慢。

如下圖所示，從其查詢成本我們也可以看到顯著區(qū)別。

結(jié)論： 左(外)連接時，索引加在右表的連接字段。left join用于確定如何從右表搜索行，左表一定都有。同理，右(外)連接時，索引創(chuàng)建在左表的連接字段。該連接字段在兩個表中的數(shù)據(jù)類型保持一致。

此外，從上面Using where; Using join buffer (Block Nested Loop)我們也可以想到，如果有條件，那么join buffer給一個較大的容量是有助于提升性能的。

內(nèi)連接INNER JOIN

我們?nèi)サ羲饕?，然后查看?zhí)行計劃。

DROP INDEX X ON `type`;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

我們給被驅(qū)動表 book.card 添加索引

CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

我們再給驅(qū)動表type添加索引

CREATE INDEX X ON `type`(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

可以看到這里二者均用到了索引。需要說明的是，這時type和book上下次序可能轉(zhuǎn)換，也就是說 對于inner join來講，查詢優(yōu)化器可以決定誰作為驅(qū)動表，誰作為被驅(qū)動表出現(xiàn)的 。

那如果book.card沒有索引，type.card 有索引呢？

DROP INDEX Y ON book;
EXPLAIN  SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

可以看到book作為了驅(qū)動表，type作為了被驅(qū)動表。即，對于內(nèi)連接來講，如果表的連接條件中只能有一個字段有索引，則有索引的字段所在的表會被作為被驅(qū)動表出現(xiàn)。

如果兩個表數(shù)據(jù)量不一致呢？比如這里我們type為40條，book為20條。

EXPLAIN  SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

結(jié)論： 對于內(nèi)連接來說，在兩個表的連接條件都存在索引的情況下，會選擇小表作為驅(qū)動表，即“小表驅(qū)動大表”。

到此這篇關(guān)于MySQL關(guān)聯(lián)查詢優(yōu)化實(shí)現(xiàn)方法詳解的文章就介紹到這了,更多相關(guān)MySQL關(guān)聯(lián)查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論