MySQL参数优化

show variables like '%max_connections%';

show global status like '%onnection%';

set global max_connections = 200;

innodb buffer pool缓存着InnoDB表，索引，及其它辅助缓冲器中的数据。使用LRU算法(最近最少使用）淘汰过期缓存数据。

推荐：系统内存的50%-70%,必须是innodb_buffer_pool_instances*innodb_buffer_pool_chunk_size的整数倍(默认8*128M）

主要用于将innodb buffer pool进行划分，通过划分innodb buffer pool为多个实例，可以提高并发能力，并且减少了不同线程读写造成的缓冲页。
每一页从其中一个buffer pool中使用hash函数随机的读取和写入。每个buffer pool管理和维护各自的信息，包括free lists、flush lists、LRUs等

默认：8 or 1(innodb_buffer_pool_size<1G)

推荐：默认值或者逻辑CPU数量

要用于存放MySQL内部的数据结构和Innodb的数据字典，所以大小主要与表的数量有关，表越多值越大。
这个值是可变的，如果不够用的话，MySQL会向操作系统申请的。

redolog的缓冲区，为了提高性能，MySQL每次写日志都将日志先写到一个内存Buffer中，
然后将Buffer按照innodb_flush_log_at_trx_commit的配置刷到disk上。

0: 最佳性能：每隔一秒，才将Log Buffer中的数据批量write入OS cache，同时MySQL主动fsync。
这种策略，如果数据库奔溃，有一秒的数据丢失。
1：强一致：每次事务提交，都将Log Buffer中的数据write入OS cache，同时MySQL主动fsync。
这种策略，是InnoDB的默认配置，为的是保证事务ACID特性。
2：折中：每次事务提交，都将Log Buffer中的数据write入OS cache；每隔一秒，MySQL主动将OS cache中的数据批量fsync。
这种策略，如果操作系统奔溃，最多有一秒的数据丢失。

推荐值：高并发场景下，使用折中配置（=2），因为：
配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快；
配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据。

0 ：当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，
而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
1：每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新。
n：在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。

默认值：1，安全性最高，性能较低

sync_binlog=0，性能最好，风险最大，一旦系统Crash，在文件系统缓存的所有binlog信息都会丢失。

MyISAM表的key缓存，这个只对MyISAM存储引擎有效，对系统临时表(MyISAM引擎)也有效

MySQL对于查询的结果会进行缓存来节省解析SQL、执行SQL的花销，query_cache是按照SQL语句的Hash值进行缓存的，同时SQL语句涉及的表发生更新，该缓存就会失效，所以这个缓存对于特定的读多更新少的库比较有用，对于绝大多数更新较多的库可能不是很适用，比较受限于应用场景。

每个线程连续扫描时为扫描的每个表分配的缓存区的大小（字节）。如果进行多次连续扫描，可能还需要增加该值。
只有当查询需要的时候，才分配read_buffer_size指定的全部内存。

当以任意顺序读取行时，可以分配随机读取缓冲区，通过该缓冲区读取行，以避免磁盘寻找。read_rnd_buffer_size系统变量决定缓冲器大小。

每一个要做排序的请求，都会分到一个sort_buffer_size大的缓存，用于做order by和group by的排序，如果设置的缓存大小无法满足需要，MySQL会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级，所以sort_buffer_size对排序的性能影响很大。由于这部分缓存是即使不用这么大，也会全部分配的，所以对系统内存分配开销是比较大的。

MySQL为每个线程分配的堆栈大小，当线程堆栈太小时，这限制了服务器可以处理的SQL语句的复杂性。

每个连接的每次join都分配一个buffer。

类似于innodb_log_buffer_size缓存事务日志，binlog_cache_size缓存Binlog，不同的是这个是每个线程单独一个，主要对于大事务有较大性能提升。

用户内存临时表的最大值，如果临时表超过该值，MySQL就会把临时表转换为一个磁盘上mysiam表。如果用户需要做一些大表的groupby的操作，可能需要较大的该值，由于是与连接相关的，同样建议在会话层设置。

是否启用慢查询日志，1为启用，0为禁用

指定慢查询日志文件的路径和名字，可使用绝对路径指定

默认值是'主机名_slow.log'，位于datadir目录

SQL语句运行时间阈值，执行时间大于参数值(单位秒)的语句才会被记录下来

SQL语句检测的记录数少于设定值的语句不会被记录到慢查询日志，即使这个语句执行时间超过了long_query_time的阈值

将没有使用索引的语句记录到慢查询日志，1为启用，0为禁用

5.6版本设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间

5.6版本记录执行缓慢的DDL语句，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。

5.6版本记录从库上执行的慢查询语句

5.7版本新增时间戳所属时区参数

默认值：记录UTC时区的时间戳到慢查询日志

推荐值：system，应修改为记录系统时区

指定慢查询日志的输出方式，从5.5版本开始可以记录到日志文件(FILE，慢查询日志)和数据库表(TABLE，mysql.slow_log)中

推荐值：FILE,TABLE

MySQL限定的最大连接数。

推荐值：Max_used_connections / max_connections * 100% ≈ 85%

MySQL服务自启动之后响应的最大连接数。

mysql在关闭一个交互的连接之前所要等待的秒数。

mysql在关闭一个非交互（空闲）的连接之前所要等待的秒数。在 LAMP 应用程序中，连接数据库的时间通常就是 Web 服务器处理请求所花费的时间。有时候，如果负载过重，连接会挂起，并且会占用连接表空间。如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取

默认值：28800，单位秒，即8个小时

max_connect_errors 是一个安全的方法。如果一个主机在连接到服务器时有问题，并重试很多次后放弃，
那么这个主机就会被锁定，直到 FLUSH HOSTS 之后才能运行。

默认值：10，10次失败就导致锁定。

推荐值：100，重试100次，会给服务器足够的时间来从问题中恢复。
如果重试 100 次都无法建立连接，那么使用再高的值也不会有太多帮助，可能它根本就无法连接。

启用binlog功能，并指定路径和名字，可使用绝对路径指定

未指定绝对路径，默认位于datadir目录

指定二进制索引文件的路径和名字，可使用绝对路径指定

只记录指定数据库的二进制日志，建议不要配置

不记录指定的数据库的二进制日志，建议不要配置

binlog使用的内存最大的尺寸

默认值：18446744073709547520

推荐值：4294967296，即4G

表示binlog使用的内存大小

默认值：37268，即32K

推荐值：1M，如果系统事务多可以设置为2到4M

状态值，表示使用内存进行二进制日志缓存的事务数量

状态值，表示使用二进制日志缓存但超binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量

Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

0 ：当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，
而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
1：每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新。
n：在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。

默认值：1，安全性最高，性能较低

sync_binlog=0，性能最好，风险最大，一旦系统Crash，在文件系统缓存的所有binlog信息都会丢失。

可选值有statement（记录逻辑sql语句）、row（记录表的行变动情况）、mixed（混合模式）

binlog过期时间

默认是0天，也就是说不自动清理

推荐值：7天，设置之后不是立即清理，触发条件为：
binlog大小超过max_binlog_size、
手动执行flush logs、
重新启动时(MySQL将会new一个新文件用于记录binlog)

指定binlog文件之前

指定时间之前

MyISAM表缓冲区大小

默认值：536870912，即512MB

命中磁盘的请求个数

请求总数

计算索引未命中缓存的概率：key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%

推荐：key_cache_miss_rate在0.1%以下都很好（每1000个请求有一个直接读硬盘），如果key_cache_miss_rate在 0.01%以下的话，key_buffer_size分配的过多，可以适当减少，如果每 1,000 个请求中命中磁盘的数目超过 1 个，就应该考虑增大关键字缓冲区了。

未使用的缓存簇(blocks)数

曾经用到的最大的blocks数

推荐：Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

在磁盘上创建的临时表数

创建的临时文件的文件数

创建的临时表的总数

推荐：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

该值以下的heap临时表才能全部放入内存，超过的就会用到磁盘临时表

该值以下的临时表才能全部放入内存，超过的就会用到磁盘临时表

与表的缓存类似，对于线程来说也有一个缓存。 mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上，对线程进行缓存便于以后使用可以加快最初的连接。

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户，而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。

缓存中相邻内存块的个数，数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理。

缓存中的空闲内存。

每次查询在缓存中命中时就增大。

每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。

查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况） 

不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。

当前缓存的查询（和响应）的数量。

缓存中块的数量。

超过此大小的查询将不缓存。

缓存块的最小大小。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

推荐：如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存大小。

查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

推荐：查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；
查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

缓存类型，决定缓存什么样的查询，ON表示不缓存 select sql_no_cache 查询

当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，ON表示返回cache结果，OFF表示等写操作完成再读表获取结果。

Sort_merge_passes 包括两步。MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，等MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 Sort_merge_passes。实际上，MySQL会用另一个临时文件来存再次排序的结果，所以通常会看到 Sort_merge_passes增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 sort_buffer_size 会减少Sort_merge_passes 和 创建临时文件的次数，但盲目的增加sort_buffer_size 并不一定能提高速度

每一个要做排序的请求，都会分到一个sort_buffer_size大的缓存，用于做order by和group by的排序，如果设置的缓存大小无法满足需要，MySQL会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级，所以sort_buffer_size对排序的性能影响很大。由于这部分缓存是即使不用这么大，也会全部分配的，所以对系统内存分配开销是比较大的。

当前Mysql文件打开数

文件打开数上限

推荐：Open_files / open_files_limit * 100% <= 75％

需要等待的表锁数

立即释放表锁数

表扫描率 ＝ Handler_read_rnd_next / Com_select

推荐：如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB

表定义信息缓存，用来存放表定义信息。用来存放表定义信息。当我们的MySQL 中使用了较多的表的时候，此缓存无疑会提高对表定义信息的访问效率。
MySQL 提供了table_definition_cache 参数给我们设置可以缓存的表的数量。注意，这里设置的是可以缓存的表定义信息的数目，而不是内存空间的大小。

打开的表定义信息的数目。