join類型

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

根據join結果集計算方式不同，join大致分為兩種主要類型：

內連接

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

外連接

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

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

右連接同理以右表數據為主返回右表所有數據。

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

join原理

mysql在表join查詢時候，使用嵌套循環(huán)方式（nested-loop algorithm）進行數據匹配。就是拿驅動表每一條和被驅動表所有數據依次進行匹配。這也很好理解。兩個關聯(lián)表相當于兩個集合，然后求兩個集合的交集。就是從一個集合依次拿出所有和另一個進行比較是否相等（是交集）。假如有t1、t2、t3三個表進行關聯(lián),t1上有range范圍篩選，t1和t2關聯(lián)有ref類型索引關聯(lián)，執(zhí)行過程大概這樣

for each row in t1匹配范圍數據 {
  for each row in t2匹配索引值 {
    for each row in t3 {
      t3滿足連接條件記錄
    }
  }
}

這樣假設表A有M行記錄，表B有N行記錄。則需要M乘N次比較取交集。表B被掃描M*N次。這些都是磁盤全表掃描。顯然如果M和N都比較大的時候會比較慢。

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

Block Nested-Loop Join Algorithm

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

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

join_buffer_size的大小默認是256KB

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

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

join buffer有以下幾個特點：

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

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

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

4、一個join分配一個join buffer，如果一個查詢有多個join可能會使用多個join buffer。

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

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

Index Nested-Loop Join

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

注意這里說的索引列是指被驅動表，因為驅動表總是要進行一次全表掃描。但是這個時候去被驅動表找數據就不是全表掃描了，因為有索引，是直接在索引樹上進行查找這就快多了。

在常規(guī)業(yè)務開發(fā)(非高并發(fā))進行表join操作是非常常見的，在表連接時候盡量使用小表驅動大表，這里的小表是指條件過濾后參加join條件的數據。連接時在被驅動表連接字段上創(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

當在某個輔助索引上進行范圍查找時，通常在回表查詢的過程中會發(fā)生大量的隨機讀。

比如下面的語句

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

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

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

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

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

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

1、批量取驅動表的關聯(lián)字段放入join buffer中

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

3、按檢索到的rowId進行排序，順序從被驅動表上獲取關聯(lián)數據。

BKA是對Index Nested-Loop Join的優(yōu)化，被驅動表上要有對應的索引。并且被驅動表上會發(fā)生回表查詢的情況。

開啟BKA首先需要開啟MRR，需要進行以下設置

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

到此這篇關于mysql 使用join進行多表關聯(lián)查詢的操作方法的文章就介紹到這了,更多相關mysql 多表關聯(lián)查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論