mysql内连接怎么选取驱动表 mysql join 驱动表

转载

码海航行侠 2023-10-24 00:02:14

文章标签 mysql内连接怎么选取驱动表 mysql 数据库索引数据 文章分类 MySQL 数据库

JOIN语句原理

join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

驱动表和被驱动表

驱动表就是主表，被驱动表就是从表、非驱动表

SELECT * FROM A JOIN B ON ...

A一定是驱动表吗？不一定，优化器会根据你查询语句做优化，决定先查哪张表。先查询的那张表就是驱动表，反之就是被驱动表。通过explain关键字可以查看。

左外连接加where作为筛选条件，查询优化器帮我做了优化，把b作为驱动表，a作为被驱动表

EXPLAIN SELECT * FROM a LEFT JOIN b ON
(a.`f1` = b.`f1`) WHERE a.`f2`=b.`f2`;

mysql内连接怎么选取驱动表 mysql join 驱动表_索引

左外连接加and，将and都作为on的条件，这也是和上面的where的区别。（这里查询优化器没有帮我们进行调整）

EXPLAIN SELECT * FROM a LEFT JOIN b ON
(a.`f1` = b.`f1`) AND a.`f2`=b.`f2`;

mysql内连接怎么选取驱动表 mysql join 驱动表_索引_02

采用内连接

EXPLAIN SELECT * FROM a  JOIN b ON
(a.`f1` = b.`f1`) WHERE a.`f2`=b.`f2`;

mysql内连接怎么选取驱动表 mysql join 驱动表_mysql内连接怎么选取驱动表_03

Simple Nested-Loop Join(简单嵌套循环匹配)

算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result…以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断：

mysql内连接怎么选取驱动表 mysql join 驱动表_mysql_04

可以看到这种方式效率是非常低的，以上述表A数据100条，表B数据1000条计算，则A*B= 10万次。开销统计如下：

开销统计	SNLJ
外表扫描次数：	1
内表扫描次数：	A
读取记录数：	A+B*A（小表驱动大表A越小越好）
JOIN比较次数：	B*A
回表读取记录次数:(因为不涉及到二级索引)	0

当然mysql肯定不会这么粗暴的去进行表的连接

Index Nested-Loop Join（嵌套循环连接）

Index Nested-Loop Join其优化思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表每层记录进行比较，这样极大的减少了对内层表的匹配次数。

mysql内连接怎么选取驱动表 mysql join 驱动表_mysql_05

驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向于使用记录数少的表作为驱动表(外表)。

开销统计	SNLJ 简单嵌套循环匹配	INLJ 嵌套循环连接
外表扫描次数	1	1
内表扫描次数	A	0
读取记录数	A+B*A	A+B(match)(match是匹配的次数)
JOIN比较次数	B*A	A*index(Height)(Hight是B+树的高度)
回表读取记录次数	0	B(match)(if possible主键索引不用回表，二级索引要回表)

如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表的索引是主键索引，效率会更高。

Block Nest-Loop Join（块嵌套循环连接）

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

注意：
这里缓存的不只是关联表的列，select后面的列也会缓存起来。(所有尽量少投影*)
在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。

mysql内连接怎么选取驱动表 mysql join 驱动表_索引_06

开销统计	SNLJ	INLJ	BNLJ
外表扫描次数	1	1	1
内表扫描次数	A	0	A * use_cloumn_size / join_buffer_size + 1
读取记录数	A+B*A	A+B(match)	A+B(A use_cloumn_size / join_buffer_size)
JOIN比较次数	B * A	A * index(Height)	B * A
回表读取记录次数	0	B(match)(if possible)	0

参数设置：

block_nested_loop

通过show variables like '%optimizer_switch' 查看block_nested_loop状态。默认是开启的。

show variables like '%optimizer_switch';

join_buffer

驱动表不能一次性加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256k

show variables like '%join_buffer%';

mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

join_buffer_size的最大值在32位系统可以申请4G，而在64位操做系统下可以申请大于4G的Join Buffer空间(64位Windows除外，其大值会被截断为4GB并发出警告)。

Join小结

整体效果比较：INLJ > BNLJ > SNLJ
永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) (小的度量单位指的是表行数*每行大小)(也就是要看过滤以后的结果集)

假设t2表的字段数量大于1，如果用第二的话，t2.*要比t1.b的字段要多，在join_buffer里面能加载到的条目数就减少了。

select t1.b, t2.* from t1 straight.join t2 on (t1.b=t2.b) where t2.id<=108;#推荐
select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;#不推荐

为被驱动表匹配的条件添加索引(减少内层循环匹配次数)
增加join buffer size的大小(一次缓存的数据越多，那么内层包的扫描次数就越少)
减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）

Hash Join

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

Nested Loop 对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。
Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。

这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能。
它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1=B.COL2)，这是由Hash的特点决定的。

类别	Nested Loop	Hash Join
使用索引条件	任何条件	等值条件（=）
相关资源	CPU、磁盘I/O	内存、临时空间
特点	当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果。	当缺乏索引或者索引条件模糊时，Hash Join比NestedLoop有效。在数据仓库环境下，如果表的纪录数多，效率高。
缺点	当索引丢失或者查询条件限制不够时，效率很低;当表的纪录数多时，效率低。	为建立哈希表，需要大量内存。第一次的结果返回较慢。