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

以下文章来源Java架构师技术，回复”Spring“获惊喜礼包

责编：架构君，来源：AustinDatabases（ID：AustinDatabases）

上一篇推文：推荐一套开源通用后台管理系统（附源码）

大家好，我是Java架构师

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)

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

你还有什么想要补充的吗？

欢迎有需要的同学试试，如果本文对您有帮助，也请帮忙点个 赞 + 在看 啦！❤️

在 GitHub猿 还有更多优质项目系统学习资源，欢迎分享给其他同学吧！



    
最后，再次推荐下我们的AI星球：
为了跟上AI时代我干了一件事儿，我创建了一个知识星球社群：ChartGPT与副业。想带着大家一起探索ChatGPT和新的AI时代。

有很多小伙伴搞不定ChatGPT账号，于是我们决定，凡是这四天之内加入ChatPGT的小伙伴，我们直接送一个正常可用的永久ChatGPT独立账户。

不光是增长速度最快，我们的星球品质也绝对经得起考验，短短一个月时间，我们的课程团队发布了8个专栏、18个副业项目：

简单说下这个星球能给大家提供什么：

1、不断分享如何使用ChatGPT来完成各种任务，让你更高效地使用ChatGPT，以及副业思考、变现思路、创业案例、落地案例分享。
2、分享ChatGPT的使用方法、最新资讯、商业价值。
3、探讨未来关于ChatGPT的机遇，共同成长。
4、帮助大家解决ChatGPT遇到的问题。


    
5、提供一整年的售后服务，一起搞副业

星球福利：

1、加入星球4天后，就送ChatGPT独立账号。
2、邀请你加入ChatGPT会员交流群。
3、赠送一份完整的ChatGPT手册和66个ChatGPT副业赚钱手册。

其它福利还在筹划中... 不过，我给你大家保证，加入星球后，收获的价值会远远大于今天加入的门票费用 ！

本星球第一期原价399，目前属于试运营，早鸟价169，每超过50人涨价10元，星球马上要来一波大的涨价，如果你还在犹豫，可能最后就要以更高价格加入了。。

早就是优势。建议大家尽早以便宜的价格加入！

最后，整理了500多套项目，赠送读者。扫码下方二维码，后台回复【赚钱】即可获取。

--END--

版权申明：内容来源网络，版权归原创者所有。除非无法确认，我们都会标明作者及出处，如有侵权烦请告知，我们会立即删除并表示歉意。谢谢!

往期惊喜：

牛逼啊！接私活必备的 400 个开源项目！赶快收藏吧（附源码合集）！

推荐一套通用管理后台，快速开发、一键生成代码、多端自适应！

性能爆表：SpringBoot利用ThreadPoolTaskExecutor批量插入百万级数据实测！

换掉ES！Redis官方搜索引擎来了，效率大幅提升

扫码关注我们的Java架构师技术

带你全面深入Java