《MYSQL數(shù)據(jù)庫InnoDB的關(guān)鍵特性-插入緩存,兩次寫,自適應(yīng)hash索引詳解》要點(diǎn)：
本文介紹了MYSQL數(shù)據(jù)庫InnoDB的關(guān)鍵特性-插入緩存,兩次寫,自適應(yīng)hash索引詳解，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

MYSQL教程InnoDB存儲(chǔ)引擎的關(guān)鍵特性包括插入緩沖、兩次寫(double write)、自適應(yīng)哈希索引(adaptive hash index).這些特性為InnoDB存儲(chǔ)引擎帶來了更好的性能和更高的可靠性.

MYSQL教程插入緩沖

MYSQL教程插入緩沖是InnoDB存儲(chǔ)引擎關(guān)鍵特性中最令人激動(dòng)的.不過,這個(gè)名字可能會(huì)讓人認(rèn)為插入緩沖是緩沖池中的一個(gè)部分.其實(shí)不然,InnoDB緩沖池中有Insert Buffer信息固然不錯(cuò),但是Insert Buffer和數(shù)據(jù)頁一樣,也是物理頁的一個(gè)組成部分.

MYSQL教程主鍵是行唯一的標(biāo)識(shí)符,在應(yīng)用程序中行記錄的插入順序是按照主鍵遞增的順序進(jìn)行插入的.因此,插入聚集索引一般是順序的,不需要磁盤的隨機(jī)讀取.

MYSQL教程比如說我們按下列SQL定義的表：create table t(id int auto_increment,name varchar(30),primary key(id));

MYSQL教程id列是自增長的,這意味著當(dāng)執(zhí)行插入操作時(shí),id列會(huì)自動(dòng)增長,頁中的行記錄按id執(zhí)行順序存放.一般情況下,不需要隨機(jī)讀取另一頁執(zhí)行記錄的存放.因此,在這樣的情況下,插入操作一般很快就能完成.但是,不可能每張表上只有一個(gè)聚集索引,在更多的情況下,一張表上有多個(gè)非聚集的輔助索引(secondary index).比如,我們還需要按照name這個(gè)字段進(jìn)行查找,并且name這個(gè)字段不是唯一的.

MYSQL教程表是按如下的SQL語句定義的：create table t (id int auto_increment,name varchar(30),primary key(id),key(name));

MYSQL教程這樣的情況下產(chǎn)生了一個(gè)非聚集的并且不是唯一的索引.在進(jìn)行插入操作時(shí),數(shù)據(jù)頁的存放還是按主鍵id的執(zhí)行順序存放,但是對(duì)于非聚集索引,葉子節(jié)點(diǎn)的插入不再是順序的了.這時(shí)就需要離散地訪問非聚集索引頁,插入性能在這里變低了.然而這并不是這個(gè)name字段上索引的錯(cuò)誤,因?yàn)锽+樹的特性決定了非聚集索引插入的離散性.

MYSQL教程InnoDB存儲(chǔ)引擎開創(chuàng)性地設(shè)計(jì)了插入緩沖,對(duì)于非聚集索引的插入或更新操作,不是每一次直接插入索引頁中,而是先判斷插入的非聚集索引頁是否在緩沖池中.如果在,則直接插入；如果不在,則先放入一個(gè)插入緩沖區(qū)中,好似欺騙數(shù)據(jù)庫這個(gè)非聚集的索引已經(jīng)插到葉子節(jié)點(diǎn)了,然后再以一定的頻率執(zhí)行插入緩沖和非聚集索引頁子節(jié)點(diǎn)的合并操作,這時(shí)通常能將多個(gè)插入合并到一個(gè)操作中(因?yàn)樵谝粋€(gè)索引頁中),這就大大提高了對(duì)非聚集索引執(zhí)行插入和修改操作的性能.

MYSQL教程插入緩沖的使用需要滿足以下兩個(gè)條件：

MYSQL教程1.索引是輔助索引.

MYSQL教程2.索引不是唯一的.

MYSQL教程當(dāng)滿足以上兩個(gè)條件時(shí),InnoDB存儲(chǔ)引擎會(huì)使用插入緩沖,這樣就能提高性能了.不過考慮一種情況,應(yīng)用程序執(zhí)行大量的插入和更新操作,這些操作都涉及了不唯一的非聚集索引,如果在這個(gè)過程中數(shù)據(jù)庫發(fā)生了宕機(jī),這時(shí)候會(huì)有大量的插入緩沖并沒有合并到實(shí)際的非聚集索引中.如果是這樣,恢復(fù)可能需要很長的時(shí)間,極端情況下甚至需要幾個(gè)小時(shí)來執(zhí)行合并恢復(fù)操作.

MYSQL教程輔助索引不能是唯一的,因?yàn)樵诎阉迦氲讲迦刖彌_時(shí),我們并不去查找索引頁的情況.如果去查找肯定又會(huì)出現(xiàn)離散讀的情況,插入緩沖就失去了意義.

