SQL Join 吹一手 不知道咋优化 语句的优化准没错
面试最怕遇到的疑问是什么,如何做优化必定当仁不让,SQL 优化更是首当其冲,这里先跟大家分享一个比拟容易了解的 join 语句的优化~
前文提到过,当能够用上被驱动表的索引的时刻,经常使用的是 Index Nested-Loop Join算法,这时性能还是很好的;然而,用不上被驱动表的索引的时刻,经常使用的 Block Nested-Loop Join 算法性能就差多了,十分消耗资源。
针对 join 语句的这两种状况,其实都还是存在继续优化的空间的
老规矩,背诵版在文末。点击阅读原文可以中转我收录整顿的各大厂面试真题
Multi-Range Read 优化
咱们先来回忆一下 “回表” 这个概念。回表是指,InnoDB 在个别索引上查到主键 id 的值后,再依据主键 id的值到主键索引树上去查问整行记载的环节。
那么,思索一个疑问,回表的环节是一行行地查数据,还是批量地查数据?
显然是一行行地。
由于回表查问的实质就是查问 B+ 树,在这棵树上,每次只能依据一个主键 id 查到一行数据。
看上方这条语句,从 user 表中失掉 80 岁以上用户的消息:
假定,age 对应的 id 是延续自增的,这样,咱们关于主键索引树的查问,就是延续的:
当然,这是现实状况,假设 age 对应的 id 值不是顺序的话,那当咱们顺序取 age 的时刻,id的失掉就是乱序随机的了,性能就会比拟差。解释下为什么这里乱序查问的性能就比拟差:
首先,咱们都知道,索引文件其实就是一个磁盘文件,虽然有内存中 Buffer Pool的存在可以缩小访问磁盘的次数,然而并不能齐全避开对磁盘的访问。而关于磁盘来说,一个磁盘从内到外有许多磁道,一个磁道又被划分红多个相反的扇区,随机读取性能较差的要素就是每次都须要破费期间去寻觅磁道,找到磁道之后又要去寻觅适合的扇区,从而消耗少量期间。所以顺序读取比随机读取快很多。
所以,一个很人造的想法,就是调整主键 id 查问的顺序,使其凑近顺序读取,从而到达减速的目标。
那么,详细该如何调整主键 id 查问的顺序呢?
由于大少数的数据都是依照主键 id 递增顺序拔出的,对吧,所以咱们可以便捷的以为,假设依照主键 id的递增顺序查问的话,对磁盘的读取会比拟凑近顺序读取,从而优化读性能。这就是 Multi-Range Read (MRR) 优化的思维。
而将主键 id 启动升序排序的环节,是在内存中的随机读取缓冲区 read_rnd_buffer 中启动的。
咱们可以设置 set optimizer_switch="mrr_cost_based=off" 来开启 MRR优化,这样,语句的口头流程就是上方这个样子:
须要留意的是,read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。假设发现 read_rnd_buffer放满了,那么 MySQL 就会先口头完步骤 2 和 3,而后清空 read_rnd_buffer,之后再继续循环。
可以看进去,经常使用 MRR 优化性能关键实用于范畴查问,这样可以失掉足够多的主键 id,经过排序,再去主键索引查数据,从而表现出顺序读取的长处。
MRR 这种开拓一个内存空间对主键 id 启动排序的思维呢,运行到 join 语句的优化层面过去,就是 MySQL 在 5.6 版本后引入的Batched Key Access 算法(BKA),上方咱们来解析下这个算法以及如何经常使用这个算法对 Index Nested-Loop Join 和Block Nested-Loop Join 两种状况启动优化。
优化 Index Nested-Loop Join
假定咱们曾经在 age 字段上建设了索引,那么上方这条 sql 语句用到的就是 Index Nested-Loop Join算法,回忆下详细的口头逻辑:
从 table1 表中读入一行数据 R
从数据行 R 中,取出 age 字段到表 table2 的 age 索引树上去找并取得对应的主键
依据主键回表查问,取出 table2 表中满足条件的行,而后跟 R 组成一行,作为结果集的一局部
也就是说,关于表 table2 来说,每次都是只婚配一个值。这时,MRR 的长处就用不上了。
所以,假构想要享遭到 MRR 带来的优化,就必定在被驱动表 table2 上经常使用范畴婚配,换句话说,咱们须要一次性性地多传些值给表table2。那么详细该怎样做呢?
方法就是,从表 table1 中一次性性地多拿些行进去,先放到一个暂时内存中,而后再一同传给表 table2。而这个暂时内存不是他人,就是join_buffer!
之前咱们剖析过 Block Nested-Loop Join 算法中用到了 join_buffer,而 Index Nested-Loop Join并没有用到,这不,在优化这里派上用场了。
这就是 BKA 算法对 Index Nested-Loop Join 的优化,可以经过上方这行命令启用 BKA 优化算法
前两个参数的作用是启用 MRR,由于 BKA 算法的优化依赖于 MRR。
优化 Block Nested-Loop Join
那假设用不上被驱动表索引的话,经常使用的 BNL 算法性能是比拟低的,所以经常出现的优化方法就是给被驱动表的 join 字段加上索引。
然而,假设这条 SQL 语句的经常使用频率比拟低并且数据量不大的话,建设索引其实就比拟糜费资源了。
所以,有没有一种一举两得的方法呢?
这时刻,咱们可以思索经常使用暂时表。经常使用暂时表的大抵思绪是:
把表 table2 中满足条件的数据放在暂时表 temp_table2 中
给暂时表 temp_table2 的字段 age 加上索引
让表 table1 和 temp_table2 做 join 操作
这样,一个 BNL 算法的优化疑问,就被咱们转换成了 Index-Nested Loop Join 的优化疑问了,依照上述所说的,可以经常使用 BKA启动优化。
详细的 SQL 语句如下:
总的来说,优化 Block Nested-Loop Join 的思绪就是经常使用有索引的暂时表,让 join 语句能够用上被驱动表上的索引,从而转换为Index Nested-Loop Join 而后触发 BKA 算法,优化查问性能。
最后放上这道题的背诵版:
面试官:SQL 优化了解过吗?
小牛肉:先说 join 语句的优化
join 语句分为两种状况,一种是能够用上被驱动表的索引,这个时刻经常使用的算法是 Index Nested-Loop,另一种是用不上,这个时刻经常使用的算法是Block Nested-Loop
关于上方这两种 join 状况来说,假设继续增加一个范畴查问的 where 条件的话,其实还存在优化空间。
其外围做法其实就是针对范畴查问的优化,也称为 Multi-Range Read 算法
详细来说,由于大少数的数据都是依照主键 id 递增顺序拔出的嘛,所以咱们可以便捷的以为,假设依照主键 id的递增顺序启动查问的话,对磁盘的读取会比拟凑近顺序读取,这样相比于乱序读取的话缩小了寻道期间,从而优化读性能。
而将主键 id 启动升序排序的环节,是在内存中的随机读取缓冲区 read_rnd_buffer 中启动的。就是先把在辅佐索引树上查找的满足条件的主键 id存到 read_rnd_buffer 中,而后对这些 id 启动递增排序,依据排序后的 id 数组,启动回表查问。
MRR 的思维运行到 join 语句的优化层面过去,就是 MySQL 在 5.6 版本后引入的 Batched Key Access,BKA 算法
balabala.......(后续其余 SQL 优化会缓缓降级的~)