InnoDB事務及索引原理

06-05

InnoDB事務及索引原理

來自專欄程序員的修鍊之道

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=771MySQL 加鎖處理分析

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

在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/shijingxiang/articles/4743324.html

5、 http://mysql.taobao.org/monthly/2015/05/01/