本文是我學(xué)習(xí)極客時間專欄《MySQL實戰(zhàn)45講》的學(xué)習(xí)筆記

01 | 基礎(chǔ)架構(gòu)：一條SQL查詢語句是如何執(zhí)行的剩胁？

查詢語句執(zhí)行流程

image

02 | 日志系統(tǒng)：一條SQL更新語句是如何執(zhí)行的屯阀？

更新語句執(zhí)行流程

更新流程也要走查詢流程那一套惰拱，更新表數(shù)據(jù)時签钩，會清除該表所有緩存垃它，所以不建議開啟查詢緩存雅任。
與查詢流程不一樣的是烦感，更新流程還涉及兩個重要的日志模塊：redo log（重做日志）和 binlog（歸檔日志）逝变。

redo log（重做日志）

WAL 的全稱是 Write-Ahead Logging基茵，它的關(guān)鍵點就是先寫日志，再寫磁盤壳影。具體來說拱层，當(dāng)有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log 里面宴咧，并更新內(nèi)存根灯，這個時候更新就算完成了。InnoDB 引擎會在適當(dāng)?shù)臅r候掺栅，將這個操作記錄更新到磁盤里面烙肺。

binlog（歸檔日志）

這binlog和redo log有以下4點不同：

1.redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實現(xiàn)的氧卧，所有引擎都可以使用桃笙。

2.redo log 是物理日志，記錄的是“在某個數(shù)據(jù)頁上做了什么修改”沙绝；binlog 是邏輯日志搏明，記錄的是這個語句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”闪檬。

3.redo log 是循環(huán)寫的星著，空間固定會用完；binlog 是可以追加寫入的粗悯⌒檠“追加寫”是指 binlog 文件寫到一定大小后會切換到下一個，并不會覆蓋以前的日志。

4.redo log是記錄這個頁 “做了什么改動”;Binlog有兩種模式横缔，statement 格式的話是記sql語句铺遂， row格式會記錄行的內(nèi)容，記兩條剪廉，更新前和更新后都有娃循。

更新語句記錄日志流程圖
mysql> update T set c=c+1 where ID=2;

image

關(guān)于 redo log 和 binlog 的持久化設(shè)置

innodb_flush_log_at_trx_commit 這個參數(shù)設(shè)置成 1 的時候，表示每次事務(wù)的 redo log 都直接持久化到磁盤斗蒋。這個參數(shù)我建議你設(shè)置成 1捌斧，這樣可以保證 MySQL 異常重啟之后數(shù)據(jù)不丟失。

sync_binlog 這個參數(shù)設(shè)置成 1 的時候泉沾，表示每次事務(wù)的 binlog 都持久化到磁盤捞蚂。這個參數(shù)我也建議你設(shè)置成 1，這樣可以保證 MySQL 異常重啟之后 binlog 不丟失跷究。

03 | 事務(wù)隔離：為什么你改了我還看不見姓迅？

事務(wù)的隔離級別

事務(wù)就是要保證一組數(shù)據(jù)庫操作，要么全部成功俊马，要么全部失敗丁存。在 MySQL 中，事務(wù)支持是在引擎層實現(xiàn)的柴我，MySQL 原生的 MyISAM 引擎不支持事務(wù)解寝，這也是 MyISAM 被 InnoDB 取代的重要原因之一。

讀未提交是指艘儒，一個事務(wù)還沒提交時聋伦，它做的變更就能被別的事務(wù)看到。

讀已提交是指界睁，一個事務(wù)提交之后觉增，它做的變更才會被其他事務(wù)看到。

可重復(fù)讀是指翻斟，一個事務(wù)執(zhí)行過程中看到的數(shù)據(jù)逾礁，總是跟這個事務(wù)在啟動時看到的數(shù)據(jù)是一致的。當(dāng)然在可重復(fù)讀隔離級別下访惜，未提交變更對其他事務(wù)也是不可見的嘹履。

