join類型

在一些報(bào)表統(tǒng)計(jì)或數(shù)據(jù)展示時(shí)候需要提取的數(shù)據(jù)分布在多個(gè)表中，這個(gè)時(shí)候需要進(jìn)行join連表操作。join將兩個(gè)或多個(gè)表當(dāng)成不同的數(shù)據(jù)集合，然后進(jìn)行集合取交集運(yùn)算。比如有訂單Order表記錄用戶id，如果像查詢訂單對應(yīng)的用戶信息，可以將Order和User表進(jìn)行關(guān)聯(lián)。

根據(jù)join結(jié)果集計(jì)算方式不同，join大致分為兩種主要類型：

內(nèi)連接

內(nèi)連接（inner join）也稱為等值連接，是最常用的Join方式。它只返回兩個(gè)表中匹配的行，即兩個(gè)表中具有相同值的列的行。比如 from order o inner join user u on o.uid=u.uid。

外連接

外連接分兩種，左外連接和右外連接。兩個(gè)區(qū)別于內(nèi)連接地方是不僅包含兩個(gè)表等值情況，返回左表中的所有行以及右表中與左表匹配的行。如果右表中沒有匹配的行，則返回NULL。

如from order o left join user u on o.uid=u.uid。order.uid即使在user表不存在也會(huì)返回訂單記錄，只不過對應(yīng)的用戶信息為null。

右連接同理以右表數(shù)據(jù)為主返回右表所有數(shù)據(jù)。

還有一種全外連接，沒有關(guān)聯(lián)條件，查找兩個(gè)表之間的所有關(guān)聯(lián)和不關(guān)聯(lián)的記錄，無論它們是否有匹配條件。這種應(yīng)該不常用，只有數(shù)據(jù)對比時(shí)候可能會(huì)用。

join原理

mysql在表join查詢時(shí)候，使用嵌套循環(huán)方式（nested-loop algorithm）進(jìn)行數(shù)據(jù)匹配。就是拿驅(qū)動(dòng)表每一條和被驅(qū)動(dòng)表所有數(shù)據(jù)依次進(jìn)行匹配。這也很好理解。兩個(gè)關(guān)聯(lián)表相當(dāng)于兩個(gè)集合，然后求兩個(gè)集合的交集。就是從一個(gè)集合依次拿出所有和另一個(gè)進(jìn)行比較是否相等（是交集）。假如有t1、t2、t3三個(gè)表進(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都比較大的時(shí)候會(huì)比較慢。

上面看到嵌套循環(huán)，外循環(huán)每行記錄都會(huì)導(dǎo)致內(nèi)循環(huán)多次執(zhí)行。這里注意多表關(guān)聯(lián)時(shí)候不是先計(jì)算前兩個(gè)的關(guān)聯(lián)結(jié)果然后結(jié)果再和后面的表進(jìn)行關(guān)聯(lián)。比如上面例子，不會(huì)有t1和t2的臨時(shí)連接結(jié)果集。

Block Nested-Loop Join Algorithm

為了提高join的效率，mysql在嵌套循環(huán)比對時(shí)候進(jìn)行了一定的修改優(yōu)化。使用一個(gè)變種塊嵌套循環(huán)（Block Nested-Loop Join Algorithm）。什么意思呢？這次我讀取驅(qū)動(dòng)表的時(shí)候不是每次讀取一條了，我讀取多條，存放到一個(gè)join buffer 塊里。然后這樣每次多條和被驅(qū)動(dòng)表的數(shù)據(jù)進(jìn)行比較，這樣就將原來的多次磁盤讀取比較轉(zhuǎn)移到了內(nèi)存比較。這樣被驅(qū)動(dòng)表全表掃描次數(shù)變成了：(M/buffer rows)*N。

一次讀取的行數(shù)（buffer rows），由每行需要的數(shù)據(jù)大小和join_buffer_size大小兩個(gè)因素決定。

join_buffer_size的大小默認(rèn)是256KB

mysql> SELECT @@join_buffer_size;
+--------------------+
| @@join_buffer_size |
+--------------------+
|             262144 |
+--------------------+

從上面的計(jì)算公式不難看出，驅(qū)動(dòng)表的行數(shù)越少，join_buffer_size越大，掃描被驅(qū)動(dòng)表的次數(shù)越少。這也是為什么要使用小表驅(qū)動(dòng)大表的原因。如果驅(qū)動(dòng)表join數(shù)據(jù)可以一次放到j(luò)oin_buffer_size中則被驅(qū)動(dòng)表只需要一次全表掃描。如果不能驅(qū)動(dòng)表會(huì)被分成多次讀取到j(luò)oin_buffer_size中，join_buffer_size會(huì)循環(huán)使用。

join buffer有以下幾個(gè)特點(diǎn)：

1、當(dāng)執(zhí)行計(jì)劃的type類型是ALL、index或range的時(shí)候會(huì)使用join buffer。

2、如果一個(gè)查詢中有多個(gè)表進(jìn)行連接操作， Join Buffer 只會(huì)為非常量表分配內(nèi)存空間，即使這個(gè)表的類型是 ALL 或者 index。

3、連接操作的 Join Buffer 中只存儲(chǔ)連接所需的列，而不是整個(gè)行。

4、一個(gè)join分配一個(gè)join buffer，如果一個(gè)查詢有多個(gè)join可能會(huì)使用多個(gè)join buffer。

5、join buffer的分配在join執(zhí)行前完成，查詢結(jié)束后立即釋放。

如果使用了join buffer，一般在explain的Extra列會(huì)有Using join buffer (Block Nested Loop)信息。

Index Nested-Loop Join

上面說的使用Block Nested-Loop Join算法，相對最初的嵌套循環(huán)使用join buffer提高了匹配的效率。但是當(dāng)表數(shù)據(jù)很大時(shí)，仍然不太理想。在Block Nested-Loop Join算法中執(zhí)行計(jì)劃type最好是range。還有更好的ref和eq_ref沒有說。也就是當(dāng)join的列是索引列時(shí)。

注意這里說的索引列是指被驅(qū)動(dòng)表，因?yàn)轵?qū)動(dòng)表總是要進(jìn)行一次全表掃描。但是這個(gè)時(shí)候去被驅(qū)動(dòng)表找數(shù)據(jù)就不是全表掃描了，因?yàn)橛兴饕侵苯釉谒饕龢渖线M(jìn)行查找這就快多了。

