MySQL中的join語句算法如何優化

本篇內容主要講解“MySQL中的join語句算法如何優化”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL中的join語句算法如何優化”吧!

一、join語句算法

創建兩個表t1和t2

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
	declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
END

create table t1 like t2;
insert into t1 (select * from t2 where id<=100);

這兩個表都有一個主鍵索引id和一個索引a，字段b上無索引。存儲過程idata()往表t2里插入了1000行數據，在表t1里插入的是100行數據

1、Index Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用join語句，MySQL優化器可能會選擇表t1或t2作為驅動表，通過straight_join讓MySQL使用固定的連接方式執行查詢，在這個語句里，t1是驅動表，t2是被驅動表

被驅動表t2的字段a上有索引，join過程用上了這個索引，因此這個語句的執行流程是這樣的：

1.從表t1中讀入一行數據R

2.從數據行R中，取出a字段到表t2里去查找

3.取出表t2中滿足條件的行，跟R組成一行，作為結果集的一部分

4.重復執行步驟1到3，直到表t1的末尾循環結束

這個過程可以用上被驅動表的索引，稱之為Index Nested-Loop Join，簡稱NLJ

在這個流程里：

1.對驅動表t1做了全表掃描，這個過程需要掃描100行

2.而對于每一行R，根據a字段去表t2查找，走的是樹搜索過程。由于我們構造的數據都是一一對應的，因此每次的搜索過程都只掃描一行，也是總共掃描100行

3.所以，整個執行流程，總掃描行數是200

假設不使用join，只能用單表查詢：

1.執行select * from t1，查出表t1的所有數據，這里有100行

2.循環遍歷這100行數據：

• 從每一行R取出字段a的值$R.a

• 執行select * from t2 where a=$R.a

• 把返回的結果和R構成結果集的一行

這個查詢過程，也是掃描了200行，但是總共執行了101條語句，比直接join多了100次交互。客戶端還要自己拼接SQL語句和結果。這么做還不如直接join好

在可以使用被驅動表的索引的情況下：

• 使用join語句，性能比強行拆成多個單表執行SQL語句的性能要好

• 如果使用join語句的話，需要讓小表做驅動表

2、Simple Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表t2的字段b上沒有索引，因此每次到t2去匹配的時候，就要做一次全表掃描。這個算法叫做Simple Nested-Loop Join

這樣算來，這個SQL請求就要掃描表t2多達100次，總共掃描100*100=10萬行

MySQL沒有使用這個Simple Nested-Loop Join算法，而是使用了另一個叫作Block Nested-Loop Join的算法，簡稱BNL

3、Block Nested-Loop Join

被驅動表上沒有可用的索引，算法的流程如下：

1.把表t1的數據讀入線程內存join_buffer中，由于這個語句中寫的是select *，因此是把整個表t1放入了內存

2.掃描表t2，把表t2中的每一行取出來，跟join_buffer中的數據作比對，滿足join條件的，作為結果集的一部分返回

在這個過程中，對表t1和表t2都做了一次全表掃描，因此總的掃描行數是1100。由于join_buffer是以無序數組的方式組織的，因此對表t2中的每一行，都要做100次判斷，總共需要在內存中做的判斷次數是100*1000=10萬次

使用Simple Nested-Loop Join算法進行查詢，掃描行數也是10萬行。因此，從時間復雜度上來說，這兩個算法是一樣的。但是，Block Nested-Loop Join算法的這10萬次判斷是內存操作，速度上會快很多，性能也更好

假設小表的行數是N，大表的行數是M，那么在這個算法里：

1）兩個表都做一次全表掃描，所以總的掃描行數是M + N

2）內存中的判斷次數是M ? N

這時候選擇大表還是小表做驅動表，執行耗時是一樣的

join_buffer的大小是由參數join_buffer_size設定的，默認值是256k。如果放不下表t1的所有數據話，策略很簡單，就是分段放

1）掃描表t1，順序讀取數據行放入join_buffer中，假設放到第88行join_buffer滿了

2）掃描表t2，把t2中的每一行取出來，跟join_buffer中的數據做對比，滿足join條件的，作為結果集的一部分返回

3）清空join_buffer

4）繼續掃描表t1，順序讀取最后的12行放入join_buffer中，繼續執行第2步

由于表t1被分成了兩次放入join_buffer中，導致表t2會被掃描兩次。雖然分成兩次放入join_buffer，但是判斷等值條件的此時還是不變的

假設，驅動表的數據行數是N，需要分成K段才能完成算法流程，被驅動表的數據行數是M 。這里的K不是常數，N越大K 就會越大，因此把K表示為λ ? N ，λ的取值范圍是(0,1)。所以，在這個算法的執行過程中：

1.掃描行數是N + λ ? N ? M

2.內存判斷N ? M

考慮到掃描行數，N 小一些，整個算式的結果會更小。所以應該讓小表當驅動表

4、能不能使用join語句？

1.如果可以使用Index Nested-Loop Join算法，也就是說可以用上被驅動表上的索引，其實是沒問題的

2.如果使用Block Nested-Loop Join算法，掃描行數就會過多。尤其是在大表上的join操作，這樣可能要掃描被驅動表很多次，會占用大量的系統資源。所以這種join盡量不要用

5、如果使用join，應該選擇大表做驅動表還是選擇小表做驅動表

1.如果是Index Nested-Loop Join算法，應該選擇小表做驅動表

2.如果是Block Nested-Loop Join算法：

• 在join_buffer_size足夠大的時候，是一樣的

• 在join_buffer_size不夠大的時候，應該選擇小表做驅動表

