第一部分 MySQL架構原理

第1節 MySQL體系架構

在這里插入圖片描述
MySQL Server架構自頂向下大致可以分網路連接層、服務層、存盤引擎層和系統檔案層，

一、網路連接層

客戶端連接器（Client Connectors）：提供與MySQL服務器建立的支持，目前幾乎支持所有主流的服務端編程技術，例如常見的 Java、C、Python、.NET等，它們通過各自API技術與MySQL建立連接，

二、服務層（MySQL Server）

??服務層是MySQL Server的核心，主要包含系統管理和控制工具、連接池、SQL介面、決議器、查詢優化器和快取六個部分，

連接池（Connection Pool）：負責存盤和管理客戶端與資料庫的連接，一個執行緒負責管理一個連接，
系統管理和控制工具（Management Services & Utilities）：例如備份恢復、安全管理、集群管理等
SQL介面（SQL Interface）：用于接受客戶端發送的各種SQL命令，并且回傳用戶需要查詢的結果，比如DML（增刪改）、DDL（資料定義語言DDL用來創建資料庫中的各種物件-----表、視圖、索引、同義詞、聚簇等如：CREATE TABLE/VIEW/INDEX/SYN/CLUSTER）、存盤程序、視圖、觸發器等，
決議器（Parser）：負責將請求的SQL決議生成一個"決議樹"，然后根據一些MySQL規則進一步檢查決議樹是否合法，
查詢優化器（Optimizer）：當“決議樹”通過決議器語法檢查后，將交由優化器將其轉化成執行計劃，然后與存盤引擎互動，
快取（Cache&Buffer）：快取機制是由一系列小快取組成的，比如表快取，記錄快取，權限快取，引擎快取等，如果查詢快取有命中的查詢結果，查詢陳述句就可以直接去查詢快取中取資料，

三、存盤引擎層（Pluggable Storage Engines）

??存盤引擎負責MySQL中資料的存盤與提取，與底層系統檔案進行互動，MySQL存盤引擎是插件式的，服務器中的查詢執行引擎通過介面與存盤引擎進行通信，介面屏蔽了不同存盤引擎之間的差異，現在有很多種存盤引擎，各有各的特點，最常見的是MyISAM和InnoDB，

四、系統檔案層（File System）

??該層負責將資料庫的資料和日志存盤在檔案系統之上，并完成與存盤引擎的互動，是檔案的物理存盤層，主要包含日志檔案，資料檔案，組態檔，pid 檔案，socket 檔案等，

日志檔案
- 錯誤日志（Error log）
  默認開啟，show variables like ‘%log_error%’
- 通用查詢日志（General query log）
  記錄一般查詢陳述句，show variables like ‘%general%’;
- 二進制日志（binary log）
  記錄了對MySQL資料庫執行的更改操作，并且記錄了陳述句的發生時間、執行時長；但是它不記錄select、show等不修改資料庫的SQL，主要用于資料庫恢復和主從復制，
  show variables like ‘%log_bin%’; //是否開啟
  show variables like ‘%binlog%’; //引數查看
  show binary logs;//查看日志檔案
- 慢查詢日志（Slow query log）
  記錄所有執行時間超時的查詢SQL，默認是10秒，
  show variables like ‘%slow_query%’; //是否開啟
  show variables like ‘%long_query_time%’; //時長
組態檔
用于存放MySQL所有的配置資訊檔案，比如my.cnf、my.ini等，
資料檔案
- db.opt 檔案：記錄這個庫的默認使用的字符集和校驗規則，
- frm 檔案：存盤與表相關的元資料（meta）資訊，包括表結構的定義資訊等，每一張表都會
  有一個frm 檔案，
- MYD 檔案：MyISAM 存盤引擎專用，存放 MyISAM 表的資料（data)，每一張表都會有一個
  .MYD 檔案，
- MYI 檔案：MyISAM 存盤引擎專用，存放 MyISAM 表的索引相關資訊，每一張 MyISAM 表對
  應一個 .MYI 檔案，
- ibd檔案和 IBDATA 檔案：存放 InnoDB 的資料檔案（包括索引），InnoDB 存盤引擎有兩種
  表空間方式：獨享表空間和共享表空間，獨享表空間使用 .ibd 檔案來存放資料，且每一張
  InnoDB 表對應一個 .ibd 檔案，共享表空間使用 .ibdata 檔案，所有表共同使用一個（或多
  個，自行配置）.ibdata 檔案，
- ibdata1 檔案：系統表空間資料檔案，存盤表元資料、Undo日志等，
- ib_logfile0、ib_logfile1 檔案：Redo log 日志檔案，
pid 檔案
pid 檔案是 mysqld 應用程式在 Unix/Linux 環境下的一個行程檔案，和許多其他 Unix/Linux 服務端程式一樣，它存放著自己的行程 id，
socket 檔案
socket 檔案也是在 Unix/Linux 環境下才有的，用戶在 Unix/Linux 環境下客戶端連接可以不通過TCP/IP 網路而直接使用 Unix Socket 來連接 MySQL，

第2節 MySQL運行機制

在這里插入圖片描述
1、建立連接（Connectors&Connection Pool）

