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 岁以上用户的信息:

  1. select * from user where age >= 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 优化,这样,语句的执行流程就是下面这个样子:

  • 根据普通索引 age,找到满足条件的主键 id,然后将 id 值放入 read_rnd_buffer 中
  • 将 read_rnd_buffer 中的 id 进行递增排序;
  • 根据排序后的 id 数组,进行回表查询

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

  1. select * from table1 join table2 on table1.age = table2.age where table2.age >= 80; 

从 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 优化算法

  1. set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'

前两个参数的作用是启用 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 语句如下:

  1. select * from table1 join table2 on table1.age = table2.age where table2.age >= 80; 
  2. create temporary table temp_table2 (id int primary keyname varchar, age intindex(age)) engine=innodb; 
  3. insert into temp_table2  select * from table1 where age >= 80; 
  4. 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 算法

  • 对于 Index Nested-Loop 来说,就是一次性地从驱动表中取出很多个行记录出来,先放到临时内存 join_buffer 中,然后再一起传给被驱动表
  • 对于 Block Nested-Loop 来说,就是对被驱动表建立一个临时表,并且对条件字段建立索引,然后把之前两张表的 join 操作转换成驱动表和临时表的 join 操作,从而转换成对 Index Nested-Loop 的优化问题

 

balabala.......(后续其他 SQL 优化会慢慢更新的~)

 

责任编辑:武晓燕 来源: 飞天小牛肉

同话题下的热门内容

访问数据库总超时?这份避坑指南请收好数据库文件损坏并且无备份,不用慌!DB Repair修复工具利器是时候检查一下使用索引的姿势是否正确了!Sentry 开发者贡献指南-数据库迁移

编辑推荐

Oracle免费的便捷Web应用开发框架二则从携程系统瘫痪,看国内企业数据管理乱象数据库update时这样干,你就悲剧了携程事件反思:是该重视数据库灾备了!四种优秀的数据库设计工具
我收藏的内容
点赞
收藏