MySql架構原理(MySql從淺入深 一）

一.MySql體系架構

mysql Server架構專案項下大致可分為網路連線層、服務層、儲存引擎層和系統檔案層。

網路連線層

• 客戶端聯結器（Client Connectors）：提供與MySql伺服器建立的支援。目前幾乎支援所有主流的服務端程式設計技術，例如常見的Java、C、Python等。他們通過各自API技術與MySql建立連線

服務層

服務層是MySQL Server的核心，主要包括系統管理和控制工具、連線池、SQL介面、解析器、查詢優化器和快取六個部分。

• 連線池（Connection Pool）：負責儲存和管理客戶端與資料庫的連結，一個執行緒負責管理一個連結。
• 系統管理和控制工具（Management Servlces & Utilities）：例如備份恢復、安全管理、叢集管理等
• SQL介面（SQL Interface）: 用於接受客戶端傳送的各種命令，並且返回使用者需要返回的結果。如：DML、DDL、儲存過程、檢視、觸發器等。
• 解析器（Parser）：負責將請求的SQL解析成一個“解析樹”。然後根據一些MySQL規則進一步檢查解析樹是否合法。
• 查詢優化器（Optimizer）：當“解析樹”通過解析器語法檢查後，將交由優化器將其轉化成執行計劃，然後與儲存引擎互動。

select id, name, from user where age = 18;
//選取--》投影--》聯接策略
//（這裡涉及到mysql的儲存結構,我在後續的文章中會慢慢的更新的，大家可以自行百度瞭解學習，也可以持續關注我）
//（1）select先根據where語句進行選取，並不是查詢出全部的資料在過濾
//（2）select查詢根據uid和name進行屬性投影，並不是取出所有欄位
//（3）將前面選取和投影聯接起來最終生成的查詢結果

• 快取（Cache&Buffer）：快取機制是由一系列小快取組成的。比如表快取，記錄快取，許可權快取，引擎快取等。如果查詢快取有命中的查詢結果，查詢語句就可以直接去查詢快取中取資料（關於快取，包括頁分裂，預讀緩衝池汙染等知識，都會在後續的文章中慢慢更新，小夥伴們也可以自行百度學習）

儲存引擎層（Pluggable Storage Engines）

儲存引擎負責MySQL中資料的儲存與提取，與底層系統檔案進行互動。MySQL儲存引擎是外掛式的，伺服器中的查詢執行引擎通過介面與儲存引擎進行通訊，介面遮蔽了不同儲存引擎之間的差異。現在有很多種儲存引擎，各有各的特點，最常見的是MyISAM和InnoDB。（關於這兩種引擎，後續都會慢慢介紹說明的，小夥伴們也可以自信百度相關的文章提前學習）。

系統檔案層（File System）

該層負責將資料庫的資料和日誌儲存在檔案系統之上，並完成與儲存引擎的互動，是檔案的物理儲存層。主要包含日誌檔案，資料檔案，配置檔案，pid檔案，socket檔案等。

• 日誌檔案
  • 錯誤日誌（Error log）：預設開啟
    
  • 通用查詢日誌（General query log）
    
  • 二進位制日誌（binary log）:記錄了對MySQL資料庫執行的更改操作，並且記錄了語句的發生時間、執行時長；但是它不記錄select、show等不修改資料庫的SQL。主要用於資料庫恢復和主從複製。
    • 是否開啟：
    • 引數檢視
      
    • 檢視日誌檔案
      
  • 慢查詢日誌（Slow query log）：記錄所有執行時間超時的查詢SQL，預設是10秒。
    • show variables like ‘%slow_query%’; //是否開啟
    • show variables like ‘%long_query_time%’; //時長
      
• 配置檔案：用於存放MySql所有的配置資訊檔案，比如my.cnf、my.ini等。
  
• 資料檔案
  • db.opt檔案：記錄這個庫的預設使用字符集和校驗規則
  • sdi檔案：儲存與MyISAM引擎建立的表相關的後設資料（meta）資訊，包括表結構的定義資訊等，每一張表都會有一個sdi檔案。
  • 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。

二.MySql執行機制

圖片連結（我個人畫的圖）

• 建立連結（Connectors&Connection Pool），通過客戶端/伺服器通訊協議與MySQL建立連線。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：正在檢查資料表
  • Locked：被其他查詢鎖住了記錄
  • Sending Data：正在處理Select查詢，同時將結果傳送給客戶端
• Info：一般記錄執行緒執行的語句，預設顯示前100個字元。想檢視完整的使用show full processlist;
  

• ②查詢快取（Cache&Buffer），這是MySql的一個可優化查詢的地方，如果開啟了查詢快取且在查詢快取過程中查詢到完全相同的SQL語句，則將查詢結果直接返回給客戶端；如果沒有開啟查詢快取或者沒有查詢到完全相同的SQL語句則會由解析器進行語法語義解析，並生成“解析樹”。

以Select查詢為例

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

• ③解析器（Parser）將客戶端傳送的SQL進行語法解析，生成"解析樹"。前處理器根據一些MySQL規則進一步檢查“解析樹”是否合法，例如這裡將檢查資料表和資料列是否存在，還會解析名字和別名，看看它們是否有歧義，最後生成新的“解析樹”。
• ④查詢優化器（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)