??通過客戶端/服務器通信協議與MySQL建立連接，MySQL 客戶端與服務端的通信方式是 “ 半雙工 ”，對于每一個 MySQL 的連接，時刻都有一個執行緒狀態來標識這個連接正在做什么，

通訊機制：
- 全雙工：能同時發送和接收資料，例如平時打電話，
- 半雙工：指的某一時刻，要么發送資料，要么接收資料，不能同時，例如早期對講機
- 單工：只能發送資料或只能接收資料，例如單行道
執行緒狀態：
show processlist;//查看用戶正在運行的執行緒資訊，root用戶能查看所有執行緒，其他用戶只能看自己的
- id：執行緒ID，可以使用kill xx；
- user：啟動這個執行緒的用戶
- Host：發送請求的客戶端的IP和埠號
- db：當前命令在哪個庫執行
- Command：該執行緒正在執行的操作命令
  - Create DB：正在創建庫操作
  - Drop DB：正在洗掉庫操作
  - Execute：正在執行一個PreparedStatement
  - Close Stmt：正在關閉一個PreparedStatement
  - Query：正在執行一個陳述句
  - Sleep：正在等待客戶端發送陳述句
  - Quit：正在退出
  - Shutdown：正在關閉服務器
- Time：表示該執行緒處于當前狀態的時間，單位是秒
- State：執行緒狀態
  - Updating：正在搜索匹配記錄，進行修改
  - Sleeping：正在等待客戶端發送新請求
  - Starting：正在執行請求處理
  - Checking table：正在檢查資料表
  - Closing table : 正在將表中資料重繪到磁盤中
  - Locked：被其他查詢鎖住了記錄
  - Sending Data：正在處理Select查詢，同時將結果發送給客戶端
- Info：一般記錄執行緒執行的陳述句，默認顯示前100個字符，想查看完整的使用show full processlist;

2、查詢快取（Cache&Buffer）

??這是MySQL的一個可優化查詢的地方，如果開啟了查詢快取且在查詢快取程序中查詢到完全相同的SQL陳述句，則將查詢結果直接回傳給客戶端；如果沒有開啟查詢快取或者沒有查詢到完全相同的 SQL 陳述句則會由決議器進行語法語意決議，并生成“決議樹”，

快取Select查詢的結果和SQL陳述句
執行Select查詢時，先查詢快取，判斷是否存在可用的記錄集，要求是否完全相同（包括引數值），這樣才會匹配快取資料命中，
即使開啟查詢快取，以下SQL也不能快取
- 查詢陳述句使用SQL_NO_CACHE
- 查詢的結果大于query_cache_limit設定
- 查詢中有一些不確定的引數，比如now()
show variables like ‘%query_cache%’; //查看查詢快取是否啟用，空間大小，限制等
show status like ‘Qcache%’; //查看更詳細的快取引數，可用快取空間，快取塊，快取多少等

3、決議器（Parser）

??將客戶端發送的SQL進行語法決議，生成"決議樹"，前處理器根據一些MySQL規則進一步檢查“決議樹”是否合法，例如這里將檢查資料表和資料列是否存在，還會決議名字和別名，看看它們是否有歧義，最后生成新的“決議樹”，

4、查詢優化器（Optimizer）

??根據“決議樹”生成最優的執行計劃，MySQL使用很多優化策略生成最優的執行計劃，可以分為兩類：靜態優化（編譯時優化）、動態優化（運行時優化），

等價變換策略
- 5=5 and a>5 改成 a > 5
- a < b and a=5 改成b>5 and a=5
- 基于聯合索引，調整條件位置等
優化count、min、max等函式
- InnoDB引擎min函式只需要找索引最左邊
- InnoDB引擎max函式只需要找索引最右邊
- MyISAM引擎count(*)，不需要計算，直接回傳
提前終止查詢
- 使用了limit查詢，獲取limit所需的資料，就不在繼續遍歷后面資料
in的優化
- MySQL對in查詢，會先進行排序，再采用二分法查找資料，比如where id in (2,1,3)，變成 in (1,2,3)

5、查詢執行引擎負責執行 SQL 陳述句

??此時查詢執行引擎會根據 SQL 陳述句中表的存盤引擎型別，以及對應的API介面與底層存盤引擎快取或者物理檔案的互動，得到查詢結果并回傳給客戶端，若開啟用查詢快取，這時會將SQL 陳述句和結果完整地保存到查詢快取（Cache&Buffer）中，以后若有相同的 SQL 陳述句執行則直接回傳結果，

如果開啟了查詢快取，先將查詢結果做快取操作
回傳結果過多，采用增量模式回傳

第3節 MySQL存盤引擎

??存盤引擎在MySQL的體系架構中位于第三層，負責MySQL中的資料的存盤和提取，是與檔案打交道的子系統，它是根據MySQL提供的檔案訪問層抽象介面定制的一種檔案訪問機制，這種機制就叫作存盤引擎，

使用show engines命令，就可以查看當前資料庫支持的引擎資訊，

在這里插入圖片描述
在5.5版本之前默認采用MyISAM存盤引擎，從5.5開始采用InnoDB存盤引擎，

