mysql优化之参数调优

在MYSQL的优化过程中，我们至少有三种办法，可以达到优化的目的，效率从低到高依次是

1. 1. 替换有问题的硬件。
   2. 对MYSQL进程的设置进行调优。
   3. 对查询进行优化。

前面我们讨论过通过索引等技术对查询进行优化，本博文是通过对MYSQL进程的参数设置进行优化，在对参数进行设置的时候，我们主要考虑以下原则：

1. 1. 数据在内存中访问，比磁盘上访问的速度要快得多。
   2. 如果可以，在内存中尽可能长地保存数据存活时间，以减少磁盘活动量。
   3. 在保存内存数据时，索引的信息比保留数据行的内容更加重要些。

一，table_open_cache调节

为了让文件打开操作的次数能够达到最小化，服务器在打开数据表文件之后会尽量让它们继续保持在打开状态，也就是会把已经打开的文件的信息长时间尽可能多的保存在数据表缓存里，table_cache控制这个缓存的大小（在MYSQL5.1版本里，是table_open_cache系统变量)。可以查看Opened_tables状态变量，以了解数据表缓存的工作情况：

SHOW STATUS LIKE ‘Open%tables’  

这个命令会显示两个状态变量：Open_tables 和Opened_tables，分别表示当前打开的表的数量 和已经打开的表的数量。

在执行flush tables; 命令后会清空缓存的内容，也就是的当前打开的表缓存清空，不影响已经打开表数量的状态显示。在你清空之后，如果再执行表查询或者相关的表操作，这时会发现这两个状态值都被加上相应的值（些次操作打开表的数量）；

一般情况下，如果Open_tables的值和table_cache的值很接近，并且Opened_tables增长很快，这时说明我们可能需要把缓存设置更大。但也不能设置过大，因为使用缓存要占用系统的文件描述符数量，就相当于打开一个文件，这可能会导致系统其它的必要打开文件需求无法得到满足，从而导致系统不稳定。

对这个值的设置，应该要综合各个因素，包括：系统的连接数以及表的数目，Open_tables 和Opened_tables的值的比例(最好保持比例在80%以上)，然后是Open_tables和table_cache的比较等等。

最后，要说明的是，由于InnoDB存储引擎的数据放于表共享空间，经过测试，这个参数对该存储引擎表的性能影响不是很大。

二，key_buffer_size

MyISAM键缓冲区，用来保持MyISAM数据表有关索引的操作的索引块，大小由key_buffer_size控制。它决定索引处理的速度，尤其是索引读的速度。

一般我们设为16M,实际上稍微大一点的站点　这个数字是远远不够的，通过检查状态值Key_read_requests和Key_reads。

show global status like 'key_read%'；

Key_read_requests表示索引读取请求，Key_reads表示索引读取在内存中没有找到，然后从磁盘读取的数量。定义：

key_cache_miss_rate = Key_reads / Key_read_reuqests * 100% .

这个表示索引未命中缓存的比例，个人觉得这个值可以控制在0.1%以下，而在0.01%以上。

如果要设置其值，可以有配置文件里设置，也可以在运行时设置，set global key_buffer_size=64*1024*1024。

另外，MYSQL还提供两个状态值，以反映缓存的使用情况，Key_blocks_unused表示未使用的缓存簇数，Key_blocks_used表示曾经用到的最大的簇数，对于这两个值，比较理想的设置是

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

最后介绍一个比较实用的索引缓存技术：为数据表分配键缓存。

这是因为有时我们可能某一个表的查询操作比较多，而其它的表索引会与这个表竞争索引缓存空间。MYSQL为这个问题准备了一个解决方案：支持建立多个键缓存，可以为某个数据表分配一个键缓存并把该数据表的索引提前加载至该缓存。实际操作过程中，可以把所有的索引都加载到一个足够大的缓存中，这样的话，我们一方面可以避免来自同一个数据表的键发生竞争，也可以避免来自其他数据表的键竞争这个缓存里的空间。

