多字段组合匹配时重复SQL IN子查询的优化方法

问题背景 #

核心业务需求为筛选会话记录时，保证metrics_user_training_cohort表中存在同一行记录，同时匹配会话对应的user_id和training_id。当前使用两个独立IN子查询的写法存在逻辑冗余，且无法完全保证同记录匹配的逻辑严谨性。
原有完整查询语句如下：

SELECT date(metrics_session.created_at) as day, COUNT(metrics_session.user_id) as total_logins,  
            sum(TIMESTAMPDIFF(MINUTE,metrics_session.created_at,metrics_session.completed_at)) as total_time_spent  
            FROM metrics_session 
            inner join metrics_training on metrics_training.id = metrics_session.training_id 
            inner join metrics_course on metrics_course.id = metrics_training.course_id
            inner join metrics_user_training_cohort on  metrics_training.id = metrics_user_training_cohort.training_id
            inner join auth_user on auth_user.id = metrics_user_training_cohort.user_id

            WHERE metrics_session.created_at >= '2021-01-15'
            AND metrics_session.created_at <= '2022-10-15' 
            AND metrics_session.completed_at IS NOT NULL
            AND metrics_session.user_id In (SELECT user_id from metrics_user_training_cohort  where user_id = 44 and training_id = 4)
            AND metrics_session.training_id In (SELECT training_id from metrics_user_training_cohort  where user_id = 44 and training_id = 4)
                           
            #AND EXISTS(SELECT user_id,training_id from metrics_user_training_cohort  where user_id = 44 and training_id = 4)
            GROUP BY date(metrics_session.created_at) ORDER BY date(metrics_session.created_at)

可选优化方案 #

方案1：多列IN匹配（最小改动） #

SQL原生支持多字段同时做IN匹配，不需要拆成两个独立子查询。该写法可以保证两个字段来自子查询的同一行记录，逻辑严谨，且只需要执行一次子查询：

-- 替换原有两个独立IN条件即可
AND (metrics_session.user_id, metrics_session.training_id) IN (
    SELECT user_id, training_id
    FROM metrics_user_training_cohort
    WHERE user_id = 44 AND training_id = 4
)

方案2：关联EXISTS（索引场景下性能更优） #

之前注释的EXISTS写法不生效，核心原因是没有将子查询与外层会话表做字段关联，正确的关联EXISTS写法如下：

AND EXISTS (
    SELECT 1
    FROM metrics_user_training_cohort mutc
    WHERE mutc.user_id = metrics_session.user_id
      AND mutc.training_id = metrics_session.training_id
      AND mutc.user_id = 44
      AND mutc.training_id = 4
)

该写法在metrics_user_training_cohort表的(user_id, training_id)联合索引存在时，查询效率远高于IN写法，数据库不需要全量扫描子查询结果，匹配到符合条件的记录就会终止当前行的校验。

方案3：复用现有JOIN逻辑（性能最优，无额外子查询） #

原查询已经对metrics_user_training_cohort做了INNER JOIN，完全不需要额外写子查询过滤，只需要补全缺失的JOIN关联条件，直接在JOIN层面做过滤即可，避免重复扫描表：

注意：原JOIN metrics_user_training_cohort时只关联了training_id，缺少user_id的关联条件，会产生不必要的笛卡尔积，导致统计结果重复计算。

优化后的完整SQL：

SELECT 
    DATE(ms.created_at) AS day,
    COUNT(ms.user_id) AS total_logins,
    SUM(TIMESTAMPDIFF(MINUTE, ms.created_at, ms.completed_at)) AS total_time_spent
FROM metrics_session ms
INNER JOIN metrics_training mt ON mt.id = ms.training_id
INNER JOIN metrics_course mc ON mc.id = mt.course_id
INNER JOIN metrics_user_training_cohort mutc
    ON mt.id = mutc.training_id
    AND ms.user_id = mutc.user_id -- 补全user_id关联，避免笛卡尔积
INNER JOIN auth_user au ON au.id = mutc.user_id
WHERE
    ms.created_at >= '2021-01-15'
    AND ms.created_at <= '2022-10-15'
    AND ms.completed_at IS NOT NULL
    AND mutc.user_id = 44
    AND mutc.training_id = 4
GROUP BY DATE(ms.created_at)
ORDER BY DATE(ms.created_at)

方案选择建议 #

• 如果只做最小改动快速修复，选方案1
• 如果后续需要动态匹配多组user_id和training_id，且表有对应联合索引，选方案2
• 如果追求最优性能，选方案3，同时建议给关联字段加上联合索引进一步提速。

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