十条MySQL性能优化的设置（mysql语句性能优化）

本文为翻译文章，原文：https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/

在这篇博客中，我们将要讨论在MySQL安装完成之后，你可以做出的前十条性能优化设置。

当我们作为一个MySQL性能审核员被雇用时，我们的工作就是来检查MySQL配置并提出改进的建议。在大部分情况下，即使有上百个可用的选项，我们也只建议修改少数几个MySQL性能优化的设置，许多人对此感觉惊讶。这篇帖子的目标是给你一个MySQL中最关键设置的列表。

开始之前。。。

即使经验丰富的人也可能犯一些导致许多麻烦的错误。所以在无脑运用这篇帖子的推荐之前，要记住下列事项：

• 一次只修改一个设置！这是评估修改是否有用的唯一办法
• 大部分设置都可以通过SET GLOBAL在运行时进行修改。它非常便捷，而且在出现问题时可以迅速恢复设置。但是最终，你想要此设置在配置文件中永久生效。
• 配置中的修改在ＭySQL重启后不生效？你使用正确的配置文件了吗？你把设置放在正确的章节了吗？（这篇帖子的所有设置都属于［mysqld］章节）
• 设置修改后，服务器启动不了：你使用了正确的单位吗？比如innodb_buffer_pool_size应该以字节为单位进行设置，但max_connection是无量纲的。
• 配置文件中不要出现重复的设置。如果你想跟踪变量，请使用版本控制。
• 不要使用简单的算术，比如“我的新机器有两个RAM，那么我把所有的值都乘以２”

基本设置

这里有三个你需要紧密关注的ＭySQL性能调优设置。否则，你很有可能很快就会碰到问题：

innodb_buffer_pool_size：这是任何以InnoDB安装时需要关注的最重要的设置。缓冲池是数据和索引被缓存的地方：把它设置得尽可能地大，能够确保大部分的读操作都是从内存而不是磁盘来进行。典型的值是5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM)。

innodb_log_file_size：这是重做日志的大小。重做日志用来确保写操作很快且是持久化的，即使在灾难恢复时也这样。一直到MySQL5.1都很难做调整，因为你又想要大的重做日志以获得很好的性能，又想要小的重做日志以快速进行灾难恢复。幸运的是，MySQL5.５以来，灾难恢复的性能大大提升了，所以你现在可以有较好的数据写的性能和快速的灾难恢复。直到ＭySQL5.5，重做日志的总大小被限制在４GB以内（默认有两个日志文件）。这个值在ＭySQL5.６中有提升。以这个设置开始：innodb_log_file_size = 512M (假如有１Ｇ的重做日志），应该可以给你很多数据写的空间。如果你的应用是写密集型的，并且MySQL版本是５.6，开始的时候你可以把它设置为４G： innodb_log_file_size = 4G。

max_connections：如果你经常碰到“Too many connections”的错误，那么max_connections的值太低了。这经常是因为程序没有正确关闭数据连接而造成的，你需要比默认的151个更多的连接数。把max_connections的值设得太高（比如1000个或者更多）的主要缺陷是，服务器会由于它需要运行1000个或者更多活跃的事务而变得没有响应。在程序层面使用连接池，或者在MySQL层面使用线程池是有帮助的。

InnoDB设置

从MySQL5.5开始，InnoDB已经成为默认的存储引擎，并且比其它的存储引擎更经常地被使用。这就是需要仔细配置它的原因。

innodb_file_per_table：这个设置告诉InnoDB，它应该把数据存储在共享的表空间（innodb_file_per_table=OFF）还是应该存储在每个表都有的单独的.ibd文件中（innodb_file_per_table=ON）。每个表都有一个文件，能够在删除，清空或者重建一个表的时候回收空间。对于一些高级的特性比如压缩，它也是需要的。然而它并不能提供任何性能方面的好处，你不想要每个表都有一个文件的主要场景是当你有非常多的表的时候（比如1万多个）。

MySQL5.6中的默认值是ON，所以大部分情况下你都不需要做什么。在之前的版本，你需要在导入数据前把它设置为ON，因为它仅仅影响新创建的表。

innodb_flush_log_at_trx_commit: 默认值是1，意味着InnoDB完全遵守ACID。如果你主要的考量是数据安全，比如在一个主服务器上，那么它是最好的选择。然而，每次修改都需要额外地同步到重做日志中，由此造成的磁盘慢读写会给系统带来显著的负担。把它设置为2不那么可靠，因为提交的事务要每隔一秒同步到重做日志，但对于一个主服务器来说，在某些场合是可以接受的，对于从服务器它肯定是一个很好的选项。0的话更快，但你更有可能在系统崩溃的时候丢失一些数据：它仅仅对于从服务器来说是一个好的选择。

innodb_flush_method: 这个设置控制数据和日志如何同步到磁盘中。当你有一个用蓄电池保护回写缓存的硬件RAID控制器，那么常用的设置是O_DIRECT，fdatasync（默认值）适用于其它大部分场景。sysbench是一个利器，帮助你在这两个值之间做出选择。

innodb_log_buffer_size：这是未提交事务的缓冲大小，默认值（1MB）通常是没问题的，然而，一旦事务包含大的blob/text字段，缓冲很快就装满并触发额外的I/O读写。看一下Innodb_log_waits状态变量，如果它不是0，那么增加innodb_log_buffer_size的值。

其它设置

query_cache_size：查询缓存是众所周知的瓶颈，即使并发量没那么大的时候。最好的选择是从第一天开始就把设置为0（现在是MySQL5.6的默认值），并使用其它的手段来加快读取：良好的索引，添加从服务器以分散读操作的负担，或者使用外部的缓存（比如memcache或者redis）。如果你的MySQL应用已经使用了查询缓存但你从来没有看到过任何问题，那么查询缓存对你是有帮助的。所以如果你决定去掉这个选项，应该要谨慎。

log_bin：如果服务器要扮演主从复制中的主服务器，那么打开二进制日志的选项是强制的。如果是这种情形，别忘了同时把server_id设置成一个独特的值。对于单个服务器，如果你想要按时间点来进行恢复，那么它也非常有用：恢复最近的备份并应用这些二进制日志。一旦创建，二进制日志文件被永久保留。所以如果你不想磁盘空间用完，你要么用PURGE BINARY LOGS来清除旧的文件，要么设置expire_logs_days来指定多少天以后，这些日志被自动清除。然而二进制日志并不是免费的，所以主从复制上不是主服务器的实例，推荐把这个选项diabled掉。

skip_name_resolve：当一个客户端连接的时候，服务器会执行主机名解析的操作，如果DNS很慢的话，建立此连接也会变得很慢。因而推荐使用skip_name_resolve来启动服务器，以禁用所有的DNS查找。唯一的限制是，GRANT语句必须只使用IP地址，如果在现存的系统中进行设置，一定要非常小心。

结论

当然还有其他的设置也很重要，这与你的工作负载或者你的硬件有关：较低内存和快速磁盘，高并发，写密集的工作负载，这些都是需要特定调优的手段。然而，这里的目标是给你一些MySQL调优技巧，让你能快速得到一份健全的MySQL配置，而不用花费大量的时间来修改一些非必要的MySQL设置或者阅读文档来搞清楚哪些设置对你很重要。