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
