MySQL 作為開源數據庫首選，廣泛應用於高併發業務場景。本文整理 101 條涵蓋硬件、配置、架構、查詢、備份等維度的調優建議，助力 DBA 與開發者構建高性能數據庫系統。
一、服務器硬件與作業系統優化
核心原則：內存優於磁盤，物理隔離減少爭用
- 足夠物理內存：確保 InnoDB 缓冲池可容納熱點數據，避免磁盤 I/O 成為瓶頸
- 禁用 Swap：交換分區會導致嚴重性能抖動，生產環境建議關閉
- 電池備援 RAID 卡：採用 RAID 10 平衡讀寫性能與數據安全，避免 RAID 5 校驗開銷
- OS 與數據物理隔離：分離作業系統盤、數據盤、臨時表空間、Binlog 盤，消除 I/O 競爭
- XFS 文件系統：優於 ext4，支援延遲分配、大文件高效處理，避免雙緩衝問題
- I/O 調度器選 NOOP/Deadline：SSD 環境下 NOOP 最優，機械盤用 Deadline
- 64 位作業系統：突破 4GB 內存限制，充分利用大內存優勢
- 精簡服務：移除非必要套件與守護進程，減少資源佔用
- Hosts 文件映射：避免 DNS 解析延遲
- 專用數據庫服務器：避免與 Web、緩存等服務混部
二、MySQL 核心配置參數
| 參數 | 建議值/策略 | 說明 |
|---|
| innodb_buffer_pool_size | 物理內存 60-80% | 核心緩衝區，容納熱點數據與索引 |
| innodb_log_file_size | 512MB-2GB | 過大延長恢復時間，過小增加檢查點頻率 |
| innodb_flush_log_at_trx_commit | 1（強一致性） / 0/2（高性能） | 權衡 ACID 與吞吐量 |
| innodb_flush_method | O_DIRECT | 避免雙緩衝，需配合 XFS/ext4 |
| max_connections | 200-500 | 防止連接風暴耗盡內存，配合連接池使用 |
| thread_cache_size | 16-64 | 減少線程創建銷毀開銷 |
| query_cache_type | OFF / DEMAND | 高寫入場景建議關閉，唯讀場景按需開啟 |
| sort_buffer_size | 256KB-2MB | 會話級，避免設過大導致 OOM |
| key_buffer_size | MyISAM 專用，按索引大小分配 | InnoDB 不使用此參數 |
三、架構設計與 Schema 優化
- 範式與反範式平衡：核心業務符合 3NF，報表查詢適度冗余
- 選擇最小足夠類型：優先
TINYINT、SMALLINT、VARCHAR 而非 CHAR、TEXT/BLOB 拆表存儲
- 索引策略：最左前綴原則、覆蓋索引、避免過度索引（單表 ≤6 個）
- 分區表：按時間範圍分區實現歷史數據歸檔與快速清理
- 存儲引擎選型：OLTP 用 InnoDB，日志/審計用 Archive，緩存用 Memory
- 會話數據外移：Redis/Memcached 存儲 Session，避免 MySQL 寫入放大
四、SQL 查詢優化實戰
- 慢查詢日志 + EXPLAIN：建立「發現-分析-優化」閉環
- 避免 SELECT *：只取需要列，減少網絡傳輸與緩衝池污染
- LIMIT 深分頁優化：延遲關聯或覆蓋索引掃描替代
LIMIT 100000,10
- OR 改 UNION：利用索引合併避免全表掃描
- COUNT(*) 優化：InnoDB 無 WHERE 時掃描聚簇索引，業務層面可用計數表
- ORDER BY RAND() 禁用：應用層隨機偏移量實現
- 子查詢改 JOIN：MySQL 5.6+ 優化器改進有限，顯式 JOIN 更可控
- 熱數據預熱：重啟後主動載入熱點頁面至 Buffer Pool
五、備份與高可用策略
- 主從架構下從庫備份：不阻塞主庫，使用
FLUSH LOGS 實現增量備份
- mysqldump 最佳參數：
--single-transaction --quick --master-data=2 --hex-blob
- 物理備份工具：Percona XtraBackup 支援熱備份、流式傳輸、並行壓縮
- 定期恢復演練：每季度驗證備份可用性，測試 RTO/RPO 指標
- 監控體系：Prometheus + Grafana 監控 QPS、延遲、連接數、複製延遲等核心指標
六、擴展架構演進
當單機優化觸達天花板：
- 讀寫分離：一主多從 + 負載均衡
- 分庫分表：ShardingSphere、Vitess 等中間件
- 異構同步：Kafka + Canal 實現實時數據集成
- NewSQL 選型：TiDB、OceanBase 解決分布式事務與強一致性需求
性能調優是系統工程，無銀彈方案。建議建立基準測試體系，針對業務特徵持續迭代優化。
