MYSQL 存儲引擎

查看MySQL提供所有的存儲引擎

mysql> show engines;

mysql常用引擎包括：MYISAM、Innodb、Memory、MERGE
1、MYISAM：全表鎖，擁有較高的執(zhí)行速度，不支持事務(wù)，不支持外鍵，并發(fā)性能差，占用空間相對較小，對事務(wù)完整性沒有要求，以select、insert為主的應(yīng)用基本上可以使用這引擎
2、Innodb：行級鎖，提供了具有提交、回滾和崩潰回復(fù)能力的事務(wù)安全，支持自動增長列，支持外鍵約束，并發(fā)能力強，占用空間是MYISAM的2.5倍，處理效率相對會差一些
3、Memory：全表鎖，存儲在內(nèi)容中，速度快，但會占用和數(shù)據(jù)量成正比的內(nèi)存空間且數(shù)據(jù)在mysql重啟時會丟失，默認(rèn)使用HASH索引，檢索效率非常高，但不適用于精確查找，主要用于那些內(nèi)容變化不頻繁的代碼表
4、MERGE：是一組MYISAM表的組合

MyISAM

MyISAM引擎特點

MyISAM引擎是MySQL5.5版本之前的數(shù)據(jù)庫所默認(rèn)的數(shù)據(jù)表引擎。每一個采用MyISAM引擎的數(shù)據(jù)表在實際存儲中都是由三個文件組成，分別是frm文件，MYD文件和MYI文件，文件后綴為上述三個，文件名與數(shù)據(jù)表名相同。
其中，frm文件保存表的結(jié)構(gòu)，MYD保存表的數(shù)據(jù)，MYI保存表的索引文件，MYD和MYI與MyISAM引擎有很深的關(guān)聯(lián)。

MyISAM引擎的特點

1、不支持事務(wù)。
2、表級鎖定。 即發(fā)生數(shù)據(jù)更新時，會鎖定整個表，以防止其他會話對該表中數(shù)據(jù)的同時修改所導(dǎo)致的混亂。這樣做可以使得操作簡單，但是會減少并發(fā)量。
3、讀寫互相堵塞。 在MyISM類型的表中，既不可以在向數(shù)據(jù)表中寫入數(shù)據(jù)的同時另一個會話也向該表中寫入數(shù)據(jù)，也不允許其他的會話讀取該表中的數(shù)據(jù)。只允許多個會話同時讀取該數(shù)據(jù)表中的數(shù)據(jù)。
4、只會緩存索引，不會緩存數(shù)據(jù)。 所謂緩存，就是指數(shù)據(jù)庫在訪問磁盤數(shù)據(jù)時，將更多的數(shù)據(jù)讀取進入內(nèi)存，這樣可以使得當(dāng)訪問這些數(shù)據(jù)時，直接從內(nèi)存中讀取而不是再次訪問硬盤。MyISAM可以通過key_buffer_size緩存索引，以減少磁盤I/O，提升訪問性能。但是MyISAM數(shù)據(jù)表并不會緩存數(shù)據(jù)。
5、讀取速度較快，占用資源較少。
6、不支持外鍵約束。
7、支持全文索引。

說明：從MySQL8.0開始，它就可以被稱為過時了 – MySQL已經(jīng)確保在使用InnoDB時，MyISAM可以完成的所有工作都可以完成，因此目前，只有當(dāng)您希望簡單的COUNT（*）查詢更快時，MyISAM才非常有用。這樣的查詢會更快，因為MyISAM將表記錄數(shù)數(shù)字存儲在表元數(shù)據(jù)中–其他 MySQL存儲引擎則沒有

InnoDB

從Mysql5.5版本開始，InnoDB是默認(rèn)的表存儲引擎。其特點是行鎖設(shè)計、支持MVCC、支持外鍵、提供一致性非鎖定讀、同時被設(shè)計用來最有效的利用以及使用內(nèi)存和CPU。

InnoDB體系架構(gòu)
下圖簡單描述了InnoDB存儲引擎的體系結(jié)構(gòu)：

