MySQL 表設計優化與索引策略全解析

表設計核心原則

1. 範式與反範式平衡

• 3NF 基礎: 消除冗余，保證數據一致性
• 適度反範式: 高頻 JOIN 查詢中冗余非關鍵列 (如用戶名)、預計算聚合列、物化視圖
• 判斷標準: 讀寫比 > 10:1、JOIN 成本高、數據修改頻率低時考慮冗余

2. 數據類型選型黃金法則

3. 窄表設計

• 單表列數 < 30，行大小 < 8KB (InnoDB 頁面 16KB，預留更新空間)
• 大字段 (BLOB/TEXT/JSON) 垂直拆分至附屬表，1:1 關聯
• 冷熱數據分離：高頻訪問列留主表，低頻遷移擴展表

索引策略體系

1. 索引類型選擇

• B+Tree (默認): 全值匹配、範圍查詢、排序、前綴匹配
• Hash (Memory/Heap): 精確等值查詢 O(1)，不支援範圍/排序
• Fulltext: 全文搜索 (MySQL 5.6+ InnoDB 支援)，中文需分詞器
• Spatial (R-Tree): GIS 地理空間查詢

2. 組合索引設計 (最左前綴原則)

-- 查詢模式: WHERE a=1 AND b=2 ORDER BY c
-- 最優索引: (a, b, c)  -- 等值列在前，排序列在後
-- 失效案例: WHERE b=2 (缺少最左列 a)
-- 失效案例: WHERE a=1 ORDER BY b (b 不在索引中)

3. 覆蓋索引與回表優化

• 查詢列全部包含在索引中 → Using index，避免回表
• 常見模式: INDEX (status, created_at, id) 覆蓋 SELECT id FROM t WHERE status=1 ORDER BY created_at LIMIT 10

4. 索引維護與監控

-- 查看索引使用統計 (需開啟 userstat 或 performance_schema)
SELECT * FROM schema_index_statistics WHERE table_schema='db' ORDER BY rows_read DESC;

-- 發現重複/冗余索引
SELECT * FROM schema_redundant_indexes WHERE table_schema='db';

-- 發現未使用索引
SELECT * FROM schema_unused_indexes WHERE table_schema='db';

避坑清單

1. ❌ 過度索引 (單表 > 5 個組合索引) → 寫入放大、優化器選擇困難
2. ❌ 低選擇性列建索引 (性別/狀態/布爾值) → 優化器傾向全表掃描
3. ❌ 隱式類型轉換 WHERE phone=13800000000 (phone 是 VARCHAR) → 索引失效
4. ❌ 函數/運算作用於索引列 WHERE DATE(created_at)='2024-01-01' → 改為範圍查詢
5. ❌ LIKE '%keyword%' 導致全掃 → 考慮 Fulltext 或 Elasticsearch

工具輔助決策

• EXPLAIN / EXPLAIN ANALYZE (8.0.18+)
• pt-index-usage 分析慢日誌索引使用情況
• pt-duplicate-key-checker 檢測重複索引
• sys.schema_index_statistics 系統視圖監控