第一章 MySQL 體系架構(gòu)和存儲(chǔ)引擎

mysql是數(shù)據(jù)庫(kù)也是數(shù)據(jù)庫(kù)實(shí)例

mysql ?是一個(gè)單進(jìn)程多線程架構(gòu)的數(shù)據(jù)庫(kù) ?daemon 守護(hù)進(jìn)程

當(dāng)啟動(dòng)實(shí)例時(shí)，MySQL數(shù)據(jù)庫(kù)會(huì)去讀取配置文件，根據(jù)配置文件的參數(shù)來(lái)啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例。

用以下命令可以查看當(dāng)Mysql 數(shù)據(jù)庫(kù)實(shí)例啟動(dòng)時(shí)，會(huì)在哪些位置查找配置文件。

mysql —help | grep my.cnf

mysql由一下幾部分組成：

連接池組件

管理服務(wù)和工具組件

sql接口組件

查詢分析器組件

優(yōu)化器組件

緩沖（cache）組件

插件式存儲(chǔ)引擎

物理文件

最大的特點(diǎn)就是其插件式的表存儲(chǔ)引擎。存儲(chǔ)引擎是基于表的，而不是數(shù)據(jù)庫(kù)。

mysql獨(dú)有的插件式體系架構(gòu)，存儲(chǔ)引擎的是mysql區(qū)別于其他數(shù)據(jù)庫(kù)的一個(gè)重要的特性。存儲(chǔ)引擎的好處是，每個(gè)存儲(chǔ)引擎都有各自的特點(diǎn)，能夠根據(jù)具體的應(yīng)用建立不同存儲(chǔ)引擎表。

MySql 存儲(chǔ)引擎的改進(jìn)http:/code.google.com.p/mysql-heap-dynamic-rows/

MySQL 數(shù)據(jù)庫(kù)開源特性，存儲(chǔ)引擎可以分為MySql官方存儲(chǔ)引擎和第三方存儲(chǔ)引擎。Inno存儲(chǔ)引擎，是mysql 數(shù)據(jù)庫(kù)OLTP（online transaction Processing在線事務(wù)處理）應(yīng)用中使用最廣泛的存儲(chǔ)引擎。

1.3.1 ?InnoDB存儲(chǔ)引擎

InnoDB存儲(chǔ)引擎支持事務(wù)，其設(shè)計(jì)目標(biāo)主要面向在線事務(wù)OLTP的應(yīng)用。其特點(diǎn)是行級(jí)鎖設(shè)計(jì)、支持外鍵，并支持類似于Oracle的非鎖定讀，從5.5.8開始InnoDB存儲(chǔ)引擎是默認(rèn)的存儲(chǔ)引擎。

InnoDB通過(guò)使用多版本并發(fā)控制MVCC來(lái)獲得高并發(fā)性，并且實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的四種隔離級(jí)別，默認(rèn)的為REPEATABLE級(jí)別，同時(shí)使用一種被稱為next-key locking的策略來(lái)避免幻讀（phantom）現(xiàn)象的產(chǎn)生。除此之外，InnoDB存儲(chǔ)引擎還提供了插入緩沖（Insert buffer）、二次寫（double write）、自適應(yīng)哈希索引（adaptive hash index）、預(yù)讀（read ahead）等高性能和高可用的功能。

對(duì)于表中的數(shù)據(jù)存儲(chǔ)，InnoDB存儲(chǔ)引擎采用聚集（cluster）的方式，因此每張表的存儲(chǔ)都是按主鍵的順序進(jìn)行存放。如果沒有顯式地在表定義時(shí)指定主鍵，InnoDB存儲(chǔ)引擎會(huì)為每一行生成一個(gè)6個(gè)字節(jié)的ROWID ，并以此作為主鍵。

1.3.2 MyISAM存儲(chǔ)引擎

MyISAM存儲(chǔ)引擎不支持事務(wù)、表鎖設(shè)計(jì)，支持全文索引，主要面向一些OLAP數(shù)據(jù)庫(kù)應(yīng)用。

1.3.3 NDB 存儲(chǔ)引擎是一個(gè)集群存儲(chǔ)引擎，類似于oracle的RAC集群，不過(guò)Oracle，Oracle RAC share everything 架構(gòu)不同是，器結(jié)構(gòu)是share nothing的集群架構(gòu)，因此能夠提供更高的可用性。NDB的特點(diǎn)是數(shù)據(jù)全部放在內(nèi)存中，因此主鍵查詢（primary key lookups）的速度極快，并且通過(guò)添加NDB數(shù)據(jù)存儲(chǔ)節(jié)點(diǎn)（data Node）可以線性地提高數(shù)據(jù)庫(kù)性能，是高可用、高性能的集群系統(tǒng)。

關(guān)于NDB存儲(chǔ)引擎有一個(gè)問(wèn)題值得注意，那就是NDB存儲(chǔ)引擎的鏈接操作JOIN時(shí)在MySQL數(shù)據(jù)庫(kù)層完成的，而不是在存儲(chǔ)引擎層完成的，這意味著復(fù)雜的鏈接操作需要巨大的網(wǎng)絡(luò)開銷，因此查詢速度很慢，如果解決了這個(gè)問(wèn)題，NDB存儲(chǔ)引擎的市場(chǎng)應(yīng)該是非常大的。

1.3.4 Memory存儲(chǔ)引擎

Memory存儲(chǔ)引擎（之前稱之為HEAP存儲(chǔ)引擎）將表中的數(shù)據(jù)存放在內(nèi)存中，如果數(shù)據(jù)庫(kù)重啟或者發(fā)生奔潰，表中的數(shù)據(jù)將消失。它非常適合用于存儲(chǔ)臨時(shí)數(shù)據(jù)的臨時(shí)表，以及數(shù)據(jù)倉(cāng)庫(kù)中的維度表。Memory存儲(chǔ)引擎默認(rèn)使用hash索引，而不是我們熟悉的B+樹索引。

