1. 首页
  2. IT资讯

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

“u003Ch1 class=”pgc-h-arrow-right”u003E问题背景u003Cu002Fh1u003Eu003Cpu003E对于 MySQL 的 JOIN,不知道大家有没有去想过他的执行流程,亦或有没有怀疑过自己的理解(自信满满的自我认为!);如果大家不知道怎么检验,可以试着回答如下的问题u003Cu002Fpu003Eu003Ch1 class=”pgc-h-arrow-right”u003E驱动表的选择u003Cu002Fh1u003Eu003Cpu003EMySQL 会如何选择驱动表,按从左至右的顺序选择第一个?u003Cu002Fpu003Eu003Ch1 class=”pgc-h-arrow-right”u003E多表连接的顺序u003Cu002Fh1u003Eu003Cpu003E假设我们有 3 张表:A、B、C,和如下 SQLu003Cu002Fpu003Eu003Cpreu003E– 伪 SQL,不能直接执行u003Cbru003EA LEFT JOIN B ON B.aId = A.idu003Cbru003ELEFT JOIN C ON C.aId = A.idu003Cbru003EWHERE A.name = '666' AND B.state = 1 AND C.create_time > '2019-11-22 12:12:30'u003Cu002Fpreu003Eu003Cpu003E是 A 和 B 联表处理完之后的结果再和 C 进行联表处理,还是 A、B、C 一起联表之后再进行过滤处理 ,还是说这两种都不对,有其他的处理方式 ?u003Cu002Fpu003Eu003Ch1 class=”pgc-h-arrow-right”u003EON、WHERE 的生效时机u003Cu002Fh1u003Eu003Cpu003E楼主无意之间逛到了一篇博文,它里面有如下介绍u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002F24431c3a307b47ceadce27cea86c8cf0″ img_width=”980″ img_height=”646″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003E 摘自 Mysql – JOIN详解u003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E看完这个,楼主第一时间有发现新大陆的感觉,原来 JOIN 的执行顺序是这样的,可后面越想越不对,感觉像是学错了技能u003Cu002Fpu003Eu003Cpu003E如果两表各有几百上千万的数据,那这两张表做笛卡尔积,结果不敢想象!也就是说 正经图1 中的顺序还有待商榷,ON 和 WHERE 的生效时间也有待商榷u003Cu002Fpu003Eu003Cpu003E如果你对上述问题都了如指掌,那请你走开,别妨碍我装逼;如果你对上述问题还不是特别清楚,那么请坐好,我要开始装逼了u003Cu002Fpu003Eu003Ch1 class=”pgc-h-arrow-right”u003E驱动表u003Cu002Fh1u003Eu003Cpu003E何谓驱动表,指多表关联查询时,第一个被处理的表,亦可称之为基表,然后再使用此表的记录去关联其他表。驱动表的选择遵循一个原则:在对最终结果集没影响的前提下,优先选择结果集最少的那张表作为驱动表。这个原则说的不好懂,结果集最少,这个也许我们能估出来,但对最终结果集不影响,这个就不好判断了,难归难,但还是有一定规律的:u003Cu002Fpu003Eu003Cpreu003ELEFT JOIN 一般以左表为驱动表(RIGHT JOIN一般则是右表 ),INNER JOIN 一般以结果集少的表为驱动表,如果还觉得有疑问,则可用 EXPLAIN 来找驱动表,其结果的第一张表即是驱动表。u003Cbru003E你以为 EXPLAIN 就一定准吗 ? 执行计划在真正执行的时候是可能改变的!u003Cbru003Eu003Cbru003E绝大多少情况下是适用的,特别是 EXPLAINu003Cu002Fpreu003Eu003Cpu003ELEFT JOIN 某些情况下会被查询优化器优化成 INNER JOIN;结果集指的是表中记录过滤后的结果,而不是表中的所有记录,如果无过滤条件则是表中所有记录u003Cu002Fpu003Eu003Cpu003E更多信息可查看:Mysql多表连接查询的执行细节(一)u003Cu002Fpu003Eu003Ch1 class=”pgc-h-arrow-right”u003ESQL 执行的流程图u003Cu002Fh1u003Eu003Cpu003E当我们向 MySQL 发送一个请求的时候,MySQL 到底做了些了什么u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp3.pstatp.comu002Flargeu002Fpgc-imageu002Feb63aa69aab44a80b581555e1ea6c3b9″ img_width=”640″ img_height=”500″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003ESQL 执行路径,摘自《高性能MySQL》u003Cu002Fpu003Eu003Cpu003E可以看到,执行计划是查询优化器的输出结果,执行引擎根据执行计划来查询数据u003Cu002Fpu003Eu003Ch1 class=”pgc-h-arrow-right”u003E数据准备u003Cu002Fh1u003Eu003Cpu003EMySQL 5.7.1,InnoDB 引擎;建表 SQL 和 数据初始 SQLu003Cu002Fpu003Eu003Cpreu003E– 表创建与数据初始化u003Cbru003EDROP TABLE IF EXISTS tbl_user;u003Cbru003ECREATE TABLE tbl_user (u003Cbru003E id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',u003Cbru003E user_name VARCHAR(50) NOT NULL COMMENT '用户名',u003Cbru003E sex TINYINT(1) NOT NULL COMMENT '性别, 1:男,0:女',u003Cbru003E create_time datetime NOT NULL COMMENT '创建时间',u003Cbru003E update_time datetime NOT NULL COMMENT '更新时间',u003Cbru003E remark VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',u003Cbru003E PRIMARY KEY (id)u003Cbru003E) COMMENT='用户表';u003Cbru003Eu003Cbru003EDROP TABLE IF EXISTS tbl_user_login_log;u003Cbru003ECREATE TABLE tbl_user_login_log (u003Cbru003E id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',u003Cbru003E user_name VARCHAR(50) NOT NULL COMMENT '用户名',u003Cbru003E ip VARCHAR(15) NOT NULL COMMENT '登录IP',u003Cbru003E client TINYINT(1) NOT NULL COMMENT '登录端, 1:android, 2:ios, 3:PC, 4:H5',u003Cbru003E create_time datetime NOT NULL COMMENT '创建时间',u003Cbru003E PRIMARY KEY (id)u003Cbru003E) COMMENT='登录日志';u003Cbru003EINSERT INTO tbl_user(user_name,sex,create_time,update_time,remark) VALUESu003Cbru003E('何天香',1,NOW(), NOW(),'朗眉星目,一表人材'),u003Cbru003E('薛沉香',0,NOW(), NOW(),'天星楼的总楼主薛摇红的女儿,也是天星楼的少总楼主,体态丰盈,乌发飘逸,指若春葱,袖臂如玉,风姿卓然,高贵典雅,人称“天星绝香”的武林第一大美女'),u003Cbru003E('慕容兰娟',0,NOW(), NOW(),'武林东南西北四大世家之北世家慕容长明的独生女儿,生得玲珑剔透,粉雕玉琢,脾气却是刚烈无比,又喜着火红,所以人送绰号“火凤凰”,是除天星楼薛沉香之外的武林第二大美女'),u003Cbru003E('苌婷',0,NOW(), NOW(),'当今皇上最宠爱的侄女,北王府的郡主,腰肢纤细,遍体罗绮,眉若墨画,唇点樱红;虽无沉香之雅重,兰娟之热烈,却别现出一种空灵'),u003Cbru003E('柳含姻',0,NOW(), NOW(),'武林四绝之一的添愁仙子董婉婉的徒弟,体态窈窕,姿容秀丽,真个是秋水为神玉为骨,芙蓉如面柳如腰,眉若墨画,唇若点樱,不弱西子半分,更胜玉环一筹; 摇红楼、听雨轩,琵琶一曲值千金!'),u003Cbru003E('李凝雪',0,NOW(), NOW(),'李相国的女儿,神采奕奕,英姿飒爽,爱憎分明'),u003Cbru003E('周遗梦',0,NOW(), NOW(),'音神传人,湘妃竹琴的拥有者,云髻高盘,穿了一身黑色蝉翼纱衫,愈觉得冰肌玉骨,粉面樱唇,格外娇艳动人'),u003Cbru003E('叶留痕',0,NOW(), NOW(),'圣域圣女,肤白如雪,白衣飘飘,宛如仙女一般,微笑中带着说不出的柔和之美'),u003Cbru003E('郭疏影',0,NOW(), NOW(),'扬灰右使的徒弟,秀发细眉,玉肌丰滑,娇润脱俗'),u003Cbru003E('钟钧天',0,NOW(), NOW(),'天界,玄天九部 – 钧天部的部主,超凡脱俗,仙气逼人'),u003Cbru003E('王雁云',0,NOW(), NOW(),'尘缘山庄二小姐,刁蛮任性'),u003Cbru003E('许侍霜',0,NOW(), NOW(),'药王谷谷主女儿,医术高明'),u003Cbru003E('冯黯凝',0,NOW(), NOW(),'桃花门门主,娇艳如火,千娇百媚');u003Cbru003EINSERT INTO tbl_user_login_log(user_name, ip, client, create_time) VALUESu003Cbru003E('薛沉香', '10.53.56.78',2, '2019-10-12 12:23:45'),u003Cbru003E('苌婷', '10.53.56.78',2, '2019-10-12 22:23:45'),u003Cbru003E('慕容兰娟', '10.53.56.12',1, '2018-08-12 22:23:45'),u003Cbru003E('何天香', '10.53.56.12',1, '2019-10-19 10:23:45'),u003Cbru003E('柳含姻', '198.11.132.198',2, '2018-05-12 22:23:45'),u003Cbru003E('冯黯凝', '198.11.132.198',2, '2018-11-11 22:23:45'),u003Cbru003E('周遗梦', '198.11.132.198',2, '2019-06-18 22:23:45'),u003Cbru003E('郭疏影', '220.181.38.148',3, '2019-10-21 09:45:56'),u003Cbru003E('薛沉香', '220.181.38.148',3, '2019-10-26 22:23:45'),u003Cbru003E('苌婷', '104.69.160.60',4, '2019-10-12 10:23:45'),u003Cbru003E('王雁云', '104.69.160.61',4, '2019-10-16 20:23:45'),u003Cbru003E('李凝雪', '104.69.160.62',4, '2019-10-17 20:23:45'),u003Cbru003E('许侍霜', '104.69.160.63',4, '2019-10-18 20:23:45'),u003Cbru003E('叶留痕', '104.69.160.64',4, '2019-10-19 20:23:45'),u003Cbru003E('王雁云', '104.69.160.65',4, '2019-10-20 20:23:45'),u003Cbru003E('叶留痕', '104.69.160.66',4, '2019-10-21 20:23:45');u003Cbru003Eu003Cbru003ESELECT * FROM tbl_user;u003Cbru003ESELECT * FROM tbl_user_login_log;u003Cu002Fpreu003Eu003Ch1 class=”pgc-h-arrow-right”u003E单表查询u003Cu002Fh1u003Eu003Cpu003E单表查询的过程比较好理解,大致如下u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp3.pstatp.comu002Flargeu002Fpgc-imageu002F362788a5dcb44996a8aa2eb143b9c8d5″ img_width=”901″ img_height=”682″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E关于单表查询就不细讲了,主要涉及到:聚集索引,覆盖索引、回表操作,知道这 3 点,上图就好理解了(不知道的赶快去查资料,暴露了就丢人了!)。u003Cu002Fpu003Eu003Ch1 class=”pgc-h-arrow-right”u003E联表算法u003Cu002Fh1u003Eu003Cpu003EMySQL 的联表算法是基于嵌套循环算法(nested-loop algorithm)而衍生出来的一系列算法,根据不同条件而选用不同的算法u003Cu002Fpu003Eu003Cpreu003E在使用索引关联的情况下,有 Index Nested-Loop join 和 Batched Key Access join 两种算法;u003Cbru003E在未使用索引关联的情况下,有 Simple Nested-Loop join 和 Block Nested-Loop join 两种算法;  u003Cu002Fpreu003Eu003Ch1 class=”pgc-h-arrow-right”u003ESimple Nested-Loopu003Cu002Fh1u003Eu003Cpu003E简单嵌套循环,简称 SNL;逐条逐条匹配,就像这样u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002F7c25ad810b1f4351a8e66233bb3baf25″ img_width=”528″ img_height=”146″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002Ffc97f54bf1e243ce96bbbeab1dffca1a” img_width=”681″ img_height=”301″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E这种算法简单粗暴,但毫无性能可言,时间性能上来说是 n(表中记录数) 的 m(表的数量) 次方,所以 MySQL 做了优化,联表查询的时候不会出现这种算法,即使在无 WHERE 条件且 ON 的连接键上无索引时,也不会选用这种算法u003Cu002Fpu003Eu003Ch1 class=”pgc-h-arrow-right”u003EBlock Nested-Loopu003Cu002Fh1u003Eu003Cpu003E缓存块嵌套循环连接,简称 BNL,是对 INL 的一种优化;一次性缓存多条驱动表的数据,然后拿 Join Buffer 里的数据批量与内层循环读取的数据进行匹配,就像这样u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp9.pstatp.comu002Flargeu002Fpgc-imageu002Fb0dbc05bf598473e9f074b8fcd889f18″ img_width=”834″ img_height=”413″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E将内部循环中读取的每一行与缓冲区中的所有记录进行比较,这样就可以减少内层循环的读表次数。举个例子,如果没有 Join Buffer,驱动表有 30 条记录,被驱动表有 50 条记录,那么内层循环的读表次数应该是 30 * 50 = 1500,如果 Join Buffer 可用并可以存 10 条记录(Join Buffer 存储的是驱动表中参与查询的列,包括 SELECT 的列、ON 的列、WHERE 的列,而不是驱动表中整行整行的完整记录),那么内层循环的读表次数应该是 30 u002F 10 * 50 = 150,被驱动表必须读取的次数减少了一个数量级。u003Cu002Fpu003Eu003Cpu003E当被驱动表在连接键上无索引且被驱动表在 WHERE 过滤条件上也没索引时,常常会采用此种算法来完成联表,如下所示u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp9.pstatp.comu002Flargeu002Fpgc-imageu002Fb812dae7ea37404496a3d5f10d3b780b” img_width=”1172″ img_height=”260″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002Fe4f3818e1a0e497f8da17e75b12c9fdb” img_width=”1168″ img_height=”255″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Ch1 class=”pgc-h-arrow-right”u003EIndex Nested-Loopu003Cu002Fh1u003Eu003Cpu003E索引嵌套循环,简称 INL,是基于被驱动表的索引进行连接的算法;驱动表的记录逐条与被驱动表的索引进行匹配,避免和被驱动表的每条记录进行比较,减少了对被驱动表的匹配次数,大致流程如下图u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002F8d25d21abdde4b2ba73cfae30017d38b” img_width=”781″ img_height=”258″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E我们来看看实际案例,先给 tbl_user_login_log 添加索引 ALTER TABLE tbl_user_login_log ADD INDEX idx_user_name (user_name); ,我们再来看联表执行计划u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp9.pstatp.comu002Flargeu002Fpgc-imageu002F1d0daa78cc8f493b88a51c4d86fd2d1d” img_width=”906″ img_height=”212″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E可以看到 tbl_user_login_log 的索引生效了,我们再往下看u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002F5c76fa1dae174a03ae5f19b751cf6926″ img_width=”1213″ img_height=”226″ alt=”神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E有趣的事发生了,驱动表变成了 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 好强大?u003Cu002Fpu003Eu003Ch1 class=”pgc-h-arrow-right”u003E总结u003Cu002Fh1u003Eu003Cpu003E1、驱动表的选择有它的一套算法,有兴趣的可以去专研下;比较靠谱的确定方法是用 EXPLAINu003Cu002Fpu003Eu003Cpu003E2、联表顺序,不是两两联合之后,再去联合第三张表,而是驱动表的一条记录穿到底,匹配完所有关联表之后,再取驱动表的下一条记录重复联表操作;u003Cu002Fpu003Eu003Cpu003E3、MySQL 的连接算法基于嵌套循环算法,基于不同的情况而采用不同的衍生算法u003Cu002Fpu003Eu003Cpu003E4、关于 ON 和 WHERE,我们下篇详细讲解,大家可以先考虑下它们的区别,以及生效时间u003Cu002Fpu003E”

原文始发于:神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程

主题测试文章,只做测试使用。发布者:乾奕,转转请注明出处:http://www.cxybcw.com/26316.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code