MySQL关键参数调优建议

innodb_buffer_pool_size

• 适用版本：8.0、5.7、5.6、5.5
• 默认值：134217728
• 修改完后是否需要重启：是
• 作用：InnoDB缓冲池大小， 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。
• 修改建议：系统内存的3/4，如32G内存则设置24G。

innodb_log_file_size、innodb_log_files_in_group

• 通常，日志文件的总大小应足够大，以使服务器可以消除工作负载活动中的高峰和低谷，这通常意味着有足够的重做日志空间来处理一个小时以上的写活动。
• 值越大，缓冲池中需要的检查点刷新活动越少，从而节省了磁盘I / O。较大的日志文件也会使崩溃恢复变慢。
• 日志文件的总大小（innodb_log_file_size * innodb_log_files_in_group）不能超过略小于512GB的最大值。
• 可以在高峰期间采样1分钟产生日志量，计算1小时的日志量。设置为1~2小时的日志量即可。如：20M * 90 = 1800M；
• innodb_log_files_in_group:2, innodb_log_file_size=900M 或 innodb_log_files_in_group:3, innodb_log_file_size=600M

innodb_flush_log_at_trx_commit

• 适用版本：8.0、5.7、5.6、5.5
• 默认值：1
• 修改完后是否需要重启：是
• 作用：控制提交操作的严格ACID遵从性与更高的性能之间的平衡，当重新安排并批量执行与提交相关的I/O操作时，可以实现更高的性能。可以通过更改默认值来获得更好的性能，但随后可能会在崩溃中丢失事务。
  • 1: 事务提交时，把事务日志从缓存区写到日志文件中，并且立刻写入到磁盘上。
  • 0: 日志每秒写入一次并刷新到磁盘。尚未刷新日志的事务可能会在崩溃中丢失。
  • 2：事务提交时，把事务日志从缓存区写到日志文件中，但不一定立刻写入到磁盘上。日志文件会每秒写入到磁盘，如果写入前系统崩溃，就会导致最后1秒的日志丢失。
• 修改建议：能够容忍系统崩溃，丢失1秒的数据，并对性能要求极高的场景可以设置为2，否则建议保持默认1。

innodb_io_capacity

• 适用版本：8.0、5.7、5.6、5.5
• 默认值：200
• 修改完后是否需要重启：是
• 作用：参数定义了InnoDB后台任务每秒可用的I/O操作数（IOPS），例如用于从buffer pool中刷新脏页和从change buffer中合并数据。innodb后台进程最大的I/O性能指标，影响刷新赃页和插入缓冲的数量，在高转速磁盘下，尤其是现在SSD盘得到普及，可以根据需要适当提高该参数的值。
• 修改建议：建议设置为innodb_io_capacity_max的1/2(系统IOPS的40%~50%)，通常SSD设置为2000，

innodb_io_capacity_max

• 适用版本：8.0、5.7、5.6、5.5
• 默认值：2000
• 作用：在压力下，控制当刷新脏数据时MySQL每秒执行的写IO量解释一下什么叫“在压力下”，MySQL中称为”紧急情况”，是当MySQL在后台刷新时，它需要刷新一些数据为了让新的写操作进来。
• 修改建议：建议设置为略低于系统IOPS，innodb_io_capacity的2倍，(系统IOPS的80%~90%)，通常SSD设置为4000，普通HHD设置200-400即可。

sync_binlog

• 适用版本：8.0、5.7、5.6、5.5
• 默认值：1
• 修改完后是否需要重启：是
• 作用：控制MySQL服务器将二进制日志同步到磁盘的频率。
  • 1: 事务提交后，将二进制日志文件写入磁盘并立即刷新，相当于同步写入磁盘，不经过系统缓存。
  • 0: 禁止MySQL服务器将二进制日志同步到磁盘。
  • N：每写入1000次系统缓存就执行一次写入磁盘并刷新的操作，会有数据丢失的风险。
• 修改建议：能够容忍系统崩溃时丢失数据，并对性能要求极高的场景可以提高此参数如1000，否则建议保持默认1。

back_log

• 适用版本：8.0、5.7、5.6、5.5
• 默认值：1
• 修改完后是否需要重启：是
• 作用：MySQL每处理一个连接请求时都会创建一个新线程与之对应。在主线程创建新线程期间，如果前端应用有大量的短连接请求到达数据库，MySQL会限制这些新的连接进入请求队列，由参数back_log控制。如果等待的连接数量超过back_log的值，则将不会接受新的连接请求，所以如果需要MySQL能够处理大量的短连接，需要提高此参数的大小。
• 修改建议：3000

innodb_flush_method

