MySQL中3表join流程分析

常聽說MySQL中3表 join 的執(zhí)行流程并不是前兩張表 join 得出結(jié)果，再與第三張表進行 join；而是3表嵌套的循環(huán)連接。那這個3表嵌套的循環(huán)連接具體又是個什么流程呢？與前兩張表 join 得出結(jié)果再與第三張表進行 join 的執(zhí)行效率相比如何呢？下面通過一個例子來分析分析。

前提

set optimizer_switch='block_nested_loop=off';
關(guān)聯(lián)字段無索引的情況下強制使用索引嵌套循環(huán)連接算法，目的是更好的觀察掃描行數(shù)。

表結(jié)構(gòu)和數(shù)據(jù)如下：

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;

drop procedure idata;
delimiter ;;
create 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;;
delimiter ;
call idata();

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

示例SQL：

select * from t1 join t2 on t1.b=t2.b  join t3 on t1.b=t3.b where t1.a<21;

通過掃描行數(shù)結(jié)果分析join過程

通過 slow log 得知一共掃描 24100 行：

# Query_time: 0.016162  Lock_time: 0.000249 Rows_sent: 20  Rows_examined: 24100
SET timestamp=1617348099;
select * from t1 join t2 on t1.b=t2.b  join t3 on t1.b=t3.b where t1.a<21;

執(zhí)行計劃顯示用的索引嵌套循環(huán)連接算法：

mysql> explain select * from t1 join t2 on t1.b=t2.b  join t3 on t1.b=t3.b where t1.a<21;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | a             | NULL | NULL    | NULL |  100 |    20.00 | Using where |
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |    10.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

掃描行數(shù)構(gòu)成：

  • t1 掃描 100行；
  • t3 掃描 20*200=4000 行；
  • t2 掃描 20*1000=20000 行。

總行數(shù)=100+4000+20000=24100。
從這個結(jié)果來看，join 過程像是先 t1 和 t3 join 得出 20 行中間結(jié)果，再與 t2 進行 join 得出結(jié)果。這結(jié)論與我們通常認為的 3表 join 實際上是3表嵌套的循環(huán)連接不一樣，接著往下看。

通過執(zhí)行成本分析join過程

查看執(zhí)行計劃成本：
mysql> explain format=json select * from t1 join t2 on t1.b=t2.b join t3 on t1.b=t3.b where t1.a<21\G

其他信息：

  • t1表100行，只有1個數(shù)據(jù)頁(可通過mysql.innodb_table_stats)；
  • t2表1000行，有4個數(shù)據(jù)頁；
  • t3表200行，只有1個數(shù)據(jù)頁；
  • io_block_read_cost=1.0，成本常數(shù)(MySQL5.7)：讀取一個頁面花費的成本默認是1.0；
  • row_evaluate_cost=0.2，成本常數(shù)(MySQL5.7)：讀取以及檢測一條記錄是否符合搜索條件的成本默認是0.2。
t1 是驅(qū)動表，全表掃描
  • 掃描 100行；
  • 預(yù)估滿足條件的只有 20%，即 100*20%=20，即t1的扇出。

IO成本=1*1.0=1
CPU成本=100*0.2=20
t1總成本=21

t3 是被驅(qū)動表，全表掃描
  • 每次掃描200行；
  • 因為驅(qū)動表扇出為20，所以要查找20次t3，總共會掃描 20*200=4000 行；
  • 預(yù)估滿足條件的行只有掃描行數(shù)的 10%，即 4000*10%=400，即為 t1 join t3 后的扇出，即 rows_produced_per_json。

IO成本=1*1.0=1
CPU成本=200*0.2=40
t3表總成本=驅(qū)動表扇出*(IO成本+CPU成本)=20*(1+40)=820
階段性總成本=21+820=841
此處 eval_cost=80，實則為驅(qū)動表扇出*被驅(qū)動每次掃描行數(shù)*filtered*成本常數(shù)，即 20*200*10%*0.2。
簡化公式為：eval_cost=rows_produced_per_json*成本常數(shù)

t2 也是被驅(qū)動表，全表掃描
  • 每次查找掃描1000行；
  • 要查找 400 次，總共會掃描 400*1000=400000 行；
  • 預(yù)估滿足條件的只有 10%，即 400000*10%=40000，即為 t2 的扇出，即 rows_produced_per_json。

IO成本=4*1.0=4
CPU成本=1000*0.2=200
t2表總成本=前2表join的扇出*(IO成本+CPU成本)=400*(4+200)=81600
階段性總成本=841+81600=82441
此處 eval_cost=8000，即 rows_produced_per_json*成本常數(shù)，即 40000*0.2

