MySQL涉及到的知識多且深，這裡主要撿兩個最基礎也是後端RD最常接觸到的點來展開——InnoDB的事務及索引原理，偏理論，面試中被問到的機率非常大。有理解不對的地方，歡迎糾錯。

一、MySQL分層架構

接入層：主要負責連線處理、授權認證、安全等事宜。

服務層：查詢解析、分析、最佳化、快取及所有內建函式，所有跨儲存引擎的功能都在這一層實現：儲存過程、觸發器、檢視、binlog、表鎖等

儲存引擎層：負責MySQL中資料的儲存和提取，服務層透過API與儲存引擎通訊，儲存引擎包含幾十個底層函式API，每種引擎提供一套具體實現。

系統檔案層：負責底層檔案系統的讀寫。

這種分層架構，可以將各層的職責劃分得很清晰，方便擴充套件。

二、InnoDB儲存引擎

InnoDB屬儲存引擎層，是MySQL的預設儲存引擎（5。1版本及以上）。InnoDB相較其它儲存引擎的主要特點有：支援事務、支援高併發、自動崩潰恢復、基於聚簇索引組織表資料等。我們主要關注如下問題：InnoDB是如何保證事務？如何支援高併發？資料如何儲存？

三、事務原理

事務具有4個基本特徵，分別是：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、永續性（Duration），簡稱ACID，這是標準SQL規範，InnoDB透過自己的方式實現之。

1、ACID 定義如下：

原子性：最小工作單元，要麼全成功，要麼全失敗 。

一致性：事務開始和結束後，資料庫的完整性不會被破壞 。

隔離性：事務之間互不影響，四種隔離級別 RU（讀未提交）、RC（讀已提交）、RR（可重複讀）、SERIALIZABLE （序列化）。

永續性：事務提交後，對資料的修改是永久性的，即使系統故障也不會丟失 。

主要關注隔離性，InnoDB預設隔離級別為RR，在該級別下InnoDB透過MVCC機制—— “非阻塞的快照讀和加鎖（行鎖+間隙鎖）的當前讀”避免了幻讀的發生。那麼什麼是幻讀呢？

所謂幻讀

，就是同一個事務，連續做兩次當前讀 （例如：select * from t1 where id = 10 for update；），那麼這兩次當前讀返回的是完全相同的記錄 （記錄數量一致，記錄本身也一致），第二次的當前讀，不會比第一次返回更多的記錄 （幻象）。

2、事務日誌

InnoDB 使用undo、 redo log來保證事務原子性、一致性及永續性，同時採用預寫日誌方式將隨機寫入變成順序追加寫入，提升事務效能。

undo log

：記錄事務變更前的狀態。操作資料之前，先將資料備份到undo log，然後進行資料修改，如果出現錯誤或使用者執行了rollback語句，則系統就可以利用undo log中的備份資料恢復到事務開始之前的狀態。

redo log:

記錄事務變更後的狀態。在事務提交前，只要將redo log持久化即可，資料在記憶體中變更。當系統崩潰時，雖然資料沒有落盤，但是redo log已持久化，系統可以根據redo Log的內容，將所有資料恢復到最新的狀態。

checkpoint:

隨著時間的積累，redo Log會變的很大很大。如果每次都從第一條記錄開始恢復，恢復的過程就會很慢。為了減少恢復的時間，就引入了Checkpoint機制。定期將databuffer的內容重新整理到磁碟datafile內，然後清除checkpoint之前的redo log。

恢復

：InnoDB透過 載入最新快照，然後重做checkpoint之後所有事務（包括未提交和回滾了的），再透過undo log回滾那些未提交的事務，來完成資料恢復。需要注意的地方是，undo 日誌其實也是行資料，對其寫操作也會記錄到redo log內，即undo log也是透過redo log來保證持久化的。

上圖為一個事務寫操作所執行的大致過程，整個過程中只有一次刷盤操作，即事務提交時的redo log的寫盤。其實寫盤並不一定會立馬持久化到磁碟，要看資料庫配置，預設Innodb_flush_log_at_trx_commit=1，即預設情況下，redo log一次寫盤操作會立即寫到磁碟中，是最保險的方案。

InnoDB中多個事務共享一個redo log buffer， 寫盤時，會將當前 buffer中的多個事務日誌持久化，而不管事務有沒有commit，而且並不是只有事務commit才會觸發redo log寫盤，其它操作如 redo log buffer空間不足、觸發checkpoint、例項shutdown及binlog切換時都會觸發redo log寫盤操作。

3、MVCC

InnoDB使用MVCC機制來提升RR隔離級別的併發性。MVCC （Multi-Version Concurrency Control） 多版本併發控制協議，將讀操作分成兩類：

快照讀 (snapshot read)與當前讀 (current read)

。快照讀，讀取的是記錄的可見版本 （有可能是歷史版本），不用加鎖。當前讀，讀取的是記錄的最新版本，並且，當前讀返回的記錄，都會加上鎖，保證其他事務不會再併發修改這條記錄。

快照讀：簡單的select操作，屬於快照讀，不加鎖。如：

select * from table where ？；

當前讀：特殊的讀操作，插入/更新/刪除操作，屬於當前讀，需要加鎖。以下都是當前讀：

select * from table where ？ lock in share mode；

select * from table where ？ for update；

insert into table values （…）；

update table set ？ where ？；

delete from table where ？；

