本篇文章給大家?guī)?lái)了關(guān)于mysql的相關(guān)知識(shí)，其中主要介紹了關(guān)于架構(gòu)原理的相關(guān)內(nèi)容，MySQL Server架構(gòu)自頂向下大致可以分網(wǎng)絡(luò)連接層、服務(wù)層、存儲(chǔ)引擎層和系統(tǒng)文件層，下面一起來(lái)看一下，希望對(duì)大家有幫助。

推薦學(xué)習(xí)：mysql視頻教程

Mysql 架構(gòu)原理

1、Mysql體系架構(gòu)

MySQL Server架構(gòu)自頂向下大致可以分網(wǎng)絡(luò)連接層、服務(wù)層、存儲(chǔ)引擎層和系統(tǒng)文件層。

網(wǎng)絡(luò)連接層

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

服務(wù)層（MySQL Server）

服務(wù)層是MySQL Server的核心，主要包含系統(tǒng)管理和控制工具、連接池、SQL接口、解析器、查詢優(yōu)化器和緩存六個(gè)部分。

• 連接池（Connection Pool）：負(fù)責(zé)存儲(chǔ)和管理客戶端與數(shù)據(jù)庫(kù)的連接，一個(gè)線程負(fù)責(zé)管理一個(gè)連接。

• 系統(tǒng)管理和控制工具（Management Services & Utilities）：例如備份恢復(fù)、安全管理、集群管理等

• SQL接口（SQL Interface）：用于接受客戶端發(fā)送的各種SQL命令，并且返回用戶需要查詢的結(jié)果。比如DML、DDL、存儲(chǔ)過(guò)程、視圖、觸發(fā)器等。

• 解析器（Parser）：負(fù)責(zé)將請(qǐng)求的SQL解析生成一個(gè)"解析樹(shù)"。然后根據(jù)一些MySQL規(guī)則進(jìn)一步檢查解析樹(shù)是否合法。

• 查詢優(yōu)化器（Optimizer）：當(dāng)"解析樹(shù)"通過(guò)解析器語(yǔ)法檢查后，將交由優(yōu)化器將其轉(zhuǎn)化成執(zhí)行計(jì)劃，然后與存儲(chǔ)引擎交互。

  select uid, name from user where gender = 1;

  選取 –》投影 –》聯(lián)接 策略

  1. select先根據(jù)where語(yǔ)句進(jìn)行選取，并不是查詢出全部數(shù)據(jù)再過(guò)濾；
  2. select查詢根據(jù)uid和name進(jìn)行屬性投影，并不是取出所有字段；
  3. 將前面選取和投影聯(lián)接起來(lái)最終生成查詢結(jié)果；

• 緩存（Cache&Buffer）： 緩存機(jī)制是由一系列小緩存組成的。比如表緩存，記錄緩存，權(quán)限緩存，引擎緩存等。如果查詢緩存有命中的查詢結(jié)果，查詢語(yǔ)句就可以直接去查詢緩存中取數(shù)據(jù)。

存儲(chǔ)引擎層（Pluggable Storage Engines）

• 存儲(chǔ)引擎負(fù)責(zé)MySQL中數(shù)據(jù)的存儲(chǔ)與提取，與底層系統(tǒng)文件進(jìn)行交互。MySQL存儲(chǔ)引擎是插件式的，服務(wù)器中的查詢執(zhí)行引擎通過(guò)接口與存儲(chǔ)引擎進(jìn)行通信，接口屏蔽了不同存儲(chǔ)引擎之間的差異 ?，F(xiàn)在有很多種存儲(chǔ)引擎，各有各的特點(diǎn)，最常見(jiàn)的是MyISAM和InnoDB。

系統(tǒng)文件層（File System）

該層負(fù)責(zé)將數(shù)據(jù)庫(kù)的數(shù)據(jù)和日志存儲(chǔ)在文件系統(tǒng)之上，并完成與存儲(chǔ)引擎的交互，是文件的物理存儲(chǔ)層。主要包含日志文件，數(shù)據(jù)文件，配置文件，pid 文件，socket 文件等。

• 日志文件
  • 錯(cuò)誤日志（Error log）
    • 默認(rèn)開(kāi)啟，show variables like ‘%log_error%’;
  • 通用查詢?nèi)罩荆℅eneral query log）
    • 記錄一般查詢語(yǔ)句，show variables like ‘%general%’;
  • 二進(jìn)制日志（binary log）
    • 記錄了對(duì)MySQL數(shù)據(jù)庫(kù)執(zhí)行的更改操作，并且記錄了語(yǔ)句的發(fā)生時(shí)間、執(zhí)行時(shí)長(zhǎng)；但是它不記錄select、show等不修改數(shù)據(jù)庫(kù)的SQL。主要用于數(shù)據(jù)庫(kù)恢復(fù)和主從復(fù)制。
    • show variables like ‘%log_bin%’; //是否開(kāi)啟
    • show variables like ‘%binlog%’; //參數(shù)查看
    • show binary logs;//查看日志文件
  • 慢查詢?nèi)罩荆⊿low query log）
    • 記錄所有執(zhí)行時(shí)間超時(shí)的查詢SQL，默認(rèn)是10秒。
    • show variables like ‘%slow_query%’; //是否開(kāi)啟
    • show variables like ‘%long_query_time%’; //時(shí)長(zhǎng)
• 配置文件
  • 用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。
