MySQL缓存研究

MySQL缓存研究

1. 题目概述

在互联网行业，MySQL数据库毫无疑问已经是最常用的数据库。LAMP（Linux+Apache+MySQL+PHP）甚至已经成为专有名词，也是很多中小网站建站的首选技术架构。和其他数据库系统相比，MySQL有点与众不同，它的架构可以在多种不同的场景中应用并发挥好的作用。MySQL并不完美，却足够灵活，能够适应高要求的环境，例如Web应用。为了充分发挥MySQL的性能并顺利地使用，就必须理解其设计，这样才能深入理解MySQL服务器。
很多数据库产品都能够缓存查询的执行计划，对于相同类型的SQL就可以跳过SQL解析和执行计划生成阶段。MySQL在某些场景下也可以实现，但是MySQL还有另一种不同的缓存类型：缓存完整的SELECT查询结果，也就是“查询缓存”。本课题是对MySQL数据库的缓存进行研究，根据MySQL5.5的官方用户手册，我们主要对MySQL的查询缓存进行深入全面地探索和研究，并简要介绍其他类型的缓存。
关键词：MySQL、缓存、查询缓存
 
2. 研究内容与目的
本课题的主要研究内容为MySQL逻辑架构和查询缓存的基本概念，MySQL如何判断缓存命中，查询缓存如何使用内存，什么情况下查询缓存能发挥作用，如何配置和维护查询缓存，InnoDB和查询缓存的关系，以及通过查询缓存优化MySQL性能和MySQL其他类型的缓存。
通过研究MySQL的逻辑架构，能够帮助我们深入理解MySQL服务器，理解MySQL对查询语句的执行过程，有助于我们研究MySQL查询缓存的工作机制。
通过研究MySQL是如何判断缓存命中，有助于我们判断通过查询缓存是否能够提高MySQL的查询效率，是否能够给系统带来性能上的提升。
由于查询缓存是完全存储在内存中的，所以在配置和使用它之前，我们需要先了解它是如何使用内存的。通过研究查询缓存如何使用内存，有助于我们深入理解缓存的工作原理。
通过研究在哪种情况下查询缓存能够发挥作用，能够帮助我们根据不同情况调整缓存策略，从而更好的提高系统的性能。
当我们理解了查询缓存的工作原理后，我们就可以对MySQL进行配置，通过研究如何配置和维护查询缓存，有助于我们更好的使用查询缓存。
通过研究InnoDB和查询缓存的关系，有助于我们从MySQL存储引擎的角度理解查询缓存，从而提高对查询缓存的认识。
最后研究通过查询缓存来优化MySQL服务器的性能以及其他类型的缓存，帮助我们全面理解MySQL缓存机制。
 
3. 研究方法
3.1研究方法
3.1.1网上资料搜集
随着互联网的发展，越来越多的人通过互联网来查找资料，进行学习。通过在互联网上以“MySQL缓存”为关键字进行查找，有许多关于MySQL查询缓存的笔记、博客等，通过对其进行学习，初步了解MySQL查询缓存。
3.1.2查阅相关书籍
在本课题研究中，主要参考书籍是电子工业出版社出版的《高性能MySQL》。通过系统学习MySQL的相关理论知识，全面深入地理解查询缓存的相关概念和工作原理。其次，参考MySQL5.5的官方用户手册，学习MySQL查询高速缓冲的配置和维护。
3.2实验方法
3.2.1实验法
由于MySQL服务器搭建简单，所以在本课题的研究中，主要采用实验法进行研究。在个人计算机中安装VMware Workstation10软件，并新建一台Ubuntu Server 14.04的虚拟机，安装MySQL5.5版本的数据库软件，通过SSH协议，利用Putty软件在个人计算机上远程管理MySQL服务器。
在实验之前，我们需要对MySQL数据库服务器性能进行基准测试，这里我们使用sysbench测试工具。由于查询缓存主要使用的硬件资源是内存，所以我们通过MONyog对MySQL数据库服务器的进行监控，以便我们及时了解MySQL查询缓存对内存的使用情况。
3.3可行性分析
3.3.1物理环境可行
目前，个人计算机的操作系统多为Windows7，内存一般为4G，硬盘一般为500G，普遍使用Inter i5的处理器，所以能够使用VMware workstation虚拟化软件在个人计算机上实现单机虚拟化环境的部署，MySQL虚拟服务器的虚拟硬盘大小为20G，CPU为单路单核，内存为1G，使用桥接模式与宿主机构成小型局域网，操作简单，所以物理环境可行。
3.3.2技术条件可行
首先，通过本学期Linux操作系统的学习，我们已经掌握了Linux操作系统的使用方法，已经能够独立在个人计算机上实现单机虚拟化，能够快速学习并搭建MySQL服务器；其次，通过本学期《MySQL数据库管理》的学习，已经具有了使用MySQL数据库的基本技能，能够对MySQL进行简单的管理，所以在技术条件上具有可行性。
 
4. 研究报告
4.1 MySQL逻辑结构
在研究MySQL缓存之前，我们需要能够在头脑中构建一副MySQL各组件之间如何协同工作的架构图，这有助于我们深入理解MySQL服务器。如下所示：

  

图4-1 MySQL服务器逻辑架构图

