圖文詳解mysql架構原理

這篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於架構原理的相關內容，MySQL Server架構自頂向下大致可以分網絡連接層、服務層、儲存引擎層和系統檔案層，下面一起來看一下，希望對大家有幫助。

推薦學習：mysql影片教學

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、預存程序、視圖、觸發器等。

• 解析器（Parser）：負責將請求的SQL解析產生一個"解析樹"。然後根據一些MySQL規則進一步檢查解析樹是否合法。

• 查詢優化器（Optimizer）：當"解析樹"通過解析器語法檢查後，將交由優化器將其轉換成執行計劃，然後與儲存引擎互動。

  select uid, name from user where gender = 1;

  選取--》投影--》聯接策略

  1. select先根據where語句進行選取，並不是查詢出全部資料再過濾；
  2. select查詢根據uid和name進行屬性投影，並不是取出所有欄位；
  3. 將前面選取和投影聯接起來最終產生查詢結果;

• 快取（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 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)；
#查詢執行引擎負責執行SQL 語句，此時查詢執行引擎會根據SQL 語句中表格的儲存引擎類型，以及對應的API介面與底層儲存引擎快取或實體檔案的交互，得到查詢結果並傳回給客戶端。若開啟用查詢緩存，這時會將SQL 語句和結果完整地儲存到查詢快取（Cache&Buffffer）中，如果以後會有相同的 SQL 語句執行則直接傳回結果。
5. 如果開啟了查詢緩存，先將查詢結果做緩存操作
   • 返回結果過多，採用增量模式返回
   • 開始執行的時候，要先判斷一下你對這個表T 有沒有執行查詢的權限，如果沒有，就會回傳沒有權限的錯誤，（如果命中查詢緩存，會在查詢快取回傳結果的時候，做權限驗證。查詢也會在優化器之前調用precheck 驗證權限）。
   • 如果有權限，就開啟表格繼續執行。打開表格的時候，執行器就會根據表格的引擎定義，去使用這個引擎提供的介面。執行器的執行流程是這樣的：
   • select * from test where age > 10;
     • 呼叫InnoDB 引擎介面取這個表的第一行，判斷age 值是不是10，如果不是則跳過，如果是則將這行存在結果集中；
     • 呼叫引擎介面取“下一行”，重複相同的判斷邏輯，直到取到這個表的最後一行。
     • 執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集傳回給客戶端。

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表進行操作；

#InnoDB和MyISAM對比

• 事務和外鍵
  • InnoDB支援交易和外鍵，具有安全性和完整性，適合大量insert或update操作
  • MyISAM不支援事務和外鍵，它提供高速儲存和檢索，適合大量的select查詢操作
• 鎖定機制
  • #InnoDB支援行級鎖定，鎖定指定記錄。基於索引來加鎖實作。
  • MyISAM支援表級鎖，鎖定整張表。
• 索引結構
  • InnoDB使用聚集索引（叢集索引），索引和記錄在一起存儲，既快取索引，也快取記錄。
  • MyISAM使用非聚集索引（非叢集索引），索引和記錄分開。
• 並發處理能力
  • MyISAM使用表鎖，會導致寫入操作並發率低，讀取之間不會阻塞，讀寫阻塞。
  • InnoDB讀寫阻塞可以與隔離等級有關，可以採用多版本並發控制（MVCC）來支援高並發
• 儲存檔案
  • InnoDB表對應兩個文件，一個.frm表結構文件，一個.ibd資料檔。 InnoDB表最大支援64TB；
  • MyISAM表對應三個文件，一個.frm表結構文件，一個MYD表資料文件，一個.MYI索引文件。從
    MySQL5.0開始預設限制是256TB。
• 適用場景
  • MyISAM
    • 不需要交易支援（不支援）
    • 並發相對較低（鎖定機制問題）
    • 資料修改相對較少，以讀為主
    • 資料一致性要求不高
  • #InnoDB
    • 需要交易支持（具有較好的交易特性）
    • 行級鎖定對高並發有很好的適應能力
    • 資料更新較為頻繁的場景
    • 資料一致性要求較高
    • 硬體設備記憶體較大，可利用InnoDB較好的快取能力來提高記憶體使用率，減少磁碟IO