InnoDB存儲引擎有多個內(nèi)存塊，這些內(nèi)存塊組成了一個大的內(nèi)存池。后臺線程主要負(fù)責(zé)刷新內(nèi)存池中的數(shù)據(jù)、將已修改的數(shù)據(jù)刷新到磁盤等等。接下來我們分別介紹后臺線程和內(nèi)存池。

后臺線程

InnoDB后臺有多個不同的線程，用來負(fù)責(zé)不同的任務(wù)。主要有如下：

1、Master Thread
這是最核心的一個線程,主要負(fù)責(zé)將緩沖池中的數(shù)據(jù)異步刷新到磁盤,保證數(shù)據(jù)的一致性,包括贓頁的刷新、合并插入緩沖、UNDO 頁的回收等.
2、IO Thread
在 InnoDB 存儲引擎中大量使用了異步 IO 來處理寫 IO 請求, IO Thread 的工作主要是負(fù)責(zé)這些 IO 請求的回調(diào)處理。
3、Purge Thread
事務(wù)被提交之后, undo log 可能不再需要,因此需要 Purge Thread 來回收已經(jīng)使用并分配的 undo頁. InnoDB 支持多個 Purge Thread, 這樣做可以加快 undo 頁的回收。
4、Page Cleaner Thread
Page Cleaner Thread 是在InnoDB 1.2.x版本新引入的,其作用是將之前版本中臟頁的刷新操作都放入單獨的線程中來完成,這樣減輕了 Master Thread 的工作及對于用戶查詢線程的阻塞。

內(nèi)存

InnoDB 存儲引擎是基于磁盤存儲的,也就是說數(shù)據(jù)都是存儲在磁盤上的,由于 CPU 速度和磁盤速度之間的鴻溝,

InnoDB 引擎使用緩沖池技術(shù)來提高數(shù)據(jù)庫的整體性能。緩沖池簡單來說就是一塊內(nèi)存區(qū)域.在數(shù)據(jù)庫中進行讀取頁的操作,首先將從磁盤讀到的頁存放在緩沖池中,下一次讀取相同的頁時,首先判斷該頁是不是在緩沖池中,若在,稱該頁在緩沖池中被命中,直接讀取該頁。否則,讀取磁盤上的頁。對于數(shù)據(jù)庫中頁的修改操作,首先修改在緩沖池中頁,然后再以一定的頻率刷新到磁盤,并不是每次頁發(fā)生改變就刷新回磁盤。

緩沖池中緩存的數(shù)據(jù)頁類型有:索引頁、數(shù)據(jù)頁、 undo 頁、插入緩沖、自適應(yīng)哈希索引、 InnoDB 的鎖信息、數(shù)據(jù)字典信息等。索引頁和數(shù)據(jù)頁占緩沖池的很大一部分（知道有這些頁，把這些頁當(dāng)做名詞即可，不用感到迷惑）。在InnoDB中，緩沖池中的頁大小默認(rèn)為16KB。

我們已經(jīng)知道這個Buffer Pool其實是一片連續(xù)的內(nèi)存空間，那現(xiàn)在就面臨這個問題了：怎么將磁盤上的頁緩存到內(nèi)存中的Buffer Pool中呢？直接把需要緩存的頁向Buffer Pool里一個一個往里懟么？不不不，為了更好的管理這些被緩存的頁，InnoDB為每一個緩存頁都創(chuàng)建了一些所謂的控制信息，這些控制信息包括該頁所屬的表空間編號、頁號、頁在Buffer Pool中的地址，一些鎖信息以及LSN信息（鎖和LSN這里可以先忽略），當(dāng)然還有一些別的控制信息。

每個緩存頁對應(yīng)的控制信息占用的內(nèi)存大小是相同的，我們就把每個頁對應(yīng)的控制信息占用的一塊內(nèi)存稱為一個控制塊吧，控制塊和緩存頁是一一對應(yīng)的，它們都被存放到 Buffer Pool 中，其中控制塊被存放到 Buffer Pool 的前邊，緩存頁被存放到 Buffer Pool 后邊，所以整個Buffer Pool對應(yīng)的內(nèi)存空間看起來就是這樣的：

