目錄

Redolog與binlog   

一個 update sql 的執行流程？

思考解惑

---

Redolog與binlog   

資料庫的查詢操作具有天然冪等性，不會對資料庫有任何的修改。但是mysql如何實現對資料庫的更新操作呢？這裡主要有兩種重要的日誌：Redo log和binlog。

Redo log	1、Redo log是引擎層 InnoDB特有的日誌； 2、迴圈寫，固定空間會用完； 3、屬於物理日誌，記錄的是做了什麼變更； ps：物理日誌只有具體引擎自己能用，別人沒有共享我的物理格式；        邏輯日誌可以給別的資料庫用，公用的邏輯；
binlog	1、Binlog是MySql Server 層記錄的日誌，所有的儲存引擎都可以使用； 2、可以追加寫，不會覆蓋以前的日誌，用於歸檔； 3、屬於邏輯日誌，記錄的是邏輯操作，是怎麼修改的(sql或者是前後的行記錄)； binlog的兩種模式：    statement記錄的是sql語句；    row格式記錄的是行的內容，記兩條，改變前和改變後的記錄；一般採用row，但是資料量會變大； 我們通常選擇binlog日誌來作為replicatio[果採用redo log來實現會更快，但是隻有innodb有，所以binlog必須存在]。

       資料庫的效能絕大部分情況下都是由於IO影響了其吞吐量和效能。如果每一次的更新操作都需要寫入磁碟，那麼意味著有一次IO磁碟去查詢，然後更新，這個成本比較大。為了提高資料庫的吞吐量和降低訪問延時，還有重要的crash-safe能力，這裡引入了重要的日誌模組：Redo-log。也是經常說的WAL，Wirte-Ahead-Logging, 它的關鍵點是先寫日誌再寫磁碟。
    InnoDB有一個buffer pool簡稱(bp)。bp是資料庫頁面的快取，對InnoDB的任何修改操作都會首先在bp的page上進行，對資料庫的修改首先將記錄在flush-list上，後續由專門的執行緒將這些頁面寫入磁碟(disk or ssd)。這樣的好處是避免每次寫操作都操作磁碟導致大量的隨機IO，階段性的寫入還可以將多次對頁面的修改merge成一次IO操作，同時非同步的寫入也降低了訪問的延遲。但是在dirty page還未刷入磁碟，server非正常關閉，那麼這些操作和資料將丟失，甚至損壞資料庫。為了避免上述問題的產生，設計師們將這些修改先寫入一個專門的檔案redo log，並在資料庫啟動時從此檔案redo log 進行恢復操作，原地滿血復活，這個檔案就是Redo log，從而提升了資料庫的吞吐，有效降低了訪問延時及保證了crash-safe能力。

Redo log優點	(1) 提高系統的吞吐量：組提交；    (2) 降低訪問延時：順序寫日誌後磁碟；    (3)崩潰恢復： crash-safe能力；
Redo log缺點	(1) 額外的寫redo log操作的開銷；         (2) 資料庫啟動時恢復操作所需要的時間；

一個 update sql 的執行流程？

瞭解了上面的一些概念和優缺點後，我們先來看一下更新sql語句的執行過程：

Update  USER_TABLE set age = age +1 where id = 6;

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狀態，更新完成。

  其中8-9-10三步中將Redo log拆成兩步就是兩階段提交：主要為了保證binlog和Redo logd的資料的邏輯一致性。

思考解惑

問題一：響應一次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，需要和業務方一起來完成資料的恢復，因為由於誤刪，可以插入了一些錯誤的操作；

問題四：為什麼需要兩階段提交？
答：
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也是寫io，如何做到優化呢？
答：主要優化有兩點：順序寫 + 組提交；
    首先資料庫的資料更新都是基於記憶體頁的更新，更新的時候不會直接更新磁碟，如果記憶體有資料就直接更新記憶體，如果沒有就從磁碟讀取資料到記憶體，在記憶體更新，並寫入redo log。目的就是為了減少訪問延遲，提高更新效率，等空閒的時候再將redo log所做的改變更新到磁碟中。Rodo log是順序寫，而update是直接更新磁碟，尋找到資料再進行更新；即使有索引也是隨機寫，所以速度會很慢；磁碟訪問順序寫的時間優勢，不用找“磁碟位置”。
    訪問磁碟的時間：每次訪問磁碟的一個塊時，磁臂就需移動到正確的磁軌上（這段時間為定址時間），然後碟片就需旋轉到正確的扇區上（這叫旋轉時延），這套動作需要時間，所以說順序寫比隨機寫效能高，要知道db的最大瓶頸在io；

問題八：資料庫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回滾當機前沒有提交的事務。

binlog檔案

檢視命令：

show binlog events mysql-bin.000001;

             *************************** 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`

學習筆記，內容簡單，用於複習，原內容2月有更新。
##參考資料，《MySql實戰詳解》