mysql left join 優化學習
mysql 如何優化left join
今天遇到一個left join優化的問題,搞了一下午,中間查了不少資料,對MySQL的查詢計劃還有查詢優化有了更進一步的瞭解,做一個簡單的記錄:
select c.* from hotel_info_original c
left join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
where h.hotel_id is null
這個sql是用來查詢出c表中有h表中無的記錄,所以想到了用left join的特性(返回左邊全部記錄,右表不滿足匹配條件的記錄對應行返回null)來滿足需求,不料這個查詢非常慢。先來看查詢計劃:
rows代表這個步驟相對上一步結果的每一行需要掃描的行數,可以看到這個sql需要掃描的行數為35773*8134,非常大的一個數字。本來c和h表的記錄條數分別為40000+和10000+,這幾乎是兩個表做笛卡爾積的開銷了(select * from c,h)。
於是我上網查了下MySQL實現join的原理,原來MySQL內部採用了一種叫做 nested loop join的演算法。Nested Loop Join 實際上就是通過驅動表的結果集作為迴圈基礎資料,然後一條一條的通過該結果集中的資料作為過濾條件到下一個表中查詢資料,然後合併結果。如果還有第三個參與 Join,則再通過前兩個表的 Join 結果集作為迴圈基礎資料,再一次通過迴圈查詢條件到第三個表中查詢資料,如此往復,基本上MySQL採用的是最容易理解的演算法來實現join。所以驅動表的選擇非常重要,驅動表的資料小可以顯著降低掃描的行數。
那麼為什麼一般情況下join的效率要高於left join很多?很多人說不明白原因,只人云亦云,我今天下午感悟出來了一點。一般情況下參與聯合查詢的兩張表都會一大一小,如果是join,在沒有其他過濾條件的情況下MySQL會選擇小表作為驅動表,但是left join一般用作大表去join小表,而left join本身的特性決定了MySQL會用大表去做驅動表,這樣下來效率就差了不少,如果我把上面那個sql改成
select c.* from hotel_info_original c
join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
查詢計劃如下:
很明顯,MySQL選擇了小表作為驅動表,再配合(hotel_id,hotel_type)上的索引瞬間降低了好多個數量級。。。。。
另外,我今天還明白了一個關於left join 的通用法則,即:如果where條件中含有右表的非空條件(除開is null),則left join語句等同於join語句,可直接改寫成join語句。
後記:
隨著檢視MySQL reference manual對這個問題進行了更進一步的瞭解。MySQL在執行join時會把join分為system/const/eq_ref/ref/range/index/ALl等好幾類,連線的效率從前往後
依次遞減,對於我的第一個sql,連線型別是index,所以幾乎是全表掃描的效果。但是我很奇怪我在(hotel_id,hotel_type)兩列上宣告瞭unique key,根據官方文件連線型別應該是eq_ref才對,
這個問題一直困擾了我兩天,在google和stackoverflow上都沒有找到能夠解釋這個問題的文章,莫非我這個問題無解了?抱著解決這個問題的決心今天又翻看了一遍MySQL官方文件
關於優化查詢的部分,看到了這樣一句:這裡的一個問題是MySQL能更高效地在宣告具有相同型別和尺寸的列上使用索引。我感覺我找到了問題所在,於是我將original和 collection表的(hotel_type,hotel_id)的encoding和collation(決定字元比較的規則)全部改成統一的utf8_general_ci,然後再次執行第一條sql的查詢計劃,得到如下結果:
連線型別已經由index優化到了ref,如果將hotel_type申明為not null可以優化到eq_ref,不過這裡影響不大了,優化後這條sql能在0.01ms內執行完。
那麼如何優化left join:
1、條件中儘量能夠過濾一些行將驅動表變得小一點,用小表去驅動大表
2、右表的條件列一定要加上索引(主鍵、唯一索引、字首索引等),最好能夠使type達到range及以上(ref,eq_ref,const,system)
3、無視以上兩點,一般不要用left join~~!
相關文章
- mysql + left joinMySql
- mysql的left join和inner join的效率對比,以及如何優化MySql優化
- MySQL LEFT JOIN/ INNER JOIN/RIGHT JOINMySql
- mysql left join轉inner joinMySql
- 【MySQL】LEFT JOIN 踩坑MySql
- MySQL 之 LEFT JOIN 避坑指南MySql
- mysql中的left join、right join 、inner join的詳細用法MySql
- MYSQL 怎麼 LEFT JOIN 多表聯查MySql
- Oracle優化案例-用left join代替反連線 not in not exists(十)Oracle優化
- MYSQL count標量子查詢改left joinMySql
- LEFT JOIN 和JOIN 多表連線
- sql:left join和join區別SQL
- SQL Server Left joinSQLServer
- 一個left join SQL 簡單優化分析SQL優化
- 深入理解mysql之left join 使用詳解MySql
- mysql常用連線查詢join,left,right,crossMySqlROS
- join、inner join、left join、right join、outer join的區別
- Mysql-left join on後接and,和where的區別MySql
- SQL Union 和left join
- mysql INNER JOIN、LEFT JOIN、RIGHT JOIN;內連線(等值連線)、左連線、右連線MySql
- MySQL系列6 - join語句的優化MySql優化
- MySQL優化學習手札(三)MySql優化
- oracle update left join查詢Oracle
- Inner Join, Left Outer Join和Association的區別
- sql的left join 、right join 、inner join之間的區別SQL
- Mysql 優化(學習筆記二十)MySql優化筆記
- MySQL優化學習筆記之explainMySql優化筆記AI
- MySQL優化學習筆記之索引MySql優化筆記索引
- join 查詢優化優化
- Mysql資料庫的join演算法介紹,優美的執行優化MySql資料庫演算法優化
- MySQL筆記-左連線的使用(left join有關聯的多表查詢)MySql筆記
- TiDB資料庫left join與版本問題TiDB資料庫
- MySQL優化學習手札(四) 單表訪問方法MySql優化
- MySQL學習之影響優化器選擇索引因素MySql優化索引
- MySQL(12)---紀錄一次left join一對多關係而引起的BUGMySql
- mysql 左連結 left join 條件寫在where 後面與 on後面的區別MySql
- Oracle中left join中右表的限制條件Oracle
- left join 後用 on 還是 where,區別大了!