控制塊和緩存頁之間的那個碎片是個什么呢？你想想啊，每一個控制塊都對應(yīng)一個緩存頁，那在分配足夠多的控制塊和緩存頁后，可能剩余的那點兒空間不夠一對控制塊和緩存頁的大小，自然就用不到嘍，這個用不到的那點兒內(nèi)存空間就被稱為碎片了。當(dāng)然，如果你把Buffer Pool的大小設(shè)置的剛剛好的話，也可能不會產(chǎn)生碎片～

前面我們知道了緩沖池的結(jié)構(gòu)。接下來說InnoDB存儲引擎是怎么對緩沖池進行管理的。講下我當(dāng)時的困惑吧，（這部分可以不看，因為有些名詞還沒涉及到）看《Mysql技術(shù)內(nèi)幕》這本書，講LRU List比較多，F(xiàn)ree List只是順帶一過，沒有將free list初始時是怎么分配的或者是什么樣的結(jié)構(gòu)，導(dǎo)致我對緩沖池的管理總是想象不出來，后來經(jīng)過在網(wǎng)上尋找資料，算是弄明白了。來看一下吧：

當(dāng)我們最初啟動MySQL服務(wù)器的時候，需要完成對Buffer Pool的初始化過程，就是分配Buffer Pool的內(nèi)存空間，把它劃分成若干對控制塊和緩存頁。但是此時并沒有真實的磁盤頁被緩存到Buffer Pool中（因為還沒有用到），之后隨著程序的運行，會不斷的有磁盤上的頁被緩存到Buffer Pool中，那么問題來了，從磁盤上讀取一個頁到Buffer Pool中的時候該放到哪個緩存頁的位置呢？或者說怎么區(qū)分Buffer Pool中哪些緩存頁是空閑的，哪些已經(jīng)被使用了呢？我們最好在某個地方記錄一下哪些頁是可用的，我們可以把所有空閑的頁包裝成一個節(jié)點組成一個鏈表，這個鏈表也可以被稱作Free鏈表（或者說空閑鏈表）。因為剛剛完成初始化的Buffer Pool中所有的緩存頁都是空閑的，所以每一個緩存頁都會被加入到Free鏈表中，假設(shè)該Buffer Pool中可容納的緩存頁數(shù)量為n，那增加了Free鏈表的效果圖就是這樣的：

從圖中可以看出，我們?yōu)榱斯芾砗眠@個Free鏈表，特意為這個鏈表定義了一個控制信息，里邊兒包含著鏈表的頭節(jié)點地址，尾節(jié)點地址，以及當(dāng)前鏈表中節(jié)點的數(shù)量等信息。我們在每個Free鏈表的節(jié)點中都記錄了某個緩存頁控制塊的地址，而每個緩存頁控制塊都記錄著對應(yīng)的緩存頁地址，所以相當(dāng)于每個Free鏈表節(jié)點都對應(yīng)一個空閑的緩存頁。

有了這個Free鏈表事兒就好辦了，每當(dāng)需要從磁盤中加載一個頁到Buffer Pool中時，就從Free鏈表中取一個空閑的緩存頁，并且把該緩存頁對應(yīng)的控制塊的信息填上，然后把該緩存頁對應(yīng)的Free鏈表節(jié)點從鏈表中移除，表示該緩存頁已經(jīng)被使用了～

簡單回顧一下，為什么講free list？是為了講怎么管理buffer pool對吧。那free list就相當(dāng)于是數(shù)據(jù)庫服務(wù)剛剛啟動沒有數(shù)據(jù)頁時，維護buffer pool的空閑緩存頁的數(shù)據(jù)結(jié)構(gòu)。

下面再來簡單地回顧Buffer Pool的工作機制。Buffer Pool兩個最主要的功能：一個是加速讀，一個是加速寫。加速讀呢？ 就是當(dāng)需要訪問一個數(shù)據(jù)頁面的時候，如果這個頁面已經(jīng)在緩存池中，那么就不再需要訪問磁盤，直接從緩沖池中就能獲取這個頁面的內(nèi)容。加速寫呢？就是當(dāng)需要修改一個頁面的時候，先將這個頁面在緩沖池中進行修改，記下相關(guān)的重做日志，這個頁面的修改就算已經(jīng)完成了。至于這個被修改的頁面什么時候真正刷新到磁盤，這個是后臺刷新線程來完成的。

