不知道咋优化 语句的优化准没错 Join 吹一手 SQL

面试最怕遇到的疑问是什么，如何做优化必定当仁不让，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 优化会缓缓降级的~)