• 數(shù)據(jù)文件
  • db.opt 文件：記錄這個(gè)庫(kù)的默認(rèn)使用的字符集和校驗(yàn)規(guī)則。
  • frm 文件：存儲(chǔ)與表相關(guān)的元數(shù)據(jù)（meta）信息，包括表結(jié)構(gòu)的定義信息等，每一張表都會(huì)有一個(gè)frm 文件。
  • MYD 文件：MyISAM 存儲(chǔ)引擎專用，存放 MyISAM 表的數(shù)據(jù)（data)，每一張表都會(huì)有一個(gè).MYD 文件。
  • MYI 文件：MyISAM 存儲(chǔ)引擎專用，存放 MyISAM 表的索引相關(guān)信息，每一張 MyISAM 表對(duì)應(yīng)一個(gè) .MYI文件。
  • ibd文件和 IBDATA 文件：存放 InnoDB 的數(shù)據(jù)文件（包括索引）。InnoDB 存儲(chǔ)引擎有兩種表空間方式：獨(dú)享表空間和共享表空間。獨(dú)享表空間使用 .ibd 文件來(lái)存放數(shù)據(jù)，且每一張InnoDB 表對(duì)應(yīng)一個(gè) .ibd 文件。共享表空間使用 .ibdata 文件，所有表共同使用一個(gè)（或多個(gè)，自行配置）.ibdata 文件。
  • ibdata1 文件：系統(tǒng)表空間數(shù)據(jù)文件，存儲(chǔ)表元數(shù)據(jù)、Undo日志等 。
  • ib_logfile0、ib_logfile1 文件：Redo log 日志文件。
• pid 文件
  • pid 文件是 mysqld 應(yīng)用程序在 Unix/Linux 環(huán)境下的一個(gè)進(jìn)程文件，和許多其他 Unix/Linux 服務(wù)端程序一樣，它存放著自己的進(jìn)程 id。
• socket 文件
  • socket 文件也是在 Unix/Linux 環(huán)境下才有的，用戶在 Unix/Linux 環(huán)境下客戶端連接可以不通過(guò)TCP/IP 網(wǎng)絡(luò)而直接使用 Unix Socket 來(lái)連接 MySQL。

2、MySQL運(yùn)行機(jī)制

1. 建立連接（Connectors&Connection Pool），通過(guò)客戶端/服務(wù)器通信協(xié)議與MySQL建立連接。MySQL 客戶端與服務(wù)端的通信方式是 “ 半雙工 ”。對(duì)于每一個(gè) MySQL 的連接，時(shí)刻都有一個(gè)線程狀態(tài)來(lái)標(biāo)識(shí)這個(gè)連接正在做什么。
   • 通訊機(jī)制：
     • 全雙工：能同時(shí)發(fā)送和接收數(shù)據(jù)，例如平時(shí)打電話。
     • 半雙工：指的某一時(shí)刻，要么發(fā)送數(shù)據(jù)，要么接收數(shù)據(jù)，不能同時(shí)。例如早期對(duì)講機(jī)
     • 單工：只能發(fā)送數(shù)據(jù)或只能接收數(shù)據(jù)。例如單行道；
   • 線程狀態(tài)：show processlist; //查看用戶正在運(yùn)行的線程信息，root用戶能查看所有線程，其他用戶只能看自己的；
     • id：線程ID，可以使用kill xx；
     • user：?jiǎn)?dòng)這個(gè)線程的用戶
     • Host：發(fā)送請(qǐng)求的客戶端的IP和端口號(hào)
     • db：當(dāng)前命令在哪個(gè)庫(kù)執(zhí)行
     • Command：該線程正在執(zhí)行的操作命令
       • Create DB：正在創(chuàng)建庫(kù)操作
       • Drop DB：正在刪除庫(kù)操作
       • Execute：正在執(zhí)行一個(gè)PreparedStatement
       • Close Stmt：正在關(guān)閉一個(gè)PreparedStatement
       • Query：正在執(zhí)行一個(gè)語(yǔ)句
       • Sleep：正在等待客戶端發(fā)送語(yǔ)句
       • Quit：正在退出
       • Shutdown：正在關(guān)閉服務(wù)器
     • Time：表示該線程處于當(dāng)前狀態(tài)的時(shí)間，單位是秒
     • State：線程狀態(tài)
       • Updating：正在搜索匹配記錄，進(jìn)行修改
       • Sleeping：正在等待客戶端發(fā)送新請(qǐng)求
       • Starting：正在執(zhí)行請(qǐng)求處理
       • Checking table：正在檢查數(shù)據(jù)表
       • Closing table : 正在將表中數(shù)據(jù)刷新到磁盤中
       • Locked：被其他查詢鎖住了記錄
       • Sending Data：正在處理Select查詢，同時(shí)將結(jié)果發(fā)送給客戶端
     • Info：一般記錄線程執(zhí)行的語(yǔ)句，默認(rèn)顯示前100個(gè)字符。想查看完整的使用show full processlist;
