MySQL 效能優化 23 條軍規：從索引設計到架構擴展全覆蓋

MySQL 效能優化 23 條軍規：從索引設計到架構擴展全覆蓋

壓箱底優化清單，涵蓋存儲引擎選型、索引策略、SQL 改寫、架構擴展、運維細節。每條配合理由，適合作團隊編碼規範附件。

優化清單

1. 引擎選型：95% 場景用 InnoDB（事務、行鎖、崩潰恢復），唯讀/全文搜尋考慮 MyISAM
2. 拒絕全表掃描：EXPLAIN 看 type=ALL 必優化
3. 索引上車：WHERE、ORDER BY、JOIN ON 列建索引，但**索引非越多越好**（寫入開銷）
4. 拒絕 NULL：NOT NULL DEFAULT ''，NULL 難優化、佔額外位圖、統計函數行為異常
5. 字符型精準選型：定長 CHAR、變長 VARCHAR，節省空間 = 節省 I/O
6. 列越短越好：INT vs BIGINT、DATETIME vs TIMESTAMP，命名語義化
7. 避開 <>/!=：導致全掃，改用 IN/UNION/範圍查詢
8. 分區分表：億級表按時間/業務鍵分區，邏輯透明、物理隔離
9. 視圖封裝複雜查詢：簡化應用層、統一優化入口
10. 查詢緩存：query_cache_type=1（5.7 廢用，8.0 移除），高更新表慎用
11. LIKE 優化：前綴匹配 LIKE 'abc%' 走索引，%abc% 全掃，考慮全文索引/ES
12. WHERE 列不做運算/函數：WHERE YEAR(dt)=2024 → WHERE dt >= '2024-01-01' AND dt < '2025-01-01'
13. BETWEEN 優於 IN：連續範圍優先 BETWEEN，優化器更易走範圍掃描
14. SELECT 只取所需列：禁用 SELECT *，減少網絡/內存/回表開銷
15. 大表刪除用 TRUNCATE：DELETE 逐行記錄 Undo，TRUNCATE 直接釋放頁（DDL，不可回滾）
16. SQL 模板化/預處理：相同邏輯不同參數用 PreparedStatement，省解析編譯
17. 連接池：HikariCP/Druid 配置適當大小，避免頻繁建連
18. 讀寫分離/集群：Master 寫、Slave 讀，中間件（ProxySQL/ShardingSphere）路由
19. 資源用完即釋放：try-with-resources、連接/Statement/ResultSet 關閉
20. 大批量 DML 拆分：DELETE/INSERT 分批 LIMIT 1000，事務小、鎖時間短
21. 批量提交：rewriteBatchedStatements=true、executeBatch()，減少網絡往返
22. 寫入節流：循環中 usleep(1000) 讓出 CPU/IO，避免把庫打掛
23. 讀從寫主：強一致走主，最終一致走從，業務層感知路由

工具箱

• 索引分析：pt-index-usage、pt-duplicate-key-checker
• 慢查詢：pt-query-digest、mysqldumpslow
• 在線 DDL：pt-online-schema-change、gh-ost
• 備份恢復：xtrabackup、mysqlbackup
• 監控：Prometheus + mysqld_exporter、PM2

核心心法

測試驗證 > 經驗主觀，數據說話 > 感覺判斷。 每次優化前後必跑 EXPLAIN、SHOW PROFILE、壓測對比。