Memory存儲(chǔ)引擎速度快，但只支持表鎖，并發(fā)性能較差，并且不支持TEXT和BLOB列類型，最重要的是，村粗變長(zhǎng)字段varchar時(shí)是按照定常字段方式進(jìn)行的，因此會(huì)浪費(fèi)內(nèi)存。

1.3.5 Archive 存儲(chǔ)引擎

Archve 存儲(chǔ)引擎只支持INSERT 和SELECT操作，從MySQL開始支持索引。

1.3.6 Federated 存儲(chǔ)引擎

1.3.7 Maria 存儲(chǔ)引擎

Maria存儲(chǔ)引擎是新開發(fā)的引擎，設(shè)計(jì)目標(biāo)主要是用來(lái)取代原有的MyISAM存儲(chǔ)殷勤，從而成為MySQL的默認(rèn)存儲(chǔ)引擎。Maria存儲(chǔ)引擎的開發(fā)者是MySQL的創(chuàng)始人之一。Maria存儲(chǔ)引擎的特點(diǎn)是：支持緩存數(shù)據(jù)和索引文件，應(yīng)用了行鎖設(shè)計(jì)，提供了MVCC功能，支持事務(wù)和非事務(wù)安全的選項(xiàng)，以及更好的BLOB字符類型的處理性能。

總結(jié)： MySQL 的InnoDB 存儲(chǔ)引擎的效率在OLTP中效率更好，對(duì)于ETL MyISAM存儲(chǔ)引擎更具有優(yōu)勢(shì)。

當(dāng)數(shù)據(jù)量大于1000萬(wàn)時(shí)MySQL的性能會(huì)急劇下降嗎？不！，MySQL是數(shù)據(jù)庫(kù)，不是文件，隨著數(shù)據(jù)行的增加，性能當(dāng)然會(huì)有所下降，但是這些下降不是線性的，如果用戶選擇了正確的存儲(chǔ)引擎，以及正確的配置，再多的數(shù)據(jù)量MySQL也能承受。InnoDB上存儲(chǔ)超過(guò)1TB的數(shù)據(jù)，還有一些其他網(wǎng)站使用InnoDB存儲(chǔ)引擎，處理插入/更新的操作平均800次/s

衡量標(biāo)準(zhǔn)：

存儲(chǔ)容量的限制、事務(wù)的支持、鎖的粒度、MVCC支持、支持的索引、備份和復(fù)制等

各大存儲(chǔ)引擎

第2章 InnoDB存儲(chǔ)引擎

InnoDB是事務(wù)安全的MySQL存儲(chǔ)引擎，設(shè)計(jì)上采用了類似于Oracle數(shù)據(jù)庫(kù)的架構(gòu)。通常來(lái)說(shuō)，InnoDB存儲(chǔ)引擎是OLTP應(yīng)用中核心代表的首選存儲(chǔ)引擎。同時(shí)，也正式因?yàn)镮nnoDB的存在，才使得MySQL數(shù)據(jù)庫(kù)變得有魅力。

2.1 InnoDB存儲(chǔ)引擎概述

最早由Innobase Oy公司開發(fā)，其特點(diǎn)行鎖設(shè)計(jì)、支持MVCC、支持外鍵、提供一致性非鎖定讀，同時(shí)被設(shè)計(jì)用來(lái)最有效地利用以及內(nèi)存和CPU。

2.3 InnoDB體系架構(gòu)

Inno存儲(chǔ)引擎有多快內(nèi)存塊，可以認(rèn)為這些內(nèi)存組成了一個(gè)大的內(nèi)存池，負(fù)責(zé)如下工作：

1、維護(hù)所有進(jìn)程/線程需要訪問(wèn)的多個(gè)內(nèi)部數(shù)據(jù)結(jié)構(gòu)

2、緩存磁盤上的數(shù)據(jù)，方便快速地讀取，同時(shí)在對(duì)磁盤文件的數(shù)據(jù)修改之前在這里緩存

3、重做日志redo緩沖

后臺(tái)線程的主要負(fù)責(zé)刷新內(nèi)存池中的數(shù)據(jù)，保證緩沖池的內(nèi)存緩存的是最近的數(shù)據(jù)，此外將已經(jīng)修改的數(shù)據(jù)文件刷新到磁盤文件，同時(shí)保證在數(shù)據(jù)庫(kù)發(fā)生的異常情況下，Inno能恢復(fù)到正常的運(yùn)行狀態(tài)。

2.3.1 后臺(tái)線程

InnoDB存儲(chǔ)引擎是多線程的模型，因此其后臺(tái)有多個(gè)不同的后臺(tái)線程，負(fù)責(zé)處理不同的任務(wù)。

1、Master Thread

Master Thread 是一個(gè)非常核心的后臺(tái)線程，主要負(fù)責(zé)將緩沖池中的數(shù)據(jù)異步刷新到磁盤，保證數(shù)據(jù)的一致性，包括臟頁(yè)的刷新、合并插入緩沖（INSERT BUFFER）、UNDO頁(yè)的回收等。

2、IO Thread

在InnoDB存儲(chǔ)引擎中大量使用了AIO（Async IO）來(lái)處理寫IO請(qǐng)求，這樣可以極大提高數(shù)據(jù)庫(kù)的性能。而IO Thread 的工作主要負(fù)責(zé)這些IO請(qǐng)求的回調(diào)（call back）處理。

3、Purge（凈化） Thread

事務(wù)被提交后，其所使用的undolog可能不在需要，因此需要PurgeThread來(lái)回收并分配的undo頁(yè)面。Purge操作可以獨(dú)立到單獨(dú)的線程中，以此來(lái)減輕Master Thread的工作，從而提高CPU的使用率以及提升存儲(chǔ)引擎的性能。用戶可以再M(fèi)ySQL 數(shù)據(jù)庫(kù)的配置文件中添加如下命令來(lái)啟動(dòng)獨(dú)立的Purge ?Thread

