MySQL JOIN关联查询的原理及优化_Mysql

来源:脚本之家  责任编辑:小易  
目录
1 关联查询的执行2 没有索引的算法

1 关联查询的执行

关联查询的执行过程是:先遍历关联表t1(驱动表,全表扫描),然后根据从表t1中取出的每行数据中的a值,去表t2(被关联表,被驱动表)中查找满足条件的记录,可以走t2的索引搜索。在形式上,这个过程就跟我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以我们称之为“Index Nested-Loop Join”,简称NLJ。在join语句的执行流程中,驱动表是走全表扫描,而被驱动表是走索引树搜索。

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

假设驱动表的行数是N,执行过程就要扫描驱动表N行,然后对于每一行,到被驱动表上匹配一次。

因此整个执行过程,近似复杂度是 N + N2log2M。显然,N对扫描行数的影响更大,因此应该让小表来做驱动表:N扩大1000倍的话,扫描行数就会扩大1000倍;而M扩大1000倍,扫描行数扩大不到10倍。

结论:如果使用join语句的话,需要让小表做驱动表,并且被驱动表的关联字段应该建立索引。一般来说,除非有其他理由,否则只需要在关联顺序中的第二个表的相应列上创建索引,即在被驱动的表的关联字段简历索引。

2 没有索引的算法

如果,被驱动表的关联字段没有使用索引,那么MySQL将使用另一种Block Nested-Loop Join算法。

把表t1的数据读入线程内存join_buffer中,这只会将查询需要返回的列放入,如果我们的语句中写的是select *,就会把整个表t1放入了内存;扫描表t2,把表t2中的每一行取出来,跟join_buffer中的数据做对比,满足join条件的,作为结果集的一部分返回。

这个过程的流程图如下:

对应地,这条SQL语句的explain结果的Extra字段中将会展示:Block Nested Loop。在这个过程中,对表t1和t2都做了一次全表扫描,因此总的扫描行数是量表的数据总和M+N。由于join_buffer是以无序数组的方式组织的,因此对表t2中的每一行,都要做100次判断,总共需要在内存中做的判断次数是:M* N次。

假设小表的行数是N,大表的行数是M,那么在这个算法里:

两个表都做一次全表扫描,所以总的扫描行数是M+N;内存中的判断次数是M*N,虽然不需要读盘,但是需要占用大量CPU进行计算。

可以看到,调换这两个算式中的M和N没差别,因此这时候选择大表还是小表做驱动表,执行耗时是一样的。

join_buffer的大小是由参数join_buffer_size设定的,默认值是256k。如果放不下表t1的所有数据话,策略很简单,就是将t1的数据分段放入、比较,假设表t1被分成了两次放入join_buffer中,那么会导致表t2会被扫描两次。虽然分成两次放入join_buffer,但是内存中判断等值条件的次数还是不变的,依然是M*N次。

假设,驱动表的数据行数是N,需要分K段才能完成算法流程,K大于等于1,被驱动表的数据行数是M。注意,这里的K不是常数,N越大K就会越大。

所以,在这个算法的执行过程中:

扫描行数是 N+K*M;内存判断 N*M次。

可以看到,如果join_buffer_size没有足够大(这是常见的情况),那么N越小,这样K就更小,扫描的行数才会更少,因此仍然应该让小表当驱动表。而且K也是影响扫描行数的关键因素,这个值越小越好,如果N不变,那么影响K的就是join_buffer_size的大小。join_buffer_size越大,一次可以放入的行越多,分成的段数K也就越少,对被驱动表的全表扫描次数就越少。

因此,如果你的join语句很慢,除了让小表当驱动表,还有就把join_buffer_size改大。

如果确定“小表”呢?除了总行数之外,还应该是两个表按照各自的条件过滤,过滤完成之后,再计算参与join的各个字段的总数据量(因为还要放入内存中),数据量小的那个表,就是“小表”,应该作为驱动表。

