MySQL 建表與索引規範 18 條鐵律：從命名到優化的完整指南

規範的建表與索引設計，是高性能、可維護數據庫的基石。本文匯總 18 條核心規範，涵蓋字段定義、索引策略、命名規範、優化驗證全生命週期。

一、建表字段規範（1-4）

1. 非空約束 + 默認值：所有字段定義為 NOT NULL，並顯式指定 DEFAULT 值
2. 業務必填字段：NOT NULL 無默認值，應用層保證傳值
3. 外鍵關聯字段必建索引：FOREIGN KEY 列或業務邏輯關聯列，必須建立索引
4. 跨表同義字段類型完全一致：類型、長度、非空、默認值全維度一致，否則關聯查詢無法使用索引

二、索引使用鐵律（5-14）

5. 單查單索引：一條 SQL 只能使用一個表的一個索引（Index Merge 除外，不建議依賴）
6. 最優索引選擇：優化器傾向選擇過濾性最強（區分度最高、返回行數最少）的索引
7. 聯合索引 = 多個前綴索引：INDEX(a,b,c) 等價於 INDEX(a)、INDEX(a,b)、INDEX(a,b,c) 三個索引
8. LIKE 索引生效條件：LIKE 'prefix%' 走索引，LIKE '%suffix' / LIKE '%mid%' 全表掃描
9. COUNT(*) vs COUNT(col)：無 WHERE 時 COUNT(*) 較慢（掃描聚簇索引），有 WHERE 時 COUNT(*) 更快
10. AND 條件需同一聯合索引：多個 AND 條件列必須屬於同一聯合索引，且包含最左列
11. OR 條件每列皆需索引：WHERE a=1 OR b=2 中 a、b 都必須有索引，否則全表掃描
12. ORDER BY 列需同一索引且序一致：ORDER BY a ASC, b DESC 需 INDEX(a ASC, b DESC)（MySQL 8.0+ 支持降序索引）
13. GROUP BY 列需同一索引且有序：所有 GROUP BY 列來自同一索引，且索引按順序存儲鍵值
14. JOIN 關聯列雙邊建索引：ON/WHERE 涉及的關聯列，驅動表與被驅動表都需建立合適索引

三、優化干預與維護（15-18）

15. FORCE INDEX 干預：全表掃描場景經驗證索引更優時，使用 FORCE INDEX(idx_name) 強制
16. 定期 ANALYZE TABLE：更新索引統計信息，修正優化器誤判
17. 慢查詢 + EXPLAIN 閉環：開啟慢查詢日志，定期分析 Top SQL 執行計劃
18. 關鍵緩衝區參數：
    • key_buffer_size（全局，MyISAM 索引緩衝）
    • query_cache_size（全局，謹慎啟用）
    • sort_buffer_size（會話級，建議 ≤4MB）

四、命名規範

• 全小寫、下劃線分隔、長度 ≤30 字符
• 過長時按表名縮寫規則縮寫

五、EXPLAIN 實戰解讀速查表

六、經典 FAQ：為什麼有索引卻不走？

-- 表 10萬行，created_at 有索引
SELECT * FROM t WHERE created_at < '2010-01-20';  -- 命中 6萬行
EXPLAIN: type=ALL, key=NULL

-- 改為範圍查詢
SELECT * FROM t WHERE created_at BETWEEN '2009-12-06' AND '2010-01-20';  -- 命中幾百行
EXPLAIN: type=range, key=created_at

核心準則：索引目的是縮小結果集。當預估掃描行數超過表總行數 20-30%，優化器判定全表掃描更優，放棄索引。類似 gender 這類低區分度列建索引無意義。

落地 18 條鐵律，建立 Code Review 機制，讓規範成為團隊肌肉記憶。