MysqlInnoDB优化策略

一.  MySQLInnoDB介绍

InnoDB给MySQL提供了具有提交，回滚，和崩溃恢复的能力。并且兼容事务。InnoDB锁定在行级锁，并且在查询语句中提供了Oracle风格一致的非锁定的读方式，这些特色增加了多用户部署和性能。没有在InnoDB类型的表与其他MySQL的表的类型混合起来。甚至在同一个查询中也可以混合。

二.  从MySQLInnoDB的配置参数优化

输入命令 show VARIABLES like "%INNODB%" 查看所有的关键参数，针对参数进行优化。

参数:innodb_buffer_pool_size 

这个是Innodb最重要的参数，作用是缓存innodb表的索引，数据，插入数据时候的缓冲，默认值为128M。如果是一个专用的DB服务器，这个参数可以占到内存的70%-80%。这个参数不是设置越大越好，设置过大，导致system的swap空间被占用，导致操作系统变的很慢。降低了数据库的sql查询效率。

设值问题，根据真实需求来设置，在项目上线之后来根据实时数据来优化。方法如下

通过命令show status like'Innodb_buffer_pool_%';来查看系统中 Innodb的BufferPool 使用情况是否正常高效。如下图

 

通过上图可以发现共有8191pages，还有7788pages是free状态而这其中仅仅只有403个page有数据，read的请求有11750次，其中有220次所请求的数据在buffer pool中没有，也就是说有220次是通过读取物理磁盘来读取的数据，所以得出了下面的计算命中率的方式(innodb_buffer_pool_read_request – innodb_buffer_pool_reads)/ innodb_buffer_pool_read_request*100%

命中率为:(11750-220)/11750*100%=98.12%

那么我们设置的BufferPool为403/8191*100%=%4.9表明缓冲池设置过大

innodb_log_file_size 

这个参数指定在一个日志组中，每个log的大小。innodb的logfile就是事务日志，用来在mysql crash后的恢复.所以设置合理的大小对于mysql的性能非常重要，直接影响数据库的写入速度，事务大小，异常重启后的恢复。在mysql5.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数据文件及redolog的打开、刷写模式。 有三个值：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