串行化，顧名思義是對于同一行記錄疾牲，“寫”會加“寫鎖”，“讀”會加“讀鎖”衙解。當(dāng)出現(xiàn)讀寫鎖沖突的時候阳柔，后訪問的事務(wù)必須等前一個事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行蚓峦。

例子：

image

讀未提交：V1=2舌剂，V2=2济锄，V3=2

讀已提交：V1=1，V2=2霍转，V3=2

可重復(fù)讀：V1=1荐绝，V2=1，V3=2

串行化：V1=1避消，V2=1低滩，V3=2

在實現(xiàn)上，數(shù)據(jù)庫里面會創(chuàng)建一個視圖岩喷，訪問的時候以視圖的邏輯結(jié)果為準(zhǔn)恕沫。

“可重復(fù)讀”隔離級別下，這個視圖是在事務(wù)啟動時創(chuàng)建的纱意，整個事務(wù)存在期間都用這個視圖婶溯；

“讀提交”隔離級別下，這個視圖是在每個 SQL 語句開始執(zhí)行的時候創(chuàng)建的偷霉；

“讀未提交”隔離級別下迄委，直接返回記錄上的最新值，沒有視圖概念类少；

“串行化”隔離級別下叙身，直接用加鎖的方式來避免并行訪問。

04 05 | 深入淺出索引

索引

索引種類

主鍵索引的葉子節(jié)點存的是整行數(shù)據(jù)瞒滴。在 InnoDB 里曲梗，主鍵索引也被稱為聚簇索引（clustered index）。

非主鍵索引的葉子節(jié)點內(nèi)容是主鍵的值妓忍。在 InnoDB 里虏两，非主鍵索引也被稱為二級索引（secondary index）。

所以說想獲取到整行數(shù)據(jù)世剖，根據(jù)主鍵索引查詢可直接得到整行數(shù)據(jù)定罢，而使用非主鍵索引需先檢索到主鍵，然后根據(jù)主鍵進行回表得到整行數(shù)據(jù)旁瘫。也就是說祖凫，基于非主鍵索引的查詢需要多掃描一棵索引樹。

覆蓋索引

如果執(zhí)行的語句是 select ID from T where k between 3 and 5酬凳，（其中k是非主鍵索引）這時只需要查 ID 的值惠况，而 ID 的值已經(jīng)在 k 索引樹上了，因此可以直接提供查詢結(jié)果宁仔，不需要回表稠屠。也就是說，在這個查詢里面，索引 k 已經(jīng)“覆蓋了”我們的查詢需求权埠，我們稱為覆蓋索引榨了。

最左匹配原則（最左前綴原則）

1.簡單說下什么是最左匹配原則
顧名思義：最左優(yōu)先，以最左邊的為起點任何連續(xù)的索引都能匹配上攘蔽。同時遇到范圍查詢(>龙屉、<、between满俗、like)就會停止匹配转捕。
例如：b = 2 如果建立(a,b)順序的索引，是匹配不到(a,b)索引的漫雷；但是如果查詢條件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以瓜富，因為優(yōu)化器會自動調(diào)整a,b的順序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引降盹，d是用不到索引的与柑，因為c字段是一個范圍查詢，它之后的字段會停止匹配蓄坏。

2.最左匹配原則的原理
最左匹配原則都是針對聯(lián)合索引來說的价捧，所以我們有必要了解一下聯(lián)合索引的原理。了解了聯(lián)合索引涡戳，那么為什么會有最左匹配原則這種說法也就理解了结蟋。

我們都知道索引的底層是一顆B+樹，那么聯(lián)合索引當(dāng)然還是一顆B+樹渔彰，只不過聯(lián)合索引的健值數(shù)量不是一個嵌屎，而是多個。構(gòu)建一顆B+樹只能根據(jù)一個值來構(gòu)建恍涂，因此數(shù)據(jù)庫依據(jù)聯(lián)合索引最左的字段來構(gòu)建B+樹宝惰。
例子：假如創(chuàng)建一個（a,b)的聯(lián)合索引，那么它的索引樹是這樣的

