首頁 >資料庫 >mysql教程 >MySQL優化之InnoDB優化程式碼詳解

MySQL優化之InnoDB優化程式碼詳解

黄舟
黄舟原創
2017-03-16 14:26:111045瀏覽

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中文網其他相關文章!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn