MySQL 服務端架構
SQL 層(Server Layer)
- 連線管理、線程池
- 查詢緩存(8.0 移除)
- 解析器、優化器、執行器
- 表管理器、權限控制
- Binlog、Redo Log、Undo Log 協調
存儲引擎層
| 引擎 | 鎖級別 | 事務 | 適用場景 |
| InnoDB | 行鎖 | ✅ ACID | OLTP、核心業務(預設) |
| MyISAM | 表鎖 | ❌ | 唯讀、全文檢索(已淘汰) |
| Memory | 表鎖 | ❌ | 臨時表、查找表 |
| Archive | 行鎖 | ❌ | 日誌、歸檔、高壓縮 |
鎖機制深度解析
表鎖
- MyISAM:
LOCK TABLES t READ/WRITE
- 優先級:
WRITE > READ,寫鎖阻塞所有讀,讀鎖阻塞寫
- 並發插入:
concurrent_insert 參數控制
行鎖
- InnoDB:基於**索引**加鎖,無索引退化為表鎖
- 三種模式:
- Record Lock:單行記錄鎖
- Gap Lock:間隙鎖(防止幻讀)
- Next-Key Lock:Record + Gap(RR 隔離級別預設)
- 意向鎖:IX/IS(表級,表示意圖加行鎖)
鎖優化原則
- 所有查詢**走索引**,避免全表掃描升級表鎖
- 索引設計精準,縮小鎖定範圍
- 減少範圍查詢,降低 Gap Lock 影響
- 控制事務大小:小事務、短持鎖時間
- 業務允許時降低隔離級別(RC + binlog_row=ROW)
監控指標體系
鎖監控
-- 表鎖
SHOW STATUS LIKE 'Table_locks%';
-- Table_locks_immediate: 立即獲得
-- Table_locks_waited: 等待次數
-- 行鎖
SHOW STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_current_waits: 當前等待數
-- Innodb_row_lock_time: 總等待時間(ms)
-- Innodb_row_lock_time_avg: 平均等待
-- Innodb_row_lock_time_max: 最大等待
-- Innodb_row_lock_waits: 總等待次數
連線與線程
SHOW STATUS LIKE 'Threads_%';
-- Threads_connected: 當前連線
-- Threads_running: 正在執行查詢
-- Threads_created: 歷史建立線程數
-- Threads_cached: 緩存中線程
-- 線程緩存命中率
(Connections - Threads_created) / Connections * 100% -- 目標 > 90%
表緩存
SHOW STATUS LIKE 'Open_tables';
SHOW GLOBAL VARIABLES LIKE 'table_open_cache';
-- Open_tables 接近 table_open_cache 時需調大
效能分析工具
Profiling(5.7/8.0 部分版本支援)
SET profiling = 1;
-- 執行 SQL
SELECT * FROM large_table WHERE id > 1000;
SHOW PROFILES;
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;
mysqlslap 壓測
# 自動生成測試
mysqlslap --concurrency=50,100 --iterations=3 \
--number-int-cols=4 --number-char-cols=2 \
--auto-generate-sql --auto-generate-sql-load-type=mixed \
--engine=innodb --number-of-queries=1000
# 指定 SQL 文件
mysqlslap --concurrency=100 --iterations=5 \
--create-schema=test --query=/path/test.sql
EXPLAIN 關鍵欄位
| 欄位 | 關鍵值 | 含義 |
| type | system > const > eq_ref > ref > range > index > ALL | 訪問類型,越左越好 |
| key | 實際使用索引 | NULL 表示全表掃描 |
| rows | 預估掃描行數 | 越小越好 |
| Extra | Using index / Using where / Using temporary / Using filesort | 額外資訊 |
索引限制與失效場景
- MyISAM 索引鍵長度總和 ≤ 1000 bytes
- BLOB/TEXT 僅支援前綴索引
- 不支援函數索引(8.0 支援函數索引/虛擬欄位)
!= / <> / NOT IN / NOT EXISTS 通常不走索引
- 欄位參與函數運算:
WHERE ABS(col) > 10
- JOIN 條件欄位類型/編碼不一致
LIKE '%abc' 以通配符開頭
- 非等值查詢無法用 Hash 索引
排序優化
兩種算法
- 雙路排序:取排序欄位 + 行指針 → Sort Buffer 排序 → 回表取其他欄位
- 單路排序:一次取所有欄位 → Sort Buffer 排序 → 直接輸出(佔用更多 Sort Buffer)
優化手段
- 增大
sort_buffer_size(會話級,慎重)
- 使用
LIMIT 減少排序行數
- 覆蓋索引:
SELECT a,b FROM t WHERE c=1 ORDER BY a 索引 (c,a,b)
GROUP BY 優化
- 優先使用**索引掃描**(松散索引掃描最佳)
- 無索引時需
sort_buffer_size + 臨時表,大結果集會落盤 → 效能斷崖式下降
DISTINCT 原理同 GROUP BY
Schema 設計原則
- 適度冗余:減少 JOIN,如冗存商品名到訂單表
- 大字段垂直拆分:TEXT/BLOB/JSON 移至擴展表
- 大表水平分片:按時間/用戶 ID 分表分庫
- 時間類型選擇:
TIMESTAMP (4B, 1970-2038) vs DATETIME (8B, 1001-9999)
Binlog 優化
| 參數 | 建議 | 說明 |
| sync_binlog | 1(金融) / 100-1000(高併發) | 每 N 次提交 fsync,平衡安全與效能 |
| binlog_cache_size | 4-16M | 事務 Binlog 緩衝,大事務調大 |
| max_binlog_size | 1G | 單文件上限,超過自動切換 |
| binlog_format | ROW | 主從一致性最佳,支援並行複寫 |
| expire_logs_days | 7 | 自動清理,防止磁碟滿 |
Query Cache 負面影響(5.7 及之前)
- 每個 SELECT 需 Hash 計算與查找,高併發下 CPU 開銷大
- 表任意變更導致相關 Cache 全部失效,寫入頻繁時命中率極低
- 緩存 Result Set 而非頁面,重複資料佔用記憶體
- **建議關閉**:
query_cache_type=0, query_cache_size=0
記憶體分配實戰(16GB 專用 DB)
OS Reserve: 2 GB
InnoDB Buffer Pool: 10 GB
InnoDB Log Buffer: 64 MB
Thread Buffers: 1.5 GB (500 conn × 3MB)
Table Open Cache: 500 MB (table_open_cache=10000)
Other/Buffer: 1 GB
-------------------------
Total: ~15 GB (留 1GB 安全)
總結
MySQL 調優是系統工程:架構理解 → 鎖機制掌握 → 監控體系建立 → 參數基準線 → 壓測驗證 → 持續迭代。核心原則:InnoDB Buffer Pool 盡可能大、索引設計服務於查詢、事務越小越好、關閉 Query Cache、啟用慢查詢日誌、建立完整監控告警。工具鏈:EXPLAIN、SHOW STATUS、mysqlslap、pt-query-digest、Prometheus+Grafana。