MySQL 邏輯架構三層模型

┌─────────────────────────────────────┐
│  連接層 (Connection Pool)           │  驗證、授權、線程處理、SSL
├─────────────────────────────────────┤
│  服務層 (SQL Layer)                 │  解析器、優化器、快取、內建函數
│  ┌───────────────────────────────┐  │
│  │  查詢緩存 (Query Cache) 8.0移除│  │
│  └───────────────────────────────┘  │
├─────────────────────────────────────┤
│  存儲引擎層 (Storage Engine)        │  InnoDB、MyISAM、Memory、CSV...
│  ┌─────────┐ ┌─────────┐ ┌───────┐  │
│  │ InnoDB  │ │ MyISAM  │ │ ...   │  │
│  └─────────┘ └─────────┘ └───────┘  │
└─────────────────────────────────────┘

並發控制：鎖機制

鎖粒度權衡

鎖類型	別名	相容性	代表引擎
讀鎖 (共享鎖, S-lock)	Shared Lock	讀讀相容，讀寫互斥	MyISAM (表鎖)
寫鎖 (排他鎖, X-lock)	Exclusive Lock	寫寫互斥，讀寫互斥	MyISAM (表鎖)
意向鎖 (IS/IX)	Intent Lock	表級標記意圖，InnoDB 多粒度鎖核心	InnoDB
記錄鎖	Record Lock	鎖單行記錄	InnoDB
間隙鎖	Gap Lock	鎖範圍防止幻讀	InnoDB (RR隔離級)
臨鍵鎖	Next-Key Lock	記錄鎖+間隙鎖	InnoDB 預設行鎖

粒度	併發度	鎖開銷	死鎖概率	典型引擎
表鎖	低	極小	無 (串行)	MyISAM, MEMORY, CSV
頁鎖	中	中	中	BerkeleyDB
行鎖	高	大	高	InnoDB

事務與 ACID

• 原子性：Undo Log 回滾
• 一致性：約束 + 應用邏輯 + 事務原子性
• 隔離性：MVCC + 鎖，四級隔離 (RU, RC, RR, SERIALIZABLE)
• 持久性：Redo Log (WAL) + innodb_flush_log_at_trx_commit=1 + Doublewrite Buffer

主流存儲引擎對比

特性	InnoDB	MyISAM	Memory	Archive
事務	✅ ACID	❌	❌	❌
鎖粒度	行鎖	表鎖	表鎖	表鎖
外鍵	✅	❌	❌	❌
全文索引	5.6+ ✅	✅	❌	❌
壓縮	✅ (ROW_FORMAT=COMPRESSED)	✅ (myisampack)	❌	✅ (zlib)
崩潰恢復	✅ 极快	⚠️ 慢、需修復	❌ 重啟丟失	❌
熱備份	✅ (xtrabackup)	❌	❌	❌
聚簇索引	✅	❌ (堆表)	❌	❌
適用場景	OLTP 核心業務	唯讀/日誌/非關鍵	臨時表/查找表	歸檔/審計日誌

選型決策樹

需要事務/外鍵/高併發寫入？
  ├─ 是 → InnoDB (預設首選)
  └─ 否
       ├─ 唯讀/極少寫、全文搜尋 → MyISAM (或 InnoDB 全文)
       ├─ 臨時計算、查找表、生命周期短 → Memory
       ├─ 僅追加寫入、極少讀、壓縮存儲 → Archive
       └─ 不確定 → InnoDB (通用性最強)

查看與切換引擎

-- 查看表引擎
SHOW TABLE STATUS LIKE 't'\G
-- Engine: InnoDB

-- 線上轉換 (大表用 pt-online-schema-change)
ALTER TABLE t ENGINE=InnoDB;