最上层的服务并不是MySQL所独有的，大多数基于网络的C/S的工具或者服务都有类似的架构，比如连接处理、授权认证、安全等。
第二层架构是MySQL的核心服务功能，包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
第三层包含了存储引擎，存储引擎负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信。不同的存储引擎之间存在差异，使用接口屏蔽了这些差异，使得这些差异对上层的查询过程透明。存储引擎不去解析SQL，但是InnoDB会解析外键定义，不同的存储引擎之间也不会互相通信，而只是简单地响应上层服务器的请求。
4.1.1连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。在MySQL5.5中支持线程池插件，可以使用较少的线程服务大量的连接。
当客户端连接到MySQL服务器时，服务器需要对其进行认证。认证是基于用户名、原始主机信息和密码。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。
4.1.2优化与执行
MySQL会解析查询，并创建内部数据结构，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示优化器，影响它的决策过程，也可以请求优化器解释优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行。优化器并不关注表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。对于SELECT语句，在解析查询之前，服务器会先检查查询缓存，如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。
4.2查询缓存的基本概念
查询缓存就是缓存完整的SELECT查询结果。MySQL查询缓存保存的查询返回的完整结果，当查询命中该缓存，MySQL会立即返回结果，跳过了解析、优化和执行阶段。
查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有缓存数据都将失效。查询缓存值的相关条目被清空。这里的变化指的是表中任何数据或是结构的改变，包括INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包括那些映射到改变了的表的使用MREGE表的查询。这种机制的效率看起来比较低，因为数据表变化时很可能对应的查询结果并没有变更，但是这种简单实现代价很小，对于一个非常繁忙的系统来说非常重要。
查询缓存对于应用程序来说应该是完全透明的。应用程序无须关心MySQL是通过查询缓存返回的结果还是实际执行返回的结果。事实上，这两种方式执行的结构是完全相同的。
4.3 MySQL如何判断缓存命中
MySQL判断缓存命中的方法是：缓存存放在一个引用表中 ，通过一个哈希值引用，这个哈希值包括了查询本身、当前要查询的数据库、客户端协议的版本等一切其他可能会影响返回结果的信息。
当判断缓存是否命中时，MySQL不会解析、“正规化”或者参数化查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。任何字符上的不同，例如空格、注释等，有任何的不同，查询缓存就认为这是一个不同的查询，就会导致缓存的不命中。
当查询语句中有一些不确定的数据时，则不会被缓存。例如包含函数NOW()或者CURRENT_DATE()的查询不会被缓存。类似的，包含CURRENT_USER或者CURRENT_ID()的查询语句因为会根据不同的用户返回不同的结果，所以也不会被缓存。事实上，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表或者任何包含列级别权限的表，都不会被缓存。
在检查查询缓存之前，MySQL通过一个大小写不敏感的检查看看SQL语句是不是以SET开头。如果查询语句中包含任何的不确定函数，那么在查询缓存中是不可能找到缓存结果的。MySQL在任何时候只要发现不能被缓存的部分，就会禁止这个查询被缓存。
MySQL的查询缓存在很多时候可以提升查询性能，在使用的时候，有一些问题需要特别注意。首先，打开查询缓存对读和写操作都会带来额外的消耗：
（1）读查询在开始之前必须先检查是否命中缓存。
（2）如果这个读查询可以被缓存，那么当完成执行后，MySQL若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗。
（3）这对写操作也会有影响，应为当向某个表写入数据的时候，MySQL必须将对应表的所有缓存都设置失效。如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大的系统消耗。
对于使用InnoDB存储引擎的用户来说，事务的一下特性会限制查询的使用。当一个语句在事务中修改了某个表，MySQL会将这个表的对应的查询缓存都设置失效，并且InnoDB会暂时将这个修改对其他事务屏蔽。在这个事务提交之前，这个表的相关查询是无法被缓存的，所以所有在这个表上面的查询都只能在该事务提交后才被缓存。因此长时间运行的事务，会大大降低查询缓存的命中率。
如果查询缓存使用了很大量的内存，缓存失效操作就可能成为一个非常严重的问题瓶颈。如果缓存中存放了大量的查询结果，那么缓存失效操作时整个系统都可能会僵死一会儿。因为这个操作是一个全局锁操作保护的，所有需要做该操作的查询都要等待这个锁，而且无论是检测是否命中缓存，还是缓存失效检测都需要等待这个全局锁。
4.4查询缓存如何使用内存
由于查询缓存是完全存储在内存中的，所以在配置和使用它之前，我们需要研究它是如何使用内存的。除了查询结果之外，需要缓存的还有很多别的维护相关的数据。例如哪些内存目前是可用的，哪些是已经用掉的，哪些用来存储数据表和查询结果之前的映射，哪些用来查询字符串和查询结果。
这些基本的管理维护数据结构大概需要40KB的内存资源，除此之外，MySQL用户查询缓存的内存被分成一个个的数据块，数据库是变长的。每一个数据块中，存储了自己的类型，大小和存储的数据本身，还外加指定前一个和后一个数据块的指针。数据块的类型有：存储查询结果、存储查询和数据表的映射、存储查询本身，等等。
当服务器启动的时候，它先初始化查询缓存需要的内存。这个内存吃初始是一个完整的空闲块。这个空闲块的大小就是所配置的查询缓存大小再减去用于维护元数据的数据结构所消耗的空间。
当有查询结果需要缓存的时候，MySQL先从大的空间块中申请一个数据块用于存储结果。这个数据块需要不小于参数query_cache_min_res_unit的配置。因为需要在查询开始返回结果的时候就分配空间，而此时是无法预知查询结果到底多大的，所以MySQL无法为每一个查询结果精确分配大小正好匹配的缓存空间。
因为需要先锁住空间块，然后找到合适大小的数据块。所以分配内存块是一个非常慢的操作。MySQL尽量避免这个操作的次数。当需要缓存一个查询结果的时候，它先选择一个尽可能小的内存块，然后将结果存入其中。如果数据块全部用完，但仍有剩余数据需要存储，那么MySQL会申请一个新的尽可能小的内存块来存储结果数据。当查询完成时，如果申请的内存空间还有剩余，MySQL会将其释放，并放入空闲内存的部分。如图4-2所示。
 
