)Mysql Innodb 引擎最佳化

介紹：

　　InnoDB給MySQL提供了具有提交，復原和崩潰恢複能力的事務安全（ACID相容）儲存引擎。InnoDB鎖定在行級並且也在SELECT語句提供一個Oracle風格一致的非鎖定讀。這些特色增加了多使用者部署和效能。沒有在InnoDB中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的空間。InnoDB也支援FOREIGN KEY強制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。

　Innodb 的創始人：Heikki Tuuri

　Heikki Tuuri在Ｉｎｎｏｄｂ的Ｂｕｇ社區裡也是很活躍的，如果遇到Ｂｕｇ也可以直接提到社區，得到作者的解答。

為什麼要學習Ｉｎｎｏｄｂ的調優：

　　目前來說：InnoDB是為Mysql處理巨大資料量時的最大效能設計。它的CPU效率可能是任何其它基於磁碟的關聯式資料庫引擎所不能匹敵的。在資料量大的網站或是應用中Innodb是倍受青睞的。

　　另一方面，在資料庫的複製操作中Innodb也是能保證master和slave資料一致有一定的作用。

參數調優內容：

　　1. 記憶體利用方面

　　2. 日值控制方面

　　3.　檔案IO分配，空間佔用方面

　　4. 其它相關參數

１.記憶體利用方面：

首先介紹一個Innodb最重要的參數：

innodb_buffer_pool_size

　　這個參數和MyISAM的key_buffer_size有相似之處，但也是有差別的。這個參數主要緩衝innodb表的索引，資料，插入資料時的緩衝。為Innodb加速最佳化首要參數。

　　該參數分配記憶體的原則：這個參數預設分配只有8M，可以說是非常小的一個值。如果是一個專用ＤＢ伺服器，那麼他可以佔到記憶體的70%-80%。這個參數不能動態更改，所以分配需多考慮。分配過大，會使Swap佔用過多，致使Mysql的查詢特慢。如果你的資料比較小，那麼可分配是你的資料大小＋１０％左右做為這個參數的值。例如：資料大小為５０Ｍ,那麼給這個值分配innodb_buffer_pool_size＝６４Ｍ

設定方法：

innodb_buffer_pool_size=4G

這個參數分配值的使用方式可以根據show innodb status\G;中的

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 4668764894;

去確認使用方式。

第二個：

innodb_additional_mem_pool：

作用：用來存放Innodb的內部目錄

這個值不用分配太大，系統可以自動調。不用設定太高。通常比較大資料設定16Ｍ夠用了，如果表比較多，可以適當的增大。如果這個值自動增加，會在error log有中顯示的。

分配原則：

用show innodb status\G;去查看運行中的ＤＢ是什麼狀態（參考BUFFER POOL AND MEMORY段中），然後可以調整到適當的值。

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 4668764894; in additional pool allocated 16777216

參考：in additional pool allocated 16777216

根據你的參數情況，可以適當的調整。

設定方法：

innodb_additional_mem_pool=16M

２.關於日值方面：

innodb_log_file_size

作用：指定日值的大小

分配原則：幾個日值成員大小加起來差不多和你的innodb_buffer_pool_size相等。上限為每個日值上限大小為4G.一般控制在幾個ＬＯＧ檔案相加大小在２Ｇ以內為佳。具體情況還需要看你的事務大小，資料大小為依據。

說明：這個值分配的大小和資料庫的寫入速度，事務大小，異常重啟後的恢複有很大的關係。

設定方法：

innodb_log_file_size=256M

innodb_log_files_in_group

作用：指定你有幾個日值組。

分配原則：　一般我們可以用２－３個日值組。預設為兩個。

設定方法：

innodb_log_files_in_group=3

innodb_log_buffer_size：

作用：事務在記憶體中的緩衝。

