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

#分類(lèi)
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#圖書(shū)
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ū)動(dòng)表（內(nèi)表），book為被驅(qū)動(dòng)表（外表）。

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

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

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

我們?yōu)楸或?qū)動(dòng)表 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中尋找時(shí)，走的是索引。對(duì)于B+樹(shù)來(lái)講，其時(shí)間復(fù)雜度為logN，相比前面的全表掃描要快很多。

也就是對(duì)于左外連接來(lái)講，如果只能添加一個(gè)索引，那么一定添加到被驅(qū)動(dòng)表上。

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

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

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

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

可以看到，同樣使用了join buffer。而對(duì)于驅(qū)動(dòng)表來(lái)講，即使用到了索引也要做一個(gè)整體的遍歷（無(wú)非這時(shí)走的是索引文件）。而被驅(qū)動(dòng)表沒(méi)有索引，那么性能會(huì)相對(duì)較慢。

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

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

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

內(nèi)連接INNER JOIN

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

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

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

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

我們?cè)俳o驅(qū)動(dòng)表type添加索引

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

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

那如果book.card沒(méi)有索引，type.card 有索引呢？

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

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

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

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

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

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

最新評(píng)論