MySQL prefer_ordering_index参数深度解析：从5.7到8.0的优化困局与实战避坑

在MySQL中，有一个长期存在且颇具争议的配置项：optimizer_switch 中的 prefer_ordering_index。其默认值为 ON，但许多资料却建议将其设为 OFF。这个从MySQL 5.7延续至今的难题，究竟该如何抉择？

prefer_ordering_index 的作用

这个优化器开关主要作用于包含 ORDER BY 或 GROUP BY 并搭配 LIMIT 的查询。它的核心决策是：优化器是否应该优先选择已经建立了排序规则的有序索引，来避免查询完成后额外的 filesort 操作。

简单来说，如果能直接通过索引顺序拿到排好序的数据，理论上可以跳过排序步骤，提升性能。

为什么“好功能”会被建议关闭？

既然能加速排序，听起来是个十足的好功能，为何会有“关闭它”的声音？问题的根源在于数据分布不均。

当优化器选择了一个有序索引进行排序时，如果该索引的列（如 gender、status）包含在 GROUP BY/ORDER BY 中，但这些列的值区分度极低（例如，表中99%的行的gender都是‘F’），就会引发性能灾难。

具体场景可归纳为：

1. 查询命中了绝大部分数据行（例如 99%）。
2. 优化器如果选择了这个低区分度的有序索引，它会进行索引扫描。
3. 由于需要读取几乎所有行，会产生大量的回表随机IO。
4. 此时，使用索引带来的随机IO开销，可能远高于直接进行全表扫描（顺序IO） 后再做 filesort 的代价。

实战演示：开启与关闭的对比

我们通过一个简单的实验来观察其影响。

首先，创建测试表和数据：

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    gender CHAR(1),  -- 'M' 或 'F'
    age INT,
    INDEX idx_gender (gender)
);

DELIMITER //
CREATE PROCEDURE load_users(IN total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= total DO
        INSERT INTO users(name, gender, age)
        VALUES (
            CONCAT('user_', i),
            IF(RAND() > 0.5, 'M', 'F'),
            FLOOR(20 + (RAND() * 30))
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;
-- 假设调用存储过程生成数据
-- CALL load_users(1000000);

场景一：ORDER BY ... LIMIT

我们观察 ORDER BY 搭配 LIMIT 时，该参数如何影响 MySQL优化器 的执行计划。

-- 开启 prefer_ordering_index
SET optimizer_switch = 'prefer_ordering_index=on';
EXPLAIN SELECT * FROM users ORDER BY gender LIMIT 10;

-- 关闭 prefer_ordering_index
SET optimizer_switch = 'prefer_ordering_index=off';
EXPLAIN SELECT * FROM users ORDER BY gender LIMIT 10;

执行结果对比：

-- 当 prefer_ordering_index=on 时
mysql> EXPLAIN SELECT * FROM users ORDER BY gender LIMIT 10;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | idx_gender | 5       | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- 当 prefer_ordering_index=off 时
mysql> EXPLAIN SELECT * FROM users ORDER BY gender LIMIT 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997227 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

关键差异：

• 开启时：优化器选择了 idx_gender 索引进行 index 扫描。它计划只读取10行（rows: 10），因为它认为可以通过索引快速拿到前10条有序数据。
• 关闭时：优化器放弃了使用 idx_gender 索引选择，转而进行全表扫描（type: ALL），并明确显示需要在内存/磁盘中进行排序（Extra: Using filesort）。

场景二：GROUP BY

再来看 GROUP BY 语句：

-- 开启 prefer_ordering_index
SET optimizer_switch = 'prefer_ordering_index=on';
EXPLAIN SELECT gender, COUNT(*) FROM users GROUP BY gender;

-- 关闭 prefer_ordering_index
SET optimizer_switch = 'prefer_ordering_index=off';
EXPLAIN SELECT gender, COUNT(*) FROM users GROUP BY gender;

执行结果：

-- 无论开关，执行计划相同
mysql> EXPLAIN SELECT gender, COUNT(*) FROM users GROUP BY gender;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | index | idx_gender    | idx_gender | 5       | NULL | 997227 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

在这个 GROUP BY 查询中，由于只需要gender字段，且该字段已建立索引，查询可以通过“覆盖索引”完成（Extra: Using index），因此优化器的选择不受 prefer_ordering_index 参数影响，避免了 全表扫描。

总结与最佳实践

MySQL 引入 prefer_ordering_index 的本意是好的，旨在利用有序索引加速排序操作。然而，截至目前的版本（包括 MySQL 8.0），优化器尚不能完全智能地判断在数据分布极度倾斜的情况下，使用有序索引带来的随机IO代价是否会超过其收益。

因此，对于数据库管理员和开发者而言，一个普遍采用的保守策略是：在会话或全局级别将该参数设置为 OFF。

建议操作：

-- 在当前会话中关闭（推荐，针对特定复杂查询）
SET SESSION optimizer_switch = 'prefer_ordering_index=off';

-- 在全局范围关闭（影响所有新连接，需谨慎）
SET GLOBAL optimizer_switch = 'prefer_ordering_index=off';

这样做可以避免因某些低区分度索引的存在，导致在包含 ORDER BY ... LIMIT 的查询中产生非预期的性能倒退。当然，最根本的解决方案还是在设计索引时充分考虑数据的分布特征，但对于复杂的、动态变化的数据环境，关闭此选项是一个简单有效的“避坑”手段。