[[399706]]

MySQL數(shù)據(jù)存儲(chǔ)和查詢(xún)流程

假如說(shuō)現(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í)是存到頁(yè)中的，一頁(yè)的大小為16k，一個(gè)表由很多頁(yè)組成，這些頁(yè)組成了B+樹(shù)」，最終的組織形式如下所示，具體的構(gòu)建過(guò)程我就不詳細(xì)介紹了，可以看我之前的文章《10張圖，搞懂索引為什么會(huì)失效?》

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

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

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

如果這樣搞的話(huà)，那一條sql的執(zhí)行過(guò)程可太慢了，因?yàn)閷?duì)一個(gè)大磁盤(pán)文件的讀寫(xiě)操作是要耗費(fèi)幾百萬(wàn)毫秒的

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

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

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

「這些臟頁(yè)是多會(huì)刷回到磁盤(pán)中的?」 有如下幾個(gè)時(shí)機(jī)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

redolog的大小=innodb_log_file_size*innodb_log_files_in_group(默認(rèn)為2)圖片「接下來(lái)我們?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ù)沒(méi)有刷到磁盤(pán)，此時(shí)會(huì)導(dǎo)致事務(wù)提交的數(shù)據(jù)丟失。

「鑒于這種情況，我們可以通過(guò)設(shè)置innodb_flush_log_at_trx_commit來(lái)決定redo log的刷盤(pán)策略」

查看innodb_flush_log_at_trx_commit的配置

SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit' 

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

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

當(dāng)我們把mysql主庫(kù)的數(shù)據(jù)同步到從庫(kù)，或者其他數(shù)據(jù)源時(shí)，如es，bi庫(kù)時(shí)，只需要訂閱主庫(kù)的binlog即可。

「binlog這一節(jié)的很多內(nèi)容參考了《MySQL實(shí)戰(zhàn)45講》的02節(jié)，有些內(nèi)容在02節(jié)做了詳細(xì)的解釋?zhuān)揖筒欢嘟榻B了，可以結(jié)合著看本文」

為什么要弄2種日志呢?其實(shí)這都是由歷史原因決定的

MySQL剛開(kāi)始用binlog實(shí)現(xiàn)歸檔的功能，但是binlog沒(méi)有crash-safe的能力，所以后來(lái)InnoDB引擎加了redo log來(lái)實(shí)現(xiàn)crash-safe。假如MySQL中只有一個(gè)InnoDB引擎，說(shuō)不定就能用redo log來(lái)實(shí)現(xiàn)歸檔了，此時(shí)就可以將redo log和 binlog合并到一塊了

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

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

「我們可以通過(guò)設(shè)置sync_binlog來(lái)決定binlog的刷盤(pán)策略」

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

兩階段提交

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

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

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

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

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

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

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

「而兩階段提交可以避免這個(gè)問(wèn)題」

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

說(shuō)說(shuō)我踩過(guò)的一些坑

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

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

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

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

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

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

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

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

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

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

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