MySQL 8中ORDER BY+GROUP BY+窗口函数组合查询排序异常问题

在MySQL 8.0.33至8.0.35版本中，当查询同时包含ORDER BY、GROUP BY、GROUP_CONCAT()以及无ORDER BY子句的COUNT(*) OVER()窗口函数时，会出现排序逻辑异常，最终结果未按指定字段排序。以下是测试用例及问题解析：

表结构与测试数据 #

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    sort INT
);

INSERT INTO users (username, email, sort) VALUES ('user1', 'user1@example.com', 50);
INSERT INTO users (username, email, sort) VALUES ('user2', 'user2@example.com', 30);
INSERT INTO users (username, email, sort) VALUES ('user3', 'user3@example.com', 20);
INSERT INTO users (username, email, sort) VALUES ('user4', 'user4@example.com', 90);
INSERT INTO users (username, email, sort) VALUES ('user5', 'user5@example.com', 40);
INSERT INTO users (username, email, sort) VALUES ('user6', 'user6@example.com', 70);

问题1：为何第一个查询未按预期排序？ #

这是MySQL 8.0.33-8.0.35版本中优化器的已知bug。当查询同时包含以下元素时：

• 带有GROUP_CONCAT()的GROUP BY分组操作
• 无ORDER BY子句的COUNT(*) OVER()全窗口聚合
• 全局的ORDER BY排序子句

优化器错误调整了执行计划的步骤顺序：本该在分组完成后执行的全局ORDER BY，被提前到窗口函数计算前执行；或者优化器将窗口函数的无排序定义错误关联，导致分组后的结果未正确应用最终排序规则，最终返回结果不符合预期。执行计划中显示的排序步骤顺序异常，也印证了这一点。

问题2：推荐的修复/规避方案 #

方案1：升级MySQL版本（优先推荐） #

官方已在MySQL 8.0.36版本中修复了该优化器bug，升级到8.0.36及以上版本后，无需修改查询语句即可恢复正常排序逻辑。

方案2：用子查询分离分组与窗口函数 #

将分组查询和窗口函数计算拆分为两层，避免优化器混淆排序逻辑，示例如下：

SELECT
    sort,
    username,
    email_concat,
    COUNT(*) OVER () AS total_count
FROM (
    SELECT
        sort,
        username,
        GROUP_CONCAT(email) AS email_concat
    FROM users
    GROUP BY id
) AS grouped_users
ORDER BY sort;

方案3：替换窗口函数的无排序定义 #

用COUNT(*) OVER (PARTITION BY 1)替代COUNT(*) OVER ()，PARTITION BY 1会将所有行视为一个分区，功能上和COUNT(*) OVER ()完全一致，但能避免优化器的错误排序关联，无需添加冗余的ORDER BY NULL：

SELECT
    sort,
    username,
    GROUP_CONCAT(email) AS email_concat,
    COUNT(*) OVER (PARTITION BY 1) AS total_count
FROM users
GROUP BY id
ORDER BY sort;

内容的提问来源于stack exchange，提问作者lxa