专栏首页Java识堂join 语句怎么优化?

join 语句怎么优化?

Simple Nested-Loop Join

我们来看一下当进行 join 操作时,mysql是如何工作的。常见的 join 方式有哪些?

如图,当我们进行连接操作时,左边的表是「驱动表」,右边的表是「被驱动表」

Simple Nested-Loop Join 这种连接操作是从驱动表中取出一条记录然后逐条匹配被驱动表的记录,如果条件匹配则将结果返回。然后接着取驱动表的下一条记录进行匹配,直到驱动表的数据全都匹配完毕

「因为每次从驱动表取数据比较耗时,所以MySQL并没有采用这种算法来进行连接操作」

Block Nested-Loop Join

既然每次从驱动表取数据比较耗时,那我们每次从驱动表取一批数据放到内存中,然后对这一批数据进行匹配操作。这批数据匹配完毕,再从驱动表中取一批数据放到内存中,直到驱动表的数据全都匹配完毕

批量取数据能减少很多IO操作,因此执行效率比较高,这种连接操作也被MySQL采用

对了,这块内存在MySQ中有一个专有的名词,叫做 join buffer,我们可以执行如下语句查看 join buffer 的大小

show variables like '%join_buffer%'

把我们之前用的 single_table 表搬出来,基于 single_table 表创建2个表,每个表插入1w条随机记录

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

create table t1 like single_table;
create table t2 like single_table;

如果直接使用 join 语句,MySQL优化器可能会选择表 t1 或者 t2 作为驱动表,这样会影响我们分析sql语句的过程,所以我们用 straight_join 让mysql使用固定的连接方式执行查询

select * from t1 straight_join t2 on (t1.common_field = t2.common_field)

运行时间为0.035s

执行计划如下

在Extra列中看到了 Using join buffer ,说明连接操作是基于 「Block Nested-Loop Join」 算法

Index Nested-Loop Join

了解了 「Block Nested-Loop Join」 算法之后,可以看到驱动表的每条记录会把被驱动表的所有记录都匹配一遍,非常耗时,能不能提高一下被驱动表匹配的效率呢?

估计这种算法你也想到了,就是给被驱动表连接的列加上索引,这样匹配的过程就非常快,如图所示

我们来看一下基于索引列进行连接执行查询有多快?

select * from t1 straight_join t2 on (t1.id = t2.id)

执行时间为0.001秒,可以看到比基于普通的列进行连接快了不止一个档次

执行计划如下

「驱动表的记录并不是所有列都会被放到 join buffer,只有查询列表中的列和过滤条件中的列才会被放入 join buffer,因此我们不要把 * 作为查询列表,只需要把我们关心的列放到查询列表就好了,这样可以在 join buffer 中放置更多的记录」

如何选择驱动表?

知道了 join 的具体实现,我们来聊一个常见的问题,即如何选择驱动表?

「如果是 Block Nested-Loop Join 算法:」

  1. 当 join buffer 足够大时,谁做驱动表没有影响
  2. 当 join buffer 不够大时,应该选择小表做驱动表(小表数据量少,放入 join buffer 的次数少,减少表的扫描次数)

「如果是 Index Nested-Loop Join 算法」

假设驱动表的行数是M,因此需要扫描驱动表M行

被驱动表的行数是N,每次在被驱动表查一行数据,要先搜索索引a,再搜索主键索引。每次搜索一颗树近似复杂度是以2为底N的对数,所以在被驱动表上查一行的时间复杂度是2*log2^N

驱动表的每一行数据都要到被驱动表上搜索一次,整个执行过程近似复杂度为M + M*2*log2^N

「显然M对扫描行数影响更大,因此应该让小表做驱动表。当然这个结论的前提是可以使用被驱动表的索引」

「总而言之,我们让小表做驱动表即可」

「当 join 语句执行的比较慢时,我们可以通过如下方法来进行优化」

  1. 进行连接操作时,能使用被驱动表的索引
  2. 小表做驱动表
  3. 增大 join buffer 的大小
  4. 不要用 * 作为查询列表,只返回需要的列
