浅谈mysql join底层原理

目录
  • join算法
  • 驱动表和非驱动表的区别
    • 1、Simple Nested-Loop Join，简单嵌套-无索引的情况
    • 2、Index Nested-Loop Join-有索引的情况
    • 3、Block Nested-Loop Join ，join buffer缓冲区
  • 缓冲区大小
  • 数据量大的表和数据量小的表如何选择连接顺序
  • 细节

join算法

mysql只支持一种join算法：Nested-Loop Join（嵌套循环连接），但Nested-Loop Join有三种变种：

  • Simple Nested-Loop Join，简单嵌套
  • Index Nested-Loop Join，索引嵌套
  • Block Nested-Loop Join ，join buffer缓冲区嵌套（临时表）

驱动表和非驱动表的区别

驱动表就是主表，非驱动表就是从表，看以下sql就知道了，A就是驱动表，B就是非驱动表。

select * from A left join B

A join B 连表时，一定先查A表再查B表吗？

答案是不一定，因为mysql内部有一个优化器，它会根据你查询语句做一些优化，先查哪张表也是由优化器决定的，但可以肯定的是，先查询的那张表就是驱动表，反之就是非驱动表；关于是那张表先查的问题，我们通过看执行计划来得出结果；在前面加上explain关键字即可;

explain select * from A join B;

1、Simple Nested-Loop Join，简单嵌套-无索引的情况

A left join B： ，每次都是全表匹配，A表的每行数据都全表匹配一次B表，也就是说，假如我A表有10条数据，B表有1000条数据，那么查询的时候扫描次数就是10*1000，也就说查询时需要扫描10000遍才能得出数据；

2、Index Nested-Loop Join-有索引的情况

select * from A join B on A.id=B.id where A.id = 1：在查询时，驱动表A会根据关联字段的索引进行查找，当在索引上找到符合的值，在回表进行查询，也就是说得匹配到索引后才会才会进行回表查询；
如果非驱动表B的关联关联字段B.id是主键的话，性能会非常高，如果不是主键，会进行多次回表查询，先关联索引，然后根据二级索引的主键id进行回表查询，性能上比主键要慢；

3、Block Nested-Loop Join ，join buffer缓冲区

如果有索引，会选取Index Nested-Loop Join进行连表，如果join列没有索引，就会采用Block Nested-Loop Join ，join buffer，在驱动表和非驱动表中间有个buffer的缓冲区，在查询时先将驱动表的数据缓存到buffer缓冲区内，然后批量与非驱动表进行匹配，这是一种将多次比较合并为一次比较的优化方案，注意：这里缓存的不只是关联表的列，select 后面的列也会缓存起来；

缓冲区大小

默认情况下buffer缓冲区join_biffer_size的容量为256k，如果说你的数据空间大于256k，就无法使用缓冲区了，转为最简单的循环嵌套Simple Nested-Loop Join，但是我们可以手动调整缓冲区大小来装入大容量的数据；查看join_biffer_size的sql：show variables like '%join_biffer_size%'

数据量大的表和数据量小的表如何选择连接顺序

最好由小表去连接大表，这样会减少扫描次数；比如大表有1000条数据，小表只有10条数据，那么最好的连接方式为：小表 join 大表；为什么要这么做呢？

  • 如果是大表 join 小表，假如我们的数据在大表的第999行，那么查询数据的时候就至少得扫描999次才能查出来；
  • 如果是小表 join 大表，假如我们的数据在小表的第9行，

细节

  • 连表查询的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
  • 优先使用内连接来连表，外连接查询数据消耗的性能比内连接要高；
  • 确保关联查询中on 后面的列或者 using()中的字段带有索引，连表时可加快数据访问

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

赞 (0)

