常見的後端資料庫結構設計與查詢優化 - Many to Many

假如用戶有很多 checkbox 勾選項目（A/B/C…），怎麼設計 DB？

在實作表單或個人化偏好設定時，常會遇到讓用戶勾選多個選項（如興趣、工作領域等）的需求。這類需求該如何設計資料庫結構？資料量大時查詢又該怎麼優化？這篇文章會從資料結構設計出發，一步步說明到查詢技巧與效能優化策略。

✅ 多選項目資料表設計：用中介表處理多對多關係

🧩 資料關聯關係

• 一位用戶可以選擇多個選項
• 一個選項也可以被多位用戶選擇

因此，最適合的設計是使用多對多關係，透過中介表紀錄每位用戶的選擇。

🗂 資料表結構如下

users 會員表

記錄用戶基本資料。

options_categories 類別表

用來分類選項，例如「興趣」、「工作領域」。

id	name
1	興趣
2	工作領域

options 選項表

儲存每個選項，並標示它屬於哪個類別。

id	category_id	label
1	1	閱讀
2	1	運動
3	1	旅行
4	2	軟體工程師
5	2	行銷
6	2	設計師

users_options 中介表

用來記錄用戶選了哪些選項。

🔍 一般的查詢某用戶的勾選項目（含分類）

SELECT u.name AS user_name, c.name AS category, o.label AS option_label
FROM users u
JOIN user_options uo ON u.id = uo.user_id
JOIN options o ON uo.option_id = o.id
JOIN option_categories c ON o.category_id = c.id
WHERE u.id = 1; -- 換成目標用戶的 ID

⚙️ 若選項可能變動，該怎麼設計？

做法	原因與說明
使用 UUID 作為主鍵	避免選項重構時產生主鍵衝突
is_active 軟刪除	避免刪除選項導致歷史資料錯誤
禁止/警告有關聯的刪除	若選項已被勾選，禁止直接刪除或改分類
支援 label 改名	不影響既有勾選資料，只變更顯示名稱

🚀 查詢效能優化：資料多時該怎麼提升速度？

當資料量增加，查詢效能成為關鍵。以下是幾個實務上的優化策略：

mindmap
  root((查詢優化))

    加 Index
      只撈需要欄位 ⚡
      快取熱門資料 🔍
      建立 Materialized View
      使用 EXPLAIN 工具

    分頁策略
      OFFSET + LIMIT（效率差）
      Keyset Pagination（推薦）
      主鍵作為 Cursor
      避免大 OFFSET

    加 Index 的原理與效益
      快速定位資料行
      支持 WHERE / JOIN / ORDER BY
      B+ Tree
        儲存與維護成本
        順序重要

🔹 分頁技巧

如果要撈 100 筆資料顯示在前端，不建議使用 OFFSET + LIMIT，效能會隨著偏移量變差。

• ✅ 推薦：Keyset Pagination（主鍵作為游標）

  • 範例如：WHERE id > ? ORDER BY id LIMIT 100
  • 效能穩定，不需跳過大量資料。
  • 實作時可用 PageToken ，優點是進行封裝以及往後後端更改也不會影響前端（因為對於前端來說就是一個字串而已）

🔹 為什麼加 Index 能加速查詢？

• 可快速定位資料，不用全表掃描。
• 對 WHERE、JOIN、ORDER BY 都有幫助。
• 常見結構如 B+ Tree，支援範圍查詢與排序。

🧠 總結

• 勾選多選項目時，使用中介表建立多對多關係是最清晰與彈性的做法。
• 設計時要考慮選項是否固定，以及日後是否會更動。
• 查詢優化不可忽略，分頁與加索引是效能關鍵。
• 工具如 EXPLAIN 可幫助分析查詢計劃，避免不必要的全表掃描。