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_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;

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_index mysql> 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)

写到这里，MySQL推出这个参数本来是好意，但是截至到目前，尚未得到在任何版本，智能化此问题的方案，对于业务和DBA来说，大部分情况选择的是关闭此选项，因为我们不能完全杜绝无可选择性下的数据和索引建立后，在遇到GROUP BY ORDER BY 情况下的错误索引的在这类语句上得使用。

作者介绍

刘华阳，20年经历风霜雨打的 DBA，5年的 DBA 架构和团队管理经验，只要是数据库都喜欢学习。PostgreSQL ACE，MongoDB 狂热者，10年的 MYSQL 工作经验，现在在玩 POLARDB 与时俱进。

来源丨公众号：AustinDatabases（ID：AustinDatabases）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn