MySQL 效能優化進階 (三)：水平/垂直分表、增量備份還原、參數調校、讀寫分離

承接前兩篇，本文深入探討 MySQL 大規模資料架構優化：分表策略、備份還原機制、核心參數調校、硬體/OS 選擇、讀寫分離架構。

一、水平分表

將單表依邏輯鍵 (時間、地域、用戶 ID) 拆分為多張結構相同的表，每表資料量控制在舒適範圍 (如 < 2000 萬列)。

關鍵：選擇正確分表鍵

核心原則：查詢模式決定分表鍵。若介面只支援「按月查詢」，按月分表查單表即可；若按地域分表但查詢需全域彙總，則需 UNION 所有表，反而比不分表慢。

查詢合併：UNION ALL

SELECT * FROM orders_202401 WHERE user_id = 123
UNION ALL
SELECT * FROM orders_202402 WHERE user_id = 123
UNION ALL
SELECT * FROM orders_202403 WHERE user_id = 123;

• 應用層封裝路由邏輯，或使用中間件 (MyCat、ShardingSphere、Vitess) 透明化。

二、垂直分表

將寬表 (大欄位如 TEXT/BLOB/長 VARCHAR) 拆分為「核心表」+「擴展表」，1:1 關聯。

案例：試題表原含 題目內容 (TEXT)、答案 (TEXT)，列表查詢只需 冊、單元、類型、難度。拆分後：

• 試題資訊：ID、冊、單元、類型、難度... (窄、常用、索引友善)
• 試題內容：ID、內容、答案 (寬、僅詳細頁讀取)

效果：列表查詢不再掃描 1GB 大表，僅掃描核心表，效能提升數十倍。

三、欄位類型選擇：保小不保大

• 主鍵：自增整數 (INT/BIGINT)，4/8 bytes，極致聯結效能。避免 UUID (36 chars)、MD5 (32 chars)、業務編碼 (變長)。
• 整數層級：TINYINT(1)/SMALLINT(2)/MEDIUMINT(3)/INT(4)/BIGINT(8)，剛好夠用即可。
• 字串：VARCHAR(N) 實際長度 + 1-2 bytes；CHAR(N) 固定長度，僅用於定長代碼 (如 MD5、ISO 代碼)。
• 金額：DECIMAL(19,4) 或 BIGINT 存分/厘 (整數運算)。

四、大檔案 (圖片/文件/影片) 離庫存儲

• 資料庫僅存 路徑/URL (VARCHAR)。
• 檔案存於分散式檔案系統 (MinIO、Ceph、HDFS)、物件儲存 (S3、OSS、COS)、或專用圖床/影片服務器。
• 優點：資料庫體積小、備份快、複寫輕、緩存命中率高。

五、核心參數調校 (InnoDB)

[mysqld]
# 記憶體相關 (假設 16GB RAM 專用 DB 伺服器)
innodb_buffer_pool_size = 12G        # 核心：資料+索引緩存，佔 RAM 70-80%
innodb_buffer_pool_instances = 8     # 減少鎖爭用
innodb_log_file_size = 1G            # Redo log 大小，減少 checkpoint 壓力
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1   # ACID 保證；可放寬為 2 (高效能、低風險)
innodb_flush_method = O_DIRECT       # 繞過 OS cache，避免雙重緩存

# 連接相關
max_connections = 1000
thread_cache_size = 100
open_files_limit = 65535

# 其他
default_storage_engine = InnoDB
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci

狀態監控：SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';、SHOW ENGINE INNODB STATUS; 觀察命中率、脏頁、鎖等待，再微調參數。

六、硬體與作業系統選擇

• 記憶體 > 4GB → 必須 64 位 OS + 64 位 MySQL 5.7+/8.0，突破 32 位 4GB 限制，充分利用大記憶體緩存。
• 儲存：NVMe SSD (高 IOPS、低延遲) 必選；RAID 10 (效能+冗餘) 或單盤 + 良好備份策略。
• CPU：高主頻 > 多核心 (MySQL 單查詢單執行緒)，但高併發受益多核心。

七、讀寫分離架構

• 主庫：處理所有寫入 (INSERT/UPDATE/DELETE/DDL)。
• 從庫 (Slave 1-N)：非同步/半同步複寫，處理讀取 (SELECT)。
• 中間件：ProxySQL、MaxScale、MyCat、ShardingSphere-Proxy，實現讀寫分離透明化、查詢路由、熔斷、審計。
• 應用層改造：資料庫連線池區分「讀連線池」與「寫連線池」，或由中間件自動路由。

八、定時備份與增量備份/還原 (二進制日誌)

完整備份 (mysqldump)

# 全庫備份
mysqldump -u root -p'password' --single-transaction --routines --triggers --events mydb > /backup/mydb_$(date +%F).sql

# 單表備份
mysqldump -u root -p'password' mydb table1 table2 > /backup/mydb_tables_$(date +%F).sql

# 壓縮備份
mysqldump ... | gzip > /backup/mydb_$(date +%F).sql.gz

增量備份：啟用二進制日誌

1. my.cnf 設定：log-bin=/var/lib/mysql/binlog/mylog → 重啟 MySQL。
2. 產生 mylog.000001 (日誌)、mylog.index (索引)。
3. 查看日誌：mysqlbinlog /var/lib/mysql/binlog/mylog.000001。

時間點還原 (PITR)

# 1. 還原完整備份
mysql -u root -p mydb < /backup/mydb_2024-01-15.sql

# 2. 套用二進制日誌至指定時間/位置
# 按時間 (誤操作前 1 秒)
mysqlbinlog --stop-datetime="2024-01-15 10:30:00" /var/lib/mysql/binlog/mylog.000001 | mysql -u root -p mydb

# 按位置 (精確到事件)
mysqlbinlog --stop-position="123456" /var/lib/mysql/binlog/mylog.000001 | mysql -u root -p mydb

Windows 排程備份

• 將 mysqldump 命令寫入 .bat 檔 → 工作排程器 → 每天 02:00 執行。

總結：優化優先級排序

1. 索引設計與查詢改寫 (最高 CP 值)
2. 分表/垂直拆分 (大資料量必經)
3. 參數調校 (專用 DB 明顯收益)
4. 讀寫分離 (寫入瓶頸)
5. 硬體升級 (最後手段)

系統性實施上述策略，MySQL 可支撐 TB 級資料、萬級 QPS 的高併發業務。