《MYSQL數(shù)據(jù)庫(kù)mysql innodb數(shù)據(jù)庫(kù)引擎解析》要點(diǎn)：
本文介紹了MYSQL數(shù)據(jù)庫(kù)mysql innodb數(shù)據(jù)庫(kù)引擎解析，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

MYSQL實(shí)例本節(jié)內(nèi)容：
mysql innodb數(shù)據(jù)庫(kù)引擎

MYSQL實(shí)例一．mysql體系結(jié)構(gòu)和存儲(chǔ)引擎

MYSQL實(shí)例1.1、數(shù)據(jù)庫(kù)和實(shí)例的區(qū)別

MYSQL實(shí)例數(shù)據(jù)庫(kù)：物理操作系統(tǒng)或其他形式文件類型的集合.在mysql下數(shù)據(jù)庫(kù)文件可以是frm,myd,myi,ibd結(jié)尾的文件.

MYSQL實(shí)例數(shù)據(jù)庫(kù)實(shí)例：由數(shù)據(jù)庫(kù)后臺(tái)進(jìn)程/線程以及一個(gè)共享內(nèi)存區(qū)組成.數(shù)據(jù)庫(kù)實(shí)例才是真正用來(lái)操作數(shù)據(jù)庫(kù)文件的.

MYSQL實(shí)例mysql數(shù)據(jù)庫(kù)是單進(jìn)程多線程的程序,與sql server比較類似.也就是說(shuō),Mysql數(shù)據(jù)庫(kù)實(shí)例在系統(tǒng)上的表現(xiàn)就是一個(gè)進(jìn)程.

MYSQL實(shí)例1.2、mysql的體系結(jié)構(gòu)

MYSQL實(shí)例mysql由連接池組件、管理服務(wù)和工具組件、sql接口組建、查詢分析器組件、優(yōu)化器組件、緩存組件、插件是存儲(chǔ)引擎、物理文件.

MYSQL實(shí)例1.3、mysql存儲(chǔ)引擎

MYSQL實(shí)例1.3.1、innodb存儲(chǔ)引擎,特點(diǎn)支持外鍵、行鎖、非鎖定讀(默認(rèn)情況下讀取不會(huì)產(chǎn)生鎖)、mysql-4.1開(kāi)始支持每個(gè)innodb引擎的表單獨(dú)放到一個(gè)表空間里.innodb通過(guò)使用MVCC來(lái)獲取高并發(fā)性,并且實(shí)現(xiàn)sql標(biāo)準(zhǔn)的4種隔離級(jí)別,同時(shí)使用一種被稱成next-key locking的策略來(lái)避免換讀(phantom)現(xiàn)象.除此之外innodb引擎還提供了插入緩存(insert buffer)、二次寫(double write)、自適應(yīng)哈西索引(adaptive hash index)、預(yù)讀(read ahead)等高性能技術(shù).

MYSQL實(shí)例1.3.2、myisam存儲(chǔ)引擎,myisam特點(diǎn)是不支持事物,適合olap應(yīng)用,myisam表由MYD和MYI組成.mysql-5.0版本之前,myisam默認(rèn)支持的表大小為4G,從mysql-5.0以后,myisam默認(rèn)支持256T的表單數(shù)據(jù).myisam只緩存索引數(shù)據(jù).

MYSQL實(shí)例1.3.3、NDB存儲(chǔ)引擎,特點(diǎn)是數(shù)據(jù)放在內(nèi)存中,mysql-5.1版本開(kāi)始可以將非索引數(shù)據(jù)放到磁盤上.NDB之前的缺陷是join查詢是mysql數(shù)據(jù)庫(kù)層完成的,而不是存儲(chǔ)引擎完成的,復(fù)雜的join查詢需要巨大的網(wǎng)絡(luò)開(kāi)銷,速度很慢.當(dāng)前mysql cluster7.2版本中已經(jīng)解決此問(wèn)題,join查詢效率提高了70倍.

MYSQL實(shí)例1.3.4、memeory存儲(chǔ)引擎,將數(shù)據(jù)放到內(nèi)存中,默認(rèn)使用hash索引,不支持text和blob類型,varchara是按照char的方式來(lái)存儲(chǔ)的.mysql數(shù)據(jù)庫(kù)使用memory存儲(chǔ)引擎作為臨時(shí)表還存儲(chǔ)中間結(jié)果集(intermediate result),如果中間集結(jié)果大于memorg表的容量設(shè)置,又或者中間結(jié)果集包含text和blog列類型字段,則mysql會(huì)把他們轉(zhuǎn)換到myisam存儲(chǔ)引擎表而放到磁盤上,會(huì)對(duì)查詢產(chǎn)生性能影響.