image

可以看到a的值是有順序的再沧，1尼夺，1，2炒瘸，2淤堵，3，3顷扩，而b的值是沒有順序的1拐邪，2，1隘截，4扎阶，1事富，2。所以b = 2這種查詢條件沒有辦法利用索引乘陪，因為聯(lián)合索引首先是按a排序的，b是無序的雕擂。

同時我們還可以發(fā)現(xiàn)在a值相等的情況下啡邑，b值又是按順序排列的，但是這種順序是相對的井赌。所以最左匹配原則遇上范圍查詢就會停止谤逼，剩下的字段都無法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引仇穗，因為在a值確定的情況下b是相對有序的流部，而a>1and b=2，a字段可以匹配上索引纹坐，但b值不可以枝冀，因為a的值是一個范圍，在這個范圍中b是無序的耘子。

索引下推

現(xiàn)在有 (name,age) 這個聯(lián)合索引果漾，執(zhí)行以下語句
select * from tuser where name like '張%' and age=10 and ismale=1;
雖然在這個語句只能使用到name的索引，但是mysql 5.6 引入了索引下推優(yōu)化谷誓， 可以在索引遍歷過程中绒障，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄捍歪，減少回表次數(shù)户辱。

06 | 全局鎖和表鎖 ：給表加個字段怎么有這么多阻礙？

全局鎖

對整個數(shù)據(jù)庫實例加鎖糙臼。

MySQL提供加全局讀鎖的方法：Flush tables with read lock(FTWRL)

這個命令可以使整個庫處于只讀狀態(tài)庐镐。使用該命令之后，數(shù)據(jù)更新語句弓摘、數(shù)據(jù)定義語句和更新類事務(wù)的提交語句等操作都會被阻塞焚鹊。

使用場景：全庫邏輯備份。

風(fēng)險：

1.如果在主庫備份韧献，在備份期間不能更新末患，業(yè)務(wù)停擺

2.如果在從庫備份，備份期間不能執(zhí)行主庫同步的binlog锤窑，導(dǎo)致主從延遲

官方自帶的邏輯備份工具mysqldump璧针，當(dāng)mysqldump使用參數(shù)--single-transaction的時候，會啟動一個事務(wù)渊啰，確保拿到一致性視圖探橱。而由于MVCC的支持申屹，這個過程中數(shù)據(jù)是可以正常更新的。

一致性讀是好隧膏，但是前提是引擎要支持這個隔離級別哗讥。

如果要全庫只讀，為什么不使用set global readonly=true的方式胞枕？

1.在有些系統(tǒng)中杆煞，readonly的值會被用來做其他邏輯，比如判斷主備庫腐泻。所以修改global變量的方式影響太大决乎。

2.在異常處理機制上有差異。如果執(zhí)行FTWRL命令之后由于客戶端發(fā)生異常斷開派桩，那么MySQL會自動釋放這個全局鎖构诚，整個庫回到可以正常更新的狀態(tài)。而將整個庫設(shè)置為readonly之后铆惑，如果客戶端發(fā)生異常范嘱，則數(shù)據(jù)庫就會一直保持readonly狀態(tài)，這樣會導(dǎo)致整個庫長時間處于不可寫狀態(tài)员魏，風(fēng)險較高彤侍。

表級鎖

MySQL里面表級鎖有兩種，一種是表鎖逆趋，一種是元數(shù)據(jù)所(meta data lock,MDL)

表鎖的語法是:lock tables ... read/write

可以用unlock tables主動釋放鎖盏阶，也可以在客戶端斷開的時候自動釋放。lock tables語法除了會限制別的線程的讀寫外闻书，也限定了本線程接下來的操作對象名斟。

對于InnoDB這種支持行鎖的引擎，一般不使用lock tables命令來控制并發(fā)魄眉，畢竟鎖住整個表的影響面還是太大砰盐。

MDL：不需要顯式使用，在訪問一個表的時候會被自動加上。