图4-2 查询缓存如何分配内存来存储结果数据
“分配内存块”是指空闲块列表中找到一个合适的内存块，或者从正在使用的，待淘汰的内存块中回收再使用。也就是说，这里MySQL自己管理一大块内存，而不依赖操作系统的内存管理。
在实际情况中内存的分配会较上面的复杂。假设平均查询结果非常小，服务器在并发地向不同的两个连接返回结果，返回完结果后MySQL回收剩余数据块时发现数据块小于query_cache_min_res_unit，所以不能够直接在后续的内存块分配中使用。如图4-3所示：
 
图4-3 查询缓存中存储查询结果后剩余的碎片
在收缩第一个查询结果使用的缓存空间时，就会在第二个查询结果之间留下一个“空隙”，因为小于query_cache_min_res_unit而不能再次被查询缓存使用，所以称之为“碎片”。
4.5什么情况下查询缓存能发挥作用
并不是什么情况下查询缓存都会提高系统性能。缓存和失效都会带来额外的消耗，所以只有当缓存带来的资源节约大于其本身的资源消耗时才能给系统带来性能的提升。理论上，可以通过观察打开或者关闭查询缓存时候的系统效率来决定是否需要开启查询缓存。关闭查询缓存时，每个查询都需要完整的执行，每一次写操作执行完成后立刻返回，打开查询缓存时，每次读请求先检查缓存是否命中，如果命中则立即返回，否则就完整地执行查询，每次写操作则需要检查查询缓存中是否有需要失效的缓存，然后再返回。
要实际具体评估打开查询缓存是否能够带来性能提升还要考虑一些外部的因素。MySQL在SHOW STATUS中只能提供一个全局的性能指标，所以很难根据此来判断查询缓存是否能够提升性能。对于那些需要消耗大量资源的查询通常都是非常适合缓存的，如COUNT（）。总的来说，对于复杂的SELECT语句都可以使用查询缓存，因为这类查询每次执行消耗很大，但返回的结果集很小，非常适合查询缓存。
一个判断查询缓存是否有效的直接数据是命中率，就是使用查询缓存返回结果占总查询的比率。当MySQL接收到一个SELECT查询的时候，要么增加Qcache_hits的值，要么增加Com_select的值。所以查询缓存命中率可以由如下公式计算：
Qcache_hits/(Qcache_hits+Com_select)
Com_Select的值可以通过命令SHOW STATUS LIKE ‘Com_select’获得。
有了计算公式，那么命中率多大才是最好？具体情况具体分析。只要查询缓存来带的效率提升大于查询缓存带来的额外消耗。另外，缓存了哪些查询也很重要，只要对系统性能提升有好处。所以没有一个简单的规则可以判断查询缓存是否对系统有好处。
任何SELECT语句没有从查询缓存中返回都称为“缓存未命中”。原因可能如下：
（1）查询语句无法被缓存，可能是查询中包含一个不确定的函数，或者是查询结果太大而无法缓存。这都会导致状态值Qcache_not_cache增加。
（2）MySQL从未处理这个查询，结果从不被缓存过。
（3）虽然之前缓存了查询结果，但是由于查询缓存的内存用完了，MySQL需要将某些缓存“逐出”，或者由于数据表被修改导致缓存失败。
如果服务器上有大量缓存未命中，但是实际上绝大多数查询都被缓存了，那么一定是有如下情况发生：
（1）查询缓存还没有完成预热。即MySQL还没有机会将查询结果都缓存起来。
（2）查询语句之前从未执行过。如果应用程序不会重复执行一条查询语句，那么即使完成预热仍然会有很多缓存为命中。
（3）缓存失效操作太多了。
缓存碎片、内存不足、数据修改都会造成缓存失效。如果配置了足够的缓存空间，而且query_cache_min_res_unit设置也合理的话，缓存失效应该主要是数据修改导致。可以通过参数Com_*来查看数据修改的情况，还可以通过Qcache_lowmen_prunes来查看有多少次失效是由于内存不足导致的。
在考虑缓存命中率的同时，通常还需要考虑缓存失效带来的额外消耗。一个极端的方法是，对某一个表先做一次只有查询的查询，并且所有的查询都命中缓存，而另一个相同的表则制作修改操作。这时，查询缓存的命中率就是100%。但因为会更新操作带来额外的消耗，所以查询缓存并不一定会带来总体效率的提升。
每一个应用程序都会有一个“最大缓存空间”，甚至对一些纯读的应用来说也一样。最大缓存空间是能够缓存所有可能查询结果的缓存空间总和。理论上，对于多数应用来说，这个值会非常大。实际上，由于缓存失效的原因，缓存空间一直都不会接近“最大缓存空间”。
通常可以通过观察查询缓存内存的实际使用情况，来确定是否需要缩小或者扩大查询缓存。如果查询缓存空间长时间都有剩余，那么建议缩小；如果经常由于空间不足而导致查询缓存失效，那么则需要增大查询缓存。最好的判断查询缓存是否有效的办法是通过查看某类查询时间消耗是否增大或者减少来判断。
4.6如何配置和维护查询缓存
理解了查询缓存的工作原理，就能够简单地对MySQL进行配置。相关参数如下所示：
query_cache_type：是否打开缓存。可以设置成OFF、ON或DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才放入查询缓存。
query_cache_size：查询缓存使用的总内存空间，单位是字节。这个值必须是1024的整数倍。
query_cache_min_res_unit：在查询缓存中分配内存块时的最小单位。
query_cache_limit：MySQL能够缓存的最大结果查询。如果查询结果大于这个值，则不会被缓存。因为查询缓存在数据生成的时候就开始尝试缓存数据，所以只有当结果全部返回后，MySQL才知道查询结果是否超出限制。如果超出，MySQL则增加状态值Qcache_not_cached，并将结果从查询缓存中删除。
query_cache_wlock_invalidate:如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果。默认是OFF。
4.6.1减少碎片
没有什么办法能够完全避免碎片，但是选择合适的query_cache_min_res_unit可以减少由碎片导致的内存空间浪费。设置合适的值可以平衡每个数据块的大小和每次存储结果时内存块申请的次数，其实也是在平衡内存浪费和CPU消耗。这个参数的最合适的大小和应用程序的查询结果的平均大小直接相关。可以通过内存实际消耗（query_cache_size-Qcache_free_memory）除以Qcache_queries_in_cache计算单个查询的平均缓存大小。通过Qcache_free_blocks来观察碎片，它反映了查询缓存中空闲块的多少。如果Qcache_free_blocks大小恰好到了Qcache_total_blocks/2，那么查询缓存就有严重的碎片问题。而如果还有很多空闲块，状态值Qcache_lowmen_prunes还不断地增加，则说明由于导致了过早的删除查询缓存结果。
可以使用命令FLUSH QUERY CACHE完整碎片整理。这个命令会将所有的查询缓存重新排列，并将所有的空闲空间都聚集到查询缓存的一块区域上。不过，这个命令不能将查询缓存清空，清空命令由RESET QUERY CACHE完成。
4.6.2提高查询缓存的使用率
如果查询缓存不再有碎片问题，但是查询命中率仍然很低，还可能是查询缓存的内存空间导致。如果MySQL无法为一个新的查询缓存结果的时候，则会选择删除某个老的缓存结果。这样会增加状态值Qcache_lowmen_prunes，如果这个值增加的很快，那么可能有两种原因：
（1）如果还有很多空闲块，那么就是碎片的问题。
（2）如果没什么空闲块，说明在这个系统压力下，分配的查询缓存空间不够大。可以通过检查状态值Qcache_free_memory来查看还有多少没有使用的内存。
如果空闲块很多，碎片很少，也没有什么由于内存导致的缓存失效，但是命中率仍然很低，那么说明在当前系统压力下，查询缓存没有什么好处。
如果在观察命中率时，仍然无法确定查询缓存是否给系统带来好处，那么可以通过禁用它，然后观察系统的性能，再重新打开它，观察性能变化，据此来判断查询缓存是否能够给系统带来好处。通过将query_cache_size设置为0，来关闭查询缓存。分析和配置查询缓存的流程图如下所示：
 
