自学内容网 自学内容网
自学内容网 > 正文

MySQL45讲 第三十四讲 到底可不可以使用join？——阅读总结

🕗 发布于 2024-12-14 04:12 mysql  数据库  

MySQL45讲 第三十四讲 到底可不可以使用join？——阅读总结

使用join语句作两个表的联合是一把双刃剑，问题主要有以下两个：

  1. 我们DBA不让使用join，使用join有什么问题呢？
  2. 如果有两个大小不同的表做join，应该用哪个表做驱动表呢？

要回答这两个问题，首先要搞明白join语句的执行过程。

一、Join 语句执行过程分析

建立两个表t1，t2如下所示，两个表都有一个主键索引id和一个索引a，字段b上无索引。存储过程idata()往表t2 里插入了1000行数据，在表t1里插入的是100行数据。

CREATE TABLE `t2` (
`id` int(11) NOTNULL,
`a` int(11) DEFAULTNULL,
`b` int(11) DEFAULTNULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

（一） 不使用join的情况下查询

如果不使用join语句直接进行查询，流畅如下：

  1. 执行select * from t1，查出表t1的所有数据，这里有100行；
  2. 循环遍历这100行数据： 从每一行R取出字段a的值$R.a；
  3. 执行select * from t2 where a=$R.a；
  4. 把返回的结果和R构成结果集的一行。

以上过程扫描了200行数据，执行了101条语句，比使用join多了100次交互。

（二）Index Nested - Loop Join（NLJ）

  1. 执行流程

    • 以select * from t1 straight_join t2 on (t1.a = t2.a);为例，t1 为驱动表，t2 为被驱动表。先从表 t1 中读入一行数据 R。
    • 从数据行 R 中取出 a 字段到表 t2 里，利用 t2 字段 a 上的索引进行查找。
    • 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分。
    • 重复上述步骤，直到表 t1 遍历结束。

    在这里插入图片描述

  2. 扫描行数与复杂度

    • 对驱动表 t1 做全表扫描，需扫描 100 行。
    • 对于每一行 R，在被驱动表 t2 中通过索引查找，每次搜索近似复杂度为以 2 为底 M（被驱动表行数）的对数，记为 log₂M，此例中每次搜索一行，共扫描 100 行。所以整个执行流程总扫描行数是 200 行，执行过程近似复杂度为 N（驱动表行数）+ N * 2 * log₂M。

    在这里插入图片描述

（三）Simple Nested-Loop Join（SNL）

如果一个表有索引，另外一个表没有索引，执行join又是什么样的结果呢？

把SQL语句改成这样：select * from t1 straight_join t2 on (t1.a=t2.b); 由于表t2的字段b上没有索引，每次到t2去匹配的时候，就要做一次全表扫描 ，可以这么理解：N扩大1000倍的话，扫描行数就会扩大 1000倍；而M扩大1000倍，扫描行数扩大不到10倍。 如果t1和t2都是10万行的表（当然了，这也还是属于小表的范围），就要扫 描100亿行，可能你的客户端就卡住不动了。但MySQL不会使用SNL算法，使用的是下面说的BNL算法。

（三）Block Nested - Loop Join（BNL）

当一个表有索引另外一个表没有索引时，MySQL执行join语句的流程如下：

  1. 执行流程

    • 把表 t1 的数据读入线程内存 join_buffer 中（此例中因select *将整个表 t1 放入内存）。
    • 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的作为结果集一部分返回。

    在这里插入图片描述

  2. 扫描行数与复杂度

    • 对表 t1 和 t2 都做一次全表扫描，若表 t1 有 100 行，表 t2 有 1000 行，则总扫描行数是 1100 行。
    • 内存中的判断次数为 M * N（M 为大表行数，N 为小表行数）。若 join_buffer 放不下表 t1 数据，会分段放，如将表 t1 分成 K 段（K = λ * N，λ 取值范围 (0,1)），此时扫描行数为 N + λ * N * M，内存判断次数仍为 M * N。、
    • 与SNL不同的是，BNL在内存中操作，速度会快很多。

    在这里插入图片描述

也会出现join_buffer不够大的情况，将会自动分块执行，流程如下：

  1. 扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；
  2. 扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结 果集的一部分返回；
  3. 清空join_buffer；
  4. 继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。

在这里插入图片描述

二、关于 Join 语句的常见问题解答

（一）能否使用 Join 语句？

  1. 可以使用 Index Nested - Loop Join 算法时
    • 性能比强行拆成多个单表执行 SQL 语句要好。如上述 NLJ 例子，直接 join 扫描行数少且执行语句次数少，避免了客户端多次交互和拼接 SQL 语句及结果的繁琐。
  2. 只能使用 Block Nested - Loop Join 算法时
    • 扫描行数可能过多，尤其在大表 join 操作中，会占用大量系统资源，这种情况下尽量不要使用。判断依据是查看 explain 结果中 Extra 字段有无 “Block Nested Loop” 字样。

（二）如何选择驱动表？

  1. Index Nested - Loop Join 算法

    • 应选择小表做驱动表，因为驱动表行数 N 对扫描行数影响更大，N 扩大 1000 倍，扫描行数扩大 1000 倍；而被驱动表行数 M 扩大 1000 倍，扫描行数扩大不到 10 倍。

  2. Block Nested - Loop Join 算法

    • 当 **join_buffer_size 足够大时，选择大表或小表做驱动表执行耗时一样；**当 **join_buffer_size 不够大（更常见）**时，应选择小表做驱动表。准确来说，是两个表按各自条件过滤后，计算参与 join 的各个字段总数据量，数据量小的表为 “小表”，应作为驱动表。

三、总结与思考

通过对 MySQL 执行 Join 语句的两种算法分析，我们明确了 Join 语句在不同场景下的使用策略。在实际应用中，要根据能否使用被驱动表索引来判断 Join 语句的可行性，并合理选择驱动表以优化查询性能：

  1. 如果可以使用被驱动表的索引，join语句还是有其优势的；
  2. 不能使用被驱动表的索引，只能使用Block Nested-Loop Join算法，这样的语句就尽量不要使用；
  3. 在使用join的时候，应该让小表做驱动表。

原文地址：https://blog.csdn.net/KELLENSHAW/article/details/144431678

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024