登录 |  注册
首页 >  数据库 · 存储 >  MySql实战精选笔记 >  高性能MySQL-Join的底层实现原理

高性能MySQL-Join的底层实现原理

mysql只支持一种join算法:Nested-Loop Join(嵌套循环连接),但Nested-Loop Join有三种变种:Simple Nested-Loop Join,Index Nested-Loop Join,Block Nested-Loop Join(简单-索引-缓冲区)

原理:

1.Simple Nested-Loop Join:

如下图,r为驱动表,s为匹配表,可以看到从r中分别取出r1、r2、......、rn去匹配s表的左右列,然后再合并数据,对s表进行了rn次访问,对数据库开销大

mysql join1.png

2.Index Nested-Loop Join(索引嵌套):

这个要求非驱动表(匹配表s)上有索引,可以通过索引来减少比较,加速查询。

在查询时,驱动表(r)会根据关联字段的索引进行查找,挡在索引上找到符合的值,再回表进行查询,也就是只有当匹配到索引以后才会进行回表查询。

如果非驱动表(s)的关联健是主键的话,性能会非常高,如果不是主键,要进行多次回表查询,先关联索引,然后根据二级索引的主键ID进行回表操作,性能上比索引是主键要慢。

mysql join2.png

3.Block Nested-Loop Join:

如果有索引,会选取第二种方式进行join,但如果join列没有索引,就会采用Block Nested-Loop Join。可以看到中间有个join buffer缓冲区,是将驱动表的所有join相关的列都先缓存到join buffer中,然后批量与匹配表进行匹配,将第一种多次比较合并为一次,降低了非驱动表(s)的访问频率。默认情况下join_buffer_size=256K,在查找的时候MySQL会将所有的需要的列缓存到join buffer当中,包括select的列,而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

mysql join3.png

  • left join:会保全左表数据,如果右表没相关数据,会显示null

  • fight join:会保全右表数据,如果左表没相关数据,会显示null

  • inner join:部分主从表,结果会取两个表针对on条件相匹配的最小集

总结:

不论是Index Nested-Loop Join 还是 Block Nested-Loop Join 都是在Simple Nested-Loop Join

的算法的基础上 减少嵌套的循环次数, 不同的是 Index Nested-Loop Join 是通过索引的机制减少内层表的循环次数,Block Nested-Loop Join 是通过一次缓存多条数据批量匹配的方式来减少外层表的循环次数,通过 理解join 的算法原理我们可以得出以下表连接查询的优化思路。

1、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)

2、为匹配的条件增加索引(减少内层表的循环次数)

3、增大join buffer size的大小(一次缓存的数据越多,那么外层表循环的次数就越少)

4、减少不必要的字段查询(字段越少,join buffer 所缓存的数据就越多,外层表的循环次数就越少)

上一篇: buffer pool详解
下一篇: Expression #1 of ORDER BY clause is not in GROUP BY ....
推荐文章
  • mysql只支持一种join算法:Nested-LoopJoin(嵌套循环连接),但Nested-LoopJoin有三种变种:SimpleNested-LoopJoin,IndexNested-LoopJoin,BlockNested-LoopJoin(简单-索引-缓冲区)原理:1.SimpleNe
  • redis是一个内存数据库,一旦断电或服务器进程退出,内存数据库中的数据将全部丢失,所以需要redis持久化 redis持久化就是把数据保存在磁盘上,利用永久性存储介质将数据保存,在特定的时间将保存的数据进行恢复的工作机制redis提供两种持久化机制RDB:存储数据结果,关注点在数据AOF:存储操作
  • 通过SQL的执行过程来介绍MySQL的基础结构.     首先有一个user_info表,表里有一个id字段,执行下面这条查询语句:Select * form user_info where i
  • 索引(Index)是帮助MySQL高效获取数据的数据结构,索引的目的在于提高查询效率,就像字典和书籍的目录一样,有了目录,可以帮助你快速查找你需要的内容。可以理解为一个排好序的快速查找数据结构。也就是
  • 说到数据库事务,大家脑子里一定很容易蹦出一堆事务的相关知识,如事务的ACID特性,隔离级别,解决的问题(脏读,不可重复读,幻读)等等,但是可能很少有人真正的清楚事务的这些特性又是怎么实现的,为什么要有四个隔离级别。今天我们就先来聊聊MySQL中事务的隔离性的实现原理,后续还会继续出文章分析其他特性的
  • 前面我们系统了解了一个查询语句的执行流程,并介绍了执行过程中涉及的处理模块。相信你还记得,一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块,最后到达存储引擎。那么,一条更新语句
学习大纲