某業務需統計用戶發帖數、群組歸屬、郵箱綁定等多維度信息，原始 SQL 如下：
SELECT u.*, COUNT(u.id) AS sum
FROM `user` AS u
LEFT JOIN `post` AS p ON p.user_id = u.id
RIGHT JOIN `user_has_group` AS up ON up.user_id = u.id
RIGHT JOIN `user_has_email` AS ue ON ue.user_id = u.id
WHERE u.username != ''
AND up.group_id = {$args['groupid']}
AND ue.email = '{$args['useremail']}'
GROUP BY u.id
ORDER BY u.id DESC;| 子句 | 當前寫法 | 語義 | 風險 |
|---|---|---|---|
| FROM user u | 左表 | 以用戶表為基準 | - |
| LEFT JOIN post p | 左連接 | 保留所有用戶，無帖子則 p.* 為 NULL | 若用戶帖子多，會產生笛卡爾積膨脹行數 |
| RIGHT JOIN user_has_group up | 右連接 | 以 up 為基準，無匹配用戶則 u.* 為 NULL | RIGHT JOIN 將前面的結果集（u+p）作為右表，語義極易誤讀 |
| RIGHT JOIN user_has_email ue | 右連接 | 同前，以 ue 為基準 | 同前，連接順序與語義反直覺 |
| WHERE 條件 | 過濾 | 包含關聯表字段條件 | WHERE 中引用 RIGHT JOIN 右表字段會隱式轉為 INNER JOIN，導致外連接失效 |
| GROUP BY u.id | 分組 | 按用戶聚合 | SELECT u.* 非聚合列在 ONLY_FULL_GROUP_BY 模式下報錯 |
up.group_id = ... 和 ue.email = ... 出現在 WHERE 中，會強制要求這兩張表必須有匹配行，等價於 INNER JOIN。原本的 LEFT/RIGHT JOIN 語義被破壞。
修正：將關聯表的過濾條件移至 ON 子句：
LEFT JOIN `user_has_group` AS up ON up.user_id = u.id AND up.group_id = ?
LEFT JOIN `user_has_email` AS ue ON ue.user_id = u.id AND ue.email = ?SELECT
u.id,
u.username,
u.email,
COUNT(DISTINCT p.id) AS post_count,
COUNT(DISTINCT up.id) AS group_count,
COUNT(DISTINCT ue.id) AS email_count
FROM `user` AS u
LEFT JOIN `post` AS p ON p.user_id = u.id
LEFT JOIN `user_has_group` AS up ON up.user_id = u.id AND up.group_id = ?
LEFT JOIN `user_has_email` AS ue ON ue.user_id = u.id AND ue.email = ?
WHERE u.username != ''
GROUP BY u.id, u.username, u.email
ORDER BY u.id DESC;改進點：
LEFT JOIN，以 user 為絕對主表，語義一致、易維護。COUNT(DISTINCT ...) 避免多表 JOIN 產生的笛卡爾積導致計數膨脹。ONLY_FULL_GROUP_BY，避免 5.7+ 報錯。? 防注入，替代原始字符串拼接。| 表 | 建議索引 | 理由 |
|---|---|---|
| user | PRIMARY KEY (id)、INDEX idx_username (username) | 主鍵聚簇、WHERE 篩選 |
| post | INDEX idx_user_id (user_id) | JOIN 條件 |
| user_has_group | INDEX idx_user_group (user_id, group_id) | 覆蓋 JOIN+過濾，避免回表 |
| user_has_email | INDEX idx_user_email (user_id, email) | 同前 |
type 應為 ref 或 eq_ref，避免 ALL（全表掃描）。Extra 出現 Using temporary; Using filesort 表示分組排序需優化（可考慮覆蓋索引或調大 tmp_table_size）。rows 預估行數應遠小於全表行數。若業務僅需「用戶是否有帖子/屬於某群組/綁定某郵箱」，用 EXISTS 子查詢效率更高，避免 JOIN 膨脹：
SELECT u.*
FROM `user` u
WHERE u.username != ''
AND EXISTS (SELECT 1 FROM `post` p WHERE p.user_id = u.id)
AND EXISTS (SELECT 1 FROM `user_has_group` up WHERE up.user_id = u.id AND up.group_id = ?)
AND EXISTS (SELECT 1 FROM `user_has_email` ue WHERE ue.user_id = u.id AND ue.email = ?);子查詢可被優化器轉為 Semi-Join（MySQL 5.6+），執行計劃通常優於多表 LEFT JOIN。
混用 LEFT/RIGHT JOIN 且 WHERE 引用右表字段，是 SQL 寫作中常見的「語義陷阱」。統一以核心業務表為主表、全鏈路 LEFT JOIN、過濾條件下推 ON 子句、顯式列出 SELECT 欄位、配合覆蓋索引，能獲得語義清晰、性能穩健、易於維護的查詢。複雜報表類 SQL，建議優先考慮 EXISTS 或物化視圖/預聚合表，將計算下線化，避免線上大表 JOIN 拖垮主庫。
