聊聊MySQL的基礎架構和紀錄檔系統

一、MySQL基礎架構

MySQL可以分為Server層和儲存引擎層兩部分

Server層包括聯結器、查詢快取、分析器、優化器、執行器等，涵蓋MySQL的大多數核心服務功能，以及所有的內建函數（如日期、時間、數學和加密函數等），所有跨儲存引擎的功能都在這一層實現，比如儲存過程、觸發器、檢視等

儲存引擎負責資料的儲存和提取。其架構模式是外掛式的，支援InnoDB、MyISAM、Memory等多個儲存引擎。現在最常用的儲存引擎是InnoDB，它從MySQL 5.5.5版本開始成為了預設儲存引擎。可以通過在SQL語句中使用engin=memory來指定使用記憶體引擎執行

不同的儲存引擎共用一個Server層

1、聯結器

聯結器負責跟使用者端建立連線、獲取許可權、維持和管理連線。連線命令一般是：

mysql -h$ip -P$port -u$user -p

連線命令中的mysql是使用者端工具，用來跟伺服器端建立連線。在完成TCP握手後，聯結器就要開始認證身份

• 如果使用者名稱或密碼不對，就會收到一個"Access denied for user"的錯誤，然後使用者端程式結束執行
• 如果使用者名稱密碼認證通過，聯結器回到許可權表裡面查出你擁有的許可權。之後，這個連線裡面的許可權判斷邏輯，都將依賴於此時讀到的許可權

這就意味著，一個使用者成功建立連線後，即使用管理員帳號對這個使用者的許可權做了修改，也不會影響已經存在連線的許可權。修改完成後，只有再新建的連線才會使用新的許可權設定

連線完成後，如果你沒有後續的動作，這個連線就處於空閒狀態，可以在show processlist命令中看到它

Command為Sleep表示此連線是一個空閒連線

使用者端如果太長時間沒動靜，聯結器就會自動將它斷開。這個時間是由引數wait_timeout控制的。預設值是8小時

如果在連線被斷開之後，使用者端再次傳送請求的話，就會收到一個錯誤提示：Lost connection to MySQL server during query。這時候就需要重新連線，然後在執行請求了

資料庫裡面，長連線是指連線成功後，如果使用者端持續有請求，則一直使用同一個連線。短連線則是指每次執行完很少的幾次查詢就斷開連線，下次查詢再重新建立一個

建立連線的過程通常是比較複雜的，所以建議儘量使用長連線

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

可以通過以下兩種方案解決這個問題：

1.定期斷開長連線。使用一段時間，或者程式裡面判斷執行過一個佔用記憶體的大查詢後，斷開連線，之後要查詢再重連

2.如果使用的是MySQL5.7或更新版本，可以在每次執行一個比較大的操作後，通過執行mysql_reset_connection來重新初始化連線資源。這個過程不需要重連和重新做許可權驗證，但是會將連線恢復到剛剛建立完時的狀態

2、查詢快取

建立連線完成後，可以執行select語句了。MySQL拿到一個查詢請求後，會先到查詢快取看看，之前是不是執行過這條語句。之前執行過的語句及其結果可能會以key-value對的形式，被直接快取在記憶體中。key是查詢的語句，value是查詢的結果。如果查詢能夠直接在這個快取中找到key，那麼這個value就會被直接返回給使用者端

如果語句不在查詢快取中，就會繼續後面的執行階段。執行完成後，執行結果會被存入查詢快取中。如果查詢命中快取，MySQL不需要執行後面的複雜操作，就可以直接返回結果，這個效率很高

但是大多數情況下不建議使用查詢快取，因為查詢快取的失效非常頻繁，只要對一個表的更新，這個表上所有的查詢快取都會被清空。對於更新壓力大的資料庫來說，查詢快取的命中率會非常低

可以將引數query_cache_type設定成DEMAND，這樣對於預設的SQL語句都不使用查詢快取。而對於確定要是查詢快取的語句，可以用SQL_CACHE顯示指定，如下面這條語句一樣：

select SQL_CACHE * from T where ID=10；

MySQL8.0版本直接將查詢快取的整塊功能刪掉了

3、分析器

如果沒有命中查詢快取，就要開始真正執行語句了。MySQL首先要對SQL語句做解析

分析器會先做詞法分析。輸入的是由多個字串和空格組成的一條SQL語句，MySQL需要識別出裡面的字串分別是什麼，代表什麼

