MySQL三種日志有啥用？如何提高M(jìn)ySQL并發(fā)度？
MySQL數(shù)據(jù)存儲(chǔ)和查詢流程
假如說現(xiàn)在我們建了如下一張表
- CREATE TABLE `student` (
- `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '學(xué)號(hào)',
- `name` varchar(10) NOT NULL COMMENT '學(xué)生姓名',
- `age` int(11) NOT NULL COMMENT '學(xué)生年齡',
- PRIMARY KEY (`id`),
- KEY `idx_name` (`name`)
- ) ENGINE=InnoDB;
插入如下sql
- insert into student (`name`, `age`) value('a', 10);
- insert into student (`name`, `age`) value('c', 12);
- insert into student (`name`, `age`) value('b', 9);
- insert into student (`name`, `age`) value('d', 15);
- insert into student (`name`, `age`) value('h', 17);
- insert into student (`name`, `age`) value('l', 13);
- insert into student (`name`, `age`) value('k', 12);
- insert into student (`name`, `age`) value('x', 9);
數(shù)據(jù)如下
這些數(shù)據(jù)最終會(huì)持久化到文件中，那么這些數(shù)據(jù)在文件中是如何組織的?難道是一行一行追加到文件中的?其實(shí)并不是，「數(shù)據(jù)其實(shí)是存到頁中的，一頁的大小為16k，一個(gè)表由很多頁組成，這些頁組成了B+樹」，最終的組織形式如下所示，具體的構(gòu)建過程我就不詳細(xì)介紹了，可以看我之前的文章《10張圖，搞懂索引為什么會(huì)失效?》
那么SQL語句是如何執(zhí)行的呢?MySQL的邏輯架構(gòu)圖如下所示
詳細(xì)結(jié)構(gòu)如為
「當(dāng)我們想更新某條數(shù)據(jù)的時(shí)候，難道是從磁盤中加載出來這條數(shù)據(jù)，更新后再持久化到磁盤中嗎?」
如果這樣搞的話，那一條sql的執(zhí)行過程可太慢了，因?yàn)閷?duì)一個(gè)大磁盤文件的讀寫操作是要耗費(fèi)幾百萬毫秒的
真實(shí)的執(zhí)行過程是，當(dāng)我們想更新或者讀取某條數(shù)據(jù)的時(shí)候，會(huì)把對(duì)應(yīng)的頁加載到內(nèi)存中的Buffer Pool緩沖池中(默認(rèn)為128m，當(dāng)然為了提高系統(tǒng)的并發(fā)度，你可以把這個(gè)值設(shè)大一點(diǎn))
之所以加載頁到Buffer Pool中，是考慮到當(dāng)你使用這個(gè)頁的數(shù)據(jù)時(shí)，這個(gè)頁的其他數(shù)據(jù)使用到的概率頁很大，隨機(jī)IO的耗時(shí)很長，所以多加載一點(diǎn)數(shù)據(jù)到Buffer Pool
當(dāng)更新數(shù)據(jù)的時(shí)候，如果對(duì)應(yīng)的頁在Buffer Pool中，則直接更新Buffer Pool中的頁即可，對(duì)應(yīng)的頁不在Buffer Pool中時(shí)，才會(huì)從磁盤加載對(duì)應(yīng)的頁到Buffer Pool，然后再更新，「此時(shí)Buffer Pool中的頁和磁盤中的頁數(shù)據(jù)是不一致的，被稱為臟頁」。這些臟頁是要被刷回到磁盤中的
「這些臟頁是多會(huì)刷回到磁盤中的?」 有如下幾個(gè)時(shí)機(jī)
- Buffer Pool不夠用了，要給新加載的頁騰位置了，所以會(huì)利用改進(jìn)的后的LRU算法，將一些臟頁刷回磁盤
- 后臺(tái)線程會(huì)在MySQL不繁忙的時(shí)候，將臟頁刷到磁盤中
- redolog寫滿時(shí)(redolog的作用后面會(huì)提到)
- 數(shù)據(jù)庫關(guān)閉時(shí)會(huì)將所有臟頁刷回到磁盤
這樣搞，效率是不是高很多了?
當(dāng)需要更新的數(shù)據(jù)所在的頁已經(jīng)在Buffer Pool中時(shí)，只需要操作內(nèi)存即可，效率不是一般的高
「看到這小伙伴們可能會(huì)有一個(gè)疑問?如果對(duì)應(yīng)的臟頁還沒有被刷到磁盤中，數(shù)據(jù)庫就宕機(jī)了，那我們的更改不就丟失了?」
要解決這個(gè)問題，就不得不提到rodolog了。既然都打算說rodolog了，索性一塊說說mysql中的三種日志undolog，rodolog，binlog
undolog：如何讓更新的數(shù)據(jù)可以回滾?
以上面的student表為例，當(dāng)我們想把id=1的name從a變?yōu)閍bc時(shí)，會(huì)把原來的值id=1，name=a寫入到undo log中。當(dāng)這條更新語句在事務(wù)中執(zhí)行，當(dāng)事務(wù)回滾時(shí)，就可以通過undolog將數(shù)據(jù)恢復(fù)為原來的模樣。
此外，undo log在mvcc的實(shí)現(xiàn)中也扮演了重要的作用，看我之前寫的文章《面試官：MVCC是如何實(shí)現(xiàn)的?》
rodolog：系統(tǒng)宕機(jī)了，如何避免數(shù)據(jù)丟失?
接著我們上面的問題，如果對(duì)應(yīng)的臟頁還沒有被刷到磁盤中，數(shù)據(jù)庫就宕機(jī)了，那我們的更改不久丟失了?
為了解決這個(gè)問題，我們需要把內(nèi)存所做的修改寫入到 redo log buffer中，這是內(nèi)存里的一個(gè)緩沖區(qū)，用來存在redo日志。
rodo log記錄了你對(duì)數(shù)據(jù)所做的修改，如“將id=1這條數(shù)據(jù)的name從a變?yōu)閍bc”，物理日志哈，后面會(huì)再提一下。「redo log是順序?qū)懰员入S機(jī)寫效率高」
「InnoDB的redo log是固定大小的」，比如可以配置為一組 4 個(gè)文件，每個(gè)文件的大小是 1GB，那么總大小為4GB。從頭開始寫，寫到末尾就又回到開頭循環(huán)寫，如下面這個(gè)圖所示。
write pos是當(dāng)前要寫的位置，checkpoint是要擦除的位置，擦除前要把對(duì)應(yīng)的臟頁刷回到磁盤中。write pos和checkpoint中間的位置是可以寫的位置。
當(dāng)我們的系統(tǒng)能支持的并發(fā)比較低時(shí)，可以看看對(duì)應(yīng)的redo log是不是設(shè)置的太小了。太小的話會(huì)導(dǎo)致頻繁的刷臟頁，影響并發(fā)，可以通過工具監(jiān)控redo log的大小
redolog的大小=innodb_log_file_size*innodb_log_files_in_group(默認(rèn)為2)圖片「接下來我們?cè)敿?xì)聊聊，redolog是如何避免數(shù)據(jù)丟失的」
事務(wù)未提交，MySQL宕機(jī)，這種情況Buffer Pool中的數(shù)據(jù)丟失，并且redo log buffer中的日志也會(huì)丟失，不會(huì)影響數(shù)據(jù)
提交事務(wù)成功，redo log buffer中的數(shù)據(jù)沒有刷到磁盤，此時(shí)會(huì)導(dǎo)致事務(wù)提交的數(shù)據(jù)丟失。
「鑒于這種情況，我們可以通過設(shè)置innodb_flush_log_at_trx_commit來決定redo log的刷盤策略」
查看innodb_flush_log_at_trx_commit的配置
- SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit'
| innodb_flush_log_at_trx_commit值 | 作用 |
|---|---|
| 0 | 提交事務(wù)時(shí)，不會(huì)將redo log buffer中的數(shù)據(jù)寫入os buffer，而是每秒寫入os buffer并刷到磁盤 |
| 1 | 提交事務(wù)時(shí)，必須把redo log從內(nèi)存刷入到磁盤文件中 |
| 2 | 提交事務(wù)時(shí)，將rodo log寫入os buffer中，默認(rèn)每隔1s將os buffer中的數(shù)據(jù)刷入磁盤 |
應(yīng)為0和2都可能會(huì)造成事務(wù)更新丟失，所以一般系統(tǒng)中innodb_flush_log_at_trx_commit的值都為1，你可以看看你們的系統(tǒng)用的哪個(gè)值?
binlog：主從庫之間如何同步數(shù)據(jù)?
當(dāng)我們把mysql主庫的數(shù)據(jù)同步到從庫，或者其他數(shù)據(jù)源時(shí)，如es，bi庫時(shí)，只需要訂閱主庫的binlog即可。
「binlog這一節(jié)的很多內(nèi)容參考了《MySQL實(shí)戰(zhàn)45講》的02節(jié)，有些內(nèi)容在02節(jié)做了詳細(xì)的解釋，我就不多介紹了，可以結(jié)合著看本文」
為什么要弄2種日志呢?其實(shí)這都是由歷史原因決定的
MySQL剛開始用binlog實(shí)現(xiàn)歸檔的功能，但是binlog沒有crash-safe的能力，所以后來InnoDB引擎加了redo log來實(shí)現(xiàn)crash-safe。假如MySQL中只有一個(gè)InnoDB引擎，說不定就能用redo log來實(shí)現(xiàn)歸檔了，此時(shí)就可以將redo log和 binlog合并到一塊了
這兩種日志的區(qū)別如下：
- redo log是InnoDB存儲(chǔ)引擎特有，binglog是MySQL的server層實(shí)現(xiàn)的，所有引擎都可以使用
- redo log是物理日志，記錄的是數(shù)據(jù)頁上的修改。binlog是邏輯日志，記錄的是語句的原始邏輯，如給id=2的這一行的c字段加1
- redo log是固定空間，循環(huán)寫。binlog是追加寫，當(dāng)binlog文件寫到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志
「我們可以通過設(shè)置sync_binlog來決定binlog的刷盤策略」
| sync_binlog值 | 作用 |
|---|---|
| 0 | 不立即刷盤，將binlog寫入os buffer，由操作系統(tǒng)決定何時(shí)刷盤 ，有可能會(huì)丟失多個(gè)事務(wù)的數(shù)據(jù) |
| 1 | 將binlog寫入os buffer，每n個(gè)事務(wù)提交后，將os buffer的數(shù)據(jù)刷盤 |
一般情況下將sync_binlog的值設(shè)為1即可
兩階段提交
接著我們來看一下將id=2的行c字段加1的執(zhí)行流程。
前面的這個(gè)階段大家應(yīng)該都能看懂了把，沒看懂的可以看一下《MySQL實(shí)戰(zhàn)45講 》，重點(diǎn)說一下最后三個(gè)階段
引擎將新數(shù)據(jù)更新到內(nèi)存中，將操作記錄到redo log中，此時(shí)redo log處于prepare狀態(tài)，然后告知執(zhí)行器執(zhí)行完成了，可以提交事務(wù)
執(zhí)行器生成操作的binlog，并把binlog寫入磁盤
引擎將寫入的redo log改為提交狀態(tài)，更新完成
「為什么要把relog的寫入拆成2個(gè)步驟?即prepare和commit，兩階段提交」
因?yàn)椴还苣阆葘憆edolog還是binlog，奔潰發(fā)生后，最終其實(shí)都有可能會(huì)造成原庫和用日志恢復(fù)出來的庫不一致
「而兩階段提交可以避免這個(gè)問題」
redolog和binlog具有關(guān)聯(lián)行，在恢復(fù)數(shù)據(jù)時(shí)，redolog用于恢復(fù)主機(jī)故障時(shí)的未更新的物理數(shù)據(jù)，binlog用于備份操作。每個(gè)階段的log操作都是記錄在磁盤的，在恢復(fù)數(shù)據(jù)時(shí)，redolog 狀態(tài)為commit則說明binlog也成功，直接恢復(fù)數(shù)據(jù);如果redolog是prepare，則需要查詢對(duì)應(yīng)的binlog事務(wù)是否成功，決定是回滾還是執(zhí)行。
說說我踩過的一些坑
「1. 數(shù)據(jù)庫支持的并發(fā)度不高」
在一些并發(fā)要求高的系統(tǒng)中，可以調(diào)高Buffer Pool和redo log，這樣可以避免頻繁的刷臟頁，提高并發(fā)
「2. 事務(wù)提交很慢」
原來我負(fù)責(zé)的一個(gè)系統(tǒng)跑的挺正常的，直到上游系統(tǒng)每天2點(diǎn)瘋狂調(diào)我接口，然后我這邊都是事務(wù)方法，事務(wù)提交很慢。監(jiān)控到Buffer Pool和redo log的設(shè)置都很合理，并沒有太小，所以問題出在哪了?我也不知道
「后來dba排查到原因，把復(fù)制方式從半同步復(fù)制改為異步復(fù)制解決了這個(gè)問題」
「異步復(fù)制」：MySQL默認(rèn)的復(fù)制即是異步的，主庫在執(zhí)行完客戶端提交的事務(wù)后會(huì)立即將結(jié)果返給給客戶端，并不關(guān)心從庫是否已經(jīng)接收并處理，這樣就會(huì)有一個(gè)問題，主如果crash掉了，此時(shí)主上已經(jīng)提交的事務(wù)可能并沒有傳到從庫上，如果此時(shí)，強(qiáng)行將從提升為主，可能導(dǎo)致新主上的數(shù)據(jù)不完整
「半同步復(fù)制」：是介于全同步復(fù)制與全異步復(fù)制之間的一種，主庫只需要等待至少一個(gè)從庫節(jié)點(diǎn)收到并且 Flush Binlog 到 Relay Log 文件即可，主庫不需要等待所有從庫給主庫反饋。同時(shí)，這里只是一個(gè)收到的反饋，而不是已經(jīng)完全完成并且提交的反饋，如此，節(jié)省了很多時(shí)間
「全同步復(fù)制」：指當(dāng)主庫執(zhí)行完一個(gè)事務(wù)，所有的從庫都執(zhí)行了該事務(wù)才返回給客戶端。因?yàn)樾枰却袕膸靾?zhí)行完該事務(wù)才能返回，所以全同步復(fù)制的性能必然會(huì)收到嚴(yán)重的影響
「3. 在一個(gè)方法中，我先插入了一條數(shù)據(jù)，然后過一會(huì)再查一遍，結(jié)果插入成功，卻沒有查出來」
這個(gè)比較容易排查，如果系統(tǒng)中采用了數(shù)據(jù)庫的讀寫分離時(shí)，寫插入的是主庫，讀的卻是從庫，binlog同步比較慢時(shí)，就會(huì)出現(xiàn)這種情況，此時(shí)只需要讓這個(gè)方法強(qiáng)制走主庫即可
本文轉(zhuǎn)載自微信公眾號(hào)「Java識(shí)堂」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系Java識(shí)堂公眾號(hào)。
