WEB·攻城志

本文就常见MySQL运行参数做详细列举与说明，并对各参数的设置条件做尽可能详尽的解释，由于MySQL配置参数比较多，本文尽可能全面介绍各参数，如有遗漏将陆续更新，欢迎大家点评投稿.

鉴于MySQL配置参数优化与数据库运行环境有密切关联，本文主要对MySQL参数做解释翻译整理，部分设置参数值来源整理自其他博客，考虑到尊重原创，本人尽量标注引用来源，如有遗漏，请及时联系我更正。

1. Query Cache
   • query_cache_type：控制 Query Cache 功能的开关，可以设置为0(OFF),1(ON)和2(DEMAND)三种：
     

     • 0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache
     • 1(ON)：开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用Query Cache
     • 2(DEMAND)：开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cache
       query_cache_type > 0 时(变量have_query_cache=YES)，如果query_cache_size=0(默认值为0)，Query也不可用

   • query_cache_size：设置 Query Cache 所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到1024的倍数。query_cache_size最小值不能小于40KB，否则系统将会产生一个WARNING:Query cache failed to set size 39936;new query cache size is 0.
   • query_cache_limit：允许 Cache 的单条 Query 结果集的最大容量，默认是1MB，超过此参数设置的 Query 结果集将不会被 Cache.
   • query_cache_min_res_unit：设置 Query Cache 中每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小.
   • query_cache_wlock_invalidate：控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache，如果设置为 1(TRUE)，则在写锁定的同时将失效该表相关的所有 Query Cache，如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的Query Cache。
     

     Query cache 作用于整个 MySQL Instance，主要用来缓存 MySQL 中的 ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当我们打开了 Query Cache 功能，MySQL在接受到一条select语句的请求后，如果该语句满足Query Cache的要求（未显式说明不允许使用Query Cache，或者已经显式申明需要使用Query Cache），MySQL 会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash，然后到Query Cache 中直接查找是否已经缓存。也就是说，如果已经在缓存中，该select请求就会直接将数据返回，从而省略了后面所有的步骤（如 SQL语句的解析，优化器优化以及向存储引擎请求数据等），极大的提高性能。

     当然，Query Cache 也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache 中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache 可能会得不偿失。

     Query Cache的使用需要多个参数配合，其中最为关键的是 query_cache_size 和 query_cache_type ，前者设置用于缓存 ResultSet 的内存大小，后者设置在何场景下使用 Query Cache。在以往的经验来看，如果不是用来缓存基本不变的数据的MySQL数据库，query_cache_size 一般 256MB 是一个比较合适的大小。当然，这可以通过计算Query Cache的命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）来进行调整。

     Query Cache 维护

     MySQL是按块进行内存申请分配的，运行一段时间后系统存在大量内存碎片，可以通过“FLUSH QUERY CACHE”语句进行内存碎片整理，另外2个可以管理Query Cache的分别是：RESET QUERY CACHE、FLUSH TABLES，它们将清空Query Cache内容。

2. Key Cache/Key Buffer 参考
   • key_buffer_size：用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小
   • key_cache_block_size：设置key cache 块大小
   • key_cache_division_limit：设置warm sub-clain的比例
   • key_cache_age_threshold：设置cache从warm sub-clain到hot sub-clain的“阀值”
     

     Key Buffer 可能是大家最为熟悉的一个 MySQL 缓存参数了，尤其是在 MySQL 没有更换默认存储引擎的时候，很多朋友可能会发现，默认的 MySQL 配置文件中设置最大的一个内存参数就是这个参数了。key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。

     合理的值取决于索引大小、数据量以及负载 — 记住，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer 都被利用了 — .MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。

     如果设置key_cache_division_limit,MySQL将采用Midpoint Insertion Strategy将key cache分成warm sub-clain和hot sub-clain两部分，cache block 将根据key_cache_age_threshold在两个sub-clain中进行转换。

     我们可以为几个表单独设置Key Cache空间，并预加载表索引到指定Key Cache

     mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;   #创建Key Cache
     mysql> SET GLOBAL keycache1.key_buffer_size=0;          #销毁Key Cache
     mysql> CACHE INDEX t1, t2, t3 IN hot_cache;             #设置指定表到指定Key Cache
     mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;      #预加载索引到Key Cache

     此外，当我们在使用MyISAM 存储的时候有一个及其重要的点需要注意，由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中，而不会缓存表数据库块。所以，我们的 SQL 一定要尽可能让过滤条件都在索引中，以便让缓存帮助我们提高查询效率。

