递归CTE+两次左连接致MySQL查询过慢，如何优化？

原始业务查询SQL #

SELECT
    t.id,
    t.name,
    t.email,
    t.parentId,
    SUM(d.volume) AS total_volume
FROM (
    WITH RECURSIVE users_path (id, parentId, name, email, path) AS (
        SELECT id, parentib AS parentId, name, email, name AS path
        FROM users
        WHERE parentib = 72
        UNION ALL
        SELECT c.id, c.parentib, c.name, c.email, CONCAT(cp.path, ' > ', c.name)
        FROM users_path AS cp
        JOIN users AS c ON cp.id = c.parentib
    )
    SELECT *
    FROM users_path
    ORDER BY path
) AS t
LEFT JOIN account a ON t.id = a.userid AND a.isdemo = 0 AND a.active = 1 AND a.disabled = 0
LEFT JOIN live5.deals d ON a.number = d.login AND (d.action = 0 OR d.action = 1) AND d.entry = 1 AND d.time >= "2023-10-01" AND d.time <= "2023-10-24"
GROUP BY t.id, t.name, t.parentId
ORDER BY t.path;

表数据量与关联关系 #

表数据量 #

• Users表：31000条记录
• Account表：36000条记录
• Deals表：5854000条记录

关联关系 #

• Users表id字段与Account表userid字段关联，两表基于该字段左连接
• Account表number字段与Deals表login字段关联，基于该字段二次左连接

当前问题 #

该查询执行耗时达1分钟，即使仅查询1个月数据，速度依然极慢，请求提供优化方案。

执行计划关键信息 #

执行计划显示核心瓶颈点：

1. Deals表执行全表扫描（585万条数据的全扫是主要性能消耗点）
2. 递归CTE生成的users_path提前执行了排序，增加不必要开销
3. 多表关联后才进行聚合计算，数据处理量过大

优化方案 #

1. 给Deals表创建复合覆盖索引 #

针对Deals表的过滤、关联和聚合需求，创建复合索引直接覆盖所需字段，避免全表扫描：

-- PostgreSQL支持INCLUDE语法，仅索引过滤字段，附加聚合字段
CREATE INDEX idx_deals_login_action_entry_time_volume ON live5.deals(login, action, entry, time) INCLUDE (volume);

-- MySQL不支持INCLUDE，直接把聚合字段加入索引
CREATE INDEX idx_deals_login_action_entry_time_volume ON live5.deals(login, action, entry, time, volume);

2. 提前聚合Deals表数据 #

先对Deals表按login分组聚合，减少后续关联的数据量：

SELECT
    t.id,
    t.name,
    t.email,
    t.parentId,
    COALESCE(d.total_volume, 0) AS total_volume
FROM (
    WITH RECURSIVE users_path (id, parentId, name, email, path) AS (
        SELECT id, parentib AS parentId, name, email, name AS path
        FROM users
        WHERE parentib = 72
        UNION ALL
        SELECT c.id, c.parentib, c.name, c.email, CONCAT(cp.path, ' > ', c.name)
        FROM users_path AS cp
        JOIN users AS c ON cp.id = c.parentib
    )
    SELECT *
    FROM users_path
) AS t
LEFT JOIN account a ON t.id = a.userid AND a.isdemo = 0 AND a.active = 1 AND a.disabled = 0
LEFT JOIN (
    SELECT login, SUM(volume) AS total_volume
    FROM live5.deals
    WHERE (action = 0 OR action = 1) 
      AND entry = 1 
      AND time >= "2023-10-01" 
      AND time <= "2023-10-24"
    GROUP BY login
) d ON a.number = d.login
GROUP BY t.id, t.name, t.parentId, t.path
ORDER BY t.path;

3. 移除CTE内的提前排序 #

原CTE中对users_path提前排序属于冗余操作，将排序逻辑移到最终查询的ORDER BY即可：
将CTE内的SELECT * FROM users_path ORDER BY path改为SELECT * FROM users_path，保留最终查询的ORDER BY t.path。

4. 给Account表创建复合索引 #

针对Account表的过滤和关联需求，创建复合索引快速定位有效数据：

-- PostgreSQL版本
CREATE INDEX idx_account_userid_status ON account(userid, isdemo, active, disabled) INCLUDE (number);

-- MySQL版本
CREATE INDEX idx_account_userid_status ON account(userid, isdemo, active, disabled, number);

5. 优化Users表递归查询的索引 #

给Users表的parentib字段创建索引，提升递归CTE的节点查找效率：

CREATE INDEX idx_users_parentib ON users(parentib, id, name, email);

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