mysql 學習筆記

目錄

一 基礎架構

Server 層

存盤引擎層

二 日志系統

三 事務隔離

四 索引

五 鎖

全域鎖

表級鎖

行鎖

六 一致性讀、當前讀、幻讀

一致性讀

當前讀

幻讀

一 基礎架構

一般來說，MySQL 可以分為 Server 層和存盤引擎層兩部分，

Server 層

Server 層主要由連接器、查詢快取、分析器、優化器、執行器構成，

連接器

主要功能是管理連接、權限驗證，

如何管理連接？

盡量使用長連接，定期斷開長連接，比如8小時斷開連接，如果想查看連接狀態使用：show processlist，

什么是長連接和短連接？

長連接是指連接成功后，如果客戶端持續有請求，則一直使用同一個連接，

短連接是指每次執行完很少的幾次查詢就斷開連接，下次查詢再重新建立一個，

查詢快取

主要功能是快取資料，命中直接回傳結果，不建議使用查詢快取，

為什么不建議使用查詢快取？

一旦表有更新，查詢快取就被清空，MySQL 8.0 已經去掉了查詢快取功能，

分析器

對 SQL 陳述句進行詞法分析、語法分析，明確 SQL 陳述句符合 Mysql 詞法和語法規范，

優化器

生成執行計劃，索引選擇，最優執行路徑選擇，說白了就是有 N 調執行路徑，選擇最優路徑，

執行器

與存盤引擎互動，對資料進行讀寫操作，

存盤引擎層

存盤引擎層負責資料的存盤和提取，

整體來說 MySQL 各個組件，邊界清晰，權責分明，那查詢陳述句和更新陳述句是怎么樣執行的呢？

簡單來說：

1. 連接器建立連接通道；
2. 分析器分析你要做什么；
3. 優化器指導你要怎么做；
4. 執行器是執行做這個動作；
5. 存盤引擎層執行資料讀寫，

二 日志系統

Mysql 每一次的更新操作都需要寫進磁盤，然后磁盤也要找到對應的那條記錄，然后再更新，整個程序 IO 成本、查找成本都很高，

如何解決 IO 成本、查找成本高的問題？

使用 WAL 技術來解決問題，WAL 的全稱是 Write-Ahead Logging，就是先寫日志，再寫磁盤，

redo log：重做日志，InnoDB 就可以保證即使資料庫發生例外重啟，之前提交的記錄都不會丟失，這個能力稱為 crash-safe，

binlog：歸檔日志，對 sql 陳述句進行歸檔，

為什么有兩個日志？

最開始 MySQL 里并沒有 InnoDB 引擎，MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒有 crash-safe 的能力，binlog 日志只能用于歸檔，

InnoDB 是以插件形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用 redo log 來實作 crash-safe 能力，

兩個日志有什么不同？

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實作的，所有引擎都可以使用，
2. redo log 是物理日志，記錄的是“在某個資料頁上做了什么修改”；binlog 是邏輯日志，記錄的是這個陳述句的原始邏輯，比如“給 ID=2 這一行的 c 欄位加 1 ”，
3. redo log 是回圈寫的，空間固定會用完；binlog 是可以追加寫入的，“追加寫”是指 binlog 檔案寫到一定大小后會切換到下一個，并不會覆寫以前的日志，

什么是兩階段提交？

1. 更新一條資料的時候，存盤引擎將更新記錄寫到 redo log 里面，此時 redo log 處于 prepare 狀態，通知執行器我這塊已經 ok 了，隨時能提交事務，
2. 執行器生成更新操作的 binlog，并將日志寫入磁盤，
3. 執行器通知存盤引擎可以提交了，存盤引擎將 redo log 更新成 commit 狀態，

兩階段提交是為了保證通過歸檔日志恢復的資料和真實資料庫的資料保證一致，

三 事務隔離

什么是事務？

事務是一組操作，要么成功，要么失敗，

多個事務同時執行？

有多個事務同時執行的時候，就可能出現臟讀（dirty read）、不可重復讀（non-repeatable read）、幻讀（phantom read）的問題，

如何解決多個事務執行出現的各種問題？

引入事務隔離，事務隔離級別包括：讀未提交（read uncommitted）、讀提交（read committed）、可重復讀（repeatable read）和串行化（serializable ），

讀未提交：一個事務還沒提交時，它做的變更就能被別的事務看到，

讀提交：一個事務提交之后，它做的變更才會被其他事務看到，

可重復讀：一個事務執行程序中看到的資料，總是跟這個事務在啟動時看到的資料是一致的，當然在可重復讀隔離級別下，未提交變更對其他事務也是不可見的，

