MySQL關聯查詢優化實現方法詳解

Posted on by

我們準備如下兩個表，並插入數據。

#分類
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為驅動表（內表），book為被驅動表（外表）。

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

每次從type中獲取一條數據然後後book中的數據進行對比（全表掃描），這個過程要要重復20次（type 表有20條數據）。

這裡可以看到，type均為all。另外還可以看到MySQL幫我們做瞭一個優化，使用瞭join buffer進行緩存。

我們為被驅動表 book.card 添加索引優化

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

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

也就是對於左外連接來講，如果隻能添加一個索引，那麼一定添加到被驅動表上。

當然，給type的card頁創建索引也是可以的。

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

如果索引隻加在瞭驅動表（左表）呢？

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

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

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

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

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

內連接INNER JOIN

我們去掉索引，然後查看執行計劃。

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

我們給被驅動表 book.card 添加索引

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

我們再給驅動表type添加索引

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

可以看到這裡二者均用到瞭索引。需要說明的是，這時type和book上下次序可能轉換，也就是說 對於inner join來講，查詢優化器可以決定誰作為驅動表，誰作為被驅動表出現的 。

那如果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作為瞭驅動表，type作為瞭被驅動表。即，對於內連接來講，如果表的連接條件中隻能有一個字段有索引，則有索引的字段所在的表會被作為被驅動表出現。

如果兩個表數據量不一致呢？比如這裡我們type為40條，book為20條。

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

結論： 對於內連接來說，在兩個表的連接條件都存在索引的情況下，會選擇小表作為驅動表，即“小表驅動大表”。

到此這篇關於MySQL關聯查詢優化實現方法詳解的文章就介紹到這瞭,更多相關MySQL關聯查詢優化內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：