在實現(xiàn)上面兩個功能的同時，需要考慮客觀條件的限制，因為機器的內(nèi)存大小是有限的，所以MySQL的InnoDB Buffer Pool的大小同樣是有限的，如果需要緩存的頁占用的內(nèi)存大小超過了Buffer Pool大小，也就是Free鏈表中已經(jīng)沒有多余的空閑緩存頁的時候豈不是很尷尬，發(fā)生了這樣的事兒該咋辦？當(dāng)然是把某些舊的緩存頁從Buffer Pool中移除，然后再把新的頁放進來嘍～ 那么問題來了，移除哪些緩存頁呢？

為了回答這個問題，我們還需要回到我們設(shè)立Buffer Pool的初衷，我們就是想減少和磁盤的I/O交互，最好每次在訪問某個頁的時候它都已經(jīng)被緩存到Buffer Pool中了。假設(shè)我們一共訪問了n次頁，那么被訪問的頁已經(jīng)在緩存中的次數(shù)除以n就是所謂的緩存命中率，我們的期望就是讓緩存命中率越高越好～

怎么提高緩存命中率呢？

InnoDB Buffer Pool采用經(jīng)典的LRU算法來進行頁面淘汰，以提高緩存命中率。當(dāng)Buffer Pool中不再有空閑的緩存頁時，就需要淘汰掉部分最近很少使用的緩存頁。不過，我們怎么知道哪些緩存頁最近頻繁使用，哪些最近很少使用呢？呵呵，神奇的鏈表再一次派上了用場，我們可以再創(chuàng)建一個鏈表，由于這個鏈表是為了按照最近最少使用的原則去淘汰緩存頁的，所以這個鏈表可以被稱為LRU鏈表（Least Recently Used）。當(dāng)我們需要訪問某個頁時，可以這樣處理LRU鏈表：

1、如果該頁不在Buffer Pool中，在把該頁從磁盤加載到Buffer Pool中的緩存頁時，就把該緩存頁包裝成節(jié)點塞到鏈表的頭部。
2、如果該頁在Buffer Pool中，則直接把該頁對應(yīng)的LRU鏈表節(jié)點移動到鏈表的頭部。
但是這樣做會有一些性能上的問題，比如你的一次全表掃描或一次邏輯備份就把熱數(shù)據(jù)給沖完了，就會導(dǎo)致導(dǎo)致緩沖池污染問題！Buffer Pool中的所有數(shù)據(jù)頁都被換了一次血，其他查詢語句在執(zhí)行時又得執(zhí)行一次從磁盤加載到Buffer Pool的操作，而這種全表掃描的語句執(zhí)行的頻率也不高，每次執(zhí)行都要把Buffer Pool中的緩存頁換一次血，這嚴(yán)重的影響到其他查詢對 Buffer Pool 的使用，嚴(yán)重的降低了緩存命中率 ！

所以InnoDB存儲引擎對傳統(tǒng)的LRU算法做了一些優(yōu)化，在InnoDB中加入了midpoint。新讀到的頁，雖然是最新訪問的頁，但并不是直接插入到LRU列表的首部，而是插入LRU列表的midpoint位置。這個算法稱之為midpoint insertion stategy。默認(rèn)配置插入到列表長度的5/8處。midpoint由參數(shù)innodb_old_blocks_pct控制。

midpoint之前的列表稱之為new列表，之后的列表稱之為old列表?？梢院唵蔚膶ew列表中的頁理解為最為活躍的熱點數(shù)據(jù)。

同時InnoDB存儲引擎還引入了innodb_old_blocks_time來表示頁讀取到mid位置之后需要等待多久才會被加入到LRU列表的熱端?？梢酝ㄟ^設(shè)置該參數(shù)保證熱點數(shù)據(jù)不輕易被刷出。