3. InnoDB Buffer Pool
   • innodb_buffer_pool_size：类似于 MyISAM 存储引擎的 key_buffer_size 参数，除也存储索引外，还存储数据.
   • innodb_additional_mem_pool_size：设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小.
   • innodb_log_buffer_size：InnoDB 存储引擎的事务日志所使用的缓冲区,在写事务日志的时候，为了提高性能，先将信息写入 Innofb Log Buffer 中,再将日志同步到磁盘.
   • innodb_flush_log_trx_commit ：设置事务提交到log file及文件系统到磁盘的同步操作
     

     innodb_flush_log_trx_commit 参数对 InnoDB Log 的写入性能有非常关键的影响。该参数可以设置为0，1，2，解释如下：

     0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作；

     1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步；

     2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。

     此外，MySQL文档中还提到，这几种设置中的每秒同步一次的机制，可能并不会完全确保非常准确的每秒就一定会发生同步，还取决于进程调度的问题。实际上，InnoDB 能否真正满足此参数所设置值代表的意义正常 Recovery 还是受到了不同 OS 下文件系统以及磁盘本身的限制，可能有些时候在并没有真正完成磁盘同步的情况下也会告诉 mysqld 已经完成了磁盘同步。

   • innodb_max_dirty_pages_pct：用来控制在 InnoDB Buffer Pool 中可以不用写入数据文件中的Dirty Page 的比例（已经被修但还没有从内存中写入到数据文件的脏数据）。
     

     InnoDB Buffer Pool与MyISAM的Key Cache类似，所不同的是InnoDB Buffer Pool不仅保存表索引，还保存表数据 。

     如果不使用InnoDB存储引擎，可以不用调整参数innodb_buffer_pool_size，如果需要使用，在内存允许的情况下，尽可能将所有的InnoDB数据文件存放如内存中，同样也是“越大越好”。

     innodb_max_dirty_pages_pct，根据以往的经验，重启恢复的数据如果要超过1GB的话，启动速度会比较慢，几乎难以接受，所以建议不大于 1GB/innodb_buffer_pool_size(GB)*100 这个值。当然，如果你能够忍受启动时间比较长，而且希望尽量减少内存至磁盘的flush，可以将这个值调整到90，但不建议超过90

4. table_cache/table_open_cache：表缓存区主要用来缓存表文件的文件句柄信息，MySQL所有线程打开表的数量，包括临时表以及mysqld需要的一些额外的文件描述符，相关参数还有max_connections.
   

   当用户执行SQL是，每个连接进来，都会至少打开一个表缓存。因此， table_cache 的大小应与 max_connections 的设置有关。例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是应用可以执行的查询的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符。

5. max_connections：MySQL最大同时连接数，这个值如果过低会出现“Too many connections”错误.
6. thread_cache_size：线程缓存数量，可通过查看状态变量”connections”、”threads_created”进行调整.
   

   连接线程缓存是 MySQL 为了提高创建连接线程的效率，将部分空闲的连接线程保持在一个缓存区以备新进连接请求的时候使用，这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。当我们通过 thread_cache_size 设置了连接线程缓存池可以缓存的连接线程的大小之后，可以通过(Connections – Threads_created) / Connections * 100% 计算出连接线程缓存的命中率。注意，这里设置的是可以缓存的连接线程的数目，而不是内存空间的大小。

7. sort_buffer_size：MySQL为每个需要进行排序操作的线程分配的缓冲区，它可以影响Order By和Group By操作.
   

   增大sort_buffer_size 会减少Sort_merge_passes和创建临时文件的次数，但盲目增加并不一定能提高速度。

8. open_files_limit：文件打开数
   当open_files大于open_files_limit值时，mysql数据库就会发生卡住的现象，导致web服务器打开不响应的页面。

   查看open_files命令
   show global status like ‘open_files’;
   查看open_files_limit命令
   show variables like ‘open_files_limit’;
   理想值：理想值：open_files / open_files_limit *100% ≤ 75%

9. continue….