一、資料庫設計層面
1. 盡量縮小資料寬度
- 優先選用較小型別：
TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT
CHAR 固定長度優於 VARCHAR（頻繁更新表減少碎片），但需權衡空間- 定義
NOT NULL（除非業務真需 NULL），節省位圖空間、優化查詢
- 所有欄位設定
DEFAULT 值
2. 索引策略
- 主鍵越短越好（InnoDB 聚簇索引，次要索引葉子節點存主鍵值）
- 只建立必要索引：利於讀、害於寫
- 複合索引遵循最左前綴原則，高選擇性欄位靠左
- 覆蓋索引避免回表
- MyISAM 大量導入：
ALTER TABLE ... DISABLE KEYS → 導入 → ENABLE KEYS
- InnoDB 大量導入：
SET autocommit=0 → 批次提交 → SET autocommit=1
3. 表結構優化
- 大表拆分：垂直拆分（冷熱欄位分離）、水平分表（按時間/ID 範圍）
- MyISAM 可用
MERGE 引擎邏輯合併；InnoDB 5.6+ 支援 PARTITION
- 避免不必要的外鍵（應用層維護完整性，減少鎖開銷）
- 資料入庫前預處理（清洗、轉碼、格式化），減少資料庫 CPU
4. 日期時間型別選擇
- 頻繁比較/篩選的日期：考慮
INT UNSIGNED 存 Unix Timestamp（比較快、跨時區靈活）
- 需顯示完整日期時間：
DATETIME（5.6.4+ 微秒支援）或 TIMESTAMP（1970-2038、時區感知）
二、SQL 與使用層面
1. 連線管理
- 用完即關，避免長連線佔用資源
- 連線池配置：
minIdle、maxActive、testOnBorrow、validationQuery
2. 慢查詢分析
- 開啟
slow_query_log、long_query_time=1（或 0.5）
EXPLAIN 分析執行計劃：關注 type、key、rows、Extra- 關聯表比較欄位：型別、長度、字元集必須一致，否則隱式轉換導致索引失效
3. LIMIT 優化
LIMIT 1000000, 10 極慢（掃描 100 萬行再丟棄）- 優化：
WHERE id > 1000000 LIMIT 10（利用主鍵索引直接定位）
- 配合
ORDER BY 或 DISTINCT 避免全表掃描
4. 清空表資料
TRUNCATE TABLE tbl_name; -- 秒級、DDL、不可回滾、重置自增值
DELETE FROM tbl_name; -- 慢、DML、可回滾、不重置自增值
5. 批次操作
- 多值
INSERT：INSERT INTO tbl VALUES (...),(...),(...);（單條網路往返）
LOAD DATA INFILE 導入大量資料（最快，繞過 SQL 層解析）
6. 存儲過程/函數
- 減少網路往返、封裝業務邏輯、伺服器端執行
- 注意：過度使用增加維護難度、除錯困難、版本遷移風險
7. 定期整理碎片
OPTIMIZE TABLE tbl_name; -- MyISAM/InnoDB/BDB，實際為重建表
ANALYZE TABLE tbl_name; -- 更新統計資訊，優化器受益
三、系統瓶頸識別與解決
| 瓶頸類型 | 特徵 | 解決方向 |
|---|
| 磁碟搜尋 | 隨機 I/O 高、iowait 高 | RAID 0/10、SSD/NVMe、多磁碟分離（資料/日誌/暫存） |
| 磁碟讀寫 | 吞吐飽和 | 增加記憶體（緩衝池）、壓縮、分庫分表、讀寫分離 |
| CPU 週期 | CPU 100%、複雜運算/排序 | 優化 SQL、減少函數計算、升級 CPU、增加核心 |
| 記憶體頻寬 | 大量資料在記憶體間搬運 | NUMA 感知綁核、減少不必要的複製、升級記憶體頻率 |
四、實戰檢查清單
- ☐
slow_query_log=ON、long_query_time=1
- ☐ 所有表有主鍵、InnoDB 引擎
- ☐ 核心查詢有
EXPLAIN 驗證、無 type=ALL
- ☐ 組合索引符合最左前綴、覆蓋索引應用
- ☐
innodb_buffer_pool_size ≥ 熱點資料大小、≈ 記憶體 70%
- ☐
innodb_flush_log_at_trx_commit=1（金融）或 2（高寫入）
- ☐
autocommit 批次提交模式應用
- ☐ 連線池大小匹配
max_connections、啟用驗證
- ☐ 定期
ANALYZE/OPTIMIZE TABLE、監控碎片率
- ☐ 備份驗證：每季至少一次完整還原演練
- ☐ 監控告警：QPS、延遲 P99、慢查詢數、連線數、主從延遲、磁碟空間
五、避坑指南
- ❌
SELECT * → ✅ 明確列出欄位
- ❌
WHERE col = 'value' 欄位型別不匹配（字串比數字） → ✅ 統一型別
- ❌
LIKE '%keyword%' 導致全表掃描 → ✅ 全文索引/ES/前綴索引
- ❌ 隱式轉換（
WHERE int_col = '123'） → ✅ 應用層型別一致
- ❌ 大事務長時間持有鎖 → ✅ 拆分小事務、縮短持鎖時間
- ❌ 未監控
Aborted_clients、Aborted_connects → ✅ 納入告警
