解讀數(shù)據(jù)庫的嵌套查詢的性能

explain 是非常重要的性能查詢的工具?。?！

1、嵌套查詢

首先大家都知道我們一般不提倡嵌套查詢或是join查詢

原因在哪呢？

下面是一個簡單地嵌套查詢

SELECT id ,name ,age

FROM teacher

WHERE status=0 and name IN (?

SELECT name FROM student WHERE age >18

)

我們一開始設(shè)想的是先執(zhí)行內(nèi)部查詢，然后再執(zhí)行外部查詢的。

這是我們美好的愿景。

這個時候我們就可以使用explain來看一下這條語句的執(zhí)行過程是怎樣的

+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
| id ? | select_type ?| table ? ? ? | type ? | possible_keys | key ? ? ? ? ?| key_len | ref ?| rows | Extra ? ? ? |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
| ? ?1 | PRIMARY ? ? ?| teacher ? ? | ALL ? ?| NULL ? ? ? ? ?| NULL ? ? ? ? | NULL ? ?| NULL |65712| Using where |
| ? ?1 | PRIMARY ? ? ?| <subquery2> | eq_ref | distinct_key ?| distinct_key | 4 ? ? ? | func | ? ?1 | ? ? ? ? ? ? |
| ? ?2 | DEPENDENT SUBQUERY| student ? ? | ALL ? ?| NULL ? ? ? ? ?| NULL ? ? ? ? | NULL ? ?| NULL | ?418 | Using where |

這里可以看到student表的select_type是DEPENDENT SUBQUERY

DEPENDENT SUBQUERY是什么意思呢？

翻譯就是依靠外層查詢

簡而言之就是student內(nèi)層查詢要依靠外層查詢

如上面顯示，teacher表中關(guān)聯(lián)行數(shù)是65712

那就意味著內(nèi)層查詢要執(zhí)行6萬次之多，肯定會很慢的。

但也不是所有的嵌套的select_type都是DEPENDENT SUBQUERY

比如還有MATERIALIZED類型，他就是sql自己進(jìn)行的優(yōu)化，他會在第一次進(jìn)行子查詢的時候建立一個臨時表，保證后續(xù)查詢的速度。

2、join查詢

join連接也是類似的，聯(lián)表查詢時，會有一個驅(qū)動表來作為原始數(shù)據(jù)的循環(huán)表。

如果使用的是left join那么左表就是這個驅(qū)動表，反之亦然

我們要盡量用小表來當(dāng)做驅(qū)動表。如果實在不能判斷哪個比較合適就用join讓mysql來幫你做選擇，他會自動選擇一個小表來做驅(qū)動表。

3、解決方法

1、首先，最直接簡單地方法就是不使用嵌套查詢。

使用多個單個的查詢來代替嵌套查詢

2、其次，我們還可以使用臨時表進(jìn)行簡單地嵌套查詢

SELECT id ,name ,age

FROM teacher t, (SELECT name FROM student WHERE age>18) s

WHERE t.status=0 and t.name=s.name

)

問題：數(shù)據(jù)庫內(nèi)部嵌套關(guān)系實現(xiàn)

我在做報表的時候遇到一個問題，想了很長時間沒有解決，后來轉(zhuǎn)換思路一下子就解決了。具體問題是這樣的，我們公司有一張行業(yè)表，總共有四級行業(yè)需要維護(hù)，具體包括一級行業(yè)、二級行業(yè)、三級行業(yè)和四級行業(yè)，每個行業(yè)之間又存在包含關(guān)系，比如四級行業(yè)包含于三級行業(yè)，三級行業(yè)包含于二級行業(yè)，二級行業(yè)包含于一級行業(yè)，最詭異的地方就是我們把這么多信息放在一張表里維護(hù)，只不過額外加了兩個字段以示區(qū)分，一個是行業(yè)等級，一個是父行業(yè)，具體的表結(jié)構(gòu)如下：

最后的需求是有另外一張表，是用四級行業(yè)劃分的，其中有一項費用，最后需要按一級行業(yè)統(tǒng)計每個行業(yè)的費用。

模型

根據(jù)實際業(yè)務(wù)，為了說明這個問題，筆者在這里做了一個模型簡化，假設(shè)我們只有兩張表tb_cls和tb_cost，tb_cls包含行業(yè)id，行業(yè)等級cls，父行業(yè)p_id，所有行業(yè)（包括一級、二級、三級行業(yè)都保存在這張表里）都包含在內(nèi)，具體創(chuàng)建出來的表如下（為了讀者閱讀方便，這里做了一個簡化：id前面的第一位數(shù)代表一級行業(yè)編碼，例如121表示屬于一級大行業(yè)；整個id的位數(shù)代表幾級行業(yè)，例如211總共三位表示三級行業(yè)）：

另外一張表，我也做了簡化，只提取其中用到的行業(yè)id和費用兩個字段，具體的表內(nèi)容如下：

