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

❝

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共3300人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6 7群均已爆满，开8群近400 9群 200+，开10群PolarDB专业学习群100+)

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)

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

MongoDB “升级项目” 大型连续剧(3)-- 自动校对代码与注意事项

MongoDB “升级项目” 大型连续剧(2)-- 到底谁是"der"

MongoDB “升级项目”  大型连续剧(1)-- 可“生”可不升

MongoDB  大俗大雅，上来问分片真三俗 -- 4 分什么分

MongoDB 大俗大雅，高端知识讲“庸俗” --3 奇葩数据更新方法

MongoDB 学习建模与设计思路--统计数据更新案例

MongoDB  大俗大雅，高端的知识讲“通俗” -- 2 嵌套和引用

MongoDB  大俗大雅，高端的知识讲“低俗” -- 1 什么叫多模

MongoDB 合作考试报销活动 贴附属，MongoDB基础知识速通

MongoDB 年底活动，免费考试名额 7个公众号获得

MongoDB 使用网上妙招，直接DOWN机---清理表碎片导致的灾祸 (送书活动结束)

MongoDB 2023年度纽约 MongoDB 年度大会话题 -- MongoDB 数据模式与建模

数据库优化系列