• ⑤查詢執行引擎負責執行 SQL 語句，此時查詢執行引擎會根據 SQL 語句中表的儲存引擎型別，以及對應的API介面與底層儲存引擎快取或者物理檔案的互動，得到查詢結果並返回給客戶端。若開啟用查詢快取，這時會將SQL 語句和結果完整地儲存到查詢快取（Cache&Buffer）中，以後若有相同的 SQL 語句執行則直接返回結果。

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

三.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儲存引擎，因為這也是我們最常用的儲存引擎。那麼下面我們就來看看這兩者的區別。

• 事務和外來鍵

InnoDB	MyISAM
支援事務和外來鍵，具有安全性和完整性，適合大量insert或update操作	不支援事務和外來鍵，它提供高速儲存和檢索，適合大量的select查詢操作

• 鎖機制

InnoDB	MyISAM
支援行級鎖，鎖定指定記錄。基於索引來加鎖實現。	支援表級鎖，鎖定整張表。

• 索引結構

InnoDB	MyISAM
使用聚集索引（聚簇索引），索引和記錄在一起儲存，既快取索引，也快取記錄。	使用非聚集索引（非聚簇索引），索引和記錄分開。

• 併發處理能力

InnoDB	MyISAM
讀寫阻塞可以與隔離級別有關，可以採用多版本併發控制（MVCC）來支援高併發	使用表鎖，會導致寫操作併發率低，讀之間並不阻塞，讀寫阻塞。

• 儲存檔案

InnoDB	MyISAM
對應兩個檔案，一個.frm表結構檔案，一個.ibd資料檔案。InnoDB表最大支援64TB；	對應三個檔案，一個.frm表結構檔案，一個MYD表資料檔案，一個.MYI索引檔案。從MySQL5.0開始預設限制是256TB。

• 適用場景

InnoDB	MyISAM
需要事務支援（具有較好的事務特性）	不需要事務支援（不支援）
行級鎖定對高併發有很好的適應能力	併發相對較低（鎖定機制問題）
資料更新較為頻繁的場景	資料修改相對較少，以讀為主
資料一致性要求較高	資料一致性要求不高
硬體裝置記憶體較大，可以利用InnoDB較好的快取能力來提高記憶體利用率，減少磁碟IO

• 總結
  兩種引擎該如何選擇？
  • 是否需要事務？有，InnoDB
  • 是否存在併發修改？有，InnoDB
  • 是否追求快速查詢，且資料修改少？是，MyISAM
  • 在絕大多數情況下，推薦使用InnoDB
• 擴充套件（各個引擎的對比）
  

3.2 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根據狀態可以分為三種型別：
      • free 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可以設定為多個，這樣可以避免快取爭奪。

  • 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、自動擴充套件)。

    • 獨立表空間（File-Per-Table Tablespaces）

      預設開啟，獨立表空間是一個單表表空間，該表建立於自己的資料檔案中，而非建立於系統表空間中。當innodb_file_per_table選項開啟時，表將被建立於表空間中。否則，innodb將被建立於系統表空間中。每個表檔案表空間由一個.ibd資料檔案代表，該檔案預設被建立於資料庫目錄中。表空間的表檔案支援動態（dynamic）和壓縮（commpressed）行格式。

    • 通用表空間（General Tablespaces）

      通用表空間為通過create tablespace語法建立的共享表空間。通用表空間可以建立於mysql資料目錄外的其他表空間，其可以容納多張表，且其支援所有的行格式。

    CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //建立表空間ts1
    CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //將表新增到ts1表空間

    • 撤銷表空間（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兩種。sessiontemporary tablespaces 儲存的是使用者建立的臨時表和磁碟內部的臨時表。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中也分離出來了。

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秒的操作：

  • 重新整理日誌緩衝區，刷到磁碟
  • 合併寫緩衝區資料，根據IO讀寫壓力來決定是否操作
  • 重新整理髒頁資料到磁碟，根據髒頁比例達到75%才操作（innodb_max_dirty_pages_pct，innodb_io_capacity）

  每10秒的操作：

  • 重新整理髒頁資料到磁碟
  • 合併寫緩衝區資料
  • 重新整理日誌緩衝區
  • 刪除無用的undo頁

3.4 InnoDB資料檔案

