mysql資料庫相關流程圖/原理圖 1.mysql主從複製原理圖 mysql主從複製原理是大廠後端的高頻面試題，瞭解mysql主從複製原理非常有必要。 主從複製原理，簡言之，就三步曲，如下： 主資料庫有個bin-log二進位文件，紀錄了所有增刪改Sql語句。（binlog線程） 從資料庫把主資料庫的 ...
mysql資料庫相關流程圖/原理圖
1.mysql主從複製原理圖
mysql主從複製原理是大廠後端的高頻面試題，瞭解mysql主從複製原理非常有必要。
主從複製原理，簡言之，就三步曲，如下：
- 主資料庫有個bin-log二進位文件，紀錄了所有增刪改Sql語句。（binlog線程）
- 從資料庫把主資料庫的bin-log文件的sql語句複製過來。（io線程）
- 從資料庫的relay-log重做日誌文件中再執行一次這些sql語句。（Sql執行線程）
如下圖所示：
上圖主從複製分了五個步驟進行：
步驟一：主庫的更新事件(update、insert、delete)被寫到binlog
步驟二：從庫發起連接，連接到主庫。
步驟三：此時主庫創建一個binlog dump thread，把binlog的內容發送到從庫。
步驟四：從庫啟動之後，創建一個I/O線程，讀取主庫傳過來的binlog內容並寫入到relay log
步驟五：還會創建一個SQL線程，從relay log裡面讀取內容，從Exec_Master_Log_Pos位置開始執行讀取到的更新事件，將更新內容寫入到slave的db
2.Mysql邏輯架構圖
如果能在腦海中構建出MySql各組件之間如何協同工作的架構圖，就會有助於深入理解MySql伺服器
Mysql邏輯架構圖主要分三層：
1) 第一層負責連接處理，授權認證，安全等等
- 每個客戶端連接都會在伺服器進程中擁有一個線程，伺服器維護了一個線程池，因此不需要為每一個新建的連接創建或者銷毀線程。
- 當客戶端連接到Mysql伺服器時，伺服器對其進行認證，通過用戶名和密碼認證，也可以通過SSL證書進行認證。
- 一旦客戶端連接成功，伺服器會繼續驗證客戶端是否具有執行某個特定查詢的許可權。
2）第二層負責編譯並優化SQL
- 這一層包括查詢解析，分析，優化，緩存以及所有的的內置函數。
- 對於SELECT語句，在解析查詢前，伺服器會先檢查查詢緩存，如果能在其中找到對應的查詢結果，則無需再進行查詢解析、優化等過程，直接返回查詢結果。
- 所有跨存儲引擎的功能都在這一層實現:存儲過程，觸發器，視圖。
3）第三層是存儲引擎。
- 存儲引擎負責在MySQL中存儲數據、提取數據。
- 存儲引擎通過API與上層進行通信，這些API屏蔽了不同存儲引擎之間的差異，使得這些差異對上層查詢過程透明。
- 存儲引擎不會去解析SQL，不同存儲引擎之間也不會相互通信，而只是簡單地響應上層伺服器的請求。
3.InnoDb 邏輯存儲結構圖
從InnoDb 存儲引擎的邏輯存儲結構看，所有數據都被邏輯地存放在一個空間中，稱之為表空間（tablespace）。表空間又由段（segment），區（extent），頁（page）組成。頁在一些文檔中有時候也稱為塊（block）。 InnoDb 邏輯存儲結構圖如下：
表空間（tablespace）
- 表空間是Innodb存儲引擎邏輯的最高層，所有的數據都存放在表空間中。
- 預設情況下，Innodb存儲引擎有一個共用表空間ibdata1,即所有數據都存放在這個表空間中內。
- 如果啟用了innodb_file_per_table參數，需要註意的是每張表的表空間記憶體放的只是數據、索引、和插入緩衝Bitmap，其他類的數據，比如回滾(undo)信息、插入緩衝檢索頁、系統事物信息，二次寫緩衝等還是放在原來的共用表內的。
段（segment）
- 表空間由段組成，常見的段有數據段、索引段、回滾段等。
- InnoDB存儲引擎表是索引組織的，因此數據即索引，索引即數據。數據段即為B+樹的葉子結點，索引段即為B+樹的非索引結點。
- 在InnoDB存儲引擎中對段的管理都是由引擎自身所完成，DBA不能也沒必要對其進行控制。
區（extent）
- 區是由連續頁組成的空間，在任何情況下每個區的大小都為1MB。
- 為了保證區中頁的連續性，InnoDB存儲引擎一次從磁碟申請4~5個區。
- 預設情況下，InnoDB存儲引擎頁的大小為16KB，一個區中一共64個連續的區。
頁（page）
- 頁是InnoDB磁碟管理的最小單位。
- 在InnoDB存儲引擎中，預設每個頁的大小為16KB。
- 從InnoDB1.2.x版本開始，可以通過參數innodb_page_size將頁的大小設置為4K，8K，16K。
- InnoDB存儲引擎中，常見的頁類型有：數據頁，undo頁，系統頁，事務數據頁，插入緩衝點陣圖頁，插入緩衝空閑列表頁等。
4.Innodb頁結構相關示意圖
Innodb頁結構單體圖
InnoDB數據頁由以下7部分組成，如圖所示：
其中File Header、Page Header、File Trailer的大小是固定的，分別為38，56，8位元組，這些空間用來標記該頁的一些信息，如Checksum，數據頁所在B+樹索引的層數等。User Records、Free Space、Page Directory這些部分為實際的行記錄存儲空間，因此大小是動態的。
下邊我們用表格的方式來大致描述一下這7個部分：
記錄在頁中的存儲流程圖
每當我們插入一條記錄，都會從Free Space部分，也就是尚未使用的存儲空間中申請一個記錄大小的空間劃分到User Records部分，當Free Space部分的空間全部被User Records部分替代掉之後，也就意味著這個頁使用完了，如果還有新的記錄插入的話，就需要去申請新的頁了，這個過程的圖示如下：
不同Innodb頁構成的數據結構圖
一張表中可以有成千上萬條記錄，一個頁只有16KB，所以可能需要好多頁來存放數據。不同頁其實構成了一條雙向鏈表，File Header是InnoDB頁的第一部分，它的FIL_PAGE_PREV和FIL_PAGE_NEXT就分別代表本頁的上一個和下一個頁的頁號，即鏈表的上一個以及下一個節點指針。
5.Innodb索引結構圖
我們先看一份數據表樣本，假設Col1是主鍵，如下：
B+樹聚集索引結構圖
- 聚集索引就是以主鍵創建的索引
- 聚集索引在葉子節點存儲的是表中的數據
非聚集索引結構圖
假設索引列為Col3，索引結構圖如下：
- 非聚集索引就是以非主鍵創建的索引
- 非聚集索引在葉子節點存儲的是主鍵和索引列
- 使用非聚集索引查詢出數據時，拿到葉子上的主鍵再去查到想要查找的數據。(拿到主鍵再查找這個過程叫做回表)
- 假設所查詢的列，剛好都是索引對應的列，不用再回表查，那麼這個索引列，就叫覆蓋索引。
InnoDB 鎖類型思維導圖
加鎖機制
樂觀鎖與悲觀鎖是兩種併發控制的思想，可用於解決丟失更新問題。
樂觀鎖
- 每次去取數據，都很樂觀，覺得不會出現併發問題。
- 因此，訪問、處理數據每次都不上鎖。
- 但是在更新的時候，再根據版本號或時間戳判斷是否有衝突，有則處理，無則提交事務。
悲觀鎖
- 每次去取數據，很悲觀，都覺得會被別人修改，會有併發問題。
- 因此，訪問、處理數據前就加排他鎖。
- 在整個數據處理過程中鎖定數據，事務提交或回滾後才釋放鎖.
鎖粒度
- 表鎖： 開銷小，加鎖快；鎖定力度大，發生鎖衝突概率高，併發度最低;不會出現死鎖。
- 行鎖： 開銷大，加鎖慢；會出現死鎖；鎖定粒度小，發生鎖衝突的概率低，併發度高。
- 頁鎖： 開銷和加鎖速度介於表鎖和行鎖之間；會出現死鎖；鎖定粒度介於表鎖和行鎖之間，併發度一般
相容性
共用鎖：
- 又稱讀鎖（S鎖）。
- 一個事務獲取了共用鎖，其他事務可以獲取共用鎖，不能獲取排他鎖，其他事務可以進行讀操作，不能進行寫操作。
- SELECT ... LOCK IN SHARE MODE 顯示加共用鎖。
排他鎖：
- 又稱寫鎖（X鎖）。
- 如果事務T對數據A加上排他鎖後，則其他事務不能再對A加任任何類型的封鎖。獲准排他鎖的事務既能讀數據，又能修改數據。
- SELECT ... FOR UPDATE 顯示添加排他鎖。
鎖模式
- 記錄鎖： 在行相應的索引記錄上的鎖，鎖定一個行記錄
- gap鎖： 是在索引記錄間歇上的鎖,鎖定一個區間
- next-key鎖： 是記錄鎖和在此索引記錄之前的gap上的鎖的結合，鎖定行記錄+區間。
- 意向鎖 是為了支持多種粒度鎖同時存在；
