MySQL07-查询缓存

查询缓存

总的select查询数等于com_select(没命中) + qcache_hits(命中) + 解析错误的

其中的com_select等于qcache_inserts(失效) + qcache_not_cache + 权限检查错误的。

也就是说qcache_inserts这个计数不是表示没被缓存而进行的读，而是缓存失效而进行的读，没被缓存和缓存失效是两个概念，分别计数，但都会引起com_select。

查询缓存相关命令

FLUSH QUERY CACHE
整理缓存碎片，将所有查询缓存重新排列，并将所有空闲空间都聚集在查询缓存的一块区域上。
该命令会访问所用缓存，在此期间无法访问查询缓存

RESET QUERY CACHE
清空查询缓存

查看mysql服务器缓存相关配置参数

show status like 'Qcache%';

Qcache_free_memory
Query Cache中目前剩余的内存大小，通过这个参数可以较为准确的观察到当前系统的Query Cache是否充足，是否需要增加还是过剩了

Qcache_lowmem_prunes
多少条query语句因为内存不足而从Query Cache中清除掉，通过Qcache_lowmem_prunes和Qcache_free_memory相互结合，能更清楚的了解到系统中的Query Cache是否真的足够，是否非常频繁的出现因为内存不足而存在Query被清除的现象，这个数字要放在一个较长的时间段看才有意义，如果这个数字在不断增长，表示可能存在较严重的碎片化现象，或者内存很少了

Qcache_total_blocks
当前Query Cache中block的数量

Qcache_free_blocks
缓存中相邻内存块的个数，如果该值显示较大，说明Query Cache中的内存碎片较多了

查询缓存碎片率
Qcache_free_memory /Qcache_total_blocks * 100% ，经验值判断，如果查询碎片率超过20%，可以使用 flush query cache来整理缓存碎片

mysql中Block的默认大小为4KB，这个值可以手动调整，设置的较大的话，对于大数据的查询有好处，但是如果你实际查询数据时都是小数据，就容易造成内存碎片

Qcache_hits
表示有多少次命中缓存，我们可以通过这个值来验证我们查询的缓存效果，数字越大效果越理想

Qcache_inserts
表示多少次未命中而插入，就是说新来的SQL请求在缓存中没有找到，不得不执行查询处理，执行查询处理之后将结果insert到缓存中，这样的情况次数越大，表示查询缓存应用的数量越少，效果也就越不理想

Qcache_queries_in_cache
当前的Query Cache 中Query数量，即当前的缓存中使用到的缓存查询数量

Qcache_not_cached
未进入查询缓存的select数量

query_cache_size
查询缓存大小，最小是1024byte，所以如果你设定的一个不是1024的倍数的值，这个值将会被四舍五入到最接近或等于1024的倍数)

query_cache_limit
超出此大小的查询将不被缓存，限定了单个查询缓存的最大内存数量

设置Query Cache Type参数

query_cache_type:缓存类型，决定缓存什么样的查询，这个值不能随便设置，而必须为数字，可选择的数值如下：
0：OFF，相当于禁用（5.7默认禁用了查询缓存，需要在my.cnf中进行设置）
1：ON，缓存所有结果，除非你的select语句显示使用了SQL_NO_CACHE禁用了查询缓存
2：DEMAND，只缓存select语句中通过SQL_CACHE指定需要缓存的查询

设置Query Block参数

query_cache_min_res_unit
缓存块的最小单位，最小值是4KB，设置的大点对大数据查询有好处，但是小数据多的话容易产生内存碎片造成资源浪费

单个查询占用缓存的平均值
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

查询缓存利用率
(query_cache_size - Qcache_free_memory）/ query_cache_size * 100%
查询缓存利用率在25%以下说明query_cache_size 设置的过大
查询缓存利用率在80%以上，而且 Qcache_lowmem_prunes 》 50说明query_cache_size 设置的有点小，要不就是碎片太多

查询缓存命中率
Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%

查询排序缓存区设置

sort_buffer_size
每个需要排序的线程分配的一个缓存区域，通过设置该参数可以加速 order by 或者group by的操作

sort_buffer_size是一个connection级别的参数，在每个connection第一次需要使用这个buffer的时候，一次性分配指定数量大小的内存

sort_buffer_size不是越大越好，由于是connection级别的参数,过大的设置在遇到高并发场景下可能会很快耗尽系统资源，例如1000个请求的并发的消耗大小为：1000 * sort_buffer_size(2MB) = 2G，这个内存占用就比较吓人了

InnoDB缓冲池（Buffer Pool）

innodb_max_dirty_pages_pct 最大赃页百分比
可以监控innodb_buffer_pool_pages_dirty状态变量或者使用innnotop来监控，SHOW INNODB STATUS来观察赃页的刷新量
当数据库有一个很大的缓冲池时，重启服务器后可能会花费很长时间来预热缓冲池（几小时甚至几天）可以利用Perconna Server功能来重新载入缓冲池的页。缩短预热时间至几分钟，如果不行可以尝试在重启后立即进行全表扫描或者索引扫描，把索引载入缓冲池，可以使用init_file来实现

MyISAM键缓存（Key Caches）
也叫键缓冲，默认只有一个，可以设置多个
MyISAM自身只缓存索引，不缓存数据
最重要的配置项是key_buffer_size，MySQL5.0上限4G，5.1允许更大
计算索引存储占用空间
seleset sum(index_length) from information_schema,tables where engine='myisam';
或者
du -sch find /path/to/mysql/data/directory -name "*.MYI"

线程缓存
快速响应连接请求
连接关闭后，缓存空间充足时回收，不足时销毁
thread_cache_size 可以保存在线程缓存中的线程数
Threads_created 已创建的缓存线程数
观察Threads_connected变量的值来调整thread_cache_size的大小

innodb表空间

存放表空间
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = data1:1G;data2:1G;data3:1G

或者，存放在不同磁盘
innodb_data_file_path = /disk1/data1:1G;/disk2/data2:2G

自动扩展与限制上限
/disk1/data1:1G:autoextend:max:2G

innodb并发配置

一次可以进入内核的线程数量，0表示不限制
innodb_thread_concurrency

并发值=CPU数量*磁盘数量*2     # 参考
innodb_thread_sleep_delay    线程休眠时间，然后再重试
innodb_concurrency_tickets   票据数量
innodb_commit_concurrency    有多少线程可以在同一时间提交

myisam并发配置