技巧一：慢查詢日誌 + pt-query-digest 閉環
# my.cnf
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time=1 # 1 秒閾值
log_queries_not_using_indexes=1
# 分析
pt-query-digest /var/lib/mysql/slow.log --limit 20 --filter '$event->{fingerprint} =~ /^SELECT/'
核心：持續收集 → 定期分析 → 針對優化 → 驗證效果。
技巧二：EXPLAIN 讀懂執行計劃
type: system > const > eq_ref > ref > range > index > ALLkey: 實際使用索引，NULL 表示全掃rows: 預估掃描行數，越小越好Extra: Using index (覆蓋)、Using where (服務層過濾)、Using temporary/filesort (需優化)
技巧三：索引設計黃金法則
- 單表索引 ≤ 5 個，組合索引列 ≤ 5 個
- 最左前綴：等值在前，範圍/排序在後
- 高選擇性列優先 (唯一值/總行數 > 0.1)
- 覆蓋索引避免回表：
INDEX(a,b) INCLUDE(c)
- 避免冗余索引：
(a) + (a,b) → 保留後者
技巧四：SQL 改寫模式
| 反模式 | 優化模式 | 原理 |
|---|
| SELECT * | 顯式列出所需列 | 減少網絡/IO/內存，利用覆蓋索引 |
| IN (子查詢) | JOIN / EXISTS | 避免 DEPENDENT SUBQUERY 低效執行 |
| OR 條件 | UNION ALL | 各分支可用各自索引，合併結果 |
| LIKE '%kw%' | Fulltext / Elasticsearch | B+Tree 無法支援前導模糊 |
| 函數列 | 範圍查詢 | WHERE DATE(t)='...' → WHERE t >= '...' AND t < '...'+1day |
技巧五：分頁優化 (深分頁必看)
-- 反模式: LIMIT 100000, 20
-- 優化: 覆蓋索引 + 子查詢回表
SELECT * FROM t
WHERE id IN (SELECT id FROM t WHERE status=1 ORDER BY created_at LIMIT 100000, 20);
-- 極致: Keyset 分頁 (業務允許時)
WHERE created_at < @last_ts ORDER BY created_at LIMIT 20;
技巧六：配置參數調優 (InnoDB 核心)
| 參數 | 建議值 | 說明 |
|---|
| innodb_buffer_pool_size | 物理內存 60-80% | 數據+索引熱點緩存，核心參數 |
| innodb_log_file_size | 1-4 GB | 大事務/高寫入減少 checkpoint 衝刷 |
| innodb_flush_log_at_trx_commit | 1 (核心) / 2 (高寫入可妥協) | ACID 與性能平衡 |
| innodb_file_per_table | ON | 獨立表空間，支援 DROP TABLE 回收空間 |
| innodb_io_capacity | SSD: 2000-5000 / HDD: 200 | 後台刷盤能力，匹配存儲硬件 |
技巧七：架構層面讀寫分離
- 主庫寫、從庫讀，中間件 (ProxySQL/MaxScale) 路由
- 核心業務 (交易/庫存) 強制走主，非核心容忍延遲走從
- 半同步復制
rpl_semi_sync_master=ON 降低延遲至毫秒級
技巧八：大表拆分策略
- 垂直拆分: 冷熱列分離、大字段 (BLOB/TEXT/JSON) 獨立表
- 水平分片: 分片鍵選擇 (用戶ID/時間/地區)、中間件透明路由
- 歸檔歷史: 定期將冷數據遷移至歸檔庫/ClickHouse/對象存儲
技巧九：監控體系建設
# 關鍵指標 (Prometheus + Grafana)
- QPS/TPS、慢查詢數、連接數、緩存命中率
- Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests (命中率 > 99%)
- Innodb_row_lock_waits、Innodb_row_lock_time_avg (鎖爭用)
- Replication Lag (Seconds_Behind_Master)
- 磁碟 IOPS/使用率、CPU/內存
技巧十：定期維護作業
- 統計信息更新:
ANALYZE TABLE (低峰期，大表分區執行)
- 碎片整理:
OPTIMIZE TABLE / ALTER TABLE ... ENGINE=InnoDB (需鎖表，5.6+ Online DDL)
- 備份驗證: 定期恢復演練，校驗數據完整性
- 權限審計: 回收無用賬號、最小權限原則