2. 查詢緩存（Cache&Buffer），這是MySQL的一個(gè)可優(yōu)化查詢的地方，如果開(kāi)啟了查詢緩存且在查詢緩存過(guò)程中查詢到完全相同的SQL語(yǔ)句，則將查詢結(jié)果直接返回給客戶端；如果沒(méi)有開(kāi)啟查詢緩存或者沒(méi)有查詢到完全相同的 SQL 語(yǔ)句則會(huì)由解析器進(jìn)行語(yǔ)法語(yǔ)義解析，并生成“解析樹(shù)”。
   • 緩存Select查詢的結(jié)果和SQL語(yǔ)句；
   • 執(zhí)行Select查詢時(shí)，先查詢緩存，判斷是否存在可用的記錄集，要求是否完全相同（包括參數(shù)值），這樣才會(huì)匹配緩存數(shù)據(jù)命中；
   • 即使開(kāi)啟查詢緩存，以下SQL也不能緩存：
     • 查詢語(yǔ)句使用SQL_NO_CACHE
     • 查詢的結(jié)果大于query_cache_limit設(shè)置
     • 查詢中有一些不確定的參數(shù)，比如now()
   • show variables like ‘%query_cache%’; //查看查詢緩存是否啟用，空間大小，限制等
   • show status like ‘Qcache%’; //查看更詳細(xì)的緩存參數(shù)，可用緩存空間，緩存塊，緩存多少等
3. 解析器（Parser）將客戶端發(fā)送的SQL進(jìn)行語(yǔ)法解析，生成"解析樹(shù)"。預(yù)處理器根據(jù)一些MySQL規(guī)則進(jìn)一步檢查“解析樹(shù)”是否合法，例如這里將檢查數(shù)據(jù)表和數(shù)據(jù)列是否存在，還會(huì)解析名字和別名，看看它們是否有歧義，最后生成新的“解析樹(shù)”。
4. 查詢優(yōu)化器（Optimizer）根據(jù)“解析樹(shù)”生成最優(yōu)的執(zhí)行計(jì)劃。MySQL使用很多優(yōu)化策略生成最優(yōu)的執(zhí)行計(jì)劃，可以分為兩類：靜態(tài)優(yōu)化（編譯時(shí)優(yōu)化）、動(dòng)態(tài)優(yōu)化（運(yùn)行時(shí)優(yōu)化）。
   • 等價(jià)變換策略
     • 5=5 and a>5 改成 a > 5
     • a < b and a=5 改成b>5 and a=5
     • 基于聯(lián)合索引，調(diào)整條件位置等
   • 優(yōu)化count、min、max等函數(shù)
     • InnoDB引擎min函數(shù)只需要找索引最左邊
     • InnoDB引擎max函數(shù)只需要找索引最右邊
     • MyISAM引擎count(*)，不需要計(jì)算，直接返回
   • 提前終止查詢
     • 使用了limit查詢，獲取limit所需的數(shù)據(jù)，就不在繼續(xù)遍歷后面數(shù)據(jù)
   • in的優(yōu)化
     • MySQL對(duì) in 查詢，會(huì)先進(jìn)行排序，再采用二分法查找數(shù)據(jù)。比如where id in (2,1,3)，變成 in (1,2,3)；
5. 查詢執(zhí)行引擎負(fù)責(zé)執(zhí)行 SQL 語(yǔ)句，此時(shí)查詢執(zhí)行引擎會(huì)根據(jù) SQL 語(yǔ)句中表的存儲(chǔ)引擎類型，以及對(duì)應(yīng)的API接口與底層存儲(chǔ)引擎緩存或者物理文件的交互，得到查詢結(jié)果并返回給客戶端。若開(kāi)啟用查詢緩存，這時(shí)會(huì)將SQL 語(yǔ)句和結(jié)果完整地保存到查詢緩存（Cache&Buffffer）中，以后若有相同的 SQL 語(yǔ)句執(zhí)行則直接返回結(jié)果。
   • 如果開(kāi)啟了查詢緩存，先將查詢結(jié)果做緩存操作
   • 返回結(jié)果過(guò)多，采用增量模式返回
   • 開(kāi)始執(zhí)行的時(shí)候，要先判斷一下你對(duì)這個(gè)表 T 有沒(méi)有執(zhí)行查詢的權(quán)限，如果沒(méi)有，就會(huì)返回沒(méi)有權(quán)限的錯(cuò)誤，（如果命中查詢緩存，會(huì)在查詢緩存返回結(jié)果的時(shí)候，做權(quán)限驗(yàn)證。查詢也會(huì)在優(yōu)化器之前調(diào)用 precheck 驗(yàn)證權(quán)限）。
   • 如果有權(quán)限，就打開(kāi)表繼續(xù)執(zhí)行。打開(kāi)表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的接口。執(zhí)行器的執(zhí)行流程是這樣的：
     • select * from test where age > 10;
     • 調(diào)用 InnoDB 引擎接口取這個(gè)表的第一行，判斷 age 值是不是 10，如果不是則跳過(guò)，如果是則將這行存在結(jié)果集中；
     • 調(diào)用引擎接口取“下一行”，重復(fù)相同的判斷邏輯，直到取到這個(gè)表的最后一行。
     • 執(zhí)行器將上述遍歷過(guò)程中所有滿足條件的行組成的記錄集作為結(jié)果集返回給客戶端。

3、Mysql存儲(chǔ)引擎

存儲(chǔ)引擎在MySQL的體系架構(gòu)中位于第三層，負(fù)責(zé)MySQL中的數(shù)據(jù)的存儲(chǔ)和提取，是與文件打交道的子系統(tǒng)，它是根據(jù)MySQL提供的文件訪問(wèn)層抽象接口定制的一種文件訪問(wèn)機(jī)制，這種機(jī)制就叫作存儲(chǔ)引擎。

