文章目錄
- 一、事務篇(必考重點)
- 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到手!