Mysql--JOIN连表查询

一、Join查询原理

MySQL内部采用了一种叫做 nested loop join（嵌套循环连接）的算法：通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复，基本上MySQL采用的是最容易理解的算法来实现join

所以驱动表的选择非常重要，驱动表的数据小可以显著降低扫描的行。

一般情况下参与联合查询的两张表都会一大一小，如果是join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表。简单来说，驱动表就是主表，left join 中的左表就是驱动表，right join 中的右表是驱动表。

 

 

 

二、Nested-Loop Join

如 select * from t1 inner join t2 on t1.id=t2.tid ，t1称为外层表，也可称为驱动表，t2称为内层表，也可称为被驱动表

mysql只支持一种join算法：Nested-Loop Join（嵌套循环连接），但Nested-Loop Join有三种变种：

• 简单嵌套循环连接：Simple Nested-Loop Join(SNLJ)
• 索引嵌套循环连接：Index Nested-Loop Join(INLJ)
• 缓存块嵌套循环连接：Block Nested-Loop Join(BNLJ)

在选择Join算法时，会有优先级，理论上会优先判断能否使用INLJ、BNLJ： Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join 

 

2.1 Simple Nested-Loop Join

如下图，r为驱动表，s为匹配表，可以看到从r中分别取出r1、r2、…、rn去匹配s表的左右列，然后再合并数据，对s表进行了rn次访问，对数据库开销大。

如果table1有1万条数据，table2有1万条数据，那么数据比较的次数=1万 * 1万 =1亿次，这种查询效率会非常慢。

故基本不使用这种方式，mysql会根据情况选择其他两种方式进行查询

 

 

2.2 Index Nested-Loop Join(减少内层表数据的匹配次数)

• 索引嵌套循环连接是基于索引进行连接的算法，索引是基于内层表的，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较， 从而利用索引的查询减少了对内层表的匹配次数，优势极大的提升了 join的性能
• 使用场景：只有内层表join的列有索引时，才能用到Index Nested-LoopJoin进行连接
• 由于用到索引，如果索引是辅助索引而且返回的数据还包括内层表的其他数据，则会回内层表查询数据，多了一些IO操作

这个要求非驱动表（匹配表s）上有索引，可以通过索引来减少比较，加速查询。

在查询时，驱动表（r）会根据关联字段的索引进行查找，当在索引上找到符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。

如果非驱动表（s）的关联健是主键的话，性能会非常高，如果不是主键，要进行多次回表查询，先关联索引，然后根据二级索引的主键ID进行回表操作，性能上比索引是主键要慢。

 

 

2.3 Block Nested-Loop Join(减少内层表数据的循环次数)

缓存块嵌套循环连接通过一次性缓存多条数据，把参与查询的列缓存到Join Buffer 里，然后拿join buffer里的数据批量与内层表的数据进行匹配，从而减少了内层循环的次数（遍历一次内层表就可以批量匹配一次Join Buffer里面的外层表数据）。

当不使用Index Nested-Loop Join的时候(内层表查询不适用索引)，默认使用Block Nested-Loop Join

1. Join Buffer会缓存所有参与查询的列而不是只有Join的列
2. 可以通过调整join_buffer_size缓存大小
3. join_buffer_size的默认值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1，而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间
4. 使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启

 

 

三 优化

1. 用小结果集驱动大结果集，减少外层循环的数据量：如果小结果集和大结果集连接的列都是索引列，mysql在内连接时也会选择用小结果集驱动大结果集，因为索引查询的成本是比较固定的，这时候外层的循环越少，join的速度便越快
2. 为匹配的条件增加索引：争取使用INLJ，减少内层表的循环次数
3. 增大join buffer size的大小：当使用BNLJ时，一次缓存的数据越多，那么外层表循环的次数就越少
4. 减少不必要的字段查询：
   • 当用到BNLJ时，字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少
   • 当用到INLJ时，如果可以不回表查询，即利用到覆盖索引，则可能可以提示速度
5. 尽量使用inner join,避免left join 和NULL

 

摘抄自(有删改)：https://blog.csdn.net/agonie201218/article/details/106993948

 

 

四 补充

4.1 找出所有在左表，不在右表的纪录

注：列值为null应该用is null 而不能用=NULL

　　a.user_id 列必须声明为 NOT NULL 的

select id, name, action from user as u left join user_action a on u.id = a.user_id where a.user_id is NULL

 

4.2 where使用

在连表查询时，通常在子查询中使用 WHERE 子句限制表数据会比在最后的主查询中使用 WHERE 子句效率更高。

原因是，在子查询中使用 WHERE 子句可以对较小的结果集进行过滤，减少了主查询需要处理的数据量。这样可以减少数据的传输和处理的负担，提高查询性能。

另一方面，在最后的主查询中使用 WHERE 子句时，会先执行联接操作并获取所有的匹配行，然后再对整个结果集进行过滤。这样会导致在查询过程中处理了更多的数据，增加了查询的开销，可能导致性能下降。