• 總結
  • 兩種引擎該如何選擇？
    • 是否需要事務？有，InnoDB
    • 是否存在並發修改？有，InnoDB
    • 是否追求快速查詢，且資料修改少？是，MyISAM
    • 在絕大多數情況下，建議使用InnoDB

#InnoDB儲存結構

##從MySQL 5.5版本開始預設使用InnoDB作為引擎，它擅長處理事務，具有自動崩潰復原的特性。下面是官方的InnoDB引擎架構圖，主要分為記憶體結構和磁碟結構兩大部分。

InnoDB記憶體結構

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

• Buffer Pool：緩衝池，簡稱BP。 BP以Page頁為單位，預設大小為16K，BP的底層採用鍊錶資料結構管理Page。在InnoDB存取表記錄和索引時會在Page頁中緩存，以後使用可以減少磁碟IO操作，提升效率。
  • Page管理機制
    • Page依狀態可以分為三種：
      1. free page ： 空閒page，未使用
      2. clean page：被使用page，資料沒有被修改過
      3. dirty page：髒頁，被使用page，資料被修改過，頁中資料和磁碟的資料產生了不一致
    • 針對上述三種page類型，InnoDB透過三種鍊錶結構來維護和管理：
      1. free list ：表示空閒緩衝區，管理free page
      2. flush list：表示需要刷新到磁碟的緩衝區，管理dirty page，內部page依修改時間排序。髒頁即存在於flush鍊錶，也在LRU鍊錶中，但是兩種互不影響，LRU鍊錶負責管理page的可用性和放，而flush鍊錶負責管理髒頁的刷盤操作。
      3. 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%'; //查看buffffer pool參數
    • 建議：將innodb_buffer_pool_size設定為總記憶體大小的60% -80%，innodb_buffer_pool_instances可以設定為多個，這樣可以避免快取爭奪。
• 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操作。
• Adaptive Hash Index：自適應雜湊索引，用於最佳化對BP資料的查詢。 InnoDB儲存引擎會監控對資料表索引的查找，如果觀察到建立雜湊索引可以帶來速度的提升，則建立哈希索引，所以稱為自適應。 InnoDB儲存引擎會自動根據存取的頻率和模式來為某些頁面建立雜湊索引。
• 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，刷盤OScache -->磁碟檔案），最多遺失1秒資料
    • 1：交易提交，立刻寫日誌檔案和刷盤，資料不遺失，但是會頻繁IO操作
    • 2：事務提交，立刻寫日誌文件，每隔1秒鐘進行刷盤操作

# InnoDB磁碟結構

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

• 表空間（Tablespaces）：用於儲存表格結構和資料。表空間又分為系統表空間、獨立表空間、通用表空間、暫存表空間、Undo表空間等多種型別；

  • 系統表空間（The System Tablespace）

    • 包含InnoDB資料字典，Doublewrite Buffer，Change Buffer，Undo Logs的儲存區域。系統表空間也預設包含任何使用者在系統表空間建立的表資料和索引資料。系統表空間是一個共享的表空間因為它是被多個表共享的。該空間的資料檔案透過參數innodb_data_file_path控制，預設值是ibdata1:12M:autoextend(檔案名稱為ibdata1、12MB、自動擴充)。

    • CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空 间ts1 CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1 表空间

  • 獨立表空間（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兩種：
      1. session temporary tablespaces 儲存的是使用者建立的暫存表和磁碟內部的暫存表。
      2. global temporary tablespace儲存使用者臨時表的回滾段（rollback segments ）。 mysql伺服器正常關閉或異常終止時，臨時表空間將會移除，每次啟動時會重新建立。

• 資料字典（InnoDB Data Dictionary）

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

• 雙寫緩衝區（Doublewrite Buffer）

  • 位於系統表空間，是一個儲存區域。在BufferPage的page頁刷新到磁碟真正的位置前，會先將資料存在Doublewrite 緩衝區。如果在page頁寫入過程中出現作業系統、儲存子系統或mysqld進程崩潰，InnoDB可以在崩潰復原期間從Doublewrite 緩衝區找到頁面的一個好備份。在大多數情況下，預設啟用雙寫緩衝區，要停用Doublewrite 緩衝區，可以將innodb_doublewrite設定為0。使用Doublewrite 緩衝區時建議將innodb_flush_method設定為O_DIRECT。
    • MySQL的innodb_flush_method這個參數控制著innodb資料檔及redo log的開啟、刷寫模式。有三個值：fdatasync(預設)，O_DSYNC，O_DIRECT。設定O_DIRECT表示資料檔案寫入操作會通知作業系統不要快取數據，也不要用預讀，直接從InnodbBuffer寫到磁碟檔案。
    • 預設的fdatasync意思是先寫入作業系統緩存，然後再呼叫fsync()函數去非同步刷資料檔與redo log的快取資訊。