問題

我們現(xiàn)在的任務(wù)有兩個：

• 第一、建立三級行業(yè)跟一級行業(yè)一一對應(yīng)關(guān)系；
• 第二、按一級行業(yè)統(tǒng)計費用。

思路

彎路：

最開始的思路是嵌套，就是根據(jù)現(xiàn)實世界的邏輯關(guān)系一層一層建立聯(lián)系，SELECT * FROM tb WHERE id IN(SELECT * FROM tb WHERE)，沿著這個思路嘗試了很多，首先在SELECT外層聲明的變量內(nèi)層的嵌套識別不了，內(nèi)外層建立的變量不能相互訪問，另外一個這種建立起來的關(guān)系，沒有一一對應(yīng)關(guān)系，因為我們用的是IN，最終只要存在就可以，所以沒有嚴(yán)格的一一對應(yīng)關(guān)系。具體思路如下：

1.1 第1層：

SELECT id FROM tb_cost

1.2 第2層：

SELECT p_id FROM tb_cls WHERE id IN(SELECT id FROM tb_cost) AND cls=3

1.3 第3層：

SELECT p_id FROM tb_cls WHERE id IN(SELECT p_id FROM tb_cls WHERE id IN(SELECT id FROM tb_cost) AND cls=3) AND cls=2

1.4 第4層（最終）：

SELECT t1.id,t2.id FROM tb_cls AS t1,tb_cost AS t2 WHERE t1.id IN(SELECT p_id FROM tb_cls WHERE id IN(SELECT p_id FROM tb_cls WHERE id IN(SELECT id FROM tb_cost) AND cls=3) AND cls=2)AND cls=1;

最終查詢的結(jié)果如下：

發(fā)現(xiàn)那里不對了沒有，每個一級行業(yè)下面包含所有的三級行業(yè)，所以這種嵌套方式走不通，同時進(jìn)一步深入下去研究發(fā)現(xiàn)嵌套內(nèi)外層定義的變量是不能相互交互的，什么意思呢？

SELECT t1.id, var_1 FROM t1 WHERE p_id IN(SELECT id AS var_1 FROM t1)var_1變量在內(nèi)層那個SELECT是不可用的。

新思路：

基于上面的彎路，筆者換了一個，假設(shè)我們有3張一模一樣的表，通過這3張不同的表來區(qū)分各自的邏輯關(guān)系，把這3張表看成不同的表，一個個添加條件，具體思路如下：

2.1 第1層：tb_cls(AS t3)三級行業(yè)跟tb_cost(AS t4)建立關(guān)聯(lián)：t3.id=t4.id AND t3.cls=3

2.2 第2層：tb_cls(AS t2)二級行業(yè)跟tb_cls(AS t3)建立關(guān)聯(lián)：t3.p_id=t2.id AND t2.cls=2

2.3 第3層：tb_cls(AS t1)一級行業(yè)跟tb_cls(AS t2)建立關(guān)聯(lián)：t2.p_id=t1.id AND t1.cls=1

最終，建立起來的三級行業(yè)對應(yīng)一級行業(yè)的對應(yīng)關(guān)系如下：

SELECT t1.id,t4.id FROM tb_cls AS t1,tb_cls AS t2,tb_cls AS t3,tb_cost AS t4 WHERE t4.id=t3.id AND t3.p_id=t2.id AND t2.p_id=t1.id AND t3.cls=3 AND t2.cls=2 AND t1.cls=1;

查詢結(jié)果如下，跟我們實際建立的情況一致，第一個任務(wù)（第一、建立三級行業(yè)跟一級行業(yè)一一對應(yīng)關(guān)系）完成。 

解決了第一個任務(wù)，第二個任務(wù)就簡單多了，其實就是按照一級行業(yè)id加個GROUP BY，分一下組就可以，

具體語句如下：

SELECT t1.id,SUM(t4.cost) FROM tb_cls AS t1,tb_cls AS t2,tb_cls AS t3,tb_cost AS t4 WHERE t4.id=t3.id AND t3.p_id=t2.id AND t2.p_id=t1.id AND t3.cls=3 AND t2.cls=2 AND t1.cls=1 GROUP BY t1.id;

查詢結(jié)果如下，簡單計算一下一級、二級、三級費用是不是查詢出來的值，至此，任務(wù)二也圓滿完成。

總之，當(dāng)我們需要解決SQL語句的查詢?nèi)蝿?wù)的時候，不要一味的選擇深奧的技術(shù)、邏輯復(fù)雜的語言去解決（像筆者這里用多層嵌套，最后把自己繞進(jìn)去了。）首先我們要做的是簡化邏輯，能通過簡單的思路解決復(fù)雜的問題本身也是一種能力，在這個基礎(chǔ)上然后基于性能、需求、業(yè)務(wù)慢慢再繼續(xù)優(yōu)化SQL才是我們應(yīng)該做的。

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論