|
|
51CTO旗下网站
|
|
移动端

MySQL 的 MRR 到底是什么?

MRR,全称「Multi-Range Read Optimization」。 简单说:MRR 通过把「随机磁盘读」,转化为「顺序磁盘读」,从而提高了索引查询的性能。

作者:靠发型吃饭的柳树来源:柳树的絮叨叨|2020-03-05 10:28

MRR,全称「Multi-Range Read Optimization」。

简单说:MRR 通过把「随机磁盘读」,转化为「顺序磁盘读」,从而提高了索引查询的性能。

至于:

  • 为什么要把随机读转化为顺序读?
  • 怎么转化的?
  • 为什么顺序读就能提升读取性能?

咱们开始吧。

磁盘:苦逼的底层劳动人民

执行一个范围查询:

  1. mysql > explain select * from stu where age between 10 and 20; 
  2. +----+-------------+-------+-------+------+---------+------+------+-----------------------+ 
  3. | id | select_type | table | type  | key  | key_len | ref  | rows | Extra                 | 
  4. +----+-------------+-------+-------+----------------+------+------+-----------------------+ 
  5. |  1 | SIMPLE      |  stu  | range | age  | 5       | NULL |  960 | Using index condition | 
  6. +----+-------------+-------+-------+----------------+------+------+------------- 

当这个 sql 被执行时,MySQL 会按照下图的方式,去磁盘读取数据(假设数据不在数据缓冲池里):

图中红色线就是整个的查询过程,蓝色线则是磁盘的运动路线。

这张图是按照 Myisam 的索引结构画的,不过对于 Innodb 也同样适用。

对于 Myisam,左边就是字段 age 的二级索引,右边是存储完整行数据的地方。

先到左边的二级索引找,找到第一条符合条件的记录(实际上每个节点是一个页,一个页可以有很多条记录,这里我们假设每个页只有一条),接着到右边去读取这条数据的完整记录。

读取完后,回到左边,继续找下一条符合条件的记录,找到后,再到右边读取,这时发现这条数据跟上一条数据,在物理存储位置上,离的贼远!

咋办,没办法,只能让磁盘和磁头一起做机械运动,去给你读取这条数据。

第三条、第四条,都是一样,每次读取数据,磁盘和磁头都得跑好远一段路。

磁盘的简化结构可以看成这样:

可以想象一下,为了执行你这条 sql 语句,磁盘要不停的旋转,磁头要不停的移动,这些机械运动,都是很费时的。

10,000 RPM(Revolutions Per Minute,即转每分) 的机械硬盘,每秒大概可以执行 167 次磁盘读取,所以在极端情况下,MySQL 每秒只能给你返回 167 条数据,这还不算上 CPU 排队时间。

上面讲的都是机械硬盘,SSD 的土豪,请随意 - -

对于 Innodb,也是一样的。 Innodb 是聚簇索引(cluster index),所以只需要把右边也换成一颗叶子节点带有完整数据的 B+ tree 就可以了。

顺序读:一场狂风暴雨般的革命

到这里你知道了磁盘随机访问是多么奢侈的事了,所以,很明显,要把随机访问转化成顺序访问:

  1. mysql > set optimizer_switch='mrr=on'
  2. Query OK, 0 rows affected (0.06 sec) 
  3.  
  4. mysql > explain select * from stu where age between 10 and 20; 
  5. +----+-------------+-------+-------+------+---------+------+------+----------------+ 
  6. | id | select_type | table | type  | key  | key_len | ref  | rows | Extra          | 
  7. +----+-------------+-------+-------+------+---------+------+------+----------------+ 
  8. |  1 | SIMPLE      | tbl   | range | age  |    5    | NULL |  960 | ...; Using MRR | 
  9. +----+-------------+-------+-------+------+---------+------+------+----------------+ 

我们开启了 MRR,重新执行 sql 语句,发现 Extra 里多了一个「Using MRR」。

这下 MySQL 的查询过程会变成这样:

对于 Myisam,在去磁盘获取完整数据之前,会先按照 rowid 排好序,再去顺序的读取磁盘。

对于 Innodb,则会按照聚簇索引键值排好序,再顺序的读取聚簇索引。

顺序读带来了几个好处:

1、磁盘和磁头不再需要来回做机械运动;

2、可以充分利用磁盘预读

比如在客户端请求一页的数据时,可以把后面几页的数据也一起返回,放到数据缓冲池中,这样如果下次刚好需要下一页的数据,就不再需要到磁盘读取。这样做的理论依据是计算机科学中著名的局部性原理:

当一个数据被用到时,其附近的数据也通常会马上被使用。

3、在一次查询中,每一页的数据只会从磁盘读取一次

MySQL 从磁盘读取页的数据后,会把数据放到数据缓冲池,下次如果还用到这个页,就不需要去磁盘读取,直接从内存读。

但是如果不排序,可能你在读取了第 1 页的数据后,会去读取第2、3、4页数据,接着你又要去读取第 1 页的数据,这时你发现第 1 页的数据,已经从缓存中被剔除了,于是又得再去磁盘读取第 1 页的数据。

而转化为顺序读后,你会连续的使用第 1 页的数据,这时候按照 MySQL 的缓存剔除机制,这一页的缓存是不会失效的,直到你利用完这一页的数据,由于是顺序读,在这次查询的余下过程中,你确信不会再用到这一页的数据,可以和这一页数据说告辞了。

顺序读就是通过这三个方面,最大的优化了索引的读取。

别忘了,索引本身就是为了减少磁盘 IO,加快查询,而 MRR,则是把索引减少磁盘 IO 的作用,进一步放大。

一些关于这场革命的配置

和 MRR 相关的配置有两个:

  • mrr: on/off
  • mrr_cost_based: on/off

第一个就是上面演示时用到的,用来打开 MRR 的开关:

  1. mysql > set optimizer_switch='mrr=on'

如果你不打开,是一定不会用到 MRR 的。

另一个,则是用来告诉优化器,要不要基于使用 MRR 的成本,考虑使用 MRR 是否值得(cost-based choice),来决定具体的 sql 语句里要不要使用 MRR。

很明显,对于只返回一行数据的查询,是没有必要 MRR 的,而如果你把 mrr_cost_based 设为 off,那优化器就会通通使用 MRR,这在有些情况下是很 stupid 的,所以建议这个配置还是设为 on,毕竟优化器在绝大多数情况下都是正确的。

另外还有一个配置 read_rnd_buffer_size ,是用来设置用于给 rowid 排序的内存的大小。

显然,MRR 在本质上是一种用空间换时间的算法。MySQL 不可能给你无限的内存来进行排序,如果 read_rnd_buffer 满了,就会先把满了的 rowid 排好序去磁盘读取,接着清空,然后再往里面继续放 rowid,直到 read_rnd_buffer 又达到 read_rnd_buffe 配置的上限,如此循环。

尾声

你也看出来了,MRR 跟索引有很大的关系。

索引是 MySQL 对查询做的一个优化,把原本杂乱无章的数据,用有序的结构组织起来,让全表扫描变成有章可循的查询。

而我们讲的 MRR,则是 MySQL 对基于索引的查询做的一个的优化,可以说是对优化的优化了。

要优化 MySQL 的查询,就得先知道 MySQL 的查询过程;而要优化索引的查询,则要知道 MySQL 索引的原理。

就像之前在「如何学习 MySQL」里说的,要优化一项技术、学会调优,首先得先弄懂它的原理,这两者是不同的 Level。

以上(此处应该有点赞)。

【编辑推荐】

  1. 关于MySQL索引面试题的6连炮!招架的住吗?
  2. 万字分享,我是如何一步一步监控公司MySQL的?
  3. 基于 MySQL Binlog 实现可配置的异构数据同步
  4. 为什么PyMySQL获取一条数据会让内存爆炸
  5. Linux运维:高可用MySQL解决方案概述
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

VMware NSX 入门到实战

VMware NSX 入门到实战

网络虚拟化革命性技术
共16章 | Cloud袁

135人订阅学习

消息队列Kafka运维实践攻略

消息队列Kafka运维实践攻略

入门级消息队列
共3章 | 独行侠梦

107人订阅学习

编程零基础,Scratch 开发小游戏

编程零基础,Scratch 开发小游戏

游戏化编程
共5章 | googlingman

42人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微