MySQL MRR优化：让磁盘不再“跳广场舞”！

大家好，今天要给大家讲个硬盘老哥的烦恼故事。这位硬盘老哥每天被MySQL使唤得晕头转向，直到遇见了MRR这位"舞蹈教练"，从此磁盘舞步从混乱的广场舞变成了优雅的华尔兹…

硬盘老哥的烦恼

想象一下，你是一个硬盘（Disk），每天的工作就是存储数据。有一天，MySQL给你发来一个任务：

“嘿，Disk老哥，给我找一下dbbro_user表里所有年龄在18到30岁之间的小哥哥小姐姐的信息呗！”

你一看这个请求，心里盘算着：“这还不简单？” 但当你开始工作的时候，悲剧发生了：

1. 1. 先找到年龄18的小王 - 位置：盘片最外圈
2. 2. 接着找年龄25的小李 - 位置：盘片最内圈
3. 3. 然后找年龄22的小张 - 位置：盘片中间层

你的磁头就像跳广场舞的大妈，在盘片上疯狂摇摆：“左三圈！右三圈！脖子扭扭！屁股扭扭！” 🕺💃

一天下来，磁头老腰都快断了，忍不住抱怨：“MySQL大哥，您能不能让我按顺序访问数据啊？”

这时候，MRR（Multi-Range Read）闪亮登场：“Disk老哥别急，我来教你跳优雅的华尔兹！”

一、MRR是什么神仙操作？

MRR（Multi-Range Read） 是MySQL 5.6版本引入的查询优化技术，专门治疗"磁盘广场舞综合征"！它的核心思想很简单：

把随机访问变成顺序访问，让磁盘跳起优雅的华尔兹 💃

MRR的三大绝技

1. 1. 顺序访问：把乱糟糟的查询结果，按照主键排好队
2. 2. 缓冲池保护：减少缓冲池页面的"踢皮球"游戏
3. 3. 批量处理：一卡车一卡车地运送数据，告别小推车

二、MRR的魔法原理揭秘

传统查询的"死亡之舞"

    A[索引扫描] --> B[随机获取主键1]
    B --> C[回表查询1]
    A --> D[随机获取主键2]
    D --> E[回表查询2]
    A --> F[随机获取主键3]
    F --> G[回表查询3]

就像在超市购物：

1. 1. 拿一瓶酱油（过道3）
2. 2. 拿一包盐（过道1）
3. 3. 拿一瓶醋（过道5） …来回穿梭，累成狗！

MRR的优雅舞步

    A[索引扫描] --> B[收集所有主键]
    B --> C[主键排序]
    C --> D[顺序回表查询]

这才是聪明的购物方式：

1. 1. 先列购物清单：酱油、盐、醋
2. 2. 按过道排序：过道1（盐）-> 过道3（酱油）-> 过道5（醋）
3. 3. 一次性采购完成！

技术解剖（InnoDB版）

在MySQL InnoDB引擎中，MRR的工作流程：

1. 1. 收集阶段：通过辅助索引扫描，收集满足条件的行指针
2. 2. 排序阶段：将收集到的行指针按主键顺序排序

   -- MRR的内心OS
   SELECT row_id FROM index WHEREcondition; -- 先收集
   SORT row_id BYPRIMARY KEY; -- 再排序
   

3. 3. 顺序访问阶段：按主键顺序访问数据页

   -- 优雅的磁盘访问
   FOR sorted_row_id IN sorted_list:
   GET data_page BYPRIMARY KEY
   

三、实战演示：DBBro的用户查询优化

创建测试舞台

-- 创建dbbro_user表（必须带dbbro哦！）
CREATE TABLE dbbro_user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age TINYINT NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    INDEX idx_age (age),
    INDEX idx_salary (salary)
) ENGINE=InnoDB;

-- 插入100万测试数据
DELIMITER $$
CREATEPROCEDURE insert_dbbro_data()
BEGIN
DECLARE i INTDEFAULT0;
    WHILE i <1000000 DO
INSERT INTO dbbro_user (name, age, salary)
VALUES (
            CONCAT('user_', i),
FLOOR(18+ RAND() *50),
3000+ RAND() *20000
        );
SET i = i +1;
END WHILE;
END$$
DELIMITER ;

CALL insert_dbbro_data();

场景1：没有MRR的"死亡之舞"

-- 关闭MRR方便对比
SET optimizer_switch='mrr=off';

EXPLAIN 
SELECT*FROM dbbro_user 
WHERE age BETWEEN25AND35;

执行计划：

+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | dbbro_user | NULL       | range | idx_age       | idx_age | 1       | NULL | 199045 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------------+

磁盘OS：“又要开始跳广场舞了💃，我的老腰啊！”

场景2：开启MRR的优雅华尔兹

-- 开启MRR
SET optimizer_switch='mrr=on,mrr_cost_based=off';

EXPLAIN 
SELECT/*+ MRR(dbbro_user) */*
FROM dbbro_user 
WHERE age BETWEEN25AND35;

执行计划：

+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | dbbro_user | NULL       | range | idx_age       | idx_age | 1       | NULL | 199045 |   100.00 | Using index condition; Using MRR |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------+

磁盘OS：“太舒服了！就像在红毯上走直线🌟”

性能对比

四、MRR的高级舞步技巧

1. JOIN查询优化

当MRR遇上JOIN，就像跳双人舞：

EXPLAIN
SELECT/*+ MRR(t1) MRR(t2) */
    t1.name, t2.salary
FROM dbbro_user t1
JOIN dbbro_salary t2 ON t1.id = t2.user_id
WHERE t1.age BETWEEN30AND40;

MRR在这里帮了大忙：

1. 1. 先收集符合条件的用户ID
2. 2. 按主键排序后批量获取
3. 3. 用批量数据执行JOIN

2. 参数调优秘籍

在MySQL 8.0中优化MRR：

-- 查看MRR设置
SHOW VARIABLES LIKE'optimizer_switch';

-- 关键参数
SET @@session.read_rnd_buffer_size =262144;  -- MRR缓冲区大小
SET optimizer_switch ='mrr=on,mrr_cost_based=on';

💡 专业提示：缓冲区太小→频繁排序；缓冲区太大→内存浪费。Goldilocks原则："刚刚好"才是真的好！

五、MRR的适用舞池

MRR不是万能的，但在这些场景下效果拔群：

✅ 范围查询：BETWEEN、>、<等

SELECT*FROM dbbro_products 
WHERE price BETWEEN100AND200;

✅ 多值查询：IN()列表

SELECT*FROM dbbro_orders 
WHERE user_id IN (101, 205, 307, ...);

✅ 索引合并：当使用多个索引时

SELECT*FROM dbbro_logs
WHEREdate>'2023-01-01'AND status ='SUCCESS';

❌ 不适合场景：

• • 小表查询（杀鸡用牛刀）
• • 全表扫描（本来就有序）
• • 主键查询（天然有序）

六、结语：致优雅的数据库之舞

MRR就像一位天才编舞师，把MySQL的"广场舞"变成了优雅的"华尔兹"。记住它的三大绝招：

1. 1. 收集：先把所有要找的ID记在小本本上
2. 2. 排序：按主键顺序排好队
3. 3. 访问：优雅地一次性获取数据

最后，硬盘老哥托我给大家带句话：“自从用了MRR，腰不酸了，腿不疼了，寻道时间从10ms降到1ms，感觉自己又年轻了10岁！”