MYSQL實(shí)例1.3.5、archive存儲(chǔ)引擎,壓縮能力較強(qiáng),主要用于歸檔存儲(chǔ).

MYSQL實(shí)例1.3.6、federated存儲(chǔ)引擎,不存儲(chǔ)數(shù)據(jù),他指向一臺(tái)遠(yuǎn)程mysql數(shù)據(jù)庫(kù)上的表.

MYSQL實(shí)例1.3.7、maria存儲(chǔ)引擎,myisam的后續(xù)版本,支持緩存數(shù)據(jù)和索引,行鎖設(shè)計(jì),支持mvcc,支持事務(wù)和非事務(wù)安全的選項(xiàng),以及更好的BLOG字符類型的處理性能.

MYSQL實(shí)例1.3.8、其他存儲(chǔ)引擎,sphinx用于全文索引,infobright用于數(shù)據(jù)倉(cāng)庫(kù).

MYSQL實(shí)例1.4連接Mysql

MYSQL實(shí)例1.4.1、TCP/IP：基于網(wǎng)絡(luò)的連接,連接進(jìn)行權(quán)限檢查.

MYSQL實(shí)例1.4.2、命名管道和共享內(nèi)存：Windows系統(tǒng)上同一服務(wù)器上的兩進(jìn)程可通過(guò)命名管道連接,需在配置文件中啟用--enable-named-pipe選項(xiàng).

MYSQL實(shí)例1.4.3、Unix套接字：客戶端與服務(wù)端位于同一服務(wù)器時(shí)才可使用,可以在my.cnf中指定-socket=/tmp/mysql.sock,連接時(shí)指定./mysql -S/tmp/mysql.sock.

MYSQL實(shí)例二．InnoDB存儲(chǔ)引擎

MYSQL實(shí)例2.2、innodb引擎架構(gòu)
www.jbxue.com
InnoDB的多個(gè)內(nèi)存塊組成了內(nèi)存池,負(fù)責(zé)如下工作：

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

MYSQL實(shí)例2).緩存磁盤上的數(shù)據(jù),方便快速的讀取,并且在對(duì)磁盤文件的數(shù)據(jù)進(jìn)行修改之前在這里緩存.

MYSQL實(shí)例3).重做日志緩存.

MYSQL實(shí)例后臺(tái)線程的主要作用是負(fù)責(zé)刷新內(nèi)存池中的數(shù)據(jù),保證緩沖池中的內(nèi)存緩存是最近的數(shù)據(jù),此外、將已經(jīng)修改的數(shù)據(jù)文件刷新到磁盤文件

MYSQL實(shí)例2.2.1、后臺(tái)線程

MYSQL實(shí)例innodb存儲(chǔ)引擎后臺(tái)有7個(gè)線程,—–4個(gè)IO線程(insert buffer thread,log thread,read thread,write thread),1個(gè)master thread,一個(gè)lock監(jiān)控線程,一個(gè)錯(cuò)誤監(jiān)控線程.

MYSQL實(shí)例2.2.2、內(nèi)存

MYSQL實(shí)例innodb存儲(chǔ)引擎內(nèi)存由以下三個(gè)部分組成：緩沖池(buffer pool),重做日志緩存(redo log buffer),額外的內(nèi)存池(additional memory pool).可以使用 show engine innodb status來(lái)查看innodb_buffer_pool的使用情況.

MYSQL實(shí)例innodb_buffer_pool_size：具體看,緩沖池中的數(shù)據(jù)庫(kù)類型有：索引頁(yè)、數(shù)據(jù)庫(kù)頁(yè)、undo頁(yè)、插入緩存頁(yè)(insert buffer)、自適應(yīng)hash(adaptive hashindex)、innodb存儲(chǔ)的鎖信息(lock info)、數(shù)據(jù)字典信息(data dictionary).

