01 | 基礎架構：一條SQL查詢語句是如何執行的？ Server 層所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖等。存儲引擎層負責數據的存儲和提取。其架構模式是插件式的,在 create table 語句中使用 engine=memory, 來指定使用記憶體引擎創建表連接器 ...

01 | 基礎架構：一條SQL查詢語句是如何執行的？

Server 層所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖等。

存儲引擎層負責數據的存儲和提取。其架構模式是插件式的,在 create table 語句中使用 engine=memory, 來指定使用記憶體引擎創建表

連接器

如果用戶名密碼認證通過，連接器會到許可權表裡面查出你擁有的許可權。之後，這個連接裡面的許可權判斷邏輯，都將依賴於此時讀到的許可權。

許可權表：

https://www.php.cn/mysql-tutorials-493289.html

大致就是mysql庫中的user表和db表

db 表中的許可權列和 user 表中的許可權列大致相同，只是user 表中的許可權是針對所有資料庫的，而 db 表中的許可權只針對指定的資料庫。如果希望用戶只對某個資料庫有操作許可權，可以先將 user 表中對應的許可權設置為 N，然後在 db 表中設置對應資料庫的操作許可權。

連接完成後，如果你沒有後續的動作，這個連接就處於空閑狀態，你可以在 show processlist 命令中看到它。Command 列顯示為“Sleep”的這一行，就表示現在系統裡面有一個空閑連接。客戶端如果太長時間沒動靜，連接器就會自動將它斷開。

建立連接的過程通常是比較複雜的，儘量使用長連接。

但是全部使用長連接後，你可能會發現，有些時候 MySQL 占用記憶體漲得特別快，這是因為 MySQL 在執行過程中臨時使用的記憶體是管理在連接對象裡面的。這些資源會在連接斷開的時候才釋放。所以如果長連接累積下來，可能導致記憶體占用太大，被系統強行殺掉（OOM），從現象看就是 MySQL 異常重啟了。

兩種解決方案：

重連 or 重置連接

定期斷開長連接。使用一段時間，或者程式裡面判斷執行過一個占用記憶體的大查詢後，斷開連接，之後要查詢再重連。
如果你用的是 MySQL 5.7 或更新版本，可以在每次執行一個比較大的操作後，通過執行 mysql_reset_connection 來重新初始化連接資源。這個過程不需要重連和重新做許可權驗證，但是會將連接恢復到剛剛創建完時的狀態。

查詢緩存

不要使用查詢緩存

查詢緩存的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢緩存都會被清空。

MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉

分析器

MySQL 需要知道你要做什麼，因此需要對 SQL 語句做解析

優化器

優化器是在表裡面有多個索引的時候，決定使用哪個索引；

或者在一個語句有多表關聯（join）的時候，決定各個表的連接順序

優化器階段完成後，這個語句的執行方案就確定下來，通過優化器知道了該怎麼做

執行器

開始執行的時候，要先判斷一下你對這個表 T 有沒有執行查詢的許可權，如果沒有，就會返回沒有許可權的錯誤

Q：為什麼對許可權的檢查不在優化器之前做？

A：有些時候，SQL語句要操作的表不只是SQL字面上那些。比如如果有個觸發器，得在執行器階段（過程中）才能確定。優化器階段前是無能為力的

如果有許可權，就打開表繼續執行。

打開表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的介面。

select * from T where ID=10;

比如我們這個例子中的表 T 中，ID 欄位沒有索引，那麼執行器的執行流程是這樣的：

調用 InnoDB 引擎介面取這個表的第一行，判斷 ID 值是不是 10，如果不是則跳過，如果是則將這行存在結果集中；
調用引擎介面取“下一行”，重覆相同的判斷邏輯，直到取到這個表的最後一行。
執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

對於有索引的表，執行的邏輯也差不多。

第一次調用的是“取滿足條件的第一行”這個介面，之後迴圈取“滿足條件的下一行”這個介面，這些介面都是引擎中已經定義好的。

MySQL 的慢查詢日誌是 MySQL 提供的一種日誌記錄，它用來記錄在 MySQL 中響應時間超過閥值的語句，具體指運行時間超過 long_query_time 值的SQL，則會被記錄到慢查詢日誌中。

# 查看慢查詢日誌是否開啟，預設情況下關閉
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/iZ251fpy8x9Z-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

https://blog.csdn.net/chengqiuming/article/details/120402562