innodb_Purge_threads=1

2.3.2內(nèi)存

1、緩沖池

InnoDB 存儲(chǔ)引擎是基于磁盤存儲(chǔ)的，并將其中的記錄按照頁(yè)的方式進(jìn)行管理。因此可將視為基于磁盤的數(shù)據(jù)庫(kù)系統(tǒng)。

緩沖池簡(jiǎn)單說(shuō)就是一塊內(nèi)存區(qū)域，通過(guò)內(nèi)存的速度來(lái)彌補(bǔ)磁盤速度較慢對(duì)數(shù)據(jù)庫(kù)性能的影響，首先將從磁盤讀到的頁(yè)存放在緩沖池中，這個(gè)過(guò)程稱為將頁(yè)“FIX”在緩沖池中。下一次在讀相同的也頁(yè)時(shí)，首先判斷該頁(yè)是否在緩沖池中，若在緩沖池中，稱該頁(yè)在緩沖池中被命中，直接讀取該頁(yè)。否則讀取磁盤上的頁(yè)。

對(duì)于數(shù)據(jù)庫(kù)中也的修改操作，則首先修改緩沖池中的頁(yè)，然后再以一定的頻率刷新到磁盤上。這里需要注意的是，頁(yè)從緩沖池刷新回磁盤的操作斌不是每次頁(yè)發(fā)生更新時(shí)觸發(fā)，而是通過(guò)一種稱為CheckPoint的機(jī)制刷新回磁盤。同樣這也是為了提高數(shù)據(jù)庫(kù)的整體性能。

具體來(lái)看，緩沖池中緩存的數(shù)據(jù)頁(yè)類型有：索引頁(yè)，數(shù)據(jù)頁(yè)、undo頁(yè)、插入緩沖（insert buffer）、自適應(yīng)哈希索引（adaptive hash index）、InnoDB存儲(chǔ)的鎖信息（lock info）、數(shù)據(jù)字典信息（data dictionary）等，InnoDB存儲(chǔ)引擎中內(nèi)存的結(jié)構(gòu)情況。

允許多個(gè)緩沖池實(shí)例，每個(gè)頁(yè)根據(jù)哈希值平均分配到不同的緩沖池實(shí)例中，這種做的好處是減少數(shù)據(jù)庫(kù)內(nèi)部的資源競(jìng)爭(zhēng)，增加數(shù)據(jù)庫(kù)的并發(fā)處理能力。可以通過(guò)參加innodb_buffer_pool_instances來(lái)進(jìn)行配置，該值默認(rèn)為1

2、LRU List、Free List和Flush List

通常來(lái)說(shuō)，數(shù)據(jù)庫(kù)中的緩沖池是通過(guò)LRU （Latest Recent Used），最近最少使用算法來(lái)進(jìn)行管理的。即最頻繁使用的頁(yè)在LRU列表的前端，而最少使用的頁(yè)在LRU列表的尾端。當(dāng)緩沖池不能存放新讀取到頁(yè)時(shí)，將首先釋放LRU列表中的尾端的頁(yè)。

InnoDB存儲(chǔ)引擎中，緩沖池中頁(yè)的大小默認(rèn)為16KB，同樣使用LRU算法對(duì)緩沖池進(jìn)行管理，稍有不同的是InnoDB存儲(chǔ)引擎對(duì)傳統(tǒng)的LRU算法做了一些優(yōu)化。在InnoDB的存儲(chǔ)引擎中，LRU列表中還加入了midpoint位置。新讀取到的頁(yè)，雖然是最新訪問(wèn)的頁(yè)面，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置。這個(gè)算法在InnoDB存儲(chǔ)引擎下稱為midpoint insertion strategy，在默認(rèn)配置下，該文職在LRU列表長(zhǎng)度得5/8處。midpoin位置可由參數(shù)innodb_old_blocks_pct控制，在InnoDB存儲(chǔ)引擎中，把midpoint之后的列表稱為old列表，之前的列表稱為new列表。可以簡(jiǎn)單的理解為new列表中的頁(yè)都是最為活躍的熱點(diǎn)數(shù)據(jù)。

為什么不直接采用樸素的LRU算法，直接將讀取的頁(yè)放入到LRU列表的首部？這是因?yàn)槿糁苯訉⒆x取到的頁(yè)放入到LRU的首部，那么某些SQL操作可能會(huì)使緩沖池中的頁(yè)被刷新出，從而影響緩沖池的效率。常見的這類操作為索引或數(shù)據(jù)的掃描操作。這類操作需要訪問(wèn)列表中的許多頁(yè)，設(shè)置是全部的頁(yè)，而這些頁(yè)面通常來(lái)說(shuō)又僅在這次查詢操作中需要，并不是活躍的熱點(diǎn)數(shù)據(jù)。如果頁(yè)被放入LRU列表的首部，那么非常可能將所需要的熱點(diǎn)數(shù)據(jù)從LRU列表中移除，而在下一次需要讀取該頁(yè)時(shí)，InnoDB存儲(chǔ)引擎需要再次訪問(wèn)磁盤。

為了解決這個(gè)問(wèn)題，InnoDB存儲(chǔ)引擎引入了另一個(gè)參數(shù)來(lái)進(jìn)一步管理LRU列表，這個(gè)參數(shù)是innodb_old_blocks_time，用于表示頁(yè)讀取到mid位置后需要等待多久才會(huì)被加入到LRU列表的熱端。因此當(dāng)需要執(zhí)行上述所說(shuō)的SQL操作時(shí)，可以通過(guò)下面的方法盡可能使LRU列表中熱點(diǎn)數(shù)據(jù)不被刷出。