InnoDB：支持事務，具有提交，回滾和崩潰恢復能力，事務安全
MyISAM：不支持事務和外鍵，訪問速度快
Memory：利用記憶體創建表，訪問速度非常快，因為資料在記憶體，而且默認使用Hash索引，但是一旦關閉，資料就會丟失
Archive：歸檔型別引擎，僅能支持insert和select陳述句
Csv：以CSV檔案進行資料存盤，由于檔案限制，所有列必須強制指定not null，另外CSV引擎也不支持索引和磁區，適合做資料交換的中間表
BlackHole: 黑洞，只進不出，進來消失，所有插入資料都不會保存
Federated：可以訪問遠端MySQL資料庫中的表，一個本地表，不保存資料，訪問遠程表內容，
MRG_MyISAM：一組MyISAM表的組合，這些MyISAM表必須結構相同，Merge表本身沒有資料，對Merge操作可以對一組MyISAM表進行操作，

3.1 InnoDB和MyISAM對比

InnoDB和MyISAM是使用MySQL時最常用的兩種引擎型別，我們重點來看下兩者區別，

事務和外鍵
InnoDB支持事務和外鍵，具有安全性和完整性，適合大量insert或update操作
MyISAM不支持事務和外鍵，它提供高速存盤和檢索，適合大量的select查詢操作
鎖機制
InnoDB支持行級鎖，鎖定指定記錄，基于索引來加鎖實作，
MyISAM支持表級鎖，鎖定整張表，
索引結構
InnoDB使用聚集索引（聚簇索引），索引和記錄在一起存盤，既快取索引，也快取記錄，
MyISAM使用非聚集索引（非聚簇索引），索引和記錄分開，
并發處理能力
InnoDB讀寫阻塞可以與隔離級別有關，可以采用多版本并發控制（MVCC）來支持高并發
MyISAM使用表鎖，會導致寫操作并發率低，讀之間并不阻塞，讀寫阻塞，
存盤檔案
InnoDB表對應兩個檔案，一個.frm表結構檔案，一個.ibd資料檔案，InnoDB表最大支持64TB；
MyISAM表對應三個檔案，一個.frm表結構檔案，一個MYD表資料檔案，一個.MYI索引檔案，從MySQL5.0開始默認限制是256TB，

在這里插入圖片描述

適用場景
- MyISAM
  - 不需要事務支持（不支持）
  - 并發相對較低（鎖定機制問題）
  - 資料修改相對較少，以讀為主
  - 資料一致性要求不高
- InnoDB
  - 需要事務支持（具有較好的事務特性）
  - 行級鎖定對高并發有很好的適應能力
  - 資料更新較為頻繁的場景
  - 資料一致性要求較高
  - 硬體設備記憶體較大，可以利用InnoDB較好的快取能力來提高記憶體利用率，減少磁盤IO
總結
兩種引擎該如何選擇？
- 是否需要事務？有，InnoDB
- 是否存在并發修改？有，InnoDB
- 是否追求快速查詢，且資料修改少？是，MyISAM
- 在絕大多數情況下，推薦使用InnoDB

擴展資料：各個存盤引擎特性對比

在這里插入圖片描述

3.2 InnoDB存盤結構

??從MySQL 5.5版本開始默認使用InnoDB作為引擎，它擅長處理事務，具有自動崩潰恢復的特性，在日常開發中使用非常廣泛，下面是官方的InnoDB引擎架構圖，主要分為記憶體結構和磁盤結構兩大部分，
在這里插入圖片描述

3.2.1、InnoDB記憶體結構

記憶體結構主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大組件，

1、Buffer Pool：緩沖池

??簡稱BP，BP以Page頁為單位，默認大小16K，BP的底層采用鏈表資料結構管理Page，在InnoDB訪問表記錄和索引時會在Page頁中快取，以后使用可以減少磁盤IO操作，提升效率，

Page管理機制
Page根據狀態可以分為三種型別：
- page ：空閑page，未被使用
- clean page：被使用page，資料沒有被修改過
- dirty page：臟頁，被使用page，資料被修改過，頁中資料和磁盤的資料產生了不一致
  針對上述三種page型別，InnoDB通過三種鏈表結構來維護和管理
- free list ：表示空閑緩沖區，管理free page
- flush list：表示需要重繪到磁盤的緩沖區，管理dirty page，內部page按修改時間排序，臟頁即存在于flush鏈表，也在LRU鏈表中，但是兩種互不影響，LRU鏈表負責管理page的可用性和釋放，而flush鏈表負責管理臟頁的刷盤操作，
- lru list：表示正在使用的緩沖區，管理clean page和dirty page，緩沖區以midpoint為基點，前面鏈表稱為new串列區，存放經常訪問的資料，占63%；后面的鏈表稱為old串列區，存放使用較少資料，占37%，
- 改進型LRU演算法維護
  - 普通LRU：末尾淘汰法，新資料從鏈表頭部加入，釋放空間時從末尾淘汰
  - 改性LRU：鏈表分為new和old兩個部分，加入元素時并不是從表頭插入，而是從中間midpoint位置插入，如果資料很快被訪問，那么page就會向new串列頭部移動，如果資料沒有被訪問，會逐步向old尾部移動，等待淘汰，
    每當有新的page資料讀取到buffer pool時，InnoDb引擎會判斷是否有空閑頁，是否足夠，如果有就將free page從free list串列洗掉，放入到LRU串列中，沒有空閑頁，就會根據LRU演算法淘汰LRU鏈表默認的頁，將記憶體空間釋放分配給新的頁，
