MySQL 查询优化之 Multi-Range Read
MySQL 查询优化之 Multi-Range Read
在存储引擎中未缓存的大表,使用辅助索引的range scan检索数据, 可能会导致基表进行许多随机磁盘访问。
通过磁盘扫描多范围读取(MRR)优化,可以减少随机I/O,并且将随机I/O转化为顺序I/O,提高查询效率
MRR的工作原理
根据辅助索引的叶子结点上找到的主键值得集合存储到read_rnd_buffer
中,然后在该buffer中对主键值进行排序,最后再利用已经排序好的主键值的集合,去访问表中的数据,这样就由原来的随机/O变成了顺序I/O
,降低了查询过程中的I/O消耗
。
SELECT * FROM t WHERE key_part1>=1000 and key_part1<2000 AND key_part2=1000;
表t
有(key_part1,key_part2
)的联合索引因此索引根据key_part1,key_part2
的位置关系进行排序。
若没有MRR,此时查询类型为Range。SQL优化器会先将key_part1>1000 and key_part2<2000的数据线取出来,即使key_part2不等于1000。待取出的行数据后在根据key_part2的条件进行过滤,这会导致无用的数据被取出,如果有大量的数据且其key_part2不等于1000,则启用MRR优化会使性能有巨大的提升
启用MRR优化,优化器会先将查询条件进行拆分,然后在进行数据查询。上述语句,优化器会将查询条件拆分为(1000,1000),(1001,1000),(1002,1000),...,(1999,1000),然后在根据这些拆分出的条件进行数据查询
MRR开启与关闭
通过参数 optimizer_switch
的标记来控制是否使用MRR
当设置
mrr=on
时,表示启用MRR优化。mrr_cost_based
表示是否通过cost base
基于成本的方式来启用MRR如果选择mrr=on,mrr_cost_based=off,则表示总是开启MRR优化,参数read_rnd_buffer_size 用来控制键值缓冲区的大小。
默认情况下:
mrr=on,mrr_cost_based=on
使用MRR示例
使用MRR时,EXPLAIN输出中的Extra
列显示Using MRR
。
mysql> show index from salaries;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| salaries | 0 | PRIMARY | 1 | emp_no | A | 300557 | NULL | NULL | | BTREE | | |
| salaries | 0 | PRIMARY | 2 | from_date | A | 2838426 | NULL | NULL | | BTREE | | |
| salaries | 1 | emp_no | 1 | emp_no | A | 299974 | NULL | NULL | | BTREE | | |
| salaries | 1 | idx_salary | 1 | salary | A | 73229 | NULL | NULL | | BTREE | | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec) 默认使用的是mrr=on,mrr_cost_based=on mysql> explain select * from salaries s where s.salary between 68000 and 70000;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | s | NULL | range | idx_salary | idx_salary | 4 | NULL | 222726 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec) 设置总是开启mrr mysql> set optimizer_switch='mrr=on,mrr_cost_based=off';
Query OK, 0 rows affected (0.00 sec) mysql> explain select * from salaries s where s.salary between 68000 and 70000;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | s | NULL | range | idx_salary | idx_salary | 4 | NULL | 222726 | 100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
参考文档
https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html
https://www.cnblogs.com/vadim/p/7403544.html
最新文章
- 关于.dll&#39; could not be found 的问题以及解决方案
- Ubuntu下的生活--安装
- 如何将win7变为wifi热点
- 读书笔记——网络编程与开发技术(3)基于TCP/IP协议的网络编程相关知识
- <;转>;安卓应用测试checklist
- data-theme 几种值的样式
- 并行编程条件变量(posix condition variables)
- Elastic Stack之kibana入门
- 宋宝华:Docker 最初的2小时(Docker从入门到入门)【转】
- IntelliJ IDEA使用笔记
- vue实现pc端上拉加载功能,不兼容移动端
- bootstrap响应式前端页面
- ajax请求完之前的loading加载
- 查看pip安装的Python库
- shiro学习(三)权限 authenrication
- kafka资料收集
- Luogu4717 【模板】快速沃尔什变换(FWT)
- C++生成斐波拉其数列
- 【调研】在总体为n的情况下,多少样本有代表性?
- IOS设计模式第四篇之装饰设计模式的类别设计模式