select * from T where ID=10；

MySQL從輸入的select這個關鍵字識別出來，這是一個查詢語句。它也要把字串T識別成表名T，把字串ID識別成列ID

做完了這些識別以後，就要做語法分析。根據詞法分析的結果，語法分析器會根據語法規則，判斷這個SQL語句是否滿足MySQL語法。如果語法不對，就會收到"You have an error in your SQL syntax"的錯誤提示

4、優化器

經過了分析器，在開始執行之前，還要先經過優化器的處理

優化器是在表裡面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯的時候，決定各個表的連線順序

5、執行器

優化器階段完成後，這個語句的執行方案就確定下來了，然後進入執行器階段，開始執行語句

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

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有許可權，就開啟表繼續執行。開啟表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的介面

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

1.呼叫InnoDB引擎介面取這個表的第一行，判斷ID值是不是10，如果不是則跳過，如果是則將這個行存在結果集中

2.呼叫引擎介面取下一行，重複相同的判斷邏輯，直到取到這個表的最後一行

3.執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給使用者端

在資料庫的慢查詢紀錄檔中看到一個rows_examined的欄位，表示這個語句執行過程掃描了多少行。這個值就是在執行器每次呼叫引擎獲取資料行的時候累加的

在有些場景下，執行器呼叫一次，在引起內部則掃描了多行，因此引擎掃描行數跟rows_examined並不是完全相同的

二、紀錄檔系統

表T的建立語句如下，這個表有一個主鍵ID和一個整型欄位c：

create table T(ID int primary key, c int);

如果要將ID=2這一行的值加1，SQL語句如下：

update T set c=c+1 where ID=2;

1、redo log（重做紀錄檔）

在MySQL中，如果每次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程IO成本、查詢成本都很高。MySQL裡常說的WAL技術，全稱是Write-Ahead Logging，它的關鍵點就是先寫紀錄檔，再寫磁碟

當有一條記錄需要更新的時候，InnoDB引擎就會把記錄寫到redo log裡面，並更新buffer pool的page，這個時候更新就算完成了

buffer pool是物理頁的快取，對InnoDB的任何修改操作都會首先在buffer pool的page上進行，然後這樣的頁面將被標記為髒頁並被放到專門的flush list上，後續將由專門的刷髒執行緒階段性的將這些頁面寫入磁碟

InnoDB的redo log是固定大小的，比如可以設定為一組4個檔案，每個檔案的大小是1GB，從頭開始寫，寫到末尾就又回到開頭回圈寫

write pos是當前記錄的位置，一邊寫一邊後移，寫到第3號檔案末尾後就回到0號檔案開頭。check point是當前要擦除的位置，也是往後推移並且迴圈的，擦除記錄前要把記錄更新到資料檔案

write pos和check point之間空著的部分，可以用來記錄新的操作。如果write pos追上check point，這時候不能再執行新的更新，需要停下來擦掉一些記錄，把check point推進一下

有了redo log，InnoDB就可以保證即使資料庫發生異常重新啟動，之前提交的記錄都不會丟失，這個能力稱為crash-safe

2、binlog（歸檔紀錄檔）

MySQL整體來看就有兩塊：一塊是Server層，主要做的是MySQL功能層面的事情；還有一塊是引擎層，負責儲存相關的具體事宜。redo log是InnoDB引擎特有的紀錄檔，而Server層也有自己的紀錄檔，稱為binlog

為什麼會有兩份紀錄檔？

因為最開始MySQL裡並沒有InnoDB引擎。MySQL自帶的引擎是MyISAM，但是MyISAM沒有crash-safe的能力，binlog紀錄檔只能用於歸檔。而InnoDB是以外掛形式引入MySQL的，既然只依靠binlog是沒有crash-safe能力的，所以InnoDB使用redo log來實現crash-safe能力

binlog的紀錄檔格式：

binlog的格式有三種：STATEMENT，ROW，MIXED

1）、STATEMENT模式

binlog裡面記錄的就是SQL語句的原文。優點是並不需要記錄每一行的資料變化，減少了binlog紀錄檔量，節約IO，提高效能。缺點是在某些情況下會導致master-slave中的資料不一致(如sleep()函數， last_insert_id()，以及user-defined functions(udf)等會出現問題)

2）、ROW模式

