MySQL参数优化总结

innodb_io_capacity

描述：

此参数用于控制InnoDB引擎刷脏页的速度，数据变更只记录到内存还未写入到磁盘的数据页称为脏页；把内存页数据写入到磁盘的过程，称为刷脏页（flush）。若刷脏页速度太快超过磁盘的IO写入能力，或者磁盘IO能力很高但刷脏页速度设置得太低，都会影响数据库的性能。

默认值：

200

建议值：

设置成磁盘的 IOPS，磁盘的 IOPS 可以通过 fio 这个工具来测试，命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

innodb_flush_neighbors

描述：

若开启此参数，在刷某个脏页时，如果它旁边的内存页也是脏页，就会连带把它一起刷掉，而且这个把“邻居”拖下水的逻辑还可以继续蔓延。

默认值：

8.0（不含）版本之前默认为ON

建议值

若使用机械硬盘建议设为ON，可以减少随机IO；若是SSD建议设为OFF

innodb_stats_on_metadata

描述：

若开启此参数，在执行SHOW TABLE STATUS命令或访问INFORMATION_SCHEMA库里的表时，会更新InnoDB数据表的统计信息，更新过程可能会导致大量读盘操作，占用InnoDB buffer pool，从而影响数据库性能。

参阅：https://www.iteye.com/blog/dinglin-1575840

默认值：

5.6.5（含）之前版本默认是为ON

建议值：

OFF

innodb_file_per_table

描述：

若关闭此参数，InnoDB表的数据会存放在共享表空间（ibdata1文件）里；
若开启此参数，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中；

默认值：

5.6.5（含）之前版本默认是为OFF

建议值：

若数据放在共享表空间里，即使把数据表删除，MySQL也不会释放磁盘空间，为了方便回收空间，建议设为ON

innodb_deadlock_detect

描述：

自动检测事务之间的死锁，若检测到死锁，InnoDB会回滚其中一个事务并报错（1213 - Deadlock found when trying to get lock; try restarting transaction），这样另外一个事务就可以继续执行下去

默认值：

ON

建议值：

ON。自动检测是有性能代价的，如果锁的冲突很严重，例如经常会出现几百个事务同时更新同一行的情况，这种情况开启自动检测反而会增加机器CPU负担，此时可以考虑关闭，仅依靠等待锁超时innodb_lock_wait_timeout来处理。

innodb_lock_wait_timeout

描述：

等待行锁的超时时间，超时后放弃执行当前语句并报错（1205 - Lock wait timeout exceeded; try restarting transaction），但InnoDB默认情况下并不会自动回滚当前事务，若需要自动回滚，可以把innodb_rollback_on_timeout设置为ON

默认值：

50

建议值：

根据业务可以忍受的超时时间设置

innodb_flush_log_at_trx_commit

描述：

每次执行事务后的redo log持久化策略。
0：每次事务提交时都只把redo log留在redo log buffer中（MySQL崩溃或系统崩溃重启会导致丢数据）

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘，因此设置为0时，最严重会丢失1秒钟的日志数据。

1：每次事务提交时都将redo log持久化到磁盘（不会丢数据）
2：每次事务提交时都只把redo log写到系统的page cache（系统崩溃重启会丢数据）

默认值：

1

建议值：

只考虑性能，不考虑数据安全性建议设置为0；
不考虑性能，只考虑数据安全性建议设置为1；
两者兼顾建议设置为2；

sync_binlog

描述：

每次执行事务后的binlog持久化策略。
0：表示每次提交事务都只write到系统page cache，不fsync到磁盘
1：表示每次提交事务都会执行fsync
N>1：表示每次提交事务都write，但累积N个事务后才fsync

默认值：

0

建议值：

在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。
但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。
将 sync_binlog 设置为1，可以保证binlog不丢失，但对性能有影响。

开启慢日志

开启慢日志记录功能，可以方便我们针对慢SQL语句进行监控和及时优化：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

详情参阅这里

skip_name_resolve

描述

禁用IP反向解析主机名。不禁止的话，每次客户端连接后，MySQL都会根据客户端的IP去查询对应的主机名，如果这个查询过程很缓慢的话，会导致建立连接缓慢。详情参阅这里

注：在禁用后，MySQL用户授权表中的host字段就不能够使用域名而只能够使用IP地址了

默认值

不禁用

建议值

禁用