MySQL 數據庫優化全景圖：從單機配置到分布式架構演進的運維視角
成熟的數據庫架構非一蹴而就，而是隨業務增長持續演進。本文從運維視角，劃分表設計、部署拓撲、單機優化、架構擴展、日常維護五大階段，系統梳理每階段核心問題、優化手段、工具鏈與決策依據。
階段一：數據庫表設計 —— 基石決定上限
核心原則
- 範式與反範式平衡：核心交易表遵循 3NF，報表/寬表適度冗余減少 JOIN。
- 字符集統一 utf8mb4，避免隱式轉換導致索引失效。
- 主鍵設計：優先
BIGINT AUTO_INCREMENT，避免 UUID/字符串主鍵導致頁分裂、二級索引膨脹。
- 索引策略：最左前綴、高區分度優先、覆蓋索引、避免冗余/未用索引。
- 大字段拆分：TEXT/BLOB/JSON 移至擴展表，主表僅存頻繁訪問列。
常見反模式
| 反模式 | 後果 | 修正 |
|---|
| 無主鍵/業務主鍵 | 隱式 RowID 爭用、主從復制亂序 | 顯式 BIGINT PK |
| 過度索引 | 寫入放大、優化器誤判、空間浪費 | 定期 sys.schema_unused_indexes 掃描刪除 |
| 外鍵約束 | 鎖競爭、死鎖、擴展受限 | 應用層保證完整性，DB 僅建索引 |
| 枚舉/集合類型 | ALTER TABLE 鎖表、擴展困難 | TINYINT + 應用層字典/字典表 |
階段二：部署拓撲 —— 高可用起點
- 單機：QPS ≤ 1500，開發/測試/非核心業務。
- 主從 + Keepalived/VIP：QPS 1500-3000，自動故障轉移，RTO < 30s。
- 主從 + Orchestrator/MHA：QPS 3000+，拓撲可視化、自動遷移、多機房支援。
關鍵配置：gtid_mode=ON、enforce_gtid_consistency=ON、log_slave_updates=ON、binlog_format=ROW。
階段三：單機性能優化 —— 榨乾硬件紅利
3.1 InnoDB 核心參數 (MySQL 5.7/8.0 基線)
| 參數 | 建議值 | 說明 |
|---|
| innodb_buffer_pool_size | 物理內存 60-70% | 數據+索引熱點緩存，命中率 > 99% |
| innodb_buffer_pool_instances | 4 或 8 | 減少全局鎖競爭，每實例 ≥ 1GB |
| innodb_flush_log_at_trx_commit | 2 (高寫入) / 1 (金融) | 1=最安全，2=高性能(秒級丟失風險) |
| innodb_file_per_table | ON | 獨立表空間，支援在線 DDL、表級遷移 |
| innodb_log_file_size | 1-4GB | 大事務、高寫入減少 checkpoint 壓力 |
| innodb_log_buffer_size | 64-256MB | 大事務減少磁盤同步 |
| innodb_io_capacity / max | SSD: 2000-5000 / 10000 | 根據磁盤 IOPS 校準，刷頁自適應 |
| innodb_max_dirty_pages_pct | 75-90 | 延緩刷盤，合併寫入 |
3.2 系統內核優化 (Linux)
# /etc/sysctl.conf
vm.swappiness = 1 # 盡量不 swap
vm.dirty_ratio = 30
vm.dirty_background_ratio = 10
fs.file-max = 1000000
fs.aio-max-nr = 1048576
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
# /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535
# 禁用透明大頁 (THP)
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
3.3 硬件選型建議
- CPU：高主頻 (>3.0GHz) > 核心數，MySQL 單查詢單線程，主頻決定單查延遲。
- 內存：≥ 64GB，Buffer Pool 越大命中率越高。
- 存儲：NVMe SSD 必選，RAID 10 (性能+冗余)，預留 20% 過提供空間。
- 網絡：萬兆網卡，主從同機房部署，RTT < 0.5ms。
階段四：架構擴展 —— 突破單機極限
4.1 讀寫分離
- 一主多從，主寫從讀。
- 中間件：ProxySQL (推薦、支援查詢路由、熔斷、緩存)、MaxScale、MyCat。
- 應用層路由：讀寫分離 DataSource (Spring AbstractRoutingDataSource)。
4.2 分庫分表
| 維度 | 垂直拆分 | 水平拆分 |
|---|
| 依據 | 業務域/表寬度 | 數據量/訪問熱度 |
|---|
| 手段 | 不同庫部署不同業務表 | 同表結構按 Sharding Key 拆分多表 |
|---|
| 中間件 | 服務化治理 | ShardingSphere、MyCAT、Vitess、自研 |
|---|
| 難點 | 分布式事務、跨庫 JOIN | 分布式 ID、分頁、聚合、擴容遷移 |
|---|
4.3 分區表 (Partitioning)
- 邏輯上單表，物理上多段存儲 (RANGE/LIST/HASH/KEY)。
- 優勢：歷史數據快速歸檔 (
DROP PARTITION)、分區裁剪加速範圍查詢、跨磁盤分佈 I/O。
- 局限：分區鍵必須包含在 PK/UK 中、無法使用外鍵、DDL 鎖表時間長 (8.0+ 即時 DDL 改善)。
4.4 緩存層前置
- Redis Cluster / Codis：熱點數據、會話、計數器、排行榜。
- 多級緩存：本地 (Caffeine/Guava) + 分布式 (Redis)。
- 一致性策略：Cache-Aside (旁路)、Read/Write Through、Write Behind (異步落庫)。
階段五：日常維護 —— 可觀測與自愈
5.1 核心指標儀表盤
| 指標 | 計算方式 | 告警閾值 |
|---|
| QPS | Questions / Uptime 或 ΔCom_select/Δt | > 80% 歷史峰值 |
| TPS | (Com_commit + Com_rollback) / Uptime | > 80% 歷史峰值 |
| 連接數使用率 | Threads_connected / max_connections | > 70% |
| 緩存命中率 | 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests | < 99% |
| 復制延遲 | Seconds_Behind_Master / Binlog 位點差 | > 10s / > 100MB |
| 慢查詢佔比 | Slow_queries / Questions | > 1% |
5.2 慢查詢閉環
- 開啟
slow_query_log=ON、long_query_time=1、log_queries_not_using_indexes=ON。
pt-query-digest 日誌分析 → 產出 Top N 慢 SQL。- 開發優化 (索引/改寫/拆分) → 預發驗證 → 發布 → 觀察指標回落。
5.3 備份恢復演練
- 策略：每日全量 (xtrabackup) + 每小時增量 + Binlog 實時同步異地。
- RPO 目標：< 1 小時；RTO 目標：< 30 分鐘 (核心庫)。
- 每季度至少 1 次全量恢復演練，驗證備份完整性、恢復腳本、權限重建。
5.4 表損壞自愈
