mysql 影响性能的参数

innodb_flush_log_at_trx_commit

innodb 专有

参数值有0、1、2

0：log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush(刷到磁盘)操作同时进行。该模式下在事

务提交的时候，不会主动触发写入磁盘的操作(最快)。

1：每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且 flush(刷到磁盘)中去，该模式为系

统默认(最慢)。

2：每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，但是 flush(刷到磁盘)操作并不会

同时进行。该模式下，MySQL 会每秒执行一次 flush(刷到磁盘)操作。

table_open_cache

当 MySQL 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果还

没有被缓存，但是在 MySQL 表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区；

如果表缓存满了，则会按照一定的规则将当前未用的表释放，或者临时扩大表缓存来存

放，使用表缓存的好处是可以更快速地访问表中的内容。

在 mysql 默认安装情况下，table_cache 的值在 2G 内存以下的机器中的值默认时 256 到

512，如果机器有 4G 内存,则默认这个值是 2048。不能太小了，会影响性能。

innodb_buffer_pool_instances

InnoDB缓冲池被划分为的区域数。对于缓冲池在多兆字节范围内的系统，将缓冲池划分为单独的实例，可以通过减少不同线程读写缓存页面时的争用来提高并发性。存储在哈希函数中或从缓冲池中读取的每个页面都使用哈希功能随机分配给该缓冲池实例之一。每个缓冲区池都管理它自己的免费列表、刷新列表、LRU和连接到缓冲区池的所有其他数据结构，并由它自己的缓冲区池互斥锁保护。此选项仅在将innodb_buffer_pool_size设置为1GB或更多时生效。缓冲池大小划分分为所有缓冲池。为了获得最佳效率，请指定innodb_buffer_pool_instances和innodb_buffer_pool_size的组合，以使每个缓冲池实例至少为1GB。

可以开启多个内存缓冲池，把需要缓冲的数据 hash 到不同的缓冲池中，这样可以并行的内

存读写。innodb_buffer_pool_size 设置大一些并且保证每个 pool 实例>1G 可以提高性能。

sync_binlog

控制 mysql,binlog 日志刷新策略

sync_binlog=0：禁用MySQL服务器对二进制日志与磁盘的同步。相反，MySQL服务器依赖于操作系统来不时地将二进制日志刷新到磁盘上，就像它对任何其他文件一样。此设置提供了最佳性能，但在发生电源故障或操作系统崩溃时，服务器可能提交了尚未同步到二进制日志的事务。关闭日志同步

sync_binlog=1：允许在提交事务之前将二进制日志同步到磁盘。这是最安全的设置，但由于磁盘写入数量的增加，可能会对性能产生负面影响。如果断电或操作系统崩溃，二进制日志中缺少的事务仅处于准备就绪状态。这允许自动恢复例程回滚事务，从而保证在二进制日志中不会丢失任何事务。每次提交都同步(慢)

sync_binlog=N，其中N是0或1以外的值：收集到N个二进制日志提交组后，二进制日志同步到磁盘。如果发生故障或操作系统崩溃，服务器可能提交的事务尚未刷新到二进制日志。由于磁盘写入次数的增加，此设置可能会对性能产生负面影响。更高的值可提高性能，但会增加数据丢失的风险。N 次提交都同步

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

具体的取值跟硬件配置和工作负载相关，最好做一次压测来决定。不过通常来说，linux 环

境下具有 raid 控制器和 write-back 写策略，o_direct 是比较好的选择；如果存储介质是

SAN，那么使用默认 fsync 或者 osync 或许更好一些。

query_cache_size

对于使用 MySQL 的用户，对于这个变量大家一定不会陌生。前几年的 MyISAM 引擎优化中，

这个参数也是一个重要的优化参数。但随着发展，这个参数也爆露出来一些问题。

机器的内存越来越大，人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加

大后也引发了一系列问题。我们首先分析一下 query_cache_size 的工作原理：

一个 SELECT 查询在 DB 中工作后，DB 会把该语句缓存下来，当同样的一个 SQL 再次来到 DB

里调用时，DB 在该表没发生变化的情况下把结果从缓存中返回给 Client。

这里有一个关建点，就是 DB 在利用 Query_cache 工作时，要求该语句涉及的表在这段时间

内没有发生变更。那如果该表在发生变更时，Query_cache 里的数据又怎么处理呢？首先要

把 Query_cache 和该表相关的语句全部置为失效，然后在写入更新。那么如果 Query_cache

非常大，该表的查询结构又比较多，查询语句失效也慢，一个更新或是 Insert 就会很慢，

这样看到的就是 Update 或是 Insert 怎么这么慢了。

所以在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，

写入量大的系统，建系把该功能禁掉。

tmp_table_size

控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量 tmpdir 决定

max_heap_table_size

用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。