《MYSQL數據庫mysql innodb數據庫引擎解析》要點：
本文介紹了MYSQL數據庫mysql innodb數據庫引擎解析，希望對您有用。如果有疑問，可以聯系我們。

MYSQL實例本節內容：
mysql innodb數據庫引擎

MYSQL實例一．mysql體系結構和存儲引擎

MYSQL實例1.1、數據庫和實例的區別

MYSQL實例數據庫：物理操作系統或其他形式文件類型的集合.在mysql下數據庫文件可以是frm,myd,myi,ibd結尾的文件.

MYSQL實例數據庫實例：由數據庫后臺進程/線程以及一個共享內存區組成.數據庫實例才是真正用來操作數據庫文件的.

MYSQL實例mysql數據庫是單進程多線程的程序,與sql server比較類似.也就是說,Mysql數據庫實例在系統上的表現就是一個進程.

MYSQL實例1.2、mysql的體系結構

MYSQL實例mysql由連接池組件、管理服務和工具組件、sql接口組建、查詢分析器組件、優化器組件、緩存組件、插件是存儲引擎、物理文件.

MYSQL實例1.3、mysql存儲引擎

MYSQL實例1.3.1、innodb存儲引擎,特點支持外鍵、行鎖、非鎖定讀(默認情況下讀取不會產生鎖)、mysql-4.1開始支持每個innodb引擎的表單獨放到一個表空間里.innodb通過使用MVCC來獲取高并發性,并且實現sql標準的4種隔離級別,同時使用一種被稱成next-key locking的策略來避免換讀(phantom)現象.除此之外innodb引擎還提供了插入緩存(insert buffer)、二次寫(double write)、自適應哈西索引(adaptive hash index)、預讀(read ahead)等高性能技術.

MYSQL實例1.3.2、myisam存儲引擎,myisam特點是不支持事物,適合olap應用,myisam表由MYD和MYI組成.mysql-5.0版本之前,myisam默認支持的表大小為4G,從mysql-5.0以后,myisam默認支持256T的表單數據.myisam只緩存索引數據.

MYSQL實例1.3.3、NDB存儲引擎,特點是數據放在內存中,mysql-5.1版本開始可以將非索引數據放到磁盤上.NDB之前的缺陷是join查詢是mysql數據庫層完成的,而不是存儲引擎完成的,復雜的join查詢需要巨大的網絡開銷,速度很慢.當前mysql cluster7.2版本中已經解決此問題,join查詢效率提高了70倍.

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

MYSQL實例1.3.5、archive存儲引擎,壓縮能力較強,主要用于歸檔存儲.

MYSQL實例1.3.6、federated存儲引擎,不存儲數據,他指向一臺遠程mysql數據庫上的表.

MYSQL實例1.3.7、maria存儲引擎,myisam的后續版本,支持緩存數據和索引,行鎖設計,支持mvcc,支持事務和非事務安全的選項,以及更好的BLOG字符類型的處理性能.

MYSQL實例1.3.8、其他存儲引擎,sphinx用于全文索引,infobright用于數據倉庫.

MYSQL實例1.4連接Mysql

MYSQL實例1.4.1、TCP/IP：基于網絡的連接,連接進行權限檢查.

MYSQL實例1.4.2、命名管道和共享內存：Windows系統上同一服務器上的兩進程可通過命名管道連接,需在配置文件中啟用--enable-named-pipe選項.

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

MYSQL實例二．InnoDB存儲引擎

MYSQL實例2.2、innodb引擎架構
www.jbxue.com
InnoDB的多個內存塊組成了內存池,負責如下工作：

MYSQL實例1).維護所有進程/線程需要訪問的多個內部數據結構.

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

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

MYSQL實例后臺線程的主要作用是負責刷新內存池中的數據,保證緩沖池中的內存緩存是最近的數據,此外、將已經修改的數據文件刷新到磁盤文件

MYSQL實例2.2.1、后臺線程

MYSQL實例innodb存儲引擎后臺有7個線程,—–4個IO線程(insert buffer thread,log thread,read thread,write thread),1個master thread,一個lock監控線程,一個錯誤監控線程.

MYSQL實例2.2.2、內存

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

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

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

MYSQL實例

MYSQL實例
2.3、master thread

MYSQL實例2.3.1、master thread源碼分析
">

MYSQL實例2.3.2、master thread的潛在問題

MYSQL實例1、由于硬件的發展,現在的硬件性能已經提高了很多,如果innodb每秒最大刷新100個臟頁,那么效率會很低,為了解決這個問題,innodb plugin提供了一個參數innodb_io_capacity,用來表示磁盤IO的吞吐量,默認值是200,規則如下：在合并插入緩存時,合并插入緩存的數量為innodb_io_capacity的5%；在從緩沖區刷新臟頁時,啥新臟頁的數量為innodb_io_capacity.

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

MYSQL實例2.4、關鍵特性,為innodb提高性能的技術

MYSQL實例2.4.1、插入緩存

MYSQL實例當一個表有非聚集索引時,對于非聚集索引的葉子節點的插入不是順序的,這時候需要離散的訪問非聚集索引頁,性能就在這里降低了,這是由于b+樹的原理導致的.插入緩存就是用來解決這個問題的.