MYSQL實(shí)例InnoDB工作方式：將數(shù)據(jù)文件按頁(yè)(每頁(yè)16K)讀入InnoDBbuffer pool,然后按最近最少使用算法(LRU)保留緩存數(shù)據(jù),最后通過(guò)一定頻率將臟頁(yè)刷新到文件.

MYSQL實(shí)例

MYSQL實(shí)例
2.3、master thread

MYSQL實(shí)例2.3.1、master thread源碼分析
">

MYSQL實(shí)例2.3.2、master thread的潛在問(wèn)題

MYSQL實(shí)例1、由于硬件的發(fā)展,現(xiàn)在的硬件性能已經(jīng)提高了很多,如果innodb每秒最大刷新100個(gè)臟頁(yè),那么效率會(huì)很低,為了解決這個(gè)問(wèn)題,innodb plugin提供了一個(gè)參數(shù)innodb_io_capacity,用來(lái)表示磁盤IO的吞吐量,默認(rèn)值是200,規(guī)則如下：在合并插入緩存時(shí),合并插入緩存的數(shù)量為innodb_io_capacity的5%；在從緩沖區(qū)刷新臟頁(yè)時(shí),啥新臟頁(yè)的數(shù)量為innodb_io_capacity.

MYSQL實(shí)例2、關(guān)于innodb_max_dirty_pages_pct值的爭(zhēng)議,如果值過(guò)大,內(nèi)存也很大或者服務(wù)器壓力很大,那么效率很降低,如果設(shè)置的值過(guò)小,那么硬盤的壓力會(huì)增加,建議是在75-80.并且innodb plugin引進(jìn)了innodb_adaptive_flushng(自適應(yīng)的刷新),該值影響每秒刷新臟頁(yè)的數(shù)量.

MYSQL實(shí)例2.4、關(guān)鍵特性,為innodb提高性能的技術(shù)

MYSQL實(shí)例2.4.1、插入緩存

MYSQL實(shí)例當(dāng)一個(gè)表有非聚集索引時(shí),對(duì)于非聚集索引的葉子節(jié)點(diǎn)的插入不是順序的,這時(shí)候需要離散的訪問(wèn)非聚集索引頁(yè),性能就在這里降低了,這是由于b+樹(shù)的原理導(dǎo)致的.插入緩存就是用來(lái)解決這個(gè)問(wèn)題的.

MYSQL實(shí)例對(duì)于非聚集索引的插入和更新操作,不是每一次都直接插入索引頁(yè),而是先判斷插入的非聚集索引頁(yè)是否在緩存中,如果在就直接插入,如果不在就放入到一個(gè)插入緩沖區(qū)中,好似欺騙數(shù)據(jù)庫(kù)這個(gè)非聚集索引已經(jīng)插入到葉子節(jié)點(diǎn)了.然后再以一定的頻率插入緩存和非聚集索引頁(yè)字節(jié)點(diǎn)的合并操作.

MYSQL實(shí)例插入緩存的使用需要滿足以下兩個(gè)條件(也就是非唯一的輔助索引)：索引是輔助索引；索引不是唯一的.

MYSQL實(shí)例2.4.2、兩次寫

MYSQL實(shí)例兩次寫給innodb帶來(lái)的是可靠性,主要用來(lái)解決部分寫失敗(partial page write).在應(yīng)用重做日之前,我們需要一個(gè)頁(yè)的副本,當(dāng)寫入失效發(fā)生時(shí),先通過(guò)頁(yè)的副本來(lái)還原該頁(yè),再進(jìn)行重做,這就是doublewrite.

MYSQL實(shí)例doublewrite有兩部分組成,一部分是內(nèi)存中的doublewrite buffer,大小為2M,另外一部分就是物理磁盤上的共享表空間中聯(lián)系的128個(gè)頁(yè),即兩個(gè)區(qū),大小同樣為2M.當(dāng)緩沖池的張也刷新時(shí),并不直接寫硬盤,而是回通過(guò)memcpy函數(shù)將臟頁(yè)先拷貝到內(nèi)存中的doublewrite buffer,之后通過(guò)doublewrite buffer再分兩次寫,每次寫入1M到共享表空間的物理磁盤上,然后馬上調(diào)用fsync函數(shù),同步磁盤.

MYSQL實(shí)例2.4.3、自適應(yīng)哈西索引

