對于InnoDB型數據庫優化的案例詳細說明

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

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

一、InnoDB緩存池

InnoDB緩存池（InnoDB buffer pool）是提升InnoDB提升性能的關鍵，它既可以緩存數據，又可以緩存索引，甚至其他的管理數據（元數據、行級鎖）等�？梢允褂胹how 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

【相關推薦】

1. Mysql免費視頻教程

2. 詳解innodb_index_stats導入數據時 提示表主鍵沖突的錯誤

3. 實例詳解 mysql中innodb_autoinc_lock_mode

4. MySQL中添加新用戶權限的實例詳解

5. 實例詳解mysql中init_connect方法

以上就是關于InnoDB型數據庫優化的實例詳解的詳細內容，更多請關注php中文網其它相關文章！