使用show engines命令，就可以查看當(dāng)前數(shù)據(jù)庫(kù)支持的引擎信息。

在5.5版本之前默認(rèn)采用MyISAM存儲(chǔ)引擎，從5.5開(kāi)始采用InnoDB存儲(chǔ)引擎。

• InnoDB：支持事務(wù)，具有提交，回滾和崩潰恢復(fù)能力，事務(wù)安全；
• MyISAM：不支持事務(wù)和外鍵，訪問(wèn)速度快；
• Memory：利用內(nèi)存創(chuàng)建表，訪問(wèn)速度非常快，因?yàn)閿?shù)據(jù)在內(nèi)存，而且默認(rèn)使用Hash索引，但是一旦關(guān)閉，數(shù)據(jù)就會(huì)丟失；
• Archive：歸檔類型引擎，僅能支持insert和select語(yǔ)句；
• Csv：以CSV文件進(jìn)行數(shù)據(jù)存儲(chǔ)，由于文件限制，所有列必須強(qiáng)制指定not null，另外CSV引擎也不支持索引和分區(qū)，適合做數(shù)據(jù)交換的中間表；
• BlackHole: 黑洞，只進(jìn)不出，進(jìn)來(lái)消失，所有插入數(shù)據(jù)都不會(huì)保存；
• Federated：可以訪問(wèn)遠(yuǎn)端MySQL數(shù)據(jù)庫(kù)中的表。一個(gè)本地表，不保存數(shù)據(jù)，訪問(wèn)遠(yuǎn)程表內(nèi)容。
• MRG_MyISAM：一組MyISAM表的組合，這些MyISAM表必須結(jié)構(gòu)相同，Merge表本身沒(méi)有數(shù)據(jù)，對(duì)Merge操作可以對(duì)一組MyISAM表進(jìn)行操作；

InnoDB和MyISAM對(duì)比

• 事務(wù)和外鍵
  • InnoDB支持事務(wù)和外鍵，具有安全性和完整性，適合大量insert或update操作
  • MyISAM不支持事務(wù)和外鍵，它提供高速存儲(chǔ)和檢索，適合大量的select查詢操作
• 鎖機(jī)制
  • InnoDB支持行級(jí)鎖，鎖定指定記錄?；谒饕齺?lái)加鎖實(shí)現(xiàn)。
  • MyISAM支持表級(jí)鎖，鎖定整張表。
• 索引結(jié)構(gòu)
  • InnoDB使用聚集索引（聚簇索引），索引和記錄在一起存儲(chǔ)，既緩存索引，也緩存記錄。
  • MyISAM使用非聚集索引（非聚簇索引），索引和記錄分開(kāi)。
• 并發(fā)處理能力
  • MyISAM使用表鎖，會(huì)導(dǎo)致寫操作并發(fā)率低，讀之間并不阻塞，讀寫阻塞。
  • InnoDB讀寫阻塞可以與隔離級(jí)別有關(guān)，可以采用多版本并發(fā)控制（MVCC）來(lái)支持高并發(fā)
• 存儲(chǔ)文件
  • InnoDB表對(duì)應(yīng)兩個(gè)文件，一個(gè).frm表結(jié)構(gòu)文件，一個(gè).ibd數(shù)據(jù)文件。InnoDB表最大支持64TB；
  • MyISAM表對(duì)應(yīng)三個(gè)文件，一個(gè).frm表結(jié)構(gòu)文件，一個(gè)MYD表數(shù)據(jù)文件，一個(gè).MYI索引文件。從
    MySQL5.0開(kāi)始默認(rèn)限制是256TB。
• 適用場(chǎng)景
  • MyISAM
    • 不需要事務(wù)支持（不支持）
    • 并發(fā)相對(duì)較低（鎖定機(jī)制問(wèn)題）
    • 數(shù)據(jù)修改相對(duì)較少，以讀為主
    • 數(shù)據(jù)一致性要求不高
  • InnoDB
    • 需要事務(wù)支持（具有較好的事務(wù)特性）
    • 行級(jí)鎖定對(duì)高并發(fā)有很好的適應(yīng)能力
    • 數(shù)據(jù)更新較為頻繁的場(chǎng)景
    • 數(shù)據(jù)一致性要求較高
    • 硬件設(shè)備內(nèi)存較大，可以利用InnoDB較好的緩存能力來(lái)提高內(nèi)存利用率，減少磁盤IO
• 總結(jié)
  • 兩種引擎該如何選擇？
    • 是否需要事務(wù)？有，InnoDB
    • 是否存在并發(fā)修改？有，InnoDB
    • 是否追求快速查詢，且數(shù)據(jù)修改少？是，MyISAM
    • 在絕大多數(shù)情況下，推薦使用InnoDB

InnoDB存儲(chǔ)結(jié)構(gòu)

從MySQL 5.5版本開(kāi)始默認(rèn)使用InnoDB作為引擎，它擅長(zhǎng)處理事務(wù)，具有自動(dòng)崩潰恢復(fù)的特性。下面是官方的InnoDB引擎架構(gòu)圖，主要分為內(nèi)存結(jié)構(gòu)和磁盤結(jié)構(gòu)兩大部分。

InnoDB內(nèi)存結(jié)構(gòu)

內(nèi)存結(jié)構(gòu)主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大組件。