• 一、InnoDB檔案儲存結構
  
  InnoDB資料檔案儲存結構：
  分為一個ibd資料檔案–>Segment（段）–>Extent（區）–>Page（頁）–>Row（行）
  • Tablesapce
    表空間，用於儲存多個ibd資料檔案，用於儲存表的記錄和索引。一個檔案包含多個段。
  • Segment
    段，用於管理多個Extent，分為資料段（Leaf node segment）、索引段（Non-leaf nodesegment）、回滾段（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。

Row Format	Compact Storage Characteristics	Enhanced Variable-Length Column Storage	Large Index Key Prefix Support	Compression Support	Supported Tablespace Types	Required File Format
REDUNDANT	NO	NO	NO	NO	system,file-per-table	Antelope or Barracuda
COMPACT	YES	NO	NO	NO	system,file-per-table	Antelope or Barracuda
DYNAMIC	YSE	YES	YES	NO	file-per-table	Barracuda
COMPRESSED	YES	YES	YES	YES	file-per-table	Barracuda

• 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和Bin Log

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

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秒執行OScache -> flush cache to disk 的操作。

一般建議選擇取值2，因為 MySQL 掛了資料沒有損失，整個伺服器掛了才會損失1秒的事務提交資料。

3.6.2 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檔案操作

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

擴充套件知識

頁分裂/頁合併（InnoDB引擎）

在InnoDB中，資料即索引（譯註：索引組織資料）。你可能聽過這種說法，但它具體是什麼樣的呢？
檔案表（File-Table）結構
假設你已經裝好了MySql 5.7版本，並且你建立了一個windmills庫（schema）和wmills表。在檔案目錄（通常是/var/lib/mysql/）你會看到以下內容：

• data/
  • windmills/
    • wmills.ibd
    • wmills.frm

這是因為從MySql 5.6版本開始innodb_file_per_table引數預設設定為1。該配置下你的每一個表都會單獨作為一個檔案儲存（如果有分割槽也可能有多個檔案）。
目前下要要注意的是這個叫wmills.ibd的檔案。這個檔案由多個段（segments）組成，每個段和一個索引相關。
檔案的結構是不會隨著資料行的刪除而變化的，但段則會跟著構成它的更小一級單位—區的變化而變化。區僅存在於段內，並且每個區都是固定的1MB大小（頁體積預設的情況下）。頁則是區的下一級構成單位，預設體積為16KB。
也就是 檔案結構→段→區→頁

擴充套件：擴充套件：1TB = 1024GB 1GB = 1024M 1MB = 1024KB 1KB = 1024B 1B = 8bit

按這樣算，一個區可以容納最多64個頁，一個頁可以容納2-N個行。行的數量取決於他的大小。由你的表結構定義。InnoDB要求，頁至少要有兩個行，因此可以算出行的大小最多為8000bytes。

根，分支與葉子
每個頁（邏輯上講即葉子節點）是包含了2-N行資料，根據主鍵排序。樹有著特殊的頁區管理不同的分支，即內部節點（INodes）。

上圖僅為示例，後文才是真實的結構描述。

ROOT NODE #3: 4 records, 68 bytes
 NODE POINTER RECORD ≥ (id=2) → #197
 INTERNAL NODE #197: 464 records, 7888 bytes
 NODE POINTER RECORD ≥ (id=2) → #5
 LEAF NODE #5: 57 records, 7524 bytes
 RECORD: (id=2) → (uuid="884e471c-0e82-11e7-8bf6-08002734ed50", millid=139, kwatts_s=1956, date="2017-05-01", location="For beauty's pattern to succeeding men.Yet do thy", active=1, time="2017-03-21 22:05:45", strrecordtype="Wit")
