成熟的數據庫架構非一蹴而就，而是隨業務增長逐級演進。本文從運維角度，劃分表設計 → 部署架構 → 參數調優 → 架構擴展 → 日常維護五大階段，附具體參數、工具、指標、腳本，構建可落地的優化知識體系。
pt-table-checksum 一致性校驗、pt-online-schema-change 零停機變更。單機極限 ~1500 QPS（未優化），優化後 ~2500 QPS。架構演進：
| 階段 | 架構 | 核心組件 | 適用 QPS |
|---|---|---|---|
| 初期 | 單機 | — | ~1500 |
| 高可用 | 主從 + Keepalived/VIP | MySQL Replication, Keepalived | ~2000 (讀寫不分離) |
| 讀寫分離 | 一主多從 + 代理 | ProxySQL / MyCat / ShardingSphere-Proxy | ~10000+ (從庫橫向擴展) |
| 多主強一致 | MGR / Percona XtraDB Cluster | Group Replication / Galera | 寫入擴展、強一致 |
博客參考：雙機熱備、主從復制、讀寫分離、MySQL-MMM。
| 參數 | 默認 | 建議調優值 | 說明 |
|---|---|---|---|
max_connections | 151 | 2000~5000 | 實際峰值 * 1.2，防連接耗盡 |
sort_buffer_size | 2M | 16M | ORDER BY/GROUP BY 專用，線程私有 |
query_cache_size | 16M | 64M~256M | 5.7 已廢棄，8.0 移除；改用 Redis |
open_files_limit | 1024 | 65535 | 配合 OS ulimit，防 Too many open files |
key_buffer_size | 16M | 物理內存 30% (MyISAM) | 僅 MyISAM 索引緩存 |
read_buffer_size | 128K | 16M~32M | 全表掃描順序讀緩存 |
innodb_buffer_pool_size | 128M | 物理內存 60~70% | InnoDB 核心，數據+索引緩存 |
innodb_buffer_pool_instances | 1 | 4~8 | 減少互斥鎖競爭 |
innodb_flush_log_at_trx_commit | 1 | 2 | 每秒 fsync，性能提升明顯 |
innodb_file_per_table | OFF | ON | 獨立表空間，利於遷移/備份 |
innodb_log_buffer_size | 8M | 16M~64M | 大事務場景增大 |
# /etc/sysctl.conf
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_tw_buckets = 4096
net.ipv4.tcp_max_syn_backlog = 4096
# 生效
sysctl -p
# /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
# 立即生效
ulimit -SHn 65535
按業務域拆分：web、bbs、blog，各自主從集群，故障隔離。
| 類型 | 策略 | 解決問題 | 代價 |
|---|---|---|---|
| 垂直拆分 | 大字段/低頻字段拆獨立表 | 表寬度、I/O 放大 | JOIN 回表 |
| 水平拆分 | 取模/範圍/Hash 分片 | 單表行數、索引大小 | 分布式事務、跨分片查詢 |
工具：Merge 引擎（簡易）、ShardingSphere/MyCat（中間件）、應用層路由（極致性能）。
按 RANGE/LIST/HASH/KEY 將一張表物理分段，邏輯仍是單表，DDL 可單獨操作分區，多磁盤並行 I/O。實現簡單，無需應用改造。
| 指標 | 計算方式 | 告警閾值 |
|---|---|---|
| QPS | Questions / Uptime 或 (Com_select+Com_insert+Com_update+Com_delete) 間隔差 | > 80% 實例極限 |
| TPS | (Com_commit+Com_rollback) / Uptime | > 80% 實例極限 |
| 緩存命中率 | 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests | < 99% |
| 主從延遲 | Seconds_Behind_Master | > 5 秒 |
| 連接使用率 | Threads_connected / max_connections | > 80% |
| 慢查詢數 | Slow_queries 增長 | > 0 |
# 開啟
SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=1;
SET GLOBAL log_queries_not_using_indexes=1;
# 分析工具
mysqldumpslow -t 10 /var/log/mysql/slow.log # 官方簡易
pt-query-digest /var/log/mysql/slow.log # 專業全維度
# binlog 分析
mysqlbinlog mysql-bin.000001 > /tmp/binlog.sql
pt-query-digest --type=binlog /tmp/binlog.sql
| 工具 | 類型 | 適用場景 | 優/缺點 |
|---|---|---|---|
mysqldump | 邏輯備份 | 小庫 < 50GB | 跨版本遷移靈活 / 速度慢、鎖表久 |
mysqlhotcopy | 物理備份 | MyISAM 只讀 | 快 / 僅 MyISAM、需鎖表 |
xtrabackup | 物理熱備 | InnoDB 生產首選 | 不鎖表、支援增量、壓縮、加密、流式傳輸 |
參考：Xtrabackup 實戰。
| 工具 | 支援引擎 | 需停庫 | 常用參數 |
|---|---|---|---|
myisamchk | MyISAM | 是 | -r -q 快速恢復 |
mysqlcheck | MyISAM + InnoDB | 否 | -r -q -A 全庫快速修復 |
# MyISAM 快速修復
cd /var/lib/mysql/db_name
myisamchk -r -q *.MYI
# InnoDB/MyISAM 混合修復
mysqlcheck -r -q -u root -p db_name
# CPU 詳細
mpstat -P ALL 1
# I/O 詳細 (ms 級)
iostat -d -x -k 1
# 關鍵列：%util(100=飽和)、await(隊列等待)、svctm(服務時間)、r/s+w/s=IOPS
單機優化 (參數、索引、硬件)
↓
主從 + 讀寫分離 (讀擴展)
↓
緩存層 (Memcached/Redis) - 熱數據下沉
↓
分庫/分表/分區 (寫擴展、數據隔離)
↓
分布式 NewSQL (TiDB/OceanBase) - 原生橫向擴展
↓
HTAP 混合負載 (OLTP+OLAP 一體)
每階段落地前必須：壓測基線 → 變更單點 → 驗證指標 → 灰度發布 → 全量切換。運維的價值在於「在業務感知前發現並解決問題」，這張演進圖即是你的導航儀。