不記錄每條SQL語句的上下文資訊，僅需記錄哪條資料被修改了，修改成什麼樣了。而且不會出現某些特定情況下的儲存過程或function或trigger的呼叫和觸發無法被正確複製的問題。缺點是會產生大量的紀錄檔，尤其是alter table的時候會讓紀錄檔暴漲

3）、MIXED模式

以上兩種模式的混合使用，一般的複製使用STATEMENT模式儲存binlog，對於STATEMENT模式無法複製的操作使用ROW模式儲存binlog，MySQL會根據執行的SQL語句選擇紀錄檔儲存方式

3、redo log和binlog紀錄檔的不同

1.redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現的，所有引擎都可以使用

2.redo log是物理紀錄檔，記錄的是在某個資料也上做了什麼修改；binlog是邏輯紀錄檔，記錄的是這個語句的原始邏輯，比如給ID=2這一行的c欄位加1

3.redo log是迴圈寫的，空間固定會用完；binlog是可以追加寫入的，binlog檔案寫到一定大小後會切換到下一個，並不會覆蓋以前的紀錄檔

4、兩階段提交

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

1.執行器先找到引擎取ID=2這一行。ID是主鍵，引擎直接用樹搜尋找到這一行。如果ID=2這一行所在的資料也本來就在記憶體中，就直接返回給執行器；否則，需要先從磁碟讀入記憶體，然後再返回

2.執行器拿到引擎給的行資料，把這個值加上1，得到新的一行資料，再呼叫引擎介面寫入這行新資料

3.引擎將這行新資料更新到記憶體中，同時將這個更新操作記錄到redo log裡面，此時redo log處於prepare狀態。然後告知執行器執行完成了，隨時可以提交事務

4.執行器生成這個操作的binlog，並把binlog寫入磁碟

5.執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的redo log改成提交狀態，更新完成

update語句的執行流程圖如下，圖中淺色框表示在InnoDB內部執行的，深色框表示是在執行器中執行的

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

由於redo log和binlog是兩個獨立的邏輯，如果不用兩階段提交，要麼就是先寫完redo log再寫binlog，或者先寫完binlog再寫redo log

1.先寫完redo log再寫binlog。如果在redo log寫完，binlog還沒有寫完的時候，MySQL程序異常重新啟動。由於redo log寫完之後，系統即使崩潰，仍然能夠把資料恢復回來，所以恢復後這一行c的值是1。但是由於binlog還沒寫完就crash了，這時候binlog裡面就沒有記錄這個語句，binlog中記錄的這一行c的值為0

2.先寫binlog後寫redo log。如果在binlog寫完之後crash，由於redo log還沒寫，崩潰恢復以後這個事務無效，所以這一行的c的值是0。但是binlog裡面已經記錄了把c從0改成1這個紀錄檔。所以，在之後binlog來恢復的時候就多了一個事務出來，恢復出來的這一行c的值就是1

如果不使用兩階段提交，那麼資料庫的狀態就有可能和用它的紀錄檔恢復出來的庫的狀態不一致。redo log和binlog都可以用於表示事務的提交狀態，而兩階段提交就是讓這兩個狀態保持邏輯上的一致

redo log用於保證crash-safe能力。innodb_flush_log_at_trx_commit這個引數設定成1的時候，表示每次事務的redo log都直接持久化到磁碟，這樣可以保證MySQL異常重新啟動之後資料不丟失

sync_binlog這個引數設定成1的時候，表示每次事務的binlog都持久化到磁碟，這樣可以保證MySQL異常重新啟動之後binlog不丟失

三、MySQL刷髒頁

1、刷髒頁的場景

當記憶體資料頁跟磁碟資料頁不一致的時候，我們稱這個記憶體頁為髒頁。記憶體資料寫入到磁碟後，記憶體和磁碟行的資料頁的內容就一致了，稱為乾淨頁

• 第一種場景是，InnoDB的redo log寫滿了，這時候系統會停止所有更新操作，把checkpoint往前推進，redo log留出空間可以繼續寫
  
  checkpoint位置從CP推進到CP’，就需要將兩個點之間的紀錄檔對應的所有髒頁都flush到磁碟上。之後，上圖中從write pos到CP’之間就是可以再寫入的redo log的區域

• 第二種場景是，系統記憶體不足。當需要新的記憶體頁，而記憶體不夠用的時候，就要淘汰一些資料頁，空出記憶體給別的資料頁使用。如果淘汰的是髒頁，就要先將髒頁寫到磁碟

