一、MySQL基礎架構

1. MySQL邏輯架構

MySQL采用分層架構設計，主要分為：

• 連接層：處理客戶端連接、授權認證等

• 服務層：包含查詢解析、分析、優化、緩存等

• 引擎層：負責數據存儲和提取（InnoDB、MyISAM等）

2. 查詢執行流程

1. 客戶端發送SQL語句

2. 連接器驗證身份

3. 查詢緩存（MySQL 8.0已移除）

4. 分析器進行詞法語法分析

5. 優化器生成執行計劃

6. 執行器調用存儲引擎接口執行

二、存儲引擎對比

InnoDB vs MyISAM

特性	InnoDB	MyISAM
事務支持	支持	不支持
鎖粒度	行鎖	表鎖
外鍵	支持	不支持
崩潰恢復	支持	不支持
全文索引	MySQL 5.6+支持	支持
存儲文件	.frm, .ibd	.frm, .MYD, .MYI
適合場景	高并發寫/事務型應用	讀多寫少/非事務應用

三、索引原理與優化

1. 索引類型

• B+樹索引：最常用，適合范圍查詢

• 哈希索引：精確匹配快，不支持范圍查詢

• 全文索引：用于文本搜索

• 空間索引：用于地理數據

2. B+樹索引特點

• 多路平衡查找樹

• 非葉子節點只存鍵值

• 葉子節點形成有序鏈表

• 通常3-4層就能存儲大量數據

3. 索引優化原則

1. 最左前綴原則

2. 避免在索引列上使用函數

3. 選擇合適的索引列順序

4. 使用覆蓋索引減少回表

5. 避免過度索引

四、事務與鎖機制

1. 事務特性（ACID）

• 原子性(Atomicity)：事務不可分割

• 一致性(Consistency)：數據狀態一致

• 隔離性(Isolation)：事務間相互隔離

• 持久性(Durability)：提交后永久生效

2. 事務隔離級別

隔離級別	臟讀	不可重復讀	幻讀
READ UNCOMMITTED	?	?	?
READ COMMITTED	×	?	?
REPEATABLE READ	×	×	?
SERIALIZABLE	×	×	×

3. 鎖類型

• 共享鎖(S鎖)：讀鎖，多個事務可同時持有

• 排他鎖(X鎖)：寫鎖，獨占資源

• 意向鎖：表級鎖，表明事務將要獲取的行鎖類型

• 間隙鎖：鎖定索引記錄間隙，防止幻讀

• 臨鍵鎖：記錄鎖+間隙鎖組合

五、SQL優化技巧

1. EXPLAIN執行計劃分析

關鍵字段：

• type：訪問類型（const > eq_ref > ref > range > index > ALL）

• key：實際使用的索引

• rows：預估掃描行數

• Extra：額外信息（Using index/Using filesort等）

2. 常見優化方法

1. 避免SELECT *，只查詢需要的列

2. 合理使用JOIN，小表驅動大表

3. 避免在WHERE子句中對字段進行NULL值判斷

4. 使用LIMIT分頁時優化大偏移量查詢

5. 避免使用OR連接條件，考慮使用UNION ALL

六、高可用與性能調優

1. 主從復制原理

1. 主庫將變更寫入binlog

2. 從庫IO線程讀取主庫binlog

3. 從庫SQL線程重放binlog中的事件

2. 分庫分表策略

• 垂直拆分：按業務維度拆分

• 水平拆分：按數據行拆分

• 常見中間件：MyCat、ShardingSphere

3. 性能調優參數

ini

復制

下載

# 緩沖池大小（推薦總內存的50-70%）
innodb_buffer_pool_size = 4G# 日志文件大小
innodb_log_file_size = 256M# 連接數設置
max_connections = 500
thread_cache_size = 50# 查詢緩存（MySQL 8.0已移除）
query_cache_size = 0

七、常見面試題

1. 為什么使用B+樹而不是B樹？

   • B+樹非葉子節點不存數據，能容納更多鍵值

   • 葉子節點形成鏈表，范圍查詢更高效

   • 查詢性能更穩定（任何查詢都要到葉子節點）

2. 什么是回表查詢？如何避免？

   • 回表：通過二級索引查到主鍵后，再通過主鍵查完整數據

   • 避免：使用覆蓋索引（查詢列都在索引中）

3. MVCC實現原理？

   • 通過版本鏈和ReadView實現

   • 每行記錄有隱藏字段：DB_TRX_ID（事務ID）、DB_ROLL_PTR（回滾指針）

   • ReadView包含：m_ids（活躍事務列表）、min_trx_id、max_trx_id等

4. 大表優化方案？

   • 分庫分表

   • 讀寫分離

   • 冷熱數據分離

   • 適當增加冗余字段減少JOIN

5. 如何解決死鎖問題？

   • 設置鎖等待超時參數：innodb_lock_wait_timeout

   • 分析死鎖日志（show engine innodb status）

   • 保證事務中鎖的獲取順序一致

   • 盡量縮小事務范圍