Mysql多范围读(MRR)

通过二级索引获取表的数据时，如果表很大，未缓存在内存中时，会产生大量的随机IO。MRR优化先只读二级索引记录，并按照主键排序。然后按照排序好的顺序获取数据。MRR将随机的IO转换成了顺序的IO。MRR不支持虚拟列上的索引。

MRR工作过程：

假设查询为:

select * from t where name show indexes from emps;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emps | 0 | PRIMARY | 1 | id | A | 299489 | NULL | NULL | | BTREE | | | YES | NULL |
| emps | 1 | no_ck | 1 | emp_no | A | 299489 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
mysql> desc select * from emps where emp_no between 20000 and 25000;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | emps | NULL | range | no_ck | no_ck | 5 | NULL | 5001 | 100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)


优化器控制:
可以通过优化器参数mrr和mrr_cost_based控制MRR。

• mrr控制是否开启MRR
• mrr_cost_based控制是否基于成本做选择。

存储引擎使用read_rnd_buffer_size作为缓存，read_rnd_buffer_size决定了一个批次的mrr的大小，可适量增大read_rnd_buffer_size以提高mrr效率。