mysql5.7+innodb配置_MySQL 5.7 優化InnoDB配置

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

在進行優化前，我們先確認目前數據庫的配置，命令如下：

mysql> show variables like "%innodb%";

這會把所有innodb相關的參數顯示出來，接下來我們對關鍵參數進行優化。

innodb_buffer_pool_size

這個是Innodb最重要的參數，主要作用是緩存innodb表的索引，數據，插入數據時的緩沖，默認值為128M。

如果是一個專用DB服務器，那么它可以占到內存的70%-80%。

並不是設置的越大越好。設置的過大，會導致system的swap空間被占用，導致操作系統變慢，從而減低sql查詢的效率。

如果你的數據比較小，那么可分配是你的數據大小+10%左右做為這個參數的值。例如：數據大小為50M,那么給這個值分配innodb_buffer_pool_size＝64M就夠了

設置方法：在my.cnf文件里：

innodb_buffer_pool_size=4G

innodb_log_file_size

這個參數指定在一個日志組中，每個log的大小。innodb的logfile就是事務日志，用來在mysql crash后的恢復.所以設置合理的大小對於mysql的性能非常重要，直接影響數據庫的寫入速度，事務大小，異常重啟后的恢復。在mysql 5.5和5.5以前innodb的logfile最大設置為4GB,在5.6以后的版本中logfile最大的可以設為512GB。一般取256M可以兼顧性能和recovery的速度。

設置方法：在my.cnf文件里：

innodb_log_file_size=256M

innodb_log_buffer_size

事務在內存中的緩沖，也就是日志緩沖區的大小， 默認設置即可，具有大量事務的可以考慮設置為16M。

innodb_flush_log_at_trx_commit

控制事務的提交方式,也就是控制log的刷新到磁盤的方式。

這個參數只有3個值(0，1，2).默認為1，性能更高的可以設置為0或是2，這樣可以適當的減少磁盤IO(但會丟失一秒鍾的事務。)，游戲庫的MySQL建議設置為0。主庫請不要更改了。

其中：

0：log buffer中的數據將以每秒一次的頻率寫入到log file中，且同時會進行文件系統到磁盤的同步操作，但是每個事務的commit並不會觸發任何log buffer 到log file的刷新或者文件系統到磁盤的刷新操作；

1：(默認為1)在每次事務提交的時候將logbuffer 中的數據都會寫入到log file，同時也會觸發文件系統到磁盤的同步；

2：事務提交會觸發log buffer 到log file的刷新，但並不會觸發磁盤文件系統到磁盤的同步。此外，每秒會有一次文件系統到磁盤同步操作。

說明：

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

當這個值為1時：innodb 的事務LOG在每次提交后寫入日值文件，並對日值做刷新到磁盤。這個可以做到不丟任何一個事務。

當這個值為2時：在每個提交，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新,在對日志文件的刷新在值為2的情況也每秒發生一次。但需要注意的是，由於進程調用方面的問題，並不能保證每秒１００％的發生。從而在性能上是最快的。但操作系統崩潰或掉電才會刪除最后一秒的事務。

當這個值為0時：日志緩沖每秒一次地被寫到日志文件，並且對日志文件做到磁盤操作的刷新，但是在一個事務提交不做任何操作。mysqld進程的崩潰會刪除崩潰前最后一秒的事務。

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

innodb_flush_method

這個參數控制着innodb數據文件及redo log的打開、刷寫模式。

有三個值：fdatasync(默認)，O_DSYNC，O_DIRECT

默認是fdatasync，調用fsync()去刷數據文件與redo log的buffer

為O_DSYNC時，innodb會使用O_SYNC方式打開和刷寫redo log,使用fsync()刷寫數據文件

為O_DIRECT時，innodb使用O_DIRECT打開數據文件，使用fsync()刷寫數據文件跟redo log。

在類unix操作系統中，文件的打開方式為O_DIRECT會最小化緩沖對io的影響，該文件的io是直接在用戶空間的buffer上操作的，並且io操作是同步的，因此不管是read()系統調用還是write()系統調用，數據都保證是從磁盤上讀取的

innodb_flush_method=O_DIRECT

MySQL 5.7 提供了更加合適的默認值，一般情況下只要調整下面 3 個選項就可以了，其余參數根據實際情況再進行配置。

####innodb配置#################

innodb_buffer_pool_size=8G

innodb_log_file_size=256M

innodb_flush_method=O_DIRECT

linux服務器，內存是32G的，因為還部署了其他應用，所有這里buffer_pool_size就設置了8G。