MySQL InnoDB MRR优化指南

MySQL InnoDB MRR优化指南

什么是MRR

MRR（Multi-Range Read）是MySQL InnoDB存储引擎在执行查询时的一种优化方式。它的优化思路是将多个范围读请求合并成一个请求，从而减少磁盘I/O，提高查询效率。

MRR优化条件

MRR优化并不是所有查询都可以享受的，它有如下一些优化条件：

1. 查询是基于索引的范围查询。
2. 索引是联合索引。
3. 查询涉及的列全部来自于索引。
4. 查询涉及的所有列的数据类型是相同的。

只有当这些条件都满足时，MRR才会生效。

MRR的开启

MRR的默认开启是关闭的，需要手动设置才能启用。MRR优化的开启方式如下：

SET @@optimizer_switch='mrr=on';

我们也可以通过以下方式检查MRR是否开启：

SHOW STATUS LIKE 'Innodb_have_mrr%';

如果结果是Yes，则MRR已经成功开启。

MRR的优化效果

MRR可以因合并磁盘I/O而减少单个查询的时间，从而提高查询效率。下面我们通过两个实际的例子来演示MRR的优化效果：

示例1

我们创建一个测试表用于测试MRR优化效果：

CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_phone_idx` (`name`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

往表中插入100w条记录：

insert into test(name, phone) select left(md5(rand()), 4), left(md5(rand()), 11) from t1,t1,t1,t1,t1,t1,t1,t1,t1,t1;

执行如下的查询语句，查询所有名字为"abcd"的记录：

SELECT * FROM test WHERE name="abcd";

开启MRR优化后，查询时间由10s左右减少到6s左右，效率提升了40%。

示例2

接下来，我们将对表中的两列进行查询，其中一列使用等值查询，另一列使用范围查询。具体语句如下：

SELECT * FROM test WHERE name="abcd" AND phone>"dQp5kn" AND phone<"fVtwOZ";

这个语句可以看作是同时使用了索引"name_phone_idx"中的name和phone两列。如果关闭MRR的优化，执行时间大概在16s左右。

开启MRR优化后，查询时间缩短为7s左右，效率提高了超过50%。

结论

通过以上的两个实例，我们可以看到，使用MRR优化可以让我们的查询语句在减少磁盘I/O的同时，提高查询效率。对于需要处理大量数据的查询任务，这种优化方式可以给查询性能带来可观的提升。