Buffer Pool配置引數
show variables like ‘%innodb_page_size%’; //查看page頁大小
show variables like ‘%innodb_old%’; //查看lru list中old串列引數
show variables like ‘%innodb_buffer%’; //查看buffer pool引數
建議：將innodb_buffer_pool_size設定為總記憶體大小的60%-80%，innodb_buffer_pool_instances可以設定為多個，這樣可以避免快取爭奪，

2、Change Buffer：寫緩沖區

??簡稱CB，在進行DML操作時，如果BP沒有其相應的Page資料，并不會立刻將磁盤頁加載到緩沖池，而是在CB記錄緩沖變更，等未來資料被讀取時，再將資料合并恢復到BP中，
??ChangeBuffer占用BufferPool空間，默認占25%，最大允許占50%，可以根據讀寫業務量來進行調整，引數innodb_change_buffer_max_size;
??當更新一條記錄時，該記錄在BufferPool存在，直接在BufferPool修改，一次記憶體操作，如果該記錄在BufferPool不存在（沒有命中），會直接在ChangeBuffer進行一次記憶體操作，不用再去磁盤查詢資料，避免一次磁盤IO，當下次查詢記錄時，會先進性磁盤讀取，然后再從ChangeBuffer中讀取資訊合并，最終載入BufferPool中，

寫緩沖區，僅適用于非唯一普通索引頁，為什么？

??如果在索引設定唯一性，在進行修改時，InnoDB必須要做唯一性校驗，因此必須查詢磁盤，做一次IO操作，會直接將記錄查詢到BufferPool中，然后在緩沖池修改，不會在ChangeBuffer操作，

3、Adaptive Hash Index

??自適應哈希索引，用于優化對BP資料的查詢，InnoDB存盤引擎會監控對表索引的查找，如果觀察到建立哈希索引可以帶來速度的提升，則建立哈希索引，所以稱之為自適應，InnoDB存盤引擎會自動根據訪問的頻率和模式來為某些頁建立哈希索引，

4、Log Buffer：日志緩沖區

??用來保存要寫入磁盤上log檔案（Redo/Undo）的資料，日志緩沖區的內容定期重繪到磁盤log檔案中，日志緩沖區滿時會自動將其重繪到磁盤，當遇到BLOB或多行更新的大事務操作時，增加日志緩沖區可以節省磁盤I/O，

??LogBuffer主要是用于記錄InnoDB引擎日志，在DML（增刪改）操作時會產生Redo和Undo日志，

??LogBuffer空間滿了，會自動寫入磁盤，可以通過將innodb_log_buffer_size引數調大，減少磁盤IO頻率

innodb_flush_log_at_trx_commit引數控制日志重繪行為，默認為1

0 ：每隔1秒寫日志檔案和刷盤操作（寫日志檔案LogBuffer–>OS cache，刷盤OS cache–>磁盤檔案），最多丟失1秒資料
1：事務提交，立刻寫日志檔案和刷盤，資料不丟失，但是會頻繁IO操作
2：事務提交，立刻寫日志檔案，每隔1秒鐘進行刷盤操作

3.2.2、InnoDB磁盤結構

??InnoDB磁盤主要包含Tablespaces，InnoDB Data Dictionary，Doublewrite Buffer、Redo Log和Undo Logs，

1、表空間（Tablespaces）

??用于存盤表結構和資料，表空間又分為系統表空間、獨立表空間、通用表空間、臨時表空間、Undo表空間等多種型別；

系統表空間（The System Tablespace）
包含InnoDB資料字典，Doublewrite Buffer，Change Buffer，Undo Logs的存盤區域，系統表空間也默認包含任何用戶在系統表空間創建的表資料和索引資料，系統表空間是一個共享的表空間因為它是被多個表共享的，該空間的資料檔案通過引數innodb_data_file_path控制，默認值是ibdata1:12M:autoextend(檔案名為ibdata1、12MB、自動擴展)，
獨立表空間（File-Per-Table Tablespaces）
默認開啟，獨立表空間是一個單表表空間，該表創建于自己的資料檔案中，而非創建于系統表空間中，當innodb_file_per_table選項開啟時，表將被創建于表空間中，否則，innodb將被創建于系統表空間中，每個表檔案表空間由一個.ibd資料檔案代表，該檔案默認被創建于資料庫目錄中，表空間的表檔案支持動態（dynamic）和壓縮（commpressed）行格式，
通用表空間（General Tablespaces）
通用表空間為通過create tablespace語法創建的共享表空間，通用表空間可以創建于mysql資料目錄外的其他表空間，其可以容納多張表，且其支持所有的行格式，
撤銷表空間（Undo Tablespaces）
撤銷表空間由一個或多個包含Undo日志檔案組成，在MySQL 5.7版本之前Undo占用的是System Tablespace共享區，從5.7開始將Undo從System Tablespace分離了出來，InnoDB使用的undo表空間由innodb_undo_tablespaces配置選項控制，默認為0，引數值為0表示使用系統表空間ibdata1;大于0表示使用undo表空間undo_001、undo_002等，
臨時表空間（Temporary Tablespaces）
分為session temporary tablespaces 和global temporary tablespace兩種，session temporary tablespaces 存盤的是用戶創建的臨時表和磁盤內部的臨時表，global temporary tablespace儲存用戶臨時表的回滾段（rollback segments ），mysql服務器正常關倍訓例外終止時，臨時表空間將被移除，每次啟動時會被重新創建，