• 适用版本：8.0、5.7、5.6、5.5
• 默认值：fsync(unix)
• 作用：定义用于将数据刷新到InnoDB数据文件和日志文件的方法，这可能会影响I/O吞吐量。
• 修改建议：当数据盘使用SSD时，不需要使用多级缓冲，设置成O_DIRECT；如果您使用或打算将其他存储设备用于redo log files和data files，并且您的数据文件驻留在具有非电池后备缓存的设备上，请改用O_DIRECT

max_connections、max_user_connections

• 适当加大相应配置
• 保证max_connections > max_user_connections 20这样即可
• 如: 5000/4800

interactive_timeout、wait_timeout 适当调整

• interactive_timeout：交互式连接超时时间(mysql工具、mysqldump等)
• wait_timeout：非交互式连接超时时间，默认的连接mysql api程序,jdbc连接数据库等

join_buffer_size、read_buffer_size、sort_buffer_size、 适当增加

• join_buffer_size：用于普通索引扫描，范围索引扫描和不使用索引的联接的缓冲区的最小大小，从而执行全表扫描。通常，获得快速联接的最佳方法是添加索引。
• sort_buffer_size: 每个必须执行排序的会话都会分配此大小的缓冲区。sort_buffer_size并非特定于任何存储引擎，而是以一般方式进行优化。
• read_buffer_size：对MyISAM表进行顺序扫描的每个线程都会为其扫描的每个表分配此大小（以字节为单位）的缓冲区。如果进行多次顺序扫描，则可能需要增加此值，默认为131072，不使用MyISAM引擎可以不用改。

innodb_autoinc_lock_mode

• 适用版本：8.0、5.7、5.6、5.5
• 默认值：1
• 修改完后是否需要重启：是
• 作用：在MySQL 5.1.22后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode，用于控制自增主键的锁机制。该参数可以设置的值为0、1、2，RDS默认的参数值为1，表示InnoDB使用轻量级别的mutex锁来获取自增锁，替代最原始的表级锁。但是在load data（包括INSERT … SELECT和REPLACE … SELECT）场景下若使用自增表锁，则可能导致应用在并发导入数据时出现死锁。
  现象：在load data（包括INSERT … SELECT和REPLACE … SELECT）场景下若使用自增表锁，在并发导入数据时出现如下死锁：
  RECORD LOCKS space id xx page no xx n bits xx index PRIMARY of table xx.xx trx id xxx lock_mode X insert intention waiting. TABLE LOCK table xxx.xxx trx id xxxx lock mode AUTO-INC waiting；
• 修改建议：建议将该参数值改为2，表示所有情况插入都使用轻量级别的mutex锁（只针对row模式），这样就可以避免auto_inc的死锁，同时在INSERT … SELECT的场景下性能会有很大提升。
• 注：当该参数值为2时，binlog的格式需要被设置为row。

query_cache_size

• 适用版本：5.7、5.6、5.5
• 默认值：1048576
• 修改完后是否需要重启：否
• 作用：该参数用于控制MySQL query cache的内存大小。如果MySQL开启query cache，在执行每一个query的时候会先锁住query cache，然后判断是否存在于query cache中，如果存在则直接返回结果，如果不存在，则再进行引擎查询等操作。同时，insert、update和delete这样的操作都会将query cahce失效掉，这种失效还包括结构或者索引的任何变化。但是cache失效的维护代价较高，会给MySQL带来较大的压力。所以，当数据库不会频繁更新时，query cache是很有用的，但如果写入操作非常频繁并集中在某几张表上，那么query cache lock的锁机制就会造成很频繁的锁冲突，对于这一张表的写和读会互相等待query cache lock解锁，从而导致select的查询效率下降。
• 现象：数据库中有大量的连接状态为checking query cache for query、Waiting for query cache lock、storing result in query cache。
• 修改建议：MySQL默认是关闭query cache功能的，如果您的实例打开了query cache，当出现上述情况后可以关闭query cache。

net_write_timeout

• 适用版本：8.0、5.7、5.6、5.5
• 默认值：60
• 修改完后是否需要重启：否
• 作用：等待将一个block发送给客户端的超时时间。
• 现象：若参数设置过小，可能会导致客户端出现如下错误：
  the last packet successfully received from the server was milliseconds ago或the last packet sent successfully to the server was milliseconds ago.
• 修改建议：一般在网络条件比较差时或者客户端处理每个block耗时较长时，由于net_write_timeout设置过小导致的连接中断很容易发生，建议增加该参数的大小。

tmp_table_size

• 适用版本：8.0、5.7、5.6、5.5
• 默认值：2097152
• 修改完后是否需要重启：否
• 作用：该参数用于决定内部内存临时表的最大值，每个线程都要分配，实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表。优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。
• 现象：如果复杂的SQL语句中包含了group by、distinct等不能通过索引进行优化而使用了临时表，则会导致SQL执行时间加长。
• 修改建议：如果应用中有很多group by、distinct等语句，同时数据库有足够的内存，可以增大tmp_table_size（max_heap_table_size）的值，以此来提升查询性能。