關(guān)于InnoDB型數(shù)據(jù)庫(kù)優(yōu)化的實(shí)例詳解

innodb是為mysql處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)。它的cpu效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫(kù)引擎所不能匹敵的。在數(shù)據(jù)量大的網(wǎng)站或是應(yīng)用中innodb是倍受青睞的。那么它就不需要優(yōu)化了嗎，答案很顯然：當(dāng)然不是！！！

學(xué)習(xí)計(jì)劃很容易就被打斷，堅(jiān)持也不容易。最近公司里開會(huì)，要調(diào)整業(yè)務(wù)方向，建議學(xué)習(xí)NodeJS。NodeJS之前我就會(huì)一點(diǎn)，但是沒有深入研究。Node的語法和客戶端Js基本上是一樣的，這半年來很少開發(fā)有客戶端的東西。本來JS基礎(chǔ)還行的我，也對(duì)這塊的知識(shí)陌生了。看起來知識(shí)都是用進(jìn)廢退的，不常用了，過不了多久就會(huì)遺忘。所以又重新復(fù)習(xí)了JS的相關(guān)知識(shí)。學(xué)習(xí)了Node的服務(wù)器與socket知識(shí)。mysql的計(jì)劃就這樣的擱淺起來，星期天的時(shí)候吃吃喝喝睡睡，早上又懶的要命，熬著熬著就熬到了下午。廢話不多說了，繼續(xù)進(jìn)行MySQL的優(yōu)化系列，這次看下InnoDB的優(yōu)化項(xiàng)。

InnoDB的主復(fù)習(xí)是聚簇索引，索引與數(shù)據(jù)公用表空間。對(duì)于InnoDB來說，數(shù)據(jù)就是索引，索引就是數(shù)據(jù)。InnoDB復(fù)習(xí)機(jī)制與MyISAM的最大區(qū)別在于，InnoDB不僅緩存索引，同時(shí)還會(huì)復(fù)習(xí)。

一、InnoDB緩存池

InnoDB緩存池（InnoDB buffer pool）是提升InnoDB提升性能的關(guān)鍵，它既可以緩存數(shù)據(jù)，又可以緩存索引，甚至其他的管理數(shù)據(jù)（元數(shù)據(jù)、行級(jí)鎖）等。可以使用show variables like ‘innodb%pool%’; 來查看相關(guān)的參數(shù)選項(xiàng)。

mysql>?show?variables?like?'innodb%pool%';  +-------------------------------------+----------------+  |?Variable_name????????????|?Value?????|  +-------------------------------------+----------------+  |?innodb_additional_mem_pool_size???|?8388608????|  |?innodb_buffer_pool_dump_at_shutdown?|?OFF??????|  |?innodb_buffer_pool_dump_now?????|?OFF??????|  |?innodb_buffer_pool_filename?????|?ib_buffer_pool?|  |?innodb_buffer_pool_instances????|?8???????|  |?innodb_buffer_pool_load_abort????|?OFF??????|  |?innodb_buffer_pool_load_at_startup?|?OFF??????|  |?innodb_buffer_pool_load_now?????|?OFF??????|  |?innodb_buffer_pool_size???????|?134217728???|  +-------------------------------------+----------------+

innodb_buffer_pool_size

innodb_buffer_pool_size是用于設(shè)置InnoDB緩存池（InnoDBBufferPool）的大小，默認(rèn)值是128M.InnoDB緩存池的大小對(duì)InnoDB的整體性能影響較大，如果當(dāng)前的MySQL服務(wù)器專門用作MySQL服務(wù)，那么可以盡量的增加該參數(shù)的大小。

innodb_buffer_pool_instance

innodb_buffer_pool_instance默認(rèn)值是1，表示InnoDB緩存池被劃分為一個(gè)區(qū)域，適當(dāng)?shù)脑黾釉搮?shù)值，可以提升InnoDB的并發(fā)性能。

innodb_additional_mem_pool_size

指定InnoDB用于來存儲(chǔ)數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)的緩存大小，默認(rèn)值是2M.InnoDB的表個(gè)數(shù)越多，就應(yīng)該適當(dāng)?shù)脑黾釉搮?shù)的大小。

二、InnoDB緩存池內(nèi)部結(jié)構(gòu)

InnoDB在內(nèi)存中維護(hù)一個(gè)緩存池用于緩存數(shù)據(jù)和索引。緩存池可以認(rèn)為是一條很長(zhǎng)的鏈表（復(fù)習(xí)）.該鏈表分為兩個(gè)子鏈表，一個(gè)子鏈表存放old page數(shù)據(jù)，old page 數(shù)據(jù)是長(zhǎng)時(shí)間未被訪問的數(shù)據(jù)頁，亮一個(gè)子鏈表存放復(fù)習(xí) page，new page 是最近被訪問的數(shù)據(jù)頁。old page 默認(rèn)占整個(gè)鏈表大小的37%，可以通過innodb_old_blocks_pct參數(shù)查看.

mysql>?show?variables?like?'innodb_old_blocks%';  +------------------------+-------+  |?Variable_name?????|?Value?|  +------------------------+-------+  |?innodb_old_blocks_pct?|?37??|  |?innodb_old_blocks_time?|?1000?|  +------------------------+-------+

old page 和 new page 的交匯點(diǎn)稱為midpoint。

當(dāng)用戶訪問數(shù)據(jù)時(shí)，InnoDB首先會(huì)再InnoDB緩存中查找數(shù)據(jù)，如果緩存池中沒有數(shù)據(jù)，InnoDB會(huì)將硬盤中的數(shù)據(jù)插入到InnoDB緩存池中，如果緩存池已滿，則利用LRU算法清楚過期的老數(shù)據(jù)