Q：如果表 T 中沒有欄位 k，而你執行了這個語句 select * from T where k=1, 那肯定是會報“不存在這個列”的錯誤： “Unknown column ‘k’ in ‘where clause’”。這個錯誤是在哪個階段報出來的呢？

A：分析器。Oracle會在分析階段判斷語句是否正確，表是否存在，列是否存在等，MySQL確實在設計上受Oracle影響頗深。

02 | 日誌系統：一條SQL更新語句是如何執行的？

與查詢流程不一樣的是，更新流程還涉及兩個重要的日誌模塊：redo log（重做日誌）和 binlog（歸檔日誌）

https://blog.csdn.net/weixin_51261234/article/details/124908426

https://blog.csdn.net/qq_38686500/article/details/124584693

REDO LOG

1.2.1 好處

先在粉板上記下這次的賬，等打烊以後再把賬本翻出來核算。

在 MySQL 里也有這個問題，如果每一次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程 IO 成本、查找成本都很高。為瞭解決這個問題，MySQL 的設計者就用了類似酒店掌柜粉板的思路來提升更新效率。

redo日誌降低了刷盤頻率
redo日誌占用的空間非常小

存儲表空間ID，頁號，偏移量以及需要更新的值，所需的存儲空間是很小的，刷盤快。

1.2.2 WAL

WAL 技術，WAL 的全稱是 Write-Ahead Logging，它的關鍵點就是先寫日誌，再在系統比較空閑的時候寫磁碟。只有日誌寫入成功，才算是事務提交成功。

當發生宕機且數據未刷新到磁碟的時候，可以通過redo log來恢復，保證ACID中的D，這就是redo log的作用。

1.2.3 特點

redo日誌是順序寫入磁碟的

在執行事務的過程中，每執行一條語句，就可能產生若幹條redo日誌，這些日誌是按照產生的順序寫入磁碟的，也就是說使用順序IO，效率比隨機IO快

事務執行過程中，redo log不斷記錄

redo log跟bin log的區別

redo log是存儲引擎層產生的，而bin log是資料庫層產生的。假設一個事務，對錶做十萬行的記錄插入，在這個過程中，一直不斷的往redo log順序記錄，記錄的是頁面的變化，而bin log不會記錄，直到這個事務提交，才會一次寫入到bin log文件中。

redo log是物理日誌，記錄的是在具體某個數據頁上做了什麼修改，做了什麼改動；

binlog是邏輯日誌，有三種模式，statement 格式的話是記sql語句， row格式會記錄行的內容，記兩條，更新前和更新後都有，mixed根據sql語句特點，由系統決定某個修改使用row還是statement格式進行存儲。

若sql語句可能引起主備不一致，那麼使用row格式，否則使用statement格式。

1.2.4 組成

Redo log可以簡單分為以下兩個部分：

重做日誌的緩衝 (redo log buffer) ，保存在記憶體中，是易失的。
重做日誌文件 (redo log file) ，保存在硬碟中，是持久的。

InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個文件，每個文件的大小是 1GB，那麼這塊“粉板”總共就可以記錄 4GB 的操作。從頭開始寫，寫到末尾就又回到開頭迴圈寫，如下麵這個圖所示。

write pos 是當前記錄的位置，一邊寫一邊後移，寫到第 3 號文件末尾後就回到 0 號文件開頭。checkpoint 是當前要擦除的位置，也是往後推移並且迴圈的，擦除記錄前要把記錄更新到數據文件。

write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來記錄新的操作。如果 write pos 追上 checkpoint，表示“粉板”滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進一下

1.2.5 整體流程

以一個更新事務為例，redo log 流轉過程，如下圖所示：

第1步：先將原始數據從磁碟中讀入記憶體中來，修改數據的記憶體拷貝

第2步：生成一條重做日誌並寫入redo log buffer，記錄的是數據被修改後的值

第3步：當事務commit時，將redo log buffer中的內容刷新到 redo log file，對 redo log file採用追加寫的方式

第4步：定期將記憶體中修改的數據刷新到磁碟中

1.2.6 刷盤策略

redo log的寫入並不是直接寫入磁碟的，InnoDB引擎會在寫redo log的時候先寫redo log buffer，之後以 一定的頻率 刷入到真正的redo log file 中。這裡的一定頻率怎麼看待呢？這就是我們要說的刷盤策略。