2、資料字典（InnoDB Data Dictionary）

??InnoDB資料字典由內部系統表組成，這些表包含用于查找表、索引和表欄位等物件的元資料，元資料物理上位于InnoDB系統表空間中，由于歷史原因，資料字典元資料在一定程度上與InnoDB表元資料檔案（.frm檔案）中存盤的資訊重疊，

3、雙寫緩沖區（Doublewrite Buffer）

??位于系統表空間，是一個存盤區域，在BufferPage的page頁重繪到磁盤真正的位置前，會先將資料存在Doublewrite 緩沖區，如果在page頁寫入程序中出現作業系統、存盤子系統或mysqld行程崩潰，InnoDB可以在崩潰恢復期間從Doublewrite 緩沖區中找到頁面的一個好備份，在大多數情況下，默認情況下啟用雙寫緩沖區，要禁用Doublewrite 緩沖區，可以將innodb_doublewrite設定為0，使用Doublewrite 緩沖區時建議將innodb_flush_method設定為O_DIRECT，
在這里插入圖片描述
4、重做日志（Redo Log）

??重做日志是一種基于磁盤的資料結構，用于在崩潰恢復期間更正不完整事務寫入的資料，MySQL以回圈方式寫入重做日志檔案，記錄InnoDB中所有對Buffer Pool修改的日志，當出現實體故障（像斷電），導致資料未能更新到資料檔案，則資料庫重啟時須redo，重新把資料更新到資料檔案，讀寫事務在執行的程序中，都會不斷的產生redo log，默認情況下，重做日志在磁盤上由兩個名為ib_logfile0和ib_logfile1的檔案物理表示，

5、撤銷日志（Undo Logs）

??撤消日志是在事務開始之前保存的被修改資料的備份，用于例外情況時回滾事務，撤消日志屬于邏輯日志，根據每行記錄進行記錄，撤消日志存在于系統表空間、撤消表空間和臨時表空間中，

3.2.3、新版本結構演變

在這里插入圖片描述

MySQL 5.7 版本
- 將 Undo日志表空間從共享表空間 ibdata 檔案中分離出來，可以在安裝 MySQL 時由用戶自行指定檔案大小和數量，
- 增加了 temporary 臨時表空間，里面存盤著臨時表或臨時查詢結果集的資料，
- Buffer Pool 大小可以動態修改，無需重啟資料庫實體，
MySQL 8.0 版本
- 將InnoDB表的資料字典和Undo都從共享表空間ibdata中徹底分離出來了，以前需要ibdata中資料字典與獨立表空間ibd檔案中資料字典一致才行，8.0版本就不需要了，
- temporary 臨時表空間也可以配置多個物理檔案，而且均為 InnoDB 存盤引擎并能創建索引，這樣加快了處理的速度，
- 用戶可以像 Oracle 資料庫那樣設定一些表空間，每個表空間對應多個物理檔案，每個表空間可以給多個表使用，但一個表只能存盤在一個表空間中，
- 將Doublewrite Buffer從共享表空間ibdata中也分離出來了，

3.3 InnoDB執行緒模型

在這里插入圖片描述

IO Thread
在InnoDB中使用了大量的AIO（Async IO）來做讀寫處理，這樣可以極大提高資料庫的性能，在InnoDB1.0版本之前共有4個IO Thread，分別是write，read，insert buffer和log thread，后來版本將read thread和write thread分別增大到了4個，一共有10個了，
- read thread ：負責讀取操作，將資料從磁盤加載到快取page頁，4個
- write thread：負責寫操作，將快取臟頁重繪到磁盤，4個
- log thread：負責將日志緩沖區內容重繪到磁盤，1個
- insert buffer thread ：負責將寫緩沖內容重繪到磁盤，1個
Purge Thread
事務提交之后，其使用的undo日志將不再需要，因此需要Purge Thread回收已經分配的undo頁，
show variables like ‘%innodb_purge_threads%’;
Page Cleaner Thread
作用是將臟資料重繪到磁盤，臟資料刷盤后相應的redo log也就可以覆寫，即可以同步資料，又能達到redo log回圈使用的目的，會呼叫write thread執行緒處理，
show variables like ‘%innodb_page_cleaners%’;
Master Thread
Master thread是InnoDB的主執行緒，負責調度其他各執行緒，優先級最高，作用是將緩沖池中的資料異步重繪到磁盤，保證資料的一致性，包含：臟頁的重繪（page cleaner thread）、undo頁回收（purge thread）、redo日志重繪（log thread）、合并寫緩沖等，內部有兩個主處理，分別是每隔1秒和10秒處理，
每1秒的操作：
（1）重繪日志緩沖區，刷到磁盤
（2）合并寫緩沖區資料，根據IO讀寫壓力來決定是否操作
（3）重繪臟頁資料到磁盤，根據臟頁比例達到75%才操作（innodb_max_dirty_pages_pct，innodb_io_capacity）
每10秒的操作：
（1）重繪臟頁資料到磁盤
（2）合并寫緩沖區資料
（3）重繪日志緩沖區
（4）洗掉無用的undo頁

