基礎(chǔ)架構(gòu)：一條 SQL 查詢語句是如何執(zhí)行的？
MySQL 的基本架構(gòu)示意圖：
MySQL 可以分為 Server 層和存儲(chǔ)引擎層兩部分。
Server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過程、觸發(fā)器、視圖等。
存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎。現(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認(rèn)存儲(chǔ)引擎。
不同存儲(chǔ)引擎的表數(shù)據(jù)存取方式不同，支持的功能也不同。不同的存儲(chǔ)引擎共用一個(gè)Server 層，也就是從連接器到執(zhí)行器的部分。
連接器
連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。
查詢緩存
MySQL 拿到一個(gè)查詢請(qǐng)求后，會(huì)先到查詢緩存看看，之前是不是執(zhí)行過這條語句。之前執(zhí)行過的語句及其結(jié)果可能會(huì)以 key-value 對(duì)的形式，被直接緩存在內(nèi)存中。key 是查詢的語句，value 是查詢的結(jié)果。如果你的查詢能夠直接在這個(gè)緩存中找到 key，那么這個(gè) value 就會(huì)被直接返回給客戶端。
但是大多數(shù)情況下我會(huì)建議你不要使用查詢緩存，為什么呢？因?yàn)椴樵兙彺嫱状笥诶?/p>
查詢緩存的失效非常頻繁，只要有對(duì)一個(gè)表的更新，這個(gè)表上所有的查詢緩存都會(huì)被清空。因此很可能你費(fèi)勁地把結(jié)果存起來，還沒使用呢，就被一個(gè)更新全清空了。對(duì)于更新壓力大的數(shù)據(jù)庫來說，查詢緩存的命中率會(huì)非常低。除非你的業(yè)務(wù)就是有一張靜態(tài)表，很長(zhǎng)時(shí)間才會(huì)更新一次。比如，一個(gè)系統(tǒng)配置表，那這張表上的查詢才適合使用查詢緩存。
MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了，也就是說 8.0 開始徹底沒有這個(gè)功能了。
分析器
如果沒有命中查詢緩存，就要開始真正執(zhí)行語句了。
分析器先會(huì)做“詞法分析”。做完了這些識(shí)別以后，就要做“語法分析”。
優(yōu)化器
優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引；或者在一個(gè)語句有多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序。
優(yōu)化器階段完成后，這個(gè)語句的執(zhí)行方案就確定下來了，然后進(jìn)入執(zhí)行器階段。
執(zhí)行器
打開表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的接口。
你會(huì)在數(shù)據(jù)庫的慢查詢?nèi)罩局锌吹揭粋€(gè) rows_examined 的字段，表示這個(gè)語句執(zhí)行過程中掃描了多少行。這個(gè)值就是在執(zhí)行器每次調(diào)用引擎獲取數(shù)據(jù)行的時(shí)候累加的。
在有些場(chǎng)景下，執(zhí)行器調(diào)用一次，在引擎內(nèi)部則掃描了多行，因此引擎掃描行數(shù)跟 rows_examined 并不是完全相同的。
我給你留一個(gè)問題吧，如果表 T 中沒有字段 k，而你執(zhí)行了這個(gè)語句 select * from T where k=1, 那肯定是會(huì)報(bào)“不存在這個(gè)列”的錯(cuò)誤： “Unknown column ‘k’ in ‘where clause’”。你覺得這個(gè)錯(cuò)誤是在我們上面提到的哪個(gè)階段報(bào)出來的呢？
答案是分析器。
日志系統(tǒng)：一條 SQL 更新語句是如何執(zhí)行的？
與查詢流程不一樣的是，更新流程還涉及兩個(gè)重要的日志模塊，它們正是我們今天要討論的主角：redo log（重做日志）和 binlog（歸檔日志）。
重要的日志模塊：redo log
MySQL 里經(jīng)常說到的 WAL 技術(shù)，WAL 的全稱是 Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤。
具體來說，當(dāng)有一條記錄需要更新的時(shí)候，InnoDB 引擎就會(huì)先把記錄寫到 redo log（粉板）里面，并更新內(nèi)存，這個(gè)時(shí)候更新就算完成了。同時(shí)，InnoDB 引擎會(huì)在適當(dāng)?shù)臅r(shí)候，將這個(gè)操作記錄更新到磁盤里面，而這個(gè)更新往往是在系統(tǒng)比較空閑的時(shí)候做。
與此類似，InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個(gè)文件，每個(gè)文件的大小是 1GB，那么這塊“粉板”總共就可以記錄 4GB 的操作。從頭開始寫，寫到末尾就又回到開頭循環(huán)寫。
write pos 是當(dāng)前記錄的位置，一邊寫一邊后移，寫到第 3 號(hào)文件末尾后就回到 0 號(hào)文件開頭。checkpoint 是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件。
write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來記錄新的操作。如果 write pos 追上 checkpoint，表示“粉板”滿了，這時(shí)候不能再執(zhí)行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進(jìn)一下。
有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為crash-safe。
重要的日志模塊：binlog
redo log 是 InnoDB 引擎特有的日志，而 Server 層也有自己的日志，稱為 binlog（歸檔日志）。
為什么會(huì)有兩份日志呢？
因?yàn)樽铋_始 MySQL 里并沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒有 crash-safe 的能力，binlog 日志只能用于歸檔。而 InnoDB 是另一個(gè)公司以插件形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系統(tǒng)——也就是 redo log 來實(shí)現(xiàn) crash-safe 能力。
這兩種日志有以下三點(diǎn)不同。
1 redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實(shí)現(xiàn)的，所有引擎都可以使用。
2 redo log 是物理日志，記錄的是“在某個(gè)數(shù)據(jù)頁上做了什么修改”；binlog 是邏輯日志，記錄的是這個(gè)語句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。
3 redo log 是循環(huán)寫的，空間固定會(huì)用完；binlog 是可以追加寫入的?！白芳訉憽笔侵?binlog 文件寫到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。
有了對(duì)這兩個(gè)日志的概念性理解，我們?cè)賮砜磮?zhí)行器和 InnoDB 引擎在執(zhí)行這個(gè)簡(jiǎn)單的 update 語句時(shí)的內(nèi)部流程。
1 執(zhí)行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回。
2 執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個(gè)值加上 1，比如原來是 N，現(xiàn)在就是 N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。
3 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到 redo log 里面，此時(shí) redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù)。
4 執(zhí)行器生成這個(gè)操作的 binlog，并把 binlog 寫入磁盤。
5 執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成。
update 語句執(zhí)行流程：
將 redo log 的寫入拆成了兩個(gè)步驟：prepare 和 commit，這就是"兩階段提交"。
兩階段提交
前面我們說過了，binlog 會(huì)記錄所有的邏輯操作，并且是采用“追加寫”的形式。
由于 redo log 和 binlog 是兩個(gè)獨(dú)立的邏輯如果不使用“兩階段提交”，那么數(shù)據(jù)庫的狀態(tài)就有可能和用它的日志恢復(fù)出來的庫的狀態(tài)不一致。
小結(jié)
redo log 用于保證 crash-safe 能力。innodbflushlogattrx_commit 這個(gè)參數(shù)設(shè)置成 1 的時(shí)候，表示每次事務(wù)的 redo log 都直接持久化到磁盤。這個(gè)參數(shù)我建議你設(shè)置成 1，這樣可以保證 MySQL 異常重啟之后數(shù)據(jù)不丟失。
sync_binlog 這個(gè)參數(shù)設(shè)置成 1 的時(shí)候，表示每次事務(wù)的 binlog 都持久化到磁盤。這個(gè)參數(shù)我也建議你設(shè)置成 1，這樣可以保證 MySQL 異常重啟之后 binlog 不丟失。
事務(wù)隔離：為什么你改了我還看不見？
簡(jiǎn)單來說，事務(wù)就是要保證一組數(shù)據(jù)庫操作，要么全部成功，要么全部失敗。在 MySQL 中，事務(wù)支持是在引擎層實(shí)現(xiàn)的。你現(xiàn)在知道，MySQL 是一個(gè)支持多引擎的系統(tǒng)，但并不是所有的引擎都支持事務(wù)。比如 MySQL 原生的 MyISAM 引擎就不支持事務(wù)，這也是 MyISAM 被 InnoDB 取代的重要原因之一。
隔離性與隔離級(jí)別
在談隔離級(jí)別之前，你首先要知道，你隔離得越嚴(yán)實(shí)，效率就會(huì)越低。SQL 標(biāo)準(zhǔn)的事務(wù)隔離級(jí)別包括：讀未提交（read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（serializable ）。SQL 標(biāo)準(zhǔn)的事務(wù)隔離級(jí)別包括：
讀未提交是指，一個(gè)事務(wù)還沒提交時(shí)，它做的變更就能被別的事務(wù)看到。
讀提交是指，一個(gè)事務(wù)提交之后，它做的變更才會(huì)被其他事務(wù)看到。
可重復(fù)讀是指，一個(gè)事務(wù)執(zhí)行過程中看到的數(shù)據(jù)，總是跟這個(gè)事務(wù)在啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的。當(dāng)然在可重復(fù)讀隔離級(jí)別下，未提交變更對(duì)其他事務(wù)也是不可見的。
串行化，顧名思義是對(duì)于同一行記錄，“寫”會(huì)加“寫鎖”，“讀”會(huì)加“讀鎖”。當(dāng)出現(xiàn)讀寫鎖沖突的時(shí)候，后訪問的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行。
若隔離級(jí)別是“讀未提交”， 則 V1 的值就是 2。這時(shí)候事務(wù) B 雖然還沒有提交，但是結(jié)果已經(jīng)被 A 看到了。因此，V2、V3 也都是 2。
若隔離級(jí)別是“讀提交”，則 V1 是 1，V2 的值是 2。事務(wù) B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
若隔離級(jí)別是“可重復(fù)讀”，則 V1、V2 是 1，V3 是 2。之所以 V2 還是 1，遵循的就是這個(gè)要求：事務(wù)在執(zhí)行期間看到的數(shù)據(jù)前后必須是一致的。
若隔離級(jí)別是“串行化”，則在事務(wù) B 執(zhí)行“將 1 改成 2”的時(shí)候，會(huì)被鎖住。直到事務(wù) A 提交后，事務(wù) B 才可以繼續(xù)執(zhí)行。所以從 A 的角度看，V1、V2 值是 1，V3 的值是 2。
在實(shí)現(xiàn)上，數(shù)據(jù)庫里面會(huì)創(chuàng)建一個(gè)視圖，訪問的時(shí)候以視圖的邏輯結(jié)果為準(zhǔn)。在“可重復(fù)讀”隔離級(jí)別下，這個(gè)視圖是在事務(wù)啟動(dòng)時(shí)創(chuàng)建的，整個(gè)事務(wù)存在期間都用這個(gè)視圖。在“讀提交”隔離級(jí)別下，這個(gè)視圖是在每個(gè) SQL 語句開始執(zhí)行的時(shí)候創(chuàng)建的。這里需要注意的是，“讀未提交”隔離級(jí)別下直接返回記錄上的最新值，沒有視圖概念；而“串行化”隔離級(jí)別下直接用加鎖的方式來避免并行訪問。
Oracle 數(shù)據(jù)庫的默認(rèn)隔離級(jí)別其實(shí)就是“讀提交”。
事務(wù)啟動(dòng)時(shí)的視圖可以認(rèn)為是靜態(tài)的，不受其他事務(wù)更新的影響。
事務(wù)隔離的實(shí)現(xiàn)
在 MySQL 中，實(shí)際上每條記錄在更新的時(shí)候都會(huì)同時(shí)記錄一條回滾操作。記錄上的最新值，通過回滾操作，都可以得到前一個(gè)狀態(tài)的值。
同一條記錄在系統(tǒng)中可以存在多個(gè)版本，就是數(shù)據(jù)庫的多版本并發(fā)控制（MVCC）。
你一定會(huì)問，回滾日志總不能一直保留吧，什么時(shí)候刪除呢？答案是，在不需要的時(shí)候才刪除。也就是說，系統(tǒng)會(huì)判斷，當(dāng)沒有事務(wù)再需要用到這些回滾日志時(shí)，回滾日志會(huì)被刪除。
什么時(shí)候才不需要了呢？就是當(dāng)系統(tǒng)里沒有比這個(gè)回滾日志更早的 read-view 的時(shí)候。
長(zhǎng)事務(wù)意味著系統(tǒng)里面會(huì)存在很老的事務(wù)視圖。由于這些事務(wù)隨時(shí)可能訪問數(shù)據(jù)庫里面的任何數(shù)據(jù)，所以這個(gè)事務(wù)提交之前，數(shù)據(jù)庫里面它可能用到的回滾記錄都必須保留，這就會(huì)導(dǎo)致大量占用存儲(chǔ)空間。
在 MySQL 5.5 及以前的版本，回滾日志是跟數(shù)據(jù)字典一起放在 ibdata 文件里的，即使長(zhǎng)事務(wù)最終提交，回滾段被清理，文件也不會(huì)變小。
除了對(duì)回滾段的影響，長(zhǎng)事務(wù)還占用鎖資源。
事務(wù)的啟動(dòng)方式
MySQL 的事務(wù)啟動(dòng)方式有以下幾種：
1 顯式啟動(dòng)事務(wù)語句， begin 或 start transaction。配套的提交語句是 commit，回滾語句是 rollback。
2 set autocommit=0，這個(gè)命令會(huì)將這個(gè)線程的自動(dòng)提交關(guān)掉。
有些客戶端連接框架會(huì)默認(rèn)連接成功后先執(zhí)行一個(gè) set autocommit=0 的命令。這就導(dǎo)致接下來的查詢都在事務(wù)中，如果是長(zhǎng)連接，就導(dǎo)致了意外的長(zhǎng)事務(wù)。
因此，我會(huì)建議你總是使用 set autocommit=1, 通過顯式語句的方式來啟動(dòng)事務(wù)。
但是有的開發(fā)同學(xué)會(huì)糾結(jié)“多一次交互”的問題。對(duì)于一個(gè)需要頻繁使用事務(wù)的業(yè)務(wù)，第二種方式每個(gè)事務(wù)在開始時(shí)都不需要主動(dòng)執(zhí)行一次 “begin”，減少了語句的交互次數(shù)。
深入淺出索引
索引的出現(xiàn)其實(shí)就是為了提高數(shù)據(jù)查詢的效率，就像書的目錄一樣。
索引的常見模型
索引的出現(xiàn)是為了提高查詢效率，但是實(shí)現(xiàn)索引的方式卻有很多種，所以這里也就引入了索引模型的概念。簡(jiǎn)單的數(shù)據(jù)結(jié)構(gòu)，它們分別是哈希表、有序數(shù)組和搜索樹。
哈希表是一種以鍵 - 值（key-value）存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)，我們只要輸入待查找的值即 key，就可以找到其對(duì)應(yīng)的值即 Value。
不可避免地，多個(gè) key 值經(jīng)過哈希函數(shù)的換算，會(huì)出現(xiàn)同一個(gè)值的情況。處理這種情況的一種方法是，拉出一個(gè)鏈表。
有序數(shù)組索引只適用于靜態(tài)存儲(chǔ)引擎。
二叉搜索樹的特點(diǎn)是：每個(gè)節(jié)點(diǎn)的左兒子小于父節(jié)點(diǎn)，父節(jié)點(diǎn)又小于右兒子。
當(dāng)然為了維持 O(log(N)) 的查詢復(fù)雜度，你就需要保持這棵樹是平衡二叉樹。為了做這個(gè)保證，更新的時(shí)間復(fù)雜度也是 O(log(N))。
樹可以有二叉，也可以有多叉。多叉樹就是每個(gè)節(jié)點(diǎn)有多個(gè)兒子，兒子之間的大小保證從左到右遞增。二叉樹是搜索效率最高的，但是實(shí)際上大多數(shù)的數(shù)據(jù)庫存儲(chǔ)卻并不使用二叉樹。其原因是，索引不止存在內(nèi)存中，還要寫到磁盤上。
為了讓一個(gè)查詢盡量少地讀磁盤，就必須讓查詢過程訪問盡量少的數(shù)據(jù)塊。那么，我們就不應(yīng)該使用二叉樹，而是要使用“N 叉”樹。這里，“N 叉”樹中的“N”取決于數(shù)據(jù)塊的大小。
以 InnoDB 的一個(gè)整數(shù)字段索引為例，這個(gè) N 差不多是 1200。這棵樹高是 4 的時(shí)候，就可以存 1200 的 3 次方個(gè)值，這已經(jīng) 17 億了。
N 叉樹由于在讀寫上的性能優(yōu)點(diǎn)，以及適配磁盤的訪問模式，已經(jīng)被廣泛應(yīng)用在數(shù)據(jù)庫引擎中了。
在 MySQL 中，索引是在存儲(chǔ)引擎層實(shí)現(xiàn)的，所以并沒有統(tǒng)一的索引標(biāo)準(zhǔn)，即不同存儲(chǔ)引擎的索引的工作方式并不一樣。而即使多個(gè)存儲(chǔ)引擎支持同一種類型的索引，其底層的實(shí)現(xiàn)也可能不同。
InnoDB 的索引模型
在 InnoDB 中，表都是根據(jù)主鍵順序以索引的形式存放的，這種存儲(chǔ)方式的表稱為索引組織表。InnoDB 使用了 B+ 樹索引模型，所以數(shù)據(jù)都是存儲(chǔ)在 B+ 樹中的。
每一個(gè)索引在 InnoDB 里面對(duì)應(yīng)一棵 B+ 樹。索引類型分為主鍵索引和非主鍵索引。
主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚簇索引（clustered index）。
非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級(jí)索引（secondary index）。
基于主鍵索引和普通索引的查詢有什么區(qū)別？
如果語句是 select * from T where ID=500，即主鍵查詢方式，則只需要搜索 ID 這棵 B+ 樹；
如果語句是 select * from T where k=5，即普通索引查詢方式，則需要先搜索 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜索一次。這個(gè)過程稱為回表。
也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹。
索引維護(hù)
而更糟的情況是，如果 R5 所在的數(shù)據(jù)頁已經(jīng)滿了，根據(jù) B+ 樹的算法，這時(shí)候需要申請(qǐng)一個(gè)新的數(shù)據(jù)頁，然后挪動(dòng)部分?jǐn)?shù)據(jù)過去。這個(gè)過程稱為頁分裂。
當(dāng)相鄰兩個(gè)頁由于刪除了數(shù)據(jù)，利用率很低之后，會(huì)將數(shù)據(jù)頁做合并。合并的過程，可以認(rèn)為是分裂過程的逆過程。
假設(shè)你的表中確實(shí)有一個(gè)唯一字段，比如字符串類型的身份證號(hào)，那應(yīng)該用身份證號(hào)做主鍵，還是用自增字段做主鍵呢？
由于每個(gè)非主鍵索引的葉子節(jié)點(diǎn)上都是主鍵的值。如果用身份證號(hào)做主鍵，那么每個(gè)二級(jí)索引的葉子節(jié)點(diǎn)占用約 20 個(gè)字節(jié)，而如果用整型做主鍵，則只要 4 個(gè)字節(jié)，如果是長(zhǎng)整型（bigint）則是 8 個(gè)字節(jié)。
顯然，主鍵長(zhǎng)度越小，普通索引的葉子節(jié)點(diǎn)就越小，普通索引占用的空間也就越小。
有沒有什么場(chǎng)景適合用業(yè)務(wù)字段直接做主鍵的呢？還是有的。比如，有些業(yè)務(wù)的場(chǎng)景需求是這樣的：
1 只有一個(gè)索引；
2 該索引必須是唯一索引。
你一定看出來了，這就是典型的 KV 場(chǎng)景。
由于沒有其他索引，所以也就不用考慮其他索引的葉子節(jié)點(diǎn)大小的問題。
回到主鍵索引樹搜索的過程，我們稱為回表。
覆蓋索引
如果執(zhí)行的語句是 select ID from T where k between 3 and 5，這時(shí)只需要查 ID 的值，而 ID 的值已經(jīng)在 k 索引樹上了，因此可以直接提供查詢結(jié)果，不需要回表。也就是說，在這個(gè)查詢里面，索引 k 已經(jīng)“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。
由于覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段。
最左前綴原則
B+ 樹這種索引結(jié)構(gòu)，可以利用索引的“最左前綴”，來定位記錄。索引項(xiàng)是按照索引定義里面出現(xiàn)的字段順序排序的。
在建立聯(lián)合索引的時(shí)候，如何安排索引內(nèi)的字段順序。
第一原則是，如果通過調(diào)整順序，可以少維護(hù)一個(gè)索引，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的。
索引下推
在 MySQL 5.6 之前，只能從 ID3 開始一個(gè)個(gè)回表。到主鍵索引上找出數(shù)據(jù)行，再對(duì)比字段值。
MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)， 可以在索引遍歷過程中，對(duì)索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。
在圖 3 和 4 這兩個(gè)圖里面，每一個(gè)虛線箭頭表示回表一次。
圖 3 中，在 (name,age) 索引里面我特意去掉了 age 的值，這個(gè)過程 InnoDB 并不會(huì)去看 age 的值，只是按順序把“name 第一個(gè)字是’張’”的記錄一條條取出來回表。因此，需要回表 4 次。
圖 4 跟圖 3 的區(qū)別是，InnoDB 在 (name,age) 索引內(nèi)部就判斷了 age 是否等于 10，對(duì)于不等于 10 的記錄，直接判斷并跳過。在我們的這個(gè)例子中，只需要對(duì) ID4、ID5 這兩條記錄回表取數(shù)據(jù)判斷，就只需要回表 2 次。
全局鎖和表鎖：給表加個(gè)字段怎么有這么多阻礙？
數(shù)據(jù)庫鎖設(shè)計(jì)的初衷是處理并發(fā)問題。當(dāng)出現(xiàn)并發(fā)訪問的時(shí)候，數(shù)據(jù)庫需要合理地控制資源的訪問規(guī)則。而鎖就是用來實(shí)現(xiàn)這些訪問規(guī)則的重要數(shù)據(jù)結(jié)構(gòu)。
根據(jù)加鎖的范圍，MySQL 里面的鎖大致可以分成全局鎖、表級(jí)鎖和行鎖三類。
全局鎖
顧名思義，全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫實(shí)例加鎖。MySQL 提供了一個(gè)加全局讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當(dāng)你需要讓整個(gè)庫處于只讀狀態(tài)的時(shí)候，可以使用這個(gè)命令，之后其他線程的以下語句會(huì)被阻塞：數(shù)據(jù)更新語句（數(shù)據(jù)的增刪改）、數(shù)據(jù)定義語句（包括建表、修改表結(jié)構(gòu)等）和更新類事務(wù)的提交語句。
全局鎖的典型使用場(chǎng)景是，做全庫邏輯備份。
但是讓整庫都只讀，聽上去就很危險(xiǎn)：
如果你在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)基本上就得停擺；
如果你在從庫上備份，那么備份期間從庫不能執(zhí)行主庫同步過來的 binlog，會(huì)導(dǎo)致主從延遲。
一致性讀是好，但前提是引擎要支持這個(gè)隔離級(jí)別。比如，對(duì)于 MyISAM 這種不支持事務(wù)的引擎，如果備份過程中有更新，總是只能取到最新的數(shù)據(jù)，那么就破壞了備份的一致性。這時(shí)，我們就需要使用 FTWRL 命令了。
如果有的表使用了不支持事務(wù)的引擎，那么備份就只能通過 FTWRL 方法。這往往是 DBA 要求業(yè)務(wù)開發(fā)人員使用 InnoDB 替代 MyISAM 的原因之一。
業(yè)務(wù)的更新不只是增刪改數(shù)據(jù)（DML)，還有可能是加字段等修改表結(jié)構(gòu)的操作（DDL）。不論是哪種方法，一個(gè)庫被全局鎖上以后，你要對(duì)里面任何一個(gè)表做加字段操作，都是會(huì)被鎖住的。
表級(jí)鎖
MySQL 里面表級(jí)別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（meta data lock，MDL)。
表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動(dòng)釋放鎖，也可以在客戶端斷開的時(shí)候自動(dòng)釋放。需要注意，lock tables 語法除了會(huì)限制別的線程的讀寫外，也限定了本線程接下來的操作對(duì)象。
舉個(gè)例子, 如果在某個(gè)線程 A 中執(zhí)行 lock tables t1 read, t2 write; 這個(gè)語句，則其他線程寫 t1、讀寫 t2 的語句都會(huì)被阻塞。同時(shí)，線程 A 在執(zhí)行 unlock tables 之前，也只能執(zhí)行讀 t1、讀寫 t2 的操作。連寫 t1 都不允許，自然也不能訪問其他表。
而對(duì)于 InnoDB 這種支持行鎖的引擎，一般不使用 lock tables 命令來控制并發(fā)，畢竟鎖住整個(gè)表的影響面還是太大。
另一類表級(jí)的鎖是 MDL（metadata lock)。MDL 不需要顯式使用，在訪問一個(gè)表的時(shí)候會(huì)被自動(dòng)加上。MDL 的作用是，保證讀寫的正確性。你可以想象一下，如果一個(gè)查詢正在遍歷一個(gè)表中的數(shù)據(jù)，而執(zhí)行期間另一個(gè)線程對(duì)這個(gè)表結(jié)構(gòu)做變更，刪了一列，那么查詢線程拿到的結(jié)果跟表結(jié)構(gòu)對(duì)不上，肯定是不行的。
因此，在 MySQL 5.5 版本中引入了 MDL，當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加 MDL 讀鎖；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加 MDL 寫鎖。
讀鎖之間不互斥，因此你可以有多個(gè)線程同時(shí)對(duì)一張表增刪改查。
讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結(jié)構(gòu)操作的安全性。因此，如果有兩個(gè)線程要同時(shí)給一個(gè)表加字段，其中一個(gè)要等另一個(gè)執(zhí)行完才能開始執(zhí)行。
你肯定知道，給一個(gè)表加字段，或者修改字段，或者加索引，需要掃描全表的數(shù)據(jù)。在對(duì)大表操作的時(shí)候，你肯定會(huì)特別小心，以免對(duì)線上服務(wù)造成影響。
我們可以看到 session A 先啟動(dòng)，這時(shí)候會(huì)對(duì)表 t 加一個(gè) MDL 讀鎖。由于 session B 需要的也是 MDL 讀鎖，因此可以正常執(zhí)行。
之后 session C 會(huì)被 blocked，是因?yàn)?session A 的 MDL 讀鎖還沒有釋放，而 session C 需要 MDL 寫鎖，因此只能被阻塞。
如果只有 session C 自己被阻塞還沒什么關(guān)系，但是之后所有要在表 t 上新申請(qǐng) MDL 讀鎖的請(qǐng)求也會(huì)被 session C 阻塞。前面我們說了，所有對(duì)表的增刪改查操作都需要先申請(qǐng) MDL 讀鎖，就都被鎖住，等于這個(gè)表現(xiàn)在完全不可讀寫了。
事務(wù)中的 MDL 鎖，在語句執(zhí)行開始時(shí)申請(qǐng)，但是語句結(jié)束后并不會(huì)馬上釋放，而會(huì)等到整個(gè)事務(wù)提交后再釋放。
如何安全地給小表加字段？
首先我們要解決長(zhǎng)事務(wù)，事務(wù)不提交，就會(huì)一直占著 MDL 鎖。
小結(jié)
全局鎖主要用在邏輯備份過程中。對(duì)于全部是 InnoDB 引擎的庫，我建議你選擇使用–single-transaction 參數(shù)，對(duì)應(yīng)用會(huì)更友好。
表鎖一般是在數(shù)據(jù)庫引擎不支持行鎖的時(shí)候才會(huì)被用到的。如果你發(fā)現(xiàn)你的應(yīng)用程序里有 lock tables 這樣的語句，你需要追查一下，比較可能的情況是：
要么是你的系統(tǒng)現(xiàn)在還在用 MyISAM 這類不支持事務(wù)的引擎，那要安排升級(jí)換引擎；
要么是你的引擎升級(jí)了，但是代碼還沒升級(jí)。我見過這樣的情況，最后業(yè)務(wù)開發(fā)就是把 lock tables 和 unlock tables 改成 begin 和 commit，問題就解決了。
MDL 會(huì)直到事務(wù)提交才釋放，在做表結(jié)構(gòu)變更的時(shí)候，你一定要小心不要導(dǎo)致鎖住線上查詢和更新。
我給你留一個(gè)問題吧，備份一般都會(huì)在備庫上執(zhí)行，你在用–single-transaction 方法做邏輯備份的過程中，如果主庫上的一個(gè)小表做了一個(gè) DDL，比如給一個(gè)表上加了一列。這時(shí)候，從備庫上會(huì)看到什么現(xiàn)象呢？
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 時(shí)刻 1 */
Q4:show create table `t1`;
/* 時(shí)刻 2 */
Q5:SELECT * FROM `t1`;
/* 時(shí)刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 時(shí)刻 4 */
/* other tables */
參考答案如下：
如果在 Q4 語句執(zhí)行之前到達(dá)，現(xiàn)象：沒有影響，備份拿到的是 DDL 后的表結(jié)構(gòu)。
如果在“時(shí)刻 2”到達(dá)，則表結(jié)構(gòu)被改過，Q5 執(zhí)行的時(shí)候，報(bào) Table definition has changed, please retry transaction，現(xiàn)象：mysqldump 終止；
如果在“時(shí)刻 2”和“時(shí)刻 3”之間到達(dá)，mysqldump 占著 t1 的 MDL 讀鎖，binlog 被阻塞，現(xiàn)象：主從延遲，直到 Q6 執(zhí)行完成。
從“時(shí)刻 4”開始，mysqldump 釋放了 MDL 讀鎖，現(xiàn)象：沒有影響，備份拿到的是 DDL 前的表結(jié)構(gòu)。
行鎖功過：怎么減少行鎖對(duì)性能的影響？
MySQL 的行鎖是在引擎層由各個(gè)引擎自己實(shí)現(xiàn)的。但并不是所有的引擎都支持行鎖，比如 MyISAM 引擎就不支持行鎖。不支持行鎖意味著并發(fā)控制只能使用表鎖。InnoDB 是支持行鎖的，這也是 MyISAM 被 InnoDB 替代的重要原因之一。（innodb行級(jí)鎖是通過鎖索引記錄實(shí)現(xiàn)的。）
顧名思義，行鎖就是針對(duì)數(shù)據(jù)表中行記錄的鎖。這很好理解，比如事務(wù) A 更新了一行，而這時(shí)候事務(wù) B 也要更新同一行，則必須等事務(wù) A 的操作完成后才能進(jìn)行更新。
從兩階段鎖說起
在下面的操作序列中，事務(wù) B 的 update 語句執(zhí)行時(shí)會(huì)是什么現(xiàn)象呢？假設(shè)字段 id 是表 t 的主鍵。
你可以驗(yàn)證一下：實(shí)際上事務(wù) B 的 update 語句會(huì)被阻塞，直到事務(wù) A 執(zhí)行 commit 之后，事務(wù) B 才能繼續(xù)執(zhí)行。
知道了這個(gè)答案，你一定知道了事務(wù) A 持有的兩個(gè)記錄的行鎖，都是在 commit 的時(shí)候才釋放的。
也就是說，在 InnoDB 事務(wù)中，行鎖是在需要的時(shí)候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時(shí)才釋放。這個(gè)就是兩階段鎖協(xié)議。
死鎖和死鎖檢測(cè)
當(dāng)并發(fā)系統(tǒng)中不同線程出現(xiàn)循環(huán)資源依賴，涉及的線程都在等待別的線程釋放資源時(shí)，就會(huì)導(dǎo)致這幾個(gè)線程都進(jìn)入無限等待的狀態(tài)，稱為死鎖。
這時(shí)候，事務(wù) A 在等待事務(wù) B 釋放 id=2 的行鎖，而事務(wù) B 在等待事務(wù) A 釋放 id=1 的行鎖。 事務(wù) A 和事務(wù) B 在互相等待對(duì)方的資源釋放，就是進(jìn)入了死鎖狀態(tài)。當(dāng)出現(xiàn)死鎖以后，有兩種策略：
一種策略是，直接進(jìn)入等待，直到超時(shí)。這個(gè)超時(shí)時(shí)間可以通過參數(shù) innodblockwait_timeout 來設(shè)置。
另一種策略是，發(fā)起死鎖檢測(cè)，發(fā)現(xiàn)死鎖后，主動(dòng)回滾死鎖鏈條中的某一個(gè)事務(wù)，讓其他事務(wù)得以繼續(xù)執(zhí)行。將參數(shù) innodbdeadlockdetect 設(shè)置為 on，表示開啟這個(gè)邏輯。
在 InnoDB 中，innodblockwait_timeout 的默認(rèn)值是 50s，意味著如果采用第一個(gè)策略，當(dāng)出現(xiàn)死鎖以后，第一個(gè)被鎖住的線程要過 50s 才會(huì)超時(shí)退出，然后其他線程才有可能繼續(xù)執(zhí)行。對(duì)于在線服務(wù)來說，這個(gè)等待時(shí)間往往是無法接受的。
所以，超時(shí)時(shí)間設(shè)置太短的話，會(huì)出現(xiàn)很多誤傷。
所以，正常情況下我們還是要采用第二種策略，即：主動(dòng)死鎖檢測(cè)，而且 innodbdeadlockdetect 的默認(rèn)值本身就是 on。主動(dòng)死鎖檢測(cè)在發(fā)生死鎖的時(shí)候，是能夠快速發(fā)現(xiàn)并進(jìn)行處理的，但是它也是有額外負(fù)擔(dān)的。
每個(gè)新來的被堵住的線程，都要判斷會(huì)不會(huì)由于自己的加入導(dǎo)致了死鎖，這是一個(gè)時(shí)間復(fù)雜度是 O(n) 的操作。假設(shè)有 1000 個(gè)并發(fā)線程要同時(shí)更新同一行，那么死鎖檢測(cè)操作就是 100 萬這個(gè)量級(jí)的。雖然最終檢測(cè)的結(jié)果是沒有死鎖，但是這期間要消耗大量的 CPU 資源。
問題的癥結(jié)在于，死鎖檢測(cè)要耗費(fèi)大量的 CPU 資源。
一種頭痛醫(yī)頭的方法，就是如果你能確保這個(gè)業(yè)務(wù)一定不會(huì)出現(xiàn)死鎖，可以臨時(shí)把死鎖檢測(cè)關(guān)掉。
另一個(gè)思路是控制并發(fā)度。根據(jù)上面的分析，你會(huì)發(fā)現(xiàn)如果并發(fā)能夠控制住，比如同一行同時(shí)最多只有 10 個(gè)線程在更新，那么死鎖檢測(cè)的成本很低，就不會(huì)出現(xiàn)這個(gè)問題。一個(gè)直接的想法就是，在客戶端做并發(fā)控制。但是，你會(huì)很快發(fā)現(xiàn)這個(gè)方法不太可行，因?yàn)榭蛻舳撕芏?。我見過一個(gè)應(yīng)用，有 600 個(gè)客戶端，這樣即使每個(gè)客戶端控制到只有 5 個(gè)并發(fā)線程，匯總到數(shù)據(jù)庫服務(wù)端以后，峰值并發(fā)數(shù)也可能要達(dá)到 3000。
因此，這個(gè)并發(fā)控制要做在數(shù)據(jù)庫服務(wù)端。
小結(jié)
調(diào)整語句順序并不能完全避免死鎖。所以我們引入了死鎖和死鎖檢測(cè)的概念，以及提供了三個(gè)方案，來減少死鎖對(duì)數(shù)據(jù)庫的影響。減少死鎖的主要方向，就是控制訪問相同資源的并發(fā)事務(wù)量。
我給你留一個(gè)問題吧，如果你要?jiǎng)h除一個(gè)表里面的前 10000 行數(shù)據(jù)，有以下三種方法可以做到：
第一種，直接執(zhí)行 delete from T limit 10000;
第二種，在一個(gè)連接中循環(huán)執(zhí)行 20 次 delete from T limit 500;
第三種，在 20 個(gè)連接中同時(shí)執(zhí)行 delete from T limit 500。
你會(huì)選擇哪一種方法呢？為什么呢？
確實(shí)是這樣的，第二種方式是相對(duì)較好的。
第一種方式（即：直接執(zhí)行 delete from T limit 10000）里面，單個(gè)語句占用時(shí)間長(zhǎng)，鎖的時(shí)間也比較長(zhǎng)；而且大事務(wù)還會(huì)導(dǎo)致主從延遲。
第三種方式（即：在 20 個(gè)連接中同時(shí)執(zhí)行 delete from T limit 500），會(huì)人為造成鎖沖突。
事務(wù)到底是隔離的還是不隔離的？
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);
begin/start transaction 命令并不是一個(gè)事務(wù)的起點(diǎn)，在執(zhí)行到它們之后的第一個(gè)操作 InnoDB 表的語句，事務(wù)才真正啟動(dòng)。如果你想要馬上啟動(dòng)一個(gè)事務(wù)，可以使用 start transaction with consistent snapshot 這個(gè)命令。
在這個(gè)例子中，事務(wù) C 沒有顯式地使用 begin/commit，表示這個(gè) update 語句本身就是一個(gè)事務(wù)，語句完成的時(shí)候會(huì)自動(dòng)提交。事務(wù) B 在更新了行之后查詢 ; 事務(wù) A 在一個(gè)只讀事務(wù)中查詢，并且時(shí)間順序上是在事務(wù) B 的查詢之后。
這時(shí)，如果我告訴你事務(wù) B 查到的 k 的值是 3，而事務(wù) A 查到的 k 的值是 1。
在 MySQL 里，有兩個(gè)“視圖”的概念：
一個(gè)是 view。它是一個(gè)用查詢語句定義的虛擬表，在調(diào)用的時(shí)候執(zhí)行查詢語句并生成結(jié)果。創(chuàng)建視圖的語法是 create view … ，而它的查詢方法與表一樣。
另一個(gè)是 InnoDB 在實(shí)現(xiàn) MVCC 時(shí)用到的一致性讀視圖，即 consistent read view，用于支持 RC（Read Committed，讀提交）和 RR（Repeatable Read，可重復(fù)讀）隔離級(jí)別的實(shí)現(xiàn)。
它沒有物理結(jié)構(gòu)，作用是事務(wù)執(zhí)行期間用來定義“我能看到什么數(shù)據(jù)”。
“快照”在 MVCC 里是怎么工作的？
在可重復(fù)讀隔離級(jí)別下，事務(wù)在啟動(dòng)的時(shí)候就“拍了個(gè)快照”。注意，這個(gè)快照是基于整庫的。
InnoDB 里面每個(gè)事務(wù)有一個(gè)唯一的事務(wù) ID，叫作 transaction id。它是在事務(wù)開始的時(shí)候向 InnoDB 的事務(wù)系統(tǒng)申請(qǐng)的，是按申請(qǐng)順序嚴(yán)格遞增的。
而每行數(shù)據(jù)也都是有多個(gè)版本的。每次事務(wù)更新數(shù)據(jù)的時(shí)候，都會(huì)生成一個(gè)新的數(shù)據(jù)版本，并且把 transaction id 賦值給這個(gè)數(shù)據(jù)版本的事務(wù) ID，記為 row trx_id。同時(shí)，舊的數(shù)據(jù)版本要保留，并且在新的數(shù)據(jù)版本中，能夠有信息可以直接拿到它。
也就是說，數(shù)據(jù)表中的一行記錄，其實(shí)可能有多個(gè)版本 (row)，每個(gè)版本有自己的 row trx_id。
圖中虛線框里是同一行數(shù)據(jù)的 4 個(gè)版本，當(dāng)前最新版本是 V4，k 的值是 22，它是被 transaction id 為 25 的事務(wù)更新的，因此它的 row trx_id 也是 25。
實(shí)際上，圖 2 中的三個(gè)虛線箭頭，就是 undo log；而 V1、V2、V3 并不是物理上真實(shí)存在的，而是每次需要的時(shí)候根據(jù)當(dāng)前版本和 undo log 計(jì)算出來的。比如，需要 V2 的時(shí)候，就是通過 V4 依次執(zhí)行 U3、U2 算出來。
按照可重復(fù)讀的定義，一個(gè)事務(wù)啟動(dòng)的時(shí)候，能夠看到所有已經(jīng)提交的事務(wù)結(jié)果。但是之后，這個(gè)事務(wù)執(zhí)行期間，其他事務(wù)的更新對(duì)它不可見。
因此，一個(gè)事務(wù)只需要在啟動(dòng)的時(shí)候聲明說，“以我啟動(dòng)的時(shí)刻為準(zhǔn)，如果一個(gè)數(shù)據(jù)版本是在我啟動(dòng)之前生成的，就認(rèn)；如果是我啟動(dòng)以后才生成的，我就不認(rèn)，我必須要找到它的上一個(gè)版本”。
當(dāng)然，如果“上一個(gè)版本”也不可見，那就得繼續(xù)往前找。
在實(shí)現(xiàn)上， InnoDB 為每個(gè)事務(wù)構(gòu)造了一個(gè)數(shù)組，用來保存這個(gè)事務(wù)啟動(dòng)瞬間，當(dāng)前正在“活躍”的所有事務(wù) ID?！盎钴S”指的就是，啟動(dòng)了但還沒提交。
數(shù)組里面事務(wù) ID 的最小值記為低水位，當(dāng)前系統(tǒng)里面已經(jīng)創(chuàng)建過的事務(wù) ID 的最大值加 1 記為高水位。
這個(gè)視圖數(shù)組和高水位，就組成了當(dāng)前事務(wù)的一致性視圖（read-view）。
而數(shù)據(jù)版本的可見性規(guī)則，就是基于數(shù)據(jù)的 row trx_id 和這個(gè)一致性視圖的對(duì)比結(jié)果得到的。
這樣，對(duì)于當(dāng)前事務(wù)的啟動(dòng)瞬間來說，一個(gè)數(shù)據(jù)版本的 row trx_id，有以下幾種可能：
如果落在綠色部分，表示這個(gè)版本是已提交的事務(wù)或者是當(dāng)前事務(wù)自己生成的，這個(gè)數(shù)據(jù)是可見的；
如果落在紅色部分，表示這個(gè)版本是由將來啟動(dòng)的事務(wù)生成的，是肯定不可見的；
如果落在黃色部分，那就包括兩種情況：
a. 若 row trx_id 在數(shù)組中，表示這個(gè)版本是由還沒提交的事務(wù)生成的，不可見；
b. 若 row trx_id 不在數(shù)組中，表示這個(gè)版本是已經(jīng)提交了的事務(wù)生成的，可見。
因?yàn)橹蟮母?，生成的版本一定屬于上面?2 或者 3(a) 的情況，而對(duì)它來說，這些新的數(shù)據(jù)版本是不存在的，所以這個(gè)事務(wù)的快照，就是“靜態(tài)”的了。
所以你現(xiàn)在知道了，InnoDB 利用了“所有數(shù)據(jù)都有多個(gè)版本”的這個(gè)特性，實(shí)現(xiàn)了“秒級(jí)創(chuàng)建快照”的能力。
這里，我們不妨做如下假設(shè)：
事務(wù) A 開始前，系統(tǒng)里面只有一個(gè)活躍事務(wù) ID 是 99；
事務(wù) A、B、C 的版本號(hào)分別是 100、101、102，且當(dāng)前系統(tǒng)里只有這四個(gè)事務(wù)；
三個(gè)事務(wù)開始前，(1,1）這一行數(shù)據(jù)的 row trx_id 是 90。
這樣，事務(wù) A 的視圖數(shù)組就是 [99,100], 事務(wù) B 的視圖數(shù)組是 [99,100,101], 事務(wù) C 的視圖數(shù)組是 [99,100,101,102]。
從圖中可以看到，第一個(gè)有效更新是事務(wù) C，把數(shù)據(jù)從 (1,1) 改成了 (1,2)。這時(shí)候，這個(gè)數(shù)據(jù)的最新版本的 row trx_id 是 102，而 90 這個(gè)版本已經(jīng)成為了歷史版本。
第二個(gè)有效更新是事務(wù) B，把數(shù)據(jù)從 (1,2) 改成了 (1,3)。這時(shí)候，這個(gè)數(shù)據(jù)的最新版本（即 row trx_id）是 101，而 102 又成為了歷史版本。
你可能注意到了，在事務(wù) A 查詢的時(shí)候，其實(shí)事務(wù) B 還沒有提交，但是它生成的 (1,3) 這個(gè)版本已經(jīng)變成當(dāng)前版本了。但這個(gè)版本對(duì)事務(wù) A 必須是不可見的，否則就變成臟讀了。
好，現(xiàn)在事務(wù) A 要來讀數(shù)據(jù)了，它的視圖數(shù)組是 [99,100]。當(dāng)然了，讀數(shù)據(jù)都是從當(dāng)前版本讀起的。所以，事務(wù) A 查詢語句的讀數(shù)據(jù)流程是這樣的：
找到 (1,3) 的時(shí)候，判斷出 row trx_id=101，比高水位大，處于紅色區(qū)域，不可見；
接著，找到上一個(gè)歷史版本，一看 row trx_id=102，比高水位大，處于紅色區(qū)域，不可見；
再往前找，終于找到了（1,1)，它的 row trx_id=90，比低水位小，處于綠色區(qū)域，可見。
這樣執(zhí)行下來，雖然期間這一行數(shù)據(jù)被修改過，但是事務(wù) A 不論在什么時(shí)候查詢，看到這行數(shù)據(jù)的結(jié)果都是一致的，所以我們稱之為一致性讀。
一個(gè)數(shù)據(jù)版本，對(duì)于一個(gè)事務(wù)視圖來說，除了自己的更新總是可見以外，有三種情況：
版本未提交，不可見；
版本已提交，但是是在視圖創(chuàng)建后提交的，不可見；
版本已提交，而且是在視圖創(chuàng)建前提交的，可見。
現(xiàn)在，我們用這個(gè)規(guī)則來判斷圖 4 中的查詢結(jié)果，事務(wù) A 的查詢語句的視圖數(shù)組是在事務(wù) A 啟動(dòng)的時(shí)候生成的，這時(shí)候：
(1,3) 還沒提交，屬于情況 1，不可見；
(1,2) 雖然提交了，但是是在視圖數(shù)組創(chuàng)建之后提交的，屬于情況 2，不可見；
(1,1) 是在視圖數(shù)組創(chuàng)建之前提交的，可見。
更新邏輯
你看圖 5 中，事務(wù) B 的視圖數(shù)組是先生成的，之后事務(wù) C 才提交，不是應(yīng)該看不見 (1,2) 嗎，怎么能算出 (1,3) 來？
是的，如果事務(wù) B 在更新之前查詢一次數(shù)據(jù)，這個(gè)查詢返回的 k 的值確實(shí)是 1。
但是，當(dāng)它要去更新數(shù)據(jù)的時(shí)候，就不能再在歷史版本上更新了，否則事務(wù) C 的更新就丟失了。因此，事務(wù) B 此時(shí)的 set k=k+1 是在（1,2）的基礎(chǔ)上進(jìn)行的操作。
所以，這里就用到了這樣一條規(guī)則：更新數(shù)據(jù)都是先讀后寫的，而這個(gè)讀，只能讀當(dāng)前的值，稱為“當(dāng)前讀”（current read）。
因此，在更新的時(shí)候，當(dāng)前讀拿到的數(shù)據(jù)是 (1,2)，更新后生成了新版本的數(shù)據(jù) (1,3)，這個(gè)新版本的 row trx_id 是 101。
其實(shí)，除了 update 語句外，select 語句如果加鎖，也是當(dāng)前讀。
再往前一步，假設(shè)事務(wù) C 不是馬上提交的，而是變成了下面的事務(wù) C’，會(huì)怎么樣呢？
事務(wù) C’的不同是，更新后并沒有馬上提交，在它提交前，事務(wù) B 的更新語句先發(fā)起了。前面說過了，雖然事務(wù) C’還沒提交，但是 (1,2) 這個(gè)版本也已經(jīng)生成了，并且是當(dāng)前的最新版本。那么，事務(wù) B 的更新語句會(huì)怎么處理呢？
事務(wù) C’沒提交，也就是說 (1,2) 這個(gè)版本上的寫鎖還沒釋放。而事務(wù) B 是當(dāng)前讀，必須要讀最新版本，而且必須加鎖，因此就被鎖住了，必須等到事務(wù) C’釋放這個(gè)鎖，才能繼續(xù)它的當(dāng)前讀。
事務(wù)的可重復(fù)讀的能力是怎么實(shí)現(xiàn)的？
可重復(fù)讀的核心就是一致性讀（consistent read）；而事務(wù)更新數(shù)據(jù)的時(shí)候，只能用當(dāng)前讀。如果當(dāng)前的記錄的行鎖被其他事務(wù)占用的話，就需要進(jìn)入鎖等待。
而讀提交的邏輯和可重復(fù)讀的邏輯類似，它們最主要的區(qū)別是：
在可重復(fù)讀隔離級(jí)別下，只需要在事務(wù)開始的時(shí)候創(chuàng)建一致性視圖，之后事務(wù)里的其他查詢都共用這個(gè)一致性視圖；
在讀提交隔離級(jí)別下，每一個(gè)語句執(zhí)行前都會(huì)重新算出一個(gè)新的視圖。
那么，我們?cè)倏匆幌拢谧x提交隔離級(jí)別下，事務(wù) A 和事務(wù) B 的查詢語句查到的 k，分別應(yīng)該是多少呢？
下面是讀提交時(shí)的狀態(tài)圖，可以看到這兩個(gè)查詢語句的創(chuàng)建視圖數(shù)組的時(shí)機(jī)發(fā)生了變化，就是圖中的 read view 框。
這時(shí)，事務(wù) A 的查詢語句的視圖數(shù)組是在執(zhí)行這個(gè)語句的時(shí)候創(chuàng)建的，時(shí)序上 (1,2)、(1,3) 的生成時(shí)間都在創(chuàng)建這個(gè)視圖數(shù)組的時(shí)刻之前。但是，在這個(gè)時(shí)刻：
(1,3) 還沒提交，屬于情況 1，不可見；
(1,2) 提交了，屬于情況 3，可見。
所以，這時(shí)候事務(wù) A 查詢語句返回的是 k=2。
顯然地，事務(wù) B 查詢結(jié)果 k=3。
小結(jié)
InnoDB 的行數(shù)據(jù)有多個(gè)版本，每個(gè)數(shù)據(jù)版本有自己的 row trxid，每個(gè)事務(wù)或者語句有自己的一致性視圖。普通查詢語句是一致性讀，一致性讀會(huì)根據(jù) row trxid 和一致性視圖確定數(shù)據(jù)版本的可見性。
對(duì)于可重復(fù)讀，查詢只承認(rèn)在事務(wù)啟動(dòng)前就已經(jīng)提交完成的數(shù)據(jù)；
對(duì)于讀提交，查詢只承認(rèn)在語句啟動(dòng)前就已經(jīng)提交完成的數(shù)據(jù)；
而當(dāng)前讀，總是讀取已經(jīng)提交完成的最新版本。
當(dāng)然，MySQL 8.0 已經(jīng)可以把表結(jié)構(gòu)放在 InnoDB 字典里了，也許以后會(huì)支持表結(jié)構(gòu)的可重復(fù)讀。
在此我向大家推薦一個(gè)架構(gòu)學(xué)習(xí)交流群。交流學(xué)習(xí)群號(hào)：833145934 里面資深架構(gòu)師會(huì)分享一些整理好的錄制視頻錄像和BATJ面試題：有Spring，MyBatis，Netty源碼分析，高并發(fā)、高性能、分布式、微服務(wù)架構(gòu)的原理，JVM性能優(yōu)化、分布式架構(gòu)等這些成為架構(gòu)師必備的知識(shí)體系。還能領(lǐng)取免費(fèi)的學(xué)習(xí)資源，目前受益良多。
注：文章來源于網(wǎng)絡(luò)
出處：http://www.linkedkeeper.com/1304.html
