這篇文章將從查詢緩存，索引，優(yōu)化器，explain，redo 日志，undo 日志，事務(wù)隔離級(jí)別，鎖等方面來(lái)講解 MySQL。

開局一張圖

這張圖是重點(diǎn)！！！咱要先對(duì) MySQL 有一個(gè)宏觀的了解，知道他的執(zhí)行流程。

一條 SQL 語(yǔ)句過(guò)來(lái)的流程是什么樣的？

①當(dāng)客戶端連接到 MySQL 服務(wù)器時(shí)，服務(wù)器對(duì)其進(jìn)行認(rèn)證。可以通過(guò)用戶名與密碼認(rèn)證，也可以通過(guò) SSL 證書進(jìn)行認(rèn)證。登錄認(rèn)證后，服務(wù)器還會(huì)驗(yàn)證客戶端是否有執(zhí)行某個(gè)查詢的操作權(quán)限。

②在正式查詢之前，服務(wù)器會(huì)檢查查詢緩存，如果能找到對(duì)應(yīng)的查詢，則不必進(jìn)行查詢解析，優(yōu)化，執(zhí)行等過(guò)程，直接返回緩存中的結(jié)果集。

③MySQL 的解析器會(huì)根據(jù)查詢語(yǔ)句，構(gòu)造出一個(gè)解析樹，主要用于根據(jù)語(yǔ)法規(guī)則來(lái)驗(yàn)證語(yǔ)句是否正確，比如 SQL 的關(guān)鍵字是否正確，關(guān)鍵字的順序是否正確。而預(yù)處理器主要是進(jìn)一步校驗(yàn)，比如表名，字段名是否正確等。

④查詢優(yōu)化器將解析樹轉(zhuǎn)化為查詢計(jì)劃，一般情況下，一條查詢可以有很多種執(zhí)行方式，最終返回相同的結(jié)果，優(yōu)化器就是根據(jù)成本找到這其中最優(yōu)的執(zhí)行計(jì)劃。

⑤執(zhí)行計(jì)劃調(diào)用查詢執(zhí)行引擎，而查詢引擎通過(guò)一系列 API 接口查詢到數(shù)據(jù)。

⑥得到數(shù)據(jù)之后，在返回給客戶端的同時(shí)，會(huì)將數(shù)據(jù)存在查詢緩存中。

查詢緩存

我們先通過(guò) show variables like '%query_cache%' 來(lái)看一下默認(rèn)的數(shù)據(jù)庫(kù)配置，此為本地?cái)?shù)據(jù)庫(kù)的配置。

概念

①have_query_cache：當(dāng)前的 MySQL 版本是否支持“查詢緩存”功能。

②query_cache_limit：MySQL 能夠緩存的最大查詢結(jié)果，查詢結(jié)果大于該值時(shí)不會(huì)被緩存。默認(rèn)值是 1048576(1MB)。

③query_cache_min_res_unit：查詢緩存分配的最小塊（字節(jié)）。默認(rèn)值是 4096（4KB）。

當(dāng)查詢進(jìn)行時(shí)，MySQL 把查詢結(jié)果保存在 query cache，但是如果保存的結(jié)果比較大，超過(guò)了 query_cache_min_res_unit 的值，這時(shí)候 MySQL 將一邊檢索結(jié)果，一邊進(jìn)行保存結(jié)果。

他保存結(jié)果也是按默認(rèn)大小先分配一塊空間，如果不夠，又要申請(qǐng)新的空間給他。

如果查詢結(jié)果比較小，默認(rèn)的 query_cache_min_res_unit 可能造成大量的內(nèi)存碎片，如果查詢結(jié)果比較大，默認(rèn)的 query_cache_min_res_unit 又不夠，導(dǎo)致一直分配塊空間。

所以可以根據(jù)實(shí)際需求，調(diào)節(jié) query_cache_min_res_unit 的大小。

注：如果上面說(shuō)的內(nèi)容有點(diǎn)彎彎繞，那舉個(gè)現(xiàn)實(shí)生活中的例子，比如咱現(xiàn)在要給運(yùn)動(dòng)員送水，默認(rèn)的是 500ml 的瓶子，如果過(guò)來(lái)的是少年運(yùn)動(dòng)員，可能 500ml 太大了，他們喝不完，造成了浪費(fèi)。