好了，基本拿下了LRU list后，我們繼續(xù)。前面我們講到頁面更新是在緩存池中先進行的，那它就和磁盤上的頁不一致了，這樣的緩存頁也被稱為臟頁（英文名：dirty page）。

所以需要考慮這些被修改的頁面什么時候刷新到磁盤？以什么樣的順序刷新到磁盤？

當(dāng)然，最簡單的做法就是每發(fā)生一次修改就立即同步到磁盤上對應(yīng)的頁上，但是頻繁的往磁盤中寫數(shù)據(jù)會嚴(yán)重的影響程序的性能（畢竟磁盤慢的像烏龜一樣）。所以每次修改緩存頁后，我們并不著急立即把修改同步到磁盤上，而是在未來的某個時間點進行同步，由后臺刷新線程依次刷新到磁盤，實現(xiàn)修改落地到磁盤。

但是如果不立即同步到磁盤的話，那之后再同步的時候我們怎么知道Buffer Pool中哪些頁是臟頁，哪些頁從來沒被修改過呢？

總不能把所有的緩存頁都同步到磁盤上吧，假如Buffer Pool被設(shè)置的很大，比方說300G，那一次性同步這么多數(shù)據(jù)豈不是要慢死！所以，我們不得不再創(chuàng)建一個存儲臟頁的鏈表，凡是在LRU鏈表中被修改過的頁都需要加入這個鏈表中，因為這個鏈表中的頁都是需要被刷新到磁盤上的，所以也叫FLUSH鏈表，有時候也會被簡寫為FLU鏈表。鏈表的構(gòu)造和Free鏈表差不多，這就不贅述了。

這里的臟頁修改指的此頁被加載進Buffer Pool后第一次被修改，只有第一次被修改時才需要加入FLUSH鏈表（代碼中是根據(jù)Page頭部的oldest_modification == 0來判斷是否是第一次修改），如果這個頁被再次修改就不會再放到FLUSH鏈表了，因為已經(jīng)存在。需要注意的是，臟頁數(shù)據(jù)實際還在LRU鏈表中，而FLUSH鏈表中的臟頁記錄只是通過指針指向LRU鏈表中的臟頁。并且在FLUSH鏈表中的臟頁是根據(jù)oldest_lsn（這個值表示這個頁第一次被更改時的lsn號，對應(yīng)值oldest_modification，每個頁頭部記錄）進行排序刷新到磁盤的，值越小表示要最先被刷新，避免數(shù)據(jù)不一致。

注意：臟頁既存在于LRU列表中，也存在與Flush列表中。LRU列表用來管理緩沖池中頁的可用性，F(xiàn)lush列表用來管理將頁刷新回磁盤，二者互不影響。
這三個重要列表（LRU list， free list，flush list）的關(guān)系可以用下圖表示：

Free鏈表跟LRU鏈表的關(guān)系是相互流通的，頁在這兩個鏈表間來回置換。而FLUSH鏈表記錄了臟頁數(shù)據(jù)，也是通過指針指向了LRU鏈表，所以圖中FLUSH鏈表被LRU鏈表包裹。

CheckPoint技術(shù)

說完緩沖池，下面說CheckPoint技術(shù)。
CheckPoint技術(shù)是用來解決如下幾個問題：

縮短數(shù)據(jù)庫恢復(fù)時間
緩沖池不夠用時，將臟頁刷新到磁盤
重做日志不可用時，刷新臟頁
縮短數(shù)據(jù)庫恢復(fù)時間，重做日志中記錄了的checkpoint的位置，這個點之前的頁已經(jīng)刷新回磁盤，只需要對checkpoint之后的重做日志進行恢復(fù)。這樣就大大縮短了恢復(fù)時間。

緩沖池不夠用時，根據(jù)LRU算法，溢出最近最少使用的頁，如果頁為臟頁，強制執(zhí)行checkpoint，將臟頁刷新回磁盤。

