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

[[312968]] 

圖片來自 Pexels

開局一張圖

 

這張圖是重點!!!咱要先對 MySQL 有一個宏觀的了解，知道他的執(zhí)行流程。

一條 SQL 語句過來的流程是什么樣的?

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

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

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

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

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

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

查詢緩存

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

 

概念

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

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

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

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

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

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

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

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

那我們就可以選擇 300ml 的瓶子，如果過來的是成年運動員，可能 500ml 不夠，那他們一瓶喝完了，又開一瓶，直接不渴為止。那么那樣開瓶子也要時間，我們就可以選擇 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 的查詢緩存實質(zhì)上是緩存 SQL 的 Hash 值和該 SQL 的查詢結(jié)果，如果運行相同的 SQL，服務(wù)器直接從緩存中去掉結(jié)果，而不再去解析，優(yōu)化，尋找最低成本的執(zhí)行計劃等一系列操作，大大提升了查詢速度。

但是萬事有利也有弊：

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

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

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

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

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

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

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

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

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

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

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

 

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

索引

例子：創(chuàng)建一個名為 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', '男'); 

聚簇索引(主鍵索引)

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

 

他包含兩個特點：

• 使用記錄主鍵值的大小來進(jìn)行記錄和頁的排序。頁內(nèi)的記錄是按照主鍵的大小順序排成一個單項鏈表。各個存放用戶記錄的頁也是根據(jù)頁中用戶記錄的主鍵大小順序排成一個雙向鏈表。
• 葉子節(jié)點存儲的是完整的用戶記錄。

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

非聚簇索引(二級索引)

上面的聚簇索引只能在搜索條件是主鍵時才能發(fā)揮作用，因為聚簇索引可以根據(jù)主鍵進(jìn)行排序的。

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

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

 

他與聚簇索引的不同：

• 葉子節(jié)點內(nèi)部使用 name 字段排序，葉子節(jié)點之間也是使用 name 字段排序。
• 葉子節(jié)點不再是完整的數(shù)據(jù)記錄，而是 name 和主鍵值。

為什么不再是完整信息?MySQL 只讓聚簇索引的葉子節(jié)點存放完整的記錄信息，因為如果有好幾個非聚簇索引，他們的葉子節(jié)點也存放完整的記錄績效，那就不浪費空間啦。

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

• 根據(jù)查詢條件，采用 name 的非聚簇索引，先定位到該非聚簇索引某些記錄行。
• 根據(jù)記錄行找到相應(yīng)的 id，再根據(jù) id 到聚簇索引中找到相關(guān)記錄。這個過程叫做回表。

聯(lián)合索引

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

一些原則

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

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

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

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

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

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

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

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

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

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

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

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

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

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

萬年面試題(為什么索引用 B+ 樹)

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

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

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

但是 B 樹因為其分支結(jié)點同樣存儲著數(shù)據(jù)，我們要找到具體的數(shù)據(jù)，需要進(jìn)行一次中序遍歷按序來掃，所以 B+ 樹更加適合在區(qū)間查詢的情況，所以通常 B+ 樹用于數(shù)據(jù)庫索引。

優(yōu)化器

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

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

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

什么是成本?

• I/O 成本。數(shù)據(jù)存儲在硬盤上，我們想要進(jìn)行某個操作需要將其加載到內(nèi)存中，這個過程的時間被稱為 I/O 成本。默認(rèn)是 1。
• CPU 成本。在內(nèi)存對結(jié)果集進(jìn)行排序的時間被稱為 CPU 成本。默認(rèn)是 0.2。

單表查詢的成本

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

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

 

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

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

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

②計算全表掃描代價

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

 

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

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

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

• I/O 成本：97X1=97
• CPU 成本：6141X0.2=1228
• 總成本：97+1228=1325

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

因為要查詢出滿足條件的所有字段信息，所以要考慮回表成本：

• I/O 成本=1+1X1=2(范圍區(qū)間的數(shù)量+預(yù)計二級記錄索引條數(shù))
• CPU 成本=1X0.2+1X0.2=0.4(讀取二級索引的成本+回表聚簇索引的成本)
• 總成本= I/O 成本+CPU 成本=2.4

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

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

多表查詢的成本

對于兩表連接查詢來說，他的查詢成本由下面兩個部分構(gòu)成：

• 單次查詢驅(qū)動表的成本
• 多次查詢被驅(qū)動表的成本(具體查詢多次取決于對驅(qū)動表查詢的結(jié)果集有多少個記錄)

index dive

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

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

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

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

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

Explain

[[312969]] 

產(chǎn)品來索命：

