萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 核心技術筆記:架構、鎖、優化、監控與實戰調優

MySQL 服務端架構

SQL 層(Server Layer)

  • 連線管理、線程池
  • 查詢緩存(8.0 移除)
  • 解析器、優化器、執行器
  • 表管理器、權限控制
  • Binlog、Redo Log、Undo Log 協調

存儲引擎層

引擎鎖級別事務適用場景
InnoDB行鎖✅ ACIDOLTP、核心業務(預設)
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(表級,表示意圖加行鎖)

鎖優化原則

  1. 所有查詢**走索引**,避免全表掃描升級表鎖
  2. 索引設計精準,縮小鎖定範圍
  3. 減少範圍查詢,降低 Gap Lock 影響
  4. 控制事務大小:小事務、短持鎖時間
  5. 業務允許時降低隔離級別(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 關鍵欄位

欄位關鍵值含義
typesystem > const > eq_ref > ref > range > index > ALL訪問類型,越左越好
key實際使用索引NULL 表示全表掃描
rows預估掃描行數越小越好
ExtraUsing 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 索引

排序優化

兩種算法

  1. 雙路排序:取排序欄位 + 行指針 → Sort Buffer 排序 → 回表取其他欄位
  2. 單路排序:一次取所有欄位 → 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 設計原則

  1. 適度冗余:減少 JOIN,如冗存商品名到訂單表
  2. 大字段垂直拆分:TEXT/BLOB/JSON 移至擴展表
  3. 大表水平分片:按時間/用戶 ID 分表分庫
  4. 時間類型選擇:TIMESTAMP (4B, 1970-2038) vs DATETIME (8B, 1001-9999)

Binlog 優化

參數建議說明
sync_binlog1(金融) / 100-1000(高併發)每 N 次提交 fsync,平衡安全與效能
binlog_cache_size4-16M事務 Binlog 緩衝,大事務調大
max_binlog_size1G單文件上限,超過自動切換
binlog_formatROW主從一致性最佳,支援並行複寫
expire_logs_days7自動清理,防止磁碟滿

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。

copyright © 萬盛學電腦網 all rights reserved