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到原資料頁中:一是訪問這個資料頁時;二是後臺執行緒定期維護;三是在系統正常關閉的過程中。