图4-4 如何分析和配置查询缓存
4.7 InnoDB和查询缓存
因为InnoDB有自己的MVCC机制，所以查询缓存的交互要更加复杂。在MySQL5.5的InnoDB版本开始，InnoDB会控制在一个事务中是否可以使用查询缓存，InnoDB会同时控制对查询缓存的读和写操作。
事务是否可以访问查询缓存取决于当前事务ID，以及对应的数据表上是否有锁。每一个InnoDB表的内存数据字段都保存了一个事务ID号，如果当前事务ID小于该事务ID，则无法访问查询缓存。如果表上有任何的锁，那么这个表的任何查询语句都是无法被缓存的。
当事务提交时，InnoDB持有锁，并使用当前的一个系统事务ID更新当前表的计数器。如果想更新任何表的内容，获得相应锁是前提条件。InnoDB将每个表的计数器设置成某个事务ID，代表当前存在的且修改了该表的最大的事务ID。所有大于该表技术器的事务才可以使用查询缓存。该表的计数器并不是直接更新为对该表进行加锁的事务ID，而是被更新成一个系统事务ID。
查询缓存存储、检索和失效操作都是在MySQL层面完成，InnoDB无法绕过或者延迟这个行为。但InnoDB可以在事务中显式地告诉MySQL何时应该让某个表的查询缓存都失效。原则上，在InnoDB的MVCC架构下，当某些修改不影响其他事务读取一致的数据时，是可以使用查询缓存的。
4.8通过查询缓存优化系统性能
库表结构的设计、查询语句、应用程序设计都可能会影响到查询缓存的效率。除了上面介绍的内容外，还需要注意以下几点：
（1）用多个小表代替一个大表对查询缓存有好处。这个设计将会使得失效策略能够在一个更合适的粒度上进行。
（2）批量写入时只需要做一次缓存失效，这样比单条写入效率更好。
（3）因为缓存空间太大，在过期操作的时候会导致服务器僵死。一个简单的解决办法就是控制缓存空间的大小（query_cache_size），或者直接禁用查询缓存。
（4）无法再数据库或者表级别控制查询缓存，但是可以通过SQL_CACHE和SQL_NO_CACHE来控制某个SELECT语句是否需要进行缓存，或者通过修改会话级别的变量query_cache_type来控制查询缓存。
（5）对于写密集型的应用来说，直接禁用查询缓存可能会提高系统的性能，关闭查询缓存可以移除所有相关的消耗。
（6）因为对互斥信号量的竞争，有时直接关闭查询缓存对读密集型的应用会有好处。
如果不想所有的查询都进入查询缓存，但是又希望某些查询走查询缓存，那么可以将query_cache_type设置成DEMAND,然后在希望缓存的查询中加上SQL_CACHE。如果希望缓存多数查询，而少数查询又不希望缓存，那么可以使用关键字SQL_NO_CACHE。
4.9 MySQL服务器的其他重要缓存简介
如果服务器只运行MySQL，所有不需要为操作系统以及查询处理保留的内存都可以用做MySQL缓存。相比其他，MySQL需要为缓存分配更多地内存。它使用缓存来避免磁盘访问，磁盘访问比内存访问数据要慢得多。除了上面详细介绍的查询缓存外，还有一些在大部分情况来说重要的缓存，例如InnoDB缓存池、InnoDB日志文件和MyISAM数据的操作系统缓存、MyISAM键缓存以及无法手工配置的缓存。
4.9.1 InnoDB缓存池
如果大部分都是InnoDB表，InnoDB缓存池或许比其他任何东西更需要内存。InnoDB缓存池并不仅仅缓存索引，它还缓存行的数据、自适应哈希索引、插入缓冲、锁、以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序地写回。总之，InnoDB严重依赖缓冲池，必须为它分配足够的内存。可以使用SHOW命令得到的变量或者例如innotop这样的工具监控InnoDB缓冲池的内存利用情况。
4.9.2 MyISAM键缓存
MyISAM的键缓存也被称为键缓冲，默认只有一个键缓存，但也可以创建的多个。MyISAM自身只缓存索引，不缓存数据。如果大部分是MyISAM表，就应该为键缓存分配比较多的内存。最重要的配置项是key_buffer_size。任何没有分配给它的内存都可以被操作系统缓存利用。MyISAM使用操作系统缓存来缓存数据文件，通常数据文件比索引要大。因此，可以把更多的内存保留给操作系统而不是键缓存。
MySQL键缓存块大小也是很重要的，它影响了MyISAM、操作系统缓存、以及文件系统之间的交互。如果缓存块太小了，可能会碰到写时读取（read-around-write），就是操作系统在执行写操作之前必须先从磁盘上读取一些数据。假设操作系统的页大小是4KB，索引块大小是1KB，具体过程如下：
（1）MyISAM请求从磁盘上读取1KB的块；
（2）操作系统从磁盘上读取4KB的数据并缓存，然后发送需要的1KB数据给MyISAM；
（3）操作系统丢弃缓存数据以给其他数据腾出缓存；
（4）MyISAM修改1KB的索引块，然后请求操作系统把它写回磁盘；
（5）操作系统从磁盘读取同一个4KB的数据，斜日操作系统缓存，修改MyISAM改动的这1KB数据，然后把整个4KB的块写回磁盘。
4.9.3线程缓存
线程缓存保存那些当前没有与连接关联但是准备为后面新的连接服务的线程。当一个新的连接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把它分配给这个新的连接。当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存。如果没有空间的话，MySQL会销毁这个线程。只要MySQL在缓存里还有空的线程，它就可以迅速地响应连接请求，因为这样就不用为每个连接创建新的线程。Thread_cache_size变量指定了MySQL可以保持在缓存中的线程数。
4.9.4表缓存
表缓存存储的对象代表的是表。每个在缓存中的对象包含相关表.frm文件的解析结果，加上一些其他数据。准确地说，在对象里的其他数据的内存依赖于表的存储引擎。表缓存可以重用资源。对于MyISAM表来说，表缓存的好处是可以让服务器避免修改MyISAM文件头来标记表“正在使用中”。
4.9.5 InnoDB数据字典
InnoDB有自己的表缓存，称为表定义缓存或者数据字典，在目前的MySQL版本中还不能对其进行配置。当InnoDB打开一张表，就增加一个对应的对象到数据字典。每张表可能占用4KB或者更多的内存，当表关闭的时候也不会从数据字典中移除它们。
 