• Buffer Pool：緩沖池，簡(jiǎn)稱BP。BP以Page頁(yè)為單位，默認(rèn)大小16K，BP的底層采用鏈表數(shù)據(jù)結(jié)構(gòu)管理Page。在InnoDB訪問(wèn)表記錄和索引時(shí)會(huì)在Page頁(yè)中緩存，以后使用可以減少磁盤IO操作，提升效率。
  • Page管理機(jī)制
    • Page根據(jù)狀態(tài)可以分為三種類型：
      1. free page ： 空閑page，未被使用
      2. clean page：被使用page，數(shù)據(jù)沒(méi)有被修改過(guò)
      3. dirty page：臟頁(yè)，被使用page，數(shù)據(jù)被修改過(guò)，頁(yè)中數(shù)據(jù)和磁盤的數(shù)據(jù)產(chǎn)生了不一致
    • 針對(duì)上述三種page類型，InnoDB通過(guò)三種鏈表結(jié)構(gòu)來(lái)維護(hù)和管理：
      1. free list ：表示空閑緩沖區(qū)，管理free page
      2. flush list：表示需要刷新到磁盤的緩沖區(qū)，管理dirty page，內(nèi)部page按修改時(shí)間排序。臟頁(yè)即存在于flush鏈表，也在LRU鏈表中，但是兩種互不影響，LRU鏈表負(fù)責(zé)管理page的可用性和放，而flush鏈表負(fù)責(zé)管理臟頁(yè)的刷盤操作。
      3. lru list：表示正在使用的緩沖區(qū)，管理clean page和dirty page，緩沖區(qū)以midpoint為基點(diǎn)，前面鏈表稱為new列表區(qū)，存放經(jīng)常訪問(wèn)的數(shù)據(jù)，占63%；后面的鏈表稱為old列表區(qū)，存放使用較少數(shù)據(jù)，占37%。
  • 改進(jìn)型LRU算法維護(hù)
    • 普通LRU：末尾淘汰法，新數(shù)據(jù)從鏈表頭部加入，釋放空間時(shí)從末尾淘汰
    • 改性LRU：鏈表分為new和old兩個(gè)部分，加入元素時(shí)并不是從表頭插入，而是從中間midpoint位置插入，如果數(shù)據(jù)很快被訪問(wèn)，那么page就會(huì)向new列表頭部移動(dòng)，如果數(shù)據(jù)沒(méi)有被訪問(wèn)，會(huì)逐步向old尾部移動(dòng)，等待淘汰。
    • 每當(dāng)有新的page數(shù)據(jù)讀取到buffer pool時(shí)，InnoDb引擎會(huì)判斷是否有空閑頁(yè)，是否足夠，如果有就將free page從free list列表刪除，放入到LRU列表中。沒(méi)有空閑頁(yè)，就會(huì)根據(jù)LRU算法淘汰LRU鏈表默認(rèn)的頁(yè)，將內(nèi)存空間釋放分配給新的頁(yè)。
  • Buffer Pool配置參數(shù)
    • show variables like ‘%innodb_page_size%’; //查看page頁(yè)大小
    • show variables like ‘%innodb_old%’; //查看lru list中old列表參數(shù)
    • show variables like ‘%innodb_buffer%’; //查看buffffer pool參數(shù)
    • 建議：將innodb_buffer_pool_size設(shè)置為總內(nèi)存大小的60%-80%，innodb_buffer_pool_instances可以設(shè)置為多個(gè)，這樣可以避免緩存爭(zhēng)奪。
• Change Buffer：寫緩沖區(qū)，簡(jiǎn)稱CB。在進(jìn)行DML操作時(shí)，如果BP沒(méi)有其相應(yīng)的Page數(shù)據(jù)，并不會(huì)立刻將磁盤頁(yè)加載到緩沖池，而是在CB記錄緩沖變更，等未來(lái)數(shù)據(jù)被讀取時(shí)，再將數(shù)據(jù)合并恢復(fù)到BP中。
  • ChangeBuffer占用BufferPool空間，默認(rèn)占25%，最大允許占50%，可以根據(jù)讀寫業(yè)務(wù)量來(lái)進(jìn)行調(diào)整。參數(shù)innodb_change_buffer_max_size;
  • 當(dāng)更新一條記錄時(shí)，該記錄在BufferPool存在，直接在BufferPool修改，一次內(nèi)存操作。如果該記錄在BufferPool不存在（沒(méi)有命中），會(huì)直接在ChangeBuffer進(jìn)行一次內(nèi)存操作，不用再去磁盤查詢數(shù)據(jù)，避免一次磁盤IO。當(dāng)下次查詢記錄時(shí)，會(huì)先進(jìn)性磁盤讀取，然后再?gòu)腃hangeBuffer中讀取信息合并，最終載入BufferPool中。
  • 寫緩沖區(qū)，僅適用于非唯一普通索引頁(yè)
  • 如果在索引設(shè)置唯一性，在進(jìn)行修改時(shí)，InnoDB必須要做唯一性校驗(yàn)，因此必須查詢磁盤，做一次IO操作。會(huì)直接將記錄查詢到BufferPool中，然后在緩沖池修改，不會(huì)在ChangeBuffer操作。