因此，将限制条件放在子查询中，可以尽早地过滤数据，只将需要的数据传递到后续的查询阶段，减少了不必要的计算和数据传输，更加高效。

SELECT A.*
FROM 大表A A
JOIN (SELECT * FROM 小表B WHERE C) AS B ON A.关联键 = B.关联键;

 

4.3 join连表查询示例

创建两个表，数据如下：Jer有同名，分布在不同班级；Tom是转校生，暂未分配班级

MySQL [sredev]> select * from table1;
+----+-------+------+--------+
| id | name  | age  | gender |
+----+-------+------+--------+
|  1 | John  |   25 | Male   |
|  2 | Alice |   30 | Female |
|  3 | Bob   |   28 | Male   |
|  4 | Jer   |   26 | Famale |
|  5 | Jer   |   23 | Male   |
|  6 | Tom   |   26 | Famale |
+----+-------+------+--------+
6 rows in set (0.00 sec)

MySQL [sredev]> select * from table2;
+----+-------+---------+-------------+
| id | name  | class   | total_score |
+----+-------+---------+-------------+
|  1 | John  | Class A |          90 |
|  2 | Alice | Class B |          85 |
|  3 | Bob   | Class A |          92 |
|  4 | jer   | Class C |          99 |
|  5 | Jer   | Class A |          96 |
+----+-------+---------+-------------+
5 rows in set (0.00 sec)

MySQL [sredev]> 

 

left join

此时左表显示所有，右表显示匹配的记录

注：当左表有多余的记录无法匹配右表时，也会显示，不匹配的字段用NULL填充；且此示例使用大表去关联小表，实际运用中，还是尽量用小表去关联大表

MySQL [sredev]> select * from table1 a left join table2 b on a.id = b.id;
+----+-------+------+--------+------+-------+---------+-------------+
| id | name  | age  | gender | id   | name  | class   | total_score |
+----+-------+------+--------+------+-------+---------+-------------+
|  1 | John  |   25 | Male   |    1 | John  | Class A |          90 |
|  2 | Alice |   30 | Female |    2 | Alice | Class B |          85 |
|  3 | Bob   |   28 | Male   |    3 | Bob   | Class A |          92 |
|  4 | Jer   |   26 | Famale |    4 | jer   | Class C |          99 |
|  5 | Jer   |   23 | Male   |    5 | Jer   | Class A |          96 |
|  6 | Tom   |   26 | Famale | NULL | NULL  | NULL    |        NULL |
+----+-------+------+--------+------+-------+---------+-------------+
6 rows in set (0.01 sec)

MySQL [sredev]> select a.name,a.age,b.class,b.total_score from table1 a left join table2 b on a.id = b.id;
+-------+------+---------+-------------+
| name  | age  | class   | total_score |
+-------+------+---------+-------------+
| John  |   25 | Class A |          90 |
| Alice |   30 | Class B |          85 |
| Bob   |   28 | Class A |          92 |
| Jer   |   26 | Class C |          99 |
| Jer   |   23 | Class A |          96 |
| Tom   |   26 | NULL    |        NULL |
+-------+------+---------+-------------+
6 rows in set (0.00 sec)

MySQL [sredev]> 

尽量选择数据唯一的字段做关联条件，不然数据容易重复；比如用有重复数据的name字段关联的话，Jer会匹配多次，导致最终数据出错：

MySQL [sredev]> select b.name,b.class from table1 a left join table2 b on a.name = b.name;
+-------+---------+
| name  | class   |
+-------+---------+
| John  | Class A |
| Alice | Class B |
| Bob   | Class A |
| jer   | Class C |
| jer   | Class C |
| Jer   | Class A |
| Jer   | Class A |
| NULL  | NULL    |
+-------+---------+
8 rows in set (0.00 sec)

MySQL [sredev]> select a.name,b.class from table1 a left join table2 b on a.name = b.name;
+-------+---------+
| name  | class   |
+-------+---------+
| John  | Class A |
| Alice | Class B |
| Bob   | Class A |
| Jer   | Class C |
| Jer   | Class C |
| Jer   | Class A |
| Jer   | Class A |
| Tom   | NULL    |
+-------+---------+
8 rows in set (0.00 sec)

MySQL [sredev]> select b.name,b.class from table1 a left join table2 b on a.name = b.name;
+-------+---------+
| name  | class   |
+-------+---------+
| John  | Class A |
| Alice | Class B |
| Bob   | Class A |
| jer   | Class C |
| jer   | Class C |
| Jer   | Class A |
| Jer   | Class A |
| NULL  | NULL    |
+-------+---------+
8 rows in set (0.00 sec)

MySQL [sredev]> 

 

inner join

可简写为join，此时显示两表共有的数据：

