MySQL中Nested-Loop Join算法小结

不知不觉的玩了两年多的MySQL，发现很多人都说MySQL对比Oracle来说，优化器做的比较差，其实某种程度上来说确实是这样，但是毕竟MySQL才到5.7版本，Oracle都已经发展到12c了，今天我就看了看MySQL的连接算法，嗯，现在来说还是不支持Hash Join，只有Nested-Loop Join，那今天就总结一下我学习的心得吧。

Nested-Loop Join基本算法实现，伪代码是这样：

for each row in t1 matching range {
 for each row in t2 matching reference key {
  for each row in t3 {
   if row satisfies join conditions,
   send to client
  }
 }
}

这段代码很简单，虽然我也不怎么会写代码，但是我还是看得懂的。这里假设有三张表，t1, t2, t3，这段代码，分别会展现出explain计划里的range, ref和ALL，表现在SQL执行计划层里，t3就会进行一次全表扫描，我今天在这个地方看到了一个很妖的优化SQL方法，Straight-join：http://hidba.ga/2014/09/26/join-query-in-mysql/，其中提到了驱动表的概念，那么对应过来，驱动表就是伪代码里的t3表，博文里说MySQL会自动选择结果集最小的表作为驱动表，作为算法分析，这样选择驱动表确实是消耗最小的办法。那么这里还提到了，通过缩小驱动表结果集进行连接优化，那么根据这个算法来看，结果集较小的驱动表确实可以使循环次数减少。

当然了，MySQL自己在这个算法基础上，演进出了Block Nested-Loop join算法，其实基本上和上面的算法没有区别，伪代码如下：

for each row in t1 matching range {
 for each row in t2 matching reference key {
  store used columns from t1, t2 in join buffer
  if buffer is full {
   for each row in t3 {
    for each t1, t2 combination in join buffer {
     if row satisfies join conditions,
     send to client
    }
   }
   empty buffer
  }
 }
}

if buffer is not empty {
 for each row in t3 {
  for each t1, t2 combination in join buffer {
   if row satisfies join conditions,
   send to client
  }
 }
}

这个算法，将外层循环的数据缓存在join buffer中，内层循环中的表回合buffer中的数据进行对比，从而减少循环次数，这样便可以提高效率。官网上有个example，我有点没有看明白：如果有10行被缓存到了buffer里，这10行被传给了内层循环，内层循环的所有行都会和buffer中的这10行进行对比。原文是这样的：

For example, if 10 rows are read into a buffer and the buffer is passed to the next inner loop, each row read in the inner loop can be compared against all 10 rows in the buffer
      如果S指的是t1, t2组合在缓存中的大小，C是这些组合在buffer中的数量，那么t3表被扫描的次数应该是：

(S * C)/join_buffer_size + 1

根据这个算式，join_buffer_size越大，扫描的次数越小，如果join_buffer_size到了能缓存所有之前的行组合，那么这时就是性能最好的时候，之后再增大也就没有什么效果了。

在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，因为那个算法太粗暴，不忍直视。数据量大些的复杂SQL估计几年都可能跑不出结果，如果你不信，那就是too young too simple。或者Inside君可以给你些SQL跑跑看。

Simple Nested-Loop Join算法的缺点在于其对于内表的扫描次数太多，从而导致扫描的记录太过庞大。Block Nested-Loop Join算法较Simple Nested-Loop Join的改进就在于可以减少内表的扫描次数，甚至可以和Hash Join算法一样，仅需扫描内表一次。

赞 (0)