快照讀是透過undo log來實現多個版本的控制的。如下圖，每個資料行：row_id 為行id，trx_id表示最近修改的事務id，db_roll_ptr為指向undo segment中undo log的指標。快照讀時，比較當前事務id與trx_id 的關係，如果trx_id 小於事務id，則該條資料對當前事務可見，反之不可見，不可見時再透過db_roll_ptr查詢歷史版本記錄，取出可見的最近的歷史記錄。undo log 的鏈路不會很深，後臺purge執行緒定期清除無用的歷史版本（在沒有活動事務依賴時，undo log即可被刪除）。

4、加鎖分析

：總結於何登成的

http：//

hedengcheng。com/？

p=771

MySQL 加鎖處理分析

當前讀都會加鎖，怎麼加？則要看具體情景——隔離級別及索引情況。

在InnoDB的RR隔離級別下，對於同一條SQL語句：

DELETE

FROM

T1 WHERE ID=10;

當ID列為主鍵時：鎖主鍵索引上id=10 的記錄

當ID列為唯一索引：先鎖唯一索引上的id=10的行，再鎖主鍵索引上 name=d 的行

當ID列為二級索引：在二級索引上，會給id=10的所有行加X鎖，而且會給被鎖行的前後範圍加GAP鎖；主鍵索引上，給相應記錄加X鎖。

當ID列未加索引：此種情況後果很嚴重！主鍵索引所有行都被加X鎖，所有間隙被加GAP鎖！全表的資料都被鎖的，沒有併發可言，因此一定要檢查當前讀的where條件語句是否走索引。

GAP鎖的意義：當前事務佔住間隙範圍，避免其它事務往這個範圍插入資料，引起幻讀，只發生在RR隔離級別。如果id列是唯一索引（或主鍵索引 ），當前讀id不存在時，InnoDB也會給範圍加GAP鎖。

四、索引結構

使用索引的優點：減少需要掃描的資料量，避免檔案排序及臨時表，將隨機I/O變為順序I/O等，從而達到更快的讀寫資料。InnoDB採用B+樹的結構來組織索引。

1、B+樹：

InnoDB之所以採用B+樹來組織索引，是由其扁平化的結構決定的。非葉子節點記錄索引列的key值，真實資料只存在葉子節點，這樣的好處是非葉子節點很適合做快取（一個大節點約16k，能儲存1200多個key值）。真實資料庫中的B+樹是非常扁平的，高度為3時 容量可達22GB；高度4時則可儲存 26TB。另外大節點之間用雙向連結串列互連，方便順序掃描。

2、聚簇索引及二級索引

聚簇索引：是按照每張表的主鍵構造一顆B+樹，同時葉子結點存放的即為整張表的行紀錄資料（聚集索引的葉子結點也稱為資料頁），是一種資料儲存方式。主鍵id為自增是有優勢的，插入是順序的，比完全隨機效能要高，不會產生頁分裂和碎片。

二級索引：InnoDB葉節點儲存的是主鍵id，走二級索引查詢資料詳情時，先索引到主鍵id，再回聚簇表查詢資料詳情，需要走兩次索引查詢。主鍵的資料型別儘量要小，它直接影響索引樹的儲存空間。

3、高效能索引策略

正確地建立和使用索引是實現高效能查詢的基礎。

獨立的列：指索引列不能是表示式的一部分，也不能是函式的引數。我們應該養成簡化 WHERE條件的習慣，始終將索引列單獨放在比較符號的一側。

字首索引及索引選擇性：有時候需要索引很長的字元列，這會讓索引變得大且慢。可以索引開始的部分字元，可大大節約索引空間，提高索引效率，這就是字首索引。索引的選擇性越高則查詢效率越高，字首索引取多長字元，需要折中資料大小與選擇性強弱。

合適的索引列順序：索引不是越多越好，通常會建一個複合索引，以滿足多個查詢語句，這就要求合適的索引列順序。複合索引的匹配規則是，最左字首匹配，且遇到第一範圍查詢條件時，停止匹配。因此通常會將通用的列放前面，範圍查詢列放後面。

覆蓋索引：如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，稱之為“覆蓋索引”。這是個非常有用的工具，能夠極大的提高效能，只需要掃描二級索引而無須回表。

使用索引掃描來排序：MySQL有兩種方式生成有序的結果，排序操作或者按索引順序掃描。排序操作費時費空間，而索引掃描只需要從一條索引記錄移到緊接著的下一條記錄，是很快的。需要注意，只有當索引的列順序和ORDER BY子句的順序完全一致，並且所有列的排序方向都一樣時，MySQL才能夠使用索引來對結果做排序。

SQL最佳化跟索引息息相關，需要具體場景具體分析。EXPLAIN之後，關注有沒有走預期的索引，有沒有檔案排序，掃描多少資料量 等等。

五、總結

後端RD會經常遇到MySQL寫操作死鎖及慢SQL最佳化，帶著這些問題，我們能更快的去了解InnoDB的事務及索引原理；反之，理解了原理，再回顧之前遇到的場景，也能豁然。

六、參考

1、

http：//

hedengcheng。com/？

p=771

2、《高效能MySQL》

3、

https：//

liuzhengyang。github。io/

2017/04/18/innodb-mvcc/

4、

https：//www。

cnblogs。com/shijingxian

g/articles/4743324。html

5、

http：//

mysql。taobao。org/monthl

y/2015/05/01/