在決定哪個表做驅動表的時候，應該是兩個表按照各自的條件過濾，過濾完成以后，計算參數join的各個字段的總數據量，數據量小的那個表，就是小表，應該作為驅動表

二、join語句優化

創建兩個表t1、t2

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;

CREATE DEFINER = CURRENT_USER PROCEDURE `idata`()
BEGIN
	declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1001-i, i);
    set i=i+1;
  end while;
  
  set i=1;
  while(i<=1000000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;

END;

在表t1中，插入了1000行數據，每一行的a=1001-id的值。也就是說，表t1中字段a是逆序的。同時，在表t2中插入了100萬行數據

1、Multi-Range Read優化

Multi-Range Read（MRR）優化主要的目的是盡量使用順序讀盤

select * from t1 where a>=1 and a<=100;

主鍵索引是一棵B+樹，在這棵樹上，每次只能根據一個主鍵id查到一行數據。因此，回表是一行行搜索主鍵索引的

如果隨著a的值遞增順序查找的話，id的值就變成隨機的，那么就會出現隨機訪問，性能相對較差

因為大多數的數據都是按照主鍵遞增順序插入得到的，所以如果按照主鍵的遞增順序查詢，對磁盤的讀比較接近順序讀，能夠提升讀性能

這就是MRR優化的設計思路，語句的執行流程如下：

1.根據索引a，定位到滿足條件的記錄，將id值放入read_rnd_buffer中

2.將read_rnd_buffer中的id進行遞增排序

3.排序后的id數組，依次到主鍵id索引中查記錄，并作為結果返回

read_rnd_buffer的大小是由read_rnd_buffer_size參數控制的。如果步驟1中，read_rnd_buffer放滿了，就會先執行完步驟2和3，然后清空read_rnd_buffer。之后繼續找索引a的下個記錄，并繼續循環

如果想要穩定地使用MRR優化的話，需要設置set optimizer_switch="mrr_cost_based=off"

explain結果中，Extra字段多了Using MRR，表示的是用上了MRR優化。由于在read_rnd_buffer中按照id做了排序，所以最后得到的結果也是按照主鍵id遞增順序的

MRR能夠提升性能的核心在于，這條查詢語句在索引a上做的是一個范圍查詢，可以得到足夠多的主鍵id。這樣通過排序以后，再去主鍵索引查數據，才能體現出順序性的優勢

2、Batched Key Access

MySQL5.6引入了Batched Key Access（BKA）算法。這個BKA算法是對NLJ算法的優化

NLJ算法流程圖：

NLJ算法執行的邏輯是從驅動表t1，一行行地取出a的值，再到被驅動表t2去做join

BKA算法流程圖：

BKA算法執行的邏輯是把表t1的數據取出來一部分，先放到一個join_buffer，一起傳給表t2。在join_buffer中只會放入查詢需要的字段，如果join_buffer放不下所有數據，就會將數據分成多段執行上圖的流程

如果想要使用BKA優化算法的話，執行SQL語句之前，先設置

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

其中前兩個參數的作用是啟用MRR，原因是BKA算法的優化要依賴與MRR

3、BNL算法的性能問題

InnoDB對Buffer Pool的LRU算法做了優化，即：第一次從磁盤讀入內存的數據頁，會先放在old區域。如果1秒之后這個數據頁不再被訪問了，就不會被移動到LRU鏈表頭部，這樣對Buffer Pool的命中率影響就不大

如果一個使用BNL算法的join語句，多次掃描一個冷表，而且這個語句執行時間超過1秒，就會在再次掃描冷表的時候，把冷表的數據頁移到LRU鏈表頭部。這種情況對應的，是冷表的數據量小于整個Buffer Pool的3/8，能夠完全放入old區域的情況

如果這個冷表很大，就會出現另外一種情況：業務正常訪問的數據頁，沒有機會進入young區域。

由于優化機制的存在，一個正常訪問的數據頁，要進入young區域，需要隔1秒后再次被訪問到。但是，由于join語句在循環讀磁盤和淘汰內存頁，進入old區域的數據頁，很可能在1秒之內就被淘汰了。這樣就會導致MySQL實例的Buffer Pool在這段時間內，young區域的數據頁沒有被合理地淘汰

BNL算法對系統的影響主要包括三個方面：

1.可能會多次掃描被驅動表，占用磁盤IO資源

2.判斷join條件需要執行$M?N$次對比，如果是大表就會占用非常多的CPU資源

3.可能會導致Buffer Pool的熱數據被淘汰，影響內存命中率

4、BNL轉BKA

一些情況下，我們可以直接在被驅動表上建索引，這時就可以直接轉成BKA算法了

如果碰到一些不適合在被驅動表上建索引的情況，可以考慮使用臨時表。大致思路如下：

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

1）把表t2中滿足條件的數據放在臨時表tmp_t中

2）為了讓join使用BKA算法，給臨時表tmp_t的字段b加上索引

3）讓表t1和tmp_t做join操作

SQL語句寫法如下：

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;insert into temp_t select * from t2 where b>=1 and b<=2000;select * from t1 join temp_t on (t1.b=temp_t.b);

5、擴展hash join

MySQL的優化器和執行器不支持哈希join，可以自己實現在業務端，實現流程大致如下：

1.select * from t1;取得表t1的全部1000行數據，在業務端存入一個hash結構

2.select * from t2 where b>=1 and b<=2000;獲取表t2中滿足條件的2000行數據

3.把這2000行數據，一行一行地取到業務端，到hash結構的數據表中尋找匹配的數據。滿足匹配的條件的這行數據，就作為結果集的一行

到此，相信大家對“MySQL中的join語句算法如何優化”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！