//假設(shè)一個(gè)for循環(huán)

for($i = 0; $i < 10000; $i++)

{

for ($j = 0; $i < 50; $j++)

{

}

}

for($i = 0; $i < 50; $i++)

{

for ($j = 0; $i < 10000; $j++)

{

}

}

看以上兩個(gè)for循環(huán)，總共循環(huán)的次數(shù)是一樣的。但是對(duì)于mysql數(shù)據(jù)庫(kù)而言，并不是這樣了，我們盡量選擇第②個(gè)for循環(huán)，也就是小表驅(qū)動(dòng)大表。

數(shù)據(jù)庫(kù)最傷神的就是跟程序鏈接釋放，第一個(gè)建立了10000次鏈接，第二個(gè)建立了50次。假設(shè)鏈接了兩次，每次做上百萬(wàn)次的數(shù)據(jù)集查詢，查完就走，這樣就只做了兩次；相反建立了上百萬(wàn)次鏈接，申請(qǐng)鏈接釋放反復(fù)重復(fù)，這樣系統(tǒng)就受不了了。

這時(shí)候就誕生了in 和exists的對(duì)比。

小表驅(qū)動(dòng)大表：即小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集。

這里假設(shè)A表代表員工表，B表代表部門表。

假設(shè)部門只有三個(gè)，銷售、技術(shù)部、行政部，言下之意是在這三個(gè)部門里的所有員工都查出。

select * from A where id in (select id from B);

這樣寫就等價(jià)于：

for select id from B。比如華為有100個(gè)部門，但是華為的員工少說(shuō)有15W-20W，員工總比部門多，這時(shí)候就相當(dāng)于得到了小表(部門表)；for select * from A where A.id = B.id，相當(dāng)于A.id等B表里面的，相當(dāng)于從部門表獲得對(duì)應(yīng)的id。

當(dāng)B表的數(shù)據(jù)集必須小于A表的數(shù)據(jù)集時(shí)，用in優(yōu)于exists。

反之

select * from A where exists (select 1 from B where B.id = A.id); //這里的select 1并不絕對(duì)，可以寫為select 'X'或者'A','B','C'都可以，只要是常量就可以。

這樣寫就等價(jià)于：

for select * from A,先從A表做循環(huán)

for select * from B where B.id = A.id,再?gòu)腂表做循環(huán)。

這樣exists就會(huì)變成看看A表是否存在于(select 1 from B where B.id = A.id)里面，這個(gè)查詢返回的是TRUE或者FALSE的BOOL值，簡(jiǎn)單來(lái)說(shuō)就是要當(dāng)A表的數(shù)據(jù)集小于B表的數(shù)據(jù)集時(shí)，用exists優(yōu)于in。要注意的是：A表與B表的ID字段應(yīng)該建立索引。

語(yǔ)法：EXISTS

SELECT …FROM table WHERE EXISTS(subquery)。

理解：將主查詢的數(shù)據(jù)放到子查詢中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果(TRUE或者FALSE)來(lái)決定朱查詢的數(shù)據(jù)結(jié)果是否得意保留。

相當(dāng)于從表A和B中取出交集，然后再?gòu)腁表中取出所在交集的部分?jǐn)?shù)據(jù)，當(dāng)然后面加WHERE條件還可以進(jìn)一步篩選。

補(bǔ)充

1：EXISTS(subquery)只返回TRUE或者FALSE，因此子查詢中的SELECT * 也可以是SELECT 1或者SELECT ‘X’，官方說(shuō)法是實(shí)際執(zhí)行時(shí)會(huì)忽略SELECT清單，因此沒(méi)有區(qū)別。

2：EXISTS子查詢的實(shí)際執(zhí)行過(guò)程可能經(jīng)過(guò)了優(yōu)化而不是我們理解上的逐條對(duì)比，如果擔(dān)憂效率問(wèn)題，可進(jìn)行實(shí)際校驗(yàn)。

3：EXISTS子查詢旺旺可以用條件表達(dá)式，其他子查詢或者JOIN來(lái)替代，何種最優(yōu)需要具體問(wèn)題具體分析。

如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大。

延伸舉例鞏固

如果兩個(gè)表中一個(gè)較小，一個(gè)是大表，則子查詢表大的用exists，子查詢表小的用in：

例如：表A(小表)，表B(大表)

select * from A where cc in (select cc from B) ;// 效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc) ;// 效率高，用到了B表上cc列的索引。

相反

select * from B where cc in (select cc from A) ; //效率高，用到了B表上cc列的索引；

select * from B where exists(select cc from A where cc=B.cc) ;//效率低，用到了A表上cc列的索引。

not in 和not exists如果查詢語(yǔ)句使用了not in 那么內(nèi)外表都進(jìn)行全表掃描，沒(méi)有用到索引；而not extsts 的子查詢依然能用到表上的索引。所以無(wú)論那個(gè)表大，用not exists都比not in要快。

與50位技術(shù)專家面對(duì)面20年技術(shù)見(jiàn)證，附贈(zèng)技術(shù)全景圖

總結(jié)

以上是生活随笔為你收集整理的mysql算法优化原则_Mysql优化原则_小表驱动大表IN和EXISTS的合理利用的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。