首页 >数据库 >mysql教程 > 正文

MySQL进阶学习:深入了解 join 的3种算法

转载2021-10-13 10:56:270796
本篇文章是MySQL的进阶学习,给大家详细介绍一下join连接的原理,以及join的3种算法,希望对大家有所帮助!

我们经常在多表查询的时候使用join 去连接多个表,其实join的效率比不好还是应该尽量避免使用的,其本质就是各个表之间循环匹配的,MySQL中只支持一种join算法Nested-Loop Join(循环嵌套连接),但是其有多种变种的算法,其实就是提高join的执行效率。【相关推荐:mysql视频教程

1. Simple Nested-Loop Join(简单嵌套循环连接)

Simple Nested-Loop join(NLJ)算法从循环中的第一个表中一次读取一行,将每一行传递给一个嵌套循环,该嵌套循环中匹配数据是否一致。例如驱动表User,被驱动表UserInfo 的sql是 select * from User u left join User_info info on u.id = info.user_id,其实就是我们常用的for循环,伪代码的逻辑应该是

for(User u:Users){
    for(UserInfo info:UserInfos){
        if(u.id == info.userId){
            // 得到匹配数据
        }
    }
}

简单粗暴的算法,每次从User表中取出一条数据,然后扫描User_info中的所有记录匹配,最后合并数据返回。

假如驱动表User有10条数据,被驱动表UserInfo也有10条数据,那么实际上驱动表User会被扫描10次,而被驱动表会被扫描10*10=100次(每扫描一次驱动表,就会扫描全部的被驱动表),这种效率是很低的,对数据库的开销比较大,尤其是被驱动表。每一次扫描其实就是从硬盘中读取数据加载到内存中,也就是一次IO,目前IO是最大的瓶颈

1.png

2. Index Nested-Loop Join(索引嵌套循环连接)

索引嵌套循环是使用索引减少扫描的次数来提高效率的,所以要求非驱动表上必须有索引才行。

在查询的时候,驱动表(User) 会根据关联字段的索引进行查询,当索引上找到符合的值,才会进行回表查询。如果非驱动表(User_info)的关联字段(user_id)是主键的话,查询效率会非常高(主键索引结构的叶子结点包含了完整的行数据(InnoDB)),如果不是主键,每次匹配到索引后都需要进行一次回表查询(根据二级索引(非主键索引)的主键ID进行回表查询),性能肯定弱于主键的查询。

2.png

上图中的索引查询之后不一定会回表,什么情况下会回表,这个要看索引查询到的字段能不能满足查询需要的字段,具体可以参考之前的文章:你需要知道的一些索引基础知识 和 B+树的索引知识

3. Block Nested-Loop Join(缓存块嵌套循环连接)

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

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

3.png

驱动表能不能一次加载完,要看join buffer能不能存储所有的数据,默认情况下join_buffer_size=256k,查询的时候Join Buffer 会缓存所有参与查询的列而不是只有join的列,在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段,可以让join buffer中可以存放更多的列。

可以调整join_buffer_size的缓存大小show variables like '%join_buffer%'这个值可以根据实际情况更改。

4.png

使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on,默认是开启的。可以通过 show variables like '%optimizer_switch%' 查看block_nested_loop状态。

5.png

以上三种算法了解即可,其实实际工作中只要我们能都用好索引就不错了,即使是join的连接也要注意关联字段是否建立索引,还是要善于使用索引来提供查询效率。

原文地址:https://juejin.cn/post/7014105037517357093

作者:纪先生

更多编程相关知识,请访问:编程入门!!

以上就是MySQL进阶学习:深入了解 join 的3种算法的详细内容,更多请关注php中文网其它相关文章!

php中文网最新课程二维码

声明:本文转载于:掘金--纪先生,如有侵犯,请联系admin@php.cn删除

  • 相关标签:MySQL join
  • 相关文章

    相关视频


    网友评论

    文明上网理性发言,请遵守 新闻评论服务协议

    我要评论
  • 专题推荐

    作者信息

    青灯夜游

    今天学习一小步,明天提升一大步

    最近文章
    php怎么分割数组为几部分657
    php怎么删除指定下标的数组值824
    php怎么获取当前方法的参数值729
    推荐视频教程
  • PHP+Jquery+Mysql实现省市县三级联动PHP+Jquery+Mysql实现省市县三级联动
  • MySQL权威开发指南(教程)MySQL权威开发指南(教程)
  • MySQL数据库技术小知识每日分享MySQL数据库技术小知识每日分享
  • MySQL高级进阶视频教程MySQL高级进阶视频教程
  • 视频教程分类