二级索引和回表优化之MRR

---

MySQL学习系列

---

一般情况下只能利用单个二级索引执行查询， 比方说下边的这个查询：

SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:34:56' AND order_no> '你好， 李焕英。 7 排 24 号,过期时长:DD00_24S';

查询优化器会识别到这个查询中的两个搜索条件：

insert_time = '2021-03-22 18:34:56'
order_no> '你好， 李焕英。 7 排 24 号,过期时长:DD00_24S'

优化器一般会根据 order_exp 表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少， 选择那个扫描行数较少的条件到对应的二级索引中查询。

然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的 WHERE 条件过滤记录。 一般来说， 等值查找比范围查找需要扫描的行数更少（也就是 ref 的访问方法一般比 range 好， 但这也不总是一定的， 也可能采用 ref 访问方法的那个索引列的值为特定值的行数特别多） 。 所以这里假设优化器决定使用 idx_insert_time 索引进行查询， 那么整个查询过程可以分为两个步骤：

步骤 1： 使用二级索引定位记录的阶段， 也就是根据条件 insert_time = '2021-03-22 18:34:56’从 idx_insert_time 索引代表的 B+树中找到对应的二级索引记录。

步骤 2： 回表阶段， 也就是根据上一步骤中找到的记录的主键值进行回表操作， 也就是到聚簇索引中找到对应的完整的用户记录， 再根据条件 order_no>‘你好， 李焕英。 7 排 24 号,过期时长:DD00_24S’ 到完整的用户记录继续过滤。 将最终符合过滤条件的记录返回给用户。

很明显， 因为二级索引的节点中的记录只包含索引列和主键， 所以在步骤 1中使用idx_insert_time索引进行查询时只会用到与insert_time列有关的搜索条件，其余条件， 比如 order_no> ‘…………’ 这个条件在步骤 1 中是用不到的， 只有在步骤 2 完成回表操作后才能继续针对完整的用户记录中继续过滤。

MRR

从上文可以看出， 每次从二级索引中读取到一条记录后， 就会根据该记录的主键值执行回表操作。 而在某个扫描区间中的二级索引记录的主键值是无序的，也就是说这些二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。

每次执行回表操作时都相当于要随机读取一个聚簇索引页面， 而这些随机 IO带来的性能开销比较大。 MySQL 中提出了一个名为 Disk-Sweep Multi-Range Read(MRR， 多范围读取)的优化措施， 即先读取一部分二级索引记录， 将它们的主键值排好序之后再统一执行回表操作。

相对于每读取一条二级索引记录就立即执行回表操作， 这样会节省一些 IO开销。 使用这个 MRR 优化措施的条件比较苛刻， 所以我们直接认为每读取一条二级索引记录就立即执行回表操作。 MRR 的详细信息， 可以查询官方文档。