文章目錄
- 一、事務篇（必考重點）
- 1.1 事務四大特性（ACID）
- 1.2 事務實戰技巧
- 二、索引優化大法
- 2.1 索引類型全家福
- 2.2 EXPLAIN命令實戰
- 三、存儲引擎選型指南
- 3.1 InnoDB vs MyISAM 終極對決
- 四、SQL優化實戰手冊
- 4.1 慢查詢七宗罪
- 4.2 分頁優化黑科技
- 五、鎖機制深度解析
- 5.1 鎖類型大全
- 六、高頻靈魂拷問
- Q：CHAR和VARCHAR的區別？
- Q：為什么推薦自增主鍵？
- Q：大表ALTER操作卡死怎么辦？
- 七、性能優化三板斧
- 八、最新趨勢觀察
- 實戰建議
一、事務篇（必考重點）
1.1 事務四大特性（ACID）
這個知識點簡直是面試官的「必殺技」（劃重點）！！！四個字母分別代表：
- 原子性（Atomicity）：事務要么全成功，要么全失敗，不存在中間態（就像轉賬要么成功要么失敗）
- 一致性（Consistency）：數據在事務前后必須合法（比如賬戶余額不能為負數）
- 隔離性（Isolation）：多個事務并發執行互不干擾
- 持久性（Durability）：事務提交后數據永久保存
（超級重要）面試官最愛追問隔離級別問題！記住這四個級別：
- 讀未提交（可能讀到臟數據）
- 讀已提交（Oracle默認）
- 可重復讀（MySQL默認）
- 串行化（性能最差）
1.2 事務實戰技巧
-- 典型的事務代碼結構
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT; -- 或 ROLLBACK
（踩坑警告）忘記設置隔離級別導致幻讀問題，是新手常見錯誤！
二、索引優化大法
2.1 索引類型全家福
- B+Tree索引（默認選手）
- 哈希索引（精準匹配快但范圍查詢跪）
- 全文索引（文本搜索專用）
- 空間索引（GIS數據處理）
（血淚教訓）索引不是越多越好！每個額外索引都會：
- 增加存儲空間
- 降低寫操作速度
- 可能導致優化器選擇錯誤執行計劃
2.2 EXPLAIN命令實戰
EXPLAIN SELECT * FROM users WHERE age > 18;
關鍵指標三劍客：
- type列：ALL（全表掃描）→ index → range → ref → const
- key列：實際使用的索引
- rows列：預估掃描行數
（性能殺手預警）看到Using filesort或Using temporary趕緊優化！
三、存儲引擎選型指南
3.1 InnoDB vs MyISAM 終極對決
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事務支持 | ? | ? |
| 行級鎖 | ? | 表級鎖 |
| 外鍵 | ? | ? |
| 崩潰恢復 | 強 | 弱 |
| 全文索引 | ?(5.6+) | ? |
（2023最新趨勢）現在默認都用InnoDB！MyISAM只適合讀多寫少的日志表
四、SQL優化實戰手冊
4.1 慢查詢七宗罪
SELECT *全字段查詢- 濫用子查詢
- 函數處理索引字段
- 類型轉換導致索引失效
- OR條件使用不當
- 聯合索引順序錯誤
- LIMIT分頁深度過大
4.2 分頁優化黑科技
傳統分頁：
SELECT * FROM table LIMIT 1000000, 10; -- 性能暴擊！
優化方案：
SELECT * FROM table
WHERE id > 上一頁最后ID
ORDER BY id
LIMIT 10;
五、鎖機制深度解析
5.1 鎖類型大全
- 共享鎖(S鎖)：
SELECT ... LOCK IN SHARE MODE - 排他鎖(X鎖)：
SELECT ... FOR UPDATE - 意向鎖：解決行鎖與表鎖沖突
- 記錄鎖：鎖定單行記錄
- 間隙鎖：解決幻讀問題
- 臨鍵鎖：記錄鎖+間隙鎖組合
（死鎖現場）兩個事務互相等待對方釋放鎖時就會觸發！可以通過SHOW ENGINE INNODB STATUS查看死鎖日志
六、高頻靈魂拷問
Q：CHAR和VARCHAR的區別？
A：CHAR定長（適合存儲固定長度如身份證號），VARCHAR變長（適合長度變化大的數據）。CHAR末尾空格會被去除，VARCHAR會保留
Q：為什么推薦自增主鍵？
A：① 插入性能高 ② 減少頁分裂 ③ 緩存友好 ④ 避免業務耦合
Q：大表ALTER操作卡死怎么辦？
A：推薦使用pt-online-schema-change工具實現不停機修改表結構
七、性能優化三板斧
- 架構層：讀寫分離+分庫分表
- SQL層：慢查詢優化+索引優化
- 配置層：調整innodb_buffer_pool_size（建議設置物理內存的70%）
（監控必備）安裝Percona Monitoring and Management(PMM)，實時監控數據庫健康狀態
八、最新趨勢觀察
MySQL 8.0重磅更新：
- 窗口函數（分析函數爽到飛起）
- 通用表表達式CTE（SQL可讀性飆升）
- 隱藏索引（測試索引不影響生產）
- 原子DDL（再也不怕alter中途崩潰）
- JSON增強（支持->>操作符）
實戰建議
紙上得來終覺淺，絕知此事要躬行！推薦自己搭建MySQL環境，嘗試：
- 用sysbench做壓力測試
- 故意制造死鎖分析日志
- 體驗不同隔離級別下的并發問題
- 用pt-query-digest分析慢查詢日志
（終極提醒）面試前務必親手寫過JOIN查詢、子查詢、事務代碼，理論+實踐=offer到手！