LRU列表用來(lái)管理已經(jīng)讀取的頁(yè)，但當(dāng)數(shù)據(jù)庫(kù)剛啟動(dòng)時(shí)，LRU列表是空的，即沒有任何頁(yè)，這是頁(yè)都存放在free 列表中，當(dāng)需要從緩沖池中分頁(yè)時(shí)，首先從Free列表中查找是否有可用的空閑頁(yè)面，若有則將該頁(yè)從free列表中刪除，放入到LRU列表中，否則根據(jù)LRU算法，淘汰LRU列表末尾的頁(yè)面，將該內(nèi)部空間分配給新的頁(yè)面。當(dāng)頁(yè)從LRU列表的old部分加入到new部分時(shí)候，稱此時(shí)操作為page made young，而因?yàn)閕nno_old_blocks_time的設(shè)置導(dǎo)致頁(yè)從old部分移動(dòng)new部分操作稱為page not made young?？捎猛ㄟ^(guò)命令SHOW ENGINE INNODB STATUS來(lái)觀察LRU列表及Free列表的使用情況和運(yùn)行狀態(tài)。

在LRU列表中的頁(yè)被修改后，稱該頁(yè)為臟頁(yè)（dirty page），即緩沖池中的頁(yè)和磁盤上的頁(yè)數(shù)據(jù)產(chǎn)生的不一致。這時(shí)數(shù)據(jù)庫(kù)會(huì)通過(guò)CHECKPOINT機(jī)制將臟頁(yè)刷新回磁盤，而Flush列表中，LRU列表用來(lái)管理緩沖池中的頁(yè)的可用性，F(xiàn)luash列表用來(lái)管理將頁(yè)刷新回磁盤，二者互補(bǔ)影響。

3、重做日志緩沖

InnoDB存儲(chǔ)引擎的內(nèi)存區(qū)域除了有緩沖池外，還有重做日志緩沖（redo log buffer）。InnoDB存儲(chǔ)引擎首先將重做日志信息放入到這個(gè)緩沖區(qū)，然后按一定的頻率將其刷新到重做日志文件。重做日志緩沖一般不需要社會(huì)得很大，因?yàn)橐话闱闆r下每秒鐘會(huì)將重做日志緩沖刷新到新的日志文件，因此用戶只需要保證每秒產(chǎn)生的事務(wù)在這個(gè)緩沖大小之內(nèi)即可，該值可由配置參數(shù)innodb_log_buffer_size控制，默認(rèn)為8MB

在通常情況下，8MB的重做日志緩沖池足以滿足絕大部分的應(yīng)用，因?yàn)橹刈鋈罩驹谙盗腥N情況下會(huì)將重做日志緩沖中的內(nèi)容刷新到外掛磁盤的重做日志文件中。

1、Master Thread每一秒將重做日志緩沖刷新到重做日志文件；

2、每個(gè)事務(wù)提交時(shí)會(huì)將重做日志緩沖刷新到重做日志文件；

3、當(dāng)重做日志緩沖池剩余空間小于1/2，重做日志日志緩沖刷新到重做日志文件。

4、額外的內(nèi)存池

在InnoDB存儲(chǔ)引擎中，對(duì)內(nèi)存的管理是通過(guò)一種稱為內(nèi)存堆（heap）的方式進(jìn)行的。在對(duì)一些數(shù)據(jù)結(jié)構(gòu)本身的內(nèi)存進(jìn)行分配時(shí)，需要從額外的內(nèi)存池中進(jìn)行申請(qǐng)，當(dāng)該區(qū)域的內(nèi)存不夠時(shí)，會(huì)從緩沖池中進(jìn)行分配，

2.4 checkpoint技術(shù)

緩沖池的設(shè)計(jì)目的為了協(xié)調(diào)CPU速度和磁盤速度的鴻溝。因此頁(yè)的操作首先都是在緩沖池中完成。如果一條DML語(yǔ)句，如果update或者Delete改變了頁(yè)中國(guó)的記錄，那么此時(shí)頁(yè)是臟的，即緩沖池中的頁(yè)的版本要比磁盤的新。數(shù)據(jù)庫(kù)需要將新版本的頁(yè)從緩沖池刷新到磁盤。

倘若每次一個(gè)頁(yè)發(fā)生變化，就將新頁(yè)面的版本刷新到磁盤，那么這個(gè)開銷是非常大的。若熱點(diǎn)數(shù)據(jù)中在某幾個(gè)頁(yè)中，那么數(shù)據(jù)庫(kù)的性能將變得非常差。同時(shí)如果在從緩沖池將頁(yè)的新版本刷新到磁盤時(shí)發(fā)生了宕機(jī)，那么數(shù)據(jù)就不能恢復(fù)了。為了避免發(fā)生數(shù)據(jù)丟失的問(wèn)題，當(dāng)前事務(wù)數(shù)據(jù)庫(kù)系統(tǒng)普遍都采用了Write Ahead Log策略，即當(dāng)事務(wù)提交時(shí)，先重做日志，再修改頁(yè)面。當(dāng)由于發(fā)生宕機(jī)導(dǎo)致數(shù)據(jù)丟失時(shí)，通過(guò)重做日志來(lái)完成數(shù)據(jù)的恢復(fù)，這也是事務(wù)ACID中D（Durability持久性）的要求。

思考下面的場(chǎng)景，如果重做日志可以無(wú)限地增大，同時(shí)緩沖池也足夠大，能夠緩沖所有數(shù)據(jù)庫(kù)的數(shù)據(jù)，那么是不需要將緩沖池中的頁(yè)的新版本刷新回磁盤。因?yàn)楫?dāng)發(fā)生宕機(jī)的時(shí)候，完全可以通過(guò)重寫日志來(lái)恢復(fù)整個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)到宕機(jī)發(fā)生的時(shí)刻。但是這需要兩個(gè)前提條件：

1、緩沖池可以緩存數(shù)據(jù)庫(kù)中所有的數(shù)據(jù)；

2、重做日志可以無(wú)限大