• 產(chǎn)品：為什么這個頁面出來這么慢?
• 開發(fā)：因為你查的數(shù)據(jù)多唄，他就是這么慢
• 產(chǎn)品：我不管，我要這個頁面快點，你這樣，客戶怎么用啊
• 開發(fā)：......你行你來

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

 

id：一般來說一個 select 一個唯一 id，如果是子查詢，就有兩個 select，id 是不一樣的，但是凡事有例外，有些子查詢的，他們 id 是一樣的。

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

select_type：

• simple：不包括 union 和子查詢的查詢都算 simple 類型。
• primary：包括 union，union all，其中最左邊的查詢即為 primary。
• union：包括 union，union all，除了最左邊的查詢，其他的查詢類型都為 union。

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

type 訪問方法：

• ref：普通二級索引與常量進(jìn)行等值匹配
• ref_or_null：普通二級索引與常量進(jìn)行等值匹配，該索引可能是 null
• const：主鍵或唯一二級索引列與常量進(jìn)行等值匹配
• range：范圍區(qū)間的查詢
• all：全表掃描

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

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

rows：

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

filtered：

• 如果全表掃描，那么 filtered 就代表滿足搜索條件的記錄的滿分比
• 如果是索引，那么 filtered 就代表除去索引對應(yīng)的搜索，其他搜索條件的百分比

redo 日志(物理日志)

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

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

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

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

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

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

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

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

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

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

下圖為線上的大?。?/p>

 

redo 日志刷盤時機(jī)

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

那什么時候刷新到硬盤呢?

• log buffer 空間不足。上面有指定緩沖區(qū)的內(nèi)存大小，MySQL 認(rèn)為日志量已經(jīng)占了 總?cè)萘康囊话胱笥?，就需要將這些日志刷新到磁盤上。
• 事務(wù)提交時。我們使用 redo 日志的目的就是將他未刷新到磁盤的記錄保存起來，防止丟失，如果數(shù)據(jù)提交了，我們是可以不把數(shù)據(jù)提交到磁盤的，但為了保證持久性，必須 把修改這些頁面的 redo 日志刷新到磁盤。
• 后臺線程不同的刷新后臺有一個線程，大概每秒都會將 log buffer 里面的 redo 日志刷新到硬盤上。
• checkpoint 下下小節(jié)講。

redo 日志文件組

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

 

 

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

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

checkpoint

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

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

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

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

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

做一次 checkpoint 分為兩步：

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

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

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

undo 日志

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

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

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

舉個例子：

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

這條記錄是需要回滾的?；貪L的語句是：

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)，沒有這條記錄了

存儲方式：是存在段之中。

事務(wù)

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

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

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

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

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

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

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

• session A：查詢，得到某條數(shù)據(jù)
• session B：修改某條數(shù)據(jù)，但是最后回滾掉啦
• session A：在 sessionB 修改某條數(shù)據(jù)之后，在回滾之前，讀取了該條記錄

對于 session A 來說，讀到了 session 回滾之前的臟數(shù)據(jù)。

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

• session A：查詢某條記錄
• session B : 修改該條記錄，并提交事務(wù)
• session A : 再次查詢該條記錄，發(fā)現(xiàn)前后查詢不一致

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

• session A：查詢表內(nèi)所有記錄
• session B：新增一條記錄，并查詢表內(nèi)所有記錄
• session A：再次查詢該條記錄，發(fā)現(xiàn)前后查詢不一致

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

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

但凡事沒有百分百，emmmm，其實 MySQL 并沒有百分之百解決幻讀的問題。 

[[312970]] 

舉個例子： 

• session A：查詢某條不存在的記錄。
• session B：新增該條不存在的記錄，并提交事務(wù)。
• session A：再次查詢該條不存在的記錄，是查詢不出來的，但是如果我嘗試修改該條記錄，并提交，其實他是可以修改成功的。

MVCC

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

readview：

• 未提交讀：因為可以讀到未提交事務(wù)修改的記錄，所以可以直接讀取記錄的最新版本就行
• 已提交讀：每次讀取之前都生成一個 readview
• 可重復(fù)讀：只有在第一次讀取的時候才生成 readview
• 可串行化：InnoDB 涉及了加鎖的方式來訪問記錄

參考文獻(xiàn)：

• 【原創(chuàng)】面試官：講講 MySQL 表設(shè)計要注意啥
• 【原創(chuàng)】雜談自增主鍵用完了怎么辦
• MySQL 是怎樣運行的：從根兒上理解 MySQL
• 詳細(xì)分析 MySQL 事務(wù)日志(redo log 和 undo log)