值得收藏的：Mysql資料庫核心知識彙總

毫無疑問，MySQL是當下最流行的開源資料庫。憑藉強大的效能和易於使用性，它已被Google、Facebook、YouTube、百度、網易和新浪等大型網際網路公司所應用。更有統計，世界上一流的網際網路公司中，排名前20的有80%都是 MySQL 的忠實使用者。今天 陝西優就業小編就帶大家一起來學習MySQL資料庫的核心知識。

一、Mysql邏輯架構

Mysql邏輯架構主要分三層：

第一層：負責連線處理，授權認證，安全等等

1、每個客戶端連線都會在伺服器程式中擁有一個執行緒，伺服器維護了一個執行緒池，因此不需要為每一個新建的連線建立或者銷燬執行緒;

2、當客戶端連線到Mysql伺服器時，伺服器對其進行認證，透過使用者名稱和密碼認證，也可以透過SSL證書進行認證;

3、一旦客戶端連線成功，伺服器會繼續驗證客戶端是否具有執行某個特定查詢的許可權。

第二層：負責編譯並最佳化SQL

1、這一層包括查詢解析，分析，最佳化，快取以及所有的的內建函式;

2、對於SELECT語句，在解析查詢前，伺服器會先檢查查詢快取，如果能在其中找到對應的查詢結果，則無需再進行查詢解析、最佳化等過程，直接返回查詢結果;

3、所有跨儲存引擎的功能都在這一層實現:儲存過程，觸發器，檢視。

第三層：是儲存引擎

1、儲存引擎負責在MySQL中儲存資料、提取資料;

2、儲存引擎透過API與上層進行通訊，這些API遮蔽了不同儲存引擎之間的差異，使得這些差異對上層查詢過程透明;

3、儲存引擎不會去解析SQL，不同儲存引擎之間也不會相互通訊，而只是簡單地響應上層伺服器的請求。

二、主從複製

主從複製原理，簡言之，就三步曲：

1、主資料庫有個binlog二進位制檔案，紀錄了所有增刪改Sql語句。(binlog執行緒)

2、從資料庫把主資料庫的binlog檔案的sql語句複製過來。(io執行緒)

3、從資料庫的relay log重做日誌檔案中再執行一次這些sql語句。(Sql執行執行緒)

三、InnoDB檔案儲存結構

從物理意義上講，InnoDB表由共享表空間檔案(ibdata1)、獨佔表空間檔案(ibd)、表結構檔案(.frm)、以及日誌檔案(redo檔案等)組成。

四、表結構檔案

在MYSQL中建立任何一張資料表，在其資料目錄對應的資料庫目錄下都有對應表的.frm檔案,.frm檔案是用來儲存每個資料表的後設資料(meta)資訊，包括表結構的定義等，.frm檔案跟資料庫儲存引擎無關，也就是任何儲存引擎的資料表都必須有.frm檔案。

五、表空間結構

1、表空間(tablespace)

所有資料都放在表空間中。如果開啟了innodb_file_per_table選項，則InnoDB會為每張表開闢一個表空間。但是需要注意的是表空間存放的只是資料、索引和插入緩衝bitmap頁，其他資料比如undo資訊，插入緩衝索引頁，系統事務資訊，二次寫緩衝還是會放在原來的共享表空間內。

如果rollback後，共享表空間不會自動收縮，但是會判斷空間是否需要(比如undo空間)，如果不需要的話，會將這些空間標記為可用空間，供下次undo使用。

2、段(segment)

表空間由各個段組成，比如資料段，索引段，回滾段等。

3、區(extent)

區由連續的頁組成，在任何情況下區的大小都是1M。InnoDB儲存引擎一次從磁碟申請大概4-5個區(4-5M)。在預設情況下，頁的大小為16KB，即一個區中有大概64個連續的頁。

4、頁(page)

InnoDB磁碟管理的最小單位。B樹節點= 一個物理Page(16K)，資料按16KB切片為Page 並編號， 編號可對映到物理檔案偏移(16K * N)，B+樹葉子節點前後形成雙向連結串列。Page分為幾種型別，資料頁和索引頁就是其中最為重要的兩種型別。

