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

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

1、嵌套查詢

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

原因在哪呢？

下面是一個(gè)簡(jiǎn)單地嵌套查詢

SELECT id ,name ,age

FROM teacher

WHERE status=0 and name IN (?

SELECT name FROM student WHERE age >18

)

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

這是我們美好的愿景。

這個(gè)時(shí)候我們就可以使用explain來(lái)看一下這條語(yǔ)句的執(zhí)行過(guò)程是怎樣的

+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
| 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是什么意思呢？

翻譯就是依靠外層查詢

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

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

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

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

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

2、join查詢

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

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

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

3、解決方法

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

使用多個(gè)單個(gè)的查詢來(lái)代替嵌套查詢

2、其次，我們還可以使用臨時(shí)表進(jìn)行簡(jiǎ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

)

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

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

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

模型

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

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

問(wèn)題

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

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

思路

彎路：

最開(kāi)始的思路是嵌套，就是根據(jù)現(xiàn)實(shí)世界的邏輯關(guān)系一層一層建立聯(lián)系，SELECT * FROM tb WHERE id IN(SELECT * FROM tb WHERE)，沿著這個(gè)思路嘗試了很多，首先在SELECT外層聲明的變量?jī)?nèi)層的嵌套識(shí)別不了，內(nèi)外層建立的變量不能相互訪問(wèn)，另外一個(gè)這種建立起來(lái)的關(guān)系，沒(méi)有一一對(duì)應(yīng)關(guān)系，因?yàn)槲覀冇玫氖荌N，最終只要存在就可以，所以沒(méi)有嚴(yán)格的一一對(duì)應(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)那里不對(duì)了沒(méi)有，每個(gè)一級(jí)行業(yè)下面包含所有的三級(jí)行業(yè)，所以這種嵌套方式走不通，同時(shí)進(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)層那個(gè)SELECT是不可用的。

新思路：

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

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

2.2 第2層：tb_cls(AS t2)二級(jí)行業(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)一級(jí)行業(yè)跟tb_cls(AS t2)建立關(guān)聯(lián)：t2.p_id=t1.id AND t1.cls=1

最終，建立起來(lái)的三級(jí)行業(yè)對(duì)應(yīng)一級(jí)行業(yè)的對(duì)應(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é)果如下，跟我們實(shí)際建立的情況一致，第一個(gè)任務(wù)（第一、建立三級(jí)行業(yè)跟一級(jí)行業(yè)一一對(duì)應(yīng)關(guān)系）完成。 

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

具體語(yǔ)句如下：

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é)果如下，簡(jiǎn)單計(jì)算一下一級(jí)、二級(jí)、三級(jí)費(fèi)用是不是查詢出來(lái)的值，至此，任務(wù)二也圓滿完成。

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

總結(jié)

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

最新評(píng)論