• 重做日誌（Redo Log）

  • 重做日誌是一種基於磁碟的資料結構，用於在崩潰復原期間修正不完整交易寫入的資料。 MySQL以循環方式寫入重做日誌文件，記錄InnoDB中所有對Buffer Pool修改的日誌。當出現實例故障（像斷電），導致資料未能更新到資料文件，則資料庫重新啟動時須redo，重新把資料更新到資料檔案。讀寫事務在執行的過程中，都會不斷的產生redo log。預設情況下，重做日誌在磁碟上由兩個名為ib_logfile0和ib_logfile1的檔案實體表示。

• 撤銷日誌（Undo Logs）

  • #撤銷日誌是在交易開始之前保存的被修改資料的備份，用於例外情況時回滾事務。撤銷日誌屬於邏輯日誌，根據每行記錄進行記錄。撤銷日誌存在於系統表空間、撤銷表空間和臨時表空間。

新版本結構演進

• #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中也分離出來了。

InnoDB執行緒模型

• #IO Thread
  • 在InnoDB中使用了大量的AIO（Async IO）來做讀寫處理，這樣可以大幅提升資料庫的效能。在
    InnoDB共有10個IO Thread，分別是4個write，4個read，1個insert buffer和 1個log thread。
    • read thread ： 負責讀取操作，將資料從磁碟載入至快取page頁。 4個
    • write thread：負責寫入操作，將快取髒頁刷新到磁碟。 4個
    • log thread：負責將日誌緩衝區內容刷新到磁碟。 1個
    • insert buffer thread ：負責將寫入緩衝內容刷新到磁碟。 1個
• Purge Thread
  ##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秒的操作：
      
    刷新日誌緩衝區，刷到磁碟
  #合併寫緩衝區數據，根據IO讀寫壓力來決定是否要操作
  • 刷新髒頁資料到磁碟，依照髒頁比例達到75%才操作（innodb_max_dirty_pages_pct，
    • innodb_io_capacity）
    • #每10秒的動作：
    • #刷新髒頁資料到磁碟
    合併寫入緩衝區資料
  刷新日誌緩衝區

刪除無用的undo頁

InnoDB資料檔

• InnoDB檔案儲存結構
  #############InnoDB資料文件儲存結構######
  • 分为 ibd数据文件 --> Segment（段）–>Extent（区）–> Page（页）–>Row（行）    
    • Tablesapce表空间，用于存储多个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组成。如下图所示

• InnoDB文件存储格式

  • 通过 SHOW TABLE STATUS 命令 查看

    

    • 一般情况下，如果row_format为REDUNDANT、COMPACT，文件格式为Antelope；如果row_format为DYNAMIC和COMPRESSED，文件格式为Barracuda。

    • 通过 information_schema 查看指定表的文件格式

      • select * from information_schema.innodb_sys_tables;

• 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。

• 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=格式类型;

Undo Log

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%';

Undo Log作用

• 实现事务的原子性
  • Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

• 实现多版本并发控制（MVCC）

  • Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

• 事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中；

• 事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读；

• Redo Log 和 Binlog

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存储：

    • show variables like '%innodb_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秒的事务提交数
  据。

Binlog日志

• 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语句选择写入模式。

• Binlog 文件结构

  • MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。

  • Binlog文件中Log event结构如下图所示：

• 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。

• 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。

• Binlog文件操作

  • Binlog状态查看

    • show variables like 'log_bin';

  • 开启Binlog功能

    • 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 
      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" --stop- datetime="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文件会自动删除掉。

• Redo Log和 Binlog区别

  • Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。
  • Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
  • Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。
  • Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。

推荐学习：mysql视频教程

以上是圖文詳解mysql架構原理的詳細內容。更多資訊請關注PHP中文網其他相關文章！