5. 测试报告/实验报告
5.1实验目的
1、理解MySQL缓存原理；
2、掌握MySQL查询缓存配置和测试方法
3、掌握MySQL性能监控的主要形式和手段；
4、掌握MySQL性能监控工具MONyog的使用方法；
5、理解MySQL查询缓存对系统性能的作用。
5.2实验环境
1、Windows操作系统的计算机，安装VMware WorkStation工具；
2、局域网网络环境，并且使用固定IP地址；
3、基于VMware WorkStation工具安装Ubuntu Server操作系统；
4、在Ubuntu Server系统中，安装MySQL数据库软件；
5、开启Ubuntu Server SSH访问权限，开启MySQL远程访问权限；
5.3实验要求
1、基于SSH远程管理操作系统和远程管理MySQL的模式下开展实验研究；
2、完成实验内容的具体要求，掌握MySQL缓存的基本原理和实现MySQL查询缓存的方法；
3、完成对MySQL服务器的监控，体会应用系统性能监控的价值。
5.4实验原理
1、Linux远程管理，SSH、MySQL的远程管理；
2、MySQL查询缓存的工作原理；
3、MySQL服务器的性能监控的基本原理；
4、MySQL服务器的性能指标的基本概念和合理数据范围。
5.5实验步骤
（本实验的所有操作在Windows操作系统下进行，服务器和MySQL使用Ubuntu Server操作系统和MySQL Community Server进行，访问方式使用TCP/IP方式）
1、搭建实验环境
（1）根据平时实验所提供的Ubuntu Server操作系统的VMware文件，并通过附加的方式附加到VMware WorkStation中。
（2）启动Ubuntu Server，并登陆系统。
（3）修改Ubuntu Server的网络配置和本地Windows计算机的网络配置，使得能够通过SSH使用Putty远程访问Ubuntu Server。
（4）修改MySQL Community Server的配置，使得能够通过MySQL-Front远程访问MySQL服务器。
（5）使用MySQL-Front软件访问MySQL服务器，确定MySQL远程访问的配置
2、安装MONyog软件实现MySQL性能监控
（1）使用WinSCP将MONyog-6.2.2-0.i386.tar.gz上传到Ubuntu Server虚拟机的/tmp目录下；
（2）解压缩后将文件夹移动至/usr/local下；
（3）进入MONyog/bin目录下，使用./MONyog start命令启动MONyog监控软件的服务；
（4）打开浏览器输入：http://172.16.151.75:5555，登陆MONyog软件；
（5）添加服务器。输入MySQL服务器的地址，MySQL远程管理的账户和密码，测试连接，连接成功后保存。
3、MySQL查询缓存研究
查询缓存存储SELECT查询的文本以及发送给客户端的相应结果。如果随后收到一个相同的查询，服务器从查询缓存中重新得到查询结果，而不再需要解析和执行查询。如果由一个不经常改变的表并且服务器收到该表的大量相同查询，查询缓存在这样的应用环境中十分有用。对于许多Web服务器来说存在这种情况。
（1）通过have_query_cache服务器系统变量指示查询缓存是否可用：
 
