MySQL 建表與索引使用規範詳解：從命名規範到效能優化

MySQL 建表與索引使用規範詳解

良好的資料庫設計規範是系統穩定、高效運行的基石。本文整理了 18 條核心建表與索引規範，涵蓋欄位設計、索引策略、命名規範及 EXPLAIN 執行計劃分析，並附贈實戰 FAQ 案例分析。

一、核心建表與索引 18 條軍規

1. 非空預設：所有欄位必須設為 NOT NULL 並指定預設值，避免 NULL 導致的索引失效與統計偏差。
2. 必要時可空：業務確需 NULL 時，仍須給明確預設值（如空字串），不可留 NULL。
3. 外鍵必索引：關聯欄位（等價外鍵）必須建立索引，否則 JOIN 效能極差。
4. 跨表一致性：關聯欄位的類型、長度、非空屬性、預設值必須完全一致，否則無法使用索引關聯。
5. 單表單索引：單條 SQL 僅能使用單表的一個索引；多欄索引最多 15 個欄位。
6. 優選高選擇性：多索引可選時，MySQL 傾向使用「掃描行數最少」且「唯一值密度最高」的索引。
7. 最左前綴原則：複合索引 (a,b,c) 等同於建立 (a)、(a,b)、(a,b,c) 三個索引，查詢需遵循最左前綴匹配。
8. LIKE 索引條件：LIKE 'abc%' 可用索引，LIKE '%abc' 或 LIKE '%abc%' 會全表掃描。
9. COUNT 效能：無 WHERE 時 COUNT(*) 比 COUNT(col) 快（MyISAM 直接讀元資料）；有 WHERE 時 COUNT(*) 更快。
10. AND 條件索引：多個 AND 條件需屬於同一複合索引且包含第一欄；若為單列索引，僅用選擇性最高者。
11. OR 條件索引：每個 OR 條件都必須有可用索引，否則全表掃描。
12. ORDER BY 索引序：排序欄位需屬於同一索引，且排序方向一致（全 ASC 或全 DESC）。
13. GROUP BY 索引序：分組欄位需引用同一索引且索引按序儲存鍵值。
14. JOIN 索引覆蓋：ON 和 WHERE 中的關聯/過濾欄位均需建立合適索引。
15. 強制索引：全表掃描效率低時，可用 FORCE INDEX 強制走索引。
16. 定期 ANALYZE：執行 ANALYZE TABLE 更新關鍵字分佈統計資訊。
17. 慢查詢分析：定期檢查 Slow Log，配合 EXPLAIN 發現優化空間。
18. 關鍵參數調優：key_buffer_size、query_cache_size（全域）；sort_buffer_size（Session，建議 ≤4MB）。

二、命名規範標準

命名統一使用小寫英文單詞，過長時依表名縮寫規則縮寫。

三、索引相關常用語法

-- 建立索引
CREATE INDEX ind_log_url ON logaudit_log(url);

-- 查看索引
SHOW INDEX FROM logaudit_log;

-- 刪除索引
DROP INDEX log_request_time ON logaudit_log;