• Adaptive Hash Index：自適應(yīng)哈希索引，用于優(yōu)化對(duì)BP數(shù)據(jù)的查詢。InnoDB存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表索引的查找，如果觀察到建立哈希索引可以帶來(lái)速度的提升，則建立哈希索引，所以稱之為自適應(yīng)。InnoDB存儲(chǔ)引擎會(huì)自動(dòng)根據(jù)訪問(wèn)的頻率和模式來(lái)為某些頁(yè)建立哈希索引。
• Log Buffer：日志緩沖區(qū)，用來(lái)保存要寫入磁盤上log文件（Redo/Undo）的數(shù)據(jù)，日志緩沖區(qū)的內(nèi)容定期刷新到磁盤log文件中。日志緩沖區(qū)滿時(shí)會(huì)自動(dòng)將其刷新到磁盤，當(dāng)遇到BLOB或多行更新的大事務(wù)操作時(shí)，增加日志緩沖區(qū)可以節(jié)省磁盤I/O。
  • LogBuffer主要是用于記錄InnoDB引擎日志，在DML操作時(shí)會(huì)產(chǎn)生Redo和Undo日志；
  • LogBuffer空間滿了，會(huì)自動(dòng)寫入磁盤?？梢酝ㄟ^(guò)將innodb_log_buffer_size參數(shù)調(diào)大，減少磁盤IO頻率；
  • innodb_flush_log_at_trx_commit參數(shù)控制日志刷新行為，默認(rèn)為1
    • 0 ： 每隔1秒寫日志文件和刷盤操作（寫日志文件LogBuffer –> OS cache，刷盤OScache –> 磁盤文件），最多丟失1秒數(shù)據(jù)
    • 1：事務(wù)提交，立刻寫日志文件和刷盤，數(shù)據(jù)不丟失，但是會(huì)頻繁IO操作
    • 2：事務(wù)提交，立刻寫日志文件，每隔1秒鐘進(jìn)行刷盤操作

InnoDB磁盤結(jié)構(gòu)

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

• 表空間（Tablespaces）：用于存儲(chǔ)表結(jié)構(gòu)和數(shù)據(jù)。表空間又分為系統(tǒng)表空間、獨(dú)立表空間、通用表空間、臨時(shí)表空間、Undo表空間等多種類型；

  • 系統(tǒng)表空間（The System Tablespace）

    • 包含InnoDB數(shù)據(jù)字典，Doublewrite Buffer，Change Buffer，Undo Logs的存儲(chǔ)區(qū)域。系統(tǒng)表空間也默認(rèn)包含任何用戶在系統(tǒng)表空間創(chuàng)建的表數(shù)據(jù)和索引數(shù)據(jù)。系統(tǒng)表空間是一個(gè)共享的表空間因?yàn)樗潜欢鄠€(gè)表共享的。該空間的數(shù)據(jù)文件通過(guò)參數(shù)innodb_data_file_path控制，默認(rèn)值是ibdata1:12M:autoextend(文件名為ibdata1、12MB、自動(dòng)擴(kuò)展)。

    • CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //創(chuàng)建表空 間ts1 CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //將表添加到ts1 表空間

  • 獨(dú)立表空間（File-Per-Table Tablespaces）

    • 默認(rèn)開(kāi)啟，獨(dú)立表空間是一個(gè)單表表空間，該表創(chuàng)建于自己的數(shù)據(jù)文件中，而非創(chuàng)建于系統(tǒng)表空間中。當(dāng)innodb_file_per_table選項(xiàng)開(kāi)啟時(shí)，表將被創(chuàng)建于表空間中。否則，innodb將被創(chuàng)建于系統(tǒng)表空間中。每個(gè)表文件表空間由一個(gè).ibd數(shù)據(jù)文件代表，該文件默認(rèn)被創(chuàng)建于數(shù)據(jù)庫(kù)目錄中。表空間的表文件支持動(dòng)態(tài)（dynamic）和壓縮（commpressed）行格式。

  • 通用表空間（General Tablespaces）

    • 通用表空間為通過(guò)create tablespace語(yǔ)法創(chuàng)建的共享表空間。通用表空間可以創(chuàng)建于mysql數(shù)據(jù)目錄外的其他表空間，其可以容納多張表，且其支持所有的行格式。

  • 撤銷表空間（Undo Tablespaces）

    • 撤銷表空間由一個(gè)或多個(gè)包含Undo日志文件組成。在MySQL 5.7版本之前Undo占用的是System Tablespace共享區(qū)，從5.7開(kāi)始將Undo從System Tablespace分離了出來(lái)。
    • InnoDB使用的undo表空間由innodb_undo_tablespaces配置選項(xiàng)控制，默認(rèn)為0。參數(shù)值為0表示使用系統(tǒng)表空間ibdata1;大于0表示使用undo表空間undo_001、undo_002等。

  • 臨時(shí)表空間（Temporary Tablespaces）

    • 分為session temporary tablespaces 和global temporary tablespace兩種：
      1. session temporary tablespaces 存儲(chǔ)的是用戶創(chuàng)建的臨時(shí)表和磁盤內(nèi)部的臨時(shí)表。
      2. global temporary tablespace儲(chǔ)存用戶臨時(shí)表的回滾段（rollback segments ）。mysql服務(wù)器正常關(guān)閉或異常終止時(shí)，臨時(shí)表空間將被移除，每次啟動(dòng)時(shí)會(huì)被重新創(chuàng)建。

• 數(shù)據(jù)字典（InnoDB Data Dictionary）

  • InnoDB數(shù)據(jù)字典由內(nèi)部系統(tǒng)表組成，這些表包含用于查找表、索引和表字段等對(duì)象的元數(shù)據(jù)。元數(shù)據(jù)物理上位于InnoDB系統(tǒng)表空間中。由于歷史原因，數(shù)據(jù)字典元數(shù)據(jù)在一定程度上與InnoDB表元數(shù)據(jù)文件（.frm文件）中存儲(chǔ)的信息重疊。

