MySQL 5.7含关联子查询的查询聚合超时问题求助

问题背景 #

初始查询耗时约9秒，返回25万条记录，但尝试按年月分组统计UID数量时，两次聚合尝试均超时（约6分钟）。核心原因是原查询中的关联子查询会为每条UID重复执行，且受限于MySQL 5.7无法使用CTE，需要通过改写查询逻辑解决。

优化后的查询语句 #

SELECT 
    COUNT(vs.uid) AS uid_count,
    DATE_FORMAT(si_first.first_sales_activity, '%Y-%m') AS year_month
FROM (
    -- 保留原逻辑筛选有效卖家UID
    SELECT 
        u.uid
    FROM 
        user u
    WHERE 
        u.uid IN (SELECT DISTINCT si.uid FROM sell_item si)
        AND u.since < '2023-04-24'
        AND u.can_sell = 'Y'
        AND u.last_alive_time >= '2023-01-01'
) vs
-- 预计算所有符合条件的UID的首次销售活动时间，避免重复子查询
JOIN (
    SELECT 
        si.uid,
        MIN(si.created) AS first_sales_activity
    FROM 
        sell_item si
    WHERE 
        si.status IN ('For Sale', 'Sold')
        AND si.created BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY 
        si.uid
) si_first ON vs.uid = si_first.uid
GROUP BY 
    year_month;

优化逻辑说明 #

1. 避免重复子查询：原查询中每个UID都会触发一次MIN(si.created)的子查询，25万条记录会执行25万次单表查询。优化后通过独立的GROUP BY子查询，一次性计算所有符合条件的UID的首次销售时间，仅执行一次聚合操作。
2. 提前过滤数据：将原子查询中HAVING的时间过滤移至WHERE子句，提前缩小sell_item的查询范围，减少后续聚合计算的数据量。
3. 简化嵌套层级：去掉原查询中不必要的t1嵌套子查询，直接使用SELECT DISTINCT si.uid FROM sell_item si，降低查询解析复杂度。

索引优化建议（进一步提速） #

为避免全表扫描，建议添加以下复合索引：

• 给sell_item表创建：CREATE INDEX idx_uid_status_created ON sell_item(uid, status, created);
  该索引可直接用于快速计算每个UID的最小created值，无需扫描全表。
• 给user表创建：CREATE INDEX idx_user_valid_sellers ON user(since, can_sell, last_alive_time, uid);
  加速有效卖家UID的筛选过程。

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