關於InnoDB型資料庫優化的實例詳解

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%'; 來檢視相關的參數選項。

<br>

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參數查看.

<br>

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的快取中去，這樣可以縮短預熱的時間，提高業務繁忙高並發時的效率。

<br>

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缓存池预热的状态信息：

<br>

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

<br>

【相关推荐】

1. Mysql免费视频教程

2. 详解innodb_index_stats导入数据时 提示表主键冲突的错误

3. 实例详解 mysql中innodb_autoinc_lock_mode

4. MySQL中添加新用户权限的实例详解

5. 实例详解mysql中init_connect方法

以上是關於InnoDB型資料庫優化的實例詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！