数据库参数优化

innodb_thread_concurrency

该参数用于控制InnoDB存储引擎在执行查询时使用的并发线程数。

高并发场景，可调整该参数，减少InnoDB层的资源争用。

建议取默认值为“0”，它表示默认情况下不限制线程并发执行的数量。

innodb_read_io_threads

该参数用于控制InnoDB存储引擎在执行读操作时使用的IO线程数。

可以根据CPU核数及读写比例设置该参数来提高性能。

建议设置为1～24。

innodb_write_io_threads

该参数用于控制InnoDB存储引擎在执行写操作时使用的IO线程数。

可以根据CPU核数及读写比例设置该参数来提高性能。

建议设置为1～32。

innodb_buffer_pool_instances

该参数用于控制InnoDB缓冲池的实例数量。

增加缓冲池实例数可以减少内存热点页面的争用，从而提高内存读取的并发性。

建议设置为8～32。

innodb_open_files

在innodb_file_per_table模式下，该参数用于指定InnoDB存储引擎打开的最大文件数。

建议此值调大一些，尤其是表特别多的情况。

innodb_buffer_pool_size

该参数指定了InnoDB缓冲池的大小。InnoDB缓冲池是一个内存区域，用于存储InnoDB表的数据和索引。

增加该参数，可使更多数据Cache在内存，减少IO操作。

通常建议设置为内存的70%左右。

innodb_log_buffer_size

该参数用于控制InnoDB事务日志缓冲池的大小。

• 如果该参数设置得太小，InnoDB事务日志缓冲池可能会频繁地刷新到磁盘，导致性能下降。
• 如果该参数设置得太大，会占用过多的内存资源，导致系统的稳定性受到影响。

合理增加日志缓冲池大小，可提升事务提交效率，尤其在大事务和高并发场景，减少日志刷盘的次数。通过查看innodb_log_wait值，来调整innodb_log_buffer_size值。以下两种情况可以考虑调大innodb_log_buffer_size参数的值：

• 如果innodb_log_wait值不等于0并且持续增长时，表明InnoDB存储引擎的日志缓冲池（innodb_log_buffer）空间不足，导致innodb_log_buffer需要等待。
• 如果innodb_log_wait值较高，说明InnoDB存储引擎需要更多的日志缓冲池空间。

默认值为64MB。

innodb_io_capacity

该参数定义了InnoDB后台线程每秒可用的IO操作数，即IOPS。

建议设置为磁盘IOPS总能力的75%（磁盘IOPS是指innodb_page_size大小的数据块时的IOPS）。

innodb_log_file_size

该参数用于设置InnoDB日志文件的大小。

• 如果存在大量写操作，建议增加日志文件大小，但日志文件过大，会影响数据恢复时间。
• 如果是非生产环境，测试极限性能时，尽量调大日志文件。
• 如果是商用场景，需要考虑数据恢复时间，综合折中后设置日志文件大小。

innodb_flush_method

该参数用于指定InnoDB存储引擎刷新数据到磁盘的方式。

不同的刷新方式会影响到InnoDB存储引擎的性能和数据的安全性。

建议设置为O_DIRECT模式，直接写磁盘，需要经过OS缓冲池。

innodb_spin_wait_delay

该参数用于控制spin_lock轮询的时间间隔。

可通过热点函数来对该参数进行优化，尤其在整机单实例场景下，建议将对参数优化，小规格单实例场景默认值即可。

MySQL 8.0.20建议设置为180，MySQL 8.0.25建议设置为20。

innodb_sync_spin_loops

该参数用于控制spin_lock轮询的循环次数。

可通过热点函数来对该参数进行优化，尤其在整机单实例场景下，建议将对参数优化，小规格单实例场景默认值即可。

建议设置为25。

innodb_spin_wait_pause_multiplier

该参数用于控制spin_lock轮询间隔随机数。

可通过热点函数来对该参数进行优化，尤其在整机单实例场景下，建议将对参数优化，小规格单实例场景默认值即可。

默认值为“50”，建议设置为25～50。

innodb_lru_scan_depth

该参数用于控制InnoDB缓冲池中LRU列表的可用页数量。

在非生产环境中测试极限性能时，可以适当调大，减少checkpoint次数。

默认值为“1024”。

innodb_page_cleaners

该参数用于控制刷新脏数据的线程数。

建议设置为与innodb_buffer_pool_instances的值相同。

innodb_purge_threads

该参数用于控制回收undo的线程数。

-

innodb_flush_log_at_trx_commit

该参数用于控制事务提交时日志刷新的行为。

在非生产环境中测试极限性能时，可以设置为“0”，表示事务提交时不会立即将日志写入磁盘，而是将日志缓存在内存中。

innodb_doublewrite

该参数用于开启或关闭二次写。

在非生产环境中测试极限性能时，可以设置为“0”，关闭二次写。

ssl

该参数用于开启或关闭安全连接。

安全连接对性能影响较大，非生产环境，测试极限性能，可以设置为“0”；商用场景，根据客户需求调整。

table_open_cache_instances

该参数用于控制MySQL缓存table句柄的分区的个数。

建议设置为16～32。

table_open_cache

该参数用于控制Mysqld打开表的数量。

建议设置为“30000”。

skip_log_bin

该参数用于开启或关闭binlog。

在非生产环境中测试极限性能时，在参数文件中增加此参数，关闭binlog选项。

具体的方法为将如下信息添加至配置文件中：

1
2

skip_log_bin
#log-bin=mysql-bin

innodb_checksum_algorithm

该参数用于开启或关闭数据完整性校验。

在非生产环境中测试极限性能时，将该参数设置为“none”，关闭数据完整性校验。

binlog_checksum

该参数用于开启或关闭Binlog完整性校验。

在非生产环境中测试极限性能时，将该参数设置为“none”，关闭Binlog完整性校验。

innodb_log_checksums

该参数用于开启或关闭Log完整性校验。

在非生产环境中测试极限性能时，将该参数设置为“OFF”，关闭Log完整性校验。

foreign_key_checks

该参数用于开启或关闭外键校验。

在非生产环境中测试极限性能时，将该参数设置为“OFF”，关闭外键校验。

performance_schema

该参数用于开启或关闭性能模式。

在非生产环境中测试极限性能时，将该参数设置为“OFF”，关闭性能模式。