重做日志不可用，是指重做日志的這部分不可以被覆蓋，為什么？因為：由于重做日志的設(shè)計是循環(huán)使用的。這部分對應(yīng)的數(shù)據(jù)還未刷新到磁盤上。數(shù)據(jù)庫恢復(fù)時，如果不需要這部分日志，即可被覆蓋；如果需要，必須強制執(zhí)行checkpoint，將緩沖池中的頁至少刷新到當(dāng)前重做日志的位置。

checkpoint每次刷新多少頁到磁盤？每次從哪里取臟頁？什么時間觸發(fā)checkpoint？
InnoDB存儲引擎內(nèi)部，兩種checkpoint，分別為:

1、Sharp Checkpoint
2、Fuzzy Checkpoint
Sharp Checkpoint發(fā)生在數(shù)據(jù)庫關(guān)閉時，將所有的臟頁都刷新回磁盤，這是默認(rèn)的工作方式，即參數(shù)：innodb_fast_shutdown=1。

不適用于數(shù)據(jù)庫運行時的刷新。

在數(shù)據(jù)庫運行時，InnoDB存儲引擎內(nèi)部采用Fuzzy Checkpoint，只刷新一部分臟頁。

幾種發(fā)生Fuzzy Checkpoint的情況：

①MasterThread Checkpoint
異步刷新，每秒或每10秒從緩沖池臟頁列表刷新一定比例的頁回磁盤。異步刷新，即此時InnoDB存儲引擎可以進行其他操作，用戶查詢線程不會受阻。

②FLUSH_LRU_LIST Checkpoint

InnoDB存儲引擎需要保證LRU列表中差不多有100個空閑頁可供使用。在InnoDB 1.1.x版本之前，用戶查詢線程會檢查LRU列表是否有足夠的空間操作。如果沒有，根據(jù)LRU算法，溢出LRU列表尾端的頁，如果這些頁有臟頁，需要進行checkpoint。因此叫：flush_lru_list checkpoint。

InnoDB 1.2.x開始，這個檢查放在了單獨的進程（Page Cleaner）中進行。好處：
1.減少master Thread的壓力
2.減輕用戶線程阻塞。
設(shè)置參數(shù)：innodb_lru_scan_dept：控制LRU列表中可用頁的數(shù)量，該值默認(rèn)1024

③Async/Sync Flush Checkpoint

指重做日志不可用的情況，需要強制刷新頁回磁盤，此時的頁時臟頁列表選取的。

這種情況是保證重做日志的可用性，說白了就是，重做日志中可以循環(huán)覆蓋的部分空間太少了，換種說法，就是極短時間內(nèi)產(chǎn)生了大量的redo log。

接下來會有幾個變量，圖解也不難，仔細(xì)看看。

InnoDB存儲引擎，通過LSN（Log Sequence Number）來標(biāo)記版本，LSN是8字節(jié)的數(shù)字。每個頁有LSN，重做日志有LSN，checkpoint有LSN。寫入日志的LSN:redo_lsn刷新回磁盤的最新頁LSN:checkpoint_lsn

有如下定義:
checkpoint_age = redo_lsn - checkpoint_lsn
async_water_mark = 75% * total_redo_file_size
sync_water_mark = 90% * total_redo_file_size
刷新過程如下圖所示：

④Dirty Page too much Checkpoint
即臟頁太多，強制checkpoint.保證緩沖池有足夠可用的頁。
參數(shù)設(shè)置：innodb_max_dirty_pages_pct = 75 表示：當(dāng)緩沖池中臟頁的數(shù)量占75%時，強制checkpoint。1.0.x之后默認(rèn)75

InnoDB關(guān)鍵特性

插入緩沖

Insert Buffer是InnoDB存儲引擎關(guān)鍵特性中最令人激動與興奮的一個功能。不過這個名字可能會讓人認(rèn)為插入緩沖是緩沖池中的一個組成部分。其實不然，InnoDB緩沖池中有Insert Buffer信息固然不錯，但是Insert Buffer和數(shù)據(jù)頁一樣，也是物理頁的一個組成部分。