3.4 InnoDB資料檔案

1、InnoDB檔案存盤結構
在這里插入圖片描述
InnoDB資料檔案存盤結構：
分為一個ibd資料檔案–>Segment（段）–>Extent（區）–>Page（頁）–>Row（行）

Tablesapce
表空間，用于存盤多個ibd資料檔案，用于存盤表的記錄和索引，一個ibd資料檔案包含多個段，
Segment
段，用于管理多個Extent，分為資料段（Leaf node segment）、索引段（Non-leaf node segment）、回滾段（Rollback segment），一個表至少會有兩個segment，一個管理資料，一個管理索引，每多創建一個索引，會多兩個segment，
Extent
區，一個區固定包含64個連續的頁，大小為1M，當表空間不足，需要分配新的頁資源，不會一頁一頁分，直接分配一個區，
Page
頁，用于存盤多個Row行記錄，大小為16K，包含很多種頁型別，比如資料頁，undo頁，系統頁，事務資料頁，大的BLOB物件頁，
Row
行，包含了記錄的欄位值，事務ID（Trx id）、滾動指標（Roll pointer）、欄位指標（Field pointers）等資訊，

Page是檔案最基本的單位，無論何種型別的page，都是由page header，page trailer和page body組成，如下圖所示，
在這里插入圖片描述
2、InnoDB檔案存盤格式

??通過 SHOW TABLE STATUS \G； 命令
在這里插入圖片描述
??一般情況下，如果row_format為REDUNDANT、COMPACT，檔案格式為Antelope；如果row_format為DYNAMIC和COMPRESSED，檔案格式為Barracuda，

通過 information_schema 查看指定表的檔案格式

select * from information_schema.innodb_sys_tables;

3、File檔案格式（File-Format）

??在早期的InnoDB版本中，檔案格式只有一種，隨著InnoDB引擎的發展，出現了新檔案格式，用于支持新的功能，目前InnoDB只支持兩種檔案格式：Antelope 和 Barracuda，

Antelope: 先前未命名的，最原始的InnoDB檔案格式，它支持兩種行格式：COMPACT和REDUNDANT，MySQL 5.6及其以前版本默認格式為Antelope，
Barracuda: 新的檔案格式，它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED和 DYNAMIC，

??通過innodb_file_format 配置引數可以設定InnoDB檔案格式，之前默認值為Antelope，5.7版本開始改為Barracuda，

4、Row行格式（Row_format）

??表的行格式決定了它的行是如何物理存盤的，這反過來又會影響查詢和DML操作的性能，如果在單個page頁中容納更多行，查詢和索引查找可以更快地作業，緩沖池中所需的記憶體更少，寫入更新時所需的I/O更少，

InnoDB存盤引擎支持四種行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED，
在這里插入圖片描述
??DYNAMIC和COMPRESSED新格式引入的功能有：資料壓縮、增強型長列資料的頁外存盤和大索引前綴，

??每個表的資料分成若干頁來存盤，每個頁中采用B樹結構存盤；

??如果某些欄位資訊過長，無法存盤在B樹節點中，這時候會被單獨分配空間，此時被稱為溢位頁，該欄位被稱為頁外列，

REDUNDANT 行格式
使用REDUNDANT行格式，表會將變長列值的前768位元組存盤在B樹節點的索引記錄中，其余的存盤在溢位頁上，對于大于等于786位元組的固定長度欄位InnoDB會轉換為變長欄位，以便能夠在頁外存盤，
COMPACT 行格式
與REDUNDANT行格式相比，COMPACT行格式減少了約20%的行存盤空間，但代價是增加了某些操作的CPU使用量，如果系統負載是受快取命中率和磁盤速度限制，那么COMPACT格式可能更快，如果系統負載受到CPU速度的限制，那么COMPACT格式可能會慢一些，
DYNAMIC 行格式
使用DYNAMIC行格式，InnoDB會將表中長可變長度的列值完全存盤在頁外，而索引記錄只包含指向溢位頁的20位元組指標，大于或等于768位元組的固定長度欄位編碼為可變長度欄位，DYNAMIC行格式支持大索引前綴，最多可以為3072位元組，可通過innodb_large_prefix引數控制，
COMPRESSED 行格式
COMPRESSED行格式提供與DYNAMIC行格式相同的存盤特性和功能，但增加了對表和索引資料壓縮的支持，

??在創建表和索引時，檔案格式都被用于每個InnoDB表資料檔案（其名稱與*.ibd匹配），修改檔案格式的方法是重新創建表及其索引，最簡單方法是對要修改的每個表使用以下命令：

ALTER TABLE 表名 ROW_FORMAT=格式型別;

3.5 Undo Log

3.5.1 Undo Log介紹