六、緩衝池

InnoDB儲存引擎是基於磁碟儲存的，並將其中的記錄按照頁的方式進行管理，但是由於CPU速度和磁碟速度之間的鴻溝，基於磁碟的資料庫系統通常使用緩衝池記錄來提高資料庫的的整體效能。

在資料庫中進行讀取操作，首先將從磁碟中讀到的頁放在緩衝池中，下次再讀相同的頁中時，首先判斷該頁是否在緩衝池中。若在緩衝池中，稱該頁在緩衝池中被命中，直接讀取該頁。否則，讀取磁碟上的頁。

七、重做日誌

預設情況在資料庫資料資料夾下會有兩個檔案，ib_logfile0/ib_logfile1, 這就是重做日誌檔案，記錄了對於InnoDB儲存引擎的事務日誌。

每個Innodb儲存引擎至少有1個重做日誌檔案組，每個組至少包含2個重做日誌檔案(ib_logfile0,ib_logfile1)。

可以透過設定多個映象日誌組(mirrored log groups)，將不同組放到不同磁碟，提高重做日誌的高可用性。

日誌組中的檔案大小是一致的，以迴圈的方式執行。檔案1寫滿時，切換到檔案2，檔案2寫滿時，再次切換到檔案1.日誌組中的檔案大小是一致的，以迴圈的方式執行。檔案1寫滿時，切換到檔案2，檔案2寫滿時，再次切換到檔案1(從頭寫入)。

為了保證資料的安全性，事務進行中時會不斷的產生redo log，在事務提交時進行一次flush操作，儲存到磁碟中, redo log是按照順序寫入的，磁碟的順序讀寫的速度遠大於隨機讀寫。當資料庫或主機失效重啟時，會根據redo log進行資料的恢復，如果redo log中有事務提交，則進行事務提交修改資料。這樣實現了事務的原子性、一致性和永續性。

對於寫入重寫日誌檔案的操作不是直接寫，而是先寫入一個重做日誌緩衝(redo lopg buffer)中，然後按照一定的條件寫入日誌檔案。

當對應事務的髒頁寫入到磁碟之後，redo log的使命也就完成了，重做日誌佔用的空間就可以重用(被覆蓋)。

透過innodb_log_buffer_size可以配置重寫日誌緩衝的的大小。

從日誌緩衝寫入磁碟有兩個時間點：

1、主執行緒每秒都會將重做日誌緩衝寫入磁碟的重做日誌檔案，不論事務是否已經提交;

2、另外一個是由引數innodb_flush_log_at_trx_commit控制，表示在事務提交時，處理重做日誌;

引數innodb_flush_log_at_trx_commit可設的值有0、1、2。0代表當提交事務時，並不將事務的重做日誌寫入磁碟上的日誌檔案，而是等待主執行緒每秒的重新整理。而1和2不同的地方在於：1是在commit時將重做日誌緩衝同步寫到磁碟;2是重做日誌非同步寫到磁碟，即不能完全保證commit時肯定會寫入重做日誌檔案，只是有這個動作。

八、回滾日誌

除了重做記錄redo log外，當進行資料修改時還會記錄undo log，undo log用於資料的撤回操作，它記錄了修改的反向操作，比如，插入對應刪除，修改對應修改為原來的資料，透過undo log可以實現事務回滾，並且可以根據undo log回溯到某個特定的版本的資料，實現MVCC，也即非鎖定讀。

事務開始之前，將當前的版本生成undo log，undo 也會產生 redo 來保證undo log的可靠性，事務提交之後，undo log並不能立馬被刪除，而是放入待清理的連結串列，由purge執行緒判斷是否由其他事務在使用undo段中表的上一個事務之前的版本資訊，決定是否可以清理undo log的日誌空間。

預設情況下undo檔案是保持在共享表空間的，也即ibdatafile檔案中，當資料庫中發生一些大的事務性操作的時候，要生成大量的undo資訊，全部儲存在共享表空間中的。因此共享表空間可能會變的很大，預設情況下，也就是undo 日誌使用共享表空間的時候，被“撐大”的共享表空間是不會也不能自動收縮的。

九、ACID

