MySQL 设置变量的副作用

动态设置变量可能导致意外的副作用，例如从缓冲中刷新脏块。务必小心那些可以在线更改的设置，因为它们可能导致数据库做大量的工作。

有时可以通过名称推断一个变量的作用。例如，max_heap_table_size的作用就行听起来那样：它指定隐式内存临时表最大允许的大小。然而，命名的约定并不完全一样，所以不能总是通过名称来猜测一个变量有什么效果。

让我们来看一些常用的变量和动态修改它们的效果：

key_buffer_size

  设置这个变量可以一次性为键缓冲区(也叫键缓存)分配所有指定的空间。然而，操作系统不会真的立刻分配内存，而是到使用时才真正分配。例如设置键缓冲的大小为1GB，并不意味着服务器立刻分配1GB的内存。

  MySQL允许创建多个键缓存。如果把非默认键缓存的这个变量设置为0，MySQL将丢弃存在该键缓存中的索引，转而使用默认键缓存，并且当不再有任何引用时会删除该键缓存。为一个不存在的键缓存设置这个变量，将会创建新的键缓存。对一个已经存在的键缓存设置非零值，会导致刷新该键缓存的内容。这会阻塞所有尝试访问该键缓存的操作，知道刷新操作完成。

table_cache_size

  设置这个变量不会立即生效---会延迟到下次有线程打开表才有效果。当有线程打开表时，MySQL会检查这个变量的值。如果大于缓存中的表的数量，线程可以把最新打开的表放入缓存；如果值比缓存中的表小，MySQL将从缓存中删除不常使用的表。

thread_cache_size

  设置这个变量不会立即生效---将在下次连接被关闭时产生效果。当有连接被关闭时，MySQL检查缓存中是否还有空间来缓存线程。如果有空间，则缓存该线程以被下次连接重用；如果没有空间，它将销毁该线程而不再缓存。在这个场景中，缓存中的线程数，以及线程缓存使用的内存，并不会立刻减少；只有在新的连接删除缓存中的一个线程并使用后才会减少。（MySQL只在关闭连接时才在缓存中增加线程，只在创建新连接时才从缓存中删除线程。）

query_cache_size

  MySQL在启动的时候，一次性分配并且初始化这块内存。如果修改这个变量(即使设置为与当前一样的值)，MySQL会立刻删除所有缓存的查询，重新分配这片缓存到指定大小，并且重新初始化内存。这可能花费较长的时间，在完成初始化之前服务器都无法提供服务，因为MySQL是逐个清理缓存的查询，不是一次性全部删掉。

read_buffer_size

  MySQL只会在有查询需要时才会为该缓存分配内存，并且会一次性分配该参数指定大小的全部内存。

read_rnd_buffer_size

  MySQL只会在有查询需要使用时才会为该缓存分配内存，并且只会分配需要的内存大小而不是全部指定的大小。

sort_buffer_size

  MySQL只会在有查询需要做排序操作才会为该缓存分配内存。然后，一旦需要排序，MySQL就会立刻分配该参数指定的大小的全部内存，而不管该排序是否需要这么大的内存。

这里不是一个完整的参数列表，这里的目的只是简单的告诉大家，当修改一些常见的变量时，会有哪些期望的行为发生。

对于连接级别的设置，不要轻易地在全局级别增加它们的值，除非确认这样做是对的。有一些缓存会一次性分配指定大小的全部内存，而不管实际上是否需要这么大，所以一个很大的全局设置可能导致浪费大量的内存。更好的方法是，当查询需要时在连接级别单独调大这些值。

最常见的例子是sort_buffer_size，该参数控制排序操作的缓存大小，应该在配置文件里把它配置的小一些，然后在某些查询需要排序时，再在连接中把它调大。在分配内存后，MySQL会执行一些初始化的工作。

另外，即使是非常小的排序操作，排序缓存也会分配全部的大小的内存，所以如果把参数设置得超过平均排序需求太多，将会浪费很多内存，增加额外的内存分配开销。许多读者认为内存分配是一个很简单的操作，听到内存分配的代价可能会很吃惊。不需要深入很多技术细节就可以将清楚为什么内存分配也是昂贵的操作，内存分配包括了地址空间的分配，这相对来说是比较昂贵的。特别是在Linux上，内存分配根据大小使用多种开销不同的策略。

总的来说，设置很大的排序缓存代价可能非常高，所以除非确定必须要这么大，否则不要增加排序缓存的大小。

如果查询必须使用一个更大的排序缓存才能比较好地执行，可以在查询执行前增加sort_buffer_size的值，执行完成后恢复DEFAULT。

eg：

可以将类似的代码封在函数中以方便使用。其它可以设置的单个连接级别的变量有read_buffer_size，read_rnd_buffer_size，tmp_table_size，以及myisam_sort_buffer_size。

特别说明：本文章是来自<High Performance MySQL>的一章节。

个人感悟：理解可动态更改的变量产生的影响是蛮重要的，一不小心，可能导致负载飞一般的暴涨，CPU刷刷的飙升，甚至宕机。

糗事：本人曾在线上数据库动态修改query_cache_szie的值，本来server的负载都40-50的样子啦，当时也不没考虑那么多，就直接改了，负载瞬间飙升到了200多，那个汗啊，，，

好的东西是拿出来分享的，那样它就会更美！！！