假如需要为db_info数据库里的i_node数据表创建一个缓存，这个缓存的名字是name_cache，长度是1KB，如下所示：

1. 1. 创建一个新的键缓存区域，使他可以容纳所有的表索引（我这个表比较小）
      mysql> set global name_cache.key_buffer_size = 1024;
      
   2. 把数据表指定给这个键缓存
      mysql> cache index i_node in name_cache;
      +----------------+--------------------+----------+----------+
      | Table          | Op                 | Msg_type | Msg_text |
      +----------------+--------------------+----------+----------+
      | db_info.i_node | assign_to_keycache | status   | OK       | 
      +----------------+--------------------+----------+----------+
      
   3. 把数据表的索引全部加载至键缓存中
      mysql> load index into cache i_node;
      +----------------+--------------+----------+----------+
      | Table          | Op           | Msg_type | Msg_text |
      +----------------+--------------+----------+----------+
      | db_info.i_node | preload_keys | status   | OK       | 
      +----------------+--------------+----------+----------+
      

三，查询缓存

MYSQL服务器可以使用查询缓存来加快重复执行的SELECT语句的过程，查询缓存有以下特点：

1. 1. 一个给定的SELECT语句第一次执行时，服务器记录了它查询的文本和返回的结果。
   2. 服务器下一次看到这个查询时就不再执行它，而是直接从查询缓存中将查询结果取出并返回。
   3. 查询缓存以服务器接受的那些查询字符串的文本为基础。
   4. 如果某个查询命令返回的结果不确定，这个查询就不会被缓存。比如，查询结果包含当前的时间。
   5. 如果一个数据表被更新时，所有与之相关的缓存着的查询会全部失效，并删除所有之前缓存的关于这个表的缓存。

查询缓存是默认被支持的，如果不想支持，可以在configure时指定 --without-query-cache .

查询某个服务器是否支持查询缓存

show global variables like "have_query_cache";

查询缓存受3个系统变量的影响

1. 1. query_cache_type . 决定查询缓存的操作模式。 0：不缓存查询结果/不检索已经被缓存的结果。1：缓存查询，但不包括以SELECT SQL_NO_CACHE开头的查询。2：只缓存以SELECT SQL_CACHE开头的查询。
   2. query_cache_size 决定了为查询缓存分配的内存大小 ，以字节为单位。
   3. query_cache_limit 设置能够缓存的最大结果集的大小，比这个值大的查询结果不能被缓存。

与之相关的系统状态变量有

mysql> show global status like "qcache%";

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Qcache_free_blocks      | 0     |     

| Qcache_free_memory      | 0     |    

| Qcache_hits             | 0     |        

| Qcache_inserts          | 0     |     

| Qcache_lowmem_prunes    | 0     |       

| Qcache_not_cached       | 0     |    

| Qcache_queries_in_cache | 0     |      

| Qcache_total_blocks     | 0     | 

+-------------------------+-------+

对各个值的表示意思是:

Qcache_free_blocks :  缓存中空闲内存块的数目。

Qcache_free_memory ： 缓存中空闲内存的总数。

Qcache_hits  查询缓存命中的次数。

Qcache_inserts ： 加入到缓存中的数目。

Qcache_lowmem_prunes ： 因为缺少内存，而被从缓存中删除的数目。

Qcache_not_cached：没有被缓存的查询数目，由于一些设置或者非SELECT查询。

Qcache_queries_in_cache ：当前缓存中的条目。

Qcache_total_blocks：查询缓存中块的总数目。

根据这个结果我们可以统计

总的查询次数 = Qcache_inserts  + Qcache_hits  + Qcache_not_cached；

查询缓存使用变长的块，因而 Qcache_total_blocks 和 Qcache_free_blocks 可能显示查询缓存的碎片。在 

FLUSH QUERY CACHE 

之后，只有剩余一个单独的(大的)空闲块。