ACID是事務的四大特性。

1、原子性(Atomicity)

一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作，這就是事務的原子性。

2、一致性(Consistency)

資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態。

3、隔離性(Isolation)

一個事務所做的修改在最終提交以前，對其他事務是不可見的。

4、永續性(Durability)

一旦事務提交，則其所做的修改不會永久儲存到資料庫

十、事務的隔離性問題

如果不考慮事務的隔離性，會出現以下問題：

1、髒讀

一個事務在更新一條記錄，未提交前，第二個事務讀到了第一個事務更新後的記錄，那麼第二個事務就讀到了髒資料，會產生對第一個未提交資料的依賴。一旦第一個事務回滾，那麼第二個事務讀到的資料，將是錯誤的髒資料。

2、幻讀

一個事務按相同的查詢條件查詢之前檢索過的資料，確發現檢索出來的結果集條數變多或者減少(由其他事務插入、刪除的)，類似產生幻覺。

3、不可重複讀(虛讀)

一個事務在讀取某些資料後的一段時間後，再次讀取這個資料，發現其讀取出來的資料內容已經發生了改變，就是不可重複讀。

幻讀和不可重複讀的區別在於幻讀是資料條數發生了變化(插入、刪除)，而不可衝突讀在於資料發生了更新，前後讀取的結果不一致。

十一、事務隔離級別

髒讀、不可重讀度、幻讀，其實都是資料庫的一致性問題，必須由一定的事務隔離機制來解決，InnoDB下的事務隔離級別有以下四種：

1、讀未提交(Read uncommitted)

一個事務可以讀取到另一個事務未提交的修改。這會帶來髒讀、幻讀、不可重複讀問題。(基本沒用)

2、讀已提交(RC, Read Commit)

一個事務只能讀取另一個事務已經提交的修改。其避免了髒讀，但仍然存在不可重複讀和幻讀問題。

3、可重複讀(RR, Repeatable Read)

同一個事務中多次讀取相同的資料返回的結果是一樣的。其避免了髒讀和不可重複讀問題，但幻讀依然存在。

4、序列化(Serializable)

事務序列執行。避免了以上所有問題。MySQL 預設的級別是:Repeatable read 可重複讀，級別越高，資料越安全，但效能越低。

十二、MVCC

MVCC(Mutil-Version Concurrency Control)，多版本併發控制，是為了查詢一些正在被另一個事務更新的行，並且可以看到它們被更新之前的值。這是一個可以用來增強併發性的強大的技術，因為這樣的一來的話查詢就不用等待另一個事務釋放鎖。

MVCC的實現是透過儲存資料在某個時間點的快照(redo log)來實現的。這意味著一個事務無論執行多長時間，在同一個事務裡能夠看到資料一致的檢視。根據事務開始的時間不同，同時也意味著在同一個時刻不同事務看到的相同表裡的資料可能是不同的。

在每一行資料中額外儲存兩個隱藏的列：當前行建立時的版本號和刪除時的版本號(可能為空)。這裡的版本號並不是實際的時間值，而是系統版本號。每開始新的事務，系統版本號都會自動遞增。事務開始時刻的系統版本號會作為事務的版本號，用來和查詢每行記錄的版本號進行比較。

每個事務又有自己的版本號，這樣事務內執行CRUD操作時，就透過版本號的比較來達到資料版本控制的目的。

預設的隔離級別(REPEATABLE READ)下，增刪查改變成了這樣：

SELECT：讀取建立版本小於或等於當前事務版本號，並且刪除版本為空或大於當前事務版本號的記錄。這樣可以保證在讀取之前記錄是存在的。

INSERT：將當前事務的版本號儲存至行的建立版本號

UPDATE：新插入一行，並以當前事務的版本號作為新行的建立版本號，同時將原記錄行的刪除版本號設定為當前事務版本號

DELETE：將當前事務的版本號儲存至行的刪除版本號

十三、InnoDB索引結構

