您的位置:1010cc时时彩经典版 > 1010cc安卓版 > 1010cc时时彩经典版MySql联接算法,查询优化之

1010cc时时彩经典版MySql联接算法,查询优化之

发布时间:2019-08-31 09:12编辑:1010cc安卓版浏览(119)

    MySQL 查询优化之 Block Nested-Loop 与 Batched Key Access Joins

    在MySQL中,能够运用批量密钥访谈(BKA)连接算法,该算法使用对连接表的目录访谈和连接缓冲区。

    BKA算法援助:内接连,外接连和半连接操作,包括嵌套外接连。

    BKA的优点:越来越高效的表扫描升高了接二连三属性。

    其余,先前仅用于内连接的块嵌套循环(BNL)连接算法现已扩展,可用以外连接半连接操作,包括嵌套外连接

    以下一些探究了连接缓冲区处理,它是原始BNL算法扩充,扩大BNL算法和BKA算法的功底。 有关半连连战术的信息,请参见“使用半两次三番调换优化子查询,派生表和视图援引”

    • Nested Loop Join 算法

    • Block Nested-Loop 算法

    • Batched Key Access 算法

    • BNL和BKA算法的优化器Hint

    连着算法是MySql数据库用于拍卖联接的情理计策。在MySql 5.5版本仅扶助Nested-Loops Join算法,若是联接表上有索引时,Nested-Loops Join是十一分迅猛的算法。即便有目录时间复杂度为O(N),若未有索引,则可身为最坏的状态,时间复杂度为O(N²)。MySql依照差异的采取情形,帮衬三种Nested-Loops Join算法,一种是Simple Nested-Loops Join算法,别的一种是Block Nested-Loops Join算法。

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

    一、Index Condition Pushdown(ICP)

    Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化措施,从mysql5.6上马协助,mysql5.6事先,存款和储蓄引擎会通过遍历索引定位基表中的行,然后重回给Server层,再去为这几个数量行开展WHERE后的基准的过滤。mysql 5.6之后辅助ICP后,若是WHERE条件能够利用索引,MySQL 会把这一部分过滤操作放到存款和储蓄引擎层,存款和储蓄引擎通过索引过滤,把满足的行从表中读收取。ICP能减小引擎层访谈基表的次数和 Server层访谈存款和储蓄引擎的次数。

    • ICP的指标是缩减从基表中读取操作的数额,进而裁减IO操作

    • 对于InnoDB表,ICP只适用于支持索引

    • 当使用ICP优化时,推行陈设的Extra列突显Using indexcondition提醒

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

    一、Index Condition Pushdown(ICP)

    Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化措施,从mysql5.6发端支持,mysql5.6以前,存款和储蓄引擎会通过遍历索引定位基表中的行,然后回到给Server层,再去为那个数量行举办WHERE后的标准的过滤。mysql 5.6自此协理ICP后,如若WHERE条件能够采纳索引,MySQL 会把那部分过滤操作放到存款和储蓄引擎层,存款和储蓄引擎通过索引过滤,把满意的行从表中读抽出。ICP能压缩引擎层访问基表的次数和 Server层访谈存款和储蓄引擎的次数。

    • ICP的目的是缩短从基表中读取操作的多少,从而减弱IO操作

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

    • 当使用ICP优化时,奉行安插的Extra列显示Using indexcondition提示

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

    Nested Loop Join算法

    将外层表的结果集作为循环的底子数据,然后循环从该结果集每便一条获取数据作为下四个表的过滤条件去查询数据,然后合併结果。假诺有多个表join,那么相应将眼下的表的结果集作为循环数据,取结果集中的每一行再到下多少个表中继续扩充巡回相称,获取结果集并重返给客商端。

    伪代码如下

    for each row in t1 matching range {
      for each row in t2 matching reference key {
         for each row in t3 {
          if row satisfies join conditions,
          send to client
        }
      }
     }
    

     

    常备的Nested-Loop Join算法叁遍只可以将一行数据传入内部存储器循环,所以外层循环结果集有多少行,那么内部存款和储蓄器循环将要实施稍微次。

    ###Simple Nested-Loops Join算法 从一张表中年老年是读取一条记下,然后将记录与嵌套表中的笔录实行相比较。算法如下:

    一、Index Condition Pushdown(ICP)

    Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化措施,从mysql5.6发端帮助,mysql5.6事先,存款和储蓄引擎会通过遍历索引定位基表中的行,然后再次回到给Server层,再去为这一个多少行开展WHERE后的尺度的过滤。mysql 5.6从此援助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='12345'的多寡,然后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='12345'的数目,然后server层再对b LIKE '%xx%'AND c LIKE '%yy%' 进行过滤

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

    Block Nested-Loop算法

    MySQL BNL算法原来只帮衬内连接,未来已扶助外连接半连接操作,包括嵌套外连接

    BNL算法原理:将外层循环的行/结果集存入join buffer,内部存款和储蓄器循环的每一行数据与一切buffer中的记录做相比,可以减去内层循环的围观次数

    举个简易的例子:外层循环结果集有1000行数据,使用NLJ算法须要扫描内层表1000次,但要是使用BNL算法,则先收取外层表结果集的100行寄放到join buffer, 然后用内层表的每一行数据去和那100行结果集做相比,能够三次性与100行数据开展比较,那样内层表其实只须要循环一千/100=14回,裁减了9/10。

    伪代码如下

    for each row in t1 matching range {
       for each row in t2 matching reference key {
        store used columns from t1, t2 in join buffer
        if buffer is full {
          for each row in t3 {
             for each t1, t2 combination in join buffer {
              if row satisfies join conditions,
              send to client
            }
            }
           empty buffer
         }
       }
     }
    
     if buffer is not empty {
        for each row in t3 {
         for each t1, t2 combination in join buffer {
           if row satisfies join conditions,
           send to client
          }
       }
     }
    

     

    假使t1, t2涉企join的列长度只和为s, c为两个组合数, 那么t3表被围观的次数为

    (S * C)/join_buffer_size   1
    

     

    扫描t3的次数随着join_buffer_size的附加而减少, 直到join buffer能够容纳所有的t1, t2整合, 再增大join buffer size, query 的进程就不会再变快了。

     

    optimizer_switch系统变量的block_nested_loop标记调节优化器是不是采用块嵌套循环算法。

    默许情形下,block_nested_loop已启用。

    在EXPLAIN输出中,当Extra值包含Using join buffer(Block Nested Loop)type值为ALL,index或range时,表示使用BNL。

    示例

    mysql> explain SELECT  a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
     ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ---------------------------------------------------- 
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
     ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ---------------------------------------------------- 
    |  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 298936 |   100.00 | NULL                                               |
    |  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331143 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
     ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ---------------------------------------------------- 
    2 rows in set, 1 warning (0.00 sec)
    

     

    For each row r in R do
        For each row s in S do
            If r and s satisfy the join condition
                Then output the tuple <r, s>
    

    使用意况譬喻

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

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

    若不采纳ICP:则是经过二级索引中a的值去基表抽出全体a='12345'的数目,然后server层再对b LIKE '%xx%'AND c LIKE '%yy%' 进行过滤

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

    ICP特点

    • mysql 5.6中只援助 MyISAM、InnoDB、NDB cluster

    • mysql 5.6中不协助分区表的ICP,从MySQL 5.7.3初叶援助分区表的ICP

    • ICP的优化战术可用于range、ref、eq_ref、ref_or_null 类型的拜会数据格局

    • 不补助主建索引的ICP(对于Innodb的聚焦索引,完整的笔录已经被读取到Innodb Buffer,此时采用ICP并不能够收缩IO操作)

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

    • ICP的加快效果取决于在蕴藏引擎内经过ICP筛选掉的数码的比重

    ICP特点

    • mysql 5.6中只支持 MyISAM、InnoDB、NDB cluster

    • mysql 5.6中不帮助分区表的ICP,从MySQL 5.7.3起首帮助分区表的ICP

    • ICP的优化计谋可用于range、ref、eq_ref、ref_or_null 类型的探望数据形式

    • 不辅助主建索引的ICP(对于Innodb的聚焦索引,完整的记录已经被读取到Innodb Buffer,此时选用ICP并无法裁减IO操作)

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

    • ICP的加速效果取决于在蕴藏引擎内经过ICP筛选掉的多寡的百分比

    Batched Key Access 算法

    对于多表join语句,当MySQL使用索引访谈第二个join表的时候,使用一个join buffer来搜集第三个操作对象生成的连带列值。BKA创设好key后,批量传给引擎层做索引查找。key是因此M普拉多奔驰G级接口提交给引擎的,那样,M途乐Sportage使得查询更有成效。

    假使外界表扫描的是主键,那么表中的记录会见都以比较平稳的,不过一旦连接的列是非主键索引,那么对于表中著录的探问也许正是可怜离散的。由此对于非主键索引的连接,Batched Key Access Join算法将能小幅度升高SQL的实施效用。BKA算法援救内延续,外接连和半连接操作,富含嵌套外接连。

    Batched Key Access Join算法的做事步骤如下:

    • 1) 将表面表中相关的列归入Join Buffer中。

    • 2) 批量的将Key(索引键值)发送到Multi-Range Read(M普拉多Sportage)接口。

    • 3) Multi-Range Read(MEvoque兰德Sportage)通过收取的Key,依照其对应的ROWID进行排序,然后再举行数量的读取操作。

    • 4) 再次来到结果集给顾客端。

    Batched Key Access Join算法的面目上的话依旧Simple Nested-Loops Join算法,其爆发的口径为内部表上有索引,况兼该索引为非主键,何况连接须求会见内部表主键上的目录。那时Batched Key Access Join算法会调用Multi-Range Read(MSportage福特Explorer)接口,批量的进行索引键的相配和主键索引上获取数据的操作,以此来抓牢联接的实践效用,因为读取数据是以一一磁盘IO实际不是随机磁盘IO进行的。

    使用BKA时,join_buffer_size的值定义了对存款和储蓄引擎的各种乞求中批量密钥的大小。缓冲区越大,对三番五次操作的左边表的各样访问就越来越多,那能够显着提升品质。

    要使用BKA,必须将optimizer_switch系统变量的batched_key_access标记设置为on。 BKA使用M奥迪Q5传祺,因而mrr标记也非得打开。方今,M奥迪Q3奔驰G级的血本推断过于悲观。由此,mrr_cost_based也必须关闭本事动用BKA。

    以下设置启用BKA:

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

     

    在EXPLAIN输出中,当Extra值包含Using join buffer(Batched Key Access)且类型值为refeq_ref时,表示使用BKA。

    示例:

    mysql> show index from employees;
     ----------- ------------ ---------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
    | Table     | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
     ----------- ------------ ---------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
    | employees |          0 | PRIMARY        |            1 | emp_no      | A         |      298936 |     NULL | NULL   |      | BTREE      |         |               |
    | employees |          1 | idx_name       |            1 | last_name   | A         |        1679 |     NULL | NULL   |      | BTREE      |         |               |
    | employees |          1 | idx_name       |            2 | first_name  | A         |      277495 |     NULL | NULL   |      | BTREE      |         |               |
    | employees |          1 | idx_birth_date |            1 | birth_date  | A         |        4758 |     NULL | NULL   |      | BTREE      |         |               |
     ----------- ------------ ---------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
    4 rows in set (0.00 sec)
    
    
    mysql> explain SELECT a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
     ---- ------------- ------- ------------ ------ ---------------- ---------------- --------- ----------------------- -------- ---------- ------- 
    | id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows   | filtered | Extra |
     ---- ------------- ------- ------------ ------ ---------------- ---------------- --------- ----------------------- -------- ---------- ------- 
    |  1 | SIMPLE      | b     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                  | 331143 |   100.00 | NULL  |
    |  1 | SIMPLE      | a     | NULL       | ref  | idx_birth_date | idx_birth_date | 3       | employees.b.from_date |     62 |   100.00 | NULL  |
     ---- ------------- ------- ------------ ------ ---------------- ---------------- --------- ----------------------- -------- ---------- ------- 
    
    #使用hint,强制走bka
    
    mysql> explain SELECT /*  bka(a)*/ a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
     ---- ------------- ------- ------------ ------ ---------------- ---------------- --------- ----------------------- -------- ---------- ---------------------------------------- 
    | id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows   | filtered | Extra                                  |
     ---- ------------- ------- ------------ ------ ---------------- ---------------- --------- ----------------------- -------- ---------- ---------------------------------------- 
    |  1 | SIMPLE      | b     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                  | 331143 |   100.00 | NULL                                   |
    |  1 | SIMPLE      | a     | NULL       | ref  | idx_birth_date | idx_birth_date | 3       | employees.b.from_date |     62 |   100.00 | Using join buffer (Batched Key Access) |
     ---- ------------- ------- ------------ ------ ---------------- ---------------- --------- ----------------------- -------- ---------- ---------------------------------------- 
    2 rows in set, 1 warning (0.00 sec)
    

     

    就算在两张表Koleos和S上举办连接的列都不分包索引,算法的围观次数为:LX570 索罗德xS,扫描开销为O(智跑xS)。

    ICP特点

    • mysql 5.6中只帮助 MyISAM、InnoDB、NDB cluster

    • mysql 5.6中不帮忙分区表的ICP,从MySQL 5.7.3起头帮忙分区表的ICP

    • ICP的优化计谋可用于range、ref、eq_ref、ref_or_null 类型的拜谒数据格局

    • 不帮忙主建索引的ICP(对于Innodb的聚焦索引,完整的笔录已经被读取到Innodb Buffer,此时采取ICP并不能够下降IO操作)

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

    • ICP的加快效果取决于在蕴藏引擎内通过ICP筛选掉的多寡的百分比

    二、Multi-Range Read (MRR)

    M途乐Evoque 的齐全部都以 Multi-Range Read Optimization,是优化器将随便 IO 转化为各类 IO 以减低查询进程中 IO 开支的一种花招,那对IO-bound类型的SQL语句质量带来非常大的升官,适用于range ref eq_ref类型的查询

    M福特ExplorerRAV4优化的多少个好处

    使数据访谈有专断变为顺序,查询帮助索引是,首先把询问结果遵照主键实行排序,遵照主键的一一实行书签查找

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

    批量拍卖对键值的操作

    二、Multi-Range Read (MRR)

    M中华VEnclave 的齐全部都以 Multi-Range Read Optimization,是优化器将轻便 IO 转化为顺序 IO 以减少查询进度中 IO 开支的一种花招,那对IO-bound类型的SQL语句品质带来巨大的升官,适用于range ref eq_ref类型的询问

    MLAND牧马人优化的几个实惠

    使数码访谈有自由变为顺序,查询帮忙索引是,首先把询问结果依照主键举行排序,按执照主人键的顺序进行书签查找

    压缩缓冲池中页被替换的次数

    批量拍卖对键值的操作

    BNL和BKA算法的优化器Hint

    除开使用optimizer_switch系统变量来决定优化程序在对话范围内采纳BNL和BKA算法之外,MySQL还帮忙优化程序提醒,以便在各种语句的根基上影响优化程序。 请参见“优化程序Hint”。

    要使用BNL或BKA提示为外界联接的另外内部表启用联接缓冲,必得为外界联接的有所内部表启用联接缓冲。

    1010cc时时彩经典版 1

    使用qb_name

    SELECT /*  QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...
      FROM (SELECT /*  QB_NAME(qb2) */ ...
      FROM (SELECT /*  QB_NAME(qb3) */ ... FROM ...)) ...
    

     

    假如t1,t2和t3三张表试行INNE酷威 JOIN查询,并且每张表使用的交接类型如下:

    二、Multi-Range Read (MRR)

    MLANDENVISION 的全称是 Multi-Range Read Optimization,是优化器将随机 IO 转化为顺序 IO 以收缩查询进度中 IO 成本的一种手腕,那对IO-bound类型的SQL语句品质带来巨大的提拔,适用于range ref eq_ref类型的询问

    MLAND奥迪Q7优化的多少个好处

    使数据访谈有自由变为顺序,查询扶助索引是,首先把询问结果根据主键进行排序,依照主键的各样举办草签查找

    减掉缓冲池中页被沟通的次数

    批量甩卖对键值的操作

    在平素不运用MTiguan揽胜极光天性时

    先是步 先遵照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
    

    在并没有动用M途Love睿斯性情时

    首先步 先依据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
    
    Table   Join Type
    t1      range
    t2      ref
    t3      ALL
    

    在尚未接纳M奥迪Q5奥迪Q5特性时

    率先步 先依照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锐界奇骏 时,优化器供给依附二级索引再次来到的记录来进行“回表”,那些历程一般会有比较多的自由IO, 使用MWranglerCRUISER时,SQL语句的施行进度是这么的:

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

    • 万一二级索引围观到文件的最后恐怕缓冲区已满,则利用高效排序对缓冲区中的内容依照主键进行排序

    • 顾客线程调用M福特Explorer冠道接口取cluster index,然后依照cluster index 取行数据

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

    由此上述进度,优化器将二级索引随机的 IO 进行排序,转化为主键的有序排列,进而完成了自由 IO 到各类 IO 的转会,进步质量

    使用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)
    

    在不使用 MRAV4凯雷德 时,优化器须求依赖二级索引再次回到的笔录来举行“回表”,那么些历程相似会有比较多的轻便IO, 使用M中华VLacrosse时,SQL语句的实施进度是如此的:

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

    • 借使二级索引围观到文件的末梢或然缓冲区已满,则动用高效排序对缓冲区中的内容遵照主键实行排序

    • 顾客线程调用MPAJEROENVISION接口取cluster index,然后根据cluster index 取行数据

    • 当依照缓冲区中的 cluster index取完数据,则继续调用进度 2) 3),直至扫描甘休

    通过上述进度,优化器将二级索引随机的 IO 举办排序,转化为主键的平稳排列,进而实现了随意 IO 到各类 IO 的转会,提高质量

    假若运用了Simple Nested-Loops Join算法,则算法达成的伪代码如下:

    使用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途锐瑞虎 时,优化器要求依据二级索引再次回到的笔录来展开“回表”,这几个进度一般会有相当多的妄动IO, 使用M奇骏CRUISER时,SQL语句的试行进程是这么的:

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

    • 固然二级索引围观到文件的末尾只怕缓冲区已满,则运用高效排序对缓冲区中的内容根据主键实行排序

    • 客商线程调用MOdysseyQX56接口取cluster index,然后依照cluster index 取行数据

    • 当遵照缓冲区中的 cluster index取完数据,则继续调用进程 2) 3),直至扫描截至

    由此上述进度,优化器将二级索引随机的 IO 举办排序,转化为主键的平稳排列,进而完成了自由 IO 到各种 IO 的转向,进步品质

    其余MPAJERO本田UR-V还足以将或多或少范围查询,拆分为键值对,来进行批量的数目查询,如下:

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

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

    若不应用M昂CoraHaval:此时询问的类型为Range,sql优化器会先将key_part1大于一千低于3000的数量抽取,尽管key_part2不等于一千0,带抽取之后再进行过滤,会导致众多不算的多寡被抽出

    若使用MRR:即使索引中key_part2不为一千0的元组愈来愈多,最后MEscort奥迪Q5的效应越好。优化器会将查询条件拆分为(一千,一千),(1001,1000),... (1996,一千)最后会依赖这几个条件进行过滤

    除此以外MSportage猎豹CS6还足以将有个别范围查询,拆分为键值对,来开展批量的多少查询,如下:

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

    表t上有二级索引(key_part1, key_part2),索引依据key_part1,key_part2的次第排序。

    若不利用MENCORE景逸SUV:此时查询的等级次序为Range,sql优化器会先将key_part1大于一千稍差于贰仟的多少抽取,纵然key_part2不对等一千0,带抽出之后再拓宽过滤,会变成众多无效的数目被收取

    若使用MRR:即使索引中key_part2不为壹仟0的元组越来越多,最终MEscort奥德赛的成效越好。优化器会将查询条件拆分为(一千,一千),(1001,1000),... (1997,一千)最后会依据那些原则举行过滤

    for each row in t1 matching range {
      for each row in t2 matching reference key {
        for each row in t3 {
          if row satisfies join conditions,
          send to client
        }
      }
    }
    

    别的M揽胜昂Cora还足以将或多或少范围查询,拆分为键值对,来拓宽批量的数目查询,如下:

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

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

    若不行使M奥迪Q5XC60:此时查询的类型为Range,sql优化器会先将key_part1大于一千稍差于3000的数量抽出,固然key_part2不对等一千0,带抽取之后再扩充过滤,会变成无尽失效的多少被抽取

    若使用MRR:假使索引中key_part2不为一千0的元组越来越多,最终M奥迪Q7福睿斯的功能越好。优化器会将查询条件拆分为(一千,1000),(1001,一千),... (壹玖玖柒,一千)最终会依赖那个规范举行过滤

    连锁参数

    当mrr=on,mrr_cost_based=on,则意味cost base的格局还采纳启用MENVISION福睿斯优化,当开采优化后的代价过高时就能够不选取该项优化

    当mrr=on,mrr_cost_based=off,则意味总是敞开M传祺LAND优化

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

    参数read_rnd_buffer_size 用来调控键值缓冲区的深浅。二级索引围观到文件的结尾大概缓冲区已满,则使用高效排序对缓冲区中的内容依据主键举办排序

    连带参数

    当mrr=on,mrr_cost_based=on,则表示cost base的点子还甄选启用M讴歌MDXKoleos优化,当发现优化后的代价过高时就能够不选拔该项优化

    当mrr=on,mrr_cost_based=off,则意味总是敞开M奥迪Q7奥迪Q5优化

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

    参数read_rnd_buffer_size 用来支配键值缓冲区的尺寸。二级索引围观到文件的结尾只怕缓冲区已满,则应用便捷排序对缓冲区中的内容遵照主键进行排序

    唯独当当中表对所联网的列含有索引时,Simple Nested-Loops Join算法能够使用索引的特征来进展高效配合,此时的算法调度为如下:

    连锁参数

    当mrr=on,mrr_cost_based=on,则象征cost base的办法还选取启用M锐界Haval优化,当开掘优化后的代价过高时就会不使用该项优化

    当mrr=on,mrr_cost_based=off,则表示总是敞开M本田CR-V酷威优化

    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的表,听上去和MTiggo讴歌ZDX类似,实际上MSportage奥迪Q5也得以设想成二级索引和 primary key的join

    倘诺被Join的表上没有索引,则使用老版本的BNL战术(BLOCK Nested-loop)

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

    Batched Key Access (BKA)  进步表join质量的算法。当被join的表能够运用索引时,就先排好顺序,然后再去搜求被join的表,听上去和M昂科拉Evoque类似,实际上MENCORE陆风X8也得以设想成二级索引和 primary key的join

    要是被Join的表上没有索引,则动用老版本的BNL计谋(BLOCK Nested-loop)

    For each row r in R do
        lookup r in S index
            If find s == r
               Then output the tuple <r, s>
    

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

    Batched Key Access (BKA)  进步表join品质的算法。当被join的表能够运用索引时,就先排好顺序,然后再去追寻被join的表,听上去和M昂科雷福睿斯类似,实际上M传祺奥迪Q5也得以设想成二级索引和 primary key的join

    万一被Join的表上未有索引,则运用老版本的BNL计策(BLOCK Nested-loop)

    BKA原理

    对于多表join语句,当MySQL使用索引访谈第贰个join表的时候,使用叁个join buffer来采摘第多少个操作对象生成的相干列值。BKA营造好key后,批量传给引擎层做索引查找。key是经过MEvoque大切诺基接口提交给引擎的(mrr指标是比较顺序)MPAJEROPRADO使得查询更有效用。 

    大致的经过如下:

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

    • 接下来BKA算法营造key来访谈被连接的表,并批量利用MWrangler奥迪Q7接口提交keys到数据仓库储存款和储蓄引擎去探寻查找。

    • 交给keys之后,MPRADO福特Explorer使用最棒的措施来获取行并陈说给BKA

    BNL和BKA都以批量的交给一部分行给被join的表,进而减弱访谈的次数,那么它们有啥样区别吗?

    • BNL比BKA出现的早,BKA直到5.6才面世,而NBL至少在5.1里头就存在。

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

    • BKA首要是指在被join表上有索引能够接纳,那么就在行提交给被join的表以前,对那些行遵照索引字段进展排序,因而削减了自由IO,排序那才是两个最大的分别,但是只要被join的表没用索引呢?那就应用NBL

    BKA原理

    对于多表join语句,当MySQL使用索引访谈首个join表的时候,使用三个join buffer来搜集第二个操作对象生成的有关列值。BKA营造好key后,批量传给引擎层做索引查找。key是由此M福特Explorer福特Explorer接口提交给引擎的(mrr指标是相比顺序)MMuranoCRUISER使得查询更有成效。 

    大致的历程如下:

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

    • 接下来BKA算法构建key来访问被一而再的表,并批量采取M昂科雷凯雷德接口提交keys到数据仓库储存款和储蓄引擎去找出查找。

    • 提交keys之后,M奥迪Q5大切诺基使用最棒的不二等秘书技来获取行并反映给BKA

    BNL和BKA都以批量的付出一部分行给被join的表,进而收缩访谈的次数,那么它们有何样不相同呢?

    • BNL比BKA出现的早,BKA直到5.6才出现,而NBL至少在5.1之中就存在。

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

    • BKA首即使指在被join表上有索引能够运用,那么就在行提交给被join的表在此以前,对那几个行遵照索引字段张开排序,由此收缩了随机IO,排序那才是相互最大的区分,但是假设被join的表没用索引呢?那就使用NBL

    本文由1010cc时时彩经典版发布于1010cc安卓版,转载请注明出处:1010cc时时彩经典版MySql联接算法,查询优化之

    关键词: