目錄
1、記憶體與磁盤的邏輯結構圖
2、記憶體緩存子產品
2.1、Buffer pool
2.2、 Change Buffer
2.3、Log Buffer
2.4、Adaptive Hash Index自适應hash索引
3、日志三劍客
3.1、資料庫binlog日志格式
4、sql的更新流程
5、問題思考答疑
6、binlog檔案
7、小結
前言 資料庫的查詢操作具有天然幂等性，不會對資料庫有任何的修改。但是mysql如何實作對資料庫的更新操作呢？
1、記憶體與磁盤的邏輯結構圖
要了解一個sql是如何更新的，需要了解一下Innodb的記憶體和磁盤的結構之間的關系。 官網Innodb的記憶體和磁盤結構圖參考資料： https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
2、記憶體緩存子產品
思考：每次更新資料通路磁盤效率低下，有沒有什麼優化方式呢？
2.1、Buffer pool
首先資料庫更新操作都是基于記憶體頁，更新的時候不會直接更新磁盤，如果記憶體有存在就直接更新記憶體，如果記憶體沒有存在就從磁盤讀取到記憶體，在更新記憶體，并且寫redo log，目的是為了更新效率更快，等空閑時間在将其redo log所做的改變更新到磁盤中，innodb_flush_log_at_trx_commit設定為1時，也可以防止服務出現異常重新開機，資料不會丢失; Innodb操作資料有一個最小的資料機關，稱為頁（索引頁和資料頁），因為資料在磁盤更新的速度太慢，是以将資料放入記憶體頁緩存Buffer pool， 預設大小128M，下 一次讀取相同的頁，判斷是否在緩沖池裡，如果在，直接讀取，不用再通路磁盤；
- 髒頁： 修改資料的時候先修改緩存中的資料，資料發生變更就變成了 髒頁 ；
- 刷髒： 每隔一段時間将資料刷回磁盤，稱為 刷髒；
記憶體中滿了怎麼辦？
- 采用lru的算法來淘汰舊的資料，分成了young和old區來實作，分代思想，類似jvm中的分代思想；
思考：如果資料在緩存中，則直接進行更新，但是如果不在緩存中，至少需要進行一次磁盤io，有沒有什麼方法可以進行優化呢？
2.2、 Change Buffer
Change buffer 也稱 insert buffer ：寫緩沖，預設是buffer pool的25%， 為了提高 非唯一性索引而避免唯一性檢查 的資料的修改而提供的緩沖區，提高效率； 如果更新的資料不是唯一索引資料，也就是不需要從磁盤加載資料，那麼先将更新的資料記錄在change buffer中，之後再merge到頁緩存中，以提高寫的效率。 将change buffer的資料merge到資料頁的情況叫做merge，什麼時候發生merge？
- 在通路這個資料頁的時候;
- 或者通過背景線程;
- 或者資料庫 shut down;
- redo log 寫滿時觸發。
如果資料庫大部分索引都是非唯一索引，并且業務是寫多讀少，不會在寫資料後立 刻讀取，就可以使用 Change Buffer(寫緩沖)。寫多讀少的業務，調大這個值: SHOW VARIABLES LIKE 'innodb_change_buffer_max_size'; 思考：如果更新的資料在buffer pool中還未同步到磁盤中，這時候mysql重新開機了，資料是不是丢失了呢？
2.3、Log Buffer
Log Buffer 預設是16M，還有對應的 Redo Log預設大小48M，也稱重做日志。 為了避免上述問題，innodb提供了 crash-safe功能-崩潰恢複能力，使用了 WAL技術（write-ahead-log），提供了redo log。 用它來實作事務的持久性。它的關鍵點就是先寫日志再寫磁盤，二階段送出: 使用redo log和binlog來判斷事務的完整性；
2.4、Adaptive Hash Index自适應hash索引
主要儲存記憶體中的熱點頁上的資料，用于記憶體的快速索引。 思考：log buffer什麼時候寫入log file，即rodo log呢？ 和事務相關： innodb_flush_log_at_trx_commit = 1 來控制其寫入的時機
innodb_flush_log_at_trx_commit控制 刷盤方式邏輯示意圖：
3、日志三劍客
再來了解一下三個重要的日志， 日志三劍客：
- redo log-(上面提到的Log Buffer)；
- binlog；
- undo log；
| Redo log-WAL技術- （持久化， 紀錄頁做了什麼改動 ，字段0改為1） | Bin log- 歸檔日志 （怎麼修改的，sql語句本身） |
特點：
| 特點：
|
| undo log-撤銷日志 | 其他： |
| 資料恢複：當mysql資料庫出現問題後，在進行資料恢複的時候，會根據redo log來決定undo log，這樣來進行資料恢複； MVCC: 并發版本控制MVCC的時候，會有一個 一緻性視圖，裡面會記錄相應的復原日志。 比如一個事務在執行到一半的時候執行個體崩潰了，在恢複的時候是不是先恢複redo，再根據redo log和binlog兩階段送出狀态，決定執行undo復原當機前沒有送出的事務。 | 持久化控制 ：“雙一”和“非雙一”設定
|
3.1、資料庫binlog日志格式
- 行格式row：按行資料來記錄日志
- 語句格式statement：執行的sql語句記錄；
- Mixed格式：如果主庫和從庫的索引不一樣，會造成執行的sql不一樣，這時候就需要用mixed格式。
思考：為什麼會出現 mixd 格式的 binlog ？ 因為有些statement格式的binlog可能會導緻主備不一緻，是以要用row格式。 如果通過索引及範圍查找limit 1，如果binlog是 statement格式，在語句執行的時候會出現不一緻的情況。
delete from Order where num>4 and t_modified<='2018-11-10' limit 1; 例如主庫上以num建立索引，從庫上以時間t_modified建立索引，這樣執行的結果就有可能不一樣，mysql認為這樣是不安全的。
row 格式優點： 資料安全，因為記錄的詳細過程；此外， 設定row 格式的另一個好處是：恢複資料 。 缺點：占空間。比如用一個delete語句删掉10萬行資料，用statement格式就是一個sql被記錄到binlog中，占用幾十個位元組的空間。但如果用row格式的binlog，就要把10萬條記錄寫入到binlog中。這樣做，就會浪費很大的記憶體空間，同時寫binlog也要耗費io資源，影響到執行速度。 如果設定 為 row格式的另一個好處是：恢複資料。 是以，mysql就取了個折中方案，也就是有了 mixed 格式。如果mysql判斷會出現sql語句可能會引起主備的不一緻性，就用row格式，否則就用statement格式。
4、sql的更新流程
update USER set name=“king” where id = 9527; 更新sql的詳細執行步驟：
- (1). 用戶端通過tcp/ip和資料庫的 連接配接器建立連接配接，連接配接器擷取使用者賬号資訊并驗證權限是否比對；
- ⚠️此步可能出現的常見錯誤：“Access deied for user”
- (2). 如果開啟了 緩存查詢，先檢視緩存是否存在資料，對表的權限進行校驗，通過則直接傳回給用戶端；如果沒有開啟緩存，則走向第三步；
- (3). 通過 分析器的詞法分析，得到是一個update操作，表名是USER_TABLE，字段age where；
- ⚠️此步可能出現的常見錯誤：“Unknown column ‘XXX’ in ‘where clause”
- (4). 通過 分析器的語義分析，看看是否有文法問題
- ⚠️此步可能出現的錯誤：“You hava an error in your SQL syntax. ”
- (5). 通過 優化器選擇索引，id為主鍵，使用主鍵索引查詢；
- (6). 将生成的最優執行方案交給 執行器，執行器調用底層的存儲引擎的讀接口通過搜尋書取到id=6這行的資料，如果id=6的這行資料本來就在記憶體中，那麼将會直接傳回給執行器；否則，需要先從磁盤讀入記憶體，然後再傳回；
- (7). 執行器拿到 存儲引擎傳回的age資料，進行運算+1，得到新的一行資料，然後執行器調用引擎的寫接口寫入這行新資料；
- (8). 引擎将這行資料更新到記憶體中，同時将這個更新操作 記錄到Redo log 裡面，此時redo log處于 prepare狀态，然後告訴執行器完成了，随時可以送出事務；
- (9). server層的 執行器生成這個操作的binlog，并把binlog寫入磁盤；
- (10). 執行器調用引擎的事務接口，引擎把剛剛寫入的Redo log改為送出 commit狀态;
- 更新完成。
具體更新流程如下所示：
5、問題思考答疑
問題一： 響應一次update sql需要寫幾次磁盤？ 答：三次。redo log 2次(prepare + commit)，binlog一次。 問題二： 為什麼需要兩份日志呢？ 答：Mysql裡并沒有InnoDB引擎，MySql自帶的引擎是MyISAM，但是MyISAM 沒有crash-safe能力，binlog隻能用魚歸檔，是以InnoDB使用了另外一套日志系統，也就是Redo log來實作creash-safe的能力。 一句話差別：crash-safe是崩潰恢複，就是原地滿血複活；binlog是制造一個副本； 問題三： 如何讓資料庫恢複到一個月内的任意一秒的狀态呢？ 答：首先我們的備份系統需要儲存近一個月的所有的binlog；另外，要求系統會定期做整庫備份，根據系統的重要性，可以一天或者是一周備份。定期的整庫備份時間越短，“最快恢複的時間”就越短，主要根據具體的業務容忍度來做。 恢複步驟：
- 1、找到需要恢複時間點之前的最近一次的整庫備份，将其恢複到臨時資料庫；
- 2、從整庫備份時間點開始，将備份的binlog依次回放，重放到需要的時間點那個時刻；
- 3、至于誤删之後的，不能隻靠binlog，需要和業務方一起來完成資料的恢複，因為由于誤删，可以插入了一些錯誤的操作；
問題四： 為什麼需要兩階段送出？ 答： 主要為了保證binlog和原庫資料一緻性，分析步驟如下
- 1、redo log 處于prepare狀态；
- 2、server寫binglog；
- 3、redolog commit；
第2步 崩潰：不滿足binlog和redo log一緻性，重新開機恢複：沒有commit，復原；備份恢複：沒有binlog ；結果：一緻； 第3步 崩潰： 滿足binlog和redo log一緻性，重新開機恢複：自動commit，送出；備份恢複：有binlog； 結果：一緻 事務是否送出的條件是：看結果是否符合我們要達到的“用binlog恢複的庫和原庫邏輯相同”這個要求； 可利用反證法證明： 如果不使用兩階段送出，無論是先寫Redo log 後寫 binlog，還是先寫Binlog 後寫 Redo log，都會出現主從資料庫資料的不一緻性。 問題五： 兩個參數的意義？ 資料庫的“ 雙一”配置 答： innodb_flush_log_at_trx_commit：表示每次事務的redo log 都直接持久化到磁盤，值建議設定為1，可以保證MySql異常重新開機後的資料不會丢失； sync_binlog: 表示每次事務的binlog都持久化到磁盤，這個參數最好也設定為1，可以保證mysql異常重新開機後binlog不丢失； 保證事務成功，參數設定為1後，日志必須落盤，這樣在crash後不會出現資料的丢失； 問題六： 有了Redo log，binlog能不能去掉？ 答：不能去，至少目前不能去。原因：
- 1、redo log隻有innodb有，别的引擎沒有；
- 2、redo log是循環寫的，不持久儲存，binlog的歸檔功能redo log不具備。是以在主從備份的時候還是需要server層所有引擎都可以用的binlog。
- 3、binglog沒有crash-safe功能；
- 4、binlog是可以手動關閉的，是以隻依靠binlog是不靠譜的；
ps：個人觀點：當redo log可以追加寫 并被所有的存儲引擎可用的時候就可以丢棄binlog，并且redo log的恢複效率和同步效率會顯著提高，因為它記錄的是實體的變化。 問題七： 同樣是寫磁盤，為啥要先寫日志後寫磁盤呢？ 主要優化利器點：
- * 順序寫
- * 組送出；
首先資料庫的資料更新都是基于記憶體頁的更新，更新的時候不會直接更新磁盤，如果記憶體有資料就直接更新記憶體，如果沒有就從磁盤讀取資料到記憶體，在記憶體更新，并寫入redo log。目的就是為了減少通路延遲，提高更新效率，等空閑的時候再将redo log所做的改變更新到磁盤中。Rodo log是順序寫，而update是直接更新磁盤，尋找到資料再進行更新；即使有索引也是随機寫，是以速度會很慢；磁盤通路順序寫的時間優勢，不用找“磁盤位置”。 通路磁盤的時間：每次通路磁盤的一個塊時，磁臂就需移動到正确的磁道上（這段時間為尋址時間），然後盤片就需旋轉到正确的扇區上（這叫旋轉時延），這套動作需要時間，是以說順序寫比随機寫性能高，要知道db的最大瓶頸在io； 我們先分析下redo log再哪些場景會刷到磁盤。
- 場景1：redo log寫滿了，此時MySQL會停止所有更新操作，把髒頁刷到磁盤
- 場景2：系統記憶體不足，需要将髒頁淘汰，此時會把髒頁刷到磁盤
- 場景3：系統空閑時，MySQL定期将髒頁刷到磁盤
問題八： 資料庫Redo log隻有commit的時候才會真正的送出嗎？ 答：正常情況是隻有在commit時才送出到資料庫落盤，但是當崩潰恢複的過程中，當存在“binlog完整 + redo log prpare ”的條件，資料也會自動被送出到資料庫；redo log 和binlog 之間通過事務ID進行對應。 問題九： 資料寫在redo log上而沒有寫入資料庫，那讀到的資料不是不一緻嗎？ 答：寫到了記憶體，讀取的時候是在記憶體讀取。并且讀和寫操作會引起記憶體的淘汰。 問題10 ：mysql啟動，對于innodb的啟動是如何實作的，undo log的作用？ 答：mysql重新開機，需要讀完redo log的日志，從checkpoint開始到writepos結束。如果mysql的一個執行個體崩潰了，一個事務寫入了redo log但是未寫入binlog，也就是未送出commit，那麼該mysql在重新開機的時候，會先恢複redo log，之後構造undo log復原當機前沒有送出的事務。
6、binlog檔案
了解binlog檔案内容，可以更好的了解mysql的執行原理，檢視指令：
show binlog events mysql-bin.000001; 部分binlog日志的内容如下：
*************************** 20. row ***************************
Log_name: mysql-bin.000001 ----------------------------------------------> 查詢的binlog日志檔案名
Pos: 11197 ----------------------------------------------------------------> pos起始點:
Event_type: Query -------------------------------------------------------------> 事件類型：Query
Server_id: 1 --------------------------------------------------------------------> 辨別是由哪台伺服器執行的
End_log_pos: 11308 ------------------------------------------------------------> pos結束點:11308(即：下行的pos起始點)
Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5') ---> 執行的sql語句
*************************** 21. row ***************************
Log_name: mysql-bin.000001
Pos: 11308 ----------------------------------------------------------> pos起始點:11308(即：上行的pos結束點)
Event_type: Query
Server_id: 1
End_log_pos: 11417
Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */
*************************** 22. row ***************************
Log_name: mysql-bin.000001
Pos: 11417
Event_type: Query
Server_id: 1
End_log_pos: 11510
Info: use `zyyshop`; DROP TABLE IF EXISTS `type`
7、小結
一個sql的輸入執行并不是我們想象的那麼簡單，背後付出了很多的艱辛，經典的東西值得深究和回味。 水滴石穿，積少成多。學習筆記，内容簡單，用于複習，梳理鞏固，原内容2月有更新。 ##參考資料 官網Innodb的記憶體和磁盤結構圖參考資料： https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html 官網記憶體中的緩存資料參考：： https://dev.mysql.com/doc/refman/5.7/en/innodb-in-memory-structures.html 《Innodb存儲引擎》 《MySql實戰45講詳解》--丁奇