根據(jù)執(zhí)行計劃成本分析：

  • t1 表查找 1 次，每次掃描 100行；
  • t3 表查找 20 次，每次掃描 200 行；
  • t2 表查找 400 次，每次掃描 1000 行。

這樣看，3表 join 流程是：

  1. 全表掃描 t1，滿足條件的有 20 行，先取第1行數(shù)據(jù)記為 R1；
  2. 從 R1 中取出 b 字段去 t3 表中查找；
  3. 取出 t3 中滿足條件的行，跟 R1 組成一行，作為結(jié)果集的一部分，從結(jié)果集中取第1行數(shù)據(jù)記為 X1；
    a. 從X1 中取出 b 字段去 t2 表中查找；
    b. 取出 t2 中滿足條件的行，跟 X1 組成一行，作為結(jié)果集的一部分；
    c. 重復(fù) a、b 步驟，直到結(jié)束。
  4. 重復(fù) 2、3步驟，直到結(jié)束。

圖示(這里展示的是索引嵌套循環(huán)算法時3表join的流程，塊循環(huán)嵌套算法不一樣)：

注意，由于造的數(shù)據(jù)比較特殊，所以第 3 步得出的中間結(jié)果集實際上只有 1行，所以最終 t2 表的查找次數(shù)是 20*1=20，所以掃描總行數(shù)是 20*1000。所以單看 slow log 中顯示的 24100 行，會誤認為是先得出 t1 和 t3 join 的結(jié)果，再去和 t2 進行 join。

當我調(diào)整 t3 的數(shù)據(jù)，刪除20行，再插入20行，使?jié)M足 b<21 的數(shù)據(jù)翻倍，這樣“第 3 步得出的中間結(jié)果集”變成 2 行：

mysql> delete from t3 where id>180;
Query OK, 20 rows affected (0.00 sec)
mysql> insert into t3 select * from t3 where b<21;
Query OK, 20 rows affected (0.00 sec)

再來看slow log 中掃描的總行數(shù)為44100，t1、t3的掃描行數(shù)不變，t2 的掃描行數(shù)變?yōu)?20*2*1000=40000：

# Query_time: 0.013848  Lock_time: 0.000100 Rows_sent: 40  Rows_examined: 44100
SET timestamp=1617354884;
select * from t1 join t2 on t1.b=t2.b  join t3 on t1.b=t3.b where t1.a<21;

為什么執(zhí)行計劃中分析得到的是 t2 表查找 400 次呢？
因為執(zhí)行計劃對t1 join t3 的扇出是個估算值，不準確。而 slow log 是真實執(zhí)行后統(tǒng)計的，是個準確值。

為什么執(zhí)行計劃中，t2表的執(zhí)行次數(shù)是用“t1 join t3 的扇出”表示的？這不是說明 t1 先和 t3 join，結(jié)果再和 t2 join？
其實拆解來看，“3表嵌套循環(huán)” 和 “前2表 join 的結(jié)果和第3張表 join” 兩種算法，成本是一樣的，而且如果要按3表嵌套循環(huán)的方式展示每張表的成本將非常復(fù)雜，可讀性不強。所以執(zhí)行計劃中這么表示沒有問題。

總結(jié)

總的來說，對于3表join或者多表join 來說，“3表嵌套循環(huán)” 和 “先2表 join，結(jié)果和第3張表join” 兩種算法，成本是一樣的。

當被驅(qū)動表的關(guān)聯(lián)字段不是唯一索引，或者沒有索引，每次掃描行數(shù)會大于1時，其扇出誤差會非常大。比如在上面的示例中：
t3 實際的扇出只有 20，但優(yōu)化器估算值是總掃描行數(shù)的 10%，由于t3表的關(guān)聯(lián)字段沒有索引，所以每次都要全表掃描200行，總的掃描行數(shù)=20*200=4000，扇出=4000*10%=400，比實際的20大了20倍。尤其對于后續(xù)表的 join 來說，成本估算會產(chǎn)生更嚴重的偏差。

如果是 left join，每個被驅(qū)動表的 filtered 都會被優(yōu)化器認定為 100%，誤差更大！

通常建議join不超過2表，就是因為優(yōu)化器估算成本誤差大導(dǎo)致選擇不好的執(zhí)行計劃，如果要用，一定要記?。宏P(guān)聯(lián)字段必須要有索引，最好是唯一性或者基數(shù)大的索引。補充：MySQL 8.0有hash join后這種情況會好很多。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容