17站长网 网站首页 数据库 Mysql 查看内容

学习MYSQL之ICP、MRR、BKA

2023-3-16 14:38| 查看: 696 |来源: 互联网

Index Condition Pushdown(ICP)Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化方式。ICP原理禁用ICP,存储引擎会通过遍历索引定位基 ...

Index Condition Pushdown(ICP)

Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化方式。

ICP原理

禁用ICP,存储引擎会通过遍历索引定位基表中的行,然后返回给MySQL Server层,再去为这些数据行进行WHERE后的条件的过滤。

开启ICP,如果部分WHERE条件能使用索引中的字段,MySQL Server 会把这部分下推到存储引擎层,存储引擎通过索引过滤,把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。

ICP的目标是减少从基表中全纪录读取操作的数量,从而降低IO操作

对于InnoDB表,ICP只适用于辅助索引。

ICP标识

当使用ICP优化时,执行计划的Extra列显示Using indexcondition提示

相关参数

optimizer_switch="index_condition_pushdown=on”;

适用场景

#辅助索引INDEX (zipcode, lastname, firstname).

SELECT * FROM peopleWHERE zipcode='95054'AND lastname LIKE '%etrunia%'AND address LIKE '%Main Street%';

People表有个二级索引INDEX (zipcode, lastname, firstname),用户只知道某用户的zipcode,和大概的lastname、address,此时想查询相关信息。

若不使用ICP:则是通过二级索引中zipcode的值去基表取出所有zipcode='95054'的数据,然后server层再对lastname LIKE '%etrunia%'AND address LIKE '%Main Street%';进行过滤

若使用ICP:则lastname LIKE '%etrunia%'AND address LIKE '%Main Street%'的过滤操作在二级索引中完成,然后再去基表取相关数据

使用限制

l  只支持 select 语句

l  5.6 中只支持 MyISAM与InnoDB引擎

l  5.6中不支持分区表的ICP;从MySQL 5.7.3开始支持分区表的ICP

l  ICP的优化策略可用于range、ref、eq_ref、ref_or_null 类型的访问数据方法;

l  不支持主建索引的ICP(对于Innodb的聚集索引,完整的记录已经被读取到Innodb Buffer,此时使用ICP并不能降低IO操作)

l  当 SQL 使用覆盖索引时但只检索部分数据时,ICP 无法使用

l  ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

Multi-Range Read (MRR)

MRR 的全称是 Multi-Range Read Optimization,是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段。

MRR原理

select non_key_column from tb where ey_column=x;

在没有使用MRR特性时,MySQL执行查询的伪代码

第一步 先根据where条件中的辅助索引获取辅助索引与主键的集合,结果集为rest。

select key_column, pk_column from tb where key_column=x order by key_column

第二步 通过第一步获取的主键来获取对应的值。

for each pk_column value in rest do:

select non_key_column from tb where pk_column=val

使用MRR特性时,MySQL执行查询的伪代码

第一步 先根据where条件中的辅助索引获取辅助索引与主键的集合,结果集为rest

select key_column, pk_column from tb where key_column = x order by key_column

第二步 将结果集rest放在buffer里面(read_rnd_buffer_size 大小直到buffer满了),然后对结果集rest按照pk_column排序,得到结果集是rest_sort

第三步 利用已经排序过的结果集,访问表中的数据,此时是顺序IO.

select non_key_column fromtb where pk_column in (rest_sort)

综上

在不使用 MRR 时,优化器需要根据二级索引返回的记录来进行“回表”,这个过程一般会有较多的随机IO, 使用MRR时,SQL语句的执行过程是这样的:

1)   优化器将二级索引查询到的记录放到一块缓冲区中

2)   如果二级索引扫描到文件的末尾或者缓冲区已满,则使用快速排序对缓冲区中的内容按照主键进行排序

3)   用户线程调用MRR接口取cluster index,然后根据cluster index 取行数据

