解析MySQL join查询的原理

MySQL用Nested-Loop Join算法实现join查询

区分驱动表和被驱动表，以驱动表的结果集为循环的基础，访问被驱动表过滤数据，然后合并结果，驱动表在外循环、被驱动表在内循环。
如果还有第三张参与join查询的表，则以合并的结果为驱动表，第三张表作为被驱动表，以此类推。

left join中的左表是驱动表、右表是被驱动表，right join刚好相反。

Nested-Loop Join有三种实现

SNLJ

Simple Nested-Loop Join

假设A是驱动表，B是被驱动表。

这里会扫描A表，用A的结果集作为外循环，
每循环一次，会扫描B表一遍(遍历内循环）

A表有N行，B表有M行。

SNLJ的开销如下（最大情况下）：

扫描A表1次；
扫描B表N次。
总共读取记录数：N + N * M。

为了专注于理解Nested-Loop Join，这里不讨论带where子句的情况，以下相同。

BNLJ

Block Nested-Loop Join

假设A是驱动表，B是被驱动表。

用来join的字段在被驱动表没有建立索引

Join Buffer
MySQL会将驱动表结果集中（多条记录）用来join的字段缓存到Join Buffer，
Join Buffer的特点是只需要扫描被驱动表一次，就能得到Join Buffer中所有记录的匹配结果，
减少扫描的次数。

Join Buffer默认大小256k，会生成n-1个Join Buffer缓冲区，n为参与join查询的表数量。

A表有N行，B表有M行。

BNLJ的开销如下（最大情况下）：

扫描A表1次；
扫描B表X次；
X的大小取决于N、join字段的大小、Join Buffer的大小，通常X<<N。

INLJ

Index Nested-Loop Join

假设A是驱动表，B是被驱动表。

用来join的字段在被驱动表建立了索引

聚集索引

非聚集索引

在这里我们假设您已对MySQL的索引结构有了一定的了解，
如果没有的话，可以去看下：通过B+Tree平衡多叉树理解InnoDB引擎的聚集和非聚集索引

这里会扫描A表，用A的结果集作为外循环，
然后通过B表的索引来检索，不会遍历B表。

A表有N行，B表有M行。

INLJ的开销如下（最大情况下）：

扫描A表1次；
通过B表索引检索N次，成本比扫描B表N次会低很多；
回表：先找到非聚集索引，再找到聚集索引，会多一次磁盘IO。

NLJ优先级

INLJ>BNLJ>SNLJ

如何优化join查询效率

尽量将小表作为驱动表，大表作为被驱动表；
为参加join的字段在被驱动表建立聚集索引，其次是非聚集索引；
尽可能减少join的字段数量，或者使用长度比较小的字段来join，这样Join Buffer一次可以缓存更多条记录。

inner join时，MySQL会自动将小表作为驱动表，大表作为被驱动表。

扫描整张表是成本非常高的操作。

到此这篇关于MySQL join查询的原理的文章就介绍到这了,更多相关MySQL join查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • Mysql join连接查询的语法与示例

    连接查询: 是将两个查询(或表)的每一行,以"两两横同对接"的方式,所得到的所有行的结果,即一个表中的某行,跟另一个表中的某行.进行"横向对接",得到一个新行. 连接查询包括以下这些不同形式,连接方式: 交叉连接.内连接.外连接(分:左外连接,右外连接) 连接查询语法: select * from 表名 [连接方式] join 表名 [on 连接条件] where ...; 测试数据: mysql> select * from test; +----+----

  • MySQL中（JOIN/ORDER BY）语句的查询过程及优化方法

    MySQL中（JOIN/ORDER BY）语句的查询过程及优化方法

    在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子: 可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦. 在优化这个语句之前,我们先了解下SQL查询的基本执行过程: 1.应用通过MySQL API把查询命令发送给MySQL服务器,然后被解析 2.检查权限.MySQL optimizer进行优化,经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划(quer

  • MySQL联表查询基本操作之left-join常见的坑

    概述 对于中小体量的项目而言,联表查询是再常见不过的操作了,尤其是在做报表的时候.然而校对数据的时候,您发现坑了吗?本篇文章就 mysql 常用联表查询复现常见的坑. 基础环境 建表语句 DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_name` VARCHAR(50) DEFAULT NULL COMMENT '角色名', PRIMARY KEY (`i

  • mysql中各种常见join连表查询实例总结

    mysql中各种常见join连表查询实例总结

    本文实例讲述了mysql中各种常见join连表查询.分享给大家供大家参考,具体如下: 通常我们需要连接多个表查询数据,以获取想要的结果. 一.连接可以分为三类: (1) 内连接:join,inner join (2) 外连接:left join,left outer join,right join,right outer join,union,union all (3) 交叉连接:cross join 二.准备需要演示的表: CREATE TABLE `a` ( `id` int(11) uns

  • 浅谈为什么MySQL不推荐使用子查询和join

    做分页查询: 1.对于mysql,不推荐使用子查询和join是因为本身join的效率就是硬伤,一旦数据量很大效率就很难保证,强烈推荐分别根据索引单表取数据,然后在程序里面做join,merge数据. 2.子查询就更别用了,效率太差,执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,这里多了一个创建和销毁临时表的过程. 3.如果是JOIN的话,它是走嵌套查询的.小表驱动大表,且通过索引字段进行关联.如果表记录比较少的话,还是OK的.大的话业务逻

  • mysql高效查询left join和group by(加索引)

    mysql高效查询 mysql牺牲了group by来增加left join的速度(前提是加了索引). user表:10万数据 实例1: 200秒左右 SELECT U.id, A.favorite_count FROM (SELECT id from user) U LEFT JOIN ( -- 点赞数 SELECT favorite_by AS user_id, SUM(favorite_count) AS favorite_count FROM favorite GROUP BY favo

  • 解析MySQL join查询的原理

    解析MySQL join查询的原理

    MySQL用Nested-Loop Join算法实现join查询 区分驱动表和被驱动表,以驱动表的结果集为循环的基础,访问被驱动表过滤数据,然后合并结果,驱动表在外循环.被驱动表在内循环.如果还有第三张参与join查询的表,则以合并的结果为驱动表,第三张表作为被驱动表,以此类推. left join中的左表是驱动表.右表是被驱动表,right join刚好相反. Nested-Loop Join有三种实现 SNLJ Simple Nested-Loop Join 假设A是驱动表,B是被驱动表.

  • MySQL 连接查询的原理和应用

    概述 MySQL最强大的功能之一就是能在数据检索的执行中连接(join)表.大部分的单表数据查询并不能满足我们的需求,这时候我们就需要连接一个或者多个表,并通过一些条件过滤筛选出我们需要的数据. 了解MySQL连接查询之前我们先来理解下笛卡尔积的原理. 数据准备 依旧使用上节的表数据(包含classes 班级表和students 学生表): mysql> select * from classes; +---------+-----------+ | classid | classname |

  • 一文解析MySQL的MVCC实现原理

    一文解析MySQL的MVCC实现原理

    目录 1. 什么是MVCC 2. 事务的隔离级别 3. Undo Log(回滚日志) 4. MVCC的实现原理 4.1 当前读和快照读 4.2 隐藏字段 4.3 版本链 4.4 Read View(读视图) 5. 不同隔离级别下可见性分析 5.1 READ COMMITTED(读已提交) 5.2 REPEATABLE READ(可重复读) 1. 什么是MVCC MVCC全称是Multi-Version Concurrency Control(多版本并发控制),是一种并发控制的方法,通过维护一个数

  • 详解Mysql两表 join 查询方式

    详解Mysql两表 join 查询方式

    目录 一.SQL基本语法格式 二.3种join方式 1. left join(左连接) 2. right join(右连接) 3. inner join(内连接) 4. 在理解上面的三种join下,查询(A -  A∩B) 5. 查询 ( B - A∩B ) 6. 查询(A∪B - A∩B) 7. 查询 AUB 一.SQL基本语法格式 SELECT DISTINCT < select_list > FROM < left_table > < join_type > JO

  • MySQL中join查询的深入探究

    MySQL中join查询的深入探究

    目录 前引 索引对 join 查询的影响 数据准备 有索引查询过程 无索引查询过程 了解 Block Nested-Loop Join Block Nested-Loop Join查询过程 Join_buffer 如何正确的写出 join 查询 驱动表的选择 什么是小表 结论: 前引 相信大家 MySQL 都用了很久了,各种 join 查询天天都在写,但是 join 查询到底是怎么查的,怎么写才是最正确的,今天我就和大家一起学习探讨一下 索引对 join 查询的影响 数据准备 假设有两张表 t1

  • MySQL数据库连接查询 join原理

    目录 1.连接查询的分类 2.交叉连接 2.1.原理 2.2.基本语法 2.3.应用 3.内连接 3.1.原理 3.2.基本语法 3.3.应用 4.外连接 4.1.原理 4.2.基本语法 4.3.特点 4.4.应用 5.using关键字 5.1.原理 5.2.基本语法 1.连接查询的分类 交叉连接 内连接 外连接 左外链接(左连接) 右外连接(右连接) 自然连接 2.交叉连接 将两张表的数据与另外一张表彼此交叉 2.1.原理 笛卡尔积: 从第一张表一次取出每一条数据 取出每一条记录之后,与另外一

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

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

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

  • MySQL慢查询相关参数原理解析

    MySQL的慢查询,全名是慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句.具体环境中,运行时间超过long_query_time值的SQL语句,则会被记录到慢查询日志中.long_query_time的默认值为10,意思是记录运行10秒以上的语句.默认情况下,MySQL数据库并不启动慢查询日志,需要手动来设置这个参数. 当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响. 此外,慢查询日志支持将日志记录写入文

  • 深入解析MySQL索引的原理与优化策略

    目录 索引的概念 索引的原理 索引的类型 索引的使用 索引的使用方式 注意事项 索引优化技巧 索引的概念 MySQL索引是一种用于加速数据库查询的数据结构,它类似于书籍的目录,能够快速指导我们找到需要的信息.MySQL索引可以根据一定的算法和数据结构进行排序和存储,从而实现高效的数据查找和访问.在数据库中,索引可以加速数据的查询和更新操作,提高系统性能. MySQL支持多种索引类型,常见的包括B-tree索引.哈希索引和全文索引等.其中,B-tree索引是最常用的一种,它是一种平衡树结构,可以将

  • MYSQL子查询和嵌套查询优化实例解析

    查询游戏历史成绩最高分前100 Sql代码 SELECT ps.* FROM cdb_playsgame ps WHERE ps.credits=(select MAX(credits) FROM cdb_playsgame ps1 where ps.uid=ps1.uid AND ps.gametag=ps1.gametag) AND ps.gametag='yeti3' GROUP BY ps.uid order by ps.credits desc LIMIT 100; Sql代码 SEL

随机推荐