17认证网MySQL在SQL执行中有一个一直没有明晰，且没有定论的问题，这个问题就是SET optimizer_switch = ‘prefer_ordering_index=off’; 可他默认的设置是ON。我到底应该是ON 还是OFF。
这个优化器的主要作用针对order by 和 group by中的搭配limit的查询，决定优化器是否优先选择已经建立排序规则的有序索引问题，来替代在查询中的 filesort等优化数据提取后，在排序的问题。
那么问题来了，这不是一个好的功能吗？ 为什么有一种说法，要关掉这个配置建议off处理。
这个问题主要发生在以下情况中
1 你的数据分布的不均匀，优化器在选择这个索引进行数据排序的时候，这个索引中包含，如性别，状态，等被包含在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)
