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

存儲引擎對比
| 特性 | InnoDB | MyISAM | Memory | Archive |
| 事務 | ✅ ACID | ❌ | ❌ | ❌ |
| 鎖粒度 | 行鎖 | 表鎖 | 表鎖 | 行鎖 |
| 外鍵 | ✅ | ❌ | ❌ | ❌ |
| 全文索引 | 5.6+ ✅ | ✅ | ❌ | ❌ |
| 壓縮 | ✅ | ✅ | ❌ | ✅ |
| 適用場景 | OLTP、高併發 | 只讀/日志 | 臨時表/緩存 | 歸檔/審計 |
索引篇
B+ 樹索引特性
- 葉子節點鏈表結構,支持範圍掃描、ORDER BY、GROUP BY 下推
- 聚集索引:主鍵 B+ 樹葉子節點存全行數據
- 二級索引:葉子節點存主鍵值,查詢需回表(覆蓋索引除外)
索引設計三大原則
- 單行訪問慢:盡量讓一個頁包含更多所需行
- 順序訪問快:利用索引順序避免排序、滿足 GROUP BY
- 覆蓋索引最快:查詢列全部在索引中,避免回表
多列索引列序決策:三星系統
- ⭐ 將相關行聚集(WHERE 條件列靠左)
- ⭐ 索引順序 = 查詢排序順序(避免 filesort)
- ⭐ 索引包含所有查詢列(覆蓋索引)
實際妥協:高選擇性列優先、等值條件在前、範圍條件在後、排序列靠後。
前綴索引選擇性計算
-- 總選擇性
SELECT COUNT(DISTINCT col) / COUNT(*) FROM t;
-- 前綴長度 n 選擇性
SELECT COUNT(DISTINCT LEFT(col, n)) / COUNT(*) FROM t;
-- 當比例接近總選擇性時,n 即為最佳前綴長度
索引維護三板斧
CHECK TABLE / REPAIR TABLE / ALTER TABLE ... ENGINE=InnoDB 修復損壞
ANALYZE TABLE 更新統計信息(Cardinality)
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
| 級別 | 髒讀 | 不可重複讀 | 幻讀 | 實現 |
| READ UNCOMMITTED | ✅ | ✅ | ✅ | 無鎖,最快 |
| READ COMMITTED | ❌ | ✅ | ✅ | Read View 每語句創建 |
| REPEATABLE READ (默認) | ❌ | ❌ | ❌ (Next-Key Lock) | Read View 事務首次 SELECT 創建 |
| SERIALIZABLE | ❌ | ❌ | ❌ | 全表鎖/間隙鎖,並發最低 |
生產環境優化清單
- 硬件:NVMe SSD、大內存(Buffer Pool ≥ 熱數據)、CPU 高主頻
- 配置:
innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit=1、sync_binlog=1
- Schema:最小化類型、NOT NULL、避免 TEXT/BLOB 放主表
- 索引:定期 pt-duplicate-key-checker、pt-index-usage 清理冗余/未用索引
- SQL:開發階段 EXPLAIN 必跑、Code Review 針對 N+1、大事務、長鎖
- 監控:QPS、TPS、延遲分位數、連接數、緩存命中率、磁盤 I/O、主從延遲
- 備份:XtraBackup 物理熱備 + Binlog 增量,定期恢複演練
copyright ©
萬盛學電腦網 all rights reserved