MySQL 事務、日誌、鎖、索引學習總結，
MySQL架構
MySQL可分為Server和存儲引擎兩部分，如圖1所示。
Server層：包括客戶端連接器、查詢快取、解析/預處理器、優化器、執行器等，以及MySQL內置函數和所有跨引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖等。每個部分的功能參考
存儲引擎層：負責數據的存儲和讀取，為插件式架構，支援innoDB、MyISAM、Memory等多個存儲引擎，InnoDB為默認存儲引擎。
圖1 MySQL邏輯架構
表存儲
表包含兩部分，表結構定義和數據。在MySQL中，表由存儲引擎負責存儲，以InnoDB為例：
- 表結構定義：在MySQL8.0之前，只能存在於.frm 後綴文件中(MySQL Server層和InnoDB中均存在)；之後允許將其放在系統數據表中。
- 表數據：包括數據段(主鍵索引)和索引段(二級索引)，由innodb_file_per_table參數控制存儲位置, 自5.6.6開始，默認配置為ON。
- OFF表示，存放在系統共享表空間
- ON表示，單獨存放在一個.ibd文件中
使用單獨文件存儲表數據，在刪除表時直接刪除文件可以回收空間。
而在共享表空間中存儲，即使將表刪除，空間也是不會回收的。因為InnoDB 中數據以B+樹結構組織，刪除其中一部分記錄，只是將其相應的位置標記為刪除可復用(由於索引排序，記錄只能被相應的數據所復用)；而當數據頁上所有記錄都被刪除時，意味著數據頁可以復用到任意位置。
在刪除數據之後而又沒用復用時，就會造成數據空洞；同樣的，在新增時也可能導致數據空洞，當一個數據頁A已經寫滿了，但由於索引的有序性，需要在A中間再插入一條數據，這時就需要進行頁分裂再申請一個數據頁來保存數據(當前數據以及之後的索引分裂到新的數據頁)。
在大量增刪操作之後的表可能存在數據空洞，即很多位置無法復用。通過重建表可以實現空間收縮：
- recreate table: alter table t engine = InnoDB，可以實現在線重建表，短暫持有MDL寫鎖，之後持有MDL讀鎖。使用一個rowlog存儲重建表期間表數據的修改記錄，不會阻塞其他事務的增刪改。
- analyze table t 對錶的索引資訊做重新統計，沒有修改數據，加MDL讀鎖。
- optimize table t 等於recreate+analyze。
日誌
MySQL 中記錄日誌的方式為WAL(Write-Ahead Logging），先預寫日誌再更新數據，對於非記憶體資料庫來說，可以減少磁碟IO提高性能。
MySQL日誌：
- binlog：在對數據進行增刪改之後，都將會記錄一條binlog,可用於數據歸檔和備份，存在兩種格式的binlog_format：
- statement記錄的是SQL語句，最後會有COMMIT。
- row記錄的實際操作的數據記錄，最後會有一個XID event。
sync_binlog設置為1時，表示每次事務操作的binlog都持久化到磁碟中，在MySQL異常重啟後可保證binlog不丟失。
InnoDB日誌：
- redolog：在對數據進行增刪改之後，都將會記錄一條redolog。其為物理日誌，記錄的是在某個數據頁上做了什麼修改，可用於崩潰後恢復事務數據和減少更新數據時的磁碟IO訪問。innodb_flush_log_at_trx_commit這個參數設置成1的時候，表示每次事務的redo log都直接持久化到磁碟。
- undolog：在事務中對數據每進行一次修改便會記錄一次undolog，用於將最新數據恢復到之前事務版本。在長事務中可能佔用大量存儲空間。在系統判定undo-log無用時，會將其刪除，即在沒有比回滾日誌更早的Read View時。
binlog和redolog存在一個共同的數據欄位XID，通過這個欄位可以將redolog和binlog關聯起來，可用於事務恢復。
索引
在InnoDB中，表數據都是根據主鍵順序以索引的形式存放的，這種存儲方式的表稱為索引組織表。索引的底層數據結構為B+樹，所以每一個索引在InnoDB中都對應一顆B+樹，InnoDB中存在有兩種類型的索引：
- 聚簇索引(主鍵)
聚簇索引的葉子結點存的是整行數據。 - 二級索引
二級索引分為唯一和普通索引，葉子結點中存的是主鍵的值，如果需要獲取整行數據，需要使用主鍵值再去聚簇索引中回表查詢。
索引維護：由於底層數據結構為B+樹，所以維護索引就是在維護B+樹；而B+樹是有序的，插入更新數據時可能導致數據移動而引入額外磁碟IO。而在索引欄位重複時，又會頁分裂出新的數據頁來保存重複Key。
創建高性能索引
B+ 樹的高和階：階由頁大小(默認16K)和索引大小而決定，而高又由階和行數決定。
InnoDB 事務
資料庫事務是資料庫管理系統執行過程中的一個邏輯單位，由一個有限的資料庫操作序列構成，具備四個基本屬性，原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)、持久性(Duarbility)。
如何啟動/回滾事務：
- 手動使用 BEGIN, ROLLBACK, COMMIT來實現；BEGIN 開始一個事務，ROLLBACK 事務回滾，COMMIT 事務提交
- 直接用 SET AUTOCOMMIT = 0/1 來改變 MySQL 的自動提交模式:
- 若參數autocommit=0(禁止自動提交)，事務則在用戶本次對數據進行操作時自動開啟，在用戶執行commit命令時提交，用戶本次對資料庫開始進行操作到用戶執行commit命令之間的一系列操作為一個完整的事務周期。若不執行commit命令，系統則默認事務回滾。總而言之，當前情況下事務的狀態是自動開啟手動提交。
- 若參數autocommit=1（系統默認值，開啟自動提交），事務的開啟與提交又分為兩種狀態：
- 手動開啟手動提交：當用戶執行start transaction命令時（事務初始化），一個事務開啟，當執行commit命令時事務提交，若不執行commit命令，系統則默認事務回滾。
- 自動開啟自動提交：如果用戶在當前情況下未執行start transaction命令而對資料庫進行了操作，系統則默認用戶對資料庫的每一個操作為一個孤立的事務，也就是說用戶每進行一次操作系都會即時提交或者即時回滾。
事務提交:
InnoDB中事務分為兩階段提交：
- 第一階段是在更新完數據後，記錄redo-log，這時redolog狀態為prepare
- 第二階段是在記完redo-log之後，記錄bin-log，將redolog狀態置為commit
兩階段提交常用於分散式系統中，InnoDB 中使用兩階段提交可以保證在事務恢復時，其binlog是正確的；如果只記錄redolog，在恢復事務之後就會導致數據與binlog不一致。
事務恢復
得益於兩階段提交，事務在恢復之後可以保證數據與binlog的一致，事務恢復時的判斷規則為：
- 如果redo log裡面的事務是完整的，也就是有prepare、commit標識，則直接提交；
- 如果redo log裡面的事務只有完整的prepare，則判斷對應的事務binlog是否是存在並且完整的，如果是，則提交事務； 否則，回滾事務。
實現事務隔離
在InndDB中，通過MVCC(一致性視圖)實現事務的隔離性，在InnoDB中，一行數據物理上只保存最新值，但通過undolog可以回滾到之前事務版本，所以數據可能存在多個版本(事務Id用作區分)。MVCC便是使用事務Id、數據、undolog來實現一致性視圖(read-view），但在生成一致性讀時，可重複讀(Repeatable read)和讀提交(read committed)的read view生成策略是不一致的：
- 在可重複讀級別下，整個事務存在期間都使用同一個視圖，只會獲取小於等於當前事務Id版本數據，如果數據被更新了，就通過undolog計算得到相應版本的數據，解決了不可重複讀問題。
- 在讀提交級別下，視圖在每條SQL執行期間創建，只獲取已提交的最新事務版本數據，所以每條SQL看到的數據可能都是不一致的，存在不可重複讀問題。
事務問題
- 在可重複讀級別下，每條SQL使用到的鎖需要等到事務提交或回滾之後才釋放，存在長事務時，可能會佔用的更多的資源，如鎖、undolog等，所以應避免長事務並且將資源佔用較多的SQL放在事務後程進行。
- 在讀提交級別下，每條SQL使用到的鎖在SQL執行完成後便會釋放，在多事務並行時，如果binlog_format=statement時可能造成數據和binlog的不一致，所以應將其設置為row。
InnoDB 行鎖、間隙鎖、臨鍵鎖
行鎖(record lock)：
InnoDB事務中，一條更新語句執行時，必須要獲得其行寫鎖，而行鎖分為讀鎖和寫鎖，其中讀鎖之間兼容，讀寫鎖、寫鎖之間互斥，如 select id from table1 lock in share mode 當前讀加讀鎖；select if from table1 for update 當前讀加寫鎖。在RR級別下，行鎖在需要的時候才加上，但是得等到事務結束時才釋放，這叫做兩階段鎖協議，兩階段加鎖協議主要為了保證事務的隔離性(解決不可重複讀)和一致性(數據狀態一致)。
間隙鎖(Gap Lock)：
通過兩階段鎖協議可以解決不可重複讀和數據一致性問題，但幻讀(兩次當前讀時，行數不一致)仍然存在，即便將所有行都加上行鎖也無法解決幻讀問題。間隙鎖(Gap Lock)的引入就是為解決幻讀問題，間隙鎖鎖定的記錄之間間隙，是一個區間範圍；在被間隙鎖鎖定的區間範圍內，不能插入新的數據。
如索引中存在三個聚簇節點Id[1,3,6]，事務A執行update table t1 set name = 'ss' where t1.id = 3，若沒有間隙鎖，事務可以同時執行insert into table(id, name) values(4, 's4')；但由於間隙鎖的存在(1,6)，事務B需要等待事務A釋放間隙鎖之後才能新增成功。在當前讀時，索引掃描到的記錄都會加上間隙鎖，區間為前開後開。
臨鍵鎖(next-key lock)：
臨鍵鎖是加鎖的基本單位，由行鎖+間隙鎖組成，區間範圍為前開後閉。在使用select * from table for update時，會將表中所有記錄行鎖和間隙都鎖住，間隙鎖區間為(-∞，+∞]。
加鎖有兩個基本原則，一是加鎖基本單位為臨鍵鎖；二是索引查找過程中訪問到的對象才會加鎖。針對等值查詢，有兩個專門優化的點，一是在唯一索引上加鎖時，臨鍵鎖退化成行鎖(唯一約束已經確保不能新增相同數據)；二是在普通索引上時，臨鍵鎖退化成間隙鎖(間隙已經確保無法新增相同數據)。
InnoDB Buffer
InnoDB使用緩衝池(buffer pool)管理記憶體，在緩衝池中存在一個change buffer，用來對在數據進行增、刪、改時進行優化，可以減少隨機IO讀取。innodb_change_buffer_max_size=50，表示change buffer最多佔用buffer bool的50%
InnoDB中查詢記錄是一條一條的，但是讀取時是以數據頁為單位的，讀取一條記錄時會將記錄所在的數據頁整個讀取到緩衝池中。
Change Buffer的應用
數據的更新/插入/刪除都包括對聚簇索引和普通索引的修改，這個過程中分為兩種情況：
- 如果對應數據頁(聚簇和二級)已經存在於記憶體：直接更新記憶體中的數據頁，記錄redo-log、binlog；
- 如果數據頁不在記憶體之中，對於唯一索引(包括聚簇)，需要將數據載入到記憶體中進行唯一性約束校驗，校驗通過再在記憶體中更新數據、記錄redolog；對於非唯一索引，直接將數據更改日誌存儲在change-buffer中(不寫磁碟)，記錄redolog、binlog。
對於一個唯一、非唯一索引皆存的表來說，對於數據的更新可能是不同步的，主鍵、唯一索引數據已經在記憶體中更改、而普通索引則是將更改先記錄在change buffer中，寫入change buffer時也會記錄redolog、binlog，確保崩潰後恢復。change buffer不僅在記憶體中，也會被寫入到系統表空間中，change buffer寫入磁碟的操作叫做purge。在特定時刻，change buffer中的操作會merge到原數據頁中：一是訪問這個數據頁時；二是後台執行緒定期維護；三是在系統正常關閉的過程中。
