MySql语法（5）—join连接

1. Join语法

实例表表A和表B.png

内连接：只返回满足条件的数据

INNER JOIN语法：

select * from A INNER JOIN B ON A.A1=B.B1

返回的数据是：

image.png

外连接：外连接又分为左外连接和右外连接，选择某个表为基表，基表数据全显示。

左外连接语法：

select * from A LEFT JOIN B ON A.A1=B.B1;

上面的语句最终得到的连接顺序：AB，然后以左表为基表。只返回满足左表的数据，若右表无数据，那么返回null。

左外连接.png

右外连接语法：

select * from A RIGHT JOIN B ON A.A1=B.B1;

上面的语句最终得到的连接顺序：AB，然后以右边为基表。值返回满足右边的数据，若左边无数据，那么返回null。

右外连接.png

2. Join连接的算法

Mysql的联表算法是基于嵌套循环算法（nested-loop algorithm）而衍生出来的一系列算法，根据不同条件而选用不同的算法

2.1 使用索引

驱动表（小表）循环拿出一条数据，那么去被驱动表（大表）中进行索引查询。

例：t_img_log表为大表，t_req_log表为小表。

EXPLAIN SELECT * from t_img_log c1 INNER JOIN t_req_log c2 ON c1.req_id=c2.req_id

+----+-------------+-------+------------+------+---------------+-----------+---------+-----------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref             | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-----------------+------+----------+-----------------------+
|  1 | SIMPLE      | c2    | NULL       | ALL  | index_id      | NULL      | NULL    | NULL            | 1861 |   100.00 | NULL                  |
|  1 | SIMPLE      | c1    | NULL       | ref  | key_index     | key_index | 153     | exam.c2.req_key |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-----------------+------+----------+-----------------------+

2.2 未使用索引

在未使用索引的情况下，有Simple Nested-Loop join 和 Block Nested-Loop join 两种算法;

Simple Nested-Loop join 简单嵌套算法

一次从一个循环中的第一个表中读取行，并将每行传递到一个嵌套循环中，该循环处理联接中的下一个表。重复此过程的次数与要连接的表的次数相同。

这种算法简单粗暴(n*m)，但毫无性能可言。mysql不会采用这种算法。

Block Nested-Loop 缓存块嵌套算法

一次性缓存多条驱动表的数据到join buffer（缓存块），然后拿join buffer里的数据批量和被驱动表的数据进行匹配。

例如：驱动表结果集是100行，使用NLJ算法需要扫描被驱动表100次，如果使用BLJ算法，先把驱动表每次读取10行记录放到join buffer，然后在被驱动表中直接匹配这10行记录，内存循环就可以和这10行匹配，这样就需要匹配10次，对被驱动表的扫描减少了9/10。索引BNJ算法能够显著减少内层循环表扫描的次数。

当被驱动表上无索引且被驱动表在where过滤条件上也没索引时，常常会借助这种算法来完成联表。

mysql> show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY  |            1 | id          | A         |     4170338 | NULL     | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
 
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |      261958 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
 
mysql> explain select sql_no_cache  * from user  join article on(user.name = article.name) where article.name like 'user_%';
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                              |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------------+
|  1 | SIMPLE      | user    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  261958 |   100.00 | NULL                                               |
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4170338 |     1.11 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------------+
2 rows in set (0.00 sec)