MySQL 事務、日誌、鎖、索引學習總結，

cd_along發表於2021-08-11
MySql索引

MySQL架構

MySQL可分為Server和儲存引擎兩部分，如圖1所示。
Server層：包括客戶端聯結器、查詢快取、解析/前處理器、優化器、執行器等，以及MySQL內建函式和所有跨引擎的功能都在這一層實現，比如儲存過程、觸發器、檢視等。每個部分的功能參考
儲存引擎層：負責資料的儲存和讀取，為外掛式架構，支援innoDB、MyISAM、Memory等多個儲存引擎，InnoDB為預設儲存引擎。
image
圖1 MySQL邏輯架構

表儲存

表包含兩部分，表結構定義和資料。在MySQL中，表由儲存引擎負責儲存，以InnoDB為例：

  1. 表結構定義：在MySQL8.0之前，只能存在於.frm 字尾檔案中(MySQL Server層和InnoDB中均存在)；之後允許將其放在系統資料表中。
  2. 表資料：包括資料段(主鍵索引)和索引段(二級索引)，由innodb_file_per_table引數控制儲存位置, 自5.6.6開始，預設配置為ON。
    1. OFF表示，存放在系統共享表空間
    2. ON表示，單獨存放在一個.ibd檔案中

使用單獨檔案儲存表資料，在刪除表時直接刪除檔案可以回收空間。
而在共享表空間中儲存，即使將表刪除，空間也是不會回收的。因為InnoDB 中資料以B+樹結構組織，刪除其中一部分記錄，只是將其相應的位置標記為刪除可複用(由於索引排序，記錄只能被相應的資料所複用)；而當資料頁上所有記錄都被刪除時，意味著資料頁可以複用到任意位置。

在刪除資料之後而又沒用複用時，就會造成資料空洞；同樣的，在新增時也可能導致資料空洞，當一個資料頁A已經寫滿了，但由於索引的有序性，需要在A中間再插入一條資料，這時就需要進行頁分裂再申請一個資料頁來儲存資料(當前資料以及之後的索引分裂到新的資料頁)。

在大量增刪操作之後的表可能存在資料空洞，即很多位置無法複用。通過重建表可以實現空間收縮：

  1. recreate table: alter table t engine = InnoDB，可以實現線上重建表，短暫持有MDL寫鎖，之後持有MDL讀鎖。使用一個rowlog儲存重建表期間表資料的修改記錄，不會阻塞其他事務的增刪改。
  2. analyze table t 對錶的索引資訊做重新統計，沒有修改資料，加MDL讀鎖。
  3. optimize table t 等於recreate+analyze。

日誌

MySQL 中記錄日誌的方式為WAL(Write-Ahead Logging），先預寫日誌再更新資料，對於非記憶體資料庫來說，可以減少磁碟IO提高效能。
MySQL日誌：

  1. binlog：在對資料進行增刪改之後，都將會記錄一條binlog,可用於資料歸檔和備份，存在兩種格式的binlog_format：
    1. statement記錄的是SQL語句，最後會有COMMIT。
    2. row記錄的實際操作的資料記錄，最後會有一個XID event。

sync_binlog設定為1時，表示每次事務操作的binlog都持久化到磁碟中，在MySQL異常重啟後可保證binlog不丟失。

InnoDB日誌：

  1. redolog：在對資料進行增刪改之後，都將會記錄一條redolog。其為物理日誌，記錄的是在某個資料頁上做了什麼修改，可用於崩潰後恢復事務資料和減少更新資料時的磁碟IO訪問。innodb_flush_log_at_trx_commit這個引數設定成1的時候，表示每次事務的redo log都直接持久化到磁碟。
  2. undolog：在事務中對資料每進行一次修改便會記錄一次undolog，用於將最新資料恢復到之前事務版本。在長事務中可能佔用大量儲存空間。在系統判定undo-log無用時，會將其刪除，即在沒有比回滾日誌更早的Read View時。

binlog和redolog存在一個共同的資料欄位XID，通過這個欄位可以將redolog和binlog關聯起來，可用於事務恢復。

索引

在InnoDB中，表資料都是根據主鍵順序以索引的形式存放的，這種儲存方式的表稱為索引組織表。索引的底層資料結構為B+樹，所以每一個索引在InnoDB中都對應一顆B+樹，InnoDB中存在有兩種型別的索引：

  1. 聚簇索引(主鍵)
    聚簇索引的葉子結點存的是整行資料。
  2. 二級索引
    二級索引分為唯一和普通索引，葉子結點中存的是主鍵的值，如果需要獲取整行資料，需要使用主鍵值再去聚簇索引中回表查詢。

索引維護：由於底層資料結構為B+樹，所以維護索引就是在維護B+樹；而B+樹是有序的，插入更新資料時可能導致資料移動而引入額外磁碟IO。而在索引欄位重複時，又會頁分裂出新的資料頁來儲存重複Key。
建立高效能索引

B+ 樹的高和階：階由頁大小(預設16K)和索引大小而決定，而高又由階和行數決定。

InnoDB 事務

資料庫事務是資料庫管理系統執行過程中的一個邏輯單位，由一個有限的資料庫操作序列構成，具備四個基本屬性，原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)、永續性(Duarbility)。

