1. Join语法
- 内连接：只返回满足条件的数据
INNER JOIN语法：
select * from A INNER JOIN B ON A.A1=B.B1
返回的数据是：
- 外连接：外连接又分为左外连接和右外连接，选择某个表为基表，基表数据全显示。
左外连接语法：
select * from A LEFT JOIN B ON A.A1=B.B1;
上面的语句最终得到的连接顺序：AB，然后以左表为基表。只返回满足左表的数据，若右表无数据，那么返回null。
右外连接语法：
select * from A RIGHT JOIN B ON A.A1=B.B1;
上面的语句最终得到的连接顺序：AB，然后以右边为基表。值返回满足右边的数据，若左边无数据，那么返回null。
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)
Index Nested-Loop
索引嵌套循环，是基于被驱动表的索引进行连接的算法；驱动表的记录逐条与被驱动表进行匹配，避免和被驱动表的每条记录进行比较，减少了对驱动表的匹配次数。
2.3 join_buffer_size的设置
join_buffer_size即join连接的缓存块。
相关的命令：
# join连接的缓存块
SELECT @@join_buffer_size;
# sort排序的缓存块
SELECT @@sort_buffer_size;
如果应用中，很少出现join语句，则可以不用太在乎join_buffer_size参数的设置大小。如果join语句不是很少的话，个人建议可以适当增大join_buffer_size到1MB左右，如果内存充足可以设置为2MB...
MySQL使用Join Buffer有以下要点:
- join_buffer_size变量决定buffer大小。
- 只有在join类型为all, index, range的时候才可以使用join buffer。
- 能够被buffer的每一个join都会分配一个buffer, 也就是说一个query最终可能会使用多个join buffer。
- 第一个nonconst table不会分配join buffer, 即便其扫描类型是all或者index。
- 在join之前就会分配join buffer, 在query执行完毕即释放。
- join buffer中只会保存参与join的列, 并非整个数据行。
3. 规范：不推荐使用复杂的多表查询
在《高性能mysql第三版》第6.3章查询性能优化中是这样描述的：根据实际情况，看看是否有必要将一个复杂的查询分解成多个简单的查询，并不一定将所有的工作全部交给数据库（Join拆解的核心就是利用In关键字）。
mysql连接和断开都是很轻量级，返回一个小的查询结果方面很高效。所以运行多个小查询不是存在性能瓶颈，数据库的优化器性能比较差，涉及到多个表的查询，往往得不到很好的查询计划。
而将join拆分为多个单表连接优点：
- 利于缓存：应用系统可以方便的缓存单笔查询对应的结果；
- 减少竞争：将查询分解后，执行单个查询可以减少锁的竞争；
- 易于扩展：更加容易对数据库进行拆分；
- 性能提升：使用in()来代替关联查询，可以让mysql按照ID顺序进行查询，这可能比随机的关联查询更加高效；
- 减少冗余：在应用层做关联查询，意味着某些记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。
在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景有很多，比如：当应用能够方便的缓存当个查询的结果的时候、当可以将数据分布到不同的mysql服务器上的时候、当能够使用IN()的方式代替关联查询的时候、当查询中使用的是同一个数据表的时候。
join和in的区别
Mysql优化器会将in()中子查询优化为join方式。所以当相同的select结果下，in的效率会比join效率低。因为in子查询每次执行内部查询的时候都必须重新构造一个JOIN结构，并且还需要完成析构过程。
历史文章
JAVA && Spring && SpringBoot2.x — 学习目录
文章参考
MySQL性能优化之Block Nested-Loop Join(BNL)