文章分享自微信公众号:
Java识堂

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!

作者:小识
原始发表时间:2022-04-14
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • 35 | join语句优化

    一般来说,使用join语句,会用到两种算法,分别是Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BN...

    HaC
  • Mysql Join语句的优化

    1. 尽可能减少Join语句中Nested Loop的循环总次数 最有效的办法是让驱动表的结果集尽可能地小,这也正是在本章第二节中所提到的优化基本原则之一——“...

    dys
  • MySQL实战第三十五讲- join语句怎么优化?

    在上一篇文章中,我和你介绍了 join 语句的两种算法,分别是 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop ...

    越陌度阡
  • Mysql - join 优化

      mutil-range read , 正如他的名字一样,优化的是离散范围的读,具体是优化在 主键上离散范围的读

    执生
  • Hive Join优化

    在阐述Hive Join具体的优化方法之前,首先看一下Hive Join的几个重要特点,在实际使用时也可以利用下列特点做相应优化:

    大数据学习与分享
  • 34 | join语句的使用

    在选择Join算法时,会有优先级,理论上会优先判断能否使用INLJ、BNLJ: Index Nested-LoopJoin > Block Nested-Lo...

    HaC
  • MySQL中的join语句

    在MySQL中,join语句想必大家都不陌生,今天我们围绕join语句展开,说一些可能平时不关注的知识点。

    AsiaYe
  • sql语句中的left join,right join,inner join的区别

    left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 right join(右联接) 返回包括右表中的所有记录和左表中联结字段相...

    码农阿宇
  • Hive Join优化

    在阐述Hive Join具体的优化方法之前,首先看一下Hive Join的几个重要特点,在实际使用时也可以利用下列特点做相应优化:

    大数据学习与分享
  • SQL 不知道咋优化?吹一手 join 语句的优化准没错

    面试最怕遇到的问题是什么,如何做优化一定当仁不让,SQL 优化更是首当其冲,这里先跟大家分享一个比较容易理解的 join 语句的优化~

    飞天小牛肉
  • 面试官:在项目中如何使用join语句优化提升性能?

    面试官:sync; echo 3 > /proc/sys/vm/drop_caches就可以清理buff/cache了,你说说我在线上执行这条命令做好不好?

    码农编程进阶笔记
  • HIVE:JOIN原理、优化

    hive的一些join操作以及hivejoin操作的优化

    俺也想起舞
  • MySQL Join深度优化

    在上述查询中,我们需要回表主键索引,在主键索引树上,每次只能根据一个主键id查找到一行数据。随着a的值递增查询的话,id的值回表查询就会变成随机访问,性能较差。

    shysh95
  • 一条Insert语句怎么优化和解构

    insert into crash_extend_bak select a.extend_id,a.crash_id,a.system_log,a.user_l...

    jeanron100
  • [MySQL系列] SELECT STRAIGHT_JOIN优化join查询技巧

    需要理解MySQL对多表连接的处理方式,首先MySQL优化器要确定以谁为驱动表,也就是说以哪个表为基准,在处理此类问题时,MySQL优化器采用了简单粗暴的解决方...

    大灰狼2
  • 大数据量分页查询,SQL语句怎么优化?

    当需要从数据库查询的表有上万条记录的时候,一次性查询所有结果会变得很慢,特别是随着数据量的增加特别明显,这时需要使用分页查询。对于数据库分页查询,也有很多种方法...

    Bug开发工程师
  • Hive Join方式与优化

    Hive支持的Join方式有Inner Join和Outer Join,这和标准SQL一致。除此之外,还支持一种特殊的Join:Left Semi-Join。

    十里桃花舞丶
  • 详解hive的join优化

    Hive自动识别各种用例并对其进行优化。Hive 0.11改进了这些情况的优化器:

    Spark学习技巧

扫码关注腾讯云开发者

领取腾讯云代金券