4)   当根据缓冲区中的 cluster index取完数据,则继续调用过程 2) 3),直至扫描结束

通过上述过程,优化器将二级索引随机的 IO 进行排序,转化为主键的有序排列,从而实现了随机 IO 到顺序 IO 的转化,提升性能

此外MRR还可以将某些范围查询,拆分为键值对,来进行批量的数据查询,如下:

SELECT * FROM tWHERE key_part1 >= 1000 AND key_part1 < 2000AND key_part2 = 10000;

表t上有二级索引(key_part1, key_part2),索引根据key_part1,key_part2的顺序排序。

若不使用MRR:索引扫描会将key_part1在1000到2000的索引元组,而不管key_part2的值,这样对key_part2不等于10000的索引元组也做了额外的扫描。此时扫描的范围是:

[{1000, 10000}, {2000, MIN_INT}]此间隔可能包含key_part2不等于10000的部分

若使用MRR:扫描则分为多个范围,对于每一个Key_part1(1000,1001…,1999)单个值的扫描只需要扫描索引中key_part2为10000的元组。如果索引中包含很多key_part2不为10000的元组,最终MRR的效果越好。MRR扫描的范围是多个单点间隔[{1000, 10000}], ..., [{1999, 10000}] 此间隔只包含key_part2=10000的部分。

MRR标识

当使用ICP优化时,执行计划的Extra列显示Using MRR提示

相关参数

用optimizer_switch 的标记来控制是否使用MRR.设置mrr=on时,表示启用MRR优化。

mrr_cost_based表示是否通过cost base的方式来启用MRR.

当mrr=on,mrr_cost_based=on,则表示cost base的方式还选择启用MRR优化,当发现优化后的代价过高时就会不使用该项优化

当mrr=on,mrr_cost_based=off,则表示总是开启MRR优化

SET  @@optimizer_switch='mrr=on,mrr_cost_based=on';

参数read_rnd_buffer_size 用来控制键值缓冲区的大小。二级索引扫描到文件的末尾或者缓冲区已满,则使用快速排序对缓冲区中的内容按照主键进行排序

适用场景

#辅助索引key_part1,查询key_part1在1000到2000范围内的数据

SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 < 2000

不使用MRR:先通过二级索引的key_part1字段取出满足条件的key_part1,pk_col order by key_part1.然后通过pk_col去表中取出满足条件的数据,此时,因为取出的pk_col是乱序的,而表又是pk_col存放数据的,当去表中取数据时,则会产生大量的随机IO

使用MRR:先通过二级索引的key_part1字段取出满足条件的key_part1,pk_col order by key_part1.放到缓存中(read_rnd_buffer_size),当对应的缓冲满了以后,将这部分key值按照pk_col排序,最后再按照排序后的reset去取表中数据,此时pk_col1是顺序的,将随机IO转化为顺序IO,多页数据记录可一次性读入或根据此次的主键范围分次读入,以减少IO操作,提高查询效率

使用限制

MRR 适用于range、ref、eq_ref的查询

Batched Key Access (BKA)和Block Nested-Loop(BNL)

Batched Key Access (BKA)-- 提高表join性能的算法。

当被join的表能够使用索引时,就先排好顺序,然后再去检索被join的表,听起来和MRR类似,实际上MRR也可以想象成二级索引和primary key的join

如果被Join的表上没有索引,则使用老版本的BNL策略(BLOCK Nested-loop)

BKA原理

对于多表join语句,当MySQL使用索引访问第二个join表的时候,使用一个join buffer来收集第一个操作对象生成的相关列值。BKA构建好key后,批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的(mrr目的是较为顺序).这样,MRR使得查询更有效率。

大致的过程如下:

1 BKA使用join buffer保存由join的第一个操作产生的符合条件的数据

2 然后BKA算法构建key来访问被连接的表,并批量使用MRR接口提交keys到数据库存储引擎去查找查找。

3 提交keys之后,MRR使用最佳的方式来获取行并反馈给BKA

