神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(一)

  • 时间:
  • 浏览:3
  • 来源:幸运快3_快3遗漏_幸运快3遗漏

开心一刻

  我:嗨,老板娘,有冰红茶没

  老板娘:有

  我:2个钱一瓶

  老板娘:3块

  我:给我来一瓶,给,3块

  老板娘:来,你的冰红茶

  我:玩呐,我须要冰红茶,你给我个瓶口干哈?

  老板娘:这是再来一瓶,他家卖完了,你去他家换一下

什么的问题背景

  对于 MySQL 的 JOIN,告诉我你们都你们都 有这么 去想过他的执行流程,亦或有这么 怀疑过买车人的理解(自信满满的自我认为!);为什么会么会你们都都你们都 告诉我为什么会检验,能能试着回答如下的什么的问题

  驱动表的选者

    MySQL 会怎么能能选者驱动表,按从左至右的顺序选者第另另一个?

  多表连接的顺序

    假设你们都你们都 有 3 张表:A、B、C,和如下 SQL

-- 伪 SQL,这么

直接执行
A LEFT JOIN B ON B.aId = A.id
LEFT JOIN C ON C.aId = A.id
WHERE A.name = '666' AND B.state = 1 AND C.create_time > '2019-11-22 12:12:400'

    是 A 和 B 联表防止完曾经的结果再和 C 进行联表防止,还是 A、B、C 一齐联表曾经再进行过滤防止 ,还是说这有五种全版前会对,有或多或少的防止法子 ?

  ON、WHERE 的生效时机

    楼主无意之间逛到了一篇博文,它底下有如下介绍

正经图1 摘自 Mysql - JOIN详解

    看多五种 ,楼主第一时间有发现新大陆的感觉,曾经 JOIN 的执行顺序是曾经的(全版前会颠覆了楼主曾经的认知,为什么会么会让楼主曾经就没想过五种 什么的问题,什么都有种新技能获取的满足),可底下越想越不对,感觉像是学错了技能(6级没学大!)

    为什么会么会让两表各有几百上千万的数据,那这两张表做笛卡尔积,结果不敢想象!也什么都说 正经图1 中的顺序还有待商榷,ON 和 WHERE 的生效时间全版前会待商榷

  为什么会么会我想要对上述什么的问题都了如指掌,那请你走开,别妨碍我装逼;为什么会么会我想要对上述什么的问题还全版前会很糙清楚,这么 请坐好,我须要开始装逼了

前提准备

  正式开讲曾经了,先给你们都你们都 入党入党积极分子或多或少花生、瓜子和啤酒,装逼就得有装逼的氛围,不然为什么会看的下去,是我不好是吧 ?(楼主,你个骗子,货了?)

  驱动表

    何谓驱动表,指多表关联查询时,第另另一个被防止的表,亦可称之为基表,为什么会么会让再使用此表的记录去关联或多或少表。驱动表的选者遵循另另一个原则:在对最终结果集没影响的前提下,优先选者结果集大约的那张表作为驱动表。五种 原则说的不好懂,结果集大约,五种 是我不好你们都你们都 能估出来,但对最终结果集不影响,五种 就不好判断了,难归难,但还是有一定规律的:

LEFT JOIN 一般以左表为驱动表(RIGHT JOIN一般则是右表 ),INNER JOIN 一般以结果集少的表为驱动表,为什么会么会让还实在有什么的问题,则可用 EXPLAIN 来找驱动表,其结果的第一张表即是驱动表。

你造 EXPLAIN 就一定准吗 ? 执行计划在真正执行的曾经是为什么会么会让改变的! 绝大2个情形下是适用的,很糙是 EXPLAIN

    LEFT JOIN 或多或少情形下会被查询优化器优化成 INNER JOIN;结果集指的是表中记录过滤后的结果,而全版前会表中的所有记录,为什么会么会让无过滤条件则是表中所有记录

    更多信息可查看:Mysql多表连接查询的执行细节(一)

  SQL 执行的流程图

    你们都都你们都 向 MySQL 发送另另一个请求的曾经,MySQL 到底做了些了那先

 SQL 执行路径,摘自《高性能MySQL》

    能能看多,执行计划是查询优化器的输出结果,执行引擎根据执行计划来查询数据

  数据准备

    MySQL 5.7.1,InnoDB 引擎;建表 SQL 和 数据初始 SQL