实际在查询优化时,如果join不是使用的Index Nested-Loop Join算法,则应该尽量改为使用该算法。

到此这篇关于MySQL JOIN关联查询的原理及优化的文章就介绍到这了,更多相关MySQL JOIN关联查询 内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

您可能感兴趣的文章:mysql高效查询left join和group by(加索引)解决Mysql的left join无效及使用的注意事项说明MySQL七种JOIN类型小结MYSQL Left Join优化(10秒优化到20毫秒内)MySQL数据库连接查询?join原理Mysql体系化探讨令人头疼的JOIN运算

  • 本文相关:
  • navicat中创建存储过程、触发器和使用游标的简单实例(图文)
  • mysql 数据库定时备份的几种方式(全面)
  • mysql日期及时间字段的查询
  • mssql 添加字段说明
  • mysql?优化?index?merge引起的死锁分析
  • mac下mysql5.7忘记root密码的解决方法
  • linux/mac安装mysql忘记密码的解决办法
  • mysql启动错误解决方法
  • mysql版本低了不支持两个时间戳类型的值解决方法
  • mysql 5.7 学习心得之安全相关特性
  • MySQL中inner join 和 cross join
  • MySQL 的子查询和left join的比较,什么时候用子查询效率高,什么...
  • 解析mysql left( right ) join使用on与where筛选的差异
  • mysql的full join报错[Err] 1054 - Unknown column '***.id_p' ...
  • 深入理解mysql之left join 使用详解
  • MySQL中Nested-Loop Join算法小结
  • Mysql连接join查询原理知识点
  • mysql where和 join on 哪个效率高
  • mysql里面 left join on 和 普通多表查询 有啥区别 我感觉区别不...
  • 请问这条mysql语句怎么实现?join的语句
  • mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录
  • MySQL的LEFT JOIN表连接的进阶学习教程
  • 请教大神,mysql查询,left join关联表时想对关联表进行数据筛选,...
  • MySQL优化之使用连接(join)代替子查询
  • MySQL 的子查询和left join的比较,什么时候用子查询效率高,什么...
  • mysql中的inner join问题
  • mysql update和inner join要怎么写才对 急 救命
  • mysql多个left join连接查询用法分析
  • mysql创建Bitmap_Join_Indexes中的约束与索引
  • mysql的inner join连接查询怎么使用
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysqlmysql高效查询left join和group by(加索引)解决mysql的left join无效及使用的注意事项说明mysql七种join类型小结mysql left join优化(10秒优化到20毫秒内)mysql数据库连接查询?join原理mysql体系化探讨令人头疼的join运算navicat中创建存储过程、触发器和使用游标的简单实例(图文)mysql 数据库定时备份的几种方式(全面)mysql日期及时间字段的查询mssql 添加字段说明mac下mysql5.7忘记root密码的解决方法linux/mac安装mysql忘记密码的解决办法mysql启动错误解决方法mysql版本低了不支持两个时间戳类型的值解决方法mysql 5.7 学习心得之安全相关特性mysql安装图解 mysql图文安装教程(详细说明)can""""t connect to mysql servwindows下mysql5.6版本安装及配置过程附有截图和mysql字符串截取函数substring的用法说明mysql提示:the server quit withoumysql之timestamp(时间戳)用法详解mysql创建用户与授权方法mysql——修改root密码的4种方法(以windows为mysql查看版本号的几种方式mysql日期数据类型、时间类型使用总结mysql 查找价格最高的图书经销商的几种sql语句优化mysql数据库的8个方法mysql中文乱码问题的最佳解决方法mysql中安装样本数据库sakila过程分享mysql5.7.18的安装与主从复制图文详解教你使用vs?code的mysql扩展管理数据库的方法mysql允许外网访问设置步骤mysql innodb的重要组件汇总mysql纵表转换为横表的方法及优化教程mysql中general_log日志知识点介绍
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved