MRR 优化效果测试

最新推荐文章于 2024-03-22 09:30:00 发布
最新推荐文章于 2024-03-22 09:30:00 发布
阅读量695 收藏
点赞数
分类专栏： Mysql 文章标签： mysql mysql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/promiseonly/article/details/121261068
版权

文章目录

MRR 优化效果测试

Multi-Range Read Optimization (MRR) 多范围读优化。

不开启，Mysql 以二级索引的顺序通过主键ID回表访问聚集索引获取数据，该情况下，对于主键的访问具有随机性，检索数据较多时产生大量随机IO，性能较差。

开启后，将通过二级索引获取到的主键ID在内存中排序，以排序后的主键ID顺序回表访问聚集索引，极大的降低了随机IO的次数。

  • 该优化用于对机械硬盘随机IO的优化，因固态硬盘随机IO效率很高，所以固态场景下效果不明显。

  • 该优化对于缓存读基本无效，主要用于硬盘读场景

0.缓存清理

my.cnf 设置如下参数，并重启数据库

innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_load_at_startup=OFF

1. 案例

drop table t1;
create table t1(id int auto_increment primary key,name varchar(20),uid int,addr varchar(20));
drop procedure prc_t1;
delimiter //
create procedure prc_t1()
begin
declare i int default 0;
declare j int default 0;
while i<400 do
    while j < 10000 do
    INSERT into t1(name,uid,addr) VALUES('cym',floor(rand()*1000),'testtesttesttesttest');
    set j=j+1;
    end while;
    commit;
    set j=0;
    set i=i+1;
end while;
end
//
delimiter ;

set sql_log_bin=0;
set autocommit=0;
call prc_t1;
alter table t1 add key(uid);

2. 检查

[pro]root@cym 12:43:11>desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| uid   | int         | YES  | MUL | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

[pro]root@cym 12:43:56>show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1    |          0 | PRIMARY  |            1 | id          | A         |     3662208 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t1    |          1 | uid      |            1 | uid         | A         |        1001 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

[pro]root@cym 10:45:49>select count(1) from t1 where uid between 1 and 50;
+----------+
| count(1) |
+----------+
|   199475 |
+----------+

3. 测试

清空事件信息并重启数据库

3.1 optimizer_switch=‘mrr=off’
-- 重启数据库
systemctl restart mysqld3306

-- 检查是否有缓存
select count(1) from information_schema.innodb_buffer_page_lru WHERE table_name like '`cym`.`t1`';
+----------+
| count(1) |
+----------+
|        0 |
+----------+

-- 收集统计信息
analyze table t1;
-- 调整优化规则
set @@optimizer_switch='mrr=off';
-- 查看执行计划
desc select * from t1 where uid between 1 and 50;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | uid           | NULL | NULL    | NULL | 3986466 |    10.05 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
-- 执行查询
select * from t1 where uid between 1 and 50;
199475 rows in set (1.22 sec)
-- 再次查看执行计划
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | uid           | uid  | 5       | NULL | 400514 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
3.2 optimizer_switch=‘mrr=on,mrr_cost_based=on’
-- 重启数据库
systemctl restart mysqld3306
-- 检查是否有缓存
select count(1) from information_schema.innodb_buffer_page_lru WHERE table_name like '`cym`.`t1`';
+----------+
| count(1) |
+----------+
|        0 |
+----------+

-- 收集统计信息
analyze table t1;
-- 调整优化规则
set @@optimizer_switch='mrr=off';
-- 查看执行计划
desc select * from t1 where uid between 1 and 50;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | uid           | NULL | NULL    | NULL | 3986466 |    10.05 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
-- 执行查询
select * from t1 where uid between 1 and 50;
199475 rows in set (1.23 sec)
-- 再次查看执行计划
desc select * from t1 where uid between 1 and 50;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | uid           | uid  | 5       | NULL | 400514 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
3.3 optimizer_switch=‘mrr=on,mrr_cost_based=off’
-- 重启数据库
systemctl restart mysqld3306
-- 检查是否有缓存
select count(1) from information_schema.innodb_buffer_page_lru WHERE table_name like '`cym`.`t1`';
+----------+
| count(1) |
+----------+
|        0 |
+----------+

-- 收集统计信息
analyze table t1;
-- 调整优化规则
set @@optimizer_switch='mrr=off';
-- 查看执行计划
desc select * from t1 where uid between 1 and 50;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | uid           | uid  | 5       | NULL | 400514 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
-- 执行查询
select * from t1 where uid between 1 and 50;
199475 rows in set (0.87 sec)
-- 再次查看执行计划
desc select * from t1 where uid between 1 and 50;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | uid           | uid  | 5       | NULL | 400514 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+----------------------------------+

4. 查看执行事件ID

performance_schema 记录mysql启动后的信息，数据库重启即消失。

启用performance_schema的信息收集，参考<<Performance Scheme 分析SQL慢在哪里>>

[pro]root@cym 12:51:18>SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%uid between 1 and 50%';
+----------+----------+--------------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT                                         |
+----------+----------+--------------------------------------------------+
|       55 |   0.0007 | desc select * from t1 where uid between 1 and 50 |
|       74 |   0.8734 | select * from t1 where uid between 1 and 50      |
+----------+----------+--------------------------------------------------+

5. 查看执行分析

SELECT NESTING_EVENT_ID,event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=74;
+------------------+------------------------------------------------+----------+
| NESTING_EVENT_ID | Stage                                          | Duration |
+------------------+------------------------------------------------+----------+
|               74 | stage/sql/starting                             |   0.0000 |
|               74 | stage/sql/Executing hook on transaction begin. |   0.0000 |
|               74 | stage/sql/starting                             |   0.0000 |
|               74 | stage/sql/checking permissions                 |   0.0000 |
|               74 | stage/sql/Opening tables                       |   0.0000 |
|               74 | stage/sql/init                                 |   0.0000 |
|               74 | stage/sql/System lock                          |   0.0000 |
|               74 | stage/sql/optimizing                           |   0.0000 |
|               74 | stage/sql/statistics                           |   0.0000 |
|               74 | stage/sql/preparing                            |   0.0000 |
|               74 | stage/sql/executing                            |   0.8731 |
|               74 | stage/sql/end                                  |   0.0000 |
|               74 | stage/sql/query end                            |   0.0000 |
|               74 | stage/sql/waiting for handler commit           |   0.0000 |
|               74 | stage/sql/closing tables                       |   0.0000 |
|               74 | stage/sql/freeing items                        |   0.0000 |
|               74 | stage/sql/cleaning up                          |   0.0000 |
+------------------+------------------------------------------------+----------+

6. 总结

三种优化参数配置，对应的执行效率如下

optimizer_switch='mrr=off'                          199475 rows in set (1.22 sec)       Extra:Using where
optimizer_switch='mrr=on,mrr_cost_based=on'         199475 rows in set (1.23 sec)       Extra:Using where 
optimizer_switch='mrr=on,mrr_cost_based=off'        199475 rows in set (0.87 sec)       Extra:Using index condition; Using MRR

第三种效率最高，而且执行计划准确，故应配置 optimizer_switch=‘mrr=on,mrr_cost_based=off’

晕倒的蚂蚁
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL InnoDB MRR优化指南
12-16
前言 MRR 是 Multi-Range Read 的简写，目的是减少磁盘随机访问，将随机访问转化为较为顺序的访问。适用于 range/ref/eq_ref 类型的查询。 实现原理： 1、在二级索引查找后，根据得到的主键到聚簇索引找出需要的数据。 2、二级索引查找得到的主键的顺序是不确定的，因为二级索引的顺序与聚簇索引的顺序不一定一致； 3、如果没有 MRR，那么在聚簇索引查找时就可能出现乱序读取数据页，这对于机械硬盘是及其不友好的。 4、MRR 的优化方式： 将查找到的二级索引键值放在一个缓存中； 将缓存中的键值按照 主键 进行排序； 根据排序后的主键去聚簇索引访问实际的数
优化器 MRR & BKA
qiuyepiaoling的专栏
02-03 538
原文：http://mysql.taobao.org/monthly/2016/01/04/ 上一篇文章咱们对 ICP 进行了一次全面的分析，本篇文章小编继续为大家分析优化器的另外两个选项: MRR & batched_key_access(BKA) ，分析一下他们的作用、原理、相互关系、源码实现以及使用范围。 什么是 MRR MRR 的全称是 Multi-Range Read Op
MySQL--什么是MRR？
最新发布
weixin_42118323的博客
03-22 765
MRR，全称 Multi-Range Read Optimization，直译就是多范围读取优化，可以知道它是对范围读取的优化，MRR 是对索引读取的优化，提高索引的查询性能，原理是充分利用磁盘预读机制，把磁盘随机读取，转换为磁盘顺序读取，查询是否使用 MRR 由MySQL 优化器决定，一般不要强行干预。我们知道MRR 是对索引读取的优化，实际上 MRR 是通过对缓冲区的索引记录排序，把随机读取转换为顺序读取。注意：磁盘旋转速度快，耗时少，寻道速度慢，耗时久。如有不正确的地方请各位指出纠正。
Using MRR(Multi-Range Read )
weixin_34408717的博客
08-23 1057
2019独角兽企业重金招聘Python工程师标准>>> ...
Innodb MRR优化
wxq1075110242的博客
04-16 546
Mysql5.6版本开始支持MRR（Multi-Range Read）优化，其目的是为了减少磁盘的随机访问，并将随机访问转化为较为顺序的数据访问。这对于IO-bound类型的Sql查询语句可带来性能的极大提升。MRR优化使用与range,ref,eq_ref类型的查询。 MRR优化的好处： 使数据访问变得较为顺序，其原理是在查询辅助索引的时候，首先根据得到的结果按照主键排序，并按主键排序的的顺序...
MySQL8中文手册-MRR优化
michaelrainy的博客
03-26 399
网上充斥着各种MySQL的学习资料，有所谓的小白学习笔记也有大牛的分享文章，有免费的也有收费的，文章的质量也是鱼龙混杂，经常看到文章与文章之间相互矛盾，甚至文章前后矛盾。 至于为什么要翻译官方文档，还要源于上次学习极客时间的付费文章，其中一篇对change buffer的说明，文章中描述change buffer是用于更新数据缓存，后面又对change buffer和redo log的关系进行了说明，然而评论中有各种观点，有些说change buffer只针对索引数据进行缓存，文章前后翻了好几遍，始终有个.
爱立信MRR分析
04-02
一个可以分析MRR数据的软件，通过mrr测试数据，进行数据分析。
MRR_eccv12.zip_MRR
07-14
MRR。非对准人脸识别。
mrr2c：将Metek MRR-2微型雨雷达数据文件转换为HDF
02-14
mrr2c 将Metek MRR-2微型雨雷达数据文件转换为NetCDF。 mrr2c是一个开源程序，可将Metek Micro Rain Radar 2（MRR-2）数据转换为NetCDF。 支持RAW，PRO和AVE文件。 注意：早期版本生成HDF5文件。 如果需要这种类型的...
mysql using mrr_MySQL InnoDB MRR优化指南
weixin_34195649的博客
01-19 154
前言MRR 是 Multi-Range Read 的简写，目的是减少磁盘随机访问，将随机访问转化为较为顺序的访问。适用于 range/ref/eq_ref 类型的查询。实现原理：1、在二级索引查找后，根据得到的主键到聚簇索引找出需要的数据。2、二级索引查找得到的主键的顺序是不确定的，因为二级索引的顺序与聚簇索引的顺序不一定一致；3、如果没有 MRR，那么在聚簇索引查找时就可能出现乱序读取数据页，这...
mysql using mrr_优化的道路永无止境——Mysql的ICP及MRR
weixin_39778447的博客
01-19 445
在讲ICP和MRR之前，我们先了解一下MySQL的架构。于本文的重点并不在架构细节上，所以让我们直接看关键部分，存储引擎作为单独的一层，是连接底层存储系统和上层server其他部分的桥梁，而MySQL对存储引擎的抽象也极大地丰富了其可扩展性。ICP我们平时需要查询和写入的数据最终的存储介质都是底层的文件系统，而数据的传输必然要经过上面说到的两个部分。就好像我们将单体服务切分成微服务一样，如果我们需...
MySQL中的MRR优化分析
自律使我自由
09-27 1687
前言 MRR全称：Multi-Range Read，中文翻译过来就是多范围读取，主要解决的是当二级索引取出索引值后再去聚集索引中取行可能会造成大量的磁盘随机IO的问题，下面我们来具体进行分析。 Index Nested-Loop Join 在了解MRR前，我们先看一下什么是Index Nested-Loop Join（简称NLJ），直接翻译过来嵌套循环连接，就和我们平时写嵌套循环的思路是一样的，外层循环一个一个的去数，然后与内层循环挨个匹配。 假设要使用以下连接类型执行三个表t1、t2和 之间 t3的连接：
技术分享 | 用好 MySQL 的 MRR 优化器
ActionTech的博客
06-16 330
作者：蒋乐兴 MySQL DBA，擅长 python 和 SQL，目前维护着 github 的两个开源项目：mysqltools 、dbmc 以及独立博客：https://www.sqlpy.com。 本文来源：原创投稿 *爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 MRR 要解决的问题 MRR 是 MySQL 针对特定查询的一种优化手段。假设一个查询有二级索引可用，读完二级索引后要回表才能查到那些不在当前二级索引上的列值，由于二级索引上引用的主键值不一定是有序的，因.
MySQL 5.6 优化点（MRR、BKA 与 ICP）
zest
07-23 306
最近在学习 MySQL 相关的东西，大致整理一下，都是一下小知识点希望能帮助到你 MRR —— Multi-Range Read Optimization MRR 是优化器将随机 IO 转化为顺序 IO，以降低 IO 开销的手段。 二级索引中存储的是索引列和主键值，当查询列不都存在与索引列中时（即不是覆盖索引的情况），需要回表操作。然而回表获取完整用户记录可能回产生随机 IO（当数据量较多且比...
说说mysql的MRR,ICP,BKA优化。
weixin_40524659的博客
03-07 567
一）MRR优化 Multi-Range Read优化mysql5.6版本开始支持MRR优化，，目的是为了减少磁盘的随机访问， 并且将随机访问转化为较为顺序的顺序访问，适用于range,ref,eq_ref类型的查询。 MRR优化有以下几个好处： 1 MRR 使数据访问变得较为顺序 ，在查询辅助索引时，首先根据得到的查询结果， 按照主键进行排序，并按照主键排序的顺序进行书签查找。 2 减少缓冲池中页...
面试官：什么是索引下推？什么是 MRR 优化？怎么才能更好的为表创建索引？
架构师尼恩
05-04 2509
索引下推也被称为索引条件下推 （Index Condition Pushdown）ICPMySQL新添加的特性，用于优化数据查询的。这里尼恩给大家做一下系统化、体系化的线程池梳理，使得大家可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”。也一并把这个题目以及参考答案，收入咱们的 《尼恩Java面试宝典 PDF》，供后面的小伙伴参考，提升大家的 3高 架构、设计、开发水平。
mysql using mrr_Using MRR(Multi-Range Read )
weixin_31913557的博客
01-21 555
Using MRR(Multi-Range Read )MySQL 5.6版本提供了很多性能优化的特性，其中之一就是 Multi-Range Read 多范围读(MRR) , 它的作用针对基于辅助/第二索引的查询，减少随机IO，并且将随机IO转化为顺序IO，提高查询效率。Multi-Range Read原理在没有MRR之前,或者没有开启MRR特性时，MySQL针对基于辅助索引的查询策略是这样的：s...
mrr微环谐振腔 matlab
08-27
引用中提到了关于高Q值微环谐振器中产生克尔光频梳的理论分析。然而，引用中没有提到与Matlab相关的内容。如果您对Matlab在MRR微环谐振腔中的应用有任何问题，请提供更多的详细信息，以便我可以为您提供更准确的答案。<span class="em">1</span> #### 引用[.reference_title] - *1* [高q值微环谐振器中产生克尔光频梳的理论分析-theoretical analysis of kerr optical frequency comb ...](https://blog.csdn.net/weixin_35924765/article/details/116027839)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 100%"] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值