13个MySQL调优的配置参数

字数统计: 1k   |   阅读时长≈ 3 分钟

整理了13个Mysql常见的调优参数，类比项目中发现，如果是自建的MySQL服务基本要全部设置优化下，如果是云端的产品很多都是大厂已经调节过后的配置。我只是把参数都整理出来了，并且写了一些自己理解的描述和网上查考了一些回答。更权威的还是要撸下官网文档的~~

INNODB_BUFFER_POOL_SIZE

这是InnoDB里最重要的一个变量，它指定了buffer pool的大小，buffer pool通俗上来说是减少数据在硬盘上的I/O，buffer pool中的数据会定期的去刷盘（刷脏页），这个数值推荐位80%物理内存空间

查看

1
SHOW variables LIKE "INNODB_BUFFER_POOL_SIZE"

QUERY_CACHE_TYPE

这个主要是管理查询缓存，但是高并发下并不推荐使用它并没有提升性能的作用

  • SQL hash值作为键，查询结果为值。这样即使相同语义的查询语句因为条件不同也会无法命中缓存
  • 数据表中有一条数据变动全表缓存作废，查询缓存失效会涉及并发访问控制，会加锁。

INNODB_LOG_FILE_SIZE

InnoDB 的 redo log 文件大小，这边谈下自己的理解。每次更改数据会写入 buffer pool （内存），并且同时会写入文件日志redo log,因为写日志是顺序操作的所以会优于直接写入数据存储文件同时也保证了数据的一致性。这个值在6.5版本后是48M

查看

1
SHOW variables LIKE "INNODB_LOG_FILE_SIZE"

INNODB_FLUSH_LOG_AT_TRX_COMMIT

这个参数控制 ‘redo log buffer’数据写入日志文件并flush磁盘的时间点

0: log buffer 每秒写入 log file,并且log file flush 同时进行

1: 事务提交会将log buffer 写入 log file 并且flush（默认）

2: 事务提交会将 log buffer 写入 log file 但是flush不会同时进行，会每秒执行一次

ps: 设置为1最高的保证了数据的安全，设置为2最高的保证了效率，设置为0是折中方案

1
SHOW variables LIKE "INNODB_FLUSH_LOG_AT_TRX_COMMIT"

SYNC_BINLOG

是MySQL的二进制日志（binary log）同步到磁盘的频率

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。这个是性能最好的。

sync_binlog=1，当每进行1次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

这个参数默认是0，为了保证数据安全建议设置1

1
SHOW variables LIKE "SYNC_BINLOG"

INNODB_IO_CAPACITY

当刷新脏页时，控制每秒的IO量

1
SHOW variables LIKE "innodb_io_capacity"

INNODB_IO_CAPACITY_MAX

在紧急情况下刷脏页的IO量,innodb_io_capacity_max设置是所有buffer pool例的总限制。一般这个限制是官方建议是INNODB_IO_CAPACITY的2倍

INNODB_FLUSH_METHOD

控制数据文件及redo log的打开，刷写模式，有3个配置推荐用O_DIRECT

1
SHOW variables LIKE "INNODB_FLUSH_METHOD"

INNODB_BUFFER_POOL_INSTANCES

优化在搞并发环境下的吞吐量，5.6过后推荐配置8-16 按照mysql官方解释就是在高并发场景下分多个buffer pools 减少资源占用的问题

1
SHOW variables LIKE "INNODB_BUFFER_POOL_INSTANCES"

INNODB_THREAD_CONCURRENCY

默认是0，标识没有并发线程数限制，官方建议当并发用户数小于64 innodb_thread_concurrency=0

SKIP_NAME_RESOLVE

建议打开这个参数，如果MySQL上设置了DNS服务器，并且客服端IP在DNS没有对应的hostname，就会出现等待。打开这个参数可以跳过这个等待。

1
SHOW variables LIKE "SKIP_NAME_RESOLVE"

INNODB_STATS_ON_METADATA

5.5版本之前介意关闭，5.6以后默认关闭

default_storage_engine

MySQL5.6以后的版本默认都是InnoDB,查看或者更换这个参数可以

1
SHOW variables LIKE "default_storage_engine"
  • 版权声明： 本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！

请我喝杯咖啡吧~