MySQL优化器特性（二）MRR优化
Index Range Scan
索引范围扫描的一般步骤：
1、根据where条件，从B+树定位到第一条记录。
2、从索引页子节点中获取到行号（rowid），根据rowid回表查询数据。
3、使用额外的where条件过滤记录，若满足条件，将记录返回给客户端。
不同的存储引擎，rowid和表的存储格式可能会有差异。比如在MyISAM中，rowid记录的是记录在文件中的偏移量，实际的表中，不需要额外存储rowid。在InnoDB中，rowid就是表的主键，表存储为聚簇索引。
使用索引范围扫描(Index Range Scan)时，按顺序获取的记录ID（rowid）在表中是无序的，当有大量记录需要回表查询时，可能会产生大量随机IO，影响数据库性能。
针对这种场景，MySQL设计了DSMRR（Disk Sweep Multi-range Read）访问路径，在回表查询记录前，先将从索引中获取到的rowid存放在MRR buffer中，并按rowid进行排序，然后按rowid的顺序回表查询数据。
MRR优化的例子
满足一定的条件后，优化器才会使用MRR优化，出于演示的目的，我们给查询添加Hint。
测试表结构：
mysql> show create table tb\G *************************** 1. row *************************** Table: tb Create Table: CREATE TABLE `tb` ( `a` int DEFAULT NULL, `b` int DEFAULT NULL, `c` int DEFAULT NULL, `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), KEY `idx_c` (`c`), KEY `idx_ab` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=321 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
MRR hint：
mysql> explain select /*+ MRR(tb) */ * from tb where a < 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb partitions: NULL type: range possible_keys: idx_ab key: idx_ab key_len: 5 ref: NULL rows: 32 filtered: 100.00 Extra: Using index condition; Using MRR 1 row in set, 1 warning (0.00 sec)
Extra中显示“Using MRR”，说明采用了MRR优化。
不加Hint时，没有使用MRR优化：
mysql> explain select * from tb where a < 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb partitions: NULL type: range possible_keys: idx_ab key: idx_ab key_len: 5 ref: NULL rows: 32 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec)
使用BKA hint也会使用MRR优化
mysql> explain select /*+ BKA(tb) */ * from tb where a < 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb partitions: NULL type: range possible_keys: idx_ab key: idx_ab key_len: 5 ref: NULL rows: 32 filtered: 100.00 Extra: Using index condition; Using MRR 1 row in set, 1 warning (0.01 sec)
MRR执行过程
1、根据where条件，定位索引range。
2、获取rowid，先存放到MRR buffer中。
3、根据rowid对MRR buffer进行排序。
4、按rowid的顺序回表获取数据。
5、根据where条件中的额外条件过滤数据，若满足条件，将数据返回给客户端。
按rowid的顺序回表获取数据，可能能在一定程度上提升数据库缓存的命中率，减少磁盘随机IO。
使用MRR的条件
查询需要满足以下几个条件，优化器才会使用MRR优化：
1、使用了索引范围执行计划(range)，非覆盖索引，非Primary Key。如果查询本身没有使用索引范围扫描的执行计划，就无法使用MRR优化。如果查询用了覆盖索引，不需要回表，或者是在InnoDB的主键上进行范围扫描，都不需要使用MRR优化。
2、表占用的空间超过一定大小时，才会使用MRR优化。对于InnoDB，表的大小超过InnoDB buffer pool，对于MyISAM，表的大小超过100M，优化器才会考虑使用MRR优化。
3、range查询预估需要访问的记录数超过一定数量，才会考虑使用MRR优化。如果预估需要访问的记录数不多，则不会考虑使用MRR优化。8.0.32版本中这个值是50。
4、optimizer_switch，开启MRR。默认情况下mrr和mrr_cost_based都是开启的。
5、如果optimizer_switch mrr_cost_based=on，则优化器会评估MRR执行计划的成本，只有当MRR的成本比默认range访问的成本更低时，才会使用MRR优化。
6、如果给查询添加MRR或BKA提示，则只需要满足条件1就可以使用MRR优化。当然一般情况下不建议使用这几个Hint。让优化器自动选择就可以了。
如何确定MRR buffer大小
MRR buffer大小的上限由参数read_rnd_buffer_size决定。如果优化器评估出来range需要访问的记录数较小，则实际分配的buffer可能会比read_rnd_buffer_size小，会按需分配buffer（记录数 * 单条ROWID占用的空间）。