如何啟動/回滾事務：

  1. 手動使用 BEGIN, ROLLBACK, COMMIT來實現；BEGIN 開始一個事務，ROLLBACK 事務回滾，COMMIT 事務提交
  2. 直接用 SET AUTOCOMMIT = 0/1 來改變 MySQL 的自動提交模式:
    • 若引數autocommit=0(禁止自動提交)，事務則在使用者本次對資料進行操作時自動開啟，在使用者執行commit命令時提交，使用者本次對資料庫開始進行操作到使用者執行commit命令之間的一系列操作為一個完整的事務週期。若不執行commit命令，系統則預設事務回滾。總而言之，當前情況下事務的狀態是自動開啟手動提交。
    • 若引數autocommit=1（系統預設值，開啟自動提交），事務的開啟與提交又分為兩種狀態：
      • 手動開啟手動提交：當使用者執行start transaction命令時（事務初始化），一個事務開啟，當執行commit命令時事務提交，若不執行commit命令，系統則預設事務回滾。
      • 自動開啟自動提交：如果使用者在當前情況下未執行start transaction命令而對資料庫進行了操作，系統則預設使用者對資料庫的每一個操作為一個孤立的事務，也就是說使用者每進行一次操作系都會即時提交或者即時回滾。

事務提交:

InnoDB中事務分為兩階段提交：

  1. 第一階段是在更新完資料後，記錄redo-log，這時redolog狀態為prepare
  2. 第二階段是在記完redo-log之後，記錄bin-log，將redolog狀態置為commit

兩階段提交常用於分散式系統中，InnoDB 中使用兩階段提交可以保證在事務恢復時，其binlog是正確的；如果只記錄redolog，在恢復事務之後就會導致資料與binlog不一致。

事務恢復

得益於兩階段提交，事務在恢復之後可以保證資料與binlog的一致，事務恢復時的判斷規則為：

  1. 如果redo log裡面的事務是完整的，也就是有prepare、commit標識，則直接提交；
  2. 如果redo log裡面的事務只有完整的prepare，則判斷對應的事務binlog是否是存在並且完整的，如果是，則提交事務； 否則，回滾事務。

實現事務隔離

在InndDB中，通過MVCC(一致性檢視)實現事務的隔離性，在InnoDB中，一行資料物理上只儲存最新值，但通過undolog可以回滾到之前事務版本，所以資料可能存在多個版本(事務Id用作區分)。MVCC便是使用事務Id、資料、undolog來實現一致性檢視(read-view），但在生成一致性讀時，可重複讀(Repeatable read)和讀提交(read committed)的read view生成策略是不一致的：

  1. 在可重複讀級別下，整個事務存在期間都使用同一個檢視，只會獲取小於等於當前事務Id版本資料，如果資料被更新了，就通過undolog計算得到相應版本的資料，解決了不可重複讀問題。
  2. 在讀提交級別下，檢視在每條SQL執行期間建立，只獲取已提交的最新事務版本資料，所以每條SQL看到的資料可能都是不一致的，存在不可重複讀問題。

事務問題

  1. 在可重複讀級別下，每條SQL使用到的鎖需要等到事務提交或回滾之後才釋放，存在長事務時，可能會佔用的更多的資源，如鎖、undolog等，所以應避免長事務並且將資源佔用較多的SQL放在事務後程進行。
  2. 在讀提交級別下，每條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的應用

資料的更新/插入/刪除都包括對聚簇索引和普通索引的修改，這個過程中分為兩種情況：

  1. 如果對應資料頁(聚簇和二級)已經存在於記憶體：直接更新記憶體中的資料頁，記錄redo-log、binlog；
  2. 如果資料頁不在記憶體之中，對於唯一索引(包括聚簇)，需要將資料載入到記憶體中進行唯一性約束校驗，校驗通過再在記憶體中更新資料、記錄redolog；對於非唯一索引，直接將資料更改日誌儲存在change-buffer中(不寫磁碟)，記錄redolog、binlog。

對於一個唯一、非唯一索引皆存的表來說，對於資料的更新可能是不同步的，主鍵、唯一索引資料已經在記憶體中更改、而普通索引則是將更改先記錄在change buffer中，寫入change buffer時也會記錄redolog、binlog，確保崩潰後恢復。change buffer不僅在記憶體中，也會被寫入到系統表空間中，change buffer寫入磁碟的操作叫做purge。在特定時刻，change buffer中的操作會merge到原資料頁中：一是訪問這個資料頁時；二是後臺執行緒定期維護；三是在系統正常關閉的過程中。

相關文章