MySQL中的表連接原理分析

更新時間：2025年06月26日 14:20:39 作者：在成都搬磚的鴨鴨

這篇文章主要介紹了MySQL中的表連接原理分析,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

1、背景

在進行sql查詢時有時需要多張表的查詢結果組成一個共同的結果返回，這時就用到了mysql中連接的用法，接下來就以兩張表來講解表連接的原理。

2、環(huán)境

創(chuàng)建兩張表并插入數(shù)據(jù)如下：

mysql> select * from testjoin1;
+----+------+------+
| id | str1 | num1 |
+----+------+------+
|  1 | aaa  |  111 |
|  2 | bbb  |  222 |
|  3 | ccc  |  333 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select * from testjoin2;
+----+------+------+
| id | str2 | num2 |
+----+------+------+
|  1 | bbb  |  333 |
|  2 | ccc  |  444 |
|  3 | ddd  |  555 |
+----+------+------+
3 rows in set (0.00 sec)

3、表連接原理

【1】驅動表和被驅動表

兩張表連接查詢過程為：

1、先確定第一張要查詢的表得到第一張表的查詢結果，
2、第一張表的查詢結果作為第二張表的查詢條件進行查詢得到最終查詢結果。

其中第一張表叫驅動表，第二張表叫被驅動表，先看一下最基本連接查詢的例子：

mysql> select * from testjoin1, testjoin2;
+----+------+------+----+------+------+
| id | str1 | num1 | id | str2 | num2 |
+----+------+------+----+------+------+
|  3 | ccc  |  333 |  1 | bbb  |  333 |
|  2 | bbb  |  222 |  1 | bbb  |  333 |
|  1 | aaa  |  111 |  1 | bbb  |  333 |
|  3 | ccc  |  333 |  2 | ccc  |  444 |
|  2 | bbb  |  222 |  2 | ccc  |  444 |
|  1 | aaa  |  111 |  2 | ccc  |  444 |
|  3 | ccc  |  333 |  3 | ddd  |  555 |
|  2 | bbb  |  222 |  3 | ddd  |  555 |
|  1 | aaa  |  111 |  3 | ddd  |  555 |
+----+------+------+----+------+------+
9 rows in set (0.00 sec)

再看一下執(zhí)行計劃：

mysql> explain select * from testjoin1, testjoin2;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------
---------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra
               |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------
---------------+
|  1 | SIMPLE      | testjoin1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL
               |
|  1 | SIMPLE      | testjoin2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using join buff
er (hash join) |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------
---------------+
2 rows in set, 1 warning (0.00 sec)

可以看到執(zhí)行計劃輸出了兩條，第一條代表testjoin1表是驅動表， testjoin2表是被驅動表。

【2】內連接

驅動表的查詢結果作為查詢條件但沒有在被驅動表中匹配到結果時，這條記錄就不會加入到最終結果集中，這種連接方式就叫內連接，例如：

mysql> select * from testjoin1 inner join testjoin2 on str1=str2;
+----+------+------+----+------+------+
| id | str1 | num1 | id | str2 | num2 |
+----+------+------+----+------+------+
|  2 | bbb  |  222 |  1 | bbb  |  333 |
|  3 | ccc  |  333 |  2 | ccc  |  444 |
+----+------+------+----+------+------+
2 rows in set (0.00 sec)

可以看到testjoin1表中str1為aaa的記錄就不在查詢結果中，用圖紅色部分表示：

在這里插入圖片描述

【3】外連接

與內連接相對應的就是外連接，外連接中驅動表的查詢結果作為查詢條件即使沒有在被驅動表中查到，也會展示在最終結果集中，外連接分為左外連接和右外連接，左外連接就是將左邊的表作為驅動表，左外連接查詢如下：

mysql> select * from testjoin1 left join testjoin2 on str1=str2;
+----+------+------+------+------+------+
| id | str1 | num1 | id   | str2 | num2 |
+----+------+------+------+------+------+
|  1 | aaa  |  111 | NULL | NULL | NULL |
|  2 | bbb  |  222 |    1 | bbb  |  333 |
|  3 | ccc  |  333 |    2 | ccc  |  444 |
+----+------+------+------+------+------+
3 rows in set (0.00 sec)

用圖紫色部分表示：

在這里插入圖片描述

右外連接就是將右邊的表作為驅動表，右外連接查詢如下：

mysql> select * from testjoin1 right join testjoin2 on str1=str2;
+------+------+------+----+------+------+
| id   | str1 | num1 | id | str2 | num2 |
+------+------+------+----+------+------+
|    2 | bbb  |  222 |  1 | bbb  |  333 |
|    3 | ccc  |  333 |  2 | ccc  |  444 |
| NULL | NULL | NULL |  3 | ddd  |  555 |
+------+------+------+----+------+------+
3 rows in set (0.00 sec)

用圖綠色部分表示：

在這里插入圖片描述

【4】嵌套循環(huán)連接

驅動表只會被訪問一次，被驅動表可能被訪問多次，取決于從驅動表中得到的結果，這種連接執(zhí)行方式就叫嵌套循環(huán)連接。

【5】join buffer

mysql中的查表過程就是把數(shù)據(jù)從磁盤中加載到內存進行比較查詢，加載后面的記錄時會釋放內存中前面已經使用過的記錄，我們上面說過被驅動表可能會被訪問很多次，每次都從磁盤重新加載數(shù)據(jù)到內存無疑會增加開銷，所以提出了join buffer，也就是存儲驅動表的所有查詢結過，然后只執(zhí)行一次將被驅動表從磁盤加載到內存中，在內存中計算得到最終查詢結果，前面測試連接的explain語句中就可以看到被驅動表的Extra字段中有Using join buffer。