mysql?使用join進(jìn)行多表關(guān)聯(lián)查詢的操作方法
join類型
在一些報表統(tǒng)計或數(shù)據(jù)展示時候需要提取的數(shù)據(jù)分布在多個表中,這個時候需要進(jìn)行join連表操作。join將兩個或多個表當(dāng)成不同的數(shù)據(jù)集合,然后進(jìn)行集合取交集運算。比如有訂單Order表記錄用戶id,如果像查詢訂單對應(yīng)的用戶信息,可以將Order和User表進(jìn)行關(guān)聯(lián)。
根據(jù)join結(jié)果集計算方式不同,join大致分為兩種主要類型:
內(nèi)連接
內(nèi)連接(inner join)也稱為等值連接,是最常用的Join方式。它只返回兩個表中匹配的行,即兩個表中具有相同值的列的行。比如 from order o inner join user u on o.uid=u.uid。
外連接
外連接分兩種,左外連接和右外連接。兩個區(qū)別于內(nèi)連接地方是不僅包含兩個表等值情況,返回左表中的所有行以及右表中與左表匹配的行。如果右表中沒有匹配的行,則返回NULL。
如from order o left join user u on o.uid=u.uid。order.uid即使在user表不存在也會返回訂單記錄,只不過對應(yīng)的用戶信息為null。
右連接同理以右表數(shù)據(jù)為主返回右表所有數(shù)據(jù)。
還有一種全外連接,沒有關(guān)聯(lián)條件,查找兩個表之間的所有關(guān)聯(lián)和不關(guān)聯(lián)的記錄,無論它們是否有匹配條件。這種應(yīng)該不常用,只有數(shù)據(jù)對比時候可能會用。
join原理
mysql在表join查詢時候,使用嵌套循環(huán)方式(nested-loop algorithm)進(jìn)行數(shù)據(jù)匹配。就是拿驅(qū)動表每一條和被驅(qū)動表所有數(shù)據(jù)依次進(jìn)行匹配。這也很好理解。兩個關(guān)聯(lián)表相當(dāng)于兩個集合,然后求兩個集合的交集。就是從一個集合依次拿出所有和另一個進(jìn)行比較是否相等(是交集)。假如有t1、t2、t3三個表進(jìn)行關(guān)聯(lián),t1上有range范圍篩選,t1和t2關(guān)聯(lián)有ref類型索引關(guān)聯(lián),執(zhí)行過程大概這樣
for each row in t1匹配范圍數(shù)據(jù) { for each row in t2匹配索引值 { for each row in t3 { t3滿足連接條件記錄 } } }
這樣假設(shè)表A有M行記錄,表B有N行記錄。則需要M乘N次比較取交集。表B被掃描M*N次。這些都是磁盤全表掃描。顯然如果M和N都比較大的時候會比較慢。
上面看到嵌套循環(huán),外循環(huán)每行記錄都會導(dǎo)致內(nèi)循環(huán)多次執(zhí)行。這里注意多表關(guān)聯(lián)時候不是先計算前兩個的關(guān)聯(lián)結(jié)果然后結(jié)果再和后面的表進(jìn)行關(guān)聯(lián)。比如上面例子,不會有t1和t2的臨時連接結(jié)果集。
Block Nested-Loop Join Algorithm
為了提高join的效率,mysql在嵌套循環(huán)比對時候進(jìn)行了一定的修改優(yōu)化。使用一個變種塊嵌套循環(huán)(Block Nested-Loop Join Algorithm)。什么意思呢?這次我讀取驅(qū)動表的時候不是每次讀取一條了,我讀取多條,存放到一個join buffer 塊里。然后這樣每次多條和被驅(qū)動表的數(shù)據(jù)進(jìn)行比較,這樣就將原來的多次磁盤讀取比較轉(zhuǎn)移到了內(nèi)存比較。這樣被驅(qū)動表全表掃描次數(shù)變成了:(M/buffer rows)*N。
一次讀取的行數(shù)(buffer rows),由每行需要的數(shù)據(jù)大小和join_buffer_size大小兩個因素決定。
join_buffer_size的大小默認(rèn)是256KB
mysql> SELECT @@join_buffer_size; +--------------------+ | @@join_buffer_size | +--------------------+ | 262144 | +--------------------+
從上面的計算公式不難看出,驅(qū)動表的行數(shù)越少,join_buffer_size越大,掃描被驅(qū)動表的次數(shù)越少。這也是為什么要使用小表驅(qū)動大表的原因。如果驅(qū)動表join數(shù)據(jù)可以一次放到j(luò)oin_buffer_size中則被驅(qū)動表只需要一次全表掃描。如果不能驅(qū)動表會被分成多次讀取到j(luò)oin_buffer_size中,join_buffer_size會循環(huán)使用。
join buffer有以下幾個特點:
1、當(dāng)執(zhí)行計劃的type類型是ALL、index或range的時候會使用join buffer。
2、如果一個查詢中有多個表進(jìn)行連接操作, Join Buffer 只會為非常量表分配內(nèi)存空間,即使這個表的類型是 ALL 或者 index。
3、連接操作的 Join Buffer 中只存儲連接所需的列,而不是整個行。
4、一個join分配一個join buffer,如果一個查詢有多個join可能會使用多個join buffer。
5、join buffer的分配在join執(zhí)行前完成,查詢結(jié)束后立即釋放。
如果使用了join buffer,一般在explain的Extra列會有Using join buffer (Block Nested Loop)信息。
Index Nested-Loop Join
上面說的使用Block Nested-Loop Join算法,相對最初的嵌套循環(huán)使用join buffer提高了匹配的效率。但是當(dāng)表數(shù)據(jù)很大時,仍然不太理想。在Block Nested-Loop Join算法中執(zhí)行計劃type最好是range。還有更好的ref和eq_ref沒有說。也就是當(dāng)join的列是索引列時。
注意這里說的索引列是指被驅(qū)動表,因為驅(qū)動表總是要進(jìn)行一次全表掃描。但是這個時候去被驅(qū)動表找數(shù)據(jù)就不是全表掃描了,因為有索引,是直接在索引樹上進(jìn)行查找這就快多了。
在常規(guī)業(yè)務(wù)開發(fā)(非高并發(fā))進(jìn)行表join操作是非常常見的,在表連接時候盡量使用小表驅(qū)動大表,這里的小表是指條件過濾后參加join條件的數(shù)據(jù)。連接時在被驅(qū)動表連接字段上創(chuàng)建索引,也就是使用Index Nested-Loop Join。
join優(yōu)化 Batched Key Access Joins(BKA)
Batched Key Access Joins從名字上也能看出來大致意思。key是索引意思,是對Index Nested-Loop Join的查詢優(yōu)化。 BKA要借助于MRR,先來說下MRR。
Multi-Range Read
當(dāng)在某個輔助索引上進(jìn)行范圍查找時,通常在回表查詢的過程中會發(fā)生大量的隨機讀。
比如下面的語句
select * from user where uname like '曹%';
uname列上建有索引,可以從uname索引上獲取主鍵值,然后再回表根據(jù)主鍵逐條從聚簇索引上查詢行數(shù)據(jù)。這個時候獲取的主鍵是無序的,會造成大量的隨機讀。MRR(Multi-Range Read)優(yōu)化就是為了解決這種場景,
MRR處理過程是在根據(jù)索引獲取到主鍵后不是立即進(jìn)行回表查詢,而是先將主鍵值放入到一個read_rnd_buffer中,然后對其進(jìn)行排序,最后再有序的去聚簇索引檢索行數(shù)據(jù)。如果對應(yīng)主鍵索引值足夠密集,這樣可以大大減少隨機讀。
read_rnd_buffer的大小由read_rnd_buffer_size參數(shù)來進(jìn)行控制,默認(rèn)大小是256kb。另外開啟MMR需要在optimizer_switch中設(shè)置mrr=on。默認(rèn)值是on。另外還有一個相關(guān)的參數(shù)mrr_cost_based,如果這個參數(shù)設(shè)置為off表示符合條件就使用mrr,如果為on則優(yōu)化器會基于成本進(jìn)行考慮是否使用mrr。該值默認(rèn)是on 。
如果使用到了mrr,在explain的Extra列一般會顯示:Using index condition; Using MRR。
看完了上面的MRR,這里回到使用索引join連表查詢其實也存在上面的問題?;叵隝ndex Nested-Loop Join的查詢執(zhí)行過程,是每次一行行的去驅(qū)動表中去找數(shù)據(jù),每次驅(qū)動表只被匹配一個值。這里Batched Key Access Joins就結(jié)合了Block Nested-Loop Join和MRR兩個的優(yōu)點,執(zhí)行過程大致如下:
1、批量取驅(qū)動表的關(guān)聯(lián)字段放入join buffer中
2、將join buffer中的關(guān)聯(lián)列值,批量的發(fā)送給引擎在被驅(qū)動表上通過MRR以最優(yōu)的方式進(jìn)行索引樹搜索匹配,獲取到對應(yīng)的主鍵(rowId)。
3、按檢索到的rowId進(jìn)行排序,順序從被驅(qū)動表上獲取關(guān)聯(lián)數(shù)據(jù)。
BKA是對Index Nested-Loop Join的優(yōu)化,被驅(qū)動表上要有對應(yīng)的索引。并且被驅(qū)動表上會發(fā)生回表查詢的情況。
開啟BKA首先需要開啟MRR,需要進(jìn)行以下設(shè)置
mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
到此這篇關(guān)于mysql 使用join進(jìn)行多表關(guān)聯(lián)查詢的操作方法的文章就介紹到這了,更多相關(guān)mysql 多表關(guān)聯(lián)查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
簡單實現(xiàn)MySQL服務(wù)器的優(yōu)化配置方法
我們今天主要向大家描述的是MySQL服務(wù)器的優(yōu)化配置的時機操作步驟,以及在MySQL服務(wù)器的優(yōu)化配置的的過程中值得我們主義的事項的介紹。2011-03-03MySQL批量導(dǎo)入Excel數(shù)據(jù)(超詳細(xì))
這篇文章主要介紹了MySQL批量導(dǎo)入Excel數(shù)據(jù)(超詳細(xì)),文章圍繞主題展開詳細(xì)的內(nèi)容介紹,具有一定的參考價值,感興趣的小伙伴可以參考一下,希望對你的學(xué)習(xí)有所幫助2022-08-08JDBC鏈接mysql插入數(shù)據(jù)后顯示問號的原因及解決辦法
這篇文章主要介紹了JDBC鏈接mysql插入數(shù)據(jù)后顯示問號的原因及解決辦法的相關(guān)資料,需要的朋友可以參考下2016-04-04CentOS 7 下使用yum安裝MySQL5.7.20 最簡單方法
這篇文章主要介紹了CentOS 7 下使用yum安裝MySQL5.7.20 最簡單 方法,需要的朋友可以參考下2018-11-1164位CentOs7源碼安裝mysql-5.6.35過程分享
本文給大家分享的是在64位CentOs7系統(tǒng)中使用源碼安裝mysql-5.6.35的詳細(xì)過程,非常的細(xì)致,有需要的小伙伴可以參考下2017-02-02使用canal監(jiān)控mysql數(shù)據(jù)庫實現(xiàn)elasticsearch索引實時更新問題
這篇文章主要介紹了使用canal監(jiān)控mysql數(shù)據(jù)庫實現(xiàn)elasticsearch索引實時更新,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2022-03-03