使用MySQL递归CTE获取指定用户的所有关联用户（基于手机号）

问题背景 #

需通过手机号（ad_phones_id）获取指定用户ID（ad_users_id）的所有直接及间接关联用户，依赖表ad_phones_users_taxonomy（ad_users_id和ad_phones_id为外键）。原递归CTE查询在处理多手机号用户（如user_id 7177）时性能极差，甚至引发服务器负载过高，原查询语句如下：

WITH RECURSIVE connections (table_id, user_id, phone_id)
AS (
  SELECT
    CAST(ut.id AS CHAR(200)) AS table_id,
    ut.ad_users_id AS user_id,
    ut.ad_phones_id AS phone_id
  FROM ad_phones_users_taxonomy ut
  WHERE ut.ad_users_id = 7177  -- Starting user
  UNION ALL
  SELECT
    CONCAT(c.table_id, ',', CAST(ut2.id AS CHAR(200))) AS table_id, -- CONCATENARE pentru a evita din bucla id-urile deja trase
    ut2.ad_users_id AS user_id,
    ut2.ad_phones_id AS phone_id
  FROM ad_phones_users_taxonomy ut2
  INNER JOIN connections c ON ut2.ad_phones_id = c.phone_id OR ut2.ad_users_id = c.user_id
    WHERE NOT FIND_IN_SET(ut2.id, c.table_id)
        LIMIT 10
)
SELECT
  table_id, user_id, phone_id
    FROM connections

原查询核心问题 #

• 低效重复判断：通过CONCAT拼接表ID、FIND_IN_SET判断重复的字符串操作，递归深度增加时性能急剧下降，多手机号用户会触发大量无效判断。
• JOIN条件无索引可用：OR逻辑的关联条件会导致MySQL无法有效利用索引，强制全表扫描，放大性能损耗。
• 递归中断风险：递归部分添加LIMIT会提前终止递归，无法获取完整的关联用户链。

优化方案 #

1. 重构递归逻辑，拆分关联路径 #

分别追踪已访问的用户ID和手机号，避免用表ID拼接的方式判断重复，精准控制递归范围：

WITH RECURSIVE cte AS (
    -- 初始步骤：获取起始用户的所有关联手机号
    SELECT 
        ad_users_id AS current_user,
        ad_phones_id AS current_phone,
        CAST(ad_users_id AS CHAR(200)) AS visited_users,
        CAST(ad_phones_id AS CHAR(200)) AS visited_phones
    FROM ad_phones_users_taxonomy
    WHERE ad_users_id = 7177  -- 替换为目标用户ID
    
    UNION ALL
    
    -- 递归分支1：通过当前手机号关联其他用户
    SELECT 
        ut.ad_users_id,
        ut.ad_phones_id,
        CONCAT(cte.visited_users, ',', ut.ad_users_id),
        CONCAT(cte.visited_phones, ',', ut.ad_phones_id)
    FROM ad_phones_users_taxonomy ut
    JOIN cte ON ut.ad_phones_id = cte.current_phone
    WHERE NOT FIND_IN_SET(ut.ad_users_id, cte.visited_users)
    
    UNION ALL
    
    -- 递归分支2：通过当前用户关联其他手机号
    SELECT 
        ut.ad_users_id,
        ut.ad_phones_id,
        CONCAT(cte.visited_users, ',', ut.ad_users_id),
        CONCAT(cte.visited_phones, ',', ut.ad_phones_id)
    FROM ad_phones_users_taxonomy ut
    JOIN cte ON ut.ad_users_id = cte.current_user
    WHERE NOT FIND_IN_SET(ut.ad_phones_id, cte.visited_phones)
)
-- 去重后输出所有关联记录
SELECT DISTINCT current_user AS user_id, current_phone AS phone_id
FROM cte;

2. 添加复合索引加速查询 #

针对查询的关联方向创建复合索引，让MySQL能快速定位匹配数据：

-- 用户→手机号的查询索引
CREATE INDEX idx_user_phone ON ad_phones_users_taxonomy(ad_users_id, ad_phones_id);
-- 手机号→用户的查询索引
CREATE INDEX idx_phone_user ON ad_phones_users_taxonomy(ad_phones_id, ad_users_id);

3. 大数据量场景：用临时表追踪已访问项 #

若数据量极大，字符串集合的判断方式仍有性能瓶颈，可改用临时表记录已访问的用户和手机号，利用索引快速去重：

-- 创建临时表存储已访问用户
CREATE TEMPORARY TABLE visited_users (user_id INT PRIMARY KEY);
-- 创建临时表存储已访问手机号
CREATE TEMPORARY TABLE visited_phones (phone_id INT PRIMARY KEY);

-- 初始化起始用户及关联手机号
INSERT INTO visited_users SELECT ad_users_id FROM ad_phones_users_taxonomy WHERE ad_users_id = 7177;
INSERT INTO visited_phones SELECT ad_phones_id FROM ad_phones_users_taxonomy WHERE ad_users_id = 7177;

-- 循环获取关联数据，直到无新记录加入
REPEAT
    -- 通过手机号抓取新关联用户
    INSERT INTO visited_users
    SELECT DISTINCT ut.ad_users_id
    FROM ad_phones_users_taxonomy ut
    JOIN visited_phones vp ON ut.ad_phones_id = vp.phone_id
    WHERE ut.ad_users_id NOT IN (SELECT user_id FROM visited_users);
    
    -- 通过用户抓取新关联手机号
    INSERT INTO visited_phones
    SELECT DISTINCT ut.ad_phones_id
    FROM ad_phones_users_taxonomy ut
    JOIN visited_users vu ON ut.ad_users_id = vu.user_id
    WHERE ut.ad_phones_id NOT IN (SELECT phone_id FROM visited_phones);
UNTIL ROW_COUNT() = 0 END REPEAT;

-- 查询最终所有关联记录
SELECT ut.ad_users_id, ut.ad_phones_id
FROM ad_phones_users_taxonomy ut
JOIN visited_users vu ON ut.ad_users_id = vu.user_id
UNION
SELECT ut.ad_users_id, ut.ad_phones_id
FROM ad_phones_users_taxonomy ut
JOIN visited_phones vp ON ut.ad_phones_id = vp.phone_id;

-- 清理临时表
DROP TEMPORARY TABLE visited_users;
DROP TEMPORARY TABLE visited_phones;

关键优化说明 #

• 拆分递归的两个关联方向，避免OR条件导致的全表扫描，让索引能充分发挥作用。
• 针对用户/手机号ID判断重复，比表ID拼接的方式更精准，减少无效递归次数。
• 大数据量下用临时表替代字符串集合，利用主键索引快速判断是否已访问，进一步降低性能损耗。

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