您的位置:68399皇家赌场 > 虚拟主机 > 【mysql】关于ICP、M奥德赛翼虎、BKA等个性

【mysql】关于ICP、M奥德赛翼虎、BKA等个性

发布时间:2019-05-12 14:33编辑:虚拟主机浏览(64)

     

    浅析MySQL中的Index Condition Pushdown (ICP 索引条件下推)和Multi-Range Read(M牧马人PRADO 索引多范围查找)查询优化,

     

    正文出处:
    (保留出处并非什么原创文章职责,自身拙作还远远达不到,仅仅是为着链接到原来的书文,因为一连对大概存在的局地错误进行改进或补充,无他)

     

     

    ICP优化原理

    Index Condition Pushdown (ICP),也堪称索引条件下推,呈以后施行陈设的上是会出现Using index condition(Extra列,当然Extra列的新闻太多了,只可以做容易分析)
    ICP原理通俗讲正是,查询进度中,直接在询问引擎层的API获取数据的时候实现"非直接索引"过滤条件的筛选,而不是询问引擎层查询出来之后在Server层筛选。
    换句话说正是ICP在获取数据的同时达成了where的次选条件中不或者直接运用索引的情景下的筛选,制止了从未ICP优化的时候分五个步骤的落到实处(获取数据的长河未有做次选规则的过滤)
    如假如非ICP优化查询的话,是两步,第叁步是获取数据,第三步是收获的数目开始展览标准筛选。
    总之,比较后者,前者能够一步实现索引的找出Seek filter,功用上更高。

    适应的情况:
    ICP的优化战术可用于range、ref、eq_ref、ref_or_null 类型的访问数据格局

     

    事实上并未实例不太好了然这种优化计策,照旧举多个实在列子吧。

     

    ICP优化实例

    首先个例子在网络非常多,也特别轻巧通晓.具体表结构见上文(

    下边用到的test_orderdetail表的目录为:create index idx_orderid_productname on test_orderdetail(order_id,product_name);
    查询语句为:select * from test_orderdetail where order_id = 10900 and product_name like '\0163e0496af%';
    显然,order_id = 十900是足以一向开始展览索引查找的,就算product_name也包括在复合索引中,不过product_name like '\01六叁e04九陆af%'是心有余而力不足采用索引的
    观测其试行安插,开采Extra中是Using index condition。

    ICP在此间的优化原理正是,
    在利用第2个规范 order_id = 10900 举行索引查找的历程中,同时利用product_name like '\01陆三e04玖陆af%'这些不可能直接行使索引查找的口径进行过滤。
    最后一步就足以筛选出来结果。

    澳门皇家赌场55533网址 1

      相比关闭ICP优化的景色
      假若关闭ICP优化,执行布置的Extra展现为Using where,
      意味着使用order_id = 10900拓展索引查找之后,再对结果集举行product_name like '\0163e0496af%'的筛选

    澳门皇家赌场55533网址 2

      

      第二个例证是后边本身想的,为了验证ICP的产出气象,以及确实优于非ICP优化的动静

    那二回利用的表是test_order,test_order上的目录为create index idx_userid_order_id_createdate on test_order(user_id,order_id,create_date);
    查询语句为:select * from test_order where user_id = 500 and create_date > '2015-1-1';
    与地点的例子同样,第三个筛选标准是无力回天直接采取索引的

         首先看两个的实践布置在ICP优化上的界别

    澳门皇家赌场55533网址 3

      关闭ICP之后的奉行布署

    澳门皇家赌场55533网址 4

      然后分别执在张开与关闭ICP的情况下,观望其实行进程中的profile音讯

    澳门皇家赌场55533网址 5

      查看几个sql实行的详细新闻,也即分别在开发与关闭ICP优化的意况下,如下,在stage/sql/Sending data环节有超过常规3个数量级的差异。
      也就意味着通过ICP机制的优化,server 层和 engine 层之间数据交互的次数收缩。

      澳门皇家赌场55533网址 6

      引用MySQL · 性子深入分析 · Index Condition Pushdown (ICP)中的一句话:
      在二级索引是复合索引且前边的规范过滤性非常低的状态下,张开 ICP 能够使得的回落 server 层和 engine 层之间互相的次数,从而使得的降低在运作时刻。

     

      最终,再想想3个问题,
      对于select * from test_orderdetail where order_id = 10900 and product_name like '\01六叁e04玖六af%';那个查询,
      如果order_id 包蕴在二个二级索引中,可是product_name 未有包涵在这几个二级索引中,MySQL会不会使用ICP的法子展开优化?
      答案是不是认的。
      因为ICP的前提四个查询条件包被索引覆盖,然则次选条件无法直接运用索引查找,要是次选规则从不被索引覆盖,是不能得知次选条件的值的,也就不能够 索引条件下推优化了。

      澳门皇家赌场55533网址 7

     

      

     

    Multi-Range Read(MRR)

    非MKuga途乐优化下存在的主题材料:
    率先掌握一些背景知识:MySQL的Innodb表都以集中索引表,未有显式钦定聚焦索引的地方下,会自动生成3个集中索引。
    在选取二级索引(或许说是非集中索引)进行限制查询的口径下,二级索引会依据其B树结构的叶子节点存储的集中索引进行数据的搜索(回表操作),
    而是符合条件的数据(二级索引超找的数据)有十分大大概是即兴遍布在聚焦索引B树的别的1个有个别,那样就恐怕存在表上过多的随机IO。
    当表异常的大的时候,每一行的搜寻进度都亟需在磁盘上大肆开始展览,或者会对品质产生影响。

    举个例证,
    正如图,仿效蓝线的移位轨迹,二级索引查找到的对象数据行的情理地方为1,二,三,四(首要的是以何种顺序去获取那多个任务的数码,可以私自的点子获得,也得以顺序的主意赢得,讲究就在那一点)
    在查找那两个地方的数指标时候,要是直接遵照二级索引对应的集中索引的相继查找,
    出于二级索引排序的状态下,其对应的集中索引的逐条恐怕是随机的,那么其相应的数指标情理地方也等于随便的了
    比如依照二级索引的顺去回表超找对应的多寡行,那么那几个历程就要求自由IO查找。
    这种查询办法的瑕疵,能够领略为在查询那四行数据的长河中,在大要地方距离不小的意况下,要求磁头来回摆臂来兑现(随机IO读取)。

    澳门皇家赌场55533网址 8

    M奥迪Q五Highlander多范围读取优化的目标是通过对记录的读取请求实行排序,然后再读取数据行的时候以一1IO的方法实行,防止随便IO
    毕竟是对哪些字段排序?个人感觉能够领略成二级索引范围查找到的附和的集中索引的key值举行排序。
    不改变扫描的过程可以感到是:

    (一)通过非集中索引找到对象数据的聚焦索引的key值
    (贰)对通过二级索引找到的对象数据的集中索引的key值排序,此时聚集索引与物理地方一壹对应。
    (三)(回表的经过)通过二级索引对应的雷打不动的聚焦索引,推行叁个坚持的磁盘扫描来获取数据,从而来加快读取数据的快慢。

    澳门皇家赌场55533网址 9

    次第读磁盘经常会更加快,当然也不是说这种艺术的效用总是较高的,凡事有利必有弊,也可以有两样的景色

    1,假若扫描的是一个一点都不大的数码范围,并且指标数据现已在磁盘的缓存当中,M安德拉兰德酷路泽的唯一影响是为着缓冲/排序额外的增添了有的CPU耗费。
    2,order by *** LIMIT n查询,当n值非常小的时候,恐怕会变的更加慢,
       原因是 M路虎极光LAND试图通过逐一读盘的不二等秘书籍(来或取多少),恐怕一初始读取到的数据毫无总是排在(order by ***)符合前N条的。
    叁,M冠道XC60是2个兑现进度,个人领悟,极端景况下,假诺MySQL不知底对象数据的行数,
       假设仅仅唯有壹行,依然要拓展排序操作,然后回表读取数据行,这种景色下也是因小失大的。

      打开MRR优化
      set global optimizer_switch = 'mrr=on,mrr_cost_based=off';

      启用M途乐LAND优化的前提是要开始展览书签超找,也即要回表,借使无需回表的话,二级索引本身就足以查询出来必要的字段了,未有自由IO的空子的所谓了。

    澳门皇家赌场55533网址 10

      如下截图,倘若去掉order_status,也就表示无需回表查询,那么就不会冒出MPRADO索罗德优化了。

    澳门皇家赌场55533网址 11

      同时,1旦出现M普拉多猎豹CS6优化,查询出来的结果的次第,必然是遵纪守法集中索引来排序的,那一个规律应该是简单领会的。

      澳门皇家赌场55533网址 12

     

      当然M奥迪Q7途乐优化也可能有在表关联情状下的优化措施,原理一模一样。

     

    总结:

        Index Condition Pushdown(索引条件下推)和Multi-Range Read(多范围读)皆以MySQL为了增加查询优化而备用的选项,属于MySQL伍.六内部的新特征。
        无奈楼主接触MySQL不久,见识不够,格外以为非常,高手勿喷。
        两个的联合签名的特色都以在运用索引超找(或许索引范围扫描)的历程中的一些优化措施。
        这个优化措施能够在二级索引查找(索引范围扫描)的历程中优化查询动作的行为,
        当然那几个优化措施并非总是万能的,允许用户显式张开只怕关闭,给用户充裕的任意,可是自由也毫无全盘没不符合规律,那也要求用户在做相关优化的时候供给张开丰裕的衡量和牵记。

     

    参考:

        
        
        
          

         以及各个英特网搜索……

     

     

    终极,mariadb官方这几张图异常的赞,对驾驭难点很有帮忙,先盗下来,备用(无耻一笑,O(∩_∩)O~),

    突然又想到做人了,为何一定要直来直去呢,多数时候是心急吃不了热水豆腐,迂回一下,近日停下来,好好布置陈设再启程,未必是坏事。

    澳门皇家赌场55533网址 13

    澳门皇家赌场55533网址 14

    澳门皇家赌场55533网址 15

    澳门皇家赌场55533网址 16

    澳门皇家赌场55533网址 17

     

    Condition Pushdown (ICP 索引条件下推)和Multi-Range Read(M奥迪Q5Highlander 索引多范围查找)查询优化, 本文出处:...

    【mysql】关于ICP、MRR、BKA等特性,mysqlicpmrrbka

    一、Index Condition Pushdown(ICP)

    Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化措施,从mysql5.6伊始援救,mysql五.陆事先,存款和储蓄引擎会通过遍历索引定位基表中的行,然后回来给Server层,再去为那些数据行开始展览WHERE后的尺码的过滤。mysql 五.陆后头支持ICP后,若是WHERE条件能够行使索引,MySQL 会把那有的过滤操作放到存款和储蓄引擎层,存款和储蓄引擎通过索引过滤,把满意的行从表中读取出。ICP能压缩引擎层访问基表的次数和 Server层访问存款和储蓄引擎的次数。

    • ICP的对象是减弱从基表中读取操作的数据,从而下落IO操作

    • 对此InnoDB表,ICP只适用于援救索引

    • 当使用ICP优化时,执行安排的Extra列突显Using indexcondition提醒

    • 数据库配置 optimizer_switch="index_condition_pushdown=on”;

    正文出处:
    (保留出处并非什么原创小说职务,自己拙作还远远达不到,仅仅是为着链接到最初的文章,因为接二连三对大概存在的1对荒唐实行校正或补充,无他)

    一、Index Condition Pushdown(ICP)

    Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的壹种优化措施,从mysql五.陆方始帮助,mysql5.陆事先,存款和储蓄引擎会通过遍历索引定位基表中的行,然后回来给Server层,再去为那一个多少行开始展览WHERE后的尺度的过滤。mysql 伍.陆随后帮衬ICP后,要是WHERE条件能够选择索引,MySQL 会把那部分过滤操作放到存款和储蓄引擎层,存款和储蓄引擎通过索引过滤,把满意的行从表中读收取。ICP能收缩引擎层访问基表的次数和 Server层访问存储引擎的次数。

    • ICP的靶子是压缩从基表中读取操作的数码,从而下跌IO操作

    • 对此InnoDB表,ICP只适用于帮忙索引

    • 当使用ICP优化时,实施安插的Extra列展现Using indexcondition提醒

    • 数据库配置 optimizer_switch="index_condition_pushdown=on”;

    行使境况比方

    扶助索引INDEX (a, b, c)

    SELECT * FROM peopleWHERE a='12345' AND b LIKE '%xx%'AND c LIKE '%yy%';
    

    若不利用ICP:则是透过二级索引中a的值去基表收取全部a='123四5'的数额,然后server层再对b LIKE '%xx%'AND c LIKE '%yy%' 进行过滤

    若接纳ICP:则b LIKE '%xx%'AND c LIKE '%yy%'的过滤操作在二级索引中成功,然后再去基表取相关数据

     

    利用意况举个例子

    协助索引INDEX (a, b, c)

    SELECT * FROM peopleWHERE a='12345' AND b LIKE '%xx%'AND c LIKE '%yy%';
    

    若不选取ICP:则是通过二级索引中a的值去基表收取全体a='123四伍'的数额,然后server层再对b LIKE '%xx%'AND c LIKE '%yy%' 进行过滤

    若使用ICP:则b LIKE '%xx%'AND c LIKE '%yy%'的过滤操作在二级索引中成就,然后再去基表取相关数据

    ICP特点

    • mysql 五.陆中只援助 MyISAM、InnoDB、NDB cluster

    • mysql 5.陆中不援助分区表的ICP,从MySQL 伍.7.3上马补助分区表的ICP

    • ICP的优化计策可用于range、ref、eq_ref、ref_or_null 类型的造访数据情势

    • 不匡助主建索引的ICP(对于Innodb的聚焦索引,完整的笔录已经被读取到Innodb Buffer,此时采纳ICP并不可能减低IO操作)

    • 当 SQL 使用覆盖索引时但只检索部分数据时,ICP 不能够运用

    • ICP的加速效果取决于在仓库储存引擎内经过ICP筛选掉的多少的比例

     

    ICP特点

    • mysql 5.陆中只协助 MyISAM、InnoDB、NDB cluster

    • mysql 5.六中不援助分区表的ICP,从MySQL 伍.7.叁方始帮忙分区表的ICP

    • ICP的优化攻略可用于range、ref、eq_ref、ref_or_null 类型的拜会数据方式

    • 不援助主建索引的ICP(对于Innodb的集中索引,完整的笔录已经被读取到Innodb Buffer,此时利用ICP并不可能减低IO操作)

    • 当 SQL 使用覆盖索引时但只检索部分数据时,ICP 不可能运用

    • ICP的增长速度效果取决于在仓储引擎内经过ICP筛选掉的数量的比例

    二、Multi-Range Read (MRR)

    MXC60奥德赛 的完备是 Multi-Range Read Optimization,是优化器将随机 IO 转化为顺序 IO 以下降查询进度中 IO 费用的一种手腕,这对IO-bound类型的SQL语句质量带来巨大的进级换代,适用于range ref eq_ref类型的询问

    M哈弗途观优化的多少个便宜

    使数据访问有自由变为顺序,查询扶助索引是,首先把询问结果根据主键实行排序,遵照主键的各类举行书签查找

    削减缓冲池中页被调换的次数

    批量甩卖对键值的操作

    ICP优化原理

    二、Multi-Range Read (MRR)

    M奇骏CR-V 的全称是 Multi-Range Read Optimization,是优化器将随便 IO 转化为顺序 IO 以减低查询进程中 IO 费用的一种手腕,这对IO-bound类型的SQL语句质量带来巨大的晋升,适用于range ref eq_ref类型的询问

    MENVISION奥迪Q伍优化的多少个便宜

    使数据访问有自由变为顺序,查询支持索引是,首先把询问结果依照主键进行排序,依据主键的逐1实行书签查找

    减掉缓冲池中页被轮换的次数

    批量甩卖对键值的操作

    在一向不利用MRubicon普拉多天性时

    率先步 先依照where条件中的帮忙索引获取援助索引与主键的聚众,结果集为rest

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

    第二步 通过第1步获取的主键来获得相应的值

    for each pk_column value in rest do:
    select non_key_column from tb where pk_column=val
    

    Index Condition Pushdown (ICP),也称为索引条件下推,体今后进行安顿的上是会出现Using index condition(Extra列,当然Extra列的消息太多了,只可以做简单深入分析)
    ICP原理通俗讲就是,查询进程中,直接在查询引擎层的API获取数据的时候兑现"非直接索引"过滤条件的筛选,而不是查询引擎层查询出来以往在Server层筛选。
    换句话说正是ICP在获取数据的还要落到实处了where的次选条件中不能直接采纳索引的意况下的筛选,防止了未曾ICP优化的时候分多少个步骤的贯彻(获取数据的经过未有做次选规则的过滤)
    壹旦是非ICP优化查询的话,是两步,第贰步是获取数据,第3步是赢得的数量开始展览标准筛选。
    明显,相比较后者,前者能够一步达成索引的查究Seek filter,效用上越来越高。

    在从来不利用M途胜BMWX三特性时

    首先步 先依据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特性时

    先是步 先依据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)
    

    在不使用 M冠道奥迪Q3 时,优化器供给依据二级索引再次回到的笔录来进展“回表”,这几个历程相似会有较多的即兴IO, 使用M昂科威昂Cora时,SQL语句的实行进度是那样的:

    • 优化器将二级索引查询到的笔录停放1块缓冲区中

    • 要是二级索引围观到文件的末段只怕缓冲区已满,则接纳便捷排序对缓冲区中的内容依照主键实行排序

    • 用户线程调用M奥迪Q7奥德赛接口取cluster index,然后遵照cluster index 取行数据

    • 当根据缓冲区中的 cluster index取完数据,则接二连三调用进程 二) 3),直至扫描截至

    透过上述进程,优化器将二级索引随机的 IO 进行排序,转化为主键的不改变排列,从而完成了猖獗 IO 到各类 IO 的转账,提高质量

    适于的景色:
    ICP的优化战略可用于range、ref、eq_ref、ref_or_null 类型的拜访数据方式

    使用MRR特性时

    第壹步 先依照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)
    

    在不采用 M福睿斯Sportage 时,优化器须求依靠二级索引重返的记录来开始展览“回表”,那一个历程一般会有较多的随便IO, 使用MGL450冠道时,SQL语句的施行进程是这般的:

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

    • 如果二级索引围观到文件的结尾也许缓冲区已满,则动用高效排序对缓冲区中的内容依据主键进行排序

    • 用户线程调用MMurano奥德赛接口取cluster index,然后依据cluster index 取行数据

    • 当根据缓冲区中的 cluster index取完数据,则接二连三调用进程 二) 叁),直至扫描停止

    经过上述进度,优化器将二级索引随机的 IO 进行排序,转化为主键的有序排列,从而完结了随机 IO 到种种 IO 的转会,升高质量

    其余MGL450库罗德还足以将某个范围查询,拆分为键值对,来进行批量的数码查询,如下:

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

    表t上有二级索引(key_part1, key_part二),索引依据key_part1,key_part二的相继排序。

    若不行使M讴歌MDXCRUISER:此时询问的类别为Range,sql优化器会先将key_part一大于一千稍差于两千的数目收取,尽管key_part二不等于10000,带抽取之后再开始展览过滤,会促成众多不行的多寡被收取

    若使用MRR:若是索引中key_part二不为一千0的元组越来越多,最后M哈弗RAV4的职能越好。优化器会将查询条件拆分为(1000,1000),(100一,1000),... (一玖九八,一千)最后会基于那么些标准实行过滤

     

    除此以外MKoleos奥迪Q伍还足以将一些范围查询,拆分为键值对,来开始展览批量的多寡查询,如下:

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

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

    若不使用MXC90奥迪Q5:此时查询的项目为Range,sql优化器会先将key_part1大于一千稍低于贰仟的多少抽出,纵然key_part2不对等一千0,带收取之后再开始展览过滤,会招致看不尽空头的数目被抽出

    若使用MRR:若是索引中key_part二不为一千0的元组越来越多,最后M陆风X八卡宴的职能越好。优化器会将查询条件拆分为(一千,一千),(100一,1000),... (1997,一千)最后会依照这么些原则进行过滤

    有关参数

    当mrr=on,mrr_cost_based=on,则意味着cost base的法门还挑选启用MEnclaveXC60优化,当开掘优化后的代价过高时就能不利用该项优化

    当mrr=on,mrr_cost_based=off,则代表总是敞开M大切诺基途乐优化

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

    参数read_rnd_buffer_size 用来决定键值缓冲区的高低。二级索引围观到文件的最后大概缓冲区已满,则运用高效排序对缓冲区中的内容依照主键举办排序

    实则并未有实例不太好掌握这种优化计策,依旧举多个实在列子吧。

    相关参数

    当mrr=on,mrr_cost_based=on,则代表cost base的办法还甄选启用M福睿斯翼虎优化,当发掘优化后的代价过高时就能够不接纳该项优化

    当mrr=on,mrr_cost_based=off,则象征总是敞开M奥迪Q7Rubicon优化

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

    参数read_rnd_buffer_size 用来调整键值缓冲区的大大小小。二级索引围观到文件的末尾或许缓冲区已满,则利用便捷排序对缓冲区中的内容依据主键进行排序

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

    Batched Key Access (BKA)  升高表join质量的算法。当被join的表可以采用索引时,就先排好顺序,然后再去索求被join的表,听上去和MBMWX五猎豹CS六类似,实际上MENVISIONPRADO也得以设想成二级索引和 primary key的join

    假诺被Join的表上未有索引,则应用老版本的BNL攻略(BLOCK Nested-loop)

     

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

    Batched Key Access (BKA)  升高表join质量的算法。当被join的表能够运用索引时,就先排好顺序,然后再去寻觅被join的表,听上去和MSportage昂Cora类似,实际上MRRAV四也得以设想成二级索引和 primary key的join

    假使被Join的表上未有索引,则采纳老版本的BNL计策(BLOCK Nested-loop)

    BKA原理

    对此多表join语句,当MySQL使用索引访问第3个join表的时候,使用3个join buffer来收罗第三个操作对象生成的连带列值。BKA营造好key后,批量传给引擎层做索引查找。key是经过M帕Jero昂Cora接口提交给引擎的(mrr指标是比较顺序)M宝马X5奥迪Q3使得查询更有作用。 

    粗粗的长河如下:

    • BKA使用join buffer保存由join的第2个操作产生的符合条件的数目

    • 然后BKA算法营造key来访问被接连的表,并批量应用M奥迪Q5Sportage接口提交keys到数据仓库储存款和储蓄引擎去追寻查找。

    • 交由keys之后,MCR-VHaval使用最好的秘技来获取行并上报给BKA

    BNL和BKA都是批量的交由1部分行给被join的表,从而收缩访问的次数,那么它们有怎样分别吧?

    • BNL比BKA出现的早,BKA直到5.陆才现身,而NBL至少在伍.壹里面就存在。

    • BNL重要用以当被join的表上无索引

    • BKA首假诺指在被join表上有索引能够利用,那么就在行提交给被join的表从前,对那个行遵照索引字段进行排序,由此收缩了率性IO,排序这才是双边最大的分化,可是假诺被join的表没用索引呢?那就利用NBL

    ICP优化实例

    BKA原理

    对此多表join语句,当MySQL使用索引访问第三个join表的时候,使用贰个join buffer来搜集第一个操作对象生成的相干列值。BKA创设好key后,批量传给引擎层做索引查找。key是通过M索罗德大切诺基接口提交给引擎的(mrr指标是较为顺序)M本田UR-V本田CR-V使得查询更有功用。 

    粗粗的经过如下:

    • BKA使用join buffer保存由join的率先个操作产生的符合条件的数据

    • 然后BKA算法营造key来访问被三番五次的表,并批量采取MCRUISER哈弗接口提交keys到数据仓库储存款和储蓄引擎去搜寻查找。

    • 付出keys之后,MTucsonEscort使用最好的艺术来获取行并报告给BKA

    BNL和BKA都以批量的交给1部分行给被join的表,从而减少访问的次数,那么它们有哪些分别呢?

    • BNL比BKA出现的早,BKA直到伍.陆才面世,而NBL至少在五.一里边就存在。

    • BNL首要用来当被join的表上无索引

    • BKA主假诺指在被join表上有索引能够应用,那么就在行提交给被join的表在此之前,对这一个行根据索引字段进展排序,因而削减了随机IO,排序那才是两个最大的区分,不过1旦被join的表没用索引呢?这就应用NBL

    BKA和BNL标识

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

    首先个例子在互连网非常多,也极度轻便掌握.具体表结构见上文()

    BKA和BNL标识

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

    连锁参数

    BAK使用了M中华VLacrosse,要想行使BAK必须打开M途胜Tiggo功效,而M锐界途睿欧基于mrr_cost_based的财力估量并不能够担保总是利用MENCOREQX56,官方推荐设置mrr_cost_based=off来延续敞开MGL450CR-V作用。展开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

     

    下边用到的test_orderdetail表的目录为:create index idx_orderid_productname on test_orderdetail(order_id,product_name);
    查询语句为:select * from test_orderdetail where order_id = 10900 and product_name like '\0163e0496af%';
    显然,order_id = 10900是足以平昔开展索引查找的,即使product_name也含有在复合索引中,不过product_name like '\01陆三e04玖陆af%'是无力回天利用索引的
    重点其试行安插,开掘Extra中是Using index condition。

    有关参数

    BAK使用了MSportage奥迪Q7,要想使用BAK必须展开M福睿斯卡宴功用,而M凯雷德Rubicon基于mrr_cost_based的资金预计并无法确认保障总是利用M途观猎豹CS6,官方推荐设置mrr_cost_based=off来延续敞开M奥迪Q5LAND功效。张开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是用索引去表里取多少的壹种优化,收缩了引擎层访问基表的次数和Server层访问存款和储蓄引擎的次数,在引擎层就可以过滤掉大量的数目,收缩io次数,进步查询语句品质

    MRR(Multi-Range Read)

    是依附援救/第3索引的询问,减少自由IO,并且将随便IO转化为各种IO,进步查询功用。

    • 不使用MRR之前(MySQL5.陆之前),先遵照where条件中的援助索引获取扶助索引与主键的集中,再经过主键来获得相应的值。帮忙索引获取的主键来访问表中的数据会导致大肆的IO(扶助索引的仓库储存顺序并非与主键的各样壹致),随机主键不在同3个page里时会导致多次IO和任性读。

    • 使用MRR优化(MySQL5.6过后),先遵照where条件中的帮助索引获取协理索引与主键的集聚,再将结果集放在buffer(read_rnd_buffer_size 直到buffer满了),然后对结果集遵照pk_column排序,得到稳步的结果集rest_sort。最终选择已经排序过的结果集,访问表中的数码,此时是逐1IO。即MySQL 将依附协助索引获取的结果集依据主键进行排序,将冬季化为有序,可以用主键顺序访问基表,将随便读转化为各种读,多页数据记录可壹遍性读入或依照此番的主键范围分次读入,减弱IO操作,提高查询成效。

     

    *Nested Loop Join算法*

    将驱动表/外部表的结果集作为循环基础数据,然后循环该结果集,每一遍获得一条数据作为下3个表的过滤条件查询数据,然后合并结果,获取结果集重临给客户端。Nested-Loop3回只将1行传入内层循环, 所以外层循环(的结果集)有个别许行, 内部存款和储蓄器循环便要实行稍微次,效用特别差。


    Block Nested-Loop Join*算法

    将外层循环的行/结果集存入join buffer, 内层循环的每1行与1切buffer中的记录做比较,从而收缩内层循环的次数。主要用以当被join的表上无索引。


    Batched Key Access*算法

    当被join的表能够运用索引时,就先好顺序,然后再去追寻被join的表。对那一个行依据索引字段张开排序,因此减去了随意IO。假如被Join的表上未有索引,则运用老版本的BNL计策(BLOCK Nested-loop)。

     

    参考:

    ICP在那边的优化原理正是,
    在利用第1个原则 order_id = 拾900 实行索引查找的进程中,同时利用product_name like '\01陆叁e049陆af%'那个不可能直接使用索引查找的规范实行过滤。
    终极一步就能够筛选出来结果。

    四、总结

    ICP(Index Condition Pushdown)

    Index Condition Pushdown是用索引去表里取多少的一种优化,减弱了引擎层访问基表的次数和Server层访问存款和储蓄引擎的次数,在引擎层就能够过滤掉大量的多少,裁减io次数,升高查询语句性能

    MRR(Multi-Range Read)

    是基于援助/第二索引的查询,缩小自由IO,并且将轻松IO转化为各种IO澳门皇家赌场55533网址,,升高查询效用。

    • 不使用MRR之前(MySQL5.陆事先),先依据where条件中的帮忙索引获取帮助索引与主键的群集,再经过主键来博取相应的值。支持索引获取的主键来访问表中的数据会变成大肆的IO(帮助索引的积累顺序并非与主键的逐1一致),随机主键不在同一个page里时会导致数十次IO和轻巧读。

    • 使用MRR优化(MySQL五.6之后),先依照where条件中的帮忙索引获取匡助索引与主键的集纳,再将结果集放在buffer(read_rnd_buffer_size 直到buffer满了),然后对结果集依据pk_column排序,得到稳步的结果集rest_sort。最后选择已经排序过的结果集,访问表中的多寡,此时是各样IO。即MySQL 将基于帮忙索引获取的结果集遵照主键举行排序,将冬日化为有序,可以用主键顺序访问基表,将轻便读转化为顺序读,多页数据记录可3遍性读入或基于本次的主键范围分次读入,收缩IO操作,进步查询成效。

     

    *Nested Loop Join算法*

    将驱动表/外部表的结果集作为循环基础数据,然后循环该结果集,每一趟获得一条数据作为下二个表的过滤条件查询数据,然后合并结果,获取结果集重返给客户端。Nested-Loop一次只将1行传入内层循环, 所以外层循环(的结果集)有稍许行, 内部存款和储蓄器循环便要实行稍微次,作用特别差。


    Block Nested-Loop Join*算法

    将外层循环的行/结果集存入join buffer, 内层循环的每壹行与总体buffer中的记录做相比,从而减弱内层循环的次数。首要用于当被join的表上无索引。


    Batched Key Access*算法

    当被join的表能够使用索引时,就先好顺序,然后再去寻觅被join的表。对这几个行根据索引字段举办排序,因此削减了大肆IO。假若被Join的表上未有索引,则使用老版本的BNL计谋(BLOCK Nested-loop)。

     

    参考:

    壹、Index Condition Pushdown(ICP) Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的1种优化...

    set optimizer_switch='index_condition_pushdown=off或者on'

    澳门皇家赌场55533网址 18

      比较关闭ICP优化的情况
      假设关闭ICP优化,实行布署的Extra突显为Using where,
      意味着使用order_id = 10900开始展览索引查找之后,再对结果集举办product_name like '\0163e0496af%'的筛选

    澳门皇家赌场55533网址 19

    本文由68399皇家赌场发布于虚拟主机,转载请注明出处:【mysql】关于ICP、M奥德赛翼虎、BKA等个性

    关键词: 68399皇家赌场 mysql ICP MRR BKA

上一篇:转:MySQL与Oracle的区分,mysqloracle差距

下一篇:没有了