图5-6 查询缓存开启状态
（2）MySQL缓存状态的查看
 
图5-7 MySQL查询缓存状态


重要参数详解：
have_query_cache的值如果是yes，表示开启缓存。
binlog_cache_size:默认大小是32768，即32K。
max_binlog_cache_size: 默认值是18446744073709547520，和binlog_cache_size相对应，代表binlog所能使用的cache最大使用大小。如果系统中事务过多，而此参数值设置有小，则会报错。
query_cache_limit：允许 Cache 的单条 Query 结果集的最大容量，默认是1MB，超过此参数设置的 Query 结果集将不会被 Cache。
query_cache_min_res_unit：设置 Query Cache 中每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小。
query_cache_size：设置 Query Cache 所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到1024的倍数。
query_cache_type：控制 Query Cache 功能的开关，可以设置为0(OFF),1(ON)和2(DEMAND)三种。
query_cache_wlock_invalidate：控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache，默认为OFF。
InnoDB缓存池的状态信息：
 
图5-8 缓存池状态信息
参数详解：
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：向缓存的写数量
键缓存（key cache）的状态信息，包含4个部件：
 
图5-9 键缓存状态信息
Key_buffer_size：16777216，即16MB
Key_cache_block_size：单个块大小，默认1k
Key_cache_division_limit：warm子列的百分比(默认100)，key cache buffer列表的分隔点，用于分隔host和warm子列表
Key_cache_age_threshold：页在hot子列中的生命周期，值越小则越快的移至warm列表
线程缓存状态信息：
 
图5-10 线程缓存状态信息
（3）检查MySQL查询缓存使用情况
查询缓存状态变量都以Qcache开头，状态值如下所示：
 
