MySQL（缓存机制）
本文转自网络
在服务器级别只提供了query cache，而在存储引擎级别，MyISAM和InnoDB分别引入了key cache和buffer pool
什么是query cache
Mysql没有shared_pool缓存执行计划，但是提供了query cache缓存sql执行结果和文本，如果在生命周期内完全相同的sql再次运行，则连sql解析都免去了；
所谓完全相同，包含如下条件
Sql的大小写必须完全一样；
发起sql的客户端必须使用同样的字符集和通信协议；
sql查询同一数据库下的同一个表(不同数据库可能有同名表)；
Sql查询结果必须确定，即不能带有now()等函数；
当查询表发生DML或DDL，其缓存即失效；
针对mysql/information_schema/performance_schema的查询不缓存；
使用临时表的sql也不能缓存；
开启缓存后，每个select先检查是否有可用缓存(必须对这些表有select权限)，而每个写入操作先执行查询语句并使相关缓存失效；
5.5起可缓存基于视图的查询
Mysql维护一个hash表用来查找缓存，其key为sql text，数据库名以及客户端协议的版本等
相应参数
Have_query_cache：服务器是否支持查询缓存
Query_cache_type：0(OFF)不缓存；1(ON)缓存查询但不包括使用SQL_NO_CACHE的sql；2(DEMAND)只缓存使用SQL_CACHE的sql
Query_cache_size：字节为单位，即使query_cache_type=0也会为分配该内存，所以应该一并设置为0
Query_cache_limit：允许缓存的最大结果集，大于此的sql不予缓存
Query_cache_min_res_limit：用于限定块的最小尺寸，默认4K；
缓存的metadata占有40K内存，其可分为大小不等的多个子块，各块之间使用双向链表链接；根据其功能分别存储查询结果，基表和sql text等；
每个sql至少用到两个块：分别存储sql文本和查询结果，查询引用到的表各占一个块；
为了减少响应时间，每产生1行数据就发送给客户端；
数据库启动时调用malloc()分配查询缓存
查询缓存拥有一个全局锁，一旦有会话获取就会阻塞其他访问缓存的会话，因此当缓存大量sql时，缓存invalidation可能会消耗较长时间；
Innodb也可以使用查询缓存，每个表在数据字典中都有一个事务ID计数器，ID小于此值的事务不可使用缓存；表如果有锁(任何锁)则也不可使用查询缓存；
状态变量
有关query cache的状态变量都以Qcache打头
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
+-------------------------+--------+
Qcache_inserts—被加到缓存中query数目
Qcache_queries_in_cache—注册到缓存中的query数目
缓存每被命中一次，Qcache_hits就加1；
计算缓存query的平均大小=(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache
Com_select = Qcache_not_cached + Qcache_inserts + queries with errors found during the column-privileges check
Select = Qcache_hits + queries with errors found by parser
Buffer pool
innodb即缓存表又缓存索引，还有设置多个缓冲池以增加并发，很像oracle
采用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被定义了才可用
相关源代码如下
#ifdef UNIV_DEBUG
{"buffer_pool_pages_latched",
(char*) &export_vars.innodb_buffer_pool_pages_latched, SHOW_LONG},
#endif /* 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个部件
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列表
MyISAM只缓存索引，
可创建多个key buffer—set global hot_cache.key_buffer_size=128*1024
索引指定key buffer—cache index t1 in hot_cache
可在数据库启动时load index into key_buffer提前加载缓存，也可通过配置文件自动把索引映射到key cache
key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql
mysqld_init.sql内容如下
CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache
CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache
默认采用LRU算法，也支持名为中间点插入机制midpoint insertion strategy
索引页刚读入key cache时，被放在warm列的尾部，被访问3次后则移到hot列尾并循环移动，如果在hot列头闲置连续N次都没访问到，则会被移到warm列头，成为被剔出cache的首选；
N= block no* key_cache_age_threshold/100
