MySQL 三表關聯查詢優化：排除好友與請求列表的用戶篩選

表結構與需求

-- 表1：用戶資料
pre_common_member_profile (uid, gender, birthyear, birthmonth, birthday)

-- 表2：好友關係
pre_home_friend (uid, fuid)  -- uid 的好友為 fuid

-- 表3：好友請求
pre_home_friend_request (fuid, uid)  -- fuid 請求加 uid 為好友

需求：查詢 uid=570 的用戶資料，排除其好友 (pre_home_friend.fuid) 和向其發送請求的用戶 (pre_home_friend_request.uid where fuid=570)。

優化前 SQL（原問題）

SELECT gender, uid, birthmonth, birthday, birthyear
FROM pre_common_member_profile AS p
WHERE p.uid <> 570
  AND p.uid < 570
  AND gender = 0
  AND uid NOT IN (
      SELECT fuid FROM pre_home_friend WHERE uid = 570
      UNION
      SELECT uid FROM pre_home_friend_request WHERE fuid = 570
  )
ORDER BY uid DESC;

優化分析與改進

問題 1：NOT IN + 子查詢性能差

• 子查詢返回 NULL 會導致 NOT IN 結果為空（三值邏輯）。
• MySQL 5.6+ 可優化為 NOT EXISTS 或 LEFT JOIN ... IS NULL。

問題 2：UNION 去重開銷

• UNION 隱含 DISTINCT，若確定兩表無重疊或允許重複，用 UNION ALL 避免排序去重。

優化版 SQL

SELECT p.gender, p.uid, p.birthmonth, p.birthday, p.birthyear
FROM pre_common_member_profile p
LEFT JOIN (
    SELECT fuid AS exclude_uid FROM pre_home_friend WHERE uid = 570
    UNION ALL
    SELECT uid AS exclude_uid FROM pre_home_friend_request WHERE fuid = 570
) excl ON p.uid = excl.exclude_uid
WHERE p.uid <> 570
  AND p.uid < 570
  AND p.gender = 0
  AND excl.exclude_uid IS NULL
ORDER BY p.uid DESC;

進一步優化：索引建議

-- 表2：好友查詢
CREATE INDEX idx_friend_uid ON pre_home_friend(uid);
-- 表3：請求查詢
CREATE INDEX idx_request_fuid ON pre_home_friend_request(fuid);
-- 表1：篩選條件
CREATE INDEX idx_profile_gender_uid ON pre_common_member_profile(gender, uid);

EXPLAIN 驗證要點

1. type 列為 ref 或 eq_ref（使用索引）。
2. Extra 無 Using temporary、Using filesort（或可接受）。
3. rows 估算值較小。

大數據量替代方案

• 應用層過濾：先查好友/請求 ID 列表（通常較小），再在應用代碼中構建 WHERE uid NOT IN (...)，避免大表子查詢。
• Redis 緩存：將用戶的好友集合、請求集合存入 Redis Set，查詢時 SDIFF 排除。
• 分表/分庫：按 uid 哈希分表，減少單表掃描量。

通過 LEFT JOIN ... IS NULL 替代 NOT IN，配合 UNION ALL 和針對性索引，可顯著提升三表排除查詢性能。