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 一周全量備份

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/499799.html

標籤：MySQL

上一篇：資料庫持久化+JDBC資料庫連接

下一篇：SQL陳述句的整合

MySQL實戰45講 1,2