MySQL之JOIN原理学习
2023-06-13 09:12:13 时间
MySQL
之JOIN
原理学习
表结构
#test1表100w数据,test2表100条数据
CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `test2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
案例一
explain计划中id相同,自上而下执行,从执行计划中得知test1的type级别是eq_ref,从后面的key也了解
到他用到了主键索引,rows是扫描行数,explain只是估计值,并不实际计算,此时test1表应该是扫描了100
行,因为test2全表扫到了id,知道id后就可以直接去test1表的主键索引树精确扫到这100行test1的数据了。
所以这次sql其实一共扫了200行数据。
案例二
这个其实也和案例一一样,只不过是在index_a索引树上找的对应关系,多出的一步就是回表操作。和案例一的区别只是
一开始寻找的树是index_a树,最后匹配在树中匹配到后回表。
案例三
因为没有使用索引,索引全表扫描test2,将b列的值扫出来都放入内存区中,内存中就不是按照B+树这
种结构存放了,它是无序的。然后将test1表的每一行拿出来去与join_buffer中的值做对比。所以最
坏情况可能是总共对比100*1000000次(因为无序),俩个表扫描的数据行数分别是1000000+100。
一个内存块默认大小256KB
,驱动表数据太多怎么办?
在第一个内存块的数据都与test1的一条数据对比完后,清空内存块,将剩余数据再放入内存卡,
出现内存块一次性放不数据的情况,对比很费时。
嵌套循环连接Nested-Loop-Join(NLJ)
算法
概念
一次次循环地从驱动表中读取数据,根据关联数据在被驱动表里取出满足条件的行,然后取出满足要求的集合。
例如上面中的案例一和案例二,mysql优化器一般会选择用小表来驱动大表。并不是谁在前,谁就是大表,如下图。优化器一般会选择小表做驱动表。
使用NLJ算法,一般join语句中,如果执行计划Extra中未出现Using join buffer则表示使用的是NLJ算法
NLJ算法只针对join,如果是left join则左标是驱动表,右表是被驱动表,right join则相反
基于块的嵌套循环连接Block Nested-Loop-Join(BNL)
算法
概念
将驱动表的数据读到join_buffer中,然后扫面被驱动表,将被驱动表中每一行
数据拿出来与join_buffer中的数据对比。
如案例三中所示,看Extra中的信息是否有Using join buffer
反思
NLJ
是在磁盘上做关联,因为有索引的关系,值对比关联才会快
BLJ
是将一张表的数据放在内存中,然后将另一张表的数据取出来一一对比,在内存中的操作还是比较快的,但不如索引,因为B+本身就是排序树的原因。
将案例三使用NLJ
算法来连接会怎么样
因为b列不是索引列,所以每次操作都会涉及到磁盘IO操作,然后将test2的数
据拿到test1,
再一条一条取出test1表的数据对比,至始至终都没有用到索引,都是底层IO硬
搜,所以速度
肯定会慢四。。。。
所以有索引的还是走NLJ算法快,没索引的走BNL算法,但也要注意驱动表的数
据量,太大的话
,内存块一次放不下也麻烦。
相关文章
- MySQL学习之Mysql锁&事务隔离级别详解数据库
- 使用MySQL客户端进行学习:一个指南(mysql客户端教程)
- 如何使用MySQL建立索引(mysql怎么建索引)
- MySQL入门经典:学习PDF指南(mysql入门经典pdf)
- MySQL数据库:深度研习(mysql数据库深入学习)
- 学习PHP与MySQL:一个完美的开始(php与mysql教程)
- 据库快速掌握MySQL查询连接数据库技巧(mysql查询连接数)
- 「MySQL 连接三张表」 数据库查询操作技巧详解(mysql连接三张表)
- MySQL分布式锁的应用与原理(mysql分布式锁)
- 深入学习计算机二级数据库MySQL:解密其运作机制和数据管理核心(计算机二级数据库mysql)
- MySQL数据库基础入门指南(mysql数据库学习入门)
- 学习MySQL依赖库:学习指南(mysql依赖库)
- MySQL数据库: 找寻最佳解决方案(mysql数据库技术答案)
- 深入浅出:MySQL服务注册简明教程(mysql 服务 注册)
- MySQL中的空值判断原理(mysql为空判断)
- MySQL学习之旅,在CSDN学院开启(csdn学院mysql)
- MySQL多版本时间戳简介(mysql个版本的时间戳)
- MySQL的三大组件简介(mysql三大组件)
- MySQL如何进行一张表内连接(mysql一张表内连接)
- 深入了解MySQL语法分析器Yacc的原理与实现(mysql yacc)
- 深入探究MySQL XID原理,了解数据操作的核心机制(mysql xid原理)
- MySQL操作若不存在则创建(mysql 不存在创建)
- MySQL实现不同库之间的关联查询(mysql不同库关联查询)
- 解决MySQL下载转圈问题的小技巧(mysql下载一直在转圈)
- 上周时间回顾MySQL学习MySQL知识,备战职场(mysql 上周时间)
- MySQL精度问题,存储时无法保存毫秒(mysql不能保存毫秒)