一般情況下，主鍵是行唯一的標(biāo)識符。通常應(yīng)用程序中行記錄的插入順序是按照主鍵遞增的順序進行插入的。因此，插入聚集索引一般是順序的，不需要磁盤的隨機讀取。因為，對于此類情況下的插入，速度還是非常快的。（如果主鍵類是UUID這樣的類，那么插入和輔助索引一樣，也是隨機的。）

如果索引是非聚集的且不唯一。在進行插入操作時，數(shù)據(jù)的存放對于非聚集索引葉子節(jié)點的插入不是順序的，這時需要離散地訪問非聚集索引頁，由于隨機讀取的存在而導(dǎo)致了插入操作性能下降。這是因為B+樹的特性決定了非聚集索引插入的離散性。

Insert Buffer的設(shè)計，對于非聚集索引的插入和更新操作，不是每一次直接插入到索引頁中，而是先判斷插入非聚集索引頁是否在緩沖池中，若存在，則直接插入，不存在，則先放入一個Insert Buffer對象中。數(shù)據(jù)庫這個非聚集的索引已經(jīng)插到葉子節(jié)點，而實際并沒有，只是存放在另一個位置。然后再以一定的頻率和情況進行Insert Buffer和輔助索引頁子節(jié)點的merge（合并）操作，這時通常能將多個插入合并到一個操作中（因為在一個索引頁中），這就大大提高了對于非聚集索引插入的性能。
需要滿足的兩個條件：

1、索引是輔助索引；
2、索引不是唯一的。
輔助索引不能是唯一的，因為在插入緩沖時，數(shù)據(jù)庫并不去查找索引頁來判斷插入的記錄的唯一性。如果去查找肯定又會有離散讀取的情況發(fā)生，從而導(dǎo)致Insert Buffer失去了意義。

兩次寫

如果說插入緩沖是為了提高寫性能的話，那么兩次寫是為了提高可靠性。

介紹兩次寫之前，說一下部分寫失效：
想象這么一個場景，當(dāng)數(shù)據(jù)庫正在從內(nèi)存向磁盤寫一個數(shù)據(jù)頁時，數(shù)據(jù)庫宕機，從而導(dǎo)致這個頁只寫了部分?jǐn)?shù)據(jù)，這就是部分寫失效，它會導(dǎo)致數(shù)據(jù)丟失。這時是無法通過重做日志恢復(fù)的，因為重做日志記錄的是對頁的物理修改，如果頁本身已經(jīng)損壞，重做日志也無能為力。

從上面分析我們知道，在部分寫失效的情況下，我們在應(yīng)用重做日志之前，需要原始頁的一個副本，兩次寫就是為了解決這個問題，下面是它的原理圖：

兩次寫需要額外添加兩個部分：
1）內(nèi)存中的兩次寫緩沖（doublewrite buffer），大小為2MB
2）磁盤上共享表空間中連續(xù)的128頁，大小也為2MB

其原理是這樣的：
1）當(dāng)刷新緩沖池臟頁時，并不直接寫到數(shù)據(jù)文件中，而是先拷貝至內(nèi)存中的兩次寫緩沖區(qū)。
2）接著從兩次寫緩沖區(qū)分兩次寫入磁盤共享表空間中，每次寫入1MB
3）待第2步完成后，再將兩次寫緩沖區(qū)寫入數(shù)據(jù)文件

這樣就可以解決上文提到的部分寫失效的問題，因為在磁盤共享表空間中已有數(shù)據(jù)頁副本拷貝，如果數(shù)據(jù)庫在頁寫入數(shù)據(jù)文件的過程中宕機，在實例恢復(fù)時，可以從共享表空間中找到該頁副本，將其拷貝覆蓋原有的數(shù)據(jù)頁，再應(yīng)用重做日志即可。

其中第2步是額外的性能開銷，但由于磁盤共享表空間是連續(xù)的，因此開銷不是很大?？梢酝ㄟ^參數(shù)skip_innodb_doublewrite禁用兩次寫功能，默認(rèn)是開啟的，強烈建議開啟該功能。

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

哈希是一種非常快的查找方法，在一般情況時間復(fù)雜度為O(1)。而B+樹的查找次數(shù)，取決于B+樹的高度，在生成環(huán)境中，B+樹的高度一般為3-4層，不需要查詢3-4次。