相关推荐

  • 浅析Mysql Join语法以及性能优化

    浅析Mysql Join语法以及性能优化

    一．Join语法概述 join 用于多表中字段之间的联系,语法如下: 复制代码 代码如下: ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1:左表:table2:右表. JOIN 按照功能大致分为如下三类: INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录. LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录. RIGHT JOIN

  • MySQL JOIN之完全用法

    外联接.外联接可以是左向外联接.右向外联接或完整外部联接. 在 FROM 子句中指定外联接时,可以由下列几组关键字中的一组指定: LEFT JOIN 或 LEFT OUTER JOIN. 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅 是联接列所匹配的行.如果左表的某行在右表中没有匹配行,则在相关联的结果集行中 右表的所有选择列表列均为空值. RIGHT JOIN 或 RIGHT OUTER JOIN. 右向外联接是左向外联接的反向联接.将返回右表的所有行.如果

  • mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

    NOT IN.JOIN.IS NULL.NOT EXISTS效率对比 语句一:select count(*) from A where A.a not in (select a from B) 语句二:select count(*) from A left join B on A.a = B.a where B.a is null 语句三:select count(*) from A where not exists (select a from B where A.a = B.a) 知道以上三

  • 深入理解mysql之left join 使用详解

    ON 子句与 WHERE 子句的不同    一种更好地理解带有 WHERE ... IS NULL 子句的复杂匹配条件的简单方法    Matching-Conditions 与 Where-conditions 的不同 关于 "A LEFT JOIN B ON 条件表达式" 的一点提醒 ON 条件("A LEFT JOIN B ON 条件表达式"中的ON)用来决定如何从 B 表中检索数据行. 如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列

  • MySQL Left JOIN时指定NULL列返回特定值详解

    coalesce 函数可以接受多个参数,将会返回这些参数中第一个非NULL的值,若提供的参数全部为NULL,则返回NULLifnull 函数和coalesce功能一样,只是只可以接受两个参数if  函数接受三个参数,实现类似于三元判断符(?:)的功能,即第一个参数不为NULL且不为0时,返回第二个参数,否则返回第三个参数 复制代码 代码如下: SELECT a.*,coalesce(t.cous,0) as count FROM brand as a left join (select bran

  • MYSQL使用inner join 进行 查询/删除/修改示例

    复制代码 代码如下: --查询 SELECT tp.tp_id, tp.tpmc, tp.leveid, tp.tpdz, tp.jgm, tp.scsj, tp.pbzyid, tp.ksbfsj, tp.jsbfsj, tp.status, tp.tpbz FROM qdgl_tupian tp INNER JOIN qdgl_pqb pq ON tp.tp_id=pq.tpid WHERE pq.bfjgm='27010825' AND ps_bfsj >= '2013-01' AND p

  • MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍

    不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低. 情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team ( id int primary key, tname varchar(100) ); create table t_people (

  • mysql多表join时候update更新数据的方法

    sql语句: 复制代码 代码如下: update item i,resource_library r,resource_review_link l set i.name=CONCAT('Review:',r.resource_name) where i.item_id=l.instance_id and l.level='item' and r.resource_id=l.resource_id and i.name='' JOIN UPDATE & JOIN DELETE 复制代码 代码如下:

  • 深入理解mysql的自连接和join关联

    深入理解mysql的自连接和join关联

    一.mysql自连接 mysql有时在信息查询时需要进行对自身连接(自连接),所以我们需要为表定义别名.我们举例说明,下面是商品采购表,我们需要找到采购价格比惠惠高的所有信息. 一般情况我们看到这张表我们第一时间用语句进行操作: SELECT * FROM shoping WHERE price>27 可想而知,这是有多么简单,假设你并不知道数据库表详细数据或者数据量相当庞大呢?作为一个数据库管理员,我们就要用别的方式迅速找出所需要的数据. 分步查询 最简单的一种方式,也是最容易想到操作: SE

  • 浅谈mysql join底层原理

    浅谈mysql join底层原理

    目录 join算法 驱动表和非驱动表的区别 1.Simple Nested-Loop Join,简单嵌套-无索引的情况 2.Index Nested-Loop Join-有索引的情况 3.Block Nested-Loop Join ,join buffer缓冲区 缓冲区大小 数据量大的表和数据量小的表如何选择连接顺序 细节 join算法 mysql只支持一种join算法:Nested-Loop Join(嵌套循环连接),但Nested-Loop Join有三种变种: Simple Nested

  • 浅谈MySQL使用笛卡尔积原理进行多表查询

    MySQL的多表查询(笛卡尔积原理) 先确定数据要用到哪些表. 将多个表先通过笛卡尔积变成一个表. 然后去除不符合逻辑的数据(根据两个表的关系去掉). 最后当做是一个虚拟表一样来加上条件即可. 注意:列名最好使用表别名来区别. 笛卡尔积 Demo: 左,右连接,内,外连接 l 内连接: 要点:返回的是所有匹配的记录. select * from a,b where a.x = b.x ////内连接 l 外连接有左连接和右连接两种. 要点:返回的是所有匹配的记录 外加 每行主表外键值为null的

  • 浅谈一下mysql数据库底层原理

    1.数据库事务的基本特性. 原子性: 事务中的所有操作要么全部提交成功,要么全部失败回滚. 场景:UPDATE cs_user SET age = 18 , gender = '女' WHERE id = 4.要么全部更新要么更新失败,不会出现age更新成功,gender更新失败. 一致性: 据库总是从给一个一致性的状态转换到另一个一致性的状态. 场景:比如规定某个表的字段age大于等于12小于18时,字段type为青少年,而数据库中存在age=16的时候,type='儿童'. 隔离性: 一个事

  • 浅谈MySQL之浅入深出页原理

    浅谈MySQL之浅入深出页原理

    一.页的概览 我们往 MySQL 插入的数据最终都是存在页中的.在 InnoDB 中的设计中,页与页之间是通过一个双向链表连接起来. 而存储在页中的一行一行的数据则是通过单链表连接起来的. 上图中的 User Records 的区域就是用来存储行数据的.那 InnoDB 为什么要这么设计?假设我们没有页这个概念,那么当我们查询时,成千上万的数据要如何做到快速的查询出结果?众所周知,MySQL 的性能是不错的,而如果没有页,我们剩下的只能是逐条逐条的遍历数据了. 那页是如何做到快速查询的呢?在当前

  • 浅谈MySQL 统计行数的 count

    MySQL count() 函数我们并不陌生,用来统计每张表的行数.但如果你的表越来越大,且是 InnoDB 引擎的话,会发现计算的速度会越来越慢.在这篇文章里,会先介绍 count() 实现的原理及原因,然后是 count 不同用法的性能分析,最后给出需要频繁改变并需要统计表行数的解决方案. Count() 的实现 InnoDB 和 MyISAM 是 MySQL 常用的数据引擎,由于两者实现的不同,导致 count() 操作计算的效率也不同. 对于 MyISAM 来说,它把每个表的总行数都存在

  • 浅谈mysql执行过程以及顺序

    浅谈mysql执行过程以及顺序

    前言:mysql在我们的开发中基本每天都要面对的,作为开发中的数据的来源,mysql承担者存储数据和读写数据的职责.因为学习和了解mysql是至关重要的,那么当我们在客户端发起一个sql到出现详细的查询数据,这其中究竟经历了什么样的过程?mysql服务端是如何处理请求的,又是如何执行sql语句的?本篇博客将来探讨这个问题: 一:mysql执行过程 mysql整体的执行过程如下图所示: 1.1:连接器 连接器的主要职责就是: ①负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向

  • 浅谈mysql的timestamp存在的时区问题

    浅谈mysql的timestamp存在的时区问题

    目录 简介 基本概念 timestamp与datetime区别 为什么网上又说timestamp类型存在时区问题? 那为什么网上会说timestamp存在时区问题? serverTimezone的本质 将serverTimezone与mysql时区保持一致 Entity中日期属性是String呢? 最佳实践 数据库中用timestamp还是int来存储时间? 总结 简介 众所周知,mysql中有两个时间类型,timestamp与datetime,但当在网上搜索timestamp与datetime

  • 浅谈MySQL和Lucene索引的对比分析

    浅谈MySQL和Lucene索引的对比分析

    MySQL和Lucene都可以对数据构建索引并通过索引查询数据,一个是关系型数据库,一个是构建搜索引擎(Solr.ElasticSearch)的核心类库.两者的索引(index)有什么区别呢?以前写过一篇<Solr与MySQL查询性能对比>,只是简单的对比了下查询性能,对于内部原理却没有解释,本文简单分析下两者的索引区别. MySQL索引实现 在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式. M

  • 浅谈mysql中多表不关联查询的实现方法

    大家在使用MySQL查询时正常是直接一个表的查询,要不然也就是多表的关联查询,使用到了左联结(left join).右联结(right join).内联结(inner join).外联结(outer join).这种都是两个表之间有一定关联,也就是我们常常说的有一个外键对应关系,可以使用到 a.id = b.aId这种语句去写的关系了.这种是大家常常使用的,可是有时候我们会需要去同时查询两个或者是多个表的时候,这些表又是没有互相关联的,比如要查user表和user_history表中的某一些数据

  • 浅谈MySQL在cmd和python下的常用操作

    环境配置1:安装mysql,环境变量添加mysql的bin目录 环境配置2:python安装MySQL-Python 请根据自身操作系统下载安装,否则会报c ++ compile 9.0,import _mysql等错误 windows10 64位操作系统可到 http://www.lfd.uci.edu/~gohlke/pythonlibs/ 下载安装MySQL-Python包,至于whl和tar.gz在windows和Linux下的安装方法可查看我的上一篇文章 一 .cmd命令下的操作: 连

随机推荐