华企号 软件工程 SQL性能优化和join 语句的优化标注

SQL性能优化和join 语句的优化标注

面试最怕遇到的问题是什么,如何做优化一定当仁不让,SQL 优化更是首当其冲,这里先跟大家分享一个比较容易理解的 join 语句的优化~

前文提到过,当能够用上被驱动表的索引的时候,使用的是 Index Nested-Loop Join 算法,这时性能还是很好的;但是,用不上被驱动表的索引的时候,使用的 Block Nested-Loop Join 算法性能就差多了,非常消耗资源。

针对 join 语句的这两种情况,其实都还是存在继续优化的空间的

Multi-Range Read 优化

我们先来回顾一下 “回表” 这个概念。回表是指,InnoDB 在普通索引上查到主键 id 的值后,再根据主键 id 的值到主键索引树上去查询整行记录的过程。

那么,思考一个问题,回表的过程是一行行地查数据,还是批量地查数据?

显然是一行行地。

因为回表查询的本质就是查询 B+ 树,在这棵树上,每次只能根据一个主键 id 查到一行数据。

看下面这条语句,从 user 表中获取 80 岁以上用户的信息:

select * from user where age >= 80;

假设,age 对应的 id 是连续自增的,这样,我们对于主键索引树的查询,就是连续的:

SQL性能优化和join 语句的优化标注插图

当然,这是理想情况,如果 age 对应的 id 值不是顺序的话,那当我们顺序取 age 的时候,id 的获取就是乱序随机的了,性能就会比较差。解释下为什么这里乱序查询的性能就比较差:

首先,我们都知道,索引文件其实就是一个磁盘文件,尽管有内存中 Buffer Pool 的存在可以减少访问磁盘的次数,但是并不能完全避开对磁盘的访问。而对于磁盘来说,一个磁盘从内到外有许多磁道,一个磁道又被划分成多个相同的扇区,随机读取性能较差的原因就是每次都需要花费时间去寻找磁道,找到磁道之后又要去寻找合适的扇区,从而耗费大量时间。所以顺序读取比随机读取快很多。

SQL性能优化和join 语句的优化标注插图1

所以,一个很自然的想法,就是调整主键 id 查询的顺序,使其接近顺序读取,从而达到加速的目的

那么,具体该如何调整主键 id 查询的顺序呢?

因为大多数的数据都是按照主键 id 递增顺序插入的,对吧,所以我们可以简单的认为,如果按照主键 id 的递增顺序查询的话,对磁盘的读取会比较接近顺序读取,从而提升读性能。这就是 Multi-Range Read (MRR) 优化的思想。

而将主键 id 进行升序排序的过程,是在内存中的随机读取缓冲区 read_rnd_buffer 中进行的。

我们可以设置 set optimizer_switch="mrr_cost_based=off" 来开启 MRR 优化,这样,语句的执行流程就是下面这个样子:

  1. 根据普通索引 age,找到满足条件的主键 id,然后将 id 值放入 read_rnd_buffer 中
  2. 将 read_rnd_buffer 中的 id 进行递增排序
  3. 根据排序后的 id 数组,进行回表查询
SQL性能优化和join 语句的优化标注插图2

需要注意的是,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 算法,回顾下具体的执行逻辑:

select * from table1 join table2 on table1.age = table2.age where table2.age >= 80;
  1. 从 table1 表中读入一行数据 R
  2. 从数据行 R 中,取出 age 字段到表 table2 的 age 索引树上去找并取得对应的主键
  3. 根据主键回表查询,取出 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 优化算法

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

前两个参数的作用是启用 MRR,因为 BKA 算法的优化依赖于 MRR。

优化 Block Nested-Loop Join

那如果用不上被驱动表索引的话,使用的 BNL 算法性能是比较低的,所以常见的优化方法就是给被驱动表的 join 字段加上索引。

但是,如果这条 SQL 语句的使用频率比较低并且数据量不大的话,建立索引其实就比较浪费资源了

所以,有没有一种两全其美的办法呢?

这时候,我们可以考虑使用临时表。使用临时表的大致思路是:

  1. 把表 table2 中满足条件的数据放在临时表 temp_table2 中
  2. 给临时表 temp_table2 的字段 age 加上索引
  3. 让表 table1 和 temp_table2 做 join 操作

这样,一个 BNL 算法的优化问题,就被我们转换成了 Index-Nested Loop Join 的优化问题了,按照上述所说的,可以使用 BKA 进行优化。

具体的 SQL 语句如下:

# select * from table1 join table2 on table1.age = table2.age where table2.age >= 80;
create temporary table temp_table2 (id int primary key, name varchar, age int, index(age)) engine=innodb;
insert into temp_table2  select * from table1 where age >= 80;
select * from table1 join temp_table2  on (table1.b=temp_table2 .b);

总的来说,优化 Block Nested-Loop Join 的思路就是使用有索引的临时表,让 join 语句能够用上被驱动表上的索引,从而转换为 Index Nested-Loop Join 然后触发 BKA 算法,提升查询性能。


最后放上这道题的背诵版:

🥸 面试官:SQL 优化了解过吗?

😎 小牛肉:先说 join 语句的优化

join 语句分为两种情况,一种是能够用上被驱动表的索引,这个时候使用的算法是 Index Nested-Loop,另一种是用不上,这个时候使用的算法是 Block Nested-Loop

  • 对于 Index Nested-Loop 来说,具体步骤其实就是一个嵌套查询,首先,遍历驱动表,然后,对这每一行都去被驱动表中根据 on 条件字段进行搜索,由于被驱动表上建立了条件字段的索引,所以每次搜索只需要在辅助索引树上扫描一行就行了,性能比较高
  • 对于 Block Nested-Loop 来说,MySQL 首先把驱动表中的数据读入线程内存 join_buffer 中;然后扫描被驱动表,把被驱动表中的每一行依次取出来,跟 join_buffer 中的数据做对比,满足 on 条件的,就作为结果集的一部分返回。BNL 算法的性能比较差,因为我们需要多次遍历被驱动表。那么对于 BNL 算法来说,一个很常见的优化思路就是对被驱动表的条件字段建立索引,从而转换成 Index 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 算法

作者: 华企网通李铁牛程序员

我是程序员李铁牛,热爱互联网软件开发和设计,专注于大数据、数据分析、数据库、php、java、python、scala、k8s、docker等知识总结。15889726201 我的座右铭:"业精于勤荒于嬉,行成于思毁于随"
上一篇
下一篇

发表回复

联系我们

联系我们

028-84868647

在线咨询: QQ交谈

邮箱: tech@68v8.com

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

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

关注微博
返回顶部