-- 表创建与数据初始化
DROP TABLE IF EXISTS tbl_user;
CREATE TABLE tbl_user (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  user_name VARCHAR(400) NOT NULL COMMENT '用户名',
  sex TINYINT(1) NOT NULL COMMENT '性别, 1:男,0:女',
  create_time datetime NOT NULL COMMENT '创建时间',
  update_time datetime NOT NULL COMMENT '更新时间',
    remark VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (id)
) COMMENT='用户表';

DROP TABLE IF EXISTS tbl_user_login_log;
CREATE TABLE tbl_user_login_log (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  user_name VARCHAR(400) NOT NULL COMMENT '用户名',
  ip VARCHAR(15) NOT NULL COMMENT '登录IP',
  client TINYINT(1) NOT NULL COMMENT '登录端, 1:android, 2:ios, 3:PC, 4:H5',
  create_time datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (id)
) COMMENT='登录日志';
INSERT INTO tbl_user(user_name,sex,create_time,update_time,remark) VALUES
('何天香',1,NOW(), NOW(),'朗眉星目,一表人材'),
('薛沉香',0,NOW(), NOW(),'天星楼的总楼主薛摇红的女儿,也是天星楼的少总楼主,体态丰盈,乌发飘逸,指若春葱,袖臂如玉,风姿卓然,高贵典雅,人称“天星绝香”的武林第一大美女'),
('慕容兰娟',0,NOW(), NOW(),'武林东南西北四大世家之北世家慕容长明的独生女儿,生得玲珑剔透,粉雕玉琢,脾气却是刚烈无比,又喜着火红,什么都人送绰号“火凤凰”,是除天星楼薛沉香之外的武林第二大美女'),
('苌婷',0,NOW(), NOW(),'当今皇上最宠爱的侄女,北王府的郡主,腰肢纤细,遍体罗绮,眉若墨画,唇点樱红;虽无沉香之雅重,兰娟之热烈,却别现出有五种空灵'),
('柳含姻',0,NOW(), NOW(),'武林四绝之一的添愁仙子董婉婉的徒弟,体态窈窕,姿容秀丽,真个是秋水为神玉为骨,芙蓉如面柳如腰,眉若墨画,唇若点樱,不弱西子半分,更胜玉环一筹; 摇红楼、听雨轩,琵琶一曲值千金!'),
('李凝雪',0,NOW(), NOW(),'李相国的女儿,神采奕奕,英姿飒爽,爱憎分明'),
('周遗梦',0,NOW(), NOW(),'音神传人,湘妃竹琴的拥有者,云髻高盘,穿了一身黑色蝉翼纱衫,愈实在冰肌玉骨,粉面樱唇,格外娇艳动人'),
('叶留痕',0,NOW(), NOW(),'圣域圣女,肤白如雪,白衣飘飘,宛如仙女一般,微笑中带着说没了的柔和之美'),
('郭疏影',0,NOW(), NOW(),'扬灰右使的徒弟,秀发细眉,玉肌丰滑,娇润脱俗'),
('钟钧天',0,NOW(), NOW(),'天界,玄天九部 - 钧天部的部主,超凡脱俗,仙气逼人'),
('王雁云',0,NOW(), NOW(),'尘缘山庄二小姐,刁蛮任性'),
('许侍霜',0,NOW(), NOW(),'药王谷谷主女儿,医术高明'),
('冯黯凝',0,NOW(), NOW(),'桃花门门主,娇艳如火,千娇百媚');
INSERT INTO tbl_user_login_log(user_name, ip, client, create_time) VALUES
('薛沉香', '10.53.56.78',2, '2019-10-12 12:23:45'),
('苌婷', '10.53.56.78',2, '2019-10-12 22:23:45'),
('慕容兰娟', '10.53.56.12',1, '2018-08-12 22:23:45'),
('何天香', '10.53.56.12',1, '2019-10-19 10:23:45'),
('柳含姻', '198.11.132.198',2, '2018-05-12 22:23:45'),
('冯黯凝', '198.11.132.198',2, '2018-11-11 22:23:45'),
('周遗梦', '198.11.132.198',2, '2019-06-18 22:23:45'),
('郭疏影', '220.181.38.148',3, '2019-10-21 09:45:56'),
('薛沉香', '220.181.38.148',3, '2019-10-26 22:23:45'),
('苌婷', '104.69.1400.400',4, '2019-10-12 10:23:45'),
('王雁云', '104.69.1400.61',4, '2019-10-16 20:23:45'),
('李凝雪', '104.69.1400.62',4, '2019-10-17 20:23:45'),
('许侍霜', '104.69.1400.63',4, '2019-10-18 20:23:45'),
('叶留痕', '104.69.1400.64',4, '2019-10-19 20:23:45'),
('王雁云', '104.69.1400.65',4, '2019-10-20 20:23:45'),
('叶留痕', '104.69.1400.66',4, '2019-10-21 20:23:45');

