前言：MYSQL应该是最好的WEB后端数据库。虽然最近NOSQL被提及的越来越多，但相信大部分架构师还是会选择MYSQL来进行数据存储。本文作者总结整理了MySQL性能调优的15个重要变量。如有不足之处需要补充，请指出。1.DEFAULT_STORAGE_ENGINE如果你已经在使用MySQL5.6或者5.7，并且你的数据表都是InnoDB，那么你已经设置好了。如果不是，请确保将表转换为InnoDB并将default_storage_engine设置为InnoDB。为什么？简而言之，因为InnoDB是MySQL（包括PerconaServer和MariaDB）最好的存储引擎——它支持事务，具有高并发性，并且具有非常好的性能（如果配置正确）。2.INNODB_BUFFER_POOL_SIZE这是InnoDB最重要的变量。事实上，如果你的主要存储引擎是InnoDB，那么这个变量对你来说对于MySQL来说是最重要的。基本上，innodb_buffer_pool_size指定了MySQL应该分配多少内存给InnoDB缓冲池。InnoDB缓冲池用于存储缓存数据、二级索引、脏数据（已更改但未刷新到硬盘的数据）以及Adaptivehashindex等各种内部结构。根据经验，一个独立的MySQL服务器应该分配整个MySQL机器总内存的80%。如果您的MySQL运行在共享服务器上，或者您想知道InnoDB缓冲池大小是否设置正确，请参阅此处了解详细信息。3.INNODB_LOG_FILE_SIZEInnoDB重做日志文件的设置，在MySQL社区也叫事务日志。在MySQL5.6.8之前，事务日志默认值innodb_log_file_size=5M是唯一绝对的InnoDB性能杀手。从MySQL5.6.8开始，默认值增加到48M，但对于许多稍微繁忙的系统来说，这个值仍然低得多。根据经验，您应该将日志大小设置为能够在服务器繁忙时存储1-2小时的写入。如果不想那么麻烦，设置1-2G的大小，性能会很好。在进入下一个变量之前，让我们快速提及innodb_log_buffer_size。“快速提及”因为它经常被误解并且经常被过度关注。事实上，大多数时候你只需要使用一个小缓冲区——足以容纳你的小事务更改，直到事务被提交并写入磁盘。当然，如果你有大量的大事务更改，那么更改一个大于默认的innodblogbuffersize的值会在一定程度上提高你的性能，但是你使用的是autocommit，或者你的事务更改少了几个k,然后保持默认值。4.INNODB_FLUSH_LOG_AT_TRX_COMMIT默认情况下，innodb_flush_log_at_trx_commit设置为1，表示InnoDB在每次事务提交后立即将同步数据刷新到硬盘。如果您使用自动提交，那么您的每个INSERT、UPDATE或DELETE语句都是一个事务提交。同步是一项昂贵的操作（特别是如果您没有回写缓存），因为它涉及对硬盘的实际同步物理写入。所以如果可能的话不建议使用默认值。两个可选值是0和2：*0表示刷新到硬盘，但不同步（提交事务时没有实际的IO操作）*2表示不刷新不同步（没有实际的IO操作）所以如果将它设置为0或2，则每秒执行一次同步操作。所以明显的缺点是你可能会丢失最后一秒的提交数据。具体来说，如果你的交易已经提交了，但是服务器马上断电了，那么你的提交就相当于没有发生过。如图所示，对于银行等金融机构来说，这是无法容忍的。不过对于大多数网站来说，可以设置为innodb_flush_log_at_trx_commit=0|2，即使服务器最终崩溃也没有什么大问题。毕竟，就在几年前，还有许多使用MyISAM的站点在崩溃时会丢失30秒的数据（更不用说慢得令人抓狂的修复过程）。那么，0和2之间的实际区别是什么？性能上的显着差异可以忽略不计，因为刷新到操作系统缓存的速度非常快。所以显然应该设置为0，万一MySQL崩溃（不是整机），你不会丢失任何数据，因为数据已经缓存在OS中，最终会同步到硬盘。5、SYNC_BINLOG已经向sync_binlog写入了大量文件，它与innodb_flush_log_at_trx_commit的关系，简单介绍一下：a)如果你的服务器没有从服务器，而且你没有做备份，那么设置sync_binlog=0就可以了为了表现。b)如果你有slave并且做备份，但你不介意在master崩溃时丢失二进制日志中的一些事件，那么设置sync_binlog=0以获得更好的性能。c)如果你有slaves和backups，你很在意slaves的一致性，以及能否恢复到某个时间点（通过使用最好的一致性备份和二进制日志将数据库恢复到特定的时间点)，那么你应该设置innodb_flush_log_at_trx_commit=1，并且需要认真考虑使用sync_binlog=1。问题是sync_binlog=1比较昂贵——现在每个事务都要同步到硬盘一次。你可能会想为什么不将两个同步合并为一个，你是对的——较新版本的MySQL（5.6和5.7，MariaDB和PerconaServer）已经有合并提交，所以sync_binlog=1在这种情况下不起作用昂贵，但是在旧的mysql版本中仍然有很大的性能损失。6.INNODB_FLUSH_METHOD将innodb_flush_method设置为O_DIRECT以避免双缓冲。你不应该使用O_DIRECT的唯一情况是你的操作系统不支持它。但如果您运行的是Linux，请使用O_DIRECT启用直接IO。双缓冲将取代直接IO，因为所有数据库更改将首先写入操作系统缓存，然后同步到磁盘-因此InnoDB缓冲池和操作系统缓存都将保存相同数据的副本。特别是如果您的缓冲池被限制为总内存的50%，这意味着您可能会在写入密集型环境中浪费高达50%的内存。如果不限制在50%，服务器可能会因为OS缓存压力大而使用swap。很简单，设置innodb_flush_method=O_DIRECT。7.INNODB_BUFFER_POOL_INSTANCESMySQL5.5引入了缓冲区实例作为减少内部锁争用以提高MySQL吞吐量的方法。在5.5版本，这对提高吞吐量帮助不大，而在MySQL5.6版本，提升非常大，所以在MySQL5.5可以保守设置innodb_buffer_pool_instances=4，在MySQL5.6和5.7可以设置为8-16缓冲池实例。设置后您会发现性能几乎没有提高，但在大多数高负载情况下它应该表现良好。顺便说一句，不要指望此设置会减少您的个别查询的响应时间。这仅在具有高并发负载的服务器上可见。例如，多个线程同时做很多事情。8.INNODB_THREAD_CONCURRENCY你可能经常听到应该设置innodb_thread_concurrency=0然后不管它。但这只有在负载较轻的服务器上使用时才是正确的。那么，如果你的服务器的CPU或者IO使用率已经饱和了，尤其是偶尔出现峰值，系统在过载的情况下想要正常处理查询，那么强烈建议关注innodb_thread_concurrency。InnoDB有一种方法可以控制并行执行的线程数——我们称之为并发控制机制。大多数由innodb_thread_concurrency值控制。如果设置为0，并发控制关闭，因此InnoDB将立即处理所有传入请求（尽可能多）。当您有32个CPU内核且只有4个请求时，这很好。但是想象一下，你只有4个CPU内核和32个请求——如果同时处理32个请求，那你就是在自找麻烦。由于这32个请求只有4个CPU核，显然会比平时至少慢8倍（实际上是8倍多），而且这32个请求各自有自己的外锁和内锁，打桩的可能性很大向上请求。以下是更改此变量的方法。在mysql命令行提示符下执行：SETglobalinnodb_thread_concurrency=X;对于大多数工作负载和服务器，设置为8是一个好的开始，然后您可以根据达到此限制的服务器调整资源使用情况。未充分利用时逐渐增加。可以使用showengineinnodbstatus\G查看当前查询处理状态，发现类似如下行：22queriesinsideInnoDB,104queriesinqueue9。SKIP_NAME_RESOLVE这一项不得不提一下，因为还有很多人没有添加这一项。您应该添加skip_name_resolve以避免在连接时进行DNS解析。在大多数情况下，如果你改变这个你不会有太大的感觉，因为在大多数情况下DNS服务器解析会非常快。但是当DNS服务器出现故障时，它会在您的服务器上显示“未经身份验证的连接”，这就是所有请求突然开始变慢的原因。所以不要等到这种情况发生改变。现在添加此变量并避免基于主机名的授权。10.INNODB_IO_CAPACITY,INNODB_IO_CAPACITY_MAX*innodb_io_capacity：用于控制刷新脏数据时MySQL每秒执行的写IO量。*innodb_io_capacity_max：在压力下，控制MySQL在flushdirtydata时每秒执行的writeIO量首先，这与reads无关——SELECT查询执行操作。对于读操作，MySQL会尽量处理并返回结果。对于写操作，MySQL会在后台循环刷新，检查每个周期需要刷新多少数据，不会使用超过innodb_io_capacity指定的数量进行刷新操作。这还包括更改缓冲区合并（更改缓冲区是脏页刷新到磁盘之前辅助脏页存储的关键）。其次，我需要解释一下什么是“underpressure”，在MySQL中叫做“emergency”，就是当MySQL在后台flush的时候，需要刷新一些数据，才能让新的写操作进来。然后，MySQL将使用innodb_io_capacity_max。那么，为什么要设置innodb_io_capacity和innodb_io_capacity_max呢？最好的方法是测量存储设置的随机写入吞吐量，然后将innodb_io_capacity_max设置为设备可以达到的最高IOPS。innodb_io_capacity设置为它的50-75%，特别是如果你的系统主要是写操作。通常您可以预测系统的IOPS是多少。比如8个15k硬盘组成的RAID10，每秒可以进行1000次左右的随机写操作，那么可以设置innodb_io_capacity=600，innodb_io_capacity_max=1000。许多便宜的企业级SSD可以做4,000-10,000IOPS等。如果这个值设置不当，问题不大。但是，请注意默认值200和400会限制您的写入吞吐量，因此您可能偶尔会捕获正在进行的刷新。如果出现这种情况，可能是你的硬盘写IO吞吐量已经到了，或者这个值设置得太小，限制了吞吐量。11.INNODB_STATS_ON_METADATA如果你运行的是MySQL5.6或5.7，你不需要改变innodb_stats_on_metadata的默认值，因为它已经被正确设置了。但是，在MySQL5.5或5.1中，强烈建议关闭此变量——如果打开，showtablestatus之类的命令将立即查询INFORMATION_SCHEMA而不是等待几秒钟，这将使用额外的IO操作。从5.1.32版本开始，这是一个动态变量，这意味着您无需重新启动MySQL服务器即可将其关闭。12、INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN&INNODB_BUFFER_POOL_LOAD_AT_STARTUPinnodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup这两个变量与性能无关，但如果偶尔重启mysql服务器（如有效配置），则有关系。当两者都启用时，当MySQL停止时，MySQL缓冲池的内容（更具体地说，缓存页面）将存储到一个文件中。下次启动MySQL时，它会在后台启动一个线程来加载缓冲池的内容，以提高3-5倍的预热速度。两件事：***，它实际上并没有在关闭时将缓冲池内容复制到文件，只是表空间ID和页面ID-足够的信息来定位磁盘上的页面。然后它可以通过大量顺序读取而不是数千次小的随机读取来非常快速地加载这些页面。第二，内容是在启动时在后台加载的，因为MySQL不需要等到缓冲池内容加载完毕才接受请求（所以好像没什么作用）。从MySQL5.7.7开始，默认情况下只有25%的缓冲池页面在mysql关闭时存储到文件中，但您可以控制这个值-使用innodb_buffer_pool_dump_pct，推荐75-100。这个特性从MySQL5.6开始才被支持。13.INNODB_ADAPTIVE_HASH_INDEX_PARTS如果你正在运行一个带有大量SELECT查询的MySQL服务器（并且已经尽可能优化了它），那么自适应哈希索引将是你的下一个瓶颈。自适应哈希索引是由InnoDB内部维护的动态索引，可以提高最常用查询模式的性能。这个特性可以通过重启服务器来关闭，但是在所有版本的mysql中默认是开启的。这种技术非常复杂，在大多数情况下它会加速大多数类型的查询。但是，当您有太多查询进入数据库时??，在某个时刻它会花费太多时间等待AHI锁和闩锁。如果你的是MySQL5.7，就没有这个问题——innodb_adaptive_hash_index_parts默认设置为8，所以自适应哈希索引被切割成8个分区，因为没有全局互斥量。但是在mysql5.7之前的版本中，是无法控制AHI分区数量的。也就是说，有一个全局的mutex来保护AHI，这可能会导致你的selectquery频繁撞墙。因此，如果您运行的是5.1或5.6，并且有很多选择查询，最简单的解决方案是切换到相同版本的PerconaServer以激活AHI分区。14.QUERY_CACHE_TYPE如果人们认为查询缓存工作得很好，他们绝对应该使用它。好吧，有时它会起作用。但这只有在低负载下才有用，尤其是在主要读取、少量写入或无写入的低负载下。如果是这种情况，只需设置query_cache_type=ON和query_cache_size=256M。但切记不要将256M设置成更高的值，否则会在查询缓存失效时造成严重的服务器停顿。如果你的MySQL服务器负载较高，建议设置query_cache_size=0和query_cache_type=OFF，重启服务器生效。这样Mysql将停止对所有查询使用查询缓存互斥体。15.TABLE_OPEN_CACHE_INSTANCES从MySQL5.6.6开始，表缓存可以分成多个分区。表缓存用于存储当前打开的表的列表，互斥锁在每个表打开或关闭时被锁定——即使它是一个隐式临时表。使用多个分区肯定会减少潜在的争用。从MySQL5.7.8开始，table_open_cache_instances=16是默认配置。