BNL和BKA都是批量的提交一部分行给被join的表,从而减少访问的次数,那么它们有什么区别呢?

BKA和BNL标识

Using join buffer (Batched Key Access)和Using join buffer (Block Nested Loop)

相关参数

BAK使用了MRR,要想使用BAK必须打开MRR功能,而MRR基于mrr_cost_based的成本估算并不能保证总是使用MRR,官方推荐设置mrr_cost_based=off来总是开启MRR功能。打开BAK功能(BAK默认OFF):

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

BKA使用join buffer size来确定buffer的大小,buffer越大,访问被join的表/内部表就越顺序。

BNL默认是开启的,设置BNL相关参数:

SET optimizer_switch=’block_nested_loop’

适用场景

支持inner join, outer join, semi-join operations,including nested outer joins

BKA主要适用于join的表上有索引可利用,无索引只能使用BNL了

ICP优化(Index Condition Pushdown)

Index Condition Pushdown (ICP)是MySQL用索引去表里取数据的一种优化。禁用ICP(MySQL5.6之前),引擎层会利用索引在基表中寻找数据行,然后返回给MySQL Server层,再去为这些数据行进行WHERE后的条件的过滤(回表)。启用ICP(MySQL5.6之后),如果部分WHERE条件能使用索引中的字段,MySQL会把这部分下推到引擎层。存储引擎通过使用索引把满足的行从表中读取出。ICP减少了引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。总之是 ICP的优化在引擎层就能够过滤掉大量的数据,减少io次数,提高查询语句性能

MRR优化(Multi-Range Read)

Multi-Range Read 多范围读(MRR) 它的作用是基于辅助/第二索引的查询,减少随机IO,并且将随机IO转化为顺序IO,提高查询效率。在没有MRR之前(MySQL5.6之前),先根据where条件中的辅助索引获取辅助索引与主键的集合,再通过主键来获取对应的值。辅助索引获取的主键来访问表中的数据会导致随机的IO(辅助索引的存储顺序并非与主键的顺序一致),不同主键不在同一个page里面时必然导致多次IO 和随机读。使用MRR优化(MySQL5.6之后),先根据where条件中的辅助索引获取辅助索引与主键的集合,再将结果集放在buffer里面(read_rnd_buffer_size 大小直到buffer满了),然后对结果集按照pk_column排序,得到有序的结果集rest_sort。最后利用已经排序过的结果集,访问表中的数据,此时是顺序IO。即MySQL 将根据辅助索引获取的结果集根据主键进行排序,将无序化为有序,可以用主键顺序访问基表,将随机读转化为顺序读,多页数据记录可一次性读入或根据此次的主键范围分次读入,以减少IO操作,提高查询效率。

Nested Loop Join算法:

将驱动表/外部表的结果集作为循环基础数据,然后循环该结果集,每次获取一条数据作为下一个表的过滤条件查询数据,然后合并结果,获取结果集返回给客户端。Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次,效率非常差。

Block Nested-Loop Join算法:

将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较,从而减少内层循环的次数。主要用于当被join的表上无索引。

Batched Key Access算法:

当被join的表能够使用索引时,就先排好顺序,然后再去检索被join的表。对这些行按照索引字段进行排序,因此减少了随机IO。如果被Join的表上没有索引,则使用老版本的BNL策略(BLOCK Nested-loop)

本文最后更新于 2023-3-16 14:38,某些文章具有时效性,若有错误或已失效,请在网站留言或联系站长:17tui@17tui.com
·END·
站长网微信号:w17tui,关注站长、创业、关注互联网人 - 互联网创业者营销服务中心

免责声明:本站部分文章和图片均来自用户投稿和网络收集,旨在传播知识,文章和图片版权归原作者及原出处所有,仅供学习与参考,请勿用于商业用途,如果损害了您的权利,请联系我们及时修正或删除。谢谢!

17站长网微信二维码

