从顺序随机I/O原理来讨论MYSQL MRR NLJ BNL BKA

  • 时间:
  • 浏览:7

+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+

begin cpy use sequential read buffer is 4k:

+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+

+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+

2、A Block Nested-Loop (BNL)

testzh进行一次扫描,刚刚对扫描到的值和join_buffer_size中的数据根据条件testzh.name=testzh10.name

    读取四个多大文件守护进程池池中限制为900M,而守护进程池池顺序和随机读取8000个4096大小的数据,刚刚CPY到所以文件中,

----ref and eq_ref access:当使用BKA(Batched Key Access),使用到连接索引,键值唯一(非主键)刚刚不唯一起且使用组合索引前缀

    每四个多盘片为双面,每四个多面上分布有同心圆的磁道,磁道又分为扇区一般为512 BYTES,现代的磁盘

你这名 最好的法律方式的原理上刚刚通过顺序的连接条件的比对,减少随机读取的刚刚。能不能参考

2 rows in set, 1 warning (0.00 sec)

    刚刚朋友计ts(seek time)为寻道时间,tl(latency time)为寻道完成后听候盘片旋转到特定扇区的时间

---ALL 全表扫描

mysql> explain select * from testzh ,testzh10 where  testzh.id2=testzh10.id2;

fisrt sca array: 46515

    O_CREAT | O_WRONLY |O_EXCL 打开写文件,启用OS BUFFER,write操作写到OS kernel buffer则现在现在结束了了,一起能不能了开启

2 rows in set, 1 warning (0.00 sec)

fisrt sca array: 46134

朋友考虑四个多表连接的最好的法律方式,如下:

2 rows in set, 1 warning (0.00 sec)

4、劣势:

iostat vmstat 都能看完读取的强度非常慢。下面给出比较:

从顺序随机I/O原理来讨论MYSQL MRR NLJ BNL BKA

MRR排序后在比对呢?刚刚testzh 压根没用到索引,你join_buffer_size中的数据排序了,刚刚testzh表中关

| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref             | rows   | filtered | Extra                                  |

这里明显看出了问题,

现在现在结束了了,朋友考虑原本的优化最好的法律方式相对于BNL实在减少读取testzh表的次数,刚刚是大大减少,那为啥不使用

3、hash join:只使用于CBO,刚刚是HASH算法也就能不能了用于等值连接,他适用于小表和大表做连接,刚刚nest loop 效果不好的清况 下,

mysql> explain select * from testzh force index(id2),testzh10 where  testzh.id2=testzh10.id2;

   如图(图片摘取自mariadb官方文档):

         if row satisfies join conditions,

--顺序

2 rows in set, 1 warning (0.00 sec)

四、NLJ、BNL、BKA   

    这方面摘取一张图便于理解,实际上我O_DIRECT后读取你这名 文件是不过内核高速缓存的。

          MySQL 5.7 Reference Manual

+----+-------------+----------+------------+------+---------------+------+---------+-----------------+--------+----------+----------------------------------------+

sequential cpy begin Time: Fri Dec  2 01:36:55 2016

mysql> explain select * from testzh force index(id2),testzh10 where  testzh.name=testzh10.name;

+----+-------------+----------+------------+------+---------------+------+---------+-----------------+--------+----------+----------------------------------------+

后又读取一次testzh和join_buffer_size中的数据根据条件testzh.name=testzh10.name比对,如此 反复直到

+----+-------------+----------+------------+------+---------------+------+---------+-------------------+-------+----------+-------------+

fisrt sca array: 142115

点击(此处)折叠或打开

五、ORACLE中连接最好的法律方式简述

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util

                比如

3、Batched Key Access Joins (BKA)

1、描述

参考资料:UNIX系统编程手册

    旋转。

      范围扫描range access中MYSQL将扫描到的数据存入read_rnd_buffer_size,刚刚对其按照primary key(rowid)排序,

......    

+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

   刚刚使用排序好的数据进行顺序回表刚刚朋友知道INNODB中是叶节点数据是按照PRIMARY KEY(ROWID)进行排列的,如此

+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

scattered cpy begin Time: Fri Dec  2 01:36:58 2016

per 75 % ,Time:Fri Dec  2 01:39:29 2016

heap的形式存放,每一行刚刚分布在段上任何四个多块上,所以刚刚通过索引来查找数据行的完后 ,都有可是不是一个多索引块

CBO就能不能根据你这名 参数来判断使用你这名 索引产生的cost是几次。

+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+

   for each row in t2 matching reference key {

per 80 % ,Time:Fri Dec  2 01:38:40 2016

   源码接口handler::multi_range_read_init handler::multi_range_read_next

    原本更能说明问题,但这刚刚重要刚刚随机读刚刚慢得离谱了。下面是我守护进程池池跑出的结果。

Query OK, 0 rows affected (0.00 sec)

|  1 | SIMPLE      | testzh   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 80031 |    10.00 | Using where |

    cpy的文件为8198000字节

1 row in set, 1 warning (0.00 sec)

具体参考

刚刚了解ORACLE的朋友一定不用忘记索引集群因子,下面是集群因子的描述在索引的分析数据上clustering_factor是一

                 也直接影响了HASH JOIN的性能,比如自增序列刚刚很好的HASH连接字段。大表的hash会占用太满的临时表空间是前要注意

Query OK, 0 rows affected (0.00 sec)

    为了将写操作的影响降低,而将读操作的影响放大,分别使用

    一般外边缘磁道的扇区多,内磁道的扇区少,如此 一般读写外边缘磁道的强度很快了 了 ,刚刚转速为定值。

个怪怪的要的参数,表示的是索引和表之间的关系,刚刚,索引是按照一定的顺序排列的,刚刚,对于表来说是按照四种

per 25 % ,Time:Fri Dec  2 01:36:56 2016

   原本就转换随机读取为顺序读取了。

          Block-Based Join Algorithms(mariadb)

总结:一般用于被链接表有索引的最好的法律方式ref刚刚eq_ref,刚刚BKA代价较高,被连接表如此 索引一般使用BNL。

    而顺序I/O则不然一次定位能不能读取更多的扇区,从而尽量减少读取时间。

+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

    血块的随机I/O会造成频繁的磁道更换原因分析分析分析过长的时间,很刚刚读完几次扇区马上就要跳到另外的磁道,

sda               0.00     0.00 4979.38    2.06 19967.01    32.99     8.03     0.76    0.15   0.14  70.21

per 80 % ,Time:Fri Dec  2 01:36:57 2016

| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |

你这名 最好的法律方式在MRR中刚刚讲了所以,其目的在于优化ref刚刚eq_ref使用NLJ连接的最好的法律方式

https://mariadb.com/kb/en/mariadb/block-based-join-algorithms/

mysql> explain select * from testzh ,testzh10 where  testzh.name=testzh10.name;

有了顶端的基础,朋友明白了顺序读取的重要性,如此 朋友现在现在结束了了看看Multi-Range Read (MRR),刚刚你这名 底部形态也是BKA的

三、MRR

本文只讨论innodb存储引擎,刚刚有次要观点为作者观点,刚刚有误请指出。

1、A simple nested-loop join (NLJ) 

          send to client

    T(I/0) = ts+tl+tw

sda               0.00     0.00  104.12    2.06   465.98    32.99     9.40     1.17   11.02   9.68 102.78

刚刚输出顺序读取写入,刚刚进行随机读取写入。能不能看完差别非常大,实在使用

刚刚实在不错 您能不能考虑请作者喝杯茶(微信支付):                                作者微信号:                   

per 25 % ,Time:Fri Dec  2 01:37:51 2016

   排序是额外前要时间的。刚刚使用order limit n,会原因分析分析分析很快了 了

重要支柱 

mysql> set optimizer_switch='mrr_cost_based=off';

                 的。

---ref 索引扫描

|  1 | SIMPLE      | testzh | NULL       | range | id2           | id2  | 10      | NULL |    1 |   80.00 | Using index condition; Using MRR |

                  合并,获得更好的NEST LOOP 性能。

总结:一般用于被连接表最好的法律方式为ALL刚刚index(索引覆盖扫描)

| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref             | rows   | filtered | Extra                                  |

    O_RDONLY | O_DIRECT 打开读取文件,用O_DIRECT打开目的在于禁用OS CACHE当然也禁用了OS的预读,直接读取文件

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util

sda               0.00     9.09 7114.14    9.09 28456.57    96.97     8.02     1.04    0.15   0.13  95.86

                  对小表根据连接字段使用HASH算法,刚刚刚刚使用hash算法如此 小表连接字段的不同值决定了HASH算法散列的分布均匀性,

Batch Key Access Join

| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |

    当然你这名 守护进程池池有所以补足,刚刚使用排序算法将随机数组中的数据排序后在进行读取,而都有取四个多连续的数组。

|  1 | SIMPLE      | testzh   | NULL       | ALL  | id2           | NULL | NULL    | NULL            | 80031 |   80.00 | Using where                            |

+----+-------------+----------+------------+------+---------------+------+---------+-----------------+--------+----------+----------------------------------------+

---随机

   你这名 最好的法律方式采用逐层调用循环的最好的法律方式,很显然你这名 最好的法律方式适用于任何场景,不过在被连接表如此 索引的清况 下,如此 强度极低。

  使用MYSQL文档中伪代码的描述:

    模拟使用C语言调用LINUX API完成,主要最好的法律方式如下:

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util

很显然你这名 最好的法律方式一般是用来优化被连接表如此 索引,这被连接表最好的法律方式为ALL刚刚index,刚刚你这名 join使用NLJ实在太慢了,前要优化他对内层表

   顺序读取没了前要磁盘动臂不断的移动来寻道和听候带盘片旋转的时间。

|  1 | SIMPLE      | testzh   | NULL       | ref  | id2           | id2  | 5       | test.testzh10.id2 |     1 |   80.00 | NULL        |

Query OK, 0 rows affected (0.00 sec)                 

fisrt sca array: 134709

3、优势:

      对于MYSQL的二级索引也处于如同ORACLE clustering_factor一样的问题,过于随机的回表会造成随即读取过于严重

当join_buffer_size满后,对

      而在BKA中则将被连接表使用到ref,eq_ref索引扫描最好的法律方式的完后 将第四个多表中扫描到的键值倒进join_buffer_size中,

for each row in t1 matching range {

扫描testzh10表的相关字段,大慨testzh10.name含高其中,存入join_buffer_size,

    下图是四个多典型的磁盘组织(摘取数据底部形态(C语言版))

|  1 | SIMPLE      | testzh   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 80031 |    10.00 | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+----------+------------+------+---------------+------+---------+-----------------+--------+----------+----------------------------------------+

使用了ICP和MRR,刚刚MRR的代价评估一般较高所以这里使用mrr_cost_based=off

fisrt sca array: 198155

sda               0.00     0.00  104.17    1.04   416.67     0.52     7.93     1.04    9.79   9.81 103.23

      for each row in t3 {

| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                            |

   刚刚调用MRR接口进行排序和顺序访问刚刚通过join条件得到数据,原本连接条件之间也成为了顺序比对。

    显然在读取数据一定的清况 下,ts和tl的时间成了决定因素,而事实上ts寻道时间相对所以而言占用更长,

per 80 % ,Time:Fri Dec  2 01:40:20 2016

                               O_SYNC,现在现在结束了了O_SYNC每一次wirte会调用fsync(),将写的影响刚刚放大。

mysql> explain select * from testzh,testzh10 where  testzh.id2=testzh10.id2 ;

2、适用范围:

  }

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util

于testzh10.name的数据任然是无序的,如此 任何意义,使用MRR你这名 原理类事归并排序,前要四个多数据集都有

    机械盘由动臂,盘片,读写磁头,主轴组成,磁头是固定能不能了动的,要读取相应的扇区能不能了通过盘片的

fisrt sca array: 91580

2、SORT MERGE JOIN:你这名 连接最好的法律方式类使用归并的最好的法律方式,既然是归并一般用于连接条件之间排序好了,及都有索引的清况 。

    tw(transmission time)为传输时间,如此 读取四个多扇区的时间为:

|  1 | SIMPLE      | testzh10 | NULL       | ref  | id2           | id2  | 5       | test.testzh.id2 |      1 |   80.00 | Using join buffer (Batched Key Access) |

1、NEST LOOP JOIN:驱动表结果集较少,刚刚被驱动表有索引的清况 下,11G后加入VECTOR I/O,其原理感觉类事MYSQL BKA,将多个单块读取

fisrt sca array: 137262

+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+

          数据底部形态(C语言版本)

+----+-------------+----------+------------+------+---------------+------+---------+-----------------+--------+----------+----------------------------------------+

如图(图片摘取自mariadb官方文档):

进行比对,刚刚符合则放回,不符合则遗弃,比对完成后,清空buffer,继续扫描testzh10剩下的数据,满了

驱动的次数,如此 加入四个多缓存叫做join_buffer_size

    一起各个不同盘片上半径下同的磁道组成了四个多柱面。

sda               0.00     0.00 7204.12    0.00 28816.49     0.00     8.00     0.98    0.14   0.14  98.04

    }

(http://blog.itpub.net/7728585/viewspace-612691/)

| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref               | rows  | filtered | Extra       |

|  1 | SIMPLE      | testzh10 | NULL       | ref  | id2           | id2  | 5       | test.testzh.id2 |      1 |   80.00 | Using join buffer (Batched Key Access) |

|  1 | SIMPLE      | testzh10 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  99900 |   80.00 | NULL        |

+----+-------------+----------+------------+------+---------------+------+---------+-------------------+-------+----------+-------------+

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util

对应多个,甚至完整版表的块,所以引入了clustering_factor你这名 参数来表示表上数据存放和索引之间的对应关系。原本

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util

}

先输出次要数组中的随机值,能不能看完读取的位置是随机的。从而模拟随机读取,

sda               0.00     0.00  107.14    0.00   428.57     0.00     8.00     1.01    9.49   9.42 80.92

二、随机I/O和顺序I/O模拟

2 rows in set, 1 warning (0.00 sec)

./a.out p10404580_112080_Linux-x86-64_1of7.zip

|  1 | SIMPLE      | testzh10 | NULL       | ALL  | id2           | NULL | NULL    | NULL              | 99900 |   80.00 | Using where |

排序好的,所以这里用能不能了MRR。

mysql> explain select * from testzh force index(id2) where id2=80 and id3<80;

|  1 | SIMPLE      | testzh10 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  99900 |   80.00 | NULL                                               |

+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+

    寻道时间在10毫秒的数量级,780转的tl时间为800/780 约为 80微秒数量级,而传输时间更短。

|  1 | SIMPLE      | testzh   | NULL       | ALL  | id2,id2_2     | NULL | NULL    | NULL            | 80031 |   80.00 | Using where                            |

比如:

          Multi Range Read Optimization(mariadb)

mysql> set optimizer_switch ='block_nested_loop=off';

+----+-------------+----------+------------+------+---------------+------+---------+-------------------+-------+----------+-------------+

+----+-------------+----------+------------+------+---------------+------+---------+-----------------+--------+----------+----------------------------------------+

per 80 % ,Time:Fri Dec  2 01:36:58 2016

fisrt sca array: 10208

如图(图片摘取自mariadb官方文档):

mysql> set optimizer_switch='mrr_cost_based=off,batched_key_access=on';

begin cpy use scattered read read buffer is 4k:

   顺序读取有预读的优势。

一、机械磁盘原理

per 75 % ,Time:Fri Dec  2 01:36:57 2016

随机顺序写代码

---range access:通过四个多刚刚多个范围限制进行读取数据。

   每个块值前要读取一次,而不前要多次读取,这是理所当然,刚刚四个多块中一般存储了多行数据,不使用MRR以还会原因分析分析分析同一块多次读取到。

fisrt sca array: 25805