MySql数据库3【优化3】缓存设置的优化

1、表缓存

相关参数： table_open_cache

　　指定表缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值，如果发现open_tables等于table_cache，并且opened_tables在不断增长，那么就需要增加table_open_cache的值了。注意，不能盲目地把这个参数设置得很大，如果设置太大，会引起文件描述符不足，造成性能不稳定或者数据库连接失败。建议为512

　　table_cache = 512指定表高速缓存的大小, 如果opened_tables太大,应该把table_cache变大。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现 open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables‘获得)。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

2、查询缓存

相关参数： query_cache_size / query_cache_type

　　这个参数也是一个重要的优化参数。QC（注：查询缓存简称） 主要用来缓存 MySQL 中的 结果集，也就是一条SQL语句执行的结果集，所以仅仅只能针对select 语句。在MySQL服务器上进行查询，可以启用高速查询缓存。让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一。当同一个查询被执行多次时，如果结果是从缓存中提取，那是相当快的。

　　但随着发展，这个参数也爆露出来一些问题。机器的内存越来越大，人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加大后也引发了一系列问题。我们首先分析一下 query_cache_size的工作原理：一个SELECT查询在DB中工作后，DB会把该语句缓存下来，当同样的一个SQL再次来到DB里调用时，DB在该表没发生变化的情况下把结果从缓存中返回给Client。这里有一个关建点，就是DB在利用Query_cache工作时，要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时，Query_cache里的数据又怎么处理呢？首先要把Query_cache和该表相关的语句全部置为失效，然后在写入更新。那么如果Query_cache非常大，该表的查询结构又比较多，查询语句失效也慢，一个更新或是Insert就会很慢，这样看到的就是Update或是Insert怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建系把该功能禁掉。
    QC 的使用需要多个参数配合，其中最为关键的是 query_cache_size 和 query_cache_type ，前者设置缓存记录集的内存大小，后者设置在何场景下使用QC 。在以往的经验来看，中等规模的网站，query_cache_size 设置 256MB 足够了。当然，还可以通过计算QC的命中率来进行调整。

    Qcache_hits / (Qcache_hits + Qcache_inserts)  * 100%

　　query_cache_type有三种选择：0（OFF，不使用QC），1（ON，默认使用QC ），2（DEMAND，默认不使用QC）。为什么加上“默认”？MySQL还支持动态使用缓存的SQL语法，如下：

　　# 强制使用缓存    SELECT  SQL_CACHE  id  FROM  table

　　# 强制不使用缓存  SELECT  SQL_NO_CACHE  id  FROM  table

在有些处理任务中，我们实际上是可以阻止查询缓存工作的。

 // query cache does NOT work
        $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
     // query cache works!
        $today = date("Y-m-d");
       $r = mysql_query("SELECT username FROM user WHERE signup_date >= ‘$today‘");

3、索引缓存

相关参数：key_buffer_size

　　这个是对MyISAM表性能影响最大的一个参数，用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果有足够的内存，这个缓存区域大小可以设为所有的 MyISAM表的索引大小的总和，即 data 目录下所有*.MYI文件大小的总和。

　　注意，由于 MyISAM 引擎只会缓存索引块到内存中，而不会缓存表数据库块。所以，查询SQL语句一定要尽可能让过滤条件都在索引中，以便使用到索引缓存来提高查询效率。

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

　　如果Key_reads太大,则应该把key_buffer_size变大。增加它可得到更好处理的索引(对所有读和多重写)，如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和 Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%‘获得)。注意：该参数值设置的过大反而导致是服务器整体效率降低。

4、插入缓存

相关参数：bulk_insert_buffer_size

　　用于使用 MyISAM引擎，用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候，会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件，默认8M，建议不要超过32M

insert … select …
insert … values (…),(…),(…),…
load data infile… into… /* 非空表 */

 5、日志缓存

相关参数：binlog_cache_size

　　用于在打开了二进制日志（binlog）记录功能的环境中，是 MySQL 用来提高 binlog 的记录效率而设计的一个在短时间内缓存binlog 数据的内存缓存。

　　如果数据库中没有大事务，写入不是特别频繁，2MB～4MB是一个合适的选择。但是如果数据库大事务较多，写入比较频繁，可适当加大。使用的时候，还可以通过　　binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件来缓存了。

6、其他缓存参数设置

　　1）、key_buffer  改变索引缓冲区长度

一般，该变量控制缓冲区的长度在处理索引表（读/写操作）时使用。MySQL使用手册指出该变量可以不断增加以确保索引表的最佳性能，并推荐使用与系统内存25％的大小作为该变量的值。这是MySQL十分重要的配置变量之一，如果你对优化和提高系统性能有兴趣，可以从改变 key_buffer_size变量的值开始。 

　　2）、long_query_time 对缓长查询设定一个时间限制

MySQL带有“慢查询日志”，它会自动地记录所有的在一个特定的时间范围内尚未结束的查询。这个日志对于跟踪那些低效率或者行为不端的查询以及寻找优化对象都非常有用。long_query_time变量控制这一最大时间限定，以秒为单位。 

3）、sort_buffer_size   属重点优化参数

sort_buffer_size = 4M 查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 4 = 400MB。所以，对于内存在4GB左右的服务器推荐设置为4-8M。 

4）、read_buffer_size 读查询操作所能使用的缓冲区大小

read_buffer_size = 4M。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。 

5）、join_buffer_size  联合查询操作所能使用的缓冲区大小

　　join_buffer_size = 8M，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。 

6）、thread_cache_size

thread_cache_size = 64服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量，如果Threads_created太大,就要增加thread_cache_size的值。当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用 

7）、tmp_table_size

如果Created_tmp_disk_tables太大, 就要增加tmp_table_size的值,用基于内存的临时表代替基于磁盘的。tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小，MySQL产生一个 The table tbl_name is full 形式的错误，如果你做很多高级 GROUP BY 查询，可以增加 tmp_table_size 值。 

8）、read_rnd_buffer_size

MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。