對(duì)于第一個(gè)前提條件，有經(jīng)驗(yàn)的用戶都知道，當(dāng)數(shù)據(jù)庫(kù)開始創(chuàng)建時(shí)，表中沒有任何數(shù)據(jù)。緩沖池的確可以緩存所有的數(shù)據(jù)庫(kù)文件

。。。。。

Checkpoint 技術(shù)的目的是解決一下幾個(gè)問(wèn)題：

1、縮短數(shù)據(jù)庫(kù)恢復(fù)的時(shí)間

2、緩沖池不夠用，將臟頁(yè)刷新到磁盤

3、重做日志不可用時(shí)，刷新臟頁(yè)面；

當(dāng)數(shù)據(jù)庫(kù)發(fā)生宕機(jī)時(shí)，數(shù)據(jù)庫(kù)不需要重做所有的日志，因?yàn)镃heckPoint之前的頁(yè)都已經(jīng)刷新回磁盤。故數(shù)據(jù)庫(kù)值需要對(duì)Checkpoint后的重做日志進(jìn)行恢復(fù)，這樣就大大縮短了恢復(fù)時(shí)間。

此外，當(dāng)緩沖池不夠用時(shí)，根據(jù)LRU算法會(huì)溢出最近最少使用的頁(yè)，若此頁(yè)面為臟頁(yè)，那么需要強(qiáng)制執(zhí)行Checkpoint，將臟頁(yè)也就是也的新版本刷回磁盤。

重做日志出現(xiàn)不可用的情況是因?yàn)楫?dāng)前事務(wù)數(shù)據(jù)庫(kù)系統(tǒng)對(duì)重做日志的設(shè)計(jì)都是循環(huán)使用的，并不是讓其無(wú)限增大，這從成成本及管理上都是比較困難的，重做日志可以被重用的部分是指這些重做日志已經(jīng)不再需要，即當(dāng)數(shù)據(jù)庫(kù)發(fā)生宕機(jī)時(shí)，數(shù)據(jù)庫(kù)恢復(fù)操作不需要這部分的操作日志，因此這部分就可以被覆蓋重用。若此時(shí)重做日志還需要使用，那么必須強(qiáng)制產(chǎn)生checkpoint，將緩沖池中的頁(yè)至少刷新到當(dāng)前重做日志的位置。

對(duì)于InnoDB存儲(chǔ)引擎而言言，其是通過(guò)LSN（log Sequence Number）來(lái)標(biāo)記版本的。而LSN是8字節(jié)的數(shù)字，其單位是字節(jié)。每個(gè)頁(yè)有LSN，重做日志中也有LSN，checkpoint也有LSN，

在InnoDB存儲(chǔ)引擎中，Checkpoint發(fā)生的時(shí)間，條件以臟頁(yè)的選擇都非常復(fù)雜。而Checkpoint所做的事情無(wú)法外乎是將緩沖池中的昂頁(yè)刷回到磁盤。不同之處在于每次刷新多少頁(yè)到磁盤，每次從哪里取臟頁(yè)，以及什么時(shí)間觸發(fā)Checkpoint，在InnoDB存儲(chǔ)引擎內(nèi)部，有兩種checkpoint，分別為：

1、Sharp Checkpoint

2、Fuzzy Checkpoint

Sharp Checkpoint發(fā)生在數(shù)據(jù)庫(kù)關(guān)閉時(shí)將所有臟頁(yè)都刷新回磁盤，這是默認(rèn)的工作方式

參數(shù) innodb_fast_shutdown=1

但是若數(shù)據(jù)庫(kù)在運(yùn)行時(shí)也使用Sharp Checkpoint，那么數(shù)據(jù)庫(kù)的可用性就會(huì)受到很大的影響，所以在InnoDB存儲(chǔ)引擎內(nèi)部使用Fuzzy CheckPoint進(jìn)行頁(yè)面刷新，即只刷新一部分臟頁(yè)，而不是刷新所有臟頁(yè)回磁盤。

下面幾種情況的Fuzzy CheckPoint

1、Master Thread Checkpoint

2、FLUSH_LRU_LIST_CheckPoint

3、Async/Sync Flush Checkpoint

4、Dirty page too much Checkpoint

Master Thread CheckPoint 以每秒或每十秒的速度從緩沖池的臟頁(yè)列表中刷新一定比例的頁(yè)回磁盤。這個(gè)過(guò)程是異步的，即此時(shí)Inno搜索引擎開源進(jìn)行其他的操作，用戶查詢線程不會(huì)阻塞。

FLUSH_LRU_LIST CheckPoint是因?yàn)镮nnoDB存儲(chǔ)引擎需要保證LRU列表中需要有差不多100多個(gè)空閑頁(yè)可供使用。需要檢查L(zhǎng)RU列表中是都有足夠的可用的空間操作發(fā)生在用戶查詢線程中，顯然會(huì)阻塞用戶的查詢操作。倘若沒有100個(gè)可用空閑頁(yè)面，那么InnoDB存儲(chǔ)引擎會(huì)將LRU列表尾端的頁(yè)移除。如果這些有臟頁(yè)，那么需要進(jìn)行checkpoint，而這些頁(yè)是來(lái)自LRU列表的因此成為FLUSH_LRU_LIST checkpoint。

在mysql 5.6 也就是InnoDB1.2.x版本開始，這個(gè)檢查被一個(gè)單獨(dú)的Page Cleanner線程進(jìn)行，并且用戶可以通過(guò)innodb_lru_scan_depth控制LRU列表中可用頁(yè)面的數(shù)量，該值默認(rèn)為1024

Async/Sync flush checkpoint 指的是重做日志文件不可用的情況，這是需要強(qiáng)制將一些頁(yè)刷新回磁盤，而此時(shí)臟頁(yè)是從臟頁(yè)列表中選取的。若將已經(jīng)寫入重做日志的LSN標(biāo)記為redo_lsn，將已經(jīng)刷新回磁盤最新也的LSN記為checkpoint_lsn

