join查詢和多次查詢比較

MySQL多表關(guān)聯(lián)查詢效率高點(diǎn)還是多次單表查詢效率高？

在數(shù)據(jù)量不夠大的時(shí)候，用join沒(méi)有問(wèn)題，但是一般都會(huì)拉到service層上去做

第一：?jiǎn)螜C(jī)數(shù)據(jù)庫(kù)計(jì)算資源很貴，數(shù)據(jù)庫(kù)同時(shí)要服務(wù)寫(xiě)和讀，都需要消耗CPU，為了能讓數(shù)據(jù)庫(kù)的吞吐變得更高，而業(yè)務(wù)又不在乎那幾百微妙到毫秒級(jí)的延時(shí)差距，業(yè)務(wù)會(huì)把更多計(jì)算放到service層做，畢竟計(jì)算資源很好水平擴(kuò)展，數(shù)據(jù)庫(kù)很難啊，所以大多數(shù)業(yè)務(wù)會(huì)把純計(jì)算操作放到service層做，而將數(shù)據(jù)庫(kù)當(dāng)成一種帶事務(wù)能力的kv系統(tǒng)來(lái)使用，這是一種重業(yè)務(wù)，輕DB的架構(gòu)思路

第二：很多復(fù)雜的業(yè)務(wù)可能會(huì)由于發(fā)展的歷史原因，一般不會(huì)只用一種數(shù)據(jù)庫(kù)，一般會(huì)在多個(gè)數(shù)據(jù)庫(kù)上加一層中間件，多個(gè)數(shù)據(jù)庫(kù)之間就沒(méi)辦法join了，自然業(yè)務(wù)會(huì)抽象出一個(gè)service層，降低對(duì)數(shù)據(jù)庫(kù)的耦合。

第三：對(duì)于一些大型公司由于數(shù)據(jù)規(guī)模龐大，不得不對(duì)數(shù)據(jù)庫(kù)進(jìn)行分庫(kù)分表，對(duì)于分庫(kù)分表的應(yīng)用，使用join也受到了很多限制，除非業(yè)務(wù)能夠很好的根據(jù)sharding key明確要join的兩個(gè)表在同一個(gè)物理庫(kù)中。而中間件一般對(duì)跨庫(kù)join都支持不好。

舉一個(gè)很常見(jiàn)的業(yè)務(wù)例子，在分庫(kù)分表中，要同步更新兩個(gè)表，這兩個(gè)表位于不同的物理庫(kù)中，為了保證數(shù)據(jù)一致性，一種做法是通過(guò)分布式事務(wù)中間件將兩個(gè)更新操作放到一個(gè)事務(wù)中，但這樣的操作一般要加全局鎖，性能很捉急，而有些業(yè)務(wù)能夠容忍短暫的數(shù)據(jù)不一致，怎么做？讓它們分別更新唄，但是會(huì)存在數(shù)據(jù)寫(xiě)失敗的問(wèn)題，那就起個(gè)定時(shí)任務(wù)，掃描下A表有沒(méi)有失敗的行，然后看看B表是不是也沒(méi)寫(xiě)成功，然后對(duì)這兩條關(guān)聯(lián)記錄做訂正，這個(gè)時(shí)候同樣沒(méi)法用join去實(shí)現(xiàn)，只能將數(shù)據(jù)拉到service層應(yīng)用自己來(lái)合并了。。。

事實(shí)上，用分解關(guān)聯(lián)查詢的方式重構(gòu)查詢具有如下優(yōu)勢(shì)：

讓緩存的效率更高。

許多應(yīng)用程序可以方便地緩存單表查詢對(duì)應(yīng)的結(jié)果對(duì)象。另外對(duì)于MySQL的查詢緩存來(lái)說(shuō)，如果關(guān)聯(lián)中的某個(gè)表發(fā)生了變化，那么就無(wú)法使用查詢緩存了，而拆分后，如果某個(gè)表很少改變，那么基于該表的查詢就可以重復(fù)利用查詢緩存結(jié)果了。

將查詢分解后，執(zhí)行單個(gè)查詢可以減少鎖的競(jìng)爭(zhēng)。

在應(yīng)用層做關(guān)聯(lián)，可以更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分，更容易做到高性能和可擴(kuò)展。

查詢本身效率也可能會(huì)有所提升

可以減少冗余記錄的查詢。

更進(jìn)一步，這樣做相當(dāng)于在應(yīng)用中實(shí)現(xiàn)了哈希關(guān)聯(lián)，而不是使用MySQL的嵌套環(huán)關(guān)聯(lián)，某些場(chǎng)景哈希關(guān)聯(lián)的效率更高很多。

