2025-10-31 07:15 广东
MySQL推出这个参数本来是好意，但容易好心办坏事……
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)BEGINDECLARE i INT DEFAULT 1;WHILE i <= total DOINSERT 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;
-- 开启 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 * 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)
写到这里，MySQL推出这个参数本来是好意，但是截至到目前，尚未得到在任何版本，智能化此问题的方案，对于业务和DBA来说，大部分情况选择的是关闭此选项，因为我们不能完全杜绝无可选择性下的数据和索引建立后，在遇到GROUP BY ORDER BY 情况下的错误索引的在这类语句上得使用。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)
作者介绍
刘华阳，20年经历风霜雨打的 DBA，5年的 DBA 架构和团队管理经验，只要是数据库都喜欢学习。PostgreSQL ACE，MongoDB 狂热者，10年的 MYSQL 工作经验，现在在玩 POLARDB 与时俱进。