checkpoint_age = radio_lsn - checkpoint_lsn

再定義以下的變量：

async_water_mark = 75% * total_redo_log_file_size

sync_what_mark = 90% * total_log_file_size

2.5 Master Thread 工作方式

InnoDB存儲(chǔ)引擎的主要工作都是在一個(gè)獨(dú)立的后臺(tái)線程Master Thread中完成，

2.5.1 InnoDB 1.0.x版本之前的Master Thread

Master Thread 具有最高的線程的優(yōu)先級(jí)別。其內(nèi)部由多個(gè)循環(huán)loop組成：主循環(huán)loop、后臺(tái)循環(huán)backgroup loop、刷新循環(huán)flush loop、暫停循環(huán)suspend ?loop。Master Thread 會(huì)根據(jù)數(shù)據(jù)庫(kù)運(yùn)行的狀態(tài)在loop，backgroud loop 、flush loop 和suspend loop中進(jìn)行切換。

可以看到，loop循環(huán)通過(guò)thread sleep來(lái)實(shí)現(xiàn)，這意味著所謂的每秒一次或者10秒一次的操作時(shí)不精確的，在負(fù)載很大的情況下可能會(huì)有延遲（delay），只能說(shuō)大概在這個(gè)頻率下。當(dāng)然InnoDB源代碼中還通過(guò)了其他方法來(lái)盡量保證這個(gè)頻率。

每秒一次的操作包括：

1、日志緩沖刷新到磁盤，即使這個(gè)事務(wù)還沒有提交（總是）

2、合并插入緩沖（可能）

3、至多刷新100個(gè)InnoDB的緩沖池中的臟頁(yè)到磁盤（可能）

4、如果當(dāng)前沒有用戶活動(dòng)，則切換到background loop（可能）

即使某個(gè)事務(wù)還沒有提交，InnoDB存儲(chǔ)引擎任然每秒會(huì)將重做日志緩沖中的內(nèi)容刷新到重做日志文件。這一點(diǎn)是必須要知道的，因?yàn)檫@可以很好地解釋為什么再大的事務(wù)提交commit的時(shí)間也是很短的。

合并插入緩沖（Insert Buffer）并不是每秒都會(huì)發(fā)生的。InnoDB存儲(chǔ)引擎會(huì)判斷當(dāng)前一秒內(nèi)發(fā)生的IO次數(shù)是否小于5，如果小于5次，InnoDB認(rèn)為當(dāng)前的IO壓力很小，可以執(zhí)行合并插入緩沖的操作。

同樣，刷新100個(gè)臟頁(yè)也不是每秒會(huì)發(fā)生的，InnoDB存儲(chǔ)引擎通過(guò)判斷當(dāng)前緩沖池中臟頁(yè)的比例（buf_get_modified_ratio_pct）是否超過(guò)了配置文件中innodb_max_dirty_pages_pct 這個(gè)參數(shù)（默認(rèn)為90%），如果超過(guò)了這個(gè)閾值，InnoDB存儲(chǔ)引擎認(rèn)為需要做磁盤同步的操作，將100臟頁(yè)寫入磁盤中。

10秒的操作

1、刷新100個(gè)臟頁(yè)到磁盤（可能的情況下）

2、合并至多5個(gè)插入緩沖（總是）

3、將日志緩沖刷新到磁盤（總是）

4、刪除無(wú)用的undo頁(yè)（總是）

5、刷新100個(gè)或者10個(gè)臟頁(yè)到磁盤（總是）。

在以上的過(guò)程中，InnoDB存儲(chǔ)引擎會(huì)先判斷過(guò)去10秒內(nèi)磁盤的IO操作是否是小于200次，，如果是，InnoDB存儲(chǔ)引擎認(rèn)為目前有足夠的磁盤IO操作能力，因此將100個(gè)臟頁(yè)刷新到磁盤。接著，InnoDB存儲(chǔ)引擎會(huì)合并插入緩沖。不同于每秒一次操作時(shí)可能發(fā)生的合并插入緩沖的操作，這次的合并插入緩沖操作總會(huì)在這個(gè)階段進(jìn)行。之后，InnoDB存儲(chǔ)引擎會(huì)進(jìn)行一次將日志緩沖刷新到磁盤的操作。這和每秒一次時(shí)發(fā)生的操作是一樣的。

接著InnoDB存儲(chǔ)引擎會(huì)執(zhí)行full purge操作，即刪除無(wú)用的Undo頁(yè)面。對(duì)表進(jìn)行update、delete這類操作，原先的行被標(biāo)記為刪除，但是因?yàn)橐恢滦宰x（consistance read）的關(guān)系，需要保留這些行版本的信息。但是在full purge的過(guò)程中，InnoDB存儲(chǔ)引擎會(huì)判斷當(dāng)前事務(wù)系統(tǒng)中已被刪除的行是都可以刪除，比如有時(shí)候可能還有查詢操作需要讀取之前的版本undo的信息，如果可以刪除，InnoDB會(huì)立即將其刪除。從源代碼中可以發(fā)現(xiàn)，InnoDB存儲(chǔ)引擎在執(zhí)行full purge操作時(shí)，每次最多嘗試回收20個(gè)undo頁(yè)。

然后InnoDB存儲(chǔ)引擎會(huì)判斷緩沖池頁(yè)面的比例buf_get_modified_ratio_pct，如果有超過(guò)50%的臟頁(yè)面，則刷新100個(gè)臟頁(yè)到磁盤，如果臟頁(yè)的比例小于70%，則只需要刷新10%的臟頁(yè)面到磁盤。

若當(dāng)前沒有用戶活動(dòng)（數(shù)據(jù)庫(kù)空閑）或者數(shù)據(jù)庫(kù)關(guān)閉（shutdown），就會(huì)切換到這個(gè)循環(huán)。background loop會(huì)執(zhí)行以下操作：