MDL的作用：保證讀寫的正確性。

在對一個表做增刪改查操作的時候种樱，加MDL讀鎖沦疾；當(dāng)要對表做結(jié)構(gòu)變更操作的時候垮耳，加MDL寫鎖。
讀鎖之間不互斥。讀寫鎖之間，寫鎖之間是互斥的列疗，用來保證變更表結(jié)構(gòu)操作的安全性。

MDL 會直到事務(wù)提交才會釋放浪蹂，在做表結(jié)構(gòu)變更的時候抵栈，一定要小心不要導(dǎo)致鎖住線上查詢和更新告材。

07 | 行鎖功過：怎么減少行鎖對性能的影響？

行鎖

兩階段鎖

在 InnoDB 事務(wù)中古劲，行鎖是在需要的時候才加上的斥赋，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時才釋放产艾。這個就是兩階段鎖協(xié)議灿渴。

所以如果你的事務(wù)中需要鎖多個行，要把最可能造成鎖沖突胰舆、最可能影響并發(fā)度的鎖盡量往后放，減少持有鎖的時間蹬挤。

死鎖和死鎖檢測

當(dāng)出現(xiàn)死鎖以后缚窿，有兩種策略：

一種策略是，直接進入等待焰扳，直到超時倦零。這個超時時間可以通過參數(shù) innodb_lock_wait_timeout 來設(shè)置。

另一種策略是吨悍，發(fā)起死鎖檢測扫茅，發(fā)現(xiàn)死鎖后，主動回滾死鎖鏈條中的某一個事務(wù)育瓜，讓其他事務(wù)得以繼續(xù)執(zhí)行葫隙。將參數(shù) innodb_deadlock_detect 設(shè)置為 on，表示開啟這個邏輯躏仇。

08 | 事務(wù)到底是隔離的還是不隔離的恋脚？

一致性視圖

先來看個題目,可重復(fù)讀隔離級別下,k的初始值為1,執(zhí)行如下語句后,事務(wù)A和事務(wù)B查詢到的k值分別是多少?

注:

1. begin/start transaction 命令并不是一個事務(wù)的起點，在執(zhí)行到它們之后的第一個操作 InnoDB 表的語句焰手，事務(wù)才真正啟動糟描。如果你想要馬上啟動一個事務(wù)，可以使用 start transaction with consistent snapshot 這個命令书妻。

2. InnoDB的autocommit=1的時候(默認為1),事務(wù)C未顯示的使用begin/commit船响，表示這個 update 語句本身就是一個事務(wù)，語句完成的時候會自動提交.

   
   
   image

這組事務(wù)執(zhí)行完后,事務(wù)A查詢到的k=1,事務(wù)B查詢到的k=3.是不是感到了疑惑躲履？

在MySQL中有兩種視圖的概念:

一種是view,是虛擬表,現(xiàn)在一般都不會去使用view见间。

另一個是 InnoDB 在實現(xiàn) MVCC 時用到的一致性讀視圖，即 consistent read view工猜，用于支持 RC（Read Committed缤剧，讀提交）和 RR（Repeatable Read，可重復(fù)讀）隔離級別的實現(xiàn)域慷。

判斷規(guī)則：

1.版本未提交荒辕，不可見汗销；

2.版本已提交，但是是在視圖創(chuàng)建后提交的抵窒，不可見弛针；

3.版本已提交，而且是在視圖創(chuàng)建前提交的李皇，可見

4.更新數(shù)據(jù)都是先讀后寫的削茁，而這個讀，只能讀當(dāng)前的值掉房，稱為“當(dāng)前讀”（current read）茧跋；

5.自己的更新總是可見的。

對于事務(wù)B來說卓囚，創(chuàng)建視圖時瘾杭，k=1，但是執(zhí)行更新語句時哪亿，屬于規(guī)則4當(dāng)前讀k=2粥烁，k值更新后為3，查詢時屬于規(guī)則5蝇棉，所以查詢語句的k=3讨阻；