MYSQL實(shí)例由于innodb不支持hash索引,但是在某些情況下hash索引的效率很高,于是出現(xiàn)了 adaptive hash index功能,innodb存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上索引的查找,如果觀察到建立hash索引可以提高性能的時(shí)候,則自動(dòng)建立hash索引.

MYSQL實(shí)例2.5、啟動(dòng)、關(guān)閉、恢復(fù)
innodb_fast_shutdown影響InnoDB表關(guān)閉.該參數(shù)有0、1、2三個(gè)參數(shù).

MYSQL實(shí)例0 MySQL關(guān)閉時(shí) 完成所有的full purge和merge insertbuffer操作

MYSQL實(shí)例1默認(rèn)值 只將緩沖池內(nèi)的一些臟頁(yè)刷新至磁盤

MYSQL實(shí)例2將日志都寫入日志文件不會(huì)有任何事務(wù)丟失但下次啟動(dòng)時(shí)會(huì)進(jìn)行recovery
innodb_force_recovery影響整個(gè)innodb存儲(chǔ)引擎的恢復(fù)狀況,該值默認(rèn)為0,表示當(dāng)需要恢復(fù)時(shí),需要執(zhí)行所有的恢復(fù)操作,當(dāng)不能進(jìn)行有效恢復(fù)時(shí),如數(shù)據(jù)頁(yè)發(fā)生了corruption,mysql數(shù)據(jù)庫(kù)可能宕機(jī),并把錯(cuò)誤寫入錯(cuò)誤日志中.

MYSQL實(shí)例三．文件

MYSQL實(shí)例3.1參數(shù)文件
Mysql實(shí)例可以不需要參數(shù)文件,這是所有的參數(shù)值取決于編譯Mysql時(shí)指定的默認(rèn)值和源代碼中指定參數(shù)的默認(rèn)值.其參數(shù)文件是Mysql.cnf.

MYSQL實(shí)例3.1.1、什么是參數(shù)
參數(shù)是一個(gè)鍵/值對(duì).可以使用show variables like命令查看,也可以通過(guò)information_schema的GLOBAL_VARIABLES視圖來(lái)查找.

MYSQL實(shí)例3.1.2、參數(shù)類型
參數(shù)文件分為兩類：動(dòng)態(tài)參數(shù)和靜態(tài)參數(shù).動(dòng)態(tài)參數(shù)意味著你可以在Mysql實(shí)例運(yùn)行中進(jìn)行更改；靜態(tài)參數(shù)說(shuō)明在整個(gè)實(shí)例生命周期內(nèi)都不得進(jìn)行更改,好像是只讀的.對(duì)于動(dòng)態(tài)參數(shù),又可以分為global和session關(guān)鍵字,表明該參數(shù)的修改是基于當(dāng)前會(huì)話還是真格實(shí)例的生命周期.有些動(dòng)態(tài)參數(shù)只能在會(huì)話中進(jìn)行修改,如autocommit；有些參數(shù)修改完后,在整個(gè)實(shí)例生命周期中都會(huì)生效,如binlog_cache_size；而有些參數(shù)既可以在會(huì)話又可以在整個(gè)實(shí)例的生命周期內(nèi)生效,如read_buffer_size.

MYSQL實(shí)例3.2、日志文件

MYSQL實(shí)例3.2.1、錯(cuò)誤日志
錯(cuò)誤日志對(duì)Mysql的啟動(dòng)、運(yùn)行、關(guān)閉過(guò)程進(jìn)行了記錄.出現(xiàn)Mysql不能正常啟動(dòng)時(shí),第一個(gè)必須查找的文件應(yīng)該就是錯(cuò)誤日志文件.使用show variables like ‘log_error’來(lái)定位文件.

MYSQL實(shí)例3.2.2、慢查詢?nèi)罩?br>慢查詢能為SQL語(yǔ)句的優(yōu)化帶來(lái)很好的幫助.設(shè)定一個(gè)閥值,將運(yùn)行時(shí)間超過(guò)該值的所有SQL語(yǔ)句都記錄到慢查詢?nèi)罩疚募?用參數(shù)long_query_time來(lái)設(shè)置.另一個(gè)參數(shù)log_queries_not_using_indexes,若運(yùn)行的SQL語(yǔ)句沒(méi)有使用索引,則這條SQL語(yǔ)句會(huì)被記錄下來(lái).

