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。

---

 

其他配置参数注解：

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
#log-error  = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1

########basic settings########
server-id = 11 
port = 3306
user = mysql
#设置autocommit=0，则用户将一直处于某个事务中，直到执行一条commit提交或rollback语句才会结束当前事务重新开始一个新的事务。set autocommit=0的好处是在频繁开启事务的场景下，减少一次begin的交互。
autocommit = 1
#utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符。 
#采用utf8mb4编码的好处是：存储与获取数据的时候，不用再考虑表情字符的编码与解码问题。
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
# 对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。
max_connect_errors = 1000
#数据库隔离级别
transaction_isolation = READ-COMMITTED
#MySQL在完成某些join（连接）需求的时候，为了减少参与join的“被驱动表”的读取次数以提高性能，需要使用到join buffer来协助完成join操作当join buffer 太小，MySQL不会将该buffer存入磁盘文件而是先将join buffer中的结果与需求join的表进行操作，然后清空join buffer中的数据，继续将剩余的结果集写入次buffer中
join_buffer_size = 128M
tmp_table_size = 64M
tmpdir = /tmp
#该值设置过小将导致单个记录超过限制后写入数据库失败，且后续记录写入也将失败
max_allowed_packet = 64M
#mysql在关闭一个交互的连接之前所要等待的秒数
interactive_timeout = 1200
#mysql在关闭一个非交互的连接之前所要等待的秒数
wait_timeout = 600
#MySQL读入缓冲区的大小
read_buffer_size = 16M
#MySQL的随机读缓冲区大小
read_rnd_buffer_size = 8M
#MySQL的顺序读缓冲区大小
sort_buffer_size = 8M
########log settings########
log_error = /var/log/docker_log/mysql/error.log
#开启慢查询日志
slow_query_log = 1
#超出次设定值的SQL即被记录到慢查询日志
long_query_time = 6
slow_query_log_file = /var/log/docker_log/mysql/slow.log
#表示记录下没有使用索引的查询
log_queries_not_using_indexes = 1
#记录管理语句
log_slow_admin_statements = 1
#开启复制从库复制的慢查询的日志
log_slow_slave_statements = 1
#设置每分钟增长的没有使用索引查询的日志数量
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
########replication settings########
#将master.info和relay.info保存在表中
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
#当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。设置为零是让系统自行决定
sync_binlog = 5
#开启全局事务ID，GTID能够保证让一个从服务器到其他的从服务器那里实现数据复制而且能够实现数据整合的
gtid_mode = on
#开启gtid，必须主从全开
enforce_gtid_consistency = 1
#从服务器的更新是否写入二进制日志
log_slave_updates = 1
#三种模式 STATEMENT（有可能主从数据不一致，日质量小）、ROW（产生大量二进制日志）、MIXED
binlog_format = mixed
#relay-log日志记录的是从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器
relay_log = /var/log/docker_log/mysql/relay.log
relay_log_recovery = 1
#开启简单gtid，开启此项会提升mysql执行恢复的性能
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
########innodb settings########
#这个参数在一开始初始化时就要加入my.cnf里，如果已经创建了表，再修改，启动MySQL会报错。最好为8K
#innodb_page_size = 16K
innodb_page_size = 8K
#数据缓冲区buffer pool大小，建议使用物理内存的 75%
innodb_buffer_pool_size = 2G
#当buffer_pool的值较大的时候为1，较小的设置为8
innodb_buffer_pool_instances = 8
#运行时load缓冲池，快速预热缓冲池，将buffer pool的内容（文件页的索引）dump到文件中，然后快速load到buffer pool中。避免了数据库的预热过程，提高了应用访问的性能
innodb_buffer_pool_load_at_startup = 1
#运行时dump缓冲池
innodb_buffer_pool_dump_at_shutdown = 1
#在innodb中处理用户查询后，其结果在内存空间的缓冲池已经发生变化，但是还未记录到磁盘。这种页面称为脏页，将脏页记录到磁盘的过程称为刷脏
innodb_lru_scan_depth = 2000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
#事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败，默认50s
innodb_lock_wait_timeout = 30
#日志组所在的路径，默认为data的home目录；
innodb_log_group_home_dir = /data/mysql/
#innodb_undo_directory = /data/mysql/undolog/
#这个参数控制着innodb数据文件及redo log的打开、刷写模式，http://blog.csdn.net/gua___gua/article/details/44916207
#innodb_flush_method = O_DIRECT-不经过系统缓存直接存入磁盘，
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_strict_mode = 1
#innodb独享表空间，有点很多，缺点会导致单个表文件过大
#innodb_file_per_table = 1
#undo日志回滚段 默认为128
innodb_undo_logs = 128
#传统机械硬盘建议使用，而对于固态硬盘可以关闭
#innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
#控制是否使用独立purge线程
innodb_purge_threads = 1
#改为ON时，允许单列索引最大达到3072。否则最大为767
innodb_large_prefix = 1
innodb_thread_concurrency = 8
#开启后会将所有的死锁记录到error_log中
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 16M 
########semi sync replication settings########
#半同步复制
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
#表示转储每个bp instance LRU上最热的page的百分比。通过设置该参数可以减少转储的page数。
innodb_buffer_pool_dump_pct = 40
#刷脏的进程N-1
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
#控制回收(收缩)undo log的频率.undo log空间在它的回滚段没有得到释放之前不会收缩，
innodb_purge_rseg_truncate_frequency = 128
log_timestamps=system
#该参数基于MySQL5.7 Group Replication组复制的，没有使用不要设置
#transaction_write_set_extraction=MURMUR32
#http://www.cnblogs.com/hzhida/archive/2012/08/08/2628826.html
show_compatibility_56=on

 来源：https://www.cnblogs.com/benwu/articles/7717978.html