1、刪除無(wú)用的undo頁(yè)（總是）

2、合并20個(gè)插入緩存（總是）

3、跳回到主循環(huán)（總是）

4、不斷刷新100個(gè)頁(yè)面知道服務(wù)條件（可能，跳轉(zhuǎn)到flush loop中完成）。

若flush loop中也什么事情可以做，InnoDB存儲(chǔ)引擎會(huì)切換到suspend_loop，將Master Thread掛起，等待事情的發(fā)生。若用戶啟用enable了InnoDB存儲(chǔ)引擎，卻沒有使用任何InnoDB存儲(chǔ)引擎的表，那么Master Thread 總是處于掛起的狀態(tài)。

2.5.2 InnoDB1.2.x版本之前的Master Thread

InnoDB存儲(chǔ)引擎對(duì)于IO其實(shí)是有限制的，在緩沖池想磁盤刷新時(shí)，其實(shí)都做了一定的硬編碼（hard coding）。在磁盤技術(shù)飛速發(fā)展的今天，當(dāng)固態(tài)硬盤SSD出現(xiàn)時(shí)，這種規(guī)定在很大程度上限制了InnoDB存儲(chǔ)引擎對(duì)磁盤IO的性能，尤其是寫入性能。

InnoDB存儲(chǔ)引擎最大只會(huì)刷新100個(gè)臟頁(yè)到磁盤，合并20個(gè)插入緩沖。如果再寫入密集的應(yīng)用程序中，每秒可能產(chǎn)生大于100個(gè)臟頁(yè)面，如果是產(chǎn)生了大于20個(gè)插入緩沖的情況，Master Thread 似乎會(huì)“忙不過(guò)來(lái)”，或者說(shuō)它總是做的很慢。即使磁盤能在1秒內(nèi)處理多于100個(gè)臟頁(yè)的寫入和20個(gè)插入緩存的合并，但是由于hard coding。Master Thread 也只會(huì)選擇刷新100個(gè)臟頁(yè)和并20個(gè)插入緩沖。同時(shí)，當(dāng)發(fā)生宕機(jī)需要恢復(fù)時(shí)，由于很多數(shù)據(jù)還沒有刷新回磁盤，會(huì)導(dǎo)致恢復(fù)的時(shí)間可能需要很久，尤其是對(duì)于insert buffer來(lái)說(shuō)。經(jīng)過(guò)谷歌團(tuán)隊(duì)的修正提供了innodb_io_capacity的百分比來(lái)進(jìn)行控制，規(guī)則如下：

1、在合并插入緩沖時(shí)，合并插入緩沖的數(shù)量為innodb_io_capacity值的5%

2、在從緩沖區(qū)刷新臟頁(yè)時(shí)，刷新臟頁(yè)的數(shù)量為Innodb_io_capacity

如果用戶使用了SSD類的磁盤，或者將盡快磁盤做了RAID，當(dāng)存儲(chǔ)設(shè)備擁有更高的IO速度時(shí)，玩可以將innodb_io_capacity的值調(diào)得再高點(diǎn)，知道符合磁盤IO的吞吐量為止。

另一個(gè)問(wèn)題是，參數(shù)innodb_max_dirty_pages_pct默認(rèn)值的問(wèn)題，在InnoDB 1.0.x版本之前，該值的默認(rèn)為90，意味著臟頁(yè)占緩沖池的90%，但是該值“太大”了，因?yàn)镮nnoDB存儲(chǔ)引擎在每秒刷新緩沖池和flush loop時(shí)會(huì)判斷這個(gè)值，如果該值大于innodb_max_dirty_pages_pct，才刷新100個(gè)臟頁(yè)，如果有很大的內(nèi)存，或者數(shù)據(jù)庫(kù)服務(wù)器的壓力很大，這時(shí)刷新臟頁(yè)的速度反而會(huì)降低。同樣在數(shù)據(jù)庫(kù)的恢復(fù)階段可能需要更多的時(shí)間。

在很多論壇上都有對(duì)這個(gè)問(wèn)題的討論，有人甚至將這個(gè)值調(diào)到 20或10，然后測(cè)試發(fā)現(xiàn)性能所有提高，但是將Innodb_max-dirty_pages_pct調(diào)到20或10會(huì)增加磁盤的壓力，系統(tǒng)的負(fù)擔(dān)還是會(huì)有所增加的。google在這個(gè)問(wèn)題上進(jìn)行了測(cè)試，證明20并不是一個(gè)最優(yōu)值。從1.0.x版本開始，inno_max_dity_pages_pct默認(rèn)值變?yōu)?5和google測(cè)試的80%比較接近，這樣既可以加快刷新臟頁(yè)頻率，又能保證磁盤IO的負(fù)載。

InnoDB 1.0.x版本帶來(lái)的另一個(gè)參數(shù)是innodb_adaptive_flushing（自適應(yīng)地刷新）

該值影響每秒刷新臟頁(yè)的數(shù)量，不刷新臟頁(yè)；大于innodb_max_dirty_pages_pct時(shí)，刷新100個(gè)臟頁(yè)。隨著inno_adaptive_flushing參數(shù)的引入，InnoDB存儲(chǔ)引擎會(huì)通過(guò)一個(gè)名為buf_flush_get_desired_flush_rate的函數(shù)來(lái)判斷需要刷新臟頁(yè)最合適的數(shù)量。粗略地翻閱源代碼后發(fā)現(xiàn)buf_flush_get_desired_flush_rate通過(guò)判斷產(chǎn)生重做日志undo log的速度來(lái)決定適合的刷新臟頁(yè)的數(shù)量。因此當(dāng)臟頁(yè)的比例小于innodb_max_dirty_pages_pct時(shí)，也會(huì)刷新一定量的臟頁(yè)。