MYSQL教程查看插入緩沖的信息：

MYSQL教程show engine innodb status\G

MYSQL教程

MYSQL教程seg size顯示了當(dāng)前插入緩沖的大小為2*16KB,free list len代表了空閑列表的長度,size代表了已經(jīng)合并記錄頁的數(shù)量.

MYSQL教程下面一行可能是我們真正關(guān)心的,因?yàn)樗@示了提高性能了.inserts代表插入的記錄數(shù),merged recs代表合并的頁的數(shù)量,merges代表合并的次數(shù).

MYSQL教程merged recs:merges大約為3:1,代表插入緩沖將對(duì)于非聚集索引頁的IO請(qǐng)求大約降低了3倍.

MYSQL教程問題：

MYSQL教程目前插入緩沖存在一個(gè)問題是,在寫密集的情況下,插入緩沖會(huì)占用過多的緩沖池內(nèi)存,默認(rèn)情況下最大可以占用1/2的緩沖池內(nèi)存.Percona已發(fā)布一些patch來修正插入緩沖占用太多緩沖池內(nèi)存的問題,具體的可以到http：//www.percona.com/percona-lab.html查找.簡單來說,修改IBUF_POOL_SIZE_PER_MAX_SIZE就可以對(duì)插入緩沖的大小進(jìn)行控制,例如,將IBUF_POOL_SIZE_PER_MAX_SIZE改為3,則最大只能使用1/3的緩沖池內(nèi)存.

MYSQL教程兩次寫

MYSQL教程如果說插入緩沖帶給InnoDB存儲(chǔ)引擎的是性能,那么兩次寫帶給InnoDB存儲(chǔ)引擎的是數(shù)據(jù)的可靠性.當(dāng)數(shù)據(jù)庫宕機(jī)時(shí),可能發(fā)生數(shù)據(jù)庫正在寫一個(gè)頁面,而這個(gè)頁只寫了一部分(比如16K的頁,只寫前4K的頁)的情況,我們稱之為部分寫失效(partial page write).在InnoDB存儲(chǔ)引擎未使用double write技術(shù)前,曾出現(xiàn)過因?yàn)椴糠謱懯Ф鴮?dǎo)致數(shù)據(jù)丟失的情況.

MYSQL教程有人也許會(huì)想,如果發(fā)生寫失效,可以通過重做日志進(jìn)行恢復(fù).這是一個(gè)辦法.但是必須清楚的是,重做日志中記錄的是對(duì)頁的物理操作,如偏移量800,寫'aaaa'記錄.如果這個(gè)頁本身已經(jīng)損壞,再對(duì)其進(jìn)行重做是沒有意義的.這就是說,在應(yīng)用(apply)重做日志前,我們需要一個(gè)頁的副本,當(dāng)寫入失效發(fā)生時(shí),先通過頁的副本來還原該頁,再進(jìn)行重做,這就是doublewrite.

MYSQL教程InnoDB存儲(chǔ)引擎doublewrite的體系架構(gòu)如圖2-4所示

MYSQL教程

MYSQL教程doublewrite由兩部分組成：一部分是內(nèi)存中的doublewrite buffer,大小為2MB；另一部分是物理磁盤上共享表空間中連續(xù)的128個(gè)頁,即兩個(gè)區(qū)(extent),大小同樣為2MB(頁的副本).當(dāng)緩沖池的臟頁刷新時(shí),并不直接寫磁盤,而是會(huì)通過memcpy函數(shù)將臟頁先拷貝到內(nèi)存中的doublewrite buffer,之后通過doublewrite buffer再分兩次,每次寫入1MB到共享表空間的物理磁盤上,然后馬上調(diào)用fsync函數(shù),同步磁盤,避免緩沖寫帶來的問題.在這個(gè)過程中,因?yàn)閐oublewrite頁是連續(xù)的,因此這個(gè)過程是順序?qū)懙?開銷并不是很大.在完成doublewrite頁的寫入后,再將doublewrite buffer中的頁寫入各個(gè)表空間文件中,此時(shí)的寫入則是離散的.

MYSQL教程可以通過以下命令觀察到doublewrite運(yùn)行的情況：?show global status like 'innodb_dblwr%'\G

MYSQL教程

MYSQL教程doublewrite一共寫了18 445個(gè)頁,但實(shí)際的寫入次數(shù)為434,(42:1) ? 基本上符合64:1.

MYSQL教程如果發(fā)現(xiàn)你的系統(tǒng)在高峰時(shí)Innodb_dblwr_pages_written:Innodb_dblwr_writes遠(yuǎn)小于64:1,那么說明你的系統(tǒng)寫入壓力并不是很高.

