MySQL中如何将WHERE条件下推至所有关联的CTE表？

问题场景 #

我在MySQL项目中通过多个CTE分别计算用户的不同特征（比如平均分数、最高分数），最后关联users表和所有CTE查询用户数据。简化后的表结构和查询语句如下：

表结构 #

CREATE TABLE users (
    user_id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY (user_id)
);

CREATE TABLE scores (
   user_id int(11) NOT NULL,
   score int(3) default NULL,
   CONSTRAINT `users_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

原查询语句 #

WITH
    avg_score_by_user AS (
        SELECT 
            user_id,
            AVG(score) AS avg_score
        FROM
            scores
        GROUP BY user_id
    ),
    
    max_score_by_user AS (
        SELECT
            user_id,
            MAX(score) AS max_score
        FROM
            scores
        GROUP BY user_id
    )
    
SELECT
    users.user_id,
    avg_score_by_user.avg_score,
    max_score_by_user.max_score
FROM
    users
JOIN
    avg_score_by_user ON avg_score_by_user.user_id = users.user_id
JOIN
    max_score_by_user ON max_score_by_user.user_id = users.user_id
WHERE users.user_id = 1;

问题现象 #

添加WHERE users.user_id=1后，执行计划显示两个CTE都扫描了scores表的全部数据，而非仅过滤user_id=1的行。即使改用USING(user_id)关联，或者把条件写为avg_score_by_user.user_id=1，也只有对应的单个CTE会提前过滤，另一个CTE依然全表扫描。

解决方案 #

方案1：将过滤条件直接嵌入CTE中 #

在每个CTE内添加WHERE user_id = 1，强制提前过滤目标用户数据：

WITH
    avg_score_by_user AS (
        SELECT 
            user_id,
            AVG(score) AS avg_score
        FROM
            scores
        WHERE user_id = 1
        GROUP BY user_id
    ),
    
    max_score_by_user AS (
        SELECT
            user_id,
            MAX(score) AS max_score
        FROM
            scores
        WHERE user_id = 1
        GROUP BY user_id
    )
    
SELECT
    users.user_id,
    avg_score_by_user.avg_score,
    max_score_by_user.max_score
FROM
    users
JOIN
    avg_score_by_user ON avg_score_by_user.user_id = users.user_id
JOIN
    max_score_by_user ON max_score_by_user.user_id = users.user_id
WHERE users.user_id = 1;

方案2：改用关联子查询替代CTE #

MySQL对关联子查询的条件下推支持更友好，将CTE改写为子查询形式：

SELECT
    u.user_id,
    (SELECT AVG(score) FROM scores s WHERE s.user_id = u.user_id) AS avg_score,
    (SELECT MAX(score) FROM scores s WHERE s.user_id = u.user_id) AS max_score
FROM users u
WHERE u.user_id = 1;

方案3：合并聚合逻辑，一次扫描计算多特征 #

如果多个特征都来自同一张表，可合并成单个聚合查询，避免多次扫描表：

SELECT
    u.user_id,
    s.avg_score,
    s.max_score
FROM users u
JOIN (
    SELECT
        user_id,
        AVG(score) AS avg_score,
        MAX(score) AS max_score
    FROM scores
    WHERE user_id = 1
    GROUP BY user_id
) s ON s.user_id = u.user_id
WHERE u.user_id = 1;

原理说明 #

MySQL的CTE优化器在部分场景下不会自动将外层等值条件下推至CTE，尤其是当CTE被当作独立派生表处理时。直接在CTE内加过滤条件、改用子查询或合并聚合逻辑，能让优化器明确识别提前过滤的需求，从而减少扫描行数，提升查询性能。

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