這時候不能直接把記憶體淘汰掉，下次需要請求的時候，從磁碟讀入資料頁，然後拿redo log出來應用不就行了？

這裡是從效能考慮的。如果刷髒頁一定會寫盤，就保證了每個資料頁有兩種狀態：一種是記憶體裡存在，記憶體裡就肯定是正確的結果，直接返回；另一種是記憶體裡沒有資料，就可以肯定資料檔案上是正確的結果，讀入記憶體後返回。這樣的效率最高

• 第三種場景是，MySQL認為系統空閒的時候刷髒頁，當然在系統忙的時候也要找時間刷一點髒頁
• 第四種場景是，MySQL正常關閉的時候會把記憶體的髒頁都flush到磁碟上，這樣下次MySQL啟動的時候，就可以直接從磁碟上讀資料，啟動速度會很快

redo log寫滿了，要flush髒頁，出現這種情況的時候，整個系統就不能再接受更新了，所有的更新都必須堵住

記憶體不夠用了，要先將髒頁寫到磁碟，這種情況是常態。InnoDB用緩衝池管理記憶體，緩衝池中的記憶體頁有三種狀態：

• 第一種是還沒有使用的
• 第二種是使用了並且是乾淨頁
• 第三種是使用了並且是髒頁

InnoDB的策略是儘量使用記憶體，因此對於一個長時間執行的庫來說，未被使用的頁面很少

當要讀入的資料頁沒有在記憶體的時候，就必須到緩衝池中申請一個資料頁。這時候只能把最久不使用的資料頁從記憶體中淘汰掉：如果要淘汰的是一個乾淨頁，就直接釋放出來複用；但如果是髒頁，即必須將髒頁先刷到磁碟，變成乾淨頁後才能複用

刷頁雖然是常態，但是出現以下兩種情況，都是會明顯影響效能的：

• 一個查詢要淘汰的髒頁個數太多，會導致查詢的響應時間明顯變長
• 紀錄檔寫滿，更新全部堵住，寫效能跌為0，這種情況對敏感業務來說，是不能接受的

2、InnoDB刷髒頁的控制策略

首先，要正確地告訴InnoDB所在主機的IO能力，這樣InnoDB才能知道需要全力刷髒頁的時候，可以刷多快。引數為innodb_io_capacity，建議設定成磁碟的IOPS

InnoDB的刷盤速度就是考慮髒頁比例和redo log寫盤速度。引數innodb_max_dirty_pages_pct是髒頁比例上限，預設值是75%。髒頁比例是通過Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的，SQL語句如下：

mysql>  select VARIABLE_VALUE into @a from performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

四、紀錄檔相關問題

問題一：在兩階段提交的不同時刻，MySQL異常重新啟動會出現什麼現象

如果在圖中時刻A的地方，也就是寫入redo log處於prepare階段之後、寫binlog之前，發生了崩潰，由於此時binlog還沒寫，redo log也還沒提交，所以崩潰恢復的時候，這個事務會回滾。這時候，binlog還沒寫，所以也不會傳到備庫

如果在圖中時刻B的地方，也就是binlog寫完，redo log還沒commit前發生崩潰，那崩潰恢復的時候MySQL怎麼處理？

崩潰恢復時的判斷規則：

1）如果redo log裡面的事務是完整的，也就是已經有了commit標識，則直接提交

2）如果redo log裡面的事務只有完整的prepare，則判斷對應的事務binlog是否存在並完整

a.如果完整，則提交事務

b.否則，回滾事務

時刻B發生崩潰對應的就是2(a)的情況，崩潰恢復過程中事務會被提交

問題二：MySQL怎麼知道binlog是完整的？

一個事務的binlog是有完整格式的：

• statement格式的binlog，最後會有COMMIT
• row格式的binlog，最後會有一個XID event

問題三：redo log和binlog是怎麼關聯起來的？

它們有一個共同的資料欄位，叫XID。崩潰恢復的時候，會按順序掃描redo log：

• 如果碰到既有prepare、又有commit的redo log，就直接提交
• 如果碰到只有prepare、而沒有commit的redo log，就拿著XID去binlog找對應的事務

問題四：redo log一般設定多大？

如果是現在常見的幾個TB的磁碟的話，redo log設定為4個檔案、每個檔案1GB