串行化：顧名思義是對于同一行記錄，“寫”會加“寫鎖”，“讀”會加“讀鎖”，當出現讀寫鎖沖突的時候，后訪問的事務必須等前一個事務執行完成，才能繼續執行，

oracle 資料庫的默認隔離級別是“讀提交”，mysql 資料庫的默認隔離級別是 “可重復讀”，

事務隔離怎么實作呢？

在 MySQL 中，實際上每條記錄在更新的時候都會同時記錄一潭訓滾操作，記錄上的最新值，通過回滾操作，都可以得到前一個狀態的值，

不同時刻啟動的事務會有不同的視圖，同一條記錄在系統中可以存在多個版本，就是資料庫的多版本并發控制（MVCC），

四 索引

怎么理解索引？

索引的出現其實就是為了提高資料查詢的效率，就像書的目錄一樣，

索引怎么實作？

二叉搜索樹的特點是：父節點左子樹所有結點的值小于父節點的值，右子樹所有結點的值大于父節點的值，

樹可以有二叉，也可以有多叉，多叉樹就是每個節點有多個兒子，兒子之間的大小保證從左到右遞增，這個多叉樹就是 B+ 樹，

每一個索引在 InnoDB 里面對應一棵 B+ 樹，N 個索引對應 N 棵 B+ 樹，

根據葉子節點的內容，索引型別分為主鍵索引和非主鍵索引：

在 InnoDB 里，主鍵索引也被稱為聚簇索引（clustered index），主鍵索引的葉子節點存的是整行資料，

在 InnoDB 里，非主鍵索引也被稱為二級索引（secondary index），非主鍵索引的葉子節點內容是主鍵的值，

由于非主鍵索引存盤的是主鍵的值，所以針對非主鍵索引的查詢，需要查詢主鍵索引，也就是回表，比主鍵查詢多查一棵樹，

五 鎖

根據加鎖的范圍，MySQL 里面的鎖大致可以分成全域鎖、表級鎖和行鎖三類，

全域鎖

顧名思義，就是對整個資料庫實體加鎖，MySQL 提供了一個加全域讀鎖的方法，命令是 Flush tables with read lock (FTWRL)，

全域鎖的典型使用場景是，做全庫邏輯備份，

表級鎖

MySQL 里面表級別的鎖有兩種：一種是表鎖，一種是元資料鎖（meta data lock，MDL)，

MDL（metadata lock)，

不需要顯式使用，在訪問一個表的時候會被自動加上，MDL 的作用是，保證讀寫的正確性，

在 MySQL 5.5 版本中引入了 MDL，當對一個表做增刪改查操作的時候，加 MDL 讀鎖；當要對表做結構變更操作的時候，加 MDL 寫鎖，

1 讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪改查，

2 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性，因此，如果有兩個執行緒要同時給一個表加欄位，其中一個要等另一個執行完才能開始執行，

行鎖

什么是兩階段鎖？

行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放，這個就是兩階段鎖協議，

六 一致性讀、當前讀、幻讀

一致性讀

1. 一致性讀，也就是一致非鎖定讀，也可以稱為快照讀，其實就是普通的讀取即普通 SELECT 陳述句，其中普通的 SELECT 操作不包括 select ... lock in share mode，select ... for update，
2. 對于可重復讀隔離級別，快斬訓在事務開始的時候生成，在本事務中對有資料變更才會更新快照，
3. 對于讀提交隔離級別，每次讀取都會重新生成一個快照，讀取只承認在陳述句啟動前就已經提交完成的資料，

一致性讀是如何實作的？

可重復讀隔離級別下，一致性讀是通過 MVCC 和 undo log 來實作的，

當前讀

1. 更新資料都是先讀后寫的，而這個讀，只能讀當前的值，稱為“當前讀”（current read），

2. select ... lock in share mode、select ... for update、insert、update、delete 都是當前讀，

以 update 為例，假設有兩個事務 A 和 B 都對同一行記錄進行變更，A 事務先啟動，后提交；B 事務后啟動，先提交，

如果不是當前讀，由于事務 A 后提交，A 的變更將導致事務 B 的變更丟失，這個是不合理的，所以肯定得當前讀，

幻讀

1. 幻讀指的是一個事務在前后兩次查詢同一個范圍的時候，后一次查詢看到了前一次查詢沒有看到的行，

2. 在可重復讀隔離級別下，普通的查詢是快照讀，是不會看到別的事務插入的資料的，幻讀只有在“當前讀”下才會出現，

幻讀是如何解決的？

當前讀出現的幻讀是通過對行記錄添加 next-key lock 來解決幻讀問題的，