始终以前瞻性的眼光聚焦站长、创业、互联网等领域,为您提供最新最全的互联网资讯,帮助站长转型升级,为互联网创业者提供更加优质的创业信息和品牌营销服务,与站长一起进步!让互联网创业者不再孤独!

扫一扫,关注站长网微信

大家都在看

  • 1、打开掌阅app,点击“我的”, 2、点击“设置”, 3、进入设置页面,点击“备份与同步”, 4、进入备份与同步页面,点击“书架备份与同步”,选择需要同步 ......

    软件教程 2023-03-21
  • 此前,腾讯宣布将推出基于NT架构的新版Windows QQ。今天,腾讯正式发布了新版Windows QQ的首个公测版,面向此前报名公测,并获得资格的用户开放下载。新Windows ......

    软件 2023-03-25
  • 答:荣耀60se支持门禁卡。 只要更新到最新的操作系统。 并且使用支持nfc技术的门禁卡即可。 荣耀60se门禁卡添加教程: 1、首先打开手机“钱包app” 2、接着点 ......

    安卓手机 2023-03-21
  • 1、打开qq浏览器的页面,点击右上角“下载”图标, 2、会出现一个下载管理的页面,在这里就可以看到我们下载过的全部文件了。 找到qq浏览器的文件在哪里的方法 ......

    软件教程 2023-03-22
  • 前阵子,腾讯历时 4 年打造的“ 末日僵尸生存”游戏《 黎明觉醒》,终于上线。在前期宣发中,光子工作室表示,游戏采用虚幻四打造,画面大幅升级,还会出现可探 ......

    游戏 2023-03-22
  • 1、首先单击电脑开始键,选择控制面板。 2、打开后找到语言选项,点击“更改显示语言” 3、打开后点击“管理”按钮。 4、再点击下方的“更改系统区域设置” ......

    windows7 2023-03-21
  • 1、首先进入“控制面板—用户账户—管理其他账户”, 即可查看登录的账户是不是管理员账户,不是的话就没有权限更改。 2、右击左下角开始键,点击“命令提示符 ......

    windows10 2023-03-21
  • 方法一: 1、首先打开手机的“电话”功能。 2、接着拨打电话。 3、拨通后,点击“红色方块”的录音就可以开始通话录音了。 方法二: 1、我们也可以在通话时 ......

    iphone 2023-03-21
  • 1、开始菜单打开控制面板。 2、在控制面板中找到硬件和声音,点击打开。 3、在硬件和声音窗口中选择电源选项。 4、点击电源选项窗口左侧的更改计算机睡眠时间 ......

    windows7 2023-03-21
  • 《霍格沃茨之遗》的热潮退去后,这款游戏的Steam在线人数从近88万跌至7.5万。推特上有“反恐同”人士嘲讽道:“哈利·波特在霍格沃茨住了7年,你们这些粉丝连2个 ......

    游戏 2023-03-22
  • 以ChatGPT为代表的生成式AI火爆全球,最新的GPT-4大模型又进一步提升了ChatGPT的能力,国内有多家公司都在对标ChatGPT,然而发布出来的产品被指差距极大,360创 ......

    人物 2023-03-25
  • 1、网速问题 网速太慢,只能换网线。 2、电脑配置问题 可以换零件,重新组装。 3、浏览器问题 浏览器出现故障,可以用修复软件修复或者重新下载一个浏览器。 4 ......

    软件教程 2023-03-22
  • 1、点击左下角开始,打开管理工具选择“高级安全windows防火墙”。 2、在左侧的任务栏中选择“入站规则”随后在右侧点击“新建规则”。 3、选择“端口”点击下 ......

    系统问题 2023-03-21
  • 1、打开“资源管理器”,即可查看七个文件夹。 2、在空白桌面出建立“文本文档”。 3、随后进入文本文档,复制黏贴下列代码。 Windows Registry Editor Versio ......

    windows10 2023-03-22
  • 1、点击左下角开始,搜索cmd,选中cmd.exe用管理员身份运行。 2、打开后输入“netsh wlan set tednetwork mode=allow ssid=Test key=0123456789” (ssid是无 ......

    windows7 2023-03-21
  • 在谷歌宣布生成式AI Bard开启公测后,微软研究院首席研究员Kate Crawford在社交媒体发布文章,质疑Bard的训练数据集调用了Gmail的数据。换言之,Crawford质疑谷 ......

    软件 2023-03-22
  • 1、首先我们开机,如果已经开了那就重启电脑, 开机进入图示的“英特尔logo”画面时,马上进行第二步。 2、在进入类似上方的logo界面时,快速按下键盘上的“del ......

    系统问题 2023-03-21
  • 1、点击任务栏右下角的网络图标,点击“网络和Internet设置”。 2、在设置窗口右侧找到更改网络设置,点击下方的“更改适配器选项”。 3、右击你正在使用的网 ......

    windows10 2023-03-21
  • 继ChatGPT、文心一言之后,谷歌终于将自家对话式AI服务Bard开放测试。谷歌表示,公测将从美国和英国开始,开放更多用户访问是“改进它的下一个关键步骤”。与Cha ......

    软件 2023-03-22
  • 2016年12月新海诚导演的动画电影《你的名字。》在国内上映,首日就拿下了7629.53万票房。今天,新海诚的新作《铃芽之旅》在国内上映,截止本文成稿,首日票房成 ......

    影视 2023-03-25
  • 答:administrator是系统管理员的意思,可以删除的。 这个单词的意思就是超级用户,是指电脑权限不受控的人。 删除是可以删除的,但是一定要在创建了第二个账户 ......

    系统问题 2023-03-21
  • 1、右击桌面此电脑,点击“属性”。 2、进入属性后点击“控制面板主页”。 3、在里面选择打开“程序和功能”。 4、随后在里面打开“启用或关闭windows功能” ......

    系统问题 2023-03-21
  • 1、找到桌面的此电脑右击打开“管理”。 2、随后点击左侧任务栏中的“任务计划程序”。 3、再点击下拉中的“任务计划程序库”。 4、此时可以将右侧的菜单中的 ......

    windows10 2023-03-21
  • 第一步,点击左下角的“开始菜单” 第二步,打开其中的“设置” 第三步,打开设置里的“系统”设置。 第四步,点击显示中的“显示适配器属性”选项。 第五步 ......

    系统问题 2023-03-21
  • 答:appdata能删。 一般不建议删除,删除了之后容易造成应用程序配置丢失,可能在开启一些程序的时候会失败,严重清理还可能会导致无法启动。 里面有三个子文 ......

    系统问题 2023-03-21
  • 1、首先在桌面空白处右击选择个性化打开。 2、在打开的击界面点击打开右下角的屏幕保护程序。 3、在新开的屏幕保护界面就可以选择各种效果来保护屏幕了。 4、 ......

    windows7 2023-03-22
  • 答:电脑guest的意思是给客人访问电脑系统的账户。 在系统之中被称作“来宾账户”这个账户一般没有修改系统设置的权限。 可以用作读作计算机系统的信息和文件, ......

    系统问题 2023-03-21
  • 1、在开机界面重启三次后再次开机即可进入安全模式,点击“启用安全模式”。 2、随后点击左下角开始键,点击“设置”。 3、在windows设置中点击“应用”。 4 ......

    windows10 2023-03-21
  • 3月25日,中国计算机学会(以下简称CCF)与钉钉达成合作,联合发布了CCF的数字化平台——CCFLink。CCF上线专属钉钉,这是CCF提升数字化水平,探索全新会员服务模 ......

    科技 2023-03-25
  • 一、来源不同 1、iso来源于系统光盘,gho来源于系统克隆文件。 二、使用方法不同 1、iso不需要解压缩,直接打开或刻录到光盘中读取就能安装。 2、gho需要解压运 ......

    系统问题 2023-03-21
返回顶部