問題五：正常執行中的範例，資料寫入後的最終落盤，是從redo log更新過來的還是從buffer pool更新過來的呢？

redo log並沒有記錄資料頁的完整資料，所以它並沒有能力自己去更新磁碟資料頁，也就不存在資料最終落盤是由redo log更新過去的情況

1.如果是正常執行的範例的話，資料頁被修改以後，跟磁碟的資料頁不一致，稱為髒頁。最終資料落盤，就是把記憶體中的資料頁寫盤。這個過程，甚至與redo log毫無關係

2.在崩潰恢復場景中，InnoDB如果判斷到一個資料頁可能在崩潰恢復的時候丟失了更新，就會將它對到記憶體，然後讓redo log更新記憶體內容。更新完成後，記憶體頁變成髒頁，就回到了第一種情況的狀態

問題六：redo log buffer是什麼？是先修改記憶體，還是先寫redo log檔案？

在一個事務的更新過程中，紀錄檔是要寫多次的。比如下面這個事務：

begin;insert into t1 ...insert into t2 ...commit;

這個事務要往兩個表中插入記錄，插入資料的過程中，生成的紀錄檔都得先儲存起來，但又不能在還沒commit的時候就直接寫到redo log檔案裡

所以，redo log buffer就是一塊記憶體，用來先存redo紀錄檔的。也就是說，在執行第一個insert的時候，資料的記憶體被修改了，redo log buffer也寫入了紀錄檔。但是，真正把紀錄檔寫到redo log檔案，是在執行commit語句的時候做的

五、MySQL是怎麼保證資料不丟的？

只要redo log和binlog保證持久化到磁碟，就能確保MySQL異常重新啟動後，資料可以恢復

1、binlog的寫入機制

事務執行過程中，先把紀錄檔寫到binlog cache，事務提交的時候，再把binlog cache寫到binlog檔案中。一個事務的binlog是不能被拆開的，因此不論這個事務多大，也要確保一次性寫入

系統給binlog cache分配了一片記憶體，每個執行緒一個，引數binlog_cache_size用於控制單個執行緒內binlog cache所佔記憶體的大小。如果超過了這個引數規定的大小，就要暫存到磁碟

事務提交的時候，執行器把binlog cache裡的完整事務寫入到binlog中，並清空binlog cache

每個執行緒有自己binlog cache，但是共用一份binlog檔案

• 圖中的write，指的就是把紀錄檔寫入到檔案系統的page cache，並沒有把資料持久化到磁碟，所以速度比較快
• 圖中的fsync，才是將資料持久化到磁碟的操作。一般情況下認為fsync才佔磁碟的IOPS

write和fsync的時機，是由引數sync_binlog控制的：

• sync_binlog=0的時候，表示每次提交事務都只write，不fsync
• sync_binlog=1的時候，表示每次提交事務都會執行fsync
• sync_binlog=N（N>1）的時候，表示每次提交事務都write，但累積N個事務後才fsync

因此，在出現IO瓶頸的場景中，將sync_binlog設定成一個比較大的值，可以提升效能，對應的風險是：如果主機發生異常重新啟動，會丟失最近N個事務的binlog紀錄檔

2、redo log的寫入機制

事務在執行過程中，生成的redo log是要先寫到redo log buffer的。redo log buffer裡面的內容不是每次生成後都要直接持久化到磁碟，也有可能在事務還沒提交的時候，redo log buffer中的部分紀錄檔被持久化到磁碟

redo log可能存在三種狀態，對應下圖的三個顏色塊

這三張狀態分別是：

• 存在redo log buffer中，物理上是在MySQL程序記憶體中，就是圖中紅色的部分
• 寫到磁碟，但是沒有持久化，物理上是在檔案系統的page cache裡面，也就是圖中黃色的部分
• 持久化到磁碟，對應的是hard disk，也就是圖中的綠色部分

紀錄檔寫到redo log buffer和write到page cache都是很快的，但是持久化到磁碟的速度就慢多了

為了控制redo log的寫入策略，InnoDB提供了innodb_flush_log_at_trx_commit引數，它有三種可能取值：

• 設定為0的時候，表示每次事務提交時都只是把redo log留在redo log buffer中
• 設定為1的時候，表示每次事務提交時都將redo log直接持久化到磁碟
• 設定為2的時候，表示每次事務提交時都只是把redo log寫到page cache

