MySQL 表設計與索引優化八大黃金法則

法則 1：核心查詢必建索引

無索引 = 全表掃描。大表小結果集查詢無索引會導致災難性效能。但低基數欄位（如性別）建索引反而有害（過度索引）。

法則 2：複合索引遵循最左前綴

-- 查詢：WHERE area='bj' AND age=22
-- 單列索引：只能用一個
-- 複合索引：(area, age) 效能指數級提升
-- (area, age, salary) 等價於 (area,age,salary) + (area,age) + (area)

最常用過濾欄位放最左，依基數遞減排序。

法則 3：索引不包含 NULL 值

單列含 NULL 不入索引；複合索引任一欄位 NULL 導致該行不入複合索引。設計時 禁止預設 NULL，用 NOT NULL DEFAULT '' 或 0。

法則 4：使用短索引（前綴索引）

CHAR(255) 欄位若前 10-20 字元區分度高，建 INDEX(name(10))。節省空間、減少 I/O、提升快取命中率。

法則 5：ORDER BY 欄位索引策略

• MySQL 單查詢只能用一個索引
• WHERE 用了索引，ORDER BY 不會再用索引
• 避免多欄位排序，必要時建複合索引 (where_col, order_col)
• 預設排序滿足需求時不要加 ORDER BY

法則 6：LIKE 查詢索引生效條件

• LIKE 'prefix%' → 走索引
• LIKE '%suffix' / LIKE '%mid%' → 全表掃描
• 全文搜尋請用 FULLTEXT INDEX + MATCH() AGAINST()

法則 7：索引欄不可參與運算/函數

-- 錯誤：YEAR(adddate) = 2023 → 索引失效
-- 正確：adddate >= '2023-01-01' AND adddate < '2024-01-01'

任何 col + 1、UPPER(col)、DATE_FORMAT(col) 都會導致索引失效。

法則 8：拒絕 NOT IN / OR，改用替代寫法

• NOT IN → NOT EXISTS (SELECT 1 FROM ...) 或 LEFT JOIN ... WHERE b.id IS NULL
• id != 3 → id < 3 OR id > 3（仍可能全掃，視基數決定）
• OR 跨欄位通常全掃，改用 UNION ALL 分開查詢再合併

自我檢核清單

• ☐ 所有核心 WHERE/JOIN 欄位有索引
• ☐ 複合索引欄序符合最左前綴
• ☐ 無 NULL 預設值汙染索引
• ☐ 長字串欄位用前綴索引
• ☐ 無函數/運算作用於索引欄
• ☐ LIKE 僅用前綴匹配
• ☐ 移除 NOT IN/OR 反模式
• ☐ 定期 ANALYZE TABLE 更新統計資訊