InnoDB是為Mysql處理龐大資料量時的最大效能設計。它的CPU效率可能是任何其它基於磁碟的關聯式資料庫引擎所不能匹敵的。在資料量大的網站或應用程式中Innodb是倍受青睞的。那麼它就不需要優化了嗎,答案很顯然:當然不是! ! !
學習計畫很容易就被打斷,堅持也不容易。最近公司開會,要調整業務方向,建議學習NodeJS。 NodeJS之前我就會一點,但沒有深入研究。 Node的語法和客戶端Js基本上是一樣的,這半年來很少開發有客戶端的東西。本來JS基礎還行的我,也對這塊的知識陌生了。看起來知識都是用進廢退的,不常用了,過不了多久就會遺忘。所以又重新複習了JS的相關知識。學習了Node的伺服器與socket知識。 MySQL的計畫就這樣的擱淺起來,星期天的時候吃吃喝睡睡,早上又懶的要命,熬著熬著就熬到了下午。廢話不多說了,繼續進行MySQL的最佳化系列,這次看下InnoDB的優化項目。
InnoDB的主索引是叢集索引,索引與資料公用資料表空間。對InnoDB來說,資料就是索引,索引就是資料。 InnoDB快取機制與MyISAM的最大差異在於,InnoDB不僅快取索引,同時還會快取資料。
一、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在記憶體中維護一個快取池用於快取資料和索引。快取池可以認為是一條很長的鍊錶(list).這個鍊錶分為兩個子鍊錶,一個子鍊錶存放old page數據,old page 數據是長時間未被訪問的數據頁,亮一個子鍊錶存放new 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缓存池预热的状态信息:
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 status\G
以上是MySQL優化之InnoDB優化程式碼詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章!