热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

mysqljoin算法_【MySQL】之join算法详解

在阿里巴巴的java开发手册有这么一条强制规定:超过三个表禁止join,须要join的字段,数据类型保持绝对一致,多表关联查

在阿里巴巴的java开发手册有这么一条强制规定:超过三个表禁止join,须要join的字段,数据类型保持绝对一致,多表关联查询时,要保证被关联的字段须要有索引。

为何尽可能避免使用join?若是使用join,咱们应该怎么用呢?接下来咱们就一块儿聊一聊关于join的几种算法。

Simple Nested-Loop Joinjava

Simple Nested-Loop Join算法是指读取驱动表t1中的每行数据,将每行数据传递到被驱动表t2上,取出被驱动表t2中知足条件的行,和t1组成结果集。算法

在这个算法中,须要对t1进行全表扫描,假设t1表1000行数据,那么须要对t2表进行1000次全表扫描,假设t2表也是1000行数据,那么就须要扫描1000 X 1000=1000000行。sql

示例图以下:当t1表5行数据,t2表5行数据时,须要扫描25行数据。数据库

fe7ed27b5854970bf741a7b9913a0407.png

Index Nested-Loop Joinapache

index nested-loop join算法的优化思路是经过驱动表的匹配条件,直接与被驱动表的索引进行匹配,减小了被驱动表的扫描次数。缓存

该算法一样要对驱动表t1进行全表扫描,可是咱们在拿着t1表的数据去被驱动表t2进行匹配时能够利用t2表的索引,若是t1表中1000行数据,t2表中1000行数据,那么一共就须要扫描1000+1000=2000行数据。这个过程就跟咱们写程序时的嵌套查询相似,而且能够用上被驱动表的索引,因此称之为“Index Nested-Loop Join”,简称 NLJ。微信

示例以下:当t1表有5行数据,t2表有5行数据时,一共须要扫描5+5=10行数据。数据结构

0a24e7feddd68412387849e6ac2c639c.png

Block Nested-Loop Joinoop

Block Nested-Loop join,基于块的嵌套循环,简称BNL算法,其优化思路主要是减小被驱动表的循坏次数,它会将驱动表的数据缓存起来,把参与查询的列缓存到join buffer里,而后拿join buffer里的数据批量与内层表的数据在join buffer中进行匹配,知足join条件的,做为结果集的一部分返回。

949c67dcc28a057494cde0438bee5c87.png

能够看到该算法对两个表都进行了全表扫描,所以扫描的行数是两个表的行数之和。这种场景下,虽然在扫描行数上和NLJ算法同样,可是因为BNL算法是在内存中进行判断,速度上会快不少。

join buffer的大小是由参数join_buffer_size设定,默认256k。若是一次放不下驱动表的全部数据,会分段放,这种状况下会致使被驱动表扫描屡次。若是被驱动表是冷数据表,而且屡次扫描读取被驱动表间隔超过1S的话,就会将他放入LRU链表的young区域,致使业务数据没法进入热数据区,减小了bufferpool的命中率,这又是另一个课题了,暂不过多展开。咱们能够经过调大join_buffer_size来提升缓存的数据量,减小对被驱动表的扫描次数。

启用BNL算法须要在optimizer_switch参数中设置block_nested_loop=on。

Batched Key Access性能

BNL算法提高了join的性能,可是它在经过辅助索引链接后须要回表,就会消耗大量的随机I/O,咱们知道随机IO对MySQL的影响是很是大的。所以MySQL5.6引入了Batched Key Access(BKA,批量键访问联接)算法。

再说BKA算法时不得不提的就是MySQL的Multi-Range Read 优化,MRR的目的主要是减小磁盘的随机访问。咱们都知道,Innodb索引采用的是B+tree的数据结构,数据保存在主键索引中,而且是按照主键递增的顺序插入的,可是二级索引的排列顺序和主键的排列顺序通常是不同的,它保存的主键值也并不是按照主键顺序排列,在回表时就会出现随机访问主键索引的状况。因此若是能够按照主键递增顺序查询的话,对磁盘的读比较接近顺序读,这样就可以提高读性能。

MRR优化的思路就是在进行范围查询时,在获得主键值以后,先按照主键的顺序进行排序,而后拿着排好序的主键ID再去主键索引进行查询,这样就能体现出顺序性的优点了。由于是多值查询,因此通常用于range、ref类型的查询。

再说会BKA算法,当被驱动表上有索引能够利用时,那么就在行提交给被 join 的表以前,先对两个表的对应列的索引字段进行join,获得主键值后,按照主键排好序后,利用 MRR 技术,批量访问表取数据,减小了随机 IO。可是若是被 join 的表没用索引的话,那就只能使用BNL算法了。

具体算法以下图:

626dc2b8cfb9e643d20092d42108ee84.png

开启BKA和MRR的方式:

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

MySQL在8.0版本已经实现了hash join,这里暂不作介绍。

小结

如何优化join的速度呢,这里给出以下几点建议:

尽可能避免使用join。

用小表做为驱动表,减小外层循环的次数。

多表关联查询时,要保证被关联的字段要有索引。

适当增大join_buffer_size的值,缓存的数据越多,就越能减小被驱动表扫描的次数。

减小没必要要的字段查询。

须要join的字段,数据类型保持绝对一致。



推荐阅读
  • 本文深入探讨了MySQL中常见的面试问题,包括事务隔离级别、存储引擎选择、索引结构及优化等关键知识点。通过详细解析,帮助读者在面对BAT等大厂面试时更加从容。 ... [详细]
  • 本文详细介绍了如何在 MySQL 中授予和撤销用户权限。包括创建用户、赋予不同级别的权限(如表级、数据库级、服务器级)、使权限生效、查看用户权限以及撤销权限的方法。此外,还提供了常见错误及其解决方法。 ... [详细]
  • MySQL InnoDB Double Write机制详解
    本文深入探讨了MySQL InnoDB存储引擎的Double Write技术,该技术通过在内存和磁盘上创建数据页的副本,确保了部分写失效(Partial Page Write)情况下的数据完整性和可靠性。同时,文章介绍了InnoDB以页为单位进行读取和更新的机制,并详细解析了Double Write的工作原理。 ... [详细]
  • 本文详细介绍了优化DB2数据库性能的多种方法,涵盖统计信息更新、缓冲池调整、日志缓冲区配置、应用程序堆大小设置、排序堆参数调整、代理程序管理、锁机制优化、活动应用程序限制、页清除程序配置、I/O服务器数量设定以及编入组提交数调整等方面。通过这些技术手段,可以显著提升数据库的运行效率和响应速度。 ... [详细]
  • 简化报表生成:EasyReport工具的全面解析
    本文详细介绍了EasyReport,一个易于使用的开源Web报表工具。该工具支持Hadoop、HBase及多种关系型数据库,能够将SQL查询结果转换为HTML表格,并提供Excel导出、图表显示和表头冻结等功能。 ... [详细]
  • 优化Flask应用的并发处理:解决Mysql连接过多问题
    本文探讨了在Flask应用中通过优化后端架构来应对高并发请求,特别是针对Mysql 'too many connections' 错误的解决方案。我们将介绍如何利用Redis缓存、Gunicorn多进程和Celery异步任务队列来提升系统的性能和稳定性。 ... [详细]
  • 如何从python读取sql[mysql基础教程]
    从python读取sql的方法:1、利用python内置的open函数读入sql文件;2、利用第三方库pymysql中的connect函数连接mysql服务器;3、利用第三方库pa ... [详细]
  • 本文介绍如何从包含多个记录的会员表中,筛选出同时拥有BookID为10和14的会员。通过SQL查询语句,可以有效地获取符合条件的MemberID。 ... [详细]
  • 本文介绍了数据库体系的基础知识,涵盖关系型数据库(如MySQL)和非关系型数据库(如MongoDB)的基本操作及高级功能。通过三个阶段的学习路径——基础、优化和部署,帮助读者全面掌握数据库的使用和管理。 ... [详细]
  • 目录一、salt-job管理#job存放数据目录#缓存时间设置#Others二、returns模块配置job数据入库#配置returns返回值信息#mysql安全设置#创建模块相关 ... [详细]
  • 本文详细介绍了在XAMPP环境中如何修改Apache和MySQL的默认端口号,并确保WordPress能够正常访问。同时,提供了针对Go语言社区和Golang开发者的相关建议。 ... [详细]
  • Java项目分层架构设计与实践
    本文探讨了Java项目中应用分层的最佳实践,不仅介绍了常见的三层架构(Controller、Service、DAO),还深入分析了各层的职责划分及优化建议。通过合理的分层设计,可以提高代码的可维护性、扩展性和团队协作效率。 ... [详细]
  • PostgreSQL 最新动态 —— 2022年4月6日
    了解 PostgreSQL 社区的最新进展和技术分享 ... [详细]
  • 本文详细介绍了MySQL数据库中的Bin Log和Redo Log,阐述了它们在日志记录机制、应用场景以及数据恢复方面的区别。通过对比分析,帮助读者更好地理解这两种日志文件的作用和特性。 ... [详细]
  • 本文详细介绍了如何在云服务器上配置Nginx、Tomcat、JDK和MySQL。涵盖从下载、安装到配置的完整步骤,帮助读者快速搭建Java Web开发环境。 ... [详细]
author-avatar
evon0207165
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有