[[399706]]

MySQL數(shù)據(jù)存儲和查詢流程

假如說現(xiàn)在我們建了如下一張表

CREATE TABLE `student` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '學(xué)號', 
  `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ù)最終會持久化到文件中，那么這些數(shù)據(jù)在文件中是如何組織的?難道是一行一行追加到文件中的?其實(shí)并不是，「數(shù)據(jù)其實(shí)是存到頁中的，一頁的大小為16k，一個表由很多頁組成，這些頁組成了B+樹」，最終的組織形式如下所示，具體的構(gòu)建過程我就不詳細(xì)介紹了，可以看我之前的文章《10張圖，搞懂索引為什么會失效?》

那么SQL語句是如何執(zhí)行的呢?MySQL的邏輯架構(gòu)圖如下所示

詳細(xì)結(jié)構(gòu)如為

「當(dāng)我們想更新某條數(shù)據(jù)的時候，難道是從磁盤中加載出來這條數(shù)據(jù)，更新后再持久化到磁盤中嗎?」

如果這樣搞的話，那一條sql的執(zhí)行過程可太慢了，因?yàn)閷σ粋€大磁盤文件的讀寫操作是要耗費(fèi)幾百萬毫秒的

真實(shí)的執(zhí)行過程是，當(dāng)我們想更新或者讀取某條數(shù)據(jù)的時候，會把對應(yīng)的頁加載到內(nèi)存中的Buffer Pool緩沖池中(默認(rèn)為128m，當(dāng)然為了提高系統(tǒng)的并發(fā)度，你可以把這個值設(shè)大一點(diǎn))

之所以加載頁到Buffer Pool中，是考慮到當(dāng)你使用這個頁的數(shù)據(jù)時，這個頁的其他數(shù)據(jù)使用到的概率頁很大，隨機(jī)IO的耗時很長，所以多加載一點(diǎn)數(shù)據(jù)到Buffer Pool

當(dāng)更新數(shù)據(jù)的時候，如果對應(yīng)的頁在Buffer Pool中，則直接更新Buffer Pool中的頁即可，對應(yīng)的頁不在Buffer Pool中時，才會從磁盤加載對應(yīng)的頁到Buffer Pool，然后再更新，「此時Buffer Pool中的頁和磁盤中的頁數(shù)據(jù)是不一致的，被稱為臟頁」。這些臟頁是要被刷回到磁盤中的

「這些臟頁是多會刷回到磁盤中的?」 有如下幾個時機(jī)

1. Buffer Pool不夠用了，要給新加載的頁騰位置了，所以會利用改進(jìn)的后的LRU算法，將一些臟頁刷回磁盤
2. 后臺線程會在MySQL不繁忙的時候，將臟頁刷到磁盤中
3. redolog寫滿時(redolog的作用后面會提到)
4. 數(shù)據(jù)庫關(guān)閉時會將所有臟頁刷回到磁盤

這樣搞，效率是不是高很多了?

當(dāng)需要更新的數(shù)據(jù)所在的頁已經(jīng)在Buffer Pool中時，只需要操作內(nèi)存即可，效率不是一般的高

「看到這小伙伴們可能會有一個疑問?如果對應(yīng)的臟頁還沒有被刷到磁盤中，數(shù)據(jù)庫就宕機(jī)了，那我們的更改不就丟失了?」

要解決這個問題，就不得不提到rodolog了。既然都打算說rodolog了，索性一塊說說mysql中的三種日志undolog，rodolog，binlog

undolog：如何讓更新的數(shù)據(jù)可以回滾?

以上面的student表為例，當(dāng)我們想把id=1的name從a變?yōu)閍bc時，會把原來的值id=1，name=a寫入到undo log中。當(dāng)這條更新語句在事務(wù)中執(zhí)行，當(dāng)事務(wù)回滾時，就可以通過undolog將數(shù)據(jù)恢復(fù)為原來的模樣。

此外，undo log在mvcc的實(shí)現(xiàn)中也扮演了重要的作用，看我之前寫的文章《面試官：MVCC是如何實(shí)現(xiàn)的?》

rodolog：系統(tǒng)宕機(jī)了，如何避免數(shù)據(jù)丟失?

接著我們上面的問題，如果對應(yīng)的臟頁還沒有被刷到磁盤中，數(shù)據(jù)庫就宕機(jī)了，那我們的更改不久丟失了?

為了解決這個問題，我們需要把內(nèi)存所做的修改寫入到 redo log buffer中，這是內(nèi)存里的一個緩沖區(qū)，用來存在redo日志。

rodo log記錄了你對數(shù)據(jù)所做的修改，如“將id=1這條數(shù)據(jù)的name從a變?yōu)閍bc”，物理日志哈，后面會再提一下?！竢edo log是順序?qū)懰员入S機(jī)寫效率高」

「InnoDB的redo log是固定大小的」，比如可以配置為一組 4 個文件，每個文件的大小是 1GB，那么總大小為4GB。從頭開始寫，寫到末尾就又回到開頭循環(huán)寫，如下面這個圖所示。

write pos是當(dāng)前要寫的位置，checkpoint是要擦除的位置，擦除前要把對應(yīng)的臟頁刷回到磁盤中。write pos和checkpoint中間的位置是可以寫的位置。

當(dāng)我們的系統(tǒng)能支持的并發(fā)比較低時，可以看看對應(yīng)的redo log是不是設(shè)置的太小了。太小的話會導(dǎo)致頻繁的刷臟頁，影響并發(fā)，可以通過工具監(jiān)控redo log的大小

redolog的大小=innodb_log_file_size*innodb_log_files_in_group(默認(rèn)為2)圖片「接下來我們詳細(xì)聊聊，redolog是如何避免數(shù)據(jù)丟失的」

事務(wù)未提交，MySQL宕機(jī)，這種情況Buffer Pool中的數(shù)據(jù)丟失，并且redo log buffer中的日志也會丟失，不會影響數(shù)據(jù)

提交事務(wù)成功，redo log buffer中的數(shù)據(jù)沒有刷到磁盤，此時會導(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' 

應(yīng)為0和2都可能會造成事務(wù)更新丟失，所以一般系統(tǒng)中innodb_flush_log_at_trx_commit的值都為1，你可以看看你們的系統(tǒng)用的哪個值?

binlog：主從庫之間如何同步數(shù)據(jù)?

當(dāng)我們把mysql主庫的數(shù)據(jù)同步到從庫，或者其他數(shù)據(jù)源時，如es，bi庫時，只需要訂閱主庫的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中只有一個InnoDB引擎，說不定就能用redo log來實(shí)現(xiàn)歸檔了，此時就可以將redo log和 binlog合并到一塊了

這兩種日志的區(qū)別如下：

• redo log是InnoDB存儲引擎特有，binglog是MySQL的server層實(shí)現(xiàn)的，所有引擎都可以使用
• redo log是物理日志，記錄的是數(shù)據(jù)頁上的修改。binlog是邏輯日志，記錄的是語句的原始邏輯，如給id=2的這一行的c字段加1
• redo log是固定空間，循環(huán)寫。binlog是追加寫，當(dāng)binlog文件寫到一定大小后會切換到下一個，并不會覆蓋以前的日志

「我們可以通過設(shè)置sync_binlog來決定binlog的刷盤策略」

一般情況下將sync_binlog的值設(shè)為1即可

兩階段提交

接著我們來看一下將id=2的行c字段加1的執(zhí)行流程。

前面的這個階段大家應(yīng)該都能看懂了把，沒看懂的可以看一下《MySQL實(shí)戰(zhàn)45講 》，重點(diǎn)說一下最后三個階段

引擎將新數(shù)據(jù)更新到內(nèi)存中，將操作記錄到redo log中，此時redo log處于prepare狀態(tài)，然后告知執(zhí)行器執(zhí)行完成了，可以提交事務(wù)

執(zhí)行器生成操作的binlog，并把binlog寫入磁盤

引擎將寫入的redo log改為提交狀態(tài)，更新完成

「為什么要把relog的寫入拆成2個步驟?即prepare和commit，兩階段提交」

因?yàn)椴还苣阆葘憆edolog還是binlog，奔潰發(fā)生后，最終其實(shí)都有可能會造成原庫和用日志恢復(fù)出來的庫不一致

「而兩階段提交可以避免這個問題」

redolog和binlog具有關(guān)聯(lián)行，在恢復(fù)數(shù)據(jù)時，redolog用于恢復(fù)主機(jī)故障時的未更新的物理數(shù)據(jù)，binlog用于備份操作。每個階段的log操作都是記錄在磁盤的，在恢復(fù)數(shù)據(jù)時，redolog 狀態(tài)為commit則說明binlog也成功，直接恢復(fù)數(shù)據(jù);如果redolog是prepare，則需要查詢對應(yīng)的binlog事務(wù)是否成功，決定是回滾還是執(zhí)行。

說說我踩過的一些坑

「1. 數(shù)據(jù)庫支持的并發(fā)度不高」

在一些并發(fā)要求高的系統(tǒng)中，可以調(diào)高Buffer Pool和redo log，這樣可以避免頻繁的刷臟頁，提高并發(fā)

「2. 事務(wù)提交很慢」

原來我負(fù)責(zé)的一個系統(tǒng)跑的挺正常的，直到上游系統(tǒng)每天2點(diǎn)瘋狂調(diào)我接口，然后我這邊都是事務(wù)方法，事務(wù)提交很慢。監(jiān)控到Buffer Pool和redo log的設(shè)置都很合理，并沒有太小，所以問題出在哪了?我也不知道

「后來dba排查到原因，把復(fù)制方式從半同步復(fù)制改為異步復(fù)制解決了這個問題」

「異步復(fù)制」：MySQL默認(rèn)的復(fù)制即是異步的，主庫在執(zhí)行完客戶端提交的事務(wù)后會立即將結(jié)果返給給客戶端，并不關(guān)心從庫是否已經(jīng)接收并處理，這樣就會有一個問題，主如果crash掉了，此時主上已經(jīng)提交的事務(wù)可能并沒有傳到從庫上，如果此時，強(qiáng)行將從提升為主，可能導(dǎo)致新主上的數(shù)據(jù)不完整

「半同步復(fù)制」：是介于全同步復(fù)制與全異步復(fù)制之間的一種，主庫只需要等待至少一個從庫節(jié)點(diǎn)收到并且 Flush Binlog 到 Relay Log 文件即可，主庫不需要等待所有從庫給主庫反饋。同時，這里只是一個收到的反饋，而不是已經(jīng)完全完成并且提交的反饋，如此，節(jié)省了很多時間

「全同步復(fù)制」：指當(dāng)主庫執(zhí)行完一個事務(wù)，所有的從庫都執(zhí)行了該事務(wù)才返回給客戶端。因?yàn)樾枰却袕膸靾?zhí)行完該事務(wù)才能返回，所以全同步復(fù)制的性能必然會收到嚴(yán)重的影響

「3. 在一個方法中，我先插入了一條數(shù)據(jù)，然后過一會再查一遍，結(jié)果插入成功，卻沒有查出來」

這個比較容易排查，如果系統(tǒng)中采用了數(shù)據(jù)庫的讀寫分離時，寫插入的是主庫，讀的卻是從庫，binlog同步比較慢時，就會出現(xiàn)這種情況，此時只需要讓這個方法強(qiáng)制走主庫即可

本文轉(zhuǎn)載自微信公眾號「Java識堂」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系Java識堂公眾號。