xxxx18一20岁hd第一次

SQL 不默契咋优化?吹一手 Join 语句的优化准没错

发布日期:2022-06-18 17:11    点击次数:120

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 是说合自增的,这么,咱们关于主键索引树的查询,即是说合的:

固然,这是想象情况,若是 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 算法,追念下具体的实行逻辑:

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

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 语句如下:

# 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 算法

关于 Index Nested-Loop 来说,即是一次性地从动手表中取出许多个行记载出来,先放到临时内存 join_buffer 中,然后再悉数传给被迫手表 关于 Block Nested-Loop 来说,即是对被迫手表设备一个临时表,而况对条款字段设备索引,然后把之前两张表的 join 操作革新成动手表和临时表的 join 操作,从而革新成对 Index Nested-Loop 的优化问题

 

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

 



栏目分类



Powered by xxxx18一20岁hd第一次 @2013-2022 RSS地图 HTML地图