??Undo：意為撤銷或取消，以撤銷操作為目的，回傳指定某個狀態的操作，

??Undo Log：資料庫事務開始之前，會將要修改的記錄存放到 Undo 日志里，當事務回滾時或者資料庫崩潰時，可以利用 Undo 日志，撤銷未提交事務對資料庫產生的影響，

??Undo Log產生和銷毀：Undo Log在事務開始前產生；事務在提交時，并不會立刻洗掉undo log，innodb會將該事務對應的undo log放入到洗掉串列中，后面會通過后臺執行緒purge thread進行回收處理，Undo Log屬于邏輯日志，記錄一個變化程序，例如執行一個delete，undolog會記錄一個insert；執行一個update，undolog會記錄一個相反的update，

??Undo Log存盤：undo log采用段的方式管理和記錄，在innodb資料檔案中包含一種rollback segment回滾段，內部包含1024個undo log segment，可以通過下面一組引數來控制Undo log存盤，

show variables like '%innodb_undo%';

3.5.2 Undo Log作用

實作事務的原子性
Undo Log 是為了實作事務的原子性而出現的產物，事務處理程序中，如果出現了錯誤或者用戶執行了ROLLBACK 陳述句，MySQL 可以利用 Undo Log 中的備份將資料恢復到事務開始之前的狀態，
實作多版本并發控制（MVCC）
Undo Log 在 MySQL InnoDB 存盤引擎中用來實作多版本并發控制，事務未提交之前，Undo Log保存了未提交之前的版本資料，Undo Log 中的資料可作為資料舊版本快照供其他并發事務進行快照讀，

事務A手動開啟事務，執行更新操作，首先會把更新命中的資料備份到 Undo Buffer 中，
事務B手動開啟事務，執行查詢操作，會讀取 Undo 日志資料回傳，進行快照讀

3.6 Redo Log和Binlog

??Redo Log和Binlog是MySQL日志系統中非常重要的兩種機制，也有很多相似之處，下面介紹下兩者細節和區別，

3.6.1 Redo Log日志

Redo Log介紹
Redo：顧名思義就是重做，以恢復操作為目的，在資料庫發生意外時重現操作，
Redo Log：指事務中修改的任何資料，將最新的資料備份存盤的位置（Redo Log），被稱為重做日志，
Redo Log 的生成和釋放：隨著事務操作的執行，就會生成Redo Log，在事務提交時會將產生Redo Log寫入Log Buffer，并不是隨著事務的提交就立刻寫入磁盤檔案，等事務操作的臟頁寫入到磁盤之后，Redo Log 的使命也就完成了，Redo Log占用的空間就可以重用（被覆寫寫入），
Redo Log作業原理
Redo Log 是為了實作事務的持久性而出現的產物，防止在發生故障的時間點，尚有臟頁未寫入表的 IBD 檔案中，在重啟 MySQL 服務的時候，根據 Redo Log 進行重做，從而達到事務的未入磁盤資料進行持久化這一特性，
Redo Log寫入機制
Redo Log 檔案內容是以順序回圈的方式寫入檔案，寫滿時則回溯到第一個檔案，進行覆寫寫，

如圖所示：
- write pos 是當前記錄的位置，一邊寫一邊后移，寫到最后一個檔案末尾后就回到 0 號檔案開頭；
- checkpoint 是當前要擦除的位置，也是往后推移并且回圈的，擦除記錄前要把記錄更新到資料檔案；

??write pos 和 checkpoint 之間還空著的部分，可以用來記錄新的操作，如果 write pos 追上checkpoint，表示寫滿，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把 checkpoint推進一下，

Redo Log相關配置引數
每個InnoDB存盤引擎至少有1個重做日志檔案組（group），每個檔案組至少有2個重做日志檔案，默認為ib_logfile0和ib_logfile1，可以通過下面一組引數控制Redo Log存盤：

Redo Buffer 持久化到 Redo Log 的策略，可通過Innodb_flush_log_at_trx_commit設定：
- 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丟失一秒內的事務數
  據，由后臺Master執行緒每隔 1秒執行一次操作，
- 1（默認值）：每次事務提交執行 Redo Buffer -> OS cache -> flush cache to disk，最安
  全，性能最差的方式，
- 2：每次事務提交執行 Redo Buffer -> OS cache，然后由后臺Master執行緒再每隔1秒執行OS
  cache -> flush cache to disk 的操作，

??一般建議選擇取值2，因為 MySQL 掛了資料沒有損失，整個服務器掛了才會損失1秒的事務提交資料，
在這里插入圖片描述

3.6.2 Binlog日志

（1）Binlog記錄模式

??Redo Log 是屬于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log（二進制日志），簡稱Binlog，Binlog是記錄所有資料庫表結構變更以及表資料修改的二進制日志，不會記錄SELECT和SHOW這類操作，Binlog日志是以事件形式記錄，還包含陳述句所執行的消耗時間，開啟Binlog日志有以下兩個最重要的使用場景，

主從復制：在主庫中開啟Binlog功能，這樣主庫就可以把Binlog傳遞給從庫，從庫拿到Binlog后實作資料恢復達到主從資料一致性，
資料恢復：通過mysqlbinlog工具來恢復資料，

