前言
數據庫優化是系統工程，非單點技巧。本文總結 8 大核心策略，涵蓋索引、SQL、架構、配置、硬件，構建系統性優化知識體系。
策略一：索引優化 — 查詢性能基石
- 主鍵/外鍵必建索引：PK 自動聚簇，FK 手動建立 (避免鎖表掃描)。
- 高選擇性列優先：`COUNT(DISTINCT col)/COUNT(*)` 越接近 1 越好 (身份證 > 性別)。
- 聯合索引遵循最左前綴：`(a,b,c)` 支持 `a`、`a,b`、`a,b,c` 查詢；將**最常用作等值過濾的列放最左**。
- 覆蓋索引避免回表：`SELECT a,b FROM t WHERE c=1` → 索引 `(c,a,b)` 直接返回數據，`Extra: Using index`。
- 前綴索引權衡：長字符串 `INDEX idx_name(name(10))`，節省空間但不可用於排序/分組覆蓋。
- 拒絕過度索引：寫入密集表索引 ≤ 5 個；定期清理 `sys.schema_unused_indexes` 發現的無用索引。
策略二：SQL 語句優化 — 讓優化器無腦走索引
- 避免 `SELECT *`：只取需要列，減少 IO/網絡/內存，利於覆蓋索引。
- WHERE 條件不做運算/函數：`WHERE YEAR(create_time)=2023` → 索引失效。改 `WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'`。
- LIKE 只用後綴匹配：`LIKE 'abc%'` 走索引，`LIKE '%abc'` / `LIKE '%abc%'` 全表掃描。
- OR 改 UNION：`WHERE a=1 OR b=2` 通常全表掃描。改 `SELECT * FROM t WHERE a=1 UNION SELECT * FROM t WHERE b=2` (各自走索引)。
- IN/NOT IN 子查詢優化：`WHERE id IN (SELECT id FROM t2)` → `EXISTS` 或 `JOIN` (MySQL 5.6+ 半連接優化較好，但 JOIN 更穩)。
- 分頁大偏移優化：`LIMIT 100000, 20` → `WHERE id > last_max_id LIMIT 20` (需唯一遞增 ID)。
- 避免隱式類型轉換：`WHERE phone=13800000000` (phone 是 VARCHAR) → 全表掃描。用 `WHERE phone='13800000000'`。
策略三：架構設計優化 — 從源頭減負
- 讀寫分離：主庫寫、從庫讀，`ProxySQL`/`MaxScale`/`中間件` 路由。
- 分庫分表 (Sharding)：水平拆分 (按 `user_id%N`)、垂直拆分 (熱冷分離、業務解耦)。中間件 `ShardingSphere`/`MyCAT`。
- 緩存層 (Redis/Memcached)：熱數據 (配置、會話、計數器、排行榜) 進緩存，**緩存穿透/擊穿/雪崩** 預案必備。
- 異步處理 (MQ)：非核心寫入 (日志、通知、統計) 入隊列削峰填谷，最終一致性。
- 歸檔冷數據：歷史訂單/日志遷移至歸檔庫/ClickHouse/ES，核心庫保持精簡。
策略四：服務器參數調優 — 釋放硬件潛力
| 參數 | 核心原則 |
|---|
| innodb_buffer_pool_size | 專用機 60-80% 物理內存，**單一最重要參數** |
| innodb_log_file_size | Buffer Pool 25-50%，總大小 < 2G，平衡寫入與恢復 |
| innodb_flush_log_at_trx_commit | 主庫=1 (ACID)，從庫=2 (高性能) |
| innodb_file_per_table | =1 (獨立表空間，必開) |
| max_connections | 業務峰值 × 1.5，配合連接池 |
| query_cache_size | 5.7+ 已移除；5.6 讀多寫少可開 64-128M，寫多設 0 |
| sort/read/join_buffer_size | 會話級，**極小 (256K-1M)**，防 OOM |
| table_open_cache | ≥ 總表數 × 1.5，配合 `open_files_limit` |
策略五：硬件與存儲優化 — 物理基礎
- SSD/NVMe 必須：隨機 IOPS 碾壓 HDD，數據庫首選。
- RAID 10：性能與冗衡平衡 (RAID 5 寫入懲罰大)。
- 獨立磁盤分離：Redo Log (`innodb_log_group_home_dir`)、Binlog、數據文件、臨時目錄 (`tmpdir`) 掛載不同物理盤。
- 內存足夠大：避免 Swap，Buffer Pool 命中率 > 99%。
- 網絡萬兆/雙網卡：主從同步、備份傳輸不阻塞業務。
策略六：監控與慢查詢治理 — 可觀測性
- 開啟慢查詢日志：`slow_query_log=1`, `long_query_time=1` (秒), `log_queries_not_using_indexes=1`。
- 分析工具：`pt-query-digest` (Percona)、`mysqldumpslow`、Grafana + Prometheus (監控指標)。
- 核心指標：QPS、TPS、連接數、緩存命中率、InnoDB 行鎖等待、磁盤 IOPS/利用率、CPU/內存、主從延遲。
- 定期審計：每周分析 Top 10 慢查詢，優化或加索引。
策略七：備份與高可用 — 兜底保障
- 物理熱備 (XtraBackup)：不鎖表、速度快、支持增量、流式傳輸。
- 邏輯備份 (mydumper/mysqldump)：異構遷移、部分導出、版本升級。
- 備份驗證：**定期演練恢復**，未驗證的備份 = 無備份。
- 高可用架構：MHA / Orchestrator / Group Replication (MGR) / InnoDB Cluster 實現自動故障轉移。
策略八：安全與合規 — 防線
- 最小權限賬號：應用賬號無 `FILE`/`SUPER`/`DROP`/`GRANT`。
- SSL 加密傳輸：`require_secure_transport=ON`。
- 審計插件：`MariaDB Audit Plugin` / `MySQL Enterprise Audit` 記錄敏感操作。
- 數據脫敏：導出測試數據時脫敏手機/身份證/金額。
總結：優化閉環
監控發現慢查詢 → EXPLAIN 分析計劃 → 索引/SQL/架構優化 → 驗證效果 → 固化最佳實踐 → 持續監控
工具鏈：Prometheus+Grafana監控 → pt-query-digest慢查詢分析 → EXPLAIN計劃 → Schema/Index變更 → 壓測驗證 → 發布
原則：全鏈路視角、數據驅動、小步快跑、備份優先、安全底線