對于事務(wù)A來說，事務(wù)C的修改屬于情況2篡殷，不可見钝吮，事務(wù)B的修改屬于情況1，不可見板辽，A查詢到的k值為創(chuàng)建視圖時的k值1搀绣。

09 | 普通索引和唯一索引，應(yīng)該怎么選擇戳气？

普通索引和唯一索引應(yīng)該怎么選链患？

場景

假設(shè)你在維護一個市民系統(tǒng)，每個人都有一個唯一的身份證號瓶您，而且業(yè)務(wù)代碼已經(jīng)保證了不會寫入兩個重復(fù)的身份證號麻捻。如果市民系統(tǒng)需要按照身份證號查姓名，所以呀袱，你一定會考慮在 id_card 字段上建索引贸毕。由于身份證號字段比較大，且插入時可能無序夜赵，我不建議你把身份證號當(dāng)做主鍵明棍，那么現(xiàn)在你有兩個選擇，要么給 id_card 字段創(chuàng)建唯一索引寇僧，要么創(chuàng)建一個普通索引摊腋。如果業(yè)務(wù)代碼已經(jīng)保證了不會寫入重復(fù)的身份證號沸版，那么這兩個選擇邏輯上都是正確的。現(xiàn)在我要問你的是兴蒸，從性能的角度考慮视粮，你選擇唯一索引還是普通索引呢？選擇的依據(jù)是什么呢橙凳？

查詢過程

1.對于普通索引來說蕾殴，查找到滿足條件的第一個記錄后，會需要查找下一個記錄岛啸，判斷條件不滿足然后停止钓觉。

2.對于唯一索引來說，由于索引定義了唯一性坚踩，查找到第一個滿足條件的記錄后荡灾，就會停止繼續(xù)檢索。

那么堕虹，這個不同帶來的性能差距會有多少呢？答案是芬首，微乎其微赴捞。InnoDB 的數(shù)據(jù)是按數(shù)據(jù)頁為單位來讀寫的。也就是說郁稍，當(dāng)需要讀一條記錄的時候赦政，并不是將這個記錄本身從磁盤讀出來，而是以頁為單位耀怜，將其整體讀入內(nèi)存恢着。在 InnoDB 中，每個數(shù)據(jù)頁的大小默認是 16KB财破。對于整型字段掰派，一個數(shù)據(jù)頁可以放近千個key，所以對于普通索引來說左痢，需要查找下一個記錄大概率上是在內(nèi)存中進行的靡羡，我們計算平均性能差異時，可以認為這個操作成本可以忽略不計俊性。

更新過程

為了說明普通索引和唯一索引對更新語句性能的影響這個問題略步，需要先了解一下 change buffer。

當(dāng)需要更新一個數(shù)據(jù)頁時定页，如果數(shù)據(jù)頁在內(nèi)存中就直接更新趟薄，而如果這個數(shù)據(jù)頁還沒有在內(nèi)存中的話，在不影響數(shù)據(jù)一致性的前提下典徊，InnoDB 會將這些更新操作緩存在 change buffer 中杭煎，這樣就不需要從磁盤中讀入這個數(shù)據(jù)頁了恩够。在下次查詢需要訪問這個數(shù)據(jù)頁的時候，將數(shù)據(jù)頁讀入內(nèi)存岔帽，然后執(zhí)行 change buffer 中與這個頁有關(guān)的操作玫鸟。通過這種方式就能保證這個數(shù)據(jù)邏輯的正確性。