MYSQL實(shí)例3.2.3、查詢?nèi)罩?br>查詢?nèi)罩居涗浟怂袑?duì)Mysql請(qǐng)求的信息,不論這些請(qǐng)求是否得到正確的執(zhí)行.默認(rèn)文件名為：主機(jī)名.log.

MYSQL實(shí)例3.2.4、二進(jìn)制日志
二進(jìn)制記錄了對(duì)數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作,但是不包括SELECT和SHOW操作,還包括了執(zhí)行時(shí)間和更改操作時(shí)間.可用來(lái)恢復(fù)某些數(shù)據(jù),同時(shí)也可以用來(lái)復(fù)制同步遠(yuǎn)程數(shù)據(jù)庫(kù).將binlog_format設(shè)置成row,可以支持事務(wù)隔離級(jí)別為READ COMMITTED,以獲得更好的并發(fā)性.在使用MIXED格式下,mysql采用STATEMENT格式進(jìn)行二進(jìn)制日志文件的記錄,但是有一些情況下會(huì)使用ROW格式,可能的情況：
1、表的存儲(chǔ)引擎為NDB,這個(gè)時(shí)候DML操作都會(huì)以ROW格式記錄.
2、使用了uuid()、user(),current_user(),found_rows(),row_count(),等不確定函數(shù).
3、使用了insert delay語(yǔ)句
4、使用了用戶定于的函數(shù)(UDF)
5、使用了臨時(shí)表(temporary table)

MYSQL實(shí)例注意：針對(duì)系統(tǒng)庫(kù)mysql里面的表發(fā)生變化的處理規(guī)則如下：
1、 如果采用insert,update,delete直接操作表,則日志根據(jù)binlog_format設(shè)定的格式記錄.
2、 如果使用grant,revoke,set password等DCL語(yǔ)句,那么無(wú)論如何都會(huì)使用SBR模式記錄.
3、 blockhole引擎不支持row格式,ndb引擎不支持statement格式.

MYSQL實(shí)例3.3、套件字文件
Unix系統(tǒng)下本地連接Mysql可以采用Unix套接字方法,需要一個(gè)套接字文件,可以使用show variableslike ‘socket’查詢.

MYSQL實(shí)例3.4、pid文件和表結(jié)構(gòu)定義文件

MYSQL實(shí)例pid文件是實(shí)例啟動(dòng)是記錄自己進(jìn)程ID號(hào)的文件,表結(jié)構(gòu)定義文件是以frm為后綴名的文件,還可以用來(lái)存放視圖的定義.

MYSQL實(shí)例3.5、innodb引擎文件

MYSQL實(shí)例3.5.1、表空間文件
默認(rèn)表空間文件為ibdata1文件innodb_data_file_path存儲(chǔ)數(shù)據(jù),innodb_file_per_table可以按表分別產(chǎn)生一個(gè)表空間.db文件,但僅存該表的數(shù)據(jù)索引和插入緩沖等信息,其他信息如undo信息,系統(tǒng)事務(wù)信息,double write buffer等還是存放在默認(rèn)表空間(ibdata1或表空間組)里.

MYSQL實(shí)例3.5.2、重做日志文件
redo log是在實(shí)例或者介質(zhì)失敗的時(shí)候,用來(lái)保證數(shù)據(jù)完整性.每個(gè)innodb存儲(chǔ)引擎至少有一個(gè)重做日志組,每個(gè)重做日志文件組下至少又2個(gè)重做日志文件,如默認(rèn)的ib_logfile0、ib_logfile1.為了得到更高的可靠性,你可以設(shè)置多個(gè)重做鏡像日志組.

MYSQL實(shí)例因?yàn)橹刈鋈罩緱l目先被寫到日志緩沖中,然后根據(jù)一定條件刷新到磁盤重做日志文件中.與redo log相關(guān)的就是innodb_flush_log_at_trx_commit的值,對(duì)innodb的性能影響很大.他有0,1,2三個(gè)值,0代表提交事務(wù)時(shí),并不同步寫redo log,而是等master threas每秒寫.1代表commit的時(shí)候就將redo log緩存寫入磁盤,2代表commit的時(shí)候?qū)edo log緩存異步的寫入磁盤.

MYSQL實(shí)例就是這些了,有關(guān)mysql innodb數(shù)據(jù)庫(kù)引擎的詳細(xì)介紹,希望對(duì)大家有所幫助.

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