本文匯總 MySQL 內核架構模組、日誌體系、鎖機制、效能剖析工具、壓力測試方法、索引限制、排序算法、EXPLAIN 深度解讀、Schema 設計原則、Binlog/Query Cache 優化、記憶體分配模型等核心知識體系，構建 DBA 進階知識地圖。
一、SQL Layer 核心模組架構（17 大子系統）
| 模組 | 核心職責 | 關鍵技術點 |
|---|
| 初始化模組 | 啟動時 Buffer/Cache/變數/引擎初始化 | 內存分配、引擎註冊 |
| 核心 API | 底層資料結構、演算法、字串/數字處理、記憶體管理 | mysys、strings 目錄 |
| 網絡交互 | TCP/IP、Unix Socket 抽象介面 | VIO 庫、非阻塞 I/O |
| Client/Server 協議 | MySQL 私有協議解析/封裝 | 握手、認證、命令分發 |
| 用戶模組 | 登入認證、連線權限控制 | 大門守衛角色 |
| 訪問控制 | 列/表/庫/全域權限即時檢查 | ACL 快取、動態授權 |
| 連線管理 | 監聽、分發、線程池/緩存 | thread_cache_size、連線數限制 |
| Query 解析轉發 | 語法/語義分析、AST 生成、路由分發 | 解析器、詞法分析器 |
| Query Cache | SELECT 結果集 Hash 快取 | 失效機制、碎片整理、命中率 |
| Query 優化器 | 統計資訊、代價模型、執行計劃生成 | RBO/CBO、Join 重排序 |
| 表變更管理 | DML/DDL 執行入口 | MDL 鎖、Online DDL |
| 表維護 | CHECK/REPAIR/OPTIMIZE/ANALYZE | 碎片整理、統計更新 |
| 系統狀態管理 | SHOW STATUS/VARIABLES/PROCESSLIST | 效能_schema、資訊_schema |
| 表管理器 | .frm 結構檔管理、Table Cache、表鎖 | table_open_cache、MDL |
| 日誌記錄 | Error/Binary/Slow/General Log | 雙一致性、GTID |
| 復制模組 | Master Dump / Slave IO+SQL 線程 | 半同步、多源、GTID |
| 儲存引擎介面 | Handler API 抽象、插件式引擎 | MyISAM/InnoDB/NDB/RocksDB |
二、日誌體系分類與定位
| 日誌類型 | 層級 | 內容 | 用途 |
|---|
| Error Log | Server | 啟動/崩潰/錯誤/警告 | 故障診斷、告警 |
| Binary Log | Server | 邏輯變更事件 | 復制、PITR、審計 |
| Slow Query Log | Server | 超時/無索引查詢 | SQL 調優、審計 |
| InnoDB Redo Log | Engine | 物理頁面變更 | 崩潰恢復、WAL |
| InnoDB Undo Log | Engine | 舊版本資料 | MVCC、回滾 |
慢查詢開啟最佳配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
三、鎖機制三層級與引擎映射
| 鎖級別 | 引擎 | 特性 | 衝突檢測 |
|---|
| 表級鎖 | MyISAM、MEMORY、CSV | 粒度粗、零開銷、併發低 | Table_locks_waited |
| 頁級鎖 | BerkeleyDB | 折中方案、已淘汰 | 罕見 |
| 行級鎖 | InnoDB、NDB Cluster | 粒度細、併發高、開銷大 | Innodb_row_lock_waits |
MyISAM 鎖調度優先級規則
- 寫鎖 > 讀鎖（預設），但
LOW_PRIORITY_UPDATES=1 可反轉
INSERT DELAYED、CONCURRENT INSERT 緩解讀寫衝突
InnoDB 行鎖最佳實踐
- 所有檢索走索引，避免全表掃描升級表鎖
- 索引設計精準，縮小鎖定範圍（避免間隙鎖過大）
- 減少範圍查詢，控制事務大小與持續時間
- 業務允許下降級隔離級別（RC > RR）
鎖監控指標
-- MyISAM 表鎖
SHOW STATUS LIKE 'Table_locks%';
-- Table_locks_immediate / Table_locks_waited
-- InnoDB 行鎖
SHOW STATUS LIKE 'Innodb_row_lock%';
-- current_waits, time, time_avg, time_max, waits
四、效能剖析與壓力測試工具鏈
Profiling 微觀剖析
SET profiling = 1;
-- 執行目標 SQL
SHOW PROFILES;
SHOW PROFILE CPU, BLOCK IO FOR QUERY ;
mysqlslap 官方壓測工具
# 自動生成 SQL 壓測
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=50,100 --iterations=3 \
--auto-generate-sql --auto-generate-sql-load-type=mixed \
--engine=InnoDB --number-of-queries=1000
# 指定業務 SQL 壓測
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=5 \
--create-schema=test --query=/path/business.sql
五、索引使用八大限制（必背）
- MyISAM 索引鍵長度總和 ≤ 1000 位元組
- BLOB/TEXT 僅支援前綴索引
- 不支援函數索引（MySQL 8.0+ 支援函數索引/虛擬欄位索引）
!= / <> 無法使用索引- 過濾欄位套用函數（
ABS(col)）失效
- JOIN 條件欄位類型不一致失效
LIKE '%abc%' 通配符開頭失效- 非等值查詢無法使用 Hash 索引
六、排序算法雙路徑機制
| 算法 | 流程 | 適用場景 | 記憶體壓力 |
|---|
| 兩次傳輸 | SortBuffer 排序 (sort_key, rowid) → 回表取欄位 | 需回表欄位多、總長度大 | 較低 |
| 單次傳輸 | 一次讀取所有需求欄位 → SortBuffer 排序全行 | 需求欄位少、總長度小 | 較高（max_length_for_sort_data 控制） |
優化器根據 max_length_for_sort_data（預設 4096 位元組）自動選擇。
七、EXPLAIN 深度欄位解讀（精華版）
| 欄位 | 核心含義 | 優化關注點 |
|---|
| id | 查詢序號（子查詢遞增） | 相同 id 由上而下執行 |
| select_type | SIMPLE/PRIMARY/SUBQUERY/DEPENDENT SUBQUERY/UNION/UNION RESULT | DEPENDENT 子查詢易退化效能 |
| table | 訪問表名 | 衍生表顯示 <derivedN> |
| type | system > const > eq_ref > ref > range > index > ALL | 目標至少 ref，避免 ALL/index |
| possible_keys | 理論可用索引 | 為 NULL 表示無索引可用 |
| key | 實際選用索引 | 與 possible_keys 對比 |
| key_len | 使用索引長度 | 推斷複合索引用了幾列 |
| ref | 關聯比較對象 | const / 表.欄位 / func |
| rows | 預估掃描行數 | 統計資訊準確性關鍵 |
| Extra | Using index / Using where / Using temporary / Using filesort / Using index for group-by | 重點排查 temporary/filesort |
八、Schema 設計三大原則
- 適度冗餘： 以空間換時間，避免跨表 Join（如訂單表冗餘用戶名、商品名）
- 大欄位垂直拆分： TEXT/BLOB/JSON 獨立副表，主表只存核心屬性
- 大表水平拆分： 分庫分表（Range/Hash/一致性 Hash），中間件路由
時間類型選型
TIMESTAMP (4 Bytes)：1970-2038，自帶時區，自動更新DATETIME (8 Bytes, 5.6.4+ 5 Bytes)：1000-9999，無時區，靜態- 1970 前業務必選 DATETIME
九、Binlog 優化關鍵參數
| 參數 | 推薦值 | 說明 |
|---|
| sync_binlog | 1 (金融) / 100-1000 (高寫入) | 每 N 次提交 fsync，平衡性能與安全 |
| binlog_cache_size | 1-4 MB | 事務期間邏輯日誌快取，避免磁碟臨時檔 |
| max_binlog_size | 512 MB - 1 GB | 單檔上限，超過自動切換 |
| expire_logs_days | 7-30 | 自動清理，防磁碟佔滿 |
| binlog_format | ROW | 最安全，支援混合模式 |
十、Query Cache 負面效應與禁用建議
- Hash 計算/查找 CPU 開銷（高 QPS 明顯）
- 寫入頻繁導致快取頻繁失效（
Qcache_lowmem_prunes 高）
- 結果集重複快取浪費記憶體
- MySQL 8.0 已移除 Query Cache，建議 5.7 生產環境直接
query_cache_type=0、query_cache_size=0
十一、記憶體分配模型（8GB 實體、500 連線、MyISAM+InnoDB 混合）
總記憶體 = 8 GB
├─ 系統保留: 800 MB
├─ 線程私有 (500 連線 × ~4.5 MB): ~2.25 GB
│ ├─ sort_buffer_size: 1 MB
│ ├─ join_buffer_size: 1 MB
│ ├─ read_buffer_size: 1 MB
│ ├─ read_rnd_buffer_size: 512 KB
│ └─ thread_stack: 512 KB
├─ MyISAM Key Cache: 1.5 GB
└─ InnoDB Buffer Pool: 8 - 0.8 - 2.25 - 1.5 = 3.45 GB (≈ 43%)
核心原則：Buffer Pool 佔可用記憶體 60-80%，線程私有緩衝謹慎設定避免 OOM。
十二、thread_cache_size 與 open_files_limit 調優
thread_cache_size：命中率 (Connections - Threads_created) / Connections > 90%open_files_limit ≥ 2-3 × table_open_cache（MyISAM 重度場景）table_open_cache：Open_tables 接近上限且 Opened_tables 持續增長時擴容
此筆記體系化覆蓋 MySQL 核心內幕，建議配合官方手冊、performance_schema 實戰觀測，逐層內化為可落地的調優直覺。
