1 缓存优化之QueryCache

从 MySQL4开始，出现了QueryCache查询缓存，如果使用了QueryCache，当查询接收到一个和之前同样的查询，服务器将会从查询缓存种检索结果，而不是再次分析和执行上次的查询。这样就大大提高了性能，节省时间，非常有用。

打开查询缓存，是通过几个步骤来设置的，例如：虽然你设置Mysql允许查询缓存，但是如果你设置的查询缓存大小为了0，这和没有允许没什么区别。所以必须是几个步骤的设置才能真正打开查询缓存这个功能。

1.1 查询开启状态

一般，我们会把 query_cache_type 设置为 ON,默认情况下应该是ON(其实5.6默认是OFF)

1	mysql> select @@query_cache_type; # 查询开启状态

+——————–+
| @@query_cache_type |
+——————–+
| ON |
+——————–+
这样当我们执行 select id,name from tableName; 这样就会用到查询缓存。在 query_cache_type 打开的情况下，如果你不想使用缓存，需要指明select sql_no_cache id,name from tableName;当然也可以禁用查询缓存：mysql> set session uery_cache_type=off;
这里我们不讨论这个，我们演示常用的设置。

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

当然，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_type可以设置为0(OFF)，1(ON)或者2(DEMOND)，分别表示完全不使用query cache，除显式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache，只有显示要求才使用query cache(使用sql_cache)。

如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲. 如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；

根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大.

可以通过命令：show status like 'Qcache_%';查看目前系统Query catch使用大小

| Qcache_hits | 1892463 |

| Qcache_inserts | 35627

命中率98.17%=1892463/(1892463 +35627 )*100

1.2 系统变量 have_query_cache 设置查询缓存是否可用

1	mysql> show variables like 'have_query_cache';

+——————+——-+
| Variable_name | Value |
+——————+——-+
| have_query_cache | YES |
+——————+——-+
上面的显示，表示设置查询缓存是可用的。

1.3 系统变量 query_cache_size

表示查询缓存大小，也就是分配内存大小给查询缓存，如果你分配大小为0，
那么第一步和第二步起不到作用，还是没有任何效果。

1	mysql> select @@global.query_cache_size;

+—————————+
| @@global.query_cache_size |
+—————————+
| 16777216 |
+—————————+
上面是 mysql6.0设置默认的，之前的版本好像默认是0的，那么就要自己设置下。
设置set @@global.query_cache_size=1000000; 这里是设置1M左右，900多K。
再次查看下 select @@global.query_cache_size;
+—————————+
| @@global.query_cache_size |
+—————————+
| 999424 |
+—————————+
显示我们设置新的大小，表示设置成功。

1.4 query_cache_limit 控制缓存查询结果的最大值

例如：如果查询结果很大，也缓存？？？？这个明显是不可能的。
MySql 可以设置一个最大的缓存值，当你查询缓存数结果数据超过这个值就不会
进行缓存。缺省为1M，也就是超过了1M查询结果就不会缓存。

1	mysql> select @@global.query_cache_limit;

+—————————-+
| @@global.query_cache_limit |
+—————————-+
| 1048576 |
+—————————-+
这个是默认的数值，如果需要修改，就像设置缓存大小一样设置，使用set重新指定大小。
好了，通过4个步骤就可以打开了查询缓存，具体值的大小和查询的方式这个因不同的情况来指定了。

1.5 缓存合理性，优化

1.5.1 query_cache_size 优化

通过调节以下几个参数可以知道query_cache_size设置得是否合理

Qcache_inserts
Qcache_hits
Qcache_lowmem_prunes
Qcache_free_blocks

如果Qcache_lowmem_prunes 的值非常大，则表明经常出现缓存不够的情况，如果Qcache_hits的值非常大，则表明查询缓存冲使用非常频繁，如果该值较小反而影响效率，那么可以考虑不用查询缓存；

Qcache_free_blocks 值非常大，则表明缓存区中的碎片很多，可能需要需找合适的机会进行整理

Qcache_hits 表示多少次命中，通过这个参数我们可以查看到 Query Cache的基本效果；

Qcache_inserts 表示多少次未命中然后插入，通过Qcache_hits 和 Qcache_inserts 两个参数可以算出Query Cache的命中率

Query Cache命中率 = Qcache_hits / (Qcache_hits + Qcache_inserts)

Qcache_lowmem_prunes 表示多少条Query 因为内存不足而被清除出Query Cache，通过Qcache_lowmem_prunes和Qcache_free_memory相互结合，能够更清楚地了解到系统中Query Cache的内存大小是否真的足够，是否频繁出现因为内存不足而有Query被换出的情况

1.5.2 query_cache_min_res_unit 优化

开启了数据库缓存后用

1	show status like 'qcache%'; ## 查看缓存`query_cache_min_res_unit` 默认是4k

发现 Qcache_free_blocks 数目大说明可能有碎片。

Qcache_free_blocks: 表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。 减少碎片：合适的query_cache_min_res_unit可以减少碎片，这个参数最合适的大小和应用程序查询结果的平均大小直接相关.

可以通过内存实际消耗（ query_cache_size - Qcache_free_memory ）除以 Qcache_queries_in_cache 计算平均缓存大小。

其中 Qcache_free_memory 和 Qcache_queries_in_cache 在上面已将有了分别是119423544和 13205

query_cache_size 是自己设置的可以通过

1	SHOW VARIABLES LIKE '%query_cache%'; # 查看

（ query_cache_size - Qcache_free_memory ）除以 Qcache_queries_in_cache
就是 （134217728 - 119423544）/13205=1120.34
所以在设置的的时候 query_cache_min_res_unit可以设置成2k
修改my.cnf,配置如下：

query_cache_min_res_unit= 2k

然后在查看他的Qcache_free_blocks运行一段时间有没有减少

2 缓存优化之 Innodb

当使用innoDB存储引擎的时候，innodb_buffer_pool_size参数可能是影响性能的最为关键的一个参数了，用来设置用于缓存innoDB索引及数据块的内存区域大小，更像是Oracle数据库的db_cache_size。简单来说，当操作一个InnoDB表的时候，返回的所有数据或者查询过程中用到的任何一个索引块，都会在这个内存区域中区查询一遍。

和key_buffer_size 对于MyISAM引擎一样，innodb_buffer_pool_size设置了InnoDB存储引擎需求最大的一块内存区域的大小，直接关系到InnoDB存储引擎的性能，所有如果有足够的内存，尽可将该参数设置到足够大，将尽可能多的InnoDB的索引及数据都放入到该缓存区域中，直至全部。

可以通过(innodb_buffer_pool_read_requests - innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests * 100%计算缓存命中率，并根据命中率来调整innodb_buffer_pool_size参数大小进行优化。

3 缓存优化之table_cache

另外，table_cache是一个非常重要的MySQL性能参数，主要用于设置table高速缓存的**数量**，由于每个用户端链接都会至少访问一个表，因此该参数是与max_connections有关。当某一连接访问一个表时，MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开，则会直接访问缓存中的表以加快查询速度；

如果该表未被缓存，则会将当前的表添加进缓存并进行查询。在执行缓存操作前，table_cache参数用于限制缓存表的最大数目；如果当前已经缓存的表未达到table_cache数目，则会将新表添加进去，若已经达到此值，MySQL将根据缓存表的最后查询时间，查询率，等规则释放之前的缓存。