MySQL 8的老大难问题，从5.7延续至今，有这么难？

2025-10-31 10:04:41

MySQL在SQL执行中有一个一直没有明晰，且没有定论的问题，这个问题就是SET optimizer_switch = 'prefer_ordering_index=off'; 可他默认的设置是ON。我到底应该是ON 还是OFF。

数据库配置选项

这个优化器的主要作用针对order by 和 group by中的搭配limit的查询，决定优化器是否优先选择已经建立排序规则的有序索引问题，来替代在查询中的 filesort等优化数据提取后，在排序的问题。

那么问题来了，这不是一个好的功能吗？为什么有一种说法，要关掉这个配置建议off处理。

这个问题主要发生在以下情况中：

你的数据分布的不均匀，优化器在选择这个索引进行数据排序的时候，这个索引中包含，如性别，状态，等被包含在group by order by 中，而这些数据根本不具备索引扫描的优势，属于通篇都是女，然后就导致 index scan ，然后排序毫无用处，还需要回表。

具体总结为：

查询命中了绝大部分（例如 99%）的数据行。

优化器如果选择了这个有序索引：它会逐行扫描索引并回表读取数据。

由于需要读取几乎所有的数据行，使用索引带来的回表 IO 开销（随机 IO）变得巨大，远高于全表扫描的顺序 IO。

同时，由于 WHERE 子句的选择性极低，即使使用全表扫描后进行排序（Filesort），排序操作的开销也可能比大量的随机回表 IO 低得多.

我们做一个简单的练习

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 ;

-- 开启 prefer_ordering_indexSET optimizer_switch = 'prefer_ordering_index=on';EXPLAIN SELECT * FROM users ORDER BY gender LIMIT 10;
-- 关闭 prefer_ordering_indexSET optimizer_switch = 'prefer_ordering_index=off';EXPLAIN SELECT * FROM users ORDER BY gender LIMIT 10;

mysql> SET optimizer_switch = 'prefer_ordering_index=on';Query OK, 0 rows affected (0.00 sec)
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 inset, 1 warning (0.00 sec)
mysql> SET optimizer_switch = 'prefer_ordering_index=off';Query OK, 0 rows affected (0.00 sec)
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 inset, 1 warning (0.00 sec)

-- 开启 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> SET optimizer_switch = 'prefer_ordering_index=on';Query OK, 0 rows affected (0.00 sec)
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 inset, 1 warning (0.04 sec)
mysql> mysql> -- 关闭 prefer_ordering_indexmysql> SET optimizer_switch = 'prefer_ordering_index=off';Query OK, 0 rows affected (0.00 sec)
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 inset, 1 warning (0.00 sec)