MYSQL教程如果操作系統(tǒng)在將頁寫入磁盤的過程中崩潰了,在恢復(fù)過程中,InnoDB存儲(chǔ)引擎可以從共享表空間中的doublewrite中找到改頁的一個(gè)副本,將其拷貝到表空間文件,再應(yīng)用重做日志.下面顯示了由doublewrite進(jìn)行恢復(fù)的一種情況：?

MYSQL教程
090924 11：36：32 mysqld restarted
090924 11：36：33 InnoDB：Database was not shut down normally!
InnoDB：Starting crash recovery.
InnoDB：Reading tablespace information from the.ibd files……
InnoDB：Error：space id in fsp header 0,but in the page header 4294967295
InnoDB：Error：tablespace id 4294967295 in file./test/t.ibd is not sensible
InnoDB：Error：tablespace id 0 in file./test/t2.ibd is not sensible
090924 11：36：33 InnoDB：Operating system error number 40 in a file operation.
InnoDB：Error number 40 means'Too many levels of symbolic links'.
InnoDB：Some operating system error numbers are described at
InnoDB：http：//dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
InnoDB：File name./now/member
InnoDB：File operation call：'stat'.
InnoDB：Error：os_file_readdir_next_file()returned-1 in
InnoDB：directory./now
InnoDB：Crash recovery may have failed for some.ibd files!
InnoDB：Restoring possible half-written data pages from the doublewrite
InnoDB：buffer……

MYSQL教程參數(shù)skip_innodb_doublewrite可以禁止使用兩次寫功能,這時(shí)可能會(huì)發(fā)生前面提及的寫失效問題.不過,如果你有多臺(tái)從服務(wù)器(slave server),需要提供較快的性能(如slave上做的是RAID0),也許啟用這個(gè)參數(shù)是一個(gè)辦法.不過,在需要提供數(shù)據(jù)高可靠性的主服務(wù)器(master server)上,任何時(shí)候我們都應(yīng)確保開啟兩次寫功能.

MYSQL教程注意：有些文件系統(tǒng)本身就提供了部分寫失效的防范機(jī)制,如ZFS文件系統(tǒng).在這種情況下,我們就不要啟用doublewrite了.?

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

MYSQL教程哈希(hash)是一種非常快的查找方法,一般情況下查找的時(shí)間復(fù)雜度為O(1).常用于連接(join)操作,如SQL Server和Oracle中的哈希連接(hash join).但是SQL Server和Oracle等常見的數(shù)據(jù)庫并不支持哈希索引(hash index).MySQL的Heap存儲(chǔ)引擎默認(rèn)的索引類型為哈希,而InnoDB存儲(chǔ)引擎提出了另一種實(shí)現(xiàn)方法,自適應(yīng)哈希索引(adaptive hash index).

MYSQL教程InnoDB存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上索引的查找,如果觀察到建立哈希索引可以帶來速度的提升,則建立哈希索引,所以稱之為自適應(yīng)(adaptive)的.自適應(yīng)哈希索引通過緩沖池的B+樹構(gòu)造而來,因此建立的速度很快.而且不需要將整個(gè)表都建哈希索引,InnoDB存儲(chǔ)引擎會(huì)自動(dòng)根據(jù)訪問的頻率和模式來為某些頁建立哈希索引.

MYSQL教程根據(jù)InnoDB的官方文檔顯示,啟用自適應(yīng)哈希索引后,讀取和寫入速度可以提高2倍；對(duì)于輔助索引的連接操作,性能可以提高5倍.自適應(yīng)哈希索引是非常好的優(yōu)化模式,其設(shè)計(jì)思想是數(shù)據(jù)庫自優(yōu)化(self-tuning),即無需DBA對(duì)數(shù)據(jù)庫進(jìn)行調(diào)整.

MYSQL教程查看當(dāng)前自適應(yīng)哈希索引的使用狀況：show engine innodb status\G

MYSQL教程

MYSQL教程

MYSQL教程現(xiàn)在可以看到自適應(yīng)哈希索引的使用信息了,包括自適應(yīng)哈希索引的大小、使用情況、每秒使用自適應(yīng)哈希索引搜索的情況.值得注意的是,哈希索引只能用來搜索等值的查詢,如select * from table where index_col='xxx',而對(duì)于其他查找類型,如范圍查找,是不能使用的.因此,這里出現(xiàn)了non-hash searches/s的情況.用hash searches:non-hash searches命令可以大概了解使用哈希索引后的效率.

MYSQL教程由于自適應(yīng)哈希索引是由InnoDB存儲(chǔ)引擎控制的,所以這里的信息只供我們參考.不過我們可以通過參數(shù)innodb_adaptive_hash_index來禁用或啟動(dòng)此特性,默認(rèn)為開啟.

MYSQL教程以上這篇InnoDB的關(guān)鍵特性-插入緩存,兩次寫,自適應(yīng)hash索引詳解就是小編分享給大家的全部內(nèi)容了,希望能給大家一個(gè)參考,也希望大家多多支持維易PHP.

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/1443.html