在常規(guī)業(yè)務(wù)開發(fā)(非高并發(fā))進(jìn)行表join操作是非常常見的，在表連接時(shí)候盡量使用小表驅(qū)動(dòng)大表，這里的小表是指條件過濾后參加join條件的數(shù)據(jù)。連接時(shí)在被驅(qū)動(dòng)表連接字段上創(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)在某個(gè)輔助索引上進(jìn)行范圍查找時(shí)，通常在回表查詢的過程中會(huì)發(fā)生大量的隨機(jī)讀。

比如下面的語句

select * from user where uname like '曹%';

uname列上建有索引，可以從uname索引上獲取主鍵值，然后再回表根據(jù)主鍵逐條從聚簇索引上查詢行數(shù)據(jù)。這個(gè)時(shí)候獲取的主鍵是無序的，會(huì)造成大量的隨機(jī)讀。MRR(Multi-Range Read)優(yōu)化就是為了解決這種場景，

MRR處理過程是在根據(jù)索引獲取到主鍵后不是立即進(jìn)行回表查詢，而是先將主鍵值放入到一個(gè)read_rnd_buffer中，然后對其進(jìn)行排序，最后再有序的去聚簇索引檢索行數(shù)據(jù)。如果對應(yīng)主鍵索引值足夠密集，這樣可以大大減少隨機(jī)讀。

read_rnd_buffer的大小由read_rnd_buffer_size參數(shù)來進(jìn)行控制，默認(rèn)大小是256kb。另外開啟MMR需要在optimizer_switch中設(shè)置mrr=on。默認(rèn)值是on。另外還有一個(gè)相關(guān)的參數(shù)mrr_cost_based，如果這個(gè)參數(shù)設(shè)置為off表示符合條件就使用mrr，如果為on則優(yōu)化器會(huì)基于成本進(jìn)行考慮是否使用mrr。該值默認(rèn)是on 。

如果使用到了mrr，在explain的Extra列一般會(huì)顯示：Using index condition; Using MRR。

看完了上面的MRR，這里回到使用索引join連表查詢其實(shí)也存在上面的問題?；叵隝ndex Nested-Loop Join的查詢執(zhí)行過程，是每次一行行的去驅(qū)動(dòng)表中去找數(shù)據(jù)，每次驅(qū)動(dòng)表只被匹配一個(gè)值。這里Batched Key Access Joins就結(jié)合了Block Nested-Loop Join和MRR兩個(gè)的優(yōu)點(diǎn)，執(zhí)行過程大致如下：

1、批量取驅(qū)動(dòng)表的關(guān)聯(lián)字段放入join buffer中

2、將join buffer中的關(guān)聯(lián)列值，批量的發(fā)送給引擎在被驅(qū)動(dòng)表上通過MRR以最優(yōu)的方式進(jìn)行索引樹搜索匹配，獲取到對應(yīng)的主鍵（rowId）。

3、按檢索到的rowId進(jìn)行排序，順序從被驅(qū)動(dòng)表上獲取關(guān)聯(lián)數(shù)據(jù)。

BKA是對Index Nested-Loop Join的優(yōu)化，被驅(qū)動(dòng)表上要有對應(yīng)的索引。并且被驅(qū)動(dòng)表上會(huì)發(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)文章希望大家以后多多支持腳本之家！

最新評論