MySQL优化必须调整的10项配置

即使是经验老道的人也会犯错，会引起很多麻烦。所以在盲目的运用这些推荐之前，请记住下面的内容：

1. 一次只改变一个设置！这是测试改变是否有益的唯一方法。 大多数配置能在运行时使用SETGLOBAL改变。这是非常便捷的方法它能使你在出问题后快速撤销变更。但是，要永久生效你需要在配置文件里做出改动。
2. 一个变更即使重启了MySQL也没起作用？请确定你使用了正确的配置文件。请确定你把配置放在了正确的区域内(所有这篇文章提到的配置都属于[mysqld])
3. 服务器在改动一个配置后启不来了：请确定你使用了正确的单位。例如,innodb_buffer_pool_size的单位是MB而max_connection是没有单位的。
4. 不要在一个配置文件里出现重复的配置项。如果你想追踪改动，请使用版本控制。
5. 不要用天真的计算方法，例如”现在我的服务器的内存是之前的2倍，所以我得把所有数值都改成之前的2倍“。

基本配置你需要经常察看以下3个配置项。不然，可能很快就会出问题。

innodb_buffer_pool_size

这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

innodb_log_file_size

这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1，它都难于调整，因为一方面你想让它更大来提高性能，另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。一直到MySQL 5.5，redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。
一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6，你可以一开始就把它这是成4G。

max_connections

如果你经常看到‘Too many connections’错误，是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接，你需要比默认的151连接数更大的值。max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。InnoDB配置从MySQL 5.5版本开始，InnoDB就是默认的存储引擎并且它比任何其他存储引擎的使用都要多得多。那也是为什么它需要小心配置的原因。

innodb_file_per_table

这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里（innodb_file_per_table = OFF） 或者为每张表的数据单独放在一个.ibd文件（innodb_file_per_table = ON）。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的，比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是：有非常多的表（比如10k+）。
MySQL 5.6中，这个属性默认值是ON，因此大部分情况下你什么都不需要做。对于之前的版本你必需在加载数据之前将这个属性设置为ON，因为它只对新创建的表有影响。

innodb_flush_log_at_trx_commit

默认值为1，表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大的开销，因为每次将改变flush到redo日志都需要额外的fsyncs。将它的值设置为2会导致不太可靠（reliable）因为提交的事务仅仅每秒才flush一次到redo日志，但对于一些场景是可以接受的，比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了，但在系统崩溃时可能丢失一些数据：只适用于备份节点。

mysql> set global innodb_flush_log_at_trx_commit = 1;   
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like '%at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

innodb_flush_method

这项配置决定了数据和日志写入硬盘的方式。一般来说，如果你有硬件RAID控制器，并且其独立缓存采用write-back机制，并有着电池断电保护，那么应该设置配置为O_DIRECT；否则，大多数情况下应将其设为fdatasync（默认值）。sysbench是一个可以帮助你决定这个选项的好工具。

innodb_log_buffer_size

这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量，如果它不是0，增加innodb_log_buffer_size。其他设置query_cache_size: query cache（查询缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。 最佳选项是将其从一开始就停用，设置query_cache_size = 0（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果你已经为你的应用启用了query cache并且还没有发现任何问题，query cache可能对你有用。这是如果你想停用它，那就得小心了。

log_bin

如果你想让数据库服务器充当主节点的备份节点，那么开启二进制日志是必须的。如果这么做了之后，还别忘了设置server_id为一个唯一的值。就算只有一个服务器，如果你想做基于时间点的数据恢复，这（开启二进制日志）也是很有用的：从你最近的备份中恢复（全量备份），并应用二进制日志中的修改（增量备份）。二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽，你可以用 PURGE BINARY LOGS 来清除旧文件，或者设置 expire_logs_days 来指定过多少天日志将被自动清除。
记录二进制日志不是没有开销的，所以如果你在一个非主节点的复制节点上不需要它的话，那么建议关闭这个选项。

skip_name_resolve

当客户端连接数据库服务器时，服务器会进行主机名解析，并且当DNS很慢时，建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了，因此在添加这项设置到一个已有系统中必须格外小心。

binlog_cache_size

可以看看这篇文章：mysql innodb binlog_cache_size 设置
为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存,提高bin-log的效率。默认值：32768 – 即 32K 范围：4096 .. 4294967295 根据业务：没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是1048576 –1M 后者建议是： 2097152 – 4194304 即 2–4M
配置文件设置my.cnf binlog_cache_size = 1M 临时设置：set global binlog_cache_size = 1048576;

binlog_stmt_cache_size

基于会话的参数，用于决定非事务SQL段的缓冲大小。如果在事务中经常使用大的非事务SQL段的话增加这个参数的大小可以有一些好处。可以通过binlog_stmt_cache_use 和 binlog_stmt_cache_disk_use来判断这个值是否合适。

mysql> show session variables like 'binlog_stmt%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| binlog_stmt_cache_size | 32768 |
+------------------------+-------
mysql> set global binlog_stmt_cache_size = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'binlog_stmt%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| binlog_stmt_cache_size | 1048576 |
+------------------------+---------+
1 row in set (0.00 sec)

sync_binlog

sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

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

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

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

set global sync_binlog=0;
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

Open_table_definitions 与 table_definition_cache、

在MySQL 5.1.3之后，还添加了2个状态值：Open_table_definitions和Opened_table_definitions。这2个值代表的意思如下：
Open_table_definitions ：代表当前缓存了多少.frm文件。
table_definition_cache：控制了表定义缓存中表的个数，server层和InnoDB层的表定义缓存共用此参数，自从MySQL启动后，缓存了.frm文件的数量。该参数值的代表MySQL可以缓存的表定义的数量。
需要注意的是.frm文件是MySQL用于存放表结构的文件，对应myisam和innodb存储引擎都必须有的。
通过增加或减少table_definition_cache的值，您可以操纵内存使用量。
要设置table_definition_cache的更改，请编辑/etc/my.cnf文件。
这一次，我在文件中添加了一行名为table_definition_cache = 600 的行。

mysql>  show global status like 'Open%_table_definitions';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Open_table_definitions   | 403      |
| Opened_table_definitions | 31318377 |
+--------------------------+----------+
2 rows in set (0.00 sec)

mysql> set global table_definition_cache = 600;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like 'table_definition_cache';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| table_definition_cache | 600   |
+------------------------+-------+
1 row in set (0.00 sec)

table_open_cache 与 open_tables

table_open_cache：指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。用于控制MySQL Server能同时打开表的最大个数。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加
open_tables：当前table_cache里面缓存的table object数量

如果你发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么你就需要增加table_open_cache的值了

mysql> SHOW STATUS LIKE 'Open%tables'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 3     |
| Opened_tables | 26    |
+---------------+-------+
2 rows in set (0.03 sec)
mysql> show variables like "table_open_cache%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache           | 1024  |
| table_open_cache_instances | 1     |
+----------------------------+-------+
2 rows in set (0.00 sec)

总结

当然还有其他的设置可以起作用，取决于你的负载或硬件：在慢内存和快磁盘、高并发和写密集型负载情况下，你将需要特殊的调整。然而这里的目标是使得你可以快速地获得一个稳健的MySQL配置，而不用花费太多时间在调整一些无关紧要的MySQL设置或读文档找出哪些设置对你来说很重要上。

转载：https://jingyan.baidu.com/article/ab0b56307a809fc15bfa7d72.html