SELECT * FROM tbl_user;
SELECT * FROM tbl_user_login_log;
View Code

  单表查询

    单表查询的过程比较好理解,大致如下

    关于单表查询就不细讲了,主要涉及到:聚簇索引,覆盖索引、回表操作,知道这 3 点,上图就好理解了(告诉我的赶快去查资料,暴露了就丢人了!)。

联表算法

  MySQL 的联表算法是基于嵌套循环算法(nested-loop algorithm)而衍生出来的一系列算法,根据不同条件而选者不同的算法

在使用索引关联的情形下,有 Index Nested-Loop join 和 Batched Key Access join 有五种算法;
在未使用索引关联的情形下,有 Simple Nested-Loop join 和 Block Nested-Loop join 有五种算法;  

  Simple Nested-Loop

    简单嵌套循环,简称 SNL;逐条逐条匹配,就像曾经

    五种 算法简单粗暴,但毫无性能可言,时间性能上来说是 n(表中记录数) 的 m(表的数量) 次方,什么都 MySQL 做了优化,联表查询的曾经不要再突然出现五种 算法,即使在无 WHERE  条件且 ON 的连接键上无索引时,什么都会选者五种 算法

  Block Nested-Loop

    缓存块嵌套循环连接,简称 BNL,是对 INL 的有五种优化;一次性缓存多条驱动表的数据到 Join Buffer,为什么会么会让拿 Join Buffer 里的数据批量与内层循环读取的数据进行匹配,就像曾经

    将内部管理循环中读取的每一行与缓冲区中的所有记录进行比较,曾经就能能减少内层循环的读表次数。举个例子,为什么会么会让这么 Join Buffer,驱动表有 400 条记录,被驱动表有 400 条记录,这么 内层循环的读表次数应该是 400 * 400 = 4000,为什么会么会让 Join Buffer 可用并能能存 10 条记录(Join Buffer 存储的是驱动表中参与查询的列,包括 SELECT 的列、ON 的列、WHERE 的列,而全版前会驱动表中整行整行的全版记录),这么 内层循环的读表次数应该是 400 / 10 * 400 = 400,被驱动表须要读取的次数减少了另另一个数量级。

    当被驱动表在连接键上无索引且被驱动表在 WHERE 过滤条件上也没索引时,常常会采用此种算法来完成联表,如下所示

  Index Nested-Loop

    索引嵌套循环,简称 INL,是基于被驱动表的索引进行连接的算法;驱动表的记录逐条与被驱动表的索引进行匹配,防止和被驱动表的每条记录进行比较,减少了对被驱动表的匹配次数,大致流程如下图

    你们都你们都 来看看实际案例,先给 tbl_user_login_log 加带索引 ALTER TABLE tbl_user_login_log ADD INDEX idx_user_name (user_name); ,你们都你们都 再来看联表执行计划

     能能看多 tbl_user_login_log 的索引生效了,你们都你们都 再往下看

     有趣的事地处了,驱动表变成了 tbl_user_login_log ,而 tbl_user 成了被驱动表, tbl_user_login_log 走索引过滤后得到结果集,再通过 BNL 算法将结果集与 tbl_user 进行匹配。这实在是 MySQL进行了优化,为什么会么会让 tbl_user_login_log 走索引过滤后得到的结果集比 tbl_user 记录数要少,什么都选者了 tbl_user_login_log 作为驱动表,底下的也就理所当然了,是全版前会感觉 MySQL 好强大?

  Batched Key Access

    批量key访问,简称 BKA,是对 INL 算法的有五种优化;

    BKA 对 INL 的优化累似 于 BNL 对 SNL 的优化,但又有不同; 鉴于篇幅原困,BKA 你们都你们都 倒入下期讲解,希望各位老哥见谅!实在是不行,你来打我呀!

总结

  1、驱动表的选者有它的一套算法,有兴趣的能能去专研下;比较靠谱的选者法子是用 EXPLAIN

  2、联表顺序,全版前会两两联合曾经,再去联合第三张表,什么都驱动表的四根记录穿到底,匹配完所有关联表曾经,再取驱动表的下四根记录重复联表操作;

  3、MySQL 的连接算法基于嵌套循环算法,基于不同的情形而采用不同的衍生算法

  4、关于 ON 和 WHERE,你们都你们都 下篇全版讲解,你们都你们都 能能先考虑下它们的区别,以及生效时间

参考

  Mysql多表连接查询的执行细节(一)