註意，redo log buffer刷盤到redo log file的過程並不是真正的刷到磁碟中去，只是刷入到 文件系統緩存（page cache）中去，真正的寫入會交給系統自己來決定（比如page cache足夠大了）。那麼對於InnoDB來說就存在一個問題，如果交給系統來同步，同樣如果系統宕機，那麼數據也丟失了（雖然整個系統宕機的概率還是比較小的）。

針對這種情況，InnoDB給出 innodb_flush_log_at_trx_commit 參數，該參數控制 commit提交事務時，如何將 redo log buffer 中的日誌刷新到 redo log file 中。它支持三種策略：

設置為0 ：表示每次事務提交時不進行刷盤操作。（系統預設master thread每隔1s進行一次重做日誌的同步）實例crash最多丟失一秒鐘內的事務
設置為1 ：表示每次事務提交時都將進行同步，刷盤操作（預設值）可以保證ACID的D，數據絕對不會丟失，但是效率最差的
設置為2 ：表示每次事務提交時都只把 redo log buffer 內容寫入 page cache，不進行同步。由os自己決定什麼時候同步到磁碟文件如果僅僅只是MySQL掛了不會有任何數據的丟失。但是操作系統宕機可能會有一秒數據的丟失，這種情況下無法滿足ACID中的D

InnoDB存儲引擎有一個後臺線程，每隔一秒，就會把redo log buffer中的內容寫到文件系統緩存（page cache），然後調用刷盤操作。也就是說，一個沒有提交事務的redo log記錄，也可能刷盤。因為在事務執行過程中redo log記錄是會寫入redo log buffer中，這些redo log記錄會被後臺線程刷盤

除了後臺線程每秒1次輪詢操作，還有一種情況，當redo log buffer占用的空間即將達到innodb_log_buffer_size（這個參數預設是16M）的一半的時候，後臺線程會主動刷盤。

BINLOG

歸檔日誌

執行器和 InnoDB 引擎在執行這個簡單的 update 語句時的內部流程

執行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數據頁本來就在記憶體中，就直接返回給執行器；否則，需要先從磁碟讀入記憶體，然後再返回。
執行器拿到引擎給的行數據，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行數據，再調用引擎介面寫入這行新數據。
引擎將這行新數據更新到記憶體中，同時將這個更新操作記錄到 redo log 裡面，此時 redo log 處於 prepare 狀態。然後告知執行器執行完成了，隨時可以提交事務。
執行器生成這個操作的 binlog，並把 binlog 寫入磁碟。
執行器調用引擎的提交事務介面，引擎把剛剛寫入的 redo log 改成提交（commit）狀態，更新完成。

圖中淺色框表示是在 InnoDB 內部執行的，深色框表示是在執行器中執行的。

將 redo log 的寫入拆成了兩個步驟：prepare 和 commit，這就是"兩階段提交"。

bin log在之間寫

兩階段提交

為什麼日誌需要“兩階段提交”

反證法：

先寫 redo log 後寫 binlog。在寫完redo log後，寫binlog的時候發生 crash。資料庫恢復回來後，數據沒丟失，因為能根據redo log 恢復回來，但是這個操作卻少了一個 binlog。而在進行資料庫備份的時候使用的是binlog，所以備份的數據裡面就丟失了這次更改，以後在使用這個備份恢復的時候，自然恢復回來的數據就不對。
先寫 binlog 後寫 redo log。如果在 binlog 寫完之後 crash，由於 redo log 還沒寫，崩潰恢復以後這個事務無效。但是 binlog 裡面已經記錄了日誌，所以，在之後用 binlog 來恢復的時候就多了一個事務出來，恢復出來的這一行 c 的值就是 1，與原庫的值不同。

redolog和binlog具有關聯行，在恢複數據時，redolog用於恢復主機故障時的未更新的物理數據，binlog用於備份操作。每個階段的log操作都是記錄在磁碟的，在恢複數據時，redolog 狀態為commit則說明binlog也成功，直接恢複數據；如果redolog是prepare，則需要查詢對應的binlog事務是否成功，決定是回滾還是執行。

簡單說，redo log 和 binlog 都可以用於表示事務的提交狀態，而兩階段提交就是讓這兩個狀態保持邏輯上的一致。

Q：定期全量備份的周期“取決於系統重要性，有的是一天一備，有的是一周一備”。那麼在什麼場景下，一天一備會比一周一備更有優勢呢？或者說，它影響了這個資料庫系統的哪個指標？

A：一天一備binlog比較小，恢復時間比較短一般做法是從庫做延遲複製，binlog 一周全量備份

MySQL實戰45講 1,2