還有一個(gè)改變：之前每次進(jìn)行full purge操作時(shí)，最多回收20個(gè)undo頁(yè)面，從InnoDB 1.0.x版本開始引入?yún)?shù)innodb_purge_batch_size這個(gè)參數(shù)可以控制每次full purge回收的undo頁(yè)的數(shù)量。該參數(shù)的默認(rèn)值為20，并可以動(dòng)態(tài)地對(duì)其進(jìn)行修改。

2.5.3 InnoDB 1.2.x版本的Master Thread

srv_master_do_idle_tasks() 之前版本中每10秒的操作

srv_master_do_active_tasks()之前版本每秒的操作

從Master Thread線程分離到一個(gè)單獨(dú)的Page Cleaner Thread，從而減輕了Master Thread的工作，同時(shí)進(jìn)一步挺高了行的并發(fā)性。

2.6 InnoDB關(guān)鍵特性

1、插入緩沖 （Insert buffer）

2、兩次寫（double write）

3、自適應(yīng)哈希索引（Adaptive Hash Index）

4、異步IO（Async IO）

5、刷新鄰接頁(yè)（Flush Neighbor Page）

一般情況下按照主鍵有序插入，如自增長(zhǎng)，這樣的插入方式速度快，

如遇到UUID或者輔助索引，secondary index 并非有序，需要離散地訪問(wèn)非聚集索引頁(yè)，由于隨機(jī)讀寫的存在而導(dǎo)致插入操作性能下降，而B+樹的特性決定了非聚集索引插入的離散性。

插入緩沖（insert buffer）

InnoDB存儲(chǔ)引擎開創(chuàng)性地設(shè)計(jì)了Insert Buffer，對(duì)于非聚集索引的插入或更新操作，不是每一次直接插入到索引頁(yè)中，而是先判斷插入的非聚集的引頁(yè)是都在緩沖池中，若在，則直接插入，若不在，則先放入到一個(gè)Insert Buffer對(duì)象中。然后再以一定的頻率和情況進(jìn)行Insert buffer和輔助索引頁(yè)子節(jié)點(diǎn)的merge（合并）操作，這是通常能將多個(gè)插入合并到同一個(gè)操作中（因?yàn)樵谝粋€(gè)索引頁(yè)面中），這就大大提高了非聚集索引插入的性能。

insert buffer 的使用需要滿足以下兩個(gè)條件：

1、索引是輔助索引（secondary index）

2、索引不是唯一索引（unique）的

3、insert buffer的內(nèi)部實(shí)現(xiàn)

以前版本每個(gè)表都有一顆Insert buffer B+樹

現(xiàn)在的版本內(nèi)部只要一個(gè)B+樹，負(fù)責(zé)對(duì)所有的表的輔助索引進(jìn)行Insert buffer。而這棵B+樹存放在共享表的空間中，默認(rèn)也是在ibdata1。因此試圖通過(guò)獨(dú)立表空間ibd文件恢復(fù)表中的數(shù)據(jù)時(shí)，往往會(huì)導(dǎo)致CHECK TABLE失敗。這是因?yàn)楸淼妮o助索引中的數(shù)據(jù)可能換在Insert Buffer中，也是共享空間中，所以通過(guò)ibd文件進(jìn)行回復(fù)后，還需要進(jìn)行REPAIR TABLE操作來(lái)重建表上的所有輔助索引。

Insert Buffer 是一個(gè)B+樹，因此由也是節(jié)點(diǎn)和非葉子節(jié)點(diǎn)組成。非葉子節(jié)點(diǎn)存放的是查詢的search key（鍵值）構(gòu)造如下

search key一共占用9個(gè)字節(jié)，其中space表示帶插入記錄所在的表的表空間id，space占用4個(gè)字節(jié)。marker占用1個(gè)字節(jié)，它是用來(lái)兼容老版本的insert buffer，offset表示頁(yè)所在的偏移量，占4個(gè)字節(jié)

當(dāng)一個(gè)輔助索引要插入到頁(yè)（space，offset）時(shí)，如果這個(gè)頁(yè)不在緩沖池中，那么InnoDB存儲(chǔ)引擎首先根據(jù)上述規(guī)則茍澤一個(gè)search key，接下來(lái)查詢insert buffer 這課B+樹，然后再將這條記錄插入到Insert Buffer B+樹的葉子節(jié)點(diǎn)中

2.6.2兩次寫 doublewrite

2.6.3 自適應(yīng)哈希索引

哈希（hash）是一種非常快的查找方式，在一般的情況下這種查找的時(shí)間復(fù)雜度為O（1），即一般僅需要一次查找就能定位的數(shù)據(jù)。而B+樹的查找次數(shù)，取決于B+樹的高度，在生產(chǎn)環(huán)境中，B+樹的高度一般為3-4層，古只需要3-4查詢。

InnoDB存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上各索引頁(yè)的查詢。如果觀察到建立哈希索引可以帶來(lái)速度提升，則建立哈希索引，稱之為自適應(yīng)哈希索引（Adaptive Hash Index，AHI）。AHI是通過(guò)緩沖池的B+樹頁(yè)構(gòu)造而來(lái)，因此建立的速度很快，而且不需要對(duì)整張表構(gòu)建哈希索引。InnoDB存儲(chǔ)引擎會(huì)自動(dòng)根據(jù)訪問(wèn)的頻率和模式來(lái)自動(dòng)地為某些熱點(diǎn)建立哈希索引。

2.6.4異步IO

2.6.5刷新鄰接頁(yè)

Flush Neighbor Page（刷新緊鄰頁(yè)）的特性，其工作原理為：當(dāng)刷新一個(gè)臟頁(yè)時(shí)，Inno存儲(chǔ)引擎會(huì)檢測(cè)該頁(yè)所在區(qū)（extent）的所有頁(yè)，如果是臟頁(yè)，那么一起進(jìn)行刷新。