欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

mysql?使用join進(jìn)行多表關(guān)聯(lián)查詢的操作方法

 更新時間:2024年02月09日 09:44:14   作者:曹朋羽  
在一些報表統(tǒng)計或數(shù)據(jù)展示時候需要提取的數(shù)據(jù)分布在多個表中,這個時候需要進(jìn)行join連表操作,join將兩個或多個表當(dāng)成不同的數(shù)據(jù)集合,然后進(jìn)行集合取交集運算,這篇文章主要介紹了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)文章

最新評論