• 雙寫緩沖區(qū)（Doublewrite Buffer）

  • 位于系統(tǒng)表空間，是一個(gè)存儲(chǔ)區(qū)域。在BufferPage的page頁(yè)刷新到磁盤真正的位置前，會(huì)先將數(shù)據(jù)存在Doublewrite 緩沖區(qū)。如果在page頁(yè)寫入過(guò)程中出現(xiàn)操作系統(tǒng)、存儲(chǔ)子系統(tǒng)或mysqld進(jìn)程崩潰，InnoDB可以在崩潰恢復(fù)期間從Doublewrite 緩沖區(qū)中找到頁(yè)面的一個(gè)好備份。在大多數(shù)情況下，默認(rèn)情況下啟用雙寫緩沖區(qū)，要禁用Doublewrite 緩沖區(qū)，可以將innodb_doublewrite設(shè)置為0。使用Doublewrite 緩沖區(qū)時(shí)建議將innodb_flush_method設(shè)置為O_DIRECT。
    • MySQL的innodb_flush_method這個(gè)參數(shù)控制著innodb數(shù)據(jù)文件及redo log的打開(kāi)、刷寫模式。有三個(gè)值：fdatasync(默認(rèn))，O_DSYNC，O_DIRECT。設(shè)置O_DIRECT表示數(shù)據(jù)文件寫入操作會(huì)通知操作系統(tǒng)不要緩存數(shù)據(jù)，也不要用預(yù)讀，直接從InnodbBuffer寫到磁盤文件。
    • 默認(rèn)的fdatasync意思是先寫入操作系統(tǒng)緩存，然后再調(diào)用fsync()函數(shù)去異步刷數(shù)據(jù)文件與redo log的緩存信息。

• 重做日志（Redo Log）

  • 重做日志是一種基于磁盤的數(shù)據(jù)結(jié)構(gòu)，用于在崩潰恢復(fù)期間更正不完整事務(wù)寫入的數(shù)據(jù)。MySQL以循環(huán)方式寫入重做日志文件，記錄InnoDB中所有對(duì)Buffer Pool修改的日志。當(dāng)出現(xiàn)實(shí)例故障（像斷電），導(dǎo)致數(shù)據(jù)未能更新到數(shù)據(jù)文件，則數(shù)據(jù)庫(kù)重啟時(shí)須redo，重新把數(shù)據(jù)更新到數(shù)據(jù)文件。讀寫事務(wù)在執(zhí)行的過(guò)程中，都會(huì)不斷的產(chǎn)生redo log。默認(rèn)情況下，重做日志在磁盤上由兩個(gè)名為ib_logfile0和ib_logfile1的文件物理表示。

• 撤銷日志（Undo Logs）

  • 撤消日志是在事務(wù)開(kāi)始之前保存的被修改數(shù)據(jù)的備份，用于例外情況時(shí)回滾事務(wù)。撤消日志屬于邏輯日志，根據(jù)每行記錄進(jìn)行記錄。撤消日志存在于系統(tǒng)表空間、撤消表空間和臨時(shí)表空間中。

新版本結(jié)構(gòu)演變

• MySQL 5.7 版本
  • 將 Undo日志表空間從共享表空間 ibdata 文件中分離出來(lái)，可以在安裝 MySQL 時(shí)由用戶自行指定文件大小和數(shù)量。
  • 增加了 temporary 臨時(shí)表空間，里面存儲(chǔ)著臨時(shí)表或臨時(shí)查詢結(jié)果集的數(shù)據(jù)。
  • Buffer Pool 大小可以動(dòng)態(tài)修改，無(wú)需重啟數(shù)據(jù)庫(kù)實(shí)例。
• MySQL 8.0 版本
  • 將InnoDB表的數(shù)據(jù)字典和Undo都從共享表空間ibdata中徹底分離出來(lái)了，以前需要ibdata中數(shù)據(jù)字典與獨(dú)立表空間ibd文件中數(shù)據(jù)字典一致才行，8.0版本就不需要了。
  • temporary 臨時(shí)表空間也可以配置多個(gè)物理文件，而且均為 InnoDB 存儲(chǔ)引擎并能創(chuàng)建索引，這樣加快了處理的速度。
  • 用戶可以像 Oracle 數(shù)據(jù)庫(kù)那樣設(shè)置一些表空間，每個(gè)表空間對(duì)應(yīng)多個(gè)物理文件，每個(gè)表空間可以給多個(gè)表使用，但一個(gè)表只能存儲(chǔ)在一個(gè)表空間中。
  • 將Doublewrite Buffer從共享表空間ibdata中也分離出來(lái)了。

InnoDB線程模型

• IO Thread
  • 在InnoDB中使用了大量的AIO（Async IO）來(lái)做讀寫處理，這樣可以極大提高數(shù)據(jù)庫(kù)的性能。在
    InnoDB共有10個(gè)IO Thread，分別是4個(gè)write，4個(gè)read，1個(gè)insert buffer和 1個(gè)log thread。
    • read thread ： 負(fù)責(zé)讀取操作，將數(shù)據(jù)從磁盤加載到緩存page頁(yè)。4個(gè)
    • write thread：負(fù)責(zé)寫操作，將緩存臟頁(yè)刷新到磁盤。4個(gè)
    • log thread：負(fù)責(zé)將日志緩沖區(qū)內(nèi)容刷新到磁盤。1個(gè)
    • insert buffer thread ：負(fù)責(zé)將寫緩沖內(nèi)容刷新到磁盤。1個(gè)
