「mysql優化專題」你們要的多表查詢優化來啦！請查收（4）

上一篇講的是 單表查詢的優化 ，沒看過的朋友可以關注後檢視【mysql優化專題】。當然，對資料表的多表查詢也是必不可少的。本篇内容主要講解多表聯合查詢的優化，本号内有多個專題，緻力于長期分享高品質原創java文章。

一、多表查詢連接配接的選擇：

相信這内連接配接，左連接配接什麼的大家都比較熟悉了，當然還有左外連接配接什麼的，基本用不上我就不貼出來了。這圖隻是讓大家回憶一下，各種連接配接查詢。 然後要告訴大家的是，

需要根據查詢的情況，想好使用哪種連接配接方式效率更高。（這是技術文）

二、MySQL的JOIN實作原理

在MySQL 中，隻有一種Join 算法，就是大名鼎鼎的Nested Loop Join，他沒有其他很多資料庫所提供的Hash Join，也沒有Sort Merge Join。顧名思義，Nested Loop Join 實際上就是通過驅動表的結果集作為循環基礎資料，然後一條一條的通過該結果集中的資料作為過濾條件到下一個表中查詢資料，然後合并結果。如果還有第三個參與Join，則再通過前兩個表的Join 結果集作為循環基礎資料，再一次通過循環查詢條件到第三個表中查詢資料，如此往複。 ——摘自《MySQL 性能調優與架構設計》

三、補充：mysql對sql語句的容錯問題

即在sql語句不完全符合書寫建議的情況，mysql會允許這種情況，盡可能解釋它：

1)一般cross join後面加上where條件，但是用cross join+on也是被解釋為cross join+where；

2)一般内連接配接都需要加上on限定條件，如上面場景一；如果不加會被解釋為交叉連接配接；

3)如果連接配接表格使用的是逗号，會被解釋為交叉連接配接；

注：sql标準中還有union join和natural inner join，mysql不支援，而且本身也沒有多大意義，其實就是為了“健壯”。但是其實結果可以用上面的幾種連接配接方式得到。

三、超大型資料盡可能盡力不要寫子查詢，使用連接配接（JOIN）去替換它：

當然，關于這句話，也不一定就全是這樣。

1）因為在大型的資料進行中，子查詢是非常常見的，特别是在查詢出來的資料需要進一步處理的情況，無論是可讀性還是效率上，

這時候的子查都是更優。

2）然而在一些特定的場景，可以直接從資料庫讀取就可以的，比如一個表（A表 a，b，c字段，需要内部資料交集）join自己的效率必然比放一個子查在where中快得多。

（這真是技術文）

四、使用聯合(UNION)來代替手動建立的臨時表

UNION是會把結果排序的！！！

union查詢：它可以把需要使用臨時表的兩條或更多的select查詢合并的一個查詢中（即把兩次或多次查詢結果合并起來。）。在用戶端的查詢會話結束的時候，臨時表會被自動删除，進而保證資料庫整齊、高效。使用union來建立查詢的時候，我們隻需要用UNION作為關鍵字把多個select語句連接配接起來就可以了，要注意的是所有select語句中的字段數目要想同。

要求：兩次查詢的列數必須一緻（列的類型可以不一樣，但推薦查詢的每一列，相對應的類型要一樣）

可以來自多張表的資料：多次sql語句取出的列名可以不一緻，此時以第一個sql語句的列名為準。

如果不同的語句中取出的行，有完全相同(這裡表示的是每個列的值都相同)，那麼union會将相同的行合并，最終隻保留一行。也可以這樣了解，union會去掉重複的行。

如果不想去掉重複的行，可以使用union all。

如果子句中有order by,limit，需用括号()包起來。推薦放到所有子句之後，即對最終合并的結果來排序或篩選。

注意：

1、UNION 結果集中的列名總是等于第一個 SELECT 語句中的列名

2、UNION 内部的 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的資料類型。同時，每條 SELECT 語句中的列的順序必須相同

UNION ALL

的作用和文法：

預設地，UNION 操作符選取不同的值。如果允許重複的值，請使用 UNION ALL。當 ALL 随 UNION 一起使用時（即 UNION ALL），不消除重複行。

五、總結

（1）對于要求全面的結果時，我們需要使用連接配接操作（LEFT JOIN / RIGHT JOIN / FULL JOIN）；

（2）應盡量避免在 where 子句中對字段進行 null 值判斷，否則将導緻引擎放棄使用索引而進行全表掃描，如：

備注、描述、評論之類的可以設定為 NULL，其他最好不要使用NULL。

不要以為 NULL 不需要空間，比如：char(100) 型，在字段建立時，空間就固定了， 不管是否插入值（NULL也包含在内），都是占用 100個字元的空間的，如果是varchar這樣的變長字段， null 不占用空間。

可以在num上設定預設值0，確定表中num列沒有null值，然後這樣查詢：

select id from t where num = 0

（3）in 和 not in 也要慎用，否則會導緻全表掃描，如：

對于連續的數值，能用 between 就不要用 in 了：

很多時候用 exists 代替 in 是一個好的選擇：

（4）盡量使用數字型字段，若隻含數值資訊的字段盡量不要設計為字元型，這會降低查詢和連接配接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連 接時會逐個比較字元串中每一個字元，而對于數字型而言隻需要比較一次就夠了。

（5）盡量使用表變量來代替臨時表。如果表變量包含大量資料，請注意索引非常有限（隻有主鍵索引）。

（6）不要以為使用MySQL的一些連接配接操作對查詢有多麼大的改善，其實核心是索引（别打我，下一篇講）

（這絕對是技術文）

今天多表查詢優化就講到這裡，覺得有收獲的同學可以收藏關注。本号内有多個專題，如【資料結構】、【netty專題】、【dubbo專題】、【mysql優化專題】、【redis專題】、【高并發專題】等優質好文。一起學習，共同進步。