分配原則：控制在2-8M.這個值不用太多的。他裡面的記憶體一般一秒鐘寫到磁碟一次。具體寫入方式和你的事務提交方式有關。在Ｏｒａｃｌｅ等資料庫瞭解這個，一般最大指定為３Ｍ比較合適。

參考：Innodb_os_log_written(show global status 可以拿到)

如果這個值增長過快，可以適當的增加innodb_log_buffer_size

另外如果你需要處理大理的ＴＥＸＴ，或是ＢＬＯＢ欄位，可以考慮增加這個參數的值。

設定方法：

innodb_log_buffer_size=3M

innodb_flush_logs_at_trx_commit

作用：控制事務的提交方式

分配原則：這個參數只有３個值，０，１，２請確認一下自已能接受的層級。預設為１，主庫請不要更改了。

效能更高的可以設定為０或是２，但會丟失一秒鐘的事務。

說明：

這個參數的設定對Ｉｎｎｏｄｂ的效能有很大的影響，所以在這裡給多說明一下。

當這個值為1時：innodb 的事務LOG在每次提交後寫入日值檔案，並對日值做重新整理到磁碟。這個可以做到不丟任何一個事務。

當這個值為2時：在每個提交，日誌緩衝被寫到檔案，但不對記錄檔做到磁碟操作的重新整理,在對記錄檔的重新整理在值為2的情況也每秒發生一次。但需要注意的是，由於進程調用方面的問題，並不能保證每秒１００％的發生。從而在效能上是最快的。但作業系統崩潰或掉電才會刪除最後一秒的事務。

當這個值為0時：日誌緩衝每秒一次地被寫到記錄檔，並且對記錄檔做到磁碟操作的重新整理，但是在一個事務提交不做任何操作。mysqld進程的崩潰會刪除崩潰前最後一秒的事務。

從以上分析，當這個值不為１時，可以取得較好的效能，但遇到異常會有損失，所以需要根據自已的情況去衡量。

設定方法：

innodb_flush_logs_at_trx_commit=1

3.　檔案IO分配，空間佔用方面

innodb_file_per_table

作用：使每個Innodb的表，有自已獨立的資料表空間。如刪除檔案後可以回收那部分空間。

分配原則：只有使用不使用。但ＤＢ還需要有一個公用的資料表空間。

設定方法：

innodb_file_per_table=1

innodb_file_io_threads

作用：檔案讀寫ＩＯ數，這個參數只在Windows上起作用。在LINUX上只會等於４

設定方法：

innodb_file_io_threads=4

innodb_open_files

作用：限制Innodb能開啟的表的資料。

分配原則：如果庫裡的表特別多的情況，請增加這個。這個值預設是３００。

設定方法：

innodb_open_files=800

請適當的增加table_cache

4. 其它相關參數

這裡說明一個比較重要的參數：

innodb_flush_method

作用：Ｉｎｎｏｄｂ和系統打交道的一個ＩＯ模型

分配原則：Windows不用設定。

Ｕｎｉｘ可以設定：fsync() or O_SYNC/O_DSYNC

如果系統可以禁止系統的Ｃａｃｈｅ那就把他禁了。

Ｌｉｎｕｘ可以選擇：O_DIRECT

直接寫入磁碟，禁止系統Ｃａｃｈｅ了

設定方法：

innodb_flush_method=O_DIRECT

innodb_max_dirty_pages_pct

作用：控制Innodb的髒頁在緩衝中在那個百分比之下，值在範圍1-100,預設為90.

這個參數的另一個用處：當Ｉｎｎｏｄｂ的記憶體配置過大，致使Ｓｗａｐ佔用嚴重時，可以適當的減小調整這個值，使達到Ｓｗａｐ空間釋放出來。建義：這個值最大在９０％，最小在１５％。太大，緩衝中每次更新需要致換資料頁太多，太小，放的資料頁太小，更新操作太慢。

設定方法：

innodb_max_dirty_pages_pct＝90

動態更改需要有Ｓｕｐｅｒ許可權：

set global innodb_max_dirty_pages_pct=50;