MySQL優化之InnoDB優化代碼詳解

innodb是為mysql處理巨大數據量時的最大性能設計。它的cpu效率可能是任何mysql基于磁盤的關系數據庫引擎所不能匹敵的。在數據量大的網站或是應用中innodb是倍受青睞的。那么它就不需要優化了嗎,答案很顯然:當然不是!!!

學習計劃很容易就被打斷,堅持也不容易。最近公司里開會,要調整業務方向,建議學習Nodemysql。NodeJS之前我就會一點,但是沒有深入研究。Node的語法和客戶端Js基本上是一樣的,這半年來很少開發有客戶端的東西。本來JS基礎還行的我,也對這塊的知識陌生了。看起來知識都是用進廢退的,不常用了,過不了多久就會遺忘。所以又重新mysql了JS的相關知識。學習了Node的服務器與socket知識。MySQL的計劃就這樣的擱淺起來,星期天的時候吃吃喝喝睡睡,早上又懶的要命,熬著熬著就熬到了下午。廢話不多說了,繼續進行MySQL的優化系列,這次看下InnoDB的優化項。

InnoDB的主mysql是聚簇索引,索引與數據公用表空間。對于InnoDB來說,數據就是索引,索引就是數據。InnoDBmysql機制與MyISAM的最大區別在于,InnoDB不僅緩存索引,同時還會mysql

一、InnoDB緩存池

InnoDB緩存池(InnoDB buffer pool)是提升InnoDB提升性能的關鍵,它既可以緩存數據,又可以緩存索引,甚至其他的管理數據(元數據、行級鎖)等。可以使用show variables like ‘innodb%pool%’; 來查看相關的參數選項。

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是用于設置InnoDB緩存池(InnoDBBufferPool)的大小,默認值是128M.InnoDB緩存池的大小對InnoDB的整體性能影響較大,如果當前的MySQL服務器專門用作MySQL服務,那么可以盡量的增加該參數的大小。

innodb_buffer_pool_instance

innodb_buffer_pool_instance默認值是1,表示InnoDB緩存池被劃分為一個區域,適當的增加該參數值,可以提升InnoDB的并發性能。

innodb_additional_mem_pool_size

指定InnoDB用于來存儲數據字典和其他內部數據的緩存大小,默認值是2M.InnoDB的表個數越多,就應該適當的增加該參數的大小。

二、InnoDB緩存池內部結構

InnoDB在內存中維護一個緩存池用于緩存數據和索引。緩存池可以認為是一條很長的鏈表(mysql).該鏈表分為兩個子鏈表,一個子鏈表存放old page數據,old page 數據是長時間未被訪問的數據頁,亮一個子鏈表存放mysql page,new page 是最近被訪問的數據頁。old page 默認占整個鏈表大小的37%,可以通過innodb_old_blocks_pct參數查看.

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

old page 和 new page 的交匯點稱為midpoint。

當用戶訪問數據時,InnoDB首先會再InnoDB緩存中查找數據,如果緩存池中沒有數據,InnoDB會將硬盤中的數據插入到InnoDB緩存池中,如果緩存池已滿,則利用LRU算法清楚過期的老數據

三、InnoDB緩存池預熱。

MySQL服務器啟動一段時間后,InnoDB會將經常訪問的數據(業務數據,管理數據)放入InnoDB緩存中,即InnoDB緩存池中保存的是頻繁需要訪問的數據(簡稱熱數據)。當InnoDB緩存池的大小是幾十G或者上百G的時候,如果重啟MySQL,如果將之前InnoDB緩存池中的熱數據加載到InnoDB緩存池中呢?

如果單靠InnoDB自身預熱的InnoDB緩存池,將會是一個不短的時間周期,這對于業務繁忙的系統來說,長時間的掛機,是嚴重的生產事故,不能夠容忍。幸好在MySQL5.6版本支持關閉服務時,可以將熱數據保存至硬盤,MySQL重啟是首先將硬盤中的熱數據加載到InnoDB的緩存中去,這樣可以縮短預熱的時間,提高業務繁忙高并發時的效率。

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

默認是關的,如果開啟參數,停止MySQL服務是,InnoDB緩存中的熱數據將會保存到硬盤中。

innodb_buffer_pool_load_at_starup

默認是關閉的,如果開啟該參數,啟動MySQL服務時,MySQL將本地硬盤的熱數據加載到InnoDB緩存池中。

innodb_buffer_pool_dump_now

默認關閉,如果開啟該參數,停止MySQL服務時,以手動方式將InnoDB緩存池中的熱數據保存到本地硬盤。

innodb_buffer_pool_load_now

默認關閉,如果開啟該參數,啟動MySQL服務時,以手動方式將本地硬盤的數據加載到InnoDB緩存池中,

innodb_buffer_pool_filename

如果開啟InnoDB預熱功能,停止MySQL服務是,MySQL將InnoDB緩存池中的熱數據保存到數據庫根目錄下,默認文件名是這個參數的值。

開啟InnoDB緩存后,可以使用如下命令查看當前InnoDB緩存池預熱的mysql信息:

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??????|  +---------------------------------------+-------------+

這里面的英語都比較簡單,就不解釋了。

四、InnoDB實時監控

mysql>?show?engine?innodb?statusG

? 版權聲明
THE END
喜歡就支持一下吧
點贊11 分享