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

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

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

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

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

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

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

當(dāng)然不提表的結(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;

在有相關(guān)的索引的加持下，在查詢中先將重復(fù)的數(shù)據(jù)進(jìn)行去重后，在進(jìn)行關(guān)聯(lián)的方法要明顯比，先關(guā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  ;

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

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

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來進(jìn)行查詢，而最好的是用exists 的方式來進(jìn)行查詢， 使用  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é)果，進(jìn)行了一個去重的工作，而快的exists 則沒有這個操作。

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

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

寫法如下

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

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

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

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

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

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

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

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

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

標(biāo)題名稱：MYSQL同樣邏輯的四種SQL寫法分析
轉(zhuǎn)載來源：http://muchs.cn/article26/pgoocg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制開發(fā)、營銷型網(wǎng)站建設(shè)、網(wǎng)站排名、網(wǎng)站導(dǎo)航、標(biāo)簽優(yōu)化、軟件開發(fā)

廣告

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