萬盛學電腦網

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

高性能 MySQL 核心筆記:架構、索引、查詢優化與維護實戰總結

MySQL 邏輯架構三層模型

┌─────────────────────────────────────┐
│ 連接池 / 線程處理 / 授權 / 安全        │  ← 第一層:連接管理
├─────────────────────────────────────┤
│ 查詢緩存 / 解析器 / 優化器 / 內置函數   │  ← 第二層:SQL 處理核心
│ 存儲過程 / 觸發器 / 視圖               │
├─────────────────────────────────────┤
│ 存儲引擎層 (InnoDB, MyISAM, Memory...) │  ← 第三層:存儲引擎 API
└─────────────────────────────────────┘

MySQL 邏輯架構圖

存儲引擎對比

特性InnoDBMyISAMMemoryArchive
事務✅ ACID❌❌❌
鎖粒度行鎖表鎖表鎖行鎖
外鍵✅❌❌❌
全文索引5.6+ ✅✅❌❌
壓縮✅✅❌✅
適用場景OLTP、高併發只讀/日志臨時表/緩存歸檔/審計

索引篇

B+ 樹索引特性

  • 葉子節點鏈表結構,支持範圍掃描、ORDER BY、GROUP BY 下推
  • 聚集索引:主鍵 B+ 樹葉子節點存全行數據
  • 二級索引:葉子節點存主鍵值,查詢需回表(覆蓋索引除外)

索引設計三大原則

  1. 單行訪問慢:盡量讓一個頁包含更多所需行
  2. 順序訪問快:利用索引順序避免排序、滿足 GROUP BY
  3. 覆蓋索引最快:查詢列全部在索引中,避免回表

多列索引列序決策:三星系統

  • ⭐ 將相關行聚集(WHERE 條件列靠左)
  • ⭐ 索引順序 = 查詢排序順序(避免 filesort)
  • ⭐ 索引包含所有查詢列(覆蓋索引)

實際妥協:高選擇性列優先、等值條件在前、範圍條件在後、排序列靠後。

前綴索引選擇性計算

-- 總選擇性
SELECT COUNT(DISTINCT col) / COUNT(*) FROM t;

-- 前綴長度 n 選擇性
SELECT COUNT(DISTINCT LEFT(col, n)) / COUNT(*) FROM t;
-- 當比例接近總選擇性時,n 即為最佳前綴長度

索引維護三板斧

  1. CHECK TABLE / REPAIR TABLE / ALTER TABLE ... ENGINE=InnoDB 修復損壞
  2. ANALYZE TABLE 更新統計信息(Cardinality)
  3. OPTIMIZE TABLE / ALTER TABLE ... ENGINE=InnoDB 整理碎片

查詢性能優化篇

慢查詢三大指標

  • 響應時間 = 服務時間 + 排隊時間(I/O、鎖等待)
  • 掃描行數:EXPLAIN rows、Handler_read_* 狀態變量
  • 返回行數:與掃描行數比值反映效率

訪問類型優劣排序(EXPLAIN type)

ALL (全表掃描) > index (全索引掃描) > range (範圍) > ref (非唯一索引查找)
> eq_ref (唯一索引查找) > const (常數) > system (系統表) > NULL (無需訪問表)

常見優化模式

反模式優化方案
SELECT *顯式列出所需列,利用覆蓋索引
未命中索引 / 全表掃描添加組合索引、調整 WHERE 順序、使用 FORCE INDEX
範圍條件阻斷後續索引列將範圍列放到組合索引最後;或將範圍轉為 IN 列表
分頁深度大 LIMIT 100000, 20延遲關聯:SELECT * FROM t INNER JOIN (SELECT id FROM t ORDER BY x LIMIT 100000, 20) d USING(id)
OR 條件導致索引失效UNION ALL 分拆、或建立聯合索引
隱式類型轉換字段類型與參數類型一致,避免 VARCHAR vs INT

慢查詢分析工具鏈

  • pt-query-digest:分析 Slow Log / General Log / tcpdump 抓包
  • EXPLAIN ANALYZE (8.0.18+):實際執行計劃與成本
  • Performance Schema:events_statements_summary_by_digest
  • Sys Schema:statement_analysis 視圖

並發與鎖機制

  • 共享鎖 (S):SELECT ... LOCK IN SHARE MODE
  • 排他鎖 (X):SELECT ... FOR UPDATE / UPDATE / DELETE
  • 意向鎖 (IS/IX):表級標識行鎖意圖
  • 間隙鎖 / Next-Key Lock:RR 隔離級防止幻讀
  • 死鎖檢測:InnoDB 自動回滾代價較小的事務

事務隔離級別與 MVCC

生產環境優化清單

  1. 硬件:NVMe SSD、大內存(Buffer Pool ≥ 熱數據)、CPU 高主頻
  2. 配置:innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit=1、sync_binlog=1
  3. Schema:最小化類型、NOT NULL、避免 TEXT/BLOB 放主表
  4. 索引:定期 pt-duplicate-key-checker、pt-index-usage 清理冗余/未用索引
  5. SQL:開發階段 EXPLAIN 必跑、Code Review 針對 N+1、大事務、長鎖
  6. 監控:QPS、TPS、延遲分位數、連接數、緩存命中率、磁盤 I/O、主從延遲
  7. 備份:XtraBackup 物理熱備 + Binlog 增量,定期恢複演練
copyright © 萬盛學電腦網 all rights reserved
級別髒讀不可重複讀幻讀實現
READ UNCOMMITTED✅✅✅無鎖,最快
READ COMMITTED❌✅✅Read View 每語句創建
REPEATABLE READ (默認)❌❌❌ (Next-Key Lock)Read View 事務首次 SELECT 創建
SERIALIZABLE❌❌❌全表鎖/間隙鎖,並發最低