mysql.wpool.query_MySQL---结构优化之查询缓存

MySQL提供的缓存机制有时候在查询是起到很大的作用，减少了相当多的I/O，提高了系统的性能。MySQL在服务器级别提供了查询缓存(query

cache)，而在存储引擎级别，MyISAM和InnoDB分别引入了key cache和buffer pool。

一、query cache

MySQL查询缓存了select的完整结果集，当缓存命中的时候，服务器马上保存返回的结果，并跳过解析、优化和执行步骤。

1)命中的前提

可以命中的前提是与之前某一次的SQL语句完全相同，所谓完全相同SQL语句大小写完全一样，发起SQL的客户端必须使用同样的字符集和通信协议，SQL查询同一数据库下的同一个表(不同数据库可能有同名表)，不能包括想now()这样的不会缓存下来的函数，因为即使服务器在早些时候执行了类似的查询，服务器也不会有缓存的结果，MySQL一旦发现有组织缓存的元素存在，它里面就把查询标记为不可缓存，并且产生的记录不会被保存下来。

2)能够缓存的要求

任何一个包含不确定函数的查询不会被缓存，由不同的用户查询将会产生不同查询结果的查询也不会缓存，此外缓存不会缓存用户自定义函数、存储函数、用户自定义变量、临时表等等

3)查询缓存的开销

*读取查询在开始之前要检查缓存；

*如果查询是可以被缓存的，但是不在缓存中，那么在产生结果之后进行保存会带来一些额外的开销；

*写入数据的查询也会有额外的开销，因为它必须使缓存中相关的数据表失效；

4)相应参数

query_cache_type: 是否打开缓存

可选项

1) OFF: 关闭

2) ON: 总是打开

3) DEMAND:

只有明确写了SQL_CACHE的查询才会吸入缓存

query_cache_size:

缓存使用的总内存空间大小,单位是字节,这个值必须是1024的整数倍,否则MySQL实际分配可能跟这个数值不同(感觉这个应该跟文件系统的blcok大小有关)

query_cache_min_res_unit:

分配内存块时的最小单位大小

query_cache_limit: MySQL能够缓存的最大结果,如果超出,则增加

Qcache_not_cached的值,并删除查询结果

query_cache_wlock_invalidate:

如果某个数据表被锁住,是否仍然从缓存中返回数据,默认是OFF,表示仍然可以返回

GLOBAL STAUS 中 关于缓存的参数解释:

Qcache_free_blocks:

缓存池中空闲块的个数

Qcache_free_memory: 缓存中空闲内存量

Qcache_hits: 缓存命中次数

Qcache_inserts: 缓存写入次数

Qcache_lowmen_prunes:

因内存不足删除缓存次数

Qcache_not_cached:

查询未被缓存次数,例如查询结果超出缓存块大小,查询中包含可变函数等

Qcache_queries_in_cache:

当前缓存中缓存的SQL数量

Qcache_total_blocks:

缓存总block数

二、buffer

pool

InnoDB即缓存表又缓存索引也就是缓存.ibd文件。

采用LRU算法：

所有buffer块位于同一列表，其中后3/8为old，每当新读入一个数据块时，先从队尾移除同等块数然后插入到old子列的头部，如再次访问该块则将其移至new子列的头部

Innodb_buffer_pool_size: buffer

pool大小

Innodb_buffer_pool_instances:子buffer pool数量，buffer

pool至少为1G时才能生效

Innodb_old_blocks_pct:范围5 – 95，默认为37即3/8，指定old子列的比重

Innodb_old_blocks_time:以ms为单位，新插入old子列的buffer块必须等待指定时间后才能移入new列，适用于one-time scan频繁的操作，以避免经常访问的数据块被剔出buffer pool

可通过状态变量获知当前buffer

pool的运行信息

Innodb_buffer_pool_pages_total：缓存池总页数

Innodb_buffer_pool_bytes_data：当前buffer pool缓存的数据大小，包括脏数据

Innodb_buffer_pool_pages_data：缓存数据的页数量

Innodb_buffer_pool_bytes_dirty：缓存的脏数据大小

Innodb_buffer_pool_pages_diry：缓存脏数据页数量

Innodb_buffer_pool_pages_flush：刷新页请求数量

Innodb_buffer_pool_pages_free：空闲页数量

Innodb_buffer_pool_pages_latched：缓存中被latch的页数量，这些页此刻正在被读或写；然而计算此变量比较消耗资源，只有在UNIV_DEBUG被定义了才可用

Innodb_buffer_pool_pages_misc：用于维护诸如行级锁或自适应hash索引的内存页=总页数-空闲页-使用的页数量

Innodb_buffer_pool_read_ahead：预读入缓存的页数量

Innodb_buffer_pool_read_ahead_evicted：预读入但是1次都没用就被剔出缓存的页

Innodb_buffer_pool_read_requests：InnoDB的逻辑读请求次数

Innodb_buffer_pool_reads：直接从磁盘读取数据的逻辑读次数

Innodb_buffer_pool_wait_free：缓存中没有空闲页满足当前请求，必须等待部分页回收或刷新，记录等待次数

Innodb_buffer_pool_write_requests：向缓存的写数量

可使用innodb standard

monitor监控buffer

pool的使用情况，主要有如下指标：

Old database pages:

old子列中的页数

Pages made young, not

young:从old子列移到new子列的页数，old子列中没有被再次访问的页数

Youngs/s non-youngs/s:访问old并导致其移到new列的次数

三、key

cache

5.5仅支持一个结构化变量，即key cache，其包含4个部件。MyISAM只缓存索引,就是.MYI

Key_buffer_size

Key_cache_block_size：单个块大小，默认1k

Key_cache_division_limit：warm子列的百分比(默认100)，key cache

buffer列表的分隔点，用于分隔host和warm子列表

Key_cache_age_threshold：页在hot子列中的生命周期，值越小则越快的移至warm列表