• Purge Thread
  • 事務(wù)提交之后，其使用的undo日志將不再需要，因此需要Purge Thread回收已經(jīng)分配的undo頁(yè)。
  • show variables like ‘%innodb_purge_threads%’;
• Page Cleaner Thread
  • 作用是將臟數(shù)據(jù)刷新到磁盤，臟數(shù)據(jù)刷盤后相應(yīng)的redo log也就可以覆蓋，即可以同步數(shù)據(jù)，又能
    達(dá)到redo log循環(huán)使用的目的。會(huì)調(diào)用write thread線程處理。
  • show variables like ‘%innodb_page_cleaners%’;
• Master Thread
  • Master thread是InnoDB的主線程，負(fù)責(zé)調(diào)度其他各線程，優(yōu)先級(jí)最高。作用是將緩沖池中的數(shù)據(jù)異步刷新到磁盤 ，保證數(shù)據(jù)的一致性。包含：臟頁(yè)的刷新（page cleaner thread）、undo頁(yè)回收（purge thread）、redo日志刷新（log thread）、合并寫緩沖等。內(nèi)部有兩個(gè)主處理，分別是每隔1秒和10秒處理。
  • 每1秒的操作：
    • 刷新日志緩沖區(qū)，刷到磁盤
    • 合并寫緩沖區(qū)數(shù)據(jù)，根據(jù)IO讀寫壓力來(lái)決定是否操作
    • 刷新臟頁(yè)數(shù)據(jù)到磁盤，根據(jù)臟頁(yè)比例達(dá)到75%才操作（innodb_max_dirty_pages_pct，
      innodb_io_capacity）
  • 每10秒的操作：
    • 刷新臟頁(yè)數(shù)據(jù)到磁盤
    • 合并寫緩沖區(qū)數(shù)據(jù)
    • 刷新日志緩沖區(qū)
    • 刪除無(wú)用的undo頁(yè)

InnoDB數(shù)據(jù)文件

InnoDB文件存儲(chǔ)結(jié)構(gòu)

• InnoDB數(shù)據(jù)文件存儲(chǔ)結(jié)構(gòu)

  • 分為 ibd數(shù)據(jù)文件 –> Segment（段）–>Extent（區(qū)）–> Page（頁(yè)）–>Row（行）
    • Tablesapce表空間，用于存儲(chǔ)多個(gè)ibd數(shù)據(jù)文件，用于存儲(chǔ)表的記錄和索引。一個(gè)文件包含多個(gè)段。
    • Segment段，用于管理多個(gè)Extent，分為數(shù)據(jù)段（Leaf node segment）、索引段（Non-leaf node
      segment）、回滾段（Rollback segment）。一個(gè)表至少會(huì)有兩個(gè)segment，一個(gè)管理數(shù)據(jù)，一個(gè)管理索引。每多創(chuàng)建一個(gè)索引，會(huì)多兩個(gè)segment。
    • Extent區(qū)，一個(gè)區(qū)固定包含64個(gè)連續(xù)的頁(yè)，大小為1M。當(dāng)表空間不足，需要分配新的頁(yè)資源，不會(huì)
      一頁(yè)一頁(yè)分，直接分配一個(gè)區(qū)。
    • Page頁(yè)，用于存儲(chǔ)多個(gè)Row行記錄，大小為16K。包含很多種頁(yè)類型，比如數(shù)據(jù)頁(yè)，undo頁(yè)，系統(tǒng)頁(yè)，事務(wù)數(shù)據(jù)頁(yè)，大的BLOB對(duì)象頁(yè)。
    • Row行，包含了記錄的字段值，事務(wù)ID（Trx id）、滾動(dòng)指針（Roll pointer）、字段指針（Field
      pointers）等信息。

  • Page是文件最基本的單位，無(wú)論何種類型的page，都是由page header，page trailer和page body組成。如下圖所示

• InnoDB文件存儲(chǔ)格式

  • 通過(guò) SHOW TABLE STATUS 命令 查看

    

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

    • 通過(guò) information_schema 查看指定表的文件格式

      • select * from information_schema.innodb_sys_tables;

• File文件格式（File-Format）

  • 在早期的InnoDB版本中，文件格式只有一種，隨著InnoDB引擎的發(fā)展，出現(xiàn)了新文件格式，用于支持新的功能。目前InnoDB只支持兩種文件格式：Antelope 和 Barracuda。
    • Antelope: 先前未命名的，最原始的InnoDB文件格式，它支持兩種行格式：COMPACT和REDUNDANT，MySQL 5.6及其以前版本默認(rèn)格式為Antelope。
    • Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED和 DYNAMIC。
  • 通過(guò)innodb_file_format 配置參數(shù)可以設(shè)置InnoDB文件格式，之前默認(rèn)值為Antelope，5.7版本開(kāi)始改為Barracuda。

• Row行格式（Row_format）

• 表的行格式?jīng)Q定了它的行是如何物理存儲(chǔ)的，這反過(guò)來(lái)又會(huì)影響查詢和DML操作的性能。如果在單個(gè)page頁(yè)中容納