MYSQL實例對于非聚集索引的插入和更新操作,不是每一次都直接插入索引頁,而是先判斷插入的非聚集索引頁是否在緩存中,如果在就直接插入,如果不在就放入到一個插入緩沖區中,好似欺騙數據庫這個非聚集索引已經插入到葉子節點了.然后再以一定的頻率插入緩存和非聚集索引頁字節點的合并操作.

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

MYSQL實例2.4.2、兩次寫

MYSQL實例兩次寫給innodb帶來的是可靠性,主要用來解決部分寫失敗(partial page write).在應用重做日之前,我們需要一個頁的副本,當寫入失效發生時,先通過頁的副本來還原該頁,再進行重做,這就是doublewrite.

MYSQL實例doublewrite有兩部分組成,一部分是內存中的doublewrite buffer,大小為2M,另外一部分就是物理磁盤上的共享表空間中聯系的128個頁,即兩個區,大小同樣為2M.當緩沖池的張也刷新時,并不直接寫硬盤,而是回通過memcpy函數將臟頁先拷貝到內存中的doublewrite buffer,之后通過doublewrite buffer再分兩次寫,每次寫入1M到共享表空間的物理磁盤上,然后馬上調用fsync函數,同步磁盤.

MYSQL實例2.4.3、自適應哈西索引

MYSQL實例由于innodb不支持hash索引,但是在某些情況下hash索引的效率很高,于是出現了 adaptive hash index功能,innodb存儲引擎會監控對表上索引的查找,如果觀察到建立hash索引可以提高性能的時候,則自動建立hash索引.

MYSQL實例2.5、啟動、關閉、恢復
innodb_fast_shutdown影響InnoDB表關閉.該參數有0、1、2三個參數.

MYSQL實例0 MySQL關閉時 完成所有的full purge和merge insertbuffer操作

MYSQL實例1默認值 只將緩沖池內的一些臟頁刷新至磁盤

MYSQL實例2將日志都寫入日志文件不會有任何事務丟失但下次啟動時會進行recovery
innodb_force_recovery影響整個innodb存儲引擎的恢復狀況,該值默認為0,表示當需要恢復時,需要執行所有的恢復操作,當不能進行有效恢復時,如數據頁發生了corruption,mysql數據庫可能宕機,并把錯誤寫入錯誤日志中.

MYSQL實例三．文件

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

MYSQL實例3.1.1、什么是參數
參數是一個鍵/值對.可以使用show variables like命令查看,也可以通過information_schema的GLOBAL_VARIABLES視圖來查找.

MYSQL實例3.1.2、參數類型
參數文件分為兩類：動態參數和靜態參數.動態參數意味著你可以在Mysql實例運行中進行更改；靜態參數說明在整個實例生命周期內都不得進行更改,好像是只讀的.對于動態參數,又可以分為global和session關鍵字,表明該參數的修改是基于當前會話還是真格實例的生命周期.有些動態參數只能在會話中進行修改,如autocommit；有些參數修改完后,在整個實例生命周期中都會生效,如binlog_cache_size；而有些參數既可以在會話又可以在整個實例的生命周期內生效,如read_buffer_size.

MYSQL實例3.2、日志文件

MYSQL實例3.2.1、錯誤日志
錯誤日志對Mysql的啟動、運行、關閉過程進行了記錄.出現Mysql不能正常啟動時,第一個必須查找的文件應該就是錯誤日志文件.使用show variables like ‘log_error’來定位文件.

MYSQL實例3.2.2、慢查詢日志
慢查詢能為SQL語句的優化帶來很好的幫助.設定一個閥值,將運行時間超過該值的所有SQL語句都記錄到慢查詢日志文件中.用參數long_query_time來設置.另一個參數log_queries_not_using_indexes,若運行的SQL語句沒有使用索引,則這條SQL語句會被記錄下來.

MYSQL實例3.2.3、查詢日志
查詢日志記錄了所有對Mysql請求的信息,不論這些請求是否得到正確的執行.默認文件名為：主機名.log.

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

MYSQL實例注意：針對系統庫mysql里面的表發生變化的處理規則如下：
1、 如果采用insert,update,delete直接操作表,則日志根據binlog_format設定的格式記錄.
2、 如果使用grant,revoke,set password等DCL語句,那么無論如何都會使用SBR模式記錄.
3、 blockhole引擎不支持row格式,ndb引擎不支持statement格式.

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

MYSQL實例3.4、pid文件和表結構定義文件

MYSQL實例pid文件是實例啟動是記錄自己進程ID號的文件,表結構定義文件是以frm為后綴名的文件,還可以用來存放視圖的定義.

MYSQL實例3.5、innodb引擎文件

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

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

MYSQL實例因為重做日志條目先被寫到日志緩沖中,然后根據一定條件刷新到磁盤重做日志文件中.與redo log相關的就是innodb_flush_log_at_trx_commit的值,對innodb的性能影響很大.他有0,1,2三個值,0代表提交事務時,并不同步寫redo log,而是等master threas每秒寫.1代表commit的時候就將redo log緩存寫入磁盤,2代表commit的時候將redo log緩存異步的寫入磁盤.

MYSQL實例就是這些了,有關mysql innodb數據庫引擎的詳細介紹,希望對大家有所幫助.

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/6202.html