??Binlog檔案名默認為“主機名_binlog-序列號”格式，例如oak_binlog-000001，也可以在組態檔中指定名稱，檔案記錄模式有STATEMENT、ROW和MIXED三種，具體含義如下，

ROW（row-based replication, RBR）：日志中會記錄每一行資料被修改的情況，然后在slave端對相同的資料進行修改，
優點：能清楚記錄每一個行資料的修改細節，能完全實作主從資料同步和資料的恢復，
缺點：批量操作，會產生大量的日志，尤其是alter table會讓日志暴漲，
STATMENT（statement-based replication, SBR）：每一條被修改資料的SQL都會記錄到master的Binlog中，slave在復制的時候SQL行程會決議成和原來master端執行過的相同的SQL再次執行，簡稱SQL陳述句復制，
優點：日志量小，減少磁盤IO，提升存盤和恢復速度
缺點：在某些情況下會導致主從資料不一致，比如last_insert_id()、now()等函式，
MIXED（mixed-based replication, MBR）：以上兩種模式的混合使用，一般會使用STATEMENT模式保存binlog，對于STATEMENT模式無法復制的操作使用ROW模式保存binlog，MySQL會根據執行的SQL陳述句選擇寫入模式，

（2）Binlog檔案結構

??MySQL的binlog檔案中記錄的是對資料庫的各種修改操作，用來表示修改操作的資料結構是Log event，不同的修改操作對應的不同的log event，比較常用的log event有：Query event、Row event、Xid event等，binlog檔案的內容就是各種Log event的集合，

Binlog檔案中Log event結構如下圖所示：

在這里插入圖片描述
（3）Binlog寫入機制

根據記錄模式和操作觸發event事件生成log event（事件觸發執行機制）
將事務執行程序中產生log event寫入緩沖區，每個事務執行緒都有一個緩沖區Log Event保存在一個binlog_cache_mngr資料結構中，在該結構中有兩個緩沖區，一個是stmt_cache，用于存放不支持事務的資訊；另一個是trx_cache，用于存放支持事務的資訊，
事務在提交階段會將產生的log event寫入到外部binlog檔案中，
不同事務以串行方式將log event寫入binlog檔案中，所以一個事務包含的log event資訊在binlog檔案中是連續的，中間不會插入其他事務的log event，

（4）Binlog檔案操作

Binlog狀態查看

show variables like 'log_bin';

開啟Binlog功能

mysql> set global log_bin=mysqllogbin;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

需要修改my.cnf或my.ini組態檔，在[mysqld]下面增加log_bin=mysql_bin_log，重啟MySQL服務，

# log-bin=ON
# log-bin-basename=mysqlbinlog
# binlog-format=ROW
# 上面三行等同于下面兩行
binlog-format=ROW
log-bin=mysqlbinlog

使用show binlog events命令

show binary logs; //等價于show master logs;
show master status;
show binlog events;
show binlog events in 'mysqlbinlog.000001';

使用mysqlbinlog 命令

mysqlbinlog "檔案名"
mysqlbinlog "檔案名" > "test.sql"

使用 binlog 恢復資料

//按指定時間恢復
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stopdatetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234
//按事件位置號恢復
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234

mysqldump：定期全部備份資料庫資料，mysqlbinlog可以做增量備份和恢復操作，

洗掉Binlog檔案

purge binary logs to 'mysqlbinlog.000001'; //洗掉指定檔案
purge binary logs before '2020-04-28 00:00:00'; //洗掉指定時間之前的檔案
reset master; //清除所有檔案

??可以通過設定expire_logs_days引數來啟動自動清理功能，默認值為0表示沒啟用，設定為1表示超出1天binlog檔案會自動洗掉掉，

（5）Redo Log和Binlog區別

Redo Log是屬于InnoDB引擎功能，Binlog是屬于MySQL Server自帶功能，并且是以二進制檔案記錄，
Redo Log屬于物理日志，記錄該資料頁更新狀態內容，Binlog是邏輯日志，記錄更新程序，
Redo Log日志是回圈寫，日志空間大小是固定，Binlog是追加寫入，寫完一個寫下一個，不會覆寫使用，
Redo Log作為服務器例外宕機后事務資料自動恢復使用，Binlog可以作為主從復制和資料恢復使用，Binlog沒有自動crash-safe（崩潰恢復）能力，

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/259698.html

標籤：其他

上一篇：NoSQL 資料庫案例實戰 --Redis 主從同步

下一篇：《從零開始帶你成為訊息中間件實戰高手》筆記四

MySQL高級系列（一）—— MySQL架構原理

第一部分 MySQL架構原理

第1節 MySQL體系架構

第2節 MySQL運行機制

第3節 MySQL存盤引擎

3.1 InnoDB和MyISAM對比

3.2 InnoDB存盤結構

3.2.1、InnoDB記憶體結構

3.2.2、InnoDB磁盤結構

3.2.3、新版本結構演變

3.3 InnoDB執行緒模型

3.4 InnoDB資料檔案

3.5 Undo Log

3.5.1 Undo Log介紹

3.5.2 Undo Log作用

3.6 Redo Log和Binlog

3.6.1 Redo Log日志

3.6.2 Binlog日志