下面是表結構
CREATE TABLE `wmills` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `uuid` char(36) COLLATE utf8_bin NOT NULL,
  `millid` smallint(6) NOT NULL,
  `kwatts_s` int(11) NOT NULL,
  `date` date NOT NULL,
  `location` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `active` tinyint(2) NOT NULL DEFAULT '1',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `strrecordtype` char(3) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_millid` (`millid`)
) ENGINE=InnoDB;

所有的B樹都有著一個入口，也就是根節點，在上圖中#3就是根節點。根節點（頁）包含了如索引ID、INodes數量等資訊。INode頁包含了關於頁本身的資訊、值的範圍等。最後還有葉子節點，也就是我們資料實際所在的位置。在示例中，我們可以看到葉子節點#5有57行記錄，共7524bytes。在這行資訊後是具體的記錄，可以看到資料行的內容。
這裡想引出的概念是當你使用InnoDB管理表和行，InnoDB會將他們會以分支、頁和記錄的形式組織起來。InnoDB不是按行的來操作的，它可操作的最小粒度是頁，頁載入進記憶體後才會通過掃描頁來獲取行/記錄。

頁的內部原理

• 頁可以空或者填充滿（100%），行記錄會按照主鍵順序來排序。例如在使用AUTO_INCREMENT時，你會有順序的ID 1、2、3、4等。
• 頁還有另外一個重要的屬性：MERGE_THRESHOLD。該引數的預設值是50%頁的大小，它在InnoDB的合併操作中扮演了很重要的角色。
  • 當你插入資料時，如果資料（大小）能夠放的進頁中的話，那他們是按順序將頁填滿的。
  • 若當前頁滿，則下一行記錄會被插入下一頁（NEXT）中。
  • 根據B樹的特性，它可以自頂向下遍歷，但也可以在各葉子節點水平遍歷。因為每個葉子節點都有著一個指向包含下一條（順序）記錄的頁的指標。
  • 例如，頁#5有指向#6的指標，頁#6有指向前一頁（#5）的指標和後一頁（#7）的指標。
    這種機制下可以做到快速的順序掃描（如範圍掃描）。之前提到過，這就是當你基於自增主鍵進行插入的情況。但如果你不僅插入還進行刪除呢？

請看頁分裂和頁合併

頁合併

當你刪除了一行記錄，實際上記錄並沒有被物理刪除，記錄被標記（flaged）為刪除並且它的空間變得允許被其他記錄宣告使用。
當頁中刪除的記錄達到MERGE_THRESHOLD（預設頁體積的50%），InnoDB會開始尋找最靠近的頁（前或後）看看是否可以將兩個頁合併以優化空間使用。

在例項中，頁#6使用了不到一半的空間，頁#5又有足夠的刪除數量，現在同樣處於50%使用以下。從InnoDB的角度來看，它們能夠進行合併。

合併操作使得頁#5保留它之前的資料，並且容納來自頁#6的資料。頁#6變成一個空頁，可以接納新資料。

如果我們在UPDATE操作中讓頁中資料體積達到類似的闕值點，InnoDB也會進行一樣的操作。
規則就是：頁合併發生在刪除或更新操作中，關聯到當前頁。如果頁合併成功，在INFOMATION_SCHEMA.INNODB_METRICS中的index_page_merge_successful將會增加。

頁分裂

前面提到，頁可能填充至100%，在頁填滿了之後，下一頁會繼續接管新的記錄。但如果有下面這種情況呢？
頁#10沒有足夠的空間去容納新（或更新）的記錄。根據“下一頁”的邏輯，記錄應該由@11負責。然而：

頁#11頁同樣滿了，資料也不可能不按順序地插入。怎麼辦？
還記得之前說的連結串列嗎（譯註：指B+數的每一層都是雙向連結串列）？頁#10有指向頁#9和頁#11的指標。
InnoDB的做法是（簡化版）：

• 建立新頁
• 判斷當前頁（頁#10）可以從哪裡進行分裂（記錄行層面）
• 移動記錄行
• 重新定義頁之間的關係
  
  新的頁#12被建立：
  
  頁#11保持原樣，只有頁之間的關係發生了改變：
• 頁#10相鄰的前一頁為頁#9，後一頁為頁#12
• 頁#12相鄰的前一頁為頁#10，後一頁為頁#11
• 頁#11相鄰的前一頁為頁#10，後一頁為頁#13
  （這裡因為要畫圖的話，太大了，不好截圖，就不畫圖了，大家自己想象一下。）
  （譯註：頁#13可能本來就有，這裡意思為頁#10與頁#11之間插入了頁#12）

這樣B樹水平方向的一致性仍然滿足，因為滿足原定的順序排列邏輯。然而從物理儲存上講頁是亂序的，而且大概率會落到不同的區。

規律總結：頁分裂會發生在插入或更新，並且造成頁的錯位（dislocation，落入不同的區）

InnoDB用INFORMATION_SCHEMA.INNODB_METRICS表來跟蹤頁的分裂數。可以檢視其中的index_page_splits和index_page_reorg_attempts/successful統計。

一旦建立分裂的頁，唯一（譯註：實則仍有其他方法，見下文）將原先順序恢復的辦法就是新分裂出來的頁因為低於合併閾值（merge threshold）被刪掉。這時候InnoDB用頁合併將資料合併回來。

另一種方式就是用OPTIMIZE重新整理表。這可能是個很重量級和耗時的過程，但可能是唯一將大量分佈在不同區的頁理順的方法。

另一方面，要記住在合併和分裂的過程，InnoDB會在索引樹上加寫鎖（x-latch）。在操作頻繁的系統中這可能會是個隱患。它可能會導致索引的鎖爭用（index latch contention）。如果表中沒有合併和分裂（也就是寫操作）的操作，稱為“樂觀”更新，只需要使用讀鎖（S）。帶有合併也分裂操作則稱為“悲觀”更新，使用寫鎖（X）。