MySQL [sredev]> select * from table1 a inner join table2 b on a.id = b.id;
+----+-------+------+--------+----+-------+---------+-------------+
| id | name  | age  | gender | id | name  | class   | total_score |
+----+-------+------+--------+----+-------+---------+-------------+
|  1 | John  |   25 | Male   |  1 | John  | Class A |          90 |
|  2 | Alice |   30 | Female |  2 | Alice | Class B |          85 |
|  3 | Bob   |   28 | Male   |  3 | Bob   | Class A |          92 |
|  4 | Jer   |   26 | Famale |  4 | jer   | Class C |          99 |
|  5 | Jer   |   23 | Male   |  5 | Jer   | Class A |          96 |
+----+-------+------+--------+----+-------+---------+-------------+
5 rows in set (0.01 sec)

MySQL [sredev]> select a.name,b.class from table1 a join table2 b on a.id = b.id;
+-------+---------+
| name  | class   |
+-------+---------+
| John  | Class A |
| Alice | Class B |
| Bob   | Class A |
| Jer   | Class C |
| Jer   | Class A |
+-------+---------+
5 rows in set (0.00 sec)

MySQL [sredev]> 

使用带重复数据的字段作判断条件，结果依旧有重复数据：

MySQL [sredev]> select * from table1 a inner join table2 b on a.name = b.name;
+----+-------+------+--------+----+-------+---------+-------------+
| id | name  | age  | gender | id | name  | class   | total_score |
+----+-------+------+--------+----+-------+---------+-------------+
|  1 | John  |   25 | Male   |  1 | John  | Class A |          90 |
|  2 | Alice |   30 | Female |  2 | Alice | Class B |          85 |
|  3 | Bob   |   28 | Male   |  3 | Bob   | Class A |          92 |
|  4 | Jer   |   26 | Famale |  4 | jer   | Class C |          99 |
|  4 | Jer   |   26 | Famale |  5 | Jer   | Class A |          96 |
|  5 | Jer   |   23 | Male   |  4 | jer   | Class C |          99 |
|  5 | Jer   |   23 | Male   |  5 | Jer   | Class A |          96 |
+----+-------+------+--------+----+-------+---------+-------------+
7 rows in set (0.00 sec)

MySQL [sredev]> select a.name,b.class from table1 a inner join table2 b on a.name = b.name;
+-------+---------+
| name  | class   |
+-------+---------+
| John  | Class A |
| Alice | Class B |
| Bob   | Class A |
| Jer   | Class C |
| Jer   | Class A |
| Jer   | Class C |
| Jer   | Class A |
+-------+---------+
7 rows in set (0.00 sec)

MySQL [sredev]> 

 

right join

与left join相反，右表显示所有，左表只显示匹配的记录：

MySQL [sredev]> select * from table1 a right join table2 b on a.id = b.id;
+------+-------+------+--------+----+-------+---------+-------------+
| id   | name  | age  | gender | id | name  | class   | total_score |
+------+-------+------+--------+----+-------+---------+-------------+
|    1 | John  |   25 | Male   |  1 | John  | Class A |          90 |
|    2 | Alice |   30 | Female |  2 | Alice | Class B |          85 |
|    3 | Bob   |   28 | Male   |  3 | Bob   | Class A |          92 |
|    4 | Jer   |   26 | Famale |  4 | jer   | Class C |          99 |
|    5 | Jer   |   23 | Male   |  5 | Jer   | Class A |          96 |
+------+-------+------+--------+----+-------+---------+-------------+
5 rows in set (0.00 sec)

MySQL [sredev]> select a.name,b.class from table1 a right join table2 b on a.id = b.id;
+-------+---------+
| name  | class   |
+-------+---------+
| John  | Class A |
| Alice | Class B |
| Bob   | Class A |
| Jer   | Class C |
| Jer   | Class A |
+-------+---------+
5 rows in set (0.00 sec)

MySQL [sredev]> 

name字段用作判断条件依旧有重复数据：

MySQL [sredev]> select * from table1 a right join table2 b on a.name = b.name;
+------+-------+------+--------+----+-------+---------+-------------+
| id   | name  | age  | gender | id | name  | class   | total_score |
+------+-------+------+--------+----+-------+---------+-------------+
|    1 | John  |   25 | Male   |  1 | John  | Class A |          90 |
|    2 | Alice |   30 | Female |  2 | Alice | Class B |          85 |
|    3 | Bob   |   28 | Male   |  3 | Bob   | Class A |          92 |
|    4 | Jer   |   26 | Famale |  4 | jer   | Class C |          99 |
|    4 | Jer   |   26 | Famale |  5 | Jer   | Class A |          96 |
|    5 | Jer   |   23 | Male   |  4 | jer   | Class C |          99 |
|    5 | Jer   |   23 | Male   |  5 | Jer   | Class A |          96 |
+------+-------+------+--------+----+-------+---------+-------------+
7 rows in set (0.00 sec)

MySQL [sredev]> select a.name,b.class from table1 a right join table2 b on a.name = b.name;
+-------+---------+
| name  | class   |
+-------+---------+
| John  | Class A |
| Alice | Class B |
| Bob   | Class A |
| Jer   | Class C |
| Jer   | Class A |
| Jer   | Class C |
| Jer   | Class A |
+-------+---------+
7 rows in set (0.00 sec)

MySQL [sredev]>