那我們就可以選擇 300ml 的瓶子，如果過(guò)來(lái)的是成年運(yùn)動(dòng)員，可能 500ml 不夠，那他們一瓶喝完了，又開一瓶，直接不渴為止。那么那樣開瓶子也要時(shí)間，我們就可以選擇 1000ml 的瓶子。

④query_cache_size：為緩存查詢結(jié)果分配的總內(nèi)存。

⑤query_cache_type：默認(rèn)為 on，可以緩存除了以 select sql_no_cache 開頭的所有查詢結(jié)果。

⑥query_cache_wlock_invalidate：如果該表被鎖住，是否返回緩存中的數(shù)據(jù)，默認(rèn)是關(guān)閉的。

原理

MySQL 的查詢緩存實(shí)質(zhì)上是緩存 SQL 的 Hash 值和該 SQL 的查詢結(jié)果，如果運(yùn)行相同的 SQL，服務(wù)器直接從緩存中去掉結(jié)果，而不再去解析，優(yōu)化，尋找最低成本的執(zhí)行計(jì)劃等一系列操作，大大提升了查詢速度。

但是萬(wàn)事有利也有弊：

第一個(gè)弊端就是如果表的數(shù)據(jù)有一條發(fā)生變化，那么緩存好的結(jié)果將全部不再有效。這對(duì)于頻繁更新的表，查詢緩存是不適合的。

比如一張表里面只有兩個(gè)字段，分別是 id 和 name，數(shù)據(jù)有一條為 1，張三。

我使用 select * from 表名 where name=“張三”來(lái)進(jìn)行查詢，MySQL 發(fā)現(xiàn)查詢緩存中沒有此數(shù)據(jù)，會(huì)進(jìn)行一系列的解析，優(yōu)化等操作進(jìn)行數(shù)據(jù)的查詢。

查詢結(jié)束之后將該 SQL 的 Hash 和查詢結(jié)果緩存起來(lái)，并將查詢結(jié)果返回給客戶端。

但是這個(gè)時(shí)候我又新增了一條數(shù)據(jù) 2，張三。如果我還用相同的 SQL 來(lái)執(zhí)行，他會(huì)根據(jù)該 SQL 的 Hash 值去查詢緩存中，那么結(jié)果就錯(cuò)了。

所以 MySQL 對(duì)于數(shù)據(jù)有變化的表來(lái)說(shuō)，會(huì)直接清空關(guān)于該表的所有緩存。這樣其實(shí)效率是很差的。

第二個(gè)弊端就是緩存機(jī)制是通過(guò)對(duì) SQL 的 Hash，得出的值為 Key，查詢結(jié)果為 Value 來(lái)存放的，那么就意味著 SQL 必須完完全全一模一樣，否則就命不中緩存。

我們都知道 Hash 值的規(guī)則，就算很小的查詢，哈希出來(lái)的結(jié)果差距是很多的，所以 select * from 表名 where name=“張三”和SELECT * FROM 表名 WHERE NAME=“張三”和select * from 表名 where name = “張三”，三個(gè)SQL 哈希出來(lái)的值是不一樣的。

大小寫和空格影響了他們，所以并不能命中緩存，但其實(shí)他們搜索結(jié)果是完全一樣的。

生產(chǎn)如何設(shè)置 MySQL Query Cache

先來(lái)看線上參數(shù)：

我們發(fā)現(xiàn)將 query_cache_type 設(shè)置為 OFF，其實(shí)網(wǎng)上資料和各大云廠商提供的云服務(wù)器都是將這個(gè)功能關(guān)閉的，從上面的原理來(lái)看，在一般情況下，他的弊端大于優(yōu)點(diǎn)。

索引

例子：創(chuàng)建一個(gè)名為 user 的表，其包括 id，name，age，sex 等字段信息。此外，id 為主鍵聚簇索引，idx_name 為非聚簇索引。

