MySQL 優化非一日之功，而是隨業務成長持續演進的系統工程。資深運維專家總結五大階段優化體系，從表設計到架構擴展再到日常維護，涵蓋參數調優、核心調度、硬體選型、主從讀寫分離、分庫分表分區、緩存引入、備份恢復與監控指標，為中小型企業提供可落地的完整優化路線圖。

階段一：資料庫表設計 (開發主導)

• 依業務建模，避免過度正規化導致大量 JOIN
• 選擇合適資料型別 (優先 INT、VARCHAR、避免 TEXT/BLOB 混存)
• 核心查詢路徑預先建立覆蓋索引，避免回表
• 預留分表鍵 (如 user_id % 100)，為未來水平拆分鋪路
• 壓測驗證：模擬生產數據量與並發，提前暴露慢查詢、鎖衝突

階段二：資料庫部署 (運維主導)

• 單機支撐 ~1500 QPS，採 主從 + Keepalived 雙機熱備保高可用
• 檔案系統：XFS + noatime,nodiratime 掛載，減少元數據寫入
• 磁碟：RAID10 (SAS/SSD)，獨立 datadir、tmpdir、binlog 分區
• OS 參數：ulimit -n 65535、tcp_tw_reuse=1、swappiness=0

階段三：效能調優三層次

3.1 MySQL 核心參數 (InnoDB 為主)

[mysqld]
innodb_buffer_pool_size = 物理記憶體 60-70%
innodb_buffer_pool_instances = 4~8
innodb_flush_log_at_trx_commit = 2  (非強一致業務)
innodb_file_per_table = ON
innodb_log_file_size = 1G
innodb_log_buffer_size = 16M
max_connections = 3000
query_cache_type = OFF  (高並發寫入場景建議關閉)

3.2 核心調度優化

• I/O 排程器：deadline 或 noop (SSD)
• 關閉透明大頁 (THP)：echo never > /sys/kernel/mm/transparent_hugepage/enabled

3.3 硬體升級

• 記憶體越大 → Buffer Pool 越大 → 熱資料全記憶體
• NVMe SSD 替代 SAS，IOPS 提升 10 倍

階段四：架構擴展 (突破單機極限)

4.1 主從複制 + 讀寫分離

• 一主多從，主庫寫、從庫讀
• 代碼層路由 (推薦) 或中間件 (MyCat、ProxySQL、MaxScale)
• GTID + 半同步複制 保證資料零損失

4.2 分散式緩存

• Redis Cluster / Memcached 緩存熱點資料，QPS 萬級輕鬆支撐
• 緩存穿透/擊穿/雪崩對策：布隆過濾器、互斥鎖重建、隨機過期時間

4.3 分庫分表分區

策略	適用場景	實現複雜度
垂直分庫	業務模組解耦 (web/bbs/blog)	低
垂直分表	大字段/冷熱分離	中
水平分表	單表 > 5000萬/1億行	高 (需中間件或應用改造)
分區表	時間序列/範圍查詢為主	低 (透明於應用)

階段五：日常維護與監控 (DBA 核心職責)

5.1 關鍵指標監控

QPS = Questions / Uptime
TPS = (Com_commit + Com_rollback) / Uptime
-- 亦可用 Com_select/insert/delete/update 間差值計算

5.2 慢查詢分析

-- 開啟慢查詢
SET GLOBAL slow_query_log=ON;
SET GLOBAL long_query_time=1;
SET GLOBAL log_queries_not_using_indexes=ON;

-- 分析工具
mysqldumpslow -t 10 /var/log/mysql/slow.log
pt-query-digest /var/log/mysql/slow.log  -- 功能更全

5.3 備份策略

• 全量 + 增量：XtraBackup (Percona) 物理熱備，支援增量、流式、壓縮、並行
• 邏輯備份：mysqldump --single-transaction --master-data=2 僅適合小庫
• 定期演練恢復，驗證備份可用性

5.4 表損壞修復

• MyISAM: myisamchk -r -q (需停庫)
• InnoDB/MyISAM: mysqlcheck -r -q (線上可用)

附：101 條優化金句精選

1. 足夠記憶體裝下整個 InnoDB 資料集
2. 極力避免 Swap，必要時 swappiness=0
3. RAID10 > RAID5，電池備援 RAID 卡
4. innodb_flush_method=O_DIRECT 避免雙重緩衝
5. innodb_buffer_pool_size 覆蓋熱資料
6. max_connections 設 80% 上限，thread_cache_size=16
7. 查詢緩存 (QC) 僅適合「讀多寫少、資料少變」場景
8. 慢查詢日誌 + EXPLAIN + 基準測試 = 優化鐵三角
9. 覆蓋索引、避免 SELECT *、ORDER BY RAND()、大 OFFSET
10. 備份從從庫、停止複制、備份二進制日誌、定期驗證恢復

優化無終點，唯有持續觀測、分析、調整，才能讓 MySQL 在業務快速增長中穩健前行。