本文屬于個人備忘錄，主要是極客時間《MySQL實戰45講》學習筆記。
MySQL實戰—更新過程
一條查詢語句的執行過程一般是經過連接配接器、分析器、優化器、執行器等功能子產品，最後到達存儲引擎。那麼更新語句又是如何執行？
和查詢流程不同的是，更新流程涉及兩個重要的日志子產品：redo log（重做日志）和 binlog（二進制日志）。
redo log
redo log通常是實體日志，記錄的是資料頁的實體修改，而不是某一行或某幾行的修改，它用來恢複送出後的實體資料頁(恢複資料頁，且隻能恢複到最後一次送出的位置)。
如果MySQL每一次的更新操作都需要寫進磁盤，整個過程IO成本會很高。使用redo log能提升更新效率，即WAL技術。
WAL
- Write-Ahead Logging
- 先寫日志，再寫磁盤
更新記錄
- InnoDB引擎先把記錄寫到redo log裡面，并更新記憶體
- InnoDB會在适當的時候，比如系統空閑時，将操作記錄更新到磁盤裡
在相同的資料量下，采用WAL的資料庫系統在事務送出時，磁盤寫操作隻有傳統的復原日志的一半左右，大大提高了資料庫磁盤IO操作的效率，進而提高了資料庫的性能。
redo log大小固定，可配
- 是InnoDB引擎的日志
- 比如一組4個檔案，每個檔案大小1GB
- 從頭開始寫，寫到末尾又回到開始循環寫
- write pos是目前記錄的位置，一邊寫一邊後移，寫到第 3 号檔案末尾後就回到 0 号檔案開頭
- checkpoint是目前要擦除的位置，也是往後推移并且循環的，擦除記錄前要把記錄更新到資料檔案
- write pos和checkpoint之間是可寫部分，用來記錄新的操作
- 如果write pos追上Checkpoint，表示沒有可寫位置了，此時不能執行新的更新，需要停下來擦掉一些記錄（将記錄更新到磁盤），将checkpoint向前推進
crash-safe
- 通過redo log，InnoDB保證即使資料庫發生異常重新開機，之前送出的記錄都不會丢失，這個能力稱為crash-safe
更新過程
以下述操作為例。
mysql> create table T(ID int primary key, c int);
mysql> update T set c=c+1 where ID=2; - 執行器通過引擎取ID=2這一行。
- ID是主鍵，引擎直接用樹搜尋找到這一行
- 如果ID=2這一行所在的資料頁本來就在記憶體中，就直接傳回給執行器；
- 否則，需要先從磁盤讀入記憶體，然後再傳回。
- 執行器将引擎傳回的行資料的這個值加1，即N變成N+1，得到新的一行資料，再調用引擎接口寫入新行。
- 引擎将這行新資料更新到記憶體中，同時将這個更新操作記錄到redo log裡，此時redo log處于prepare狀态。然後告知執行器執行完成了，随時可以送出事務。
- 執行器生成這個操作的binlog，并把binlog寫入磁盤。
- 執行器調用引擎的送出事務接口，引擎把剛寫入的redo log改成送出（commit）狀态，更新完成。
執行流程如下圖。淺色步驟在InnoDB内部執行，深色步驟在執行器中執行。
binlog
binlog，即二進制日志，是一個二進制檔案，記錄了對資料庫執行更新的所有操作，并且記錄了語句發生時間、執行時長、操作資料等資訊。但不記錄SELECT、SHOW等查詢SQL語句。
二進制日志主要用于資料恢複和主從複制，及審計操作。
- max_binlog_size：日志檔案大小上限，二進制日志檔案字尾名會由 mysql 自動拼接數字，達到此參數設定大小則寫入另一個檔案，同時字尾 + 1，是以在設定 log-bin 參數時僅填寫路徑和檔案名即可，字尾名省略
是否開啟binlog
log-bin：設定日志檔案的位置，設定此參數同時開啟日志記錄，預設放在 mysql data目錄下
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.02 sec) 檢視binlog
binlog不能直接檢視。可以通過官方提供的mysqlbinlog工具檢視。
- 确認日志位置
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000120 | 47533445 |
| mysql-bin.000121 | 57556996 |
| mysql-bin.000122 | 190963566 |
+------------------+-----------+
3 rows in set (0.01 sec)
mysql>
mysql>
mysql> show master status;
+------------------+-----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000122 | 190964654 | | | a30ccda1-22af-11e9-850a-6c92bf668356:4512526-4520657,
df123bc8-3139-11ea-9587-6c92bf9bf658:1-6937,
e001bcc0-3139-11ea-a763-ac853d9f52c8:1-801427 |
+------------------+-----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) - 輸出檔案内容
# 直接檢視
mysqlbinlog mysql-bin.xxx //xxx表示檔案編号
# 輸出到指定檔案
mysqlbinlog mysql-bin.xxx > mysqllog.log
# 指定時間段輸出
mysqlbinlog --start-datetime '2020-01-08 00:00:00' --stop-datetime '2020-01-08 01:00:00' mysql-bin.xxx > mysqllog.log 如果執行過程出現以下提示，可暫時添加--no-defaults參數跳過檢查。
mysqlbinlog:[ERROR] unknown variable 'default-character-set=utf8' 更多内容，請參考
https://dev.mysql.com/doc/refman/5.7/en/binary-log.html兩階段送出
寫入redo log分為兩個步驟
- prepare
- commit
即，兩階段送出。
為什麼需要兩階段送出？
redo log和binlog是兩個獨立的邏輯，如果不用兩階段送出，要麼就是先寫完redo log再寫binlog，要麼反過來。
以上述更新為例。假設目前ID=2，字段c的值為0，并假設執行update過程中寫完第一個日志後，發生了crash。看看兩種方式會發生什麼情況。
- 先寫redo log後寫binlog
- 假設redo log寫完，binlog沒有寫完的時候，MySQL 程序異常重新開機
- redo log寫完之後，系統即使崩潰，仍然能夠把資料恢複回來，是以恢複後這一行c的值是 1
- 但由于binlog沒寫完就crash了，這時候binlog裡面就沒有記錄這個語句。是以，之後備份日志的時候，存起來的binlog裡面就沒有這條語句。
- 如果需要用這個binlog來恢複臨時庫的話，由于這個語句的binlog丢失，這個臨時庫就會少了這一次更新，恢複出來的這一行c的值就是0，與原庫的值不同。
- 出現不一緻
- 先寫binlog後寫redo log
- 假設binlog寫完之後，redo log沒有寫完的時候crash
- 由于redo log還沒寫，崩潰恢複以後這個事務無效，是以這一行c的值是0。
- 但是 binlog 裡面已經記錄了“把c從0改成1”這個日志。
- 後續用binlog恢複的時候就多了一個事務出來，恢複出來的這一行c的值就是 1，與原庫的值不同。
- 也出現不一緻
可以看出，如果不使用“兩階段送出”，資料庫的狀态有可能和用binlog恢複出來的庫的狀态不一緻。