InnoDB存儲引擎會監(jiān)控對表上各索引頁的查詢。如果觀察到建立哈希索引可以提升速度，這簡歷哈希索引，稱之為自適應(yīng)哈希索引(Adaptive Hash Index, AHI)。AHI是通過緩沖池的B+樹頁構(gòu)造而來的。因此建立的速度非?？?，且不要對整張表構(gòu)建哈希索引。InnoDB存儲引擎會自動根據(jù)訪問的頻率和模式來自動的為某些熱點頁建立哈希索引。

AHI有一個要求，對這個頁的連續(xù)訪問模式(查詢條件)必須一樣的。例如聯(lián)合索引(a,b)其訪問模式可以有以下情況:

WHERE a=XXX;
WHERE a=xxx AND b=xxx。
若交替進行上述兩張查詢，InnoDB存儲引擎不會對該頁構(gòu)造AHI。此外AHI還有如下要求：
以該模式訪問了100次；
頁通過該模式訪問了N次，其中N=頁中記錄/16。
根據(jù)官方文檔顯示，啟用AHI后，讀取和寫入的速度可以提高2倍，負(fù)責(zé)索引的鏈接操作性能可以提高5倍。其設(shè)計思想是數(shù)據(jù)庫自由化的，無需DBA對數(shù)據(jù)庫進行人為調(diào)整。
異步IO(AIO)

為了提高磁盤操作性能，當(dāng)前的數(shù)據(jù)庫系統(tǒng)都采用異步IO的方式來處理磁盤操作。InnoDB也是如此。

與AIO對應(yīng)的是Sync IO，即每進行一次IO操作，需要等待此次操作結(jié)束才能繼續(xù)接下來的操作。但是如果用戶發(fā)出的是一條索引掃描的查詢，那么這條SQL語句可能需要掃描多個索引頁，也就是需要進行多次IO操作。在每掃描一個頁并等待其完成再進行下一次掃描，這是沒有必要的。用戶可以在發(fā)出一個IO請求后立即再發(fā)出另外一個IO請求，當(dāng)全部IO請求發(fā)送完畢后，等待所有IO操作完成，這就是AIO。

AIO的另外一個優(yōu)勢是進行IO Merge操作，也就是將多個IO合并為一個IO操作，這樣可以提高IOPS的性能。

在InnoDB 1.1.x之前，AIO的實現(xiàn)是通過InnoDB存儲引擎中的代碼來模擬的。但是從這之后，提供了內(nèi)核級別的AIO的支持，稱為Native AIO。Native AIO需要操作系統(tǒng)提供支持。Windows和Linux都支持，而Mac則未提供。在選擇MySQL數(shù)據(jù)庫服務(wù)器的操作系統(tǒng)時，需要考慮這方面的因素。

MySQL可以通過參數(shù)innodb_use_native_aio來決定是否啟用Native AIO。在InnoDB存儲引擎中，read ahead方式的讀取都是通過AIO完成，臟頁的刷新，也是通過AIO完成。

刷新鄰接頁

InnoDB存儲引擎在刷新一個臟頁時，會檢測該頁所在區(qū)(extent)的所有頁，如果是臟頁，那么一起刷新。這樣做的好處是通過AIO可以將多個IO寫操作合并為一個IO操作。該工作機制在傳統(tǒng)機械磁盤下有顯著優(yōu)勢。但是需要考慮下吧兩個問題:

是不是將不怎么臟的頁進行寫入，而該頁之后又會很快變成臟頁？

固態(tài)硬盤有很高IOPS，是否還需要這個特性？

為此InnoDB存儲引擎1.2.x版本開始提供參數(shù)innodb_flush_neighbors來決定是否啟用。對于傳統(tǒng)機械硬盤建議使用，而對于固態(tài)硬盤可以關(guān)閉。文章來源地址http://www.zghlxwxcb.cn/news/detail-800309.html

到了這里，關(guān)于【MYSQL】存儲引擎MyISAM和InnoDB的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！