MYSQL同樣邏輯的四種SQL寫法分析

這篇文章將為大家詳細講解有關MySQL同樣邏輯的四種SQL寫法分析，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

創新互聯-專業網站定制、快速模板網站建設、高性價比赤峰網站開發、企業建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式赤峰網站制作公司更省心,省錢,快速模板網站建設找我們，業務覆蓋赤峰地區。費用合理售后完善，十年實體公司更值得信賴。

提到復雜查詢，MYSQL 頭疼的旅程就開始了，當然優化的方法和其他的數據監控也不大同，MYSQL的語句優化屬于發散性思維，只要你能用上的方法都可以，可不限制于數據庫本身的語句優化。所以MYSQL的優化好像是一個講不完的故事。

下面舉一個列子看看同時達到同樣結果的不同的語句的寫法，產生的性能結果有什么不同

現在有兩個表一個department 表 一個 員工與部門之間的關聯表  dept_emp

現在由于部門裁撤，要統計哪些部門現在還有員工，將有員工的部門顯示出來。

當然不提表的結構和行數的性能比較都是屬于耍流氓

下面是兩種寫法

select em.dept_name

from (select distinct dept_no from dept_emp) as de

inner join departments as em on em.dept_no = de.dept_no;

select distinct em.dept_name 

from dept_emp as de 

inner join departments as em on em.dept_no = de.dept_no;

從上圖的分析來看

select em.dept_name

from (select distinct dept_no from dept_emp) as de

inner join departments as em on em.dept_no = de.dept_no;

的寫法要優于

select distinct em.dept_name 

from dept_emp as de 

inner join departments as em on em.dept_no = de.dept_no;

在有相關的索引的加持下，在查詢中先將重復的數據進行去重后，在進行關聯的方法要明顯比，先關聯在去重的方法要好。

那到此就完結了，有么有其他的寫法，下面就是另一種寫法

select em.dept_name

from departments as em 

inner join (

select de.dept_no_d from (select distinct dept_no as dept_no_d from dept_emp) as de  where de.dept_no_d in (select dept_no from departments)) as tm on em.dept_no = tm.dept_no_d  ;

同樣能達到同樣的結果，看上去復雜的寫法，其實也并不慢

那我們是否還有其他的寫法，或者讓剛才的方式的查詢變得更快

select distinct de.dept_name from departments as de where exists (select 1 from dept_emp em where de.dept_no = em.dept_no);

最后我們將所有的四種寫法，執行一遍，通過profile 對比一下四種方法的快慢和消耗

從上面的分析看，最次的是使用in來進行查詢，而最好的是用exists 的方式來進行查詢， 使用  JOIN 的方法屬于中規中矩。

但在分析這四種查詢的方法，以及產生的不同效果中，可以看到

select distinct de.dept_name from departments as de where exists (select 1 from dept_emp em where de.dept_no = em.dept_no);

與

select distinct em.dept_name 

    -> from dept_emp as de 

    -> inner join departments as em on em.dept_no = de.dept_no;

兩種方法在選擇的索引以及執行計劃都有類似的地方，為什么使用exists的子查詢在這里要快于使用join的方式

可以看到雖然語句的執行計劃相同，但不同的是慢的那個使用了Using temporary, 也就是二次處理了搜尋上來的結果，進行了一個去重的工作，而快的exists 則沒有這個操作。

那問題就來了，不是說子查詢慢嗎，子查詢是如何進行查詢的，但實際上為什么在這個例子不慢。

MySQL子查詢是從外部到內部評估查詢。也就是說，它首先獲取外層表達式的值，然后運行子查詢并捕獲它生成的行。對于子查詢有用的優化是“通知”子查詢，只有內部表達式的條件等于外部表達式的那些行才可以進行優化，將一個適當的等式下推到子查詢的WHERE子句中來實現的。

寫法如下

EXISTS (SELECT 1 FROM ... WHERE  外部條件=內部條件)

我們例子中的寫法快的那個恰恰和這個寫法相同，在轉換之后，MySQL可以使用下推等式來限制它必須檢查的行數來計算子查詢，記得之前寫過一篇關于 ICP 的文字，這里就不說 下推的問題了。

說到這里要實現ICP 還要有一個條件就是，不能有NULL 值，也就是空值， 所以這也是 DBA 費盡心機的 和 開發人員溝通，說你的這個字段盡量不要有NULL最好有 DEFAULT  默認值的一個原因，因為你不知道何時因為你的字段里面初期設計的有NULL 值，就造成費盡心機的優化半途而廢。

如果有NULL 值結果就是

EXISTS (SELECT 1 FROM ... WHERE  外部條件=內部條件 or 內部條件 is NUll)

當然這也沒有什么，MYSQL 遇到NULL 不走索引的，我也曾經寫過一篇，辟謠了。

問題是 or 這個操作您的另外進行一個表操作的問題，另外還有無法在ICP 下推了，主要的原因是NULL 在數據庫里面并不是FALSE 而是未知的狀態，ICP 下推必須要進行適當的計算，必須能夠檢查SELECT是否已經產生了任何行，這樣內部條件 = 外部條件就不能下推到子查詢中。

所以這也是為什么人家子查詢不慢，你的慢的一個因素，不要認為查詢寫的一樣，結果就一樣，各種前期不注意的地方，就能坑你一下。

關于MYSQL同樣邏輯的四種SQL寫法分析就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

標題名稱：MYSQL同樣邏輯的四種SQL寫法分析
轉載來源：http://m.kartarina.com/article26/pgoocg.html

成都網站建設公司_創新互聯，為您提供定制開發、營銷型網站建設、網站排名、網站導航、標簽優化、軟件開發

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