InnoDB有一個後臺執行緒，每隔1秒，就會把redo log buffer中的紀錄檔，呼叫write寫到檔案系統的page cache，然後呼叫fsync持久化到磁碟。事務執行中間過程的redo log也是直接寫在redo log buffer中的，這些redo log也會被後臺執行緒一起持久化到磁碟。也就是說，一個沒有提交的事務的redo log也是可能已經持久化到磁碟的

還有兩種場景會讓一個沒有提交的事務的redo log寫入到磁碟中

1.redo log buffer佔用的空間即將達到innodb_log_buffer_size一半的時候，後臺執行緒會主動寫盤。由於事務並沒有提交，所以這個寫盤動作只是write，而沒有呼叫fsync，也就是隻留在檔案系統的page cache

2.並行的事務提交的時候，順帶將這個事務的redo log buffer持久化到磁碟。假設一個事務A執行到一半，已經寫了一些redo log到buffer中，這時候有另外一個執行緒的事務B提交，如果innodb_flush_log_at_trx_commit設定的是1，事務B要把redo log buffer裡的紀錄檔全部持久化到磁碟。這時候，就會帶上事務A在redo log buffer裡的紀錄檔一起持久化到磁碟

兩階段提交，時序上redo log先prepare，再寫binlog，最後再把redo log commit。如果把innodb_flush_log_at_trx_commit設定成1，那麼redo log在prepare階段就要持久化一次

MySQL的雙1設定，指的就是sync_binlog和innodb_flush_log_at_trx_commit都設定成1。也就是說，一個事務完整提交前，需要等待兩次刷盤，一次是redo log（prepare階段），一次是binlog

3、組提交機制

紀錄檔邏輯序列號LSN是單調遞增的，用來對應redo log的一個個寫入點，每次寫入長度為length的redo log，LSN的值就會加上length。LSN也會寫到InnoDB的資料頁中，來確保資料頁不會被多次執行重複的redo log

上圖是三個並行事務在prepare階段，都寫完redo log buffer，持久化到磁碟的過程，對應的LSN分別是50、120和160

1.trx1是第一個到達的，會被選為這組的leader

2.等trx1要開始寫盤的時候，這個組裡面已經有了三個事務，這時候LSN也變成了160

3.trx1去寫盤的時候，帶的就是LSN=160，因此等trx1返回時，所有LSN小於等於160的redo log，都已經被持久化到磁碟

4.這時候trx2和trx3就可以直接返回了

一個組提交裡面，組員越多，節約磁碟IOPS的效果要好

為了讓一次fsync帶的組員更多，MySQL做了拖時間的優化

binlog也可以組提交了，在執行上圖第4步把binlog fsync到磁碟時，如果有多個事務的binlog已經寫完了，也是一起持久化的，這樣也可以減少IOPS的消耗

如果想提升binlog組提交的效果，可以通過設定binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count兩個引數來實現

1.binlog_group_commit_sync_delay參數列示延遲多少微妙後才呼叫fsync

2.binlog_group_commit_sync_no_delay_count參數列示積累多少次以後才呼叫fsync

這兩個條件只要有一個滿足就會呼叫fsync

WAL機制主要得益於兩方面：

• redo log和binlog都是順序寫的，磁碟的順序寫比隨機寫速度要快
• 組提交機制，可以大幅降低磁碟訂單IOPS消耗

4、如果MySQL現在出現了效能瓶頸，而且瓶頸在IO上，可以通過哪些方法來提升效能

1.設定binlog_group_commit_sync_delay（延遲多少微妙後才呼叫fsync）和binlog_group_commit_sync_no_delay_count（積累多少次以後才呼叫fsync）引數，減少binlog的寫盤次數。這個方法是基於額外的故意等待來實現的，因此可能會增加語句的響應時間，但沒有丟失資料的風險

2.將sync_binlog設定為大於1的值（每次提交事務都write，但累積N個事務後才fsync）。這樣做的風險是，主機掉電的時候會丟binlog紀錄檔

3.將innodb_flush_log_at_trx_commit設定為2（每次事務提交時都只是把redo log寫到page cache）。這樣做的風險是，主機掉電的時候會丟資料

【相關推薦：】

以上就是聊聊MySQL的基礎架構和紀錄檔系統的詳細內容，更多請關注TW511.COM其它相關文章！