Mysql索引用的B+樹作為資料結構;Mysql中B+Tree在經典B+Tree的基礎上進行了最佳化，增加了順序訪問指標。在B+Tree的每個葉子節點增加一個指向相鄰葉子節點的指標，就形成了帶有順序訪問指標的B+Tree。這樣就提高了區間訪問效能：如果要查詢key為從18到49的所有資料記錄，當找到18後，只需順著節點和指標順序遍歷就可以一次性訪問到所有資料節點，極大提到了區間查詢效率(無需返回上層父節點重複遍歷查詢減少IO操作)。

MyISAM & InnoDB 都使用B+Tree索引結構。但是底層索引儲存不同，MyISAM 採用非聚簇索引，而InnoDB採用聚簇索引。

聚簇索引： 索引 和 資料檔案為同一個檔案。

非聚簇索引： 索引 和 資料檔案分開的索引。

MyISAM索引原理：採用非聚簇索引-MyISAM myi索引檔案和myd資料檔案分離，索引檔案僅儲存資料記錄的指標地址。葉子節點data域儲存指向資料記錄的指標地址。

MyISAM索引按照B+Tree搜尋，如果指定的Key存在，則取出其data域的值，然後以data域值-資料指標地址去讀取相應資料記錄。輔助索引和主索引在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重複。

InnoDB索引採用聚簇索引，InnoDB資料&索引檔案為一個idb檔案，表資料檔案本身就是主索引，相鄰的索引臨近儲存。 葉節點data域儲存了完整的資料記錄(資料[除主鍵id外其他列data]+主索引[索引key:表主鍵id])。 葉子節點直接儲存資料記錄，以主鍵id為key,葉子節點中直接儲存資料記錄。(底層儲存結構:** frm -表定義、 ibd: innoDB資料&索引檔案)

由於InnoDB採用聚簇索引結構儲存，索引InnoDB的資料檔案需要按照主鍵聚集，因此InnoDB要求表必須有主鍵(MyISAM可以沒有)。如果沒有指定mysql會自動選擇一個可以唯一表示資料記錄的列作為主鍵，如果不存在這樣的列，mysql自動為InnoDB表生成一個隱含欄位(6個位元組長整型)作為主鍵。 InnoDB的所有輔助索引都引用資料記錄的主鍵作為data域。

十四、InnoDB鎖型別

1、加鎖機制

樂觀鎖與悲觀鎖是兩種併發控制的思想，可用於解決丟失更新問題。

2、樂觀鎖

每次去取資料，都很樂觀，覺得不會出現併發問題。因此，訪問、處理資料每次都不上鎖。但是在更新的時候，再根據版本號或時間戳判斷是否有衝突，有則處理，無則提交事務。

3、悲觀鎖

每次去取資料，很悲觀，都覺得會被別人修改，會有併發問題。因此，訪問、處理資料前就加排他鎖。在整個資料處理過程中鎖定資料，事務提交或回滾後才釋放鎖。

4、鎖粒度

表鎖：開銷小，加鎖快;鎖定力度大，發生鎖衝突機率高，併發度最低;不會出現死鎖。

行鎖：開銷大，加鎖慢;會出現死鎖;鎖定粒度小，發生鎖衝突的機率低，併發度高。

頁鎖：開銷和加鎖速度介於表鎖和行鎖之間;會出現死鎖;鎖定粒度介於表鎖和行鎖之間，併發度一般。

5、相容性

01、共享鎖

又稱讀鎖(S鎖)。一個事務獲取了共享鎖，其他事務可以獲取共享鎖，不能獲取排他鎖，其他事務可以進行讀操作，不能進行寫操作。SELECT … LOCK IN SHARE MODE 顯示加共享鎖。

02、排他鎖

又稱寫鎖(X鎖)。如果事務T對資料A加上排他鎖後，則其他事務不能再對A加任任何型別的封鎖。獲准排他鎖的事務既能讀資料，又能修改資料。SELECT … FOR UPDATE 顯示新增排他鎖。

6、鎖模式

記錄鎖：在行相應的索引記錄上的鎖，鎖定一個行記錄。

gap鎖：是在索引記錄間歇上的鎖,鎖定一個區間。

next-key鎖：是記錄鎖和在此索引記錄之前的gap上的鎖的結合，鎖定行記錄+區間。

意向鎖：是為了支援多種粒度鎖同時存在。