需要說明的是犀勒，雖然名字叫作 change buffer屎飘，實際上它是可以持久化的數(shù)據(jù)。也就是說贾费，change buffer 在內(nèi)存中有拷貝钦购，也會被寫入到磁盤上。將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁褂萧，得到最新結(jié)果的過程稱為 merge押桃。除了訪問這個數(shù)據(jù)頁會觸發(fā) merge 外，系統(tǒng)有后臺線程會定期 merge导犹。在數(shù)據(jù)庫正常關(guān)閉（shutdown）的過程中唱凯，也會執(zhí)行 merge 操作。顯然谎痢，如果能夠?qū)⒏虏僮飨扔涗浽?change buffer磕昼，減少讀磁盤，語句的執(zhí)行速度會得到明顯的提升节猿。而且票从，數(shù)據(jù)讀入內(nèi)存是需要占用 buffer pool 的，所以這種方式還能夠避免占用內(nèi)存滨嘱，提高內(nèi)存利用率峰鄙。

那么，什么條件下可以使用 change buffer 呢太雨？對于唯一索引來說吟榴，所有的更新操作都要先判斷這個操作是否違反唯一性約束。所以必須要將數(shù)據(jù)頁讀入內(nèi)存才能判斷囊扳。如果都已經(jīng)讀入到內(nèi)存了煤墙，那直接更新內(nèi)存會更快，就沒必要使用 change buffer 了宪拥。因此仿野，唯一索引的更新就不能使用 change buffer，實際上也只有普通索引可以使用她君。

索引選擇和實踐

普通索引和唯一索引應(yīng)該怎么選擇脚作？。其實，這兩類索引在查詢能力上是沒差別的球涛，主要考慮的是對更新性能的影響劣针。所以，我建議你盡量選擇普通索引亿扁。如果所有的更新后面捺典，都馬上伴隨著對這個記錄的查詢，那么你應(yīng)該關(guān)閉 change buffer从祝。

而在其他情況下襟己，change buffer 都能提升更新性能。在實際使用中牍陌，你會發(fā)現(xiàn)擎浴，普通索引和 change buffer 的配合使用，對于數(shù)據(jù)量大的表的更新優(yōu)化還是很明顯的毒涧。特別地贮预，在使用機械硬盤時，change buffer 這個機制的收效是非常顯著的契讲。所以仿吞，當(dāng)你有一個類似“歷史數(shù)據(jù)”的庫，并且出于成本考慮用的是機械硬盤時捡偏，那你應(yīng)該特別關(guān)注這些表里的索引唤冈，盡量使用普通索引，然后把 change buffer 盡量開大霹琼，以確保這個“歷史數(shù)據(jù)”表的數(shù)據(jù)寫入速度务傲。

20 | 幻讀是什么凉当，幻讀有什么問題枣申？

幻讀是什么

幻讀指的是一個事務(wù)在前后兩次查詢同一個范圍的時候，后一次查詢看到了前一次查詢沒有看到的行看杭。

這里忠藤，我需要對“幻讀”做一個說明：

1.在可重復(fù)讀隔離級別下，普通的查詢是快照讀楼雹，是不會看到別的事務(wù)插入的數(shù)據(jù)的模孩。因此，幻讀在“當(dāng)前讀”下才會出現(xiàn)贮缅。

2.幻讀僅專指“新插入的行”榨咐。

因為新加的記錄不受之前的行鎖限制，所以即使把所有的記錄都加上鎖谴供，還是阻止不了新插入的記錄块茁，這也是為什么“幻讀”會被單獨拿出來解決的原因。

如何解決幻讀

引入間隙鎖，間隙鎖和行鎖一起被稱為next-key lock数焊，間隙鎖在可重復(fù)讀隔離級別下才有效

21 | 為什么我只改一行的語句永淌，鎖這么多？

加鎖規(guī)則佩耳，包含了兩個“原則”遂蛀、兩個“優(yōu)化”和一個“bug”

原則 1：加鎖的基本單位是 next-key lock。希望你還記得干厚，next-key lock 是前開后閉區(qū)間李滴，如(5,10]。

原則 2：查找過程中訪問到的對象會加鎖萍诱。

優(yōu)化 1：索引上的等值查詢悬嗓，給唯一索引加鎖的時候，next-key lock 退化為行鎖裕坊。

優(yōu)化 2：索引上的等值查詢包竹，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖籍凝。

一個 bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止周瞎。