千萬級答題記錄表優化實戰：從 LEFT JOIN 到分場景 SQL 重構

業務背景與數據規模

• Question 表： 存儲題目，約 3 萬條。
• AnswerResult 表： 存儲用戶作答記錄，分表後單表 300-400 萬行，高頻讀寫。
• 需求： 根據用戶歷史生成練習卷，優先級：未做 > 僅錯 > 錯對皆有（按錯/對比例權重） > 僅對。

原始 SQL 及性能瓶頸

SELECT q.QuestionId,
       IFNULL((0 - correct_cnt) / (correct_cnt + wrong_cnt), 1) AS weight
FROM Question q
LEFT JOIN AnswerResult a ON a.QuestionId = q.QuestionId
WHERE a.UserId = {UserId}
ORDER BY weight;

問題：

1. IFNULL() 函數計算不可用索引。
2. LEFT JOIN 產生巨大笛卡爾積，且 AnswerResult 頻繁讀寫易觸發鎖爭用。
3. 單條 SQL 強行覆蓋所有業務邏輯，無法針對性優化。

優化策略：拆分場景、冗余權重、利用索引

1. 冗余權重字段

在 AnswerResult 增加 weight 列，由應用層或觸發器維護：weight = (0 - correct) / (correct + wrong)，區間 [-1, 0]。未做題權重視為 1。

2. 分場景執行差異化 SQL

A 場景：獲取未做題目（利用 NOT IN + 知識點縮小範圍）

SELECT QuestionId
FROM Question
WHERE KnowledgePoint = {Code}
  AND QuestionId NOT IN (
    SELECT QuestionId
    FROM AnswerResult
    INNER JOIN Question USING (QuestionId)
    WHERE UserId = {UserId} AND Question.KnowledgePoint = {Code}
  );

優化點： 子查詢先按 UserId + KnowledgePoint 過濾，大幅縮小 NOT IN 集合。

B 場景：僅做錯題目（correct_cnt = 0）

SELECT QuestionId
FROM AnswerResult
INNER JOIN Question USING (QuestionId)
WHERE UserId = {UserId}
  AND KnowledgePoint = {Code}
  AND correct_cnt = 0
ORDER BY wrong_cnt DESC;

C 場景：做過題目按權重排序（correct_cnt > 0）

SELECT QuestionId
FROM AnswerResult
INNER JOIN Question USING (QuestionId)
WHERE UserId = {UserId}
  AND KnowledgePoint = {Code}
  AND correct_cnt > 0
ORDER BY weight DESC;

遺留優化點與思考

A 場景的 NOT IN 仍有性能隱患（子查詢結果集較大時）。可嘗試：

• 改用 LEFT JOIN ... IS NULL 或 NOT EXISTS，配合覆蓋索引。
• 應用層緩存用戶已做題目 ID 集合（Redis Set），直接 WHERE QuestionId NOT IN (...) 传参。
• 將「未做」邏輯下推：每日離線任務預計算用戶各知識點未做題目表。

關於 JOIN 與冗余的取捨： JOIN 非性能殺手，關鍵在索引設計與結果集大小。高併發海量寫入場景下，冗余字段更新維護成本極高，需權衡讀寫比例、一致性要求決策。