图5-11 查询缓存状态信息
参数详解：
Qcache_free_blocks：目前还处于空闲状态的 Query Cache中内存 Block数目。
Qcache_free_memory：目前还处于空闲状态的 Query Cache内存总量。
Qcache_hits：Query Cache命中次数。
Qcache_inserts：向 Query Cache中插入新的Query Cache的次数，也就是没有命中的次数。
Qcache_lowmem_prunes：当 Query Cache内存容量不够，需要从中删除老的Query Cache以给新的 Cache 对象使用的次数。
Qcache_not_cached：没有被 Cache的SQL数，包括无法被 Cache的 SQL 以及由于query_cache_type设置的不会被Cache的 SQL。
Qcache_queries_in_cache：目前在 Query Cache中的SQL数量。
Qcache_total_blocks：Query Cache中总的Block数量。
计算缓存query的平均大小：(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache
5、对MySQL查询缓存进行测试
（1）清空缓存并检查查询缓存状态
首先需要清空缓存，使用命令：RESET QUERY CACHE
 
图5-12 查询缓存状态1
（2）执行查询语句1:SELECT * FROM ta_XS;
 
图5-13 查询语句1结果
（3）执行查询语句2：select * from ta_XS;
 
图5-14 查询语句2信息
（4）再次检查查询缓存状态：
 
图5-15 查询缓存状态2
（5）再次执行查询语句1：SELECT  *  FROM  ta_XS;
 
图5-16 第二次执行查询语句1
（6）查看查询缓存状态：
 
图5-17 查询缓存状态3
（7）清空缓存，检查查询缓存状态
 
图5-18 清空查询缓存
5.6实验记录
（1）系统配置单
表5-1：Ubuntu Server系统配置单
操作系统名称 Ubuntu Server14.04 X86 虚拟主机兼容性 VMware Workstation 8.0
主机名 LinuxMySQLTeach 分区格式 默认（swap,ext4）
CPU 单路单核 内存 1G
本地域名 localhost 文件系统 Ext2
用户名 linuxmysqlteach 密码 teach#NIC
文件安装位置 D:\ ubuntu 14.04.1 server x86 Linux MySQL Teach 软件 OpenSSH,防火墙，MySQL Community Server，sysbench，MONyog，Apache，php
硬盘 大小20G
类型为SCSI
介质为虚拟磁盘 网络配置 IP地址：172.16.151.X/24
网关为172.16.151.1
DNS为211.69.32.8
网络模式为桥接
防火墙配置 允许172.16.151.X/24的访问，开放80，22，3306端口 其他 MySQL用户名root
密码为teach#NICMySQL
（2）使用Putty软件远程管理Linux操作系统
 
图5-19 Putty远程管理Linux操作系统
（3）为MySQL配置远程访问账户
 
图5-20 为MySQL授权远程管理
（4）使用MySQL-Front远程连接MySQL服务器
 
图5-21 MySQL-Front远程管理MySQL数据库
（5）使用浏览器登陆MONyog性能监控软件
 
图5-22 MONyog登录
 
图5-23 添加MySQL服务器
（6）MONyog监控情况：
 
图5-23 缓存不命中
 
图5-24 查询缓存内存块使用率
 
图5-25 查询缓存效率
5.7实验分析
在本次实验中，比较查询缓存状态1和查询缓存状态2的信息可以知道，由于查询语句1和查询语句2的不同，所以两次查询分别进行了缓存，即Query_inserts增加值为2；比较查询状态2和查询状态3的信息可知，Query_inserts的值没有增加，这是因为查询语句1的结果已经被缓存了。这时的查询缓存命中率约为18%，主要原因为由于缓存的其他信息占用了太多的缓存空间。
5.8实验结论
通过实验，可以得出结论，查询缓存并不是在什么情况下都能提高系统性能，只有当缓存带来的资源的节约大于其本身对资源的消耗的情况下才会提高系统性能；查询缓存命中率是一个很难判断的数据；当缓存区块中有太多的缓存时，会降低缓存的命中率，因此要及时进行缓存清空。










6. 课题研究结论
在本课题的研究中，我们发现，查询必须是完全相同的（逐字节相同）才能被认为是相同。另外，同样的查询字符串由于其他原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同默认字符集的查询被认为是不同的查询并且分别进行缓存。
从查询缓存中提取一个查询之前，MySQL检查用户对所有相关数据库和表的SELECT权限。如果没有权限，不使用缓存结果。如果从查询缓存中返回一个查询结果，服务器把Qcache_hits状态变量的值加1，而不是Com_select变量。如果一个表被更改了，那么使用这个表的所有缓存查询将不再有效，并且从缓冲区移出。
如果一个查询中包含下面的函数中的任何一个，它将不会被缓存：
BENCHMARK()、CONNECTION_ID()、CURDATE()、CURRENT_DATE()、 CURRENT_TIME()、CURRENT_TIMESTAMP()、CURTIME()、DATABASE()、带一个参数的ENCRYPT()、FOUND_ROWS()、GET_LOCK()、LAST_INSERT_ID()、LOAD_FILE()、MASTER_POS_WAIT()、 NOW()、RAND()、RELEASE_LOCK()、SYSDATE()、不带参数的UNIX_TIMESTAMP()、 USER()
另外，除了上面的函数外，还有一些情况的查询也不会被缓存：引用自定义函数、引用自定义变量、引用MySQL系统数据库中的表、SELECT ...IN SHARE MODE、SELECT ...FOR UPDATE、SELECT ...INTO OUTFILE ...、SELECT ...INTO DUMPFILE ...、SELECT * FROM ...WHERE autoincrement_col IS NULL、使用TEMPORARY表、不使用任何表、用户有某个表的列级权限等。
为了设置查询缓存大小，设置query_cache_size系统变量。设置为0表示禁用查询缓存，默认状态。开启查询缓存时，应注意查询缓存至少大约需要40KB来分配其数据结构，可以通过命令SET GLOBAL query_cache_size = 41984设置。要控制可以被缓存的具体查询结果的最大值，应设置query_cache_limit变量，默认值是1MB。
当一个查询结果（返回给客户端的数据）从查询缓冲中提取期间，它在查询缓存中排序。因此，数据通常不在大的数据块中处理。查询缓存根据数据排序要求分配数据块，因此，当一个数据块用完后分配一个新的数据块。因为内存分配操作是费时的，所以通过query_cache_min_res_unit系统变量给查询缓存分配最小值。当查询执行时，最新的结果数据块根据实际数据大小来确定，因此可以释放不使用的内存。
使用FLUSH QUERY CACHE语句来清理查询缓存碎片以提高内存使用性能。该语句不从缓存中移出任何查询。RESET QUERY CACHE语句从查询缓存中移出所有查询。
SELECT查询的总数量等价于：
Com_select+ Qcache_hits+ queries with errors found by parser
Com_select的值等价于：
Qcache_inserts+ Qcache_not_cached+ queries with errors found during columns/rights check
查询缓存使用长度可变块，因此Qcache_total_blocks和Qcache_free_blocks可以显示查询缓存内存碎片。执行FLUSH QUERY CACHE后，只保留一个空闲块。
每个缓存查询至少需要两个块（一个块用于查询文本，一个或多个块用于查询结果）。并且，每一个查询使用的每个表需要一个块。但是，如果两个或多个查询使用相同的表，仅需要分配一个块。
Qcache_lowmem_prunes状态变量提供的信息能够帮助调整查询缓存的大小。它计算为了缓存新的查询而从查询缓冲区中移出到自由内存中的查询的数目。查询缓冲区使用最近最少使用(LRU)策略来确定哪些查询从缓冲区中移出。
通过本课题的研究，我们可以知道查询缓存是一个非非常方便的缓存，对应用程序完全透明，无须任何额外的编码。除了查询缓存之外，MySQL还有其他的缓存方式，例如缓存池、键缓存、线程缓存、表缓存等。另外通过查询缓存来优化MySQL服务器的性能只是优化的一个方面，最高形式的优化既包含业务上的，也包含用户层上的。全方位的优化才是最好的优化。










7. 总结
本次通过对MySQL缓存的学习研究，了解了MySQL缓存的各种形式，例如：查询缓存、InnoDB缓存池、MyISAM键缓存、线程缓存、表缓存以及InnoDB数据字典。着重研究了查询缓存的工作原理、查询缓存的应用场景、查询缓存与InnoDB存储引擎的关系以及查询缓存的管理与维护。
为了深入理解MySQL查询缓存的工作原理，掌握如何使用查询缓存来提高系统的性能的方法，我们设计了具体的实验，通过具体实验，对比分析各个阶段的查询缓存状态来分析查询缓存的效率，得出结论。为了使实验更加客观和清晰，在实验过程中我们使用了MONyog作为MySQL服务器的性能监控工具。通过查看实时状态图，我们可以更好的理解查询缓存以及其他缓存的使用情况。
为了使研究内容更加详实，在MySQL缓存研究中的各种指标我们几乎都进行了说明和分析，这样能够帮助我们深入理解MySQL缓存的机制，能够帮助我们根据实际需求调整具体指标，从而更好的为应用程序服务。
在本课题的研究过程中，通过不断地学习逐渐了解和掌握MySQL的缓存原理，但是仍然有许多不如意的地方。一方面由于本课题研究主要通过在个人Windows操作系统的计算机上创建虚拟服务器，通过MySQL虚拟机来进行研究，所以可能性能上没有专业的MySQL服务器性能更加明显；另一方面，由于MySQL数据库中数据量较少，所以进行查询缓存研究的结论可能不具有代表性。
在本课题研究中，由于我们着重研究了查询缓存、而简要介绍了其他类型的缓存，所以我们的MySQL缓存研究是不全面的。因此在接下来的研究中，我们需要对其他类型的缓存进行细致的研究，并以实验来深化对不同类型缓存的理解。当然，除了MySQL缓存外，要想整体提高系统的性能，我们要需要在不同的层次上都实现缓存，比如应用层的缓存。在实际应用中应根据应用场景的不同分层实现缓存，才能整体提高系统的效率。
综上所述，我们通过对MySQL缓存的理论研究，了解了MySQL的逻辑架构，理解了MySQL查询的执行过程，掌握了MySQL查询缓存的基本原理，帮助我们全面了解MySQL缓存的工作机制；通过实验，深化了对MySQL缓存理论的理解，能够帮助我们更好地优化MySQL服务器性能；另外，由于一些原因，对MySQL缓存的研究还不够完善。总之，本课题对MySQL缓存的研究以查询缓存为主，其他缓存为辅，理论研究为主，实验研究为辅的方式进行，并最终得出了一些结论，具有一定的参考意义。