mysql优化三：配置缓存提高查询效率

MySQL数据库缓存机制

缓存机制简单的说就是缓存sql文本及查询结果。
• 当前查询若恰好命中查询缓存，直接返回缓存中的结果。而不需要再去解析和执行sql。
• 若没命中则到数据库表中查询，将结果集保存到缓存并返回结果集。

参数配置

1.查看缓存各参数：

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache          | YES     | --查询缓存是否可用
| query_cache_limit           | 1048576 | --可缓存具体查询结果的最大值
| query_cache_min_res_unit     | 4096    | 
| query_cache_size           | 999424  | --查询缓存的大小
| query_cache_type           | ON      | --阻止或是支持查询缓存
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

query_cache_type各个值如下
• 为0或off关闭缓存
• 为1或on开启缓存，但是不保存使用sql_no_cache的select语句,如不缓存select sql_no_cache name from test_user where id=2
• 为2或demand开启有条件缓存，只缓存带sql_cache的select语句，如缓存select sql_cache name from test_user where id=4

设置缓存内存、开启查询缓存

• set global query_cache_size = 1000000; --设置缓存内存
• set session query_cache_type = ON; --开启查询缓存

查看缓存状态

执行语句SHOW STATUS LIKE 'qcache%'; 查看缓存区详情其中各个参数的意义如下：
• qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
• qcache_free_memory：缓存中的空闲内存。
• qcache_hits：每次查询在缓存中命中时就增大 。
• qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
• qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个 数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况) 。
• qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
• qcache_queries_in_cache：当前缓存的查询(和响应)的数量。
• qcache_total_blocks：缓存中块的数量。

测试缓存查询效果（时间差）

Select count(*) from t_tag;
Count(*)
110997

测试结果
• 执行语句对数据库中10万条数据做查询操作：SELECT COUNT(*) FROM t_tag;
• 第一次查询，缓存中没有对应结果集，服务器从数据表中查询，耗时为total：31ms。
• 第二次查询，直接从缓存中返回结果，耗时为total：0ms，时间明显减少。

注意事项

• 所以两个查询sql在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。
• 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。
• 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存。
如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。
可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存 。
• MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。因此建议查询很频繁修改很少时使用。
• 缓存碎片。随着缓存量的增多，查询缓存会产生碎片，这将降低缓存性能。状态变量Qcache_free_blocks描述了缓存中的空闲块，该值越大表示碎片越多。可以用FLUSH QUERY CACHE命令来整理碎片，而对于大缓存，该操作会长时间阻塞查询缓存。