查詢語(yǔ)句join、on、where執(zhí)行順序

MySQL 的執(zhí)行順序

一、典型SELECT語(yǔ)句完整的執(zhí)行順序

1）from子句組裝來(lái)自不同數(shù)據(jù)源的數(shù)據(jù)；

2）使用on進(jìn)行join連接的數(shù)據(jù)篩選

3）where子句基于指定的條件對(duì)記錄行進(jìn)行篩選；

4）group by子句將數(shù)據(jù)劃分為多個(gè)分組；

5）cube， rollup

6）使用聚集函數(shù)進(jìn)行計(jì)算；

7）使用having子句篩選分組；

8）計(jì)算所有的表達(dá)式；

9）計(jì)算select的字段；

10）使用distinct 進(jìn)行數(shù)據(jù)去重

11）使用order by對(duì)結(jié)果集進(jìn)行排序。

12）選擇TOPN的數(shù)據(jù)

二、from

如果是采用的 關(guān)聯(lián) from tableA, tableB ，這2個(gè)表會(huì)先組織進(jìn)行笛卡爾積，然后在進(jìn)行下面的 where、group by 等操作。

三、on

如果使用left join， inner join 或者 outer full join的時(shí)候，使用on 進(jìn)行條件篩選后，在進(jìn)行join。

看下面的2個(gè)sql 和結(jié)果。2者的區(qū)別僅僅是在on后面的一個(gè)語(yǔ)句在on和where位置的不同。 由此可以看出是先通過(guò)on 進(jìn)行條件篩選，然后在join，最后在進(jìn)行where條件篩選。

假如：是先進(jìn)行join，在進(jìn)行on的話，會(huì)產(chǎn)生一個(gè)笛卡爾積，然后在篩選。這樣的left join 和 直連接 沒(méi)有任何的區(qū)別。 所以肯定是先on 條件篩選后，在進(jìn)行join。

假如：是在進(jìn)行where 后，在on，在進(jìn)行join， 下面2個(gè)sql的返回結(jié)果應(yīng)該是一樣的。由此可以見(jiàn)，where是針對(duì) join 后的集合進(jìn)行的篩選。

綜上： 先 執(zhí)行on 條件篩選， 在進(jìn)行join， 最后進(jìn)行where 篩選

SELECT DISTINCT a.domain , b.domain
FROM mal_nxdomains_raw a
LEFT JOIN mal_nxdomains_detail b ON a.domain = b.domain AND b.date = ‘20160403'
WHERE a.date = ‘20160403'

SELECT DISTINCT a.domain , b.domain
FROM mal_nxdomains_raw a
LEFT JOIN mal_nxdomains_detail b ON a.domain = b.domain #and b.date = ‘20160403'
WHERE a.date = ‘20160403'
AND b.date = ‘20160403'

四、on 條件與where 條件

1、使用位置

• on 條件位置在join后面
• where 條件在join 與on完成的后面

2、使用對(duì)象

• on 的使用對(duì)象是被關(guān)聯(lián)表
• where的使用對(duì)象可以是主表，也可以是關(guān)聯(lián)表

3、選擇與使用

主表?xiàng)l件篩選：只能在where后面使用。

被關(guān)聯(lián)表，如果是想縮小join范圍，可以放置到on后面。如果是關(guān)聯(lián)后再查詢，可以放置到where 后面。

如果left join 中，where條件有對(duì)被關(guān)聯(lián)表的 關(guān)聯(lián)字段的 非空查詢，與使用inner join的效果后，在進(jìn)行where 篩選的效果是一樣的。不能起到left join的作用。

五、join 流程

tableA join tableB， 從A表中拿出一條數(shù)據(jù)，到B表中進(jìn)行掃描匹配。所以A的行數(shù)決定查詢次數(shù)，B表的行數(shù)決定掃描范圍。例如A表100條，B表200表，需要100次從A表中取出一條數(shù)據(jù)到B表中進(jìn)行200次的比對(duì)。

相對(duì)來(lái)說(shuō)從A表取數(shù)據(jù)消耗的資源比較多。所以盡量tableA選擇比較小的表。同時(shí)縮小B表的查詢范圍。

但是實(shí)際應(yīng)用中，因?yàn)槎叻祷氐臄?shù)據(jù)結(jié)果不同，使用的索引也不同，導(dǎo)致條件放置在on 和 where 效率是不一定誰(shuí)更好。要根據(jù)需求來(lái)確定。

總結(jié)

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