三、InnoDB緩存池預(yù)熱。

MySQL服務(wù)器啟動(dòng)一段時(shí)間后，InnoDB會(huì)將經(jīng)常訪問的數(shù)據(jù)（業(yè)務(wù)數(shù)據(jù)，管理數(shù)據(jù)）放入InnoDB緩存中，即InnoDB緩存池中保存的是頻繁需要訪問的數(shù)據(jù)（簡(jiǎn)稱熱數(shù)據(jù)）。當(dāng)InnoDB緩存池的大小是幾十G或者上百G的時(shí)候，如果重啟MySQL，如果將之前InnoDB緩存池中的熱數(shù)據(jù)加載到InnoDB緩存池中呢？

如果單靠InnoDB自身預(yù)熱的InnoDB緩存池，將會(huì)是一個(gè)不短的時(shí)間周期，這對(duì)于業(yè)務(wù)繁忙的系統(tǒng)來說，長(zhǎng)時(shí)間的掛機(jī)，是嚴(yán)重的生產(chǎn)事故，不能夠容忍。幸好在MySQL5.6版本支持關(guān)閉服務(wù)時(shí)，可以將熱數(shù)據(jù)保存至硬盤，MySQL重啟是首先將硬盤中的熱數(shù)據(jù)加載到InnoDB的緩存中去，這樣可以縮短預(yù)熱的時(shí)間，提高業(yè)務(wù)繁忙高并發(fā)時(shí)的效率。

mysql>?show?variables?like?'%innodb%pool%';  +-------------------------------------+----------------+  |?Variable_name????????????|?Value?????|  +-------------------------------------+----------------+  |?innodb_additional_mem_pool_size???|?8388608????|  |?innodb_buffer_pool_dump_at_shutdown?|?OFF??????|  |?innodb_buffer_pool_dump_now?????|?OFF??????|  |?innodb_buffer_pool_filename?????|?ib_buffer_pool?|  |?innodb_buffer_pool_instances????|?8???????|  |?innodb_buffer_pool_load_abort????|?OFF??????|  |?innodb_buffer_pool_load_at_startup?|?OFF??????|  |?innodb_buffer_pool_load_now?????|?OFF??????|  |?innodb_buffer_pool_size???????|?134217728???|  +-------------------------------------+----------------+

innodb_buffer_pool_dump_at_shutdown

默認(rèn)是關(guān)的，如果開啟參數(shù)，停止MySQL服務(wù)是，InnoDB緩存中的熱數(shù)據(jù)將會(huì)保存到硬盤中。

innodb_buffer_pool_load_at_starup

默認(rèn)是關(guān)閉的，如果開啟該參數(shù)，啟動(dòng)MySQL服務(wù)時(shí)，MySQL將本地硬盤的熱數(shù)據(jù)加載到InnoDB緩存池中。

innodb_buffer_pool_dump_now

默認(rèn)關(guān)閉，如果開啟該參數(shù)，停止MySQL服務(wù)時(shí)，以手動(dòng)方式將InnoDB緩存池中的熱數(shù)據(jù)保存到本地硬盤。

innodb_buffer_pool_load_now

默認(rèn)關(guān)閉，如果開啟該參數(shù)，啟動(dòng)MySQL服務(wù)時(shí)，以手動(dòng)方式將本地硬盤的數(shù)據(jù)加載到InnoDB緩存池中，

innodb_buffer_pool_filename

如果開啟InnoDB預(yù)熱功能，停止MySQL服務(wù)是，MySQL將InnoDB緩存池中的熱數(shù)據(jù)保存到數(shù)據(jù)庫(kù)根目錄下，默認(rèn)文件名是這個(gè)參數(shù)的值。

開啟InnoDB緩存后，可以使用如下命令查看當(dāng)前InnoDB緩存池預(yù)熱的復(fù)習(xí)信息：

show?status?like?'innodb_buffer%';  +---------------------------------------+-------------+  |?Variable_name?????????????|?Value????|  +---------------------------------------+-------------+  |?Innodb_buffer_pool_dump_status????|?not?started?|  |?Innodb_buffer_pool_load_status????|?not?started?|  |?Innodb_buffer_pool_pages_data?????|?218?????|  |?Innodb_buffer_pool_bytes_data?????|?3571712???|  |?Innodb_buffer_pool_pages_dirty????|?0??????|  |?Innodb_buffer_pool_bytes_dirty????|?0??????|  |?Innodb_buffer_pool_pages_flushed???|?1??????|  |?Innodb_buffer_pool_pages_free?????|?7973????|  |?Innodb_buffer_pool_pages_misc?????|?0??????|  |?Innodb_buffer_pool_pages_total????|?8191????|  |?Innodb_buffer_pool_read_ahead_rnd???|?0??????|  |?Innodb_buffer_pool_read_ahead?????|?0??????|  |?Innodb_buffer_pool_read_ahead_evicted?|?0??????|  |?Innodb_buffer_pool_read_requests???|?1497????|  |?Innodb_buffer_pool_reads???????|?219?????|  |?Innodb_buffer_pool_wait_free?????|?0??????|  |?Innodb_buffer_pool_write_requests???|?1??????|  +---------------------------------------+-------------+

這里面的英語都比較簡(jiǎn)單，就不解釋了。

四、InnoDB實(shí)時(shí)監(jiān)控

mysql> show engine innodb statusG

【相關(guān)推薦】

1. 復(fù)習(xí)

2. 復(fù)習(xí)

3. 復(fù)習(xí)

4. 復(fù)習(xí)

5. 復(fù)習(xí)