相关推荐

  • MySQL JOIN之完全用法

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

  • 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 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 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 left join,right join,inner join用法分析

    下面是例子分析表A记录如下: aID        aNum 1           a20050111 2           a20050112 3           a20050113 4           a20050114 5           a20050115 表B记录如下: bID        bName 1            2006032401 2           2006032402 3           2006032403 4           20

  • 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中的7种日志小结

    mysql中的7种日志小结

    MySQL中有以下日志文件,分别是: 1:重做日志(redo log) 2:回滚日志(undo log) 3:二进制日志(binlog) 4:错误日志(errorlog) 5:慢查询日志(slow query log) 6:一般查询日志(general log) 7:中继日志(relay log) 其中重做日志和回滚日志与事务操作息息相关,二进制日志也与事务操作有一定的关系,这三种日志,对理解MySQL中的事务操作有着重要的意义. 一.重做日志(redo log) 作用: 确保事务的持久性.re

  • MySQL中Nested-Loop Join算法小结

    不知不觉的玩了两年多的MySQL,发现很多人都说MySQL对比Oracle来说,优化器做的比较差,其实某种程度上来说确实是这样,但是毕竟MySQL才到5.7版本,Oracle都已经发展到12c了,今天我就看了看MySQL的连接算法,嗯,现在来说还是不支持Hash Join,只有Nested-Loop Join,那今天就总结一下我学习的心得吧. Nested-Loop Join基本算法实现,伪代码是这样: for each row in t1 matching range { for each r

  • mysql中coalesce()的使用技巧小结

    前言 最近无意间发现mysql的coalesce,又正好有时间,就把mysql中coalesce()的使用技巧总结下分享给大家,下面来一起看看详细的介绍: coalesce()解释 返回参数中的第一个非空表达式(从左向右依次类推): 使用示例 a,b,c三个变量. select coalesce(null,2,3); // Return 2 select coalesce(null,null,3); // Return 3 select coalesce(1,2,3); // Return 1

  • MySQL中union和join语句使用区别的辨析教程

    MySQL中union和join语句使用区别的辨析教程

    union和join是需要联合多张表时常见的关联词,具体概念我就不说了,想知道上网查就行,因为我也记不准确. 先说差别:union对两张表的操作是合并数据条数,等于是纵向的,要求是两张表字段必须是相同的(Schema of both sides of union should match.).也就是说如果A表中有三条数据,B表中有两条数据,那么A union B就会有五条数据.说明一下union 和union all的差别,对于union如果存在相同的数据记录会被合并,而union all不会合

  • 查询mysql中执行效率低的sql语句的方法

    一些小技巧1. 如何查出效率低的语句?在MySQL下,在启动参数中设置 --log-slow-queries=[文件名],就可以在指定的日志文件中记录执行时间超过long_query_time(缺省为10秒)的SQL语句.你也可以在启动配置文件中修改long query的时间,如: 复制代码 代码如下: # Set long query time to 8 seconds    long_query_time=8 2. 如何查询某表的索引?可使用SHOW INDEX语句,如: 复制代码 代码如下

  • MySQL中join查询的深入探究

    MySQL中join查询的深入探究

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

  • MySQL中join语句怎么优化

    目录 Simple Nested-Loop Join Block Nested-Loop Join Index Nested-Loop Join 如何选择驱动表? Simple Nested-Loop Join 我们来看一下当进行 join 操作时,mysql是如何工作的.常见的 join 方式有哪些? 如图,当我们进行连接操作时,左边的表是驱动表,右边的表是被驱动表 Simple Nested-Loop Join 这种连接操作是从驱动表中取出一条记录然后逐条匹配被驱动表的记录,如果条件匹配则将

  • Mysql中的join操作

     join的类型 1．  内联结:将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结. 2．  外联结:分为外左联结和外右联结. 案例背景 create table java (name varchar(255)); insert into java values ('java1'),('java2'),('blue'); create table mysql (name varchar(255)); insert into mysql values ('mysql1'),('m

  • 在MySQL中使用JOIN语句进行连接操作的详细教程

    到目前,我们已经学习了从一个表中获取数据.这是简单的需要,但在大多数现实MySQL的使用,经常需要将数据从多个表中的一个单一的查询. 可以使用多个表中的单一SQL查询.在MySQL中联接(join)行为是指两个或多个表到一个表中可以使用连接在SELECT,UPDATE和DELETE语句中加入MySQL表.我们将看到一个例子LEFT JOIN简单的MySQL连接. 在命令提示符使用联接: 假设我们两个表的教程tcount_tbl和tutorials_tbl的完整列表如下: 例子: 试试下面的例子:

随机推荐