CREATE TABLE `user` (

  `id` varchar(10) NOT NULL DEFAULT '',

  `name` varchar(10) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `sex` varchar(10) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_name` (`name`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我們將其設(shè)置 10 條數(shù)據(jù)，便于下面的索引的理解：

INSERT INTO `user` VALUES ('1', 'andy', '20', '女');

INSERT INTO `user` VALUES ('10', 'baby', '12', '女');

INSERT INTO `user` VALUES ('2', 'kat', '12', '女');

INSERT INTO `user` VALUES ('3', 'lili', '20', '男');

INSERT INTO `user` VALUES ('4', 'lucy', '22', '女');

INSERT INTO `user` VALUES ('5', 'bill', '20', '男');

INSERT INTO `user` VALUES ('6', 'zoe', '20', '男');

INSERT INTO `user` VALUES ('7', 'hay', '20', '女');

INSERT INTO `user` VALUES ('8', 'tony', '20', '男');

INSERT INTO `user` VALUES ('9', 'rose', '21', '男');

聚簇索引（主鍵索引）

先來(lái)一張圖鎮(zhèn)樓，接下來(lái)就是看圖說(shuō)話：

他包含兩個(gè)特點(diǎn)：

• 使用記錄主鍵值的大小來(lái)進(jìn)行記錄和頁(yè)的排序。頁(yè)內(nèi)的記錄是按照主鍵的大小順序排成一個(gè)單項(xiàng)鏈表。各個(gè)存放用戶記錄的頁(yè)也是根據(jù)頁(yè)中用戶記錄的主鍵大小順序排成一個(gè)雙向鏈表。

• 葉子節(jié)點(diǎn)存儲(chǔ)的是完整的用戶記錄。

注：聚簇索引不需要我們顯示的創(chuàng)建，他是由 InnoDB 存儲(chǔ)引擎自動(dòng)為我們創(chuàng)建的。如果沒有主鍵，其也會(huì)默認(rèn)創(chuàng)建一個(gè)。

非聚簇索引（二級(jí)索引）

上面的聚簇索引只能在搜索條件是主鍵時(shí)才能發(fā)揮作用，因?yàn)榫鄞厮饕梢愿鶕?jù)主鍵進(jìn)行排序的。

如果搜索條件是 name，在剛才的聚簇索引上，我們可能遍歷，挨個(gè)找到符合條件的記錄，但是，這樣真的是太蠢了，MySQL 不會(huì)這樣做的。

如果我們想讓搜索條件是 name 的時(shí)候，也能使用索引，那可以多創(chuàng)建一個(gè)基于 name 的二叉樹，如下圖：

他與聚簇索引的不同：

• 葉子節(jié)點(diǎn)內(nèi)部使用 name 字段排序，葉子節(jié)點(diǎn)之間也是使用 name 字段排序。

• 葉子節(jié)點(diǎn)不再是完整的數(shù)據(jù)記錄，而是 name 和主鍵值。

為什么不再是完整信息？MySQL 只讓聚簇索引的葉子節(jié)點(diǎn)存放完整的記錄信息，因?yàn)槿绻泻脦讉€(gè)非聚簇索引，他們的葉子節(jié)點(diǎn)也存放完整的記錄績(jī)效，那就不浪費(fèi)空間啦。

如果我搜索條件是基于 name，需要查詢所有字段的信息，那查詢過(guò)程是啥？

• 根據(jù)查詢條件，采用 name 的非聚簇索引，先定位到該非聚簇索引某些記錄行。

• 根據(jù)記錄行找到相應(yīng)的 id，再根據(jù) id 到聚簇索引中找到相關(guān)記錄。這個(gè)過(guò)程叫做回表。

聯(lián)合索引

圖就不畫了，簡(jiǎn)單來(lái)說(shuō)，如果 name 和 age 組成一個(gè)聯(lián)合索引，那么先按 name 排序，如果 name 一樣，就按 age 排序。

一些原則

①最左前綴原則。一個(gè)聯(lián)合索引（a,b,c）,如果有一個(gè)查詢條件有 a，有 b，那么他則走索引，如果有一個(gè)查詢條件沒有 a，那么他則不走索引。

②使用唯一索引。具有多個(gè)重復(fù)值的列，其索引效果最差。例如，存放姓名的列具有不同值，很容易區(qū)分每行。

而用來(lái)記錄性別的列，只含有“男”，“女”，不管搜索哪個(gè)值，都會(huì)得出大約一半的行，這樣的索引對(duì)性能的提升不夠高。

③不要過(guò)度索引。每個(gè)額外的索引都要占用額外的磁盤空間，并降低寫操作的性能。

在修改表的內(nèi)容時(shí)，索引必須進(jìn)行更新，有時(shí)可能需要重構(gòu)，因此，索引越多，所花的時(shí)間越長(zhǎng)。

④索引列不能參與計(jì)算，保持列“干凈”，比如 from_unixtime(create_time) = ’2014-05-29’就不能使用到索引。

原因很簡(jiǎn)單，B+ 樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。

所以語(yǔ)句應(yīng)該寫成：

create_time = unix_timestamp(’2014-05-29’);

⑤一定要設(shè)置一個(gè)主鍵。前面聚簇索引說(shuō)到如果不指定主鍵，InnoDB 會(huì)自動(dòng)為其指定主鍵，這個(gè)我們是看不見的。

反正都要生成一個(gè)主鍵的，還不如我們?cè)O(shè)置，以后在某些搜索條件時(shí)還能用到主鍵的聚簇索引。

⑥主鍵推薦用自增 id，而不是 uuid。上面的聚簇索引說(shuō)到每頁(yè)數(shù)據(jù)都是排序的，并且頁(yè)之間也是排序的，如果是 uuid，那么其肯定是隨機(jī)的，其可能從中間插入，導(dǎo)致頁(yè)的分裂，產(chǎn)生很多表碎片。

如果是自增的，那么其有從小到大自增的，有順序，那么在插入的時(shí)候就添加到當(dāng)前索引的后續(xù)位置。當(dāng)一頁(yè)寫滿，就會(huì)自動(dòng)開辟一個(gè)新的頁(yè)。

注：如果自增 id 用完了，那將字段類型改為 bigint，就算每秒 1 萬(wàn)條數(shù)據(jù)，跑 100 年，也沒達(dá)到 bigint 的最大值。

萬(wàn)年面試題（為什么索引用 B+ 樹）

①B+ 樹的磁盤讀寫代價(jià)更低：B+ 樹的內(nèi)部節(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針，因此其內(nèi)部節(jié)點(diǎn)相對(duì) B 樹更小。

如果把所有同一內(nèi)部節(jié)點(diǎn)的關(guān)鍵字存放在同一盤塊中，那么盤塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多，一次性讀入內(nèi)存的需要查找的關(guān)鍵字也就越多，相對(duì) IO 讀寫次數(shù)就降低了。

②由于 B+ 樹的數(shù)據(jù)都存儲(chǔ)在葉子結(jié)點(diǎn)中，分支結(jié)點(diǎn)均為索引，方便掃庫(kù)，只需要掃一遍葉子結(jié)點(diǎn)即可。

但是 B 樹因?yàn)槠浞种ЫY(jié)點(diǎn)同樣存儲(chǔ)著數(shù)據(jù)，我們要找到具體的數(shù)據(jù)，需要進(jìn)行一次中序遍歷按序來(lái)掃，所以 B+ 樹更加適合在區(qū)間查詢的情況，所以通常 B+ 樹用于數(shù)據(jù)庫(kù)索引。

優(yōu)化器

在開篇的圖里面，我們知道了 SQL 語(yǔ)句從客戶端經(jīng)由網(wǎng)絡(luò)協(xié)議到查詢緩存，如果沒有命中緩存，再經(jīng)過(guò)解析工作，得到準(zhǔn)確的 SQL，現(xiàn)在就來(lái)到了我們這模塊說(shuō)的優(yōu)化器。

首先，我們知道每一條 SQL 都有不同的執(zhí)行方法，要不通過(guò)索引，要不通過(guò)全表掃描的方式。

那么問(wèn)題就來(lái)了，MySQL 是如何選擇時(shí)間最短，占用內(nèi)存最小的執(zhí)行方法呢？

什么是成本？

• I/O 成本。數(shù)據(jù)存儲(chǔ)在硬盤上，我們想要進(jìn)行某個(gè)操作需要將其加載到內(nèi)存中，這個(gè)過(guò)程的時(shí)間被稱為 I/O 成本。默認(rèn)是 1。

• CPU 成本。在內(nèi)存對(duì)結(jié)果集進(jìn)行排序的時(shí)間被稱為 CPU 成本。默認(rèn)是 0.2。

單表查詢的成本

先來(lái)建一個(gè)用戶表 dev_user，里面包括主鍵 id，用戶名 username，密碼 password，外鍵 user_info_id，狀態(tài) status，外鍵 main_station_id，是否外網(wǎng)訪問(wèn) visit，這七個(gè)字段。

索引有兩個(gè)，一個(gè)是主鍵的聚簇索引，另一個(gè)是顯式添加的以 username 為字段的唯一索引 uname_unique。

如果搜索條件是 select * from dev_user where username='XXX'，那么 MySQL 是如何選擇相關(guān)索引呢？

①使用所有可能用到的索引

我們可以看到搜索條件 username，所以可能走 uname_unique 索引。也可以做聚簇索引，也就是全表掃描。

②計(jì)算全表掃描代價(jià)

我們通過(guò) show table status like ‘dev_user’命令知道 rows 和 data_length 字段，如下圖：

rows：表示表中的記錄條數(shù)，但是這個(gè)數(shù)據(jù)不準(zhǔn)確，是個(gè)估計(jì)值。

data_length：表示表占用的存儲(chǔ)空間字節(jié)數(shù)。data_length=聚簇索引的頁(yè)面數(shù)量 X 每個(gè)頁(yè)面的大小。

反推出頁(yè)面數(shù)量=1589248÷16÷1024=97：

• I/O 成本：97X1=97

• CPU 成本：6141X0.2=1228

• 總成本：97+1228=1325

③計(jì)算使用不同索引執(zhí)行查詢的代價(jià)

因?yàn)橐樵兂鰸M足條件的所有字段信息，所以要考慮回表成本：

• I/O 成本=1+1X1=2（范圍區(qū)間的數(shù)量+預(yù)計(jì)二級(jí)記錄索引條數(shù)）

• CPU 成本=1X0.2+1X0.2=0.4（讀取二級(jí)索引的成本+回表聚簇索引的成本）

• 總成本= I/O 成本+CPU 成本=2.4

④對(duì)比各種執(zhí)行方案的代價(jià)，找出成本最低的那個(gè)

上面兩個(gè)數(shù)字一對(duì)比，成本是采用 uname_unique 索引成本最低。

多表查詢的成本

對(duì)于兩表連接查詢來(lái)說(shuō)，他的查詢成本由下面兩個(gè)部分構(gòu)成：

• 單次查詢驅(qū)動(dòng)表的成本

• 多次查詢被驅(qū)動(dòng)表的成本（具體查詢多次取決于對(duì)驅(qū)動(dòng)表查詢的結(jié)果集有多少個(gè)記錄）

index dive

如果前面的搜索條件不是等值，而是區(qū)間，如 select * from dev_user where username>'admin' and username<'test' 這個(gè)時(shí)候我們是無(wú)法看出需要回表的數(shù)量。

步驟 1：先根據(jù) username>'admin' 這個(gè)條件找到第一條記錄，稱為區(qū)間最左記錄。

步驟 2：再根據(jù) username<'test' 這個(gè)條件找到最后一條記錄，稱為區(qū)間最右記錄。

步驟 3：如果區(qū)間最左記錄和區(qū)間最右記錄相差不是很遠(yuǎn)，可以準(zhǔn)確統(tǒng)計(jì)出需要回表的數(shù)量。

如果相差很遠(yuǎn)，就先計(jì)算 10 頁(yè)有多少條記錄，再乘以頁(yè)面數(shù)量，最終模糊統(tǒng)計(jì)出來(lái)。

Explain

產(chǎn)品來(lái)索命：

• 產(chǎn)品：為什么這個(gè)頁(yè)面出來(lái)這么慢？

• 開發(fā)：因?yàn)槟悴榈臄?shù)據(jù)多唄，他就是這么慢

• 產(chǎn)品：我不管，我要這個(gè)頁(yè)面快點(diǎn)，你這樣，客戶怎么用啊

• 開發(fā)：......你行你來(lái)

哈哈哈哈，不瞎 BB 啦，如果有些 SQL 賊慢，我們需要知道他有沒有走索引，走了哪個(gè)索引，這個(gè)時(shí)候我就需要通過(guò) explain 關(guān)鍵字來(lái)深入了解 MySQL 內(nèi)部是如何執(zhí)行的。

id：一般來(lái)說(shuō)一個(gè) select 一個(gè)唯一 id，如果是子查詢，就有兩個(gè) select，id 是不一樣的，但是凡事有例外，有些子查詢的，他們 id 是一樣的。

這是為什么呢？那是因?yàn)?MySQL 在進(jìn)行優(yōu)化的時(shí)候已經(jīng)將子查詢改成了連接查詢，而連接查詢的 id 是一樣的。

select_type：

• simple：不包括 union 和子查詢的查詢都算 simple 類型。

• primary：包括 union，union all，其中最左邊的查詢即為 primary。

• union：包括 union，union all，除了最左邊的查詢，其他的查詢類型都為 union。

table：顯示這一行是關(guān)于哪張表的。

type 訪問(wèn)方法：

• ref：普通二級(jí)索引與常量進(jìn)行等值匹配

• ref_or_null：普通二級(jí)索引與常量進(jìn)行等值匹配，該索引可能是 null

• const：主鍵或唯一二級(jí)索引列與常量進(jìn)行等值匹配

• range：范圍區(qū)間的查詢

• all：全表掃描

possible_keys：對(duì)某表進(jìn)行單表查詢時(shí)可能用到的索引。

key：經(jīng)過(guò)查詢優(yōu)化器計(jì)算不同索引的成本，最終選擇成本最低的索引。

rows：

• 如果使用全表掃描，那么 rows 就代表需要掃描的行數(shù)

• 如果使用索引，那么 rows 就代表預(yù)計(jì)掃描的行數(shù)

filtered：

• 如果全表掃描，那么 filtered 就代表滿足搜索條件的記錄的滿分比

• 如果是索引，那么 filtered 就代表除去索引對(duì)應(yīng)的搜索，其他搜索條件的百分比

redo 日志（物理日志）

InnoDB 存儲(chǔ)引擎是以頁(yè)為單位來(lái)管理存儲(chǔ)空間的，我們進(jìn)行的增刪改查操作都是將頁(yè)的數(shù)據(jù)加載到內(nèi)存中，然后進(jìn)行操作，再將數(shù)據(jù)刷回到硬盤上。

那么問(wèn)題就來(lái)了，如果我要給張三轉(zhuǎn)賬 100 塊錢，事務(wù)已經(jīng)提交了，這個(gè)時(shí)候 InnoDB 把數(shù)據(jù)加載到內(nèi)存中，這個(gè)時(shí)候還沒來(lái)得及刷入硬盤，突然停電了，數(shù)據(jù)庫(kù)崩了。

重啟之后，發(fā)現(xiàn)我的錢沒有轉(zhuǎn)成功，這不是尷尬了嗎？

解決方法很明顯，我們?cè)谟脖P加載到內(nèi)存之后，進(jìn)行一系列操作，一頓操作猛如虎，還未刷新到硬盤之前，先記錄下，在 XXX 位置我的記錄中金額減 100，在 XXX 位置張三的記錄中金額加 100。

然后再進(jìn)行增刪改查操作，最后刷入硬盤。如果未刷入硬盤，在重啟之后，先加載之前的記錄，那么數(shù)據(jù)就回來(lái)了。

這個(gè)記錄就叫做重做日志，即 redo 日志。他的目的是想讓已經(jīng)提交的事務(wù)對(duì)數(shù)據(jù)的修改是永久的，就算他重啟，數(shù)據(jù)也能恢復(fù)出來(lái)。

log buffer（日志緩沖區(qū)）

為了解決磁盤速度過(guò)慢的問(wèn)題，redo 日志不能直接寫入磁盤，咱先整一大片連續(xù)的內(nèi)存空間給他放數(shù)據(jù)。

這一大片內(nèi)存就叫做日志緩沖區(qū)，即 log buffer。到了合適的時(shí)候，再刷入硬盤。至于什么時(shí)候是合適的，這個(gè)下一章節(jié)說(shuō)。

我們可以通過(guò) show VARIABLES like 'innodb_log_buffer_size' 命令來(lái)查看當(dāng)前的日志緩存大小。

下圖為線上的大小：

redo 日志刷盤時(shí)機(jī)

由于 redo 日志一直都是增長(zhǎng)的，且內(nèi)存空間有限，數(shù)據(jù)也不能一直待在緩存中，我們需要將其刷新至硬盤上。 

那什么時(shí)候刷新到硬盤呢？

• log buffer 空間不足。上面有指定緩沖區(qū)的內(nèi)存大小，MySQL 認(rèn)為日志量已經(jīng)占了總?cè)萘康囊话胱笥遥托枰獙⑦@些日志刷新到磁盤上。 

• 事務(wù)提交時(shí)。我們使用 redo 日志的目的就是將他未刷新到磁盤的記錄保存起來(lái)，防止丟失，如果數(shù)據(jù)提交了，我們是可以不把數(shù)據(jù)提交到磁盤的，但為了保證持久性，必須把修改這些頁(yè)面的 redo 日志刷新到磁盤。 

• 后臺(tái)線程不同的刷新后臺(tái)有一個(gè)線程，大概每秒都會(huì)將 log buffer 里面的 redo 日志刷新到硬盤上。

• checkpoint 下下小節(jié)講。

redo 日志文件組

我們可以通過(guò) show variables like 'datadir' 命令找到相關(guān)目錄，底下有兩個(gè)文件，分別是 ib_logfile0 和 ib_logfile1，如下圖所示：

我們將緩沖區(qū) log buffer 里面的 redo 日志刷新到這個(gè)兩個(gè)文件里面，他們寫入的方式是循環(huán)寫入的，先寫 ib_logfile0，再寫 ib_logfile1，等 ib_logfile1 寫滿了，再寫 ib_logfile0。 

那這樣就會(huì)存在一個(gè)問(wèn)題，如果 ib_logfile1 寫滿了，再寫 ib_logfile0，之前 ib_logfile0 的內(nèi)容不就被覆蓋而丟失了嗎？這就是 checkpoint 的工作啦。

checkpoint

redo 日志是為了系統(tǒng)崩潰后恢復(fù)臟頁(yè)用的，如果這個(gè)臟頁(yè)可以被刷新到磁盤上，那么他就可以功成身退，被覆蓋也就沒事啦。

沖突補(bǔ)習(xí)：從系統(tǒng)運(yùn)行開始，就不斷的修改頁(yè)面，會(huì)不斷的生成 redo 日志。

redo 日志是不斷遞增的，MySQL 為其取了一個(gè)名字日志序列號(hào) Log Sequence Number，簡(jiǎn)稱 lsn。他的初始化的值為 8704，用來(lái)記錄當(dāng)前一共生成了多少 redo 日志。

redo 日志是先寫入 log buffer，之后才會(huì)被刷新到磁盤的 redo 日志文件。MySQL 為其取了一個(gè)名字 flush_to_disk_lsn。

用來(lái)說(shuō)明緩存區(qū)中有多少的臟頁(yè)數(shù)據(jù)被刷新到磁盤上啦。他的初始值和 lsn 一樣，后面的差距就有了。

做一次 checkpoint 分為兩步：

• 計(jì)算當(dāng)前系統(tǒng)可以被覆蓋的 redo 日志對(duì)應(yīng)的 lsn 最大值是多少。redo 日志可以被覆蓋，意味著他對(duì)應(yīng)的臟頁(yè)被刷新到磁盤上。

• 只要我們計(jì)算出當(dāng)前系統(tǒng)中最早被修改的 oldest_modification，只要系統(tǒng)中 lsn 小于該節(jié)點(diǎn)的 oldest_modification 值，磁盤的 redo 日志都是可以被覆蓋的。

•  將 lsn 過(guò)程中的一些數(shù)據(jù)統(tǒng)計(jì)。

undo 日志

undo log 有兩個(gè)作用：提供回滾和多個(gè)行版本控制（MVCC）。

undo log 和 redo log 記錄物理日志不一樣，它是邏輯日志。

可以認(rèn)為當(dāng) delete 一條記錄時(shí)，undo log 中會(huì)記錄一條對(duì)應(yīng)的 insert 記錄，反之亦然，當(dāng) update 一條記錄時(shí)，它記錄一條對(duì)應(yīng)相反的 update 記錄。

舉個(gè)例子：

insert into a(id) values(1);(redo)

這條記錄是需要回滾的。回滾的語(yǔ)句是：

delete from a where id = 1;(undo)

試想想看：如果沒有做 insert into a(id) values(1);(redo)，那么 delete from a where id = 1;(undo) 這句話就沒有意義了。

現(xiàn)在看下正確的恢復(fù)：

• 先 insert into a(id) values(1);(redo)

• 然后 delete from a where id = 1;(undo)

• 系統(tǒng)就回到了原先的狀態(tài)，沒有這條記錄了

存儲(chǔ)方式：是存在段之中。

事務(wù)

事務(wù)中有一個(gè)隔離性特征，理論上在某個(gè)事務(wù)對(duì)某個(gè)數(shù)據(jù)進(jìn)行訪問(wèn)時(shí)，其他事務(wù)應(yīng)該排序，當(dāng)該事務(wù)提交之后，其他事務(wù)才能繼續(xù)訪問(wèn)這個(gè)數(shù)據(jù)。

但是這樣子對(duì)性能影響太大，我們既想保持事務(wù)的隔離性，又想讓服務(wù)器在出來(lái)多個(gè)事務(wù)時(shí)性能盡量高些，所以只能舍棄一部分隔離性而去性能。

事務(wù)并發(fā)執(zhí)行的問(wèn)題：

①臟寫：這個(gè)太嚴(yán)重了，任何隔離級(jí)別都不允許發(fā)生）。

• sessionA：修改了一條數(shù)據(jù)，回滾掉

• sessionB：修改了同一條數(shù)據(jù)，提交掉

對(duì)于 sessionB 來(lái)說(shuō)，明明數(shù)據(jù)更新了也提交了事務(wù)，不能說(shuō)自己啥都沒干。

②臟讀：一個(gè)事務(wù)讀到另一個(gè)未提交事務(wù)修改的數(shù)據(jù)。

• session A：查詢，得到某條數(shù)據(jù)

• session B：修改某條數(shù)據(jù)，但是最后回滾掉啦

• session A：在 sessionB 修改某條數(shù)據(jù)之后，在回滾之前，讀取了該條記錄

對(duì)于 session A 來(lái)說(shuō)，讀到了 session 回滾之前的臟數(shù)據(jù)。

③不可重復(fù)讀：前后多次讀取，同一個(gè)數(shù)據(jù)內(nèi)容不一樣。

• session A：查詢某條記錄

• session B : 修改該條記錄，并提交事務(wù)

• session A : 再次查詢?cè)摋l記錄，發(fā)現(xiàn)前后查詢不一致

④幻讀：前后多次讀取，數(shù)據(jù)總量不一致。

• session A：查詢表內(nèi)所有記錄

• session B：新增一條記錄，并查詢表內(nèi)所有記錄

• session A：再次查詢?cè)摋l記錄，發(fā)現(xiàn)前后查詢不一致

數(shù)據(jù)庫(kù)都有的四種隔離級(jí)別，MySQL 事務(wù)默認(rèn)的隔離級(jí)別是可重復(fù)讀，而且 MySQL 可以解決了幻讀的問(wèn)題：

• 未提交讀：臟讀，不可重復(fù)讀，幻讀都有可能發(fā)生

• 已提交讀：不可重復(fù)讀，幻讀可能發(fā)生

• 可重復(fù)讀：幻讀可能發(fā)生

• 可串行化：都不可能發(fā)生

但凡事沒有百分百，emmmm，其實(shí) MySQL 并沒有百分之百解決幻讀的問(wèn)題。

舉個(gè)例子：

• session A：查詢某條不存在的記錄。

• session B：新增該條不存在的記錄，并提交事務(wù)。

• session A：再次查詢?cè)摋l不存在的記錄，是查詢不出來(lái)的，但是如果我嘗試修改該條記錄，并提交，其實(shí)他是可以修改成功的。

MVCC

版本鏈：對(duì)于該記錄的每次更新，都會(huì)將值放在一條 undo 日志中，算是該記錄的一個(gè)舊版本，隨著更新次數(shù)的增多，所有版本都會(huì)被 roll_pointer 屬性連接成一個(gè)鏈表，即為版本鏈。

readview：

• 未提交讀：因?yàn)榭梢宰x到未提交事務(wù)修改的記錄，所以可以直接讀取記錄的最新版本就行

• 已提交讀：每次讀取之前都生成一個(gè) readview

• 可重復(fù)讀：只有在第一次讀取的時(shí)候才生成 readview

• 可串行化：InnoDB 涉及了加鎖的方式來(lái)訪問(wèn)記錄

參考文獻(xiàn)：

• 【原創(chuàng)】面試官：講講 MySQL 表設(shè)計(jì)要注意啥

• 【原創(chuàng)】雜談自增主鍵用完了怎么辦

• MySQL 是怎樣運(yùn)行的：從根兒上理解 MySQL

• 詳細(xì)分析 MySQL 事務(wù)日志(redo log 和 undo log)