背景
最近有很多业务查询需要用到连表join，由于数据量都不是很大，而且肉眼可见的生命周期内，也没有需要分布式分库分表的可能性，故而仍然直接使用了join语句进行数据查询，但是在我印象中，出现过很多不建议使用join的声音，大概都出于两个原因，
-
Join涉及连表，查询效率低 -
Join不利于分布式的数据库/表设计
因此，想深入探讨下，join对于数据查询中的可行性
Join原理
- 名词说明
- 驱动表：连表中先查询的表，通常是
from后的那个表 - 被驱动表：被连接的表，通常是
join后的表
select * from table1 left join table2 where table1.id = table2.t_id- 上述
table1为驱动表，table2为被驱动表
-
Index Nested-Loop Join，简称NLJ，一种数据连接方式 Simple Nested-Loop Join-
Blocked Nested-Loop Join，简称BNL -
Join Buffer:Join查询缓存，使用连表查询中可能用到 -
Multi-Range Read, 简称MRR -
Batch-Key Access, 简称BKA
Index Nested-Loop Join
Index Nested-Loop Join表示，在数据表连接查询时，使用到被驱动表的索引，那么此时连表查询的方式就是NLJ
查询方式
以语句：SELECT * FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col2为例，如果被驱动表table2的col2列存在索引，那么此时的连表查询方式为NLJ，算法执行过程如下：
- 从表
table1中取出一行数据Row1 - 从数据行
Row1中，取出列col1，然后拿到table2中查询 - 使用
table2.col2上的索引，获取table2.col2 = table1.col1的数据行Row2 - 将
Row1和Row2组成新行，作为结果集的一行 - 重复上述 1-4
可以看到，这里table1做了全表扫描，table2使用了col2列上的索引。假设table1，table2的行数分表为M、N, 那么此次查询次数为**M + M * log2N**
驱动表的行数M，更大的影响查询时间复杂度，因此在JOIN查询时，应尽量使用数据行较小的表做驱动表，同时需要注意，这里较小的表并不是全表行数最小，而是在where条件下过滤后的行数最小
Simple Nested-Loop Join
以SELECT * FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col2为例，如果table2.col2字段上没有索引，那么此时连表查询的方式为Simple Nested-Loop Join，查询次数为M * N
Mysql 已经不会使用这种连接查询算法，当遇到table2.col2上无索引时，使用Blocked Nested-Loop Join查询算法
Blocked Nested-Loop Join
Join Buffer
Join Buffer是Mysql Buffer Pool中一段用于连接查询的内存区域，用于存放连接查询过程中的临时数据. Join Buffer默认大小为join_buffer_size = 262144
mysql> show variables like "join%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+查询方式
以SELECT * FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col2为例，算法执行过程如下：
- 查询把
table1中所有数据，并放入Join Buffer - 扫描表
table2，取出每行数据和Join Buffer中table1的每行数据做对比，满足条件的作为结果集一部分
此时查询次数为M + N，但是计算次数依然是M * N，和Simple Nested-Loop Join不同的是，BNL的查询次数(磁盘IO)较小，且计算是在内存中，速度更快。
需要注意，如果table1中的数据量大于join buffer size，此时需要分块查询（也正是Blocked的真正含义），每次加载table1的一个block放进内存，然后扫描一遍table2的全部数据。最终查询此时M + K * N(其中K为分块的次数)，计算的次数依然为M * N
Multi-Range Read
Multi-Range Read(MRR)是一种回表查询优化手段，利用read_rnd_buffer(随机读缓冲区)，是批量查询尽量顺序读磁盘
read_rnd_buffer
MySQL随机读缓存区，将批量有序的查询加载到随机读缓冲区中，然后排序后进行查询。若按照主键的递增顺序查询，通过随机缓冲区，可以将原本随机读变成顺序读，提高查询速度。
mysql> show variables like "read_rnd%";
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |使用MRR的查询步骤
若我们有一个批量查询 SELECT * FROM table1 WHERE col1 < 1000
没有使用MRR时的查询步骤是这样的：
- 在
col1索引上，查找到第一条满足条件的记录，获取主键id - 拿此主键id到主键索引上，查询得到一行完整数据（回表），作为结果集的一行
- 循环步骤1-2
使用MRR优化后的查询步骤:
- 遍历
col1索引，查出所有满足条件的主键id，放入rand_rnd_buffer - 在
read_rnd_buffer中对id进行排序 - 根据排序后的数组，再依次到主键索引中获取行记录
可以看到，使用MRR后，将原先回表操作的随机读，改成顺序读，提高了查询性能
Mysql: 5.7.30已默认开启MRR
mysql> show variables like "%switch%";
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |Batched Key Access
Batched-Key Access (BKA)算法即是利用MRR的思想，对NLJ的优化
上面关于NLJ的段落中，说明了在Index Nested-Loop Join的算法中，会先从table1中拿出一列，然后去table2查询该列，合并列数据作为结果集的一行，然后循环此过程。这个过程中，对table2的查询时分次查询，随机读
开启BKA后，查询过程变化如下：
- 从
table1中获取所有数据，并将其放到内存中 (BKA算法使用Join Buffer作为内存缓存)
- 注意，如果
Join Buffer一次装不下，依然会分批加载
- 在
Join Buffer中，获取所有col1列数值，并排序 - 根据排序后的值，去
table2中查询列数据
- 这就将原本去
table2的随机读，变成顺序读操作
Mysql: 5.7.30好像未默认开始BKA
batched_key_access=offInner/Left/Right Join
介绍几中JOIN的概念
笛卡尔积
要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。
Inner Join
内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集，从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录
Left Join
左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录
Right Join
右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上右表中剩余的记录
思考
综上，个人得出一些浅显的结论：
- 对于小表（几千行、小万行），可以直接使用Join
- 对于大表，使用Join需要注意：
- 使用小表作驱动表
- 被驱动表必须使用索引
- 使用
Join前，先explain查看Join查询的性能
参考：
