┌─────────────────┐
│ SQL & 索引優化 │ ← 80% 收益來源,成本最低
├─────────────────┤
│ Schema 設計優化 │ ← 分區、垂直拆分、冷熱分離
├─────────────────┤
│ 實例參數調優 │ ← 內存、日志、並發、IO
├─────────────────┤
│ 架構高可用 │ ← 讀寫分離、主從、分庫分表
├─────────────────┤
│ 硬件/OS/網絡 │ ← 最後手段,成本最高
└─────────────────┘
核心原則: 按業務邏輯可劃分維度 (時間/用戶/地域) 拆分,查詢路由到單表/少數表。
-- 範例: 訂單表按年分表
orders_2023, orders_2024, orders_2025...
-- 查詢路由: 2023年訂單 → orders_2023
關鍵字: UNION ALL 合併跨分片查詢、中間件 (ShardingSphere/MyCat) 透明路由。
場景: 表字段多、含大字段 (TEXT/BLOB/JSON)、頻繁查詢少量列。
-- 原表: questions (id, stem, options, answer, analysis, meta, created_at) -- stem/analysis 極大
-- 拆分:
questions_core (id, stem, options, answer, created_at) -- 高頻查詢
questions_detail (id, analysis, meta) -- 低頻查詢,1:1 關聯
收益: 核心表行長大幅縮減,Buffer Pool 容納更多熱行,列表查詢無大字段 I/O。
-- 推薦
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
-- 避免
UUID CHAR(36) -- 36字節、無序導致頁分裂、二級索引膨脹
MD5 CHAR(32) -- 同理
業務編碼 -- 含業務邏輯,變更風險大
對比: 4 字節 vs 36 字節,主鍵越小 → 二級索引越小 → Buffer Pool 命中率越高。
url VARCHAR(500) 或 object_key VARCHAR(200)| 參數 | 推薦值 | 說明 |
|---|---|---|
innodb_buffer_pool_size | 物理內存 60-75% | 核心緩存,數據+索引+鎖+自適應哈希 |
innodb_log_file_size | 1-4 GB | Redo Log 單文件大小,減少 Checkpoint 壓力 |
innodb_log_files_in_group | 4 | 循環寫入文件數 |
innodb_flush_log_at_trx_commit | 1 | 事務提交強制刷盤,ACID 核心,極致性能可設 2 (風險秒級丟失 1 秒) |
sync_binlog | 1 | 每次提交同步 Binlog,配合上面雙 1 保證不丟數據 |
innodb_io_capacity | SSD: 2000-5000 / HDD: 200 | 後台刷頁能力,根據磁盤 IOPS 設置 |
max_connections | 1000-3000 | 配合 thread_cache_size=100、back_log=500 |
table_open_cache | 4000+ | 減少打開表句柄開銷 |
sort_buffer_size | 2-4 MB | 會話級,過大 OOM 風險,避免 * |
join_buffer_size | 2-4 MB | 無索引 JOIN 時使用,慎調大 |
discard/TRIM),掛載 noatimenumactl --interleave=all mysqld 避免跨節點內存訪問vm.swappiness=1 (極少用 swap),關閉透明大頁 transparent_hugepage=never
┌─────────────┐
│ Application │
└──────┬──────┘
│
┌────────────▼────────────┐
│ ProxySQL │ ← 連接池、讀寫分離、熔斷、SQL防火牆、查詢重寫
└────────────┬────────────┘
│
┌─────────────────┼─────────────────┐
▼ ▼ ▼
┌─────────┐ ┌─────────┐ ┌─────────┐
│ Master │──────▶│ Slave 1 │ │ Slave 2 │ ← 同步復制 (GTID/半同步)
│ (Write) │ Binlog│ (Read) │ │ (Read) │
└─────────┘ └─────────┘ └─────────┘
Seconds_Behind_Master、pt-heartbeat、ProxySQL mysql_monitor_read_only| 策略 | 工具 | 頻率 | 保留 | 驗證 |
|---|---|---|---|---|
| 物理全量 | XtraBackup | 每日 02:00 | 7 天 | 月度恢復演練 |
| 增量 | XtraBackup --incremental | 每 6 小時 | 7 天 | 鏈完整性校驗 |
| 邏輯輔助 | mysqldump (核心表) | 每日 | 30 天 | 跨版本遷移備用 |
| Binlog 歸檔 | mysqlbinlog 遠程備份 | 實時流式 | 30 天 | PITR 就緒 |
#!/bin/bash
# /opt/scripts/xtrabackup_full.sh
set -euo pipefail
BACKUP_ROOT="/backup/mysql"
DATE=$(date +%F_%H%M)
TARGET_DIR="$BACKUP_ROOT/full_$DATE"
mkdir -p "$TARGET_DIR"
xtrabackup --backup \
--user=backup_user --password='StrongPass' \
--target-dir="$TARGET_DIR" \
--parallel=4 --compress --compress-threads=4 \
--slave-info --safe-slave-backup
xtrabackup --prepare --target-dir="$TARGET_DIR" --use-memory=4G
# 保留 7 天
find "$BACKUP_ROOT" -maxdepth 1 -type d -name 'full_*' -mtime +7 -exec rm -rf {} \;
# crontab -e
0 2 * * * /opt/scripts/xtrabackup_full.sh >> /var/log/xtrabackup.log 2>&1
EXPLAIN、Performance Schema、sys schema、pt-query-digest