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

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

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

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

現(xiàn)在有兩個表一個department 表 一個 員工與部門之間的關聯(lián)表  dept_emp

現(xiàn)在由于部門裁撤，要統(tǒng)計哪些部門現(xiàn)在還有員工，將有員工的部門顯示出來。

當然不提表的結(jié)構(gòu)和行數(shù)的性能比較都是屬于耍流氓

下面是兩種寫法

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;

的寫法要優(yōu)于

select distinct em.dept_name 

from dept_emp as de 

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

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

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

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  ;

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

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

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

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

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

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

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;

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

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

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

MySQL子查詢是從外部到內(nèi)部評估查詢。也就是說，它首先獲取外層表達式的值，然后運行子查詢并捕獲它生成的行。對于子查詢有用的優(yōu)化是“通知”子查詢，只有內(nèi)部表達式的條件等于外部表達式的那些行才可以進行優(yōu)化，將一個適當?shù)牡仁较峦频阶硬樵兊腤HERE子句中來實現(xiàn)的。

寫法如下

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

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

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

如果有NULL 值結(jié)果就是

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

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

問題是 or 這個操作 您的另外進行一個表操作的問題，另外還有無法在ICP 下推了，主要的原因是NULL 在數(shù)據(jù)庫里面并不是FALSE 而是未知的狀態(tài)，ICP 下推必須要進行適當?shù)挠嬎?，必須能夠檢查SELECT是否已經(jīng)產(chǎn)生了任何行，這樣內(nèi)部條件 = 外部條件就不能下推到子查詢中。

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

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