参考官方文档：

https://dev.mysql.com/doc/refman/5.7/en/buffering-caching.html

MySQL使用多种策略缓存内存缓冲区中的信息以提高性能。

8.10.1 InnoDB buffer Pool

InnoDB维护一个称为缓冲池的存储区域，用于在内存中缓存数据和索引。 了解InnoDB缓冲池如何工作，并利用它来将频繁访问的数据保存在内存中，这是MySQL调优的一个重要方面。

方针

理想情况下，您可以将缓冲池的大小设置为尽可能大的值，从而为服务器上的其他进程留出足够的内存以在没有过多分页的情况下运行。 缓冲池越大，InnoDB就越像内存数据库，从磁盘读取数据一次，然后在后续读取期间从内存中访问数据。 缓冲池甚至缓存由插入和更新操作更改的数据，以便可以将磁盘写入分组在一起以获得更好的性能。

根据系统的典型工作负载，您可能会调整缓冲池中各部分的比例。 您可以调整缓冲池在填满时选择要缓存哪些块的方式，尽管在备份或报告等操作中突然出现活动高峰，但可以将频繁访问的数据保留在内存中。

对于具有大内存大小的64位系统，您可以将缓冲池分成多个部分，以最大限度地减少并发操作之间的内存结构争用。

内部细节

InnoDB使用最近最少使用（LRU）算法的变体将该池作为列表管理。 当需要将空间添加到池中时，InnoDB会清除最近最少使用的块，并将新块添加到列表的中间。 这种“中点插入策略”将列表视为两个子列表：

• 在头部，最近访问的“新”（或“年轻”）块的子列表。
• 尾部是最近访问过的“旧”块的子列表。

该算法保留了在新子列表中查询严重使用的块。 旧的子列表包含较少使用的块; 这些街区是驱逐的候选人。

LRU算法默认运行如下：

• 3/8的缓冲池专用于旧的子列表。
• 列表的中点是新子列表的尾部与旧子列表的头部相交的边界。
• 当InnoDB将一个块读入缓冲池时，它最初将其插入到中点（旧子列表的头部）。 可以读取块，因为它是用户指定的操作（如SQL查询）所需的，或者作为InnoDB自动执行的预读操作的一部分。
• 在旧的子列表中访问一个块使其变得“年轻”，将其移到缓冲池的头部（新子列表的头部）。 如果块因为需要而被读入，则第一次访问立即发生并且块变得年轻。 如果由于预读而将块读入，则第一次访问不会立即发生（并且可能在块被逐出之前根本不会发生）。
• 在数据库运行时，缓冲池中的块不会通过移动到列表的尾部而被“访问”。 随着其他街区变得全新，新老街区的街区都会变老。 旧的子列表中的块也会随着块的插入而中断。最终，一段时间未使用的块会到达旧子列表的尾部并被逐出。

默认情况下，查询读取的块会立即移动到新的子列表中，这意味着它们将长时间保留在缓冲池中。 表扫描（例如为mysqldump操作执行，或者不带WHERE子句的SELECT语句）可以将大量数据带入缓冲池并驱逐等量的旧数据，即使新数据再也不会使用 。同样地，由预读后台线程加载并且仅被访问一次的块移动到新列表的头部。这些情况可以将经常使用的块推送到旧的子列表，他们在那里成为被驱逐的对象

配置选项

几个InnoDB系统变量控制着缓冲池的大小，让你调整LRU算法：

• innodb_buffer_pool_size

指定缓冲池的大小。 如果您的缓冲池很小并且有足够的内存，那么通过减少查询访问InnoDB表所需的磁盘I / O量可以提高性能，从而提高性能。 从MySQL 5.7.5开始，innodb_buffer_pool_size选项是动态的，它允许您在不重新启动服务器的情况下配置缓冲池大小。

• innodb_buffer_pool_instances

将缓冲池划分为用户指定数量的单独区域，每个区域都有自己的LRU列表和相关数据结构，以减少并发内存读取和写入操作期间的争用。 只有将innodb_buffer_pool_size设置为1 GB或更大的大小时，此选项才会生效。指定的总大小在所有缓冲池中分配。 为了获得最佳效率，请指定innodb_buffer_pool_instances和innodb_buffer_pool_size的组合，以便每个缓冲池实例至少为1 GB。

• innodb_old_blocks_pct

指定InnoDB用于旧块子列表的缓冲池的近似百分比。 值的范围是5到95.默认值是37（即池的3/8）。

• innodb_old_blocks_time

指定插入到旧子列表中的块在移动到新子列表之前必须在其第一次访问后保留多长时间（以毫秒为单位）。 缺省值为0：无论插入访问发生后多久，Innodb已将所插入块的1/4页面从缓冲池中逐出，插入旧子表中的块将移动到新子表中。 如果该值大于0，块将保留在旧的子列表中，直到在第一次访问之后访问发生至少多少ms。 例如，值为1000会导致块在第一次访问后停留在旧子列表中1秒钟，然后才有资格移动到新子列表。

innodb_old_blocks_time可以在运行时设置，因此您可以在执行诸如表扫描和转储等操作时临时更改它：

SET GLOBAL innodb_old_blocks_time = 1000;

... perform queries that scan tables ...

SET GLOBAL innodb_old_blocks_time = 0;

如果您的意图是通过填充表格的内容来“缓冲”缓冲池，则此策略不适用。 例如，基准测试通常在服务器启动时执行表或索引扫描，因为在正常使用一段时间后，数据通常会在缓冲池中。 在这种情况下，请将innodb_old_blocks_time设置为0，至少在暖机阶段完成之前。

使用InnoDB标准监视器监视缓冲池

InnoDB标准监视器的输出包含与缓冲池LRU算法操作有关的BUFFER POOL AND MEMORY部分中的几个字段：

• Old database pages：缓冲池的旧子列表中的页面数。
• Pages made young, not young: 移到缓冲池（新子列表）头部的旧页面的数量以及保留在旧子列表中的新页面的数量。
• youngs/s non-youngs/s:

对旧页面的访问次数导致了他们年轻化。 这个度量与前一个项目的度量有两个不同的方面。 首先，它只涉及旧页面。 其次，它基于对页面的访问次数而不是页数。 （可以对给定页面进行多次访问，所有这些都被计算在内。）

• young-making rate: 导致块移动到缓冲池头部的命中。
• not:不会导致块移动到缓冲池的头部的命中（由于未满足延迟）。

young-making 和not rate通常不会达到整体缓冲池命中率。 旧的子列表中的块的命中使它们移动到新的子列表，但是命中新子列表中的块使其仅移动到列表的头部，只要它们距离头部一定距离。

监视器的上述信息可以帮助您进行LRU调整决策：

• 如果您在没有进行大型扫描时看到非常低的youngs/s值，则表示您可能需要缩短延迟时间，或者增加用于旧子列表的缓冲池的百分比。 增加百分比会使旧的子列表变大，因此该子列表中的块需要更长的时间才能移动到尾部并被驱逐。 这增加了他们将被再次访问并变得年轻的可能性。
• 如果您在进行大型表扫描（以及许多youngs/s）时看不到许多non-youngs/s，请将您的延迟值调大一些。

注意：

InnoDB Monitor输出中提供的每秒平均值基于当前时间与上次打印InnoDB Monitor输出之间的时间间隔。

INNODB_BUFFER_POOL_STATS表和InnoDB缓冲池服务器状态变量提供了SHOW ENGINE INNODB STATUS输出提供的许多相同的缓冲池信息。

8.10.2 MyISAM key Cache

为了最大限度地减少磁盘I / O，MyISAM存储引擎采用了许多数据库管理系统使用的策略。 它采用缓存机制将最常访问的表格块保留在内存中：

为了最大限度地减少磁盘I / O，MyISAM存储引擎采用了许多数据库管理系统使用的策略。 它采用缓存机制将最常访问的表格块保留在内存中：

• 对于索引块，维护一个称为key cache (or key buffer) 的特殊结构。 该结构包含大量使用最多索引块的块缓冲区。
• 对于数据块，MySQL不使用特殊的缓存。 相反，它依赖于本机操作系统文件系统缓存。

本节首先描述MyISAM密钥缓存的基本操作。 然后讨论提高关键缓存性能的功能，并使您能够更好地控制缓存操作：

• 多个会话可以同时访问高速缓存。
• 您可以设置多个键缓存并将表索引分配给特定的缓存。

要控制key缓存的大小，请使用key_buffer_size系统变量。 如果此变量设置为零，则不使用key缓存。 如果key_buffer_size值太小而无法分配最小数量的块缓冲区（8），则不会使用key 缓存。

当key缓存不可操作时，索引文件仅使用操作系统提供的本机文件系统缓存来访问。 （换句话说，使用与用于表数据块的策略相同的策略访问表索引块。）

索引块是对MyISAM索引文件的连续访问单元。 通常，索引块的大小等于索引B树的节点大小。 （索引在磁盘上使用B树数据结构表示，树底部的节点是叶节点，叶节点上方的节点是非叶节点。）

当必须访问来自任何表索引块的数据时，服务器首先检查它是否在密钥缓存的某个块缓冲区中可用。 如果是这样，服务器访问密钥缓存中的数据而不是磁盘上的数据。 也就是说，它从缓存读取数据或写入数据而不是读取或写入磁盘。 否则，服务器会选择一个包含不同表索引块（或多个块）的高速缓存块缓冲区，并通过所需表索引块的副本替换那里的数据。 只要新索引块在缓存中，就可以访问索引数据。

如果碰巧选择替换的块已被修改，则该块被认为是“脏的”。在这种情况下，在被替换之前，其内容被刷新到来自其的表索引。

通常服务器遵循一个LRU（最近最少使用）策略：当选择一个替换块时，它选择最近最少使用的索引块。 为了使选择更容易，密钥缓存模块将所有使用的块保存在按使用时间排序的特定列表（LRU链）中。 当一个块被访问时，它是最近使用的并放在列表的末尾。 当需要替换块时，列表开始处的块最近被最少使用，并且成为驱逐的第一个候选者。

InnoDB存储引擎也使用LRU算法来管理其缓冲池。

8.10.2.1 共享key cache 访问

线程可以同时访问键缓存缓冲区，但需满足以下条件：

• 未更新的缓冲区可以被多个会话访问。
• 正在更新的缓冲区会导致需要使用它的会话等待更新完成。
• 只要不会相互干扰（即只要它们需要不同的索引块，并因此导致不同的高速缓存块被替换），多个会话就可以启动导致高速缓存块替换的请求。

对key缓存的共享访问使服务器能够显着提高吞吐量。

8.10.2.2 多个 key caches

对key缓存的共享访问可以提高性能，但不会完全消除会话间的争用。 他们仍在争夺管理对key缓存缓冲区访问的控制结构。 为了进一步减少关键缓存访问争用，MySQL还提供了多个key缓存。 此功能使您可以将不同的表索引分配给不同的key缓存。

在有多个键缓存的情况下，服务器在处理给定MyISAM表的查询时必须知道使用哪个缓存。 默认情况下，所有MyISAM表索引缓存在默认键缓存中。 要将表索引分配给特定的键缓存，请使用CACHE INDEX语句（请参见第13.7.6.2节“CACHE INDEX语法”）。 例如，以下语句将表t1，t2和t3中的索引分配给名为hot_cache的键缓存：

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;

+---------+--------------------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+---------+--------------------+----------+----------+

| test.t1 | assign_to_keycache | status | OK |

| test.t2 | assign_to_keycache | status | OK |

| test.t3 | assign_to_keycache | status | OK |

+---------+--------------------+----------+----------+

可以通过使用SET GLOBAL参数设置语句或使用服务器启动选项设置其大小来创建CACHE INDEX语句中引用的键缓存。 例如：

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

要销毁密钥缓存，请将其大小设置为零:

SET GLOBAL keycache1.key_buffer_size=0;

您不能销毁默认密钥缓存。 任何尝试这样做都会被忽略：

mysql> SET GLOBAL key_buffer_size = 0;

mysql> SHOW VARIABLES LIKE 'key_buffer_size';

+-----------------+---------+

| Variable_name | Value |

-----------------+---------+

| key_buffer_size | 8384512 |

密钥缓存变量是具有名称和组件的结构化系统变量。 对于keycache1.key_buffer_size，keycache1是缓存变量名称，key_buffer_size是缓存组件。 有关用于引用结构化键缓存系统变量的语法的描述，请参见第5.1.6.1节“结构化系统变量”。

默认情况下，表索引分配给在服务器启动时创建的主（默认）键缓存。 当关键缓存被销毁时，分配给它的所有索引都被重新分配到默认键缓存。

对于繁忙的服务器，您可以使用包含三个关键缓存的策略：

• hot key chace 占用为所有关键缓存分配的空间的20％。 将其用于大量用于搜索但未更新的表格。
• cold key chace 占用为所有关键缓存分配的空间的20％。 将此缓存用于中等大小，密集修改的表格，如临时表格。
• warm key chace 占用关键缓存空间的60％。 将其用作默认键缓存，默认情况下用于所有其他表。

使用三个关键缓存是有益的原因之一是访问一个关键缓存结构不会阻止访问其他关键缓存。 访问分配给一个缓存的表的语句不会与访问分配给另一个缓存的表的语句竞争。 性能收益也出于其他原因：

• 热缓存仅用于检索查询，因此其内容不会被修改。 因此，无论何时索引块需要从磁盘中取出，被选择用于替换的高速缓存块的内容都不需要先刷新。
• 对于分配给热缓存的索引，如果没有查询需要索引扫描，则与索引B树的非叶节点相对应的索引块很可能保留在缓存中。
• 当更新的节点位于高速缓存中并且不需要首先从磁盘读入时，对临时表执行频率最高的更新操作执行得更快。 如果临时表的索引大小与冷关键字高速缓存大小相当，则更新的节点位于高速缓存中的概率非常高。

CACHE INDEX语句设置表和密钥缓存之间的关联，但每次服务器重新启动时关联都会丢失。 如果希望每次服务器启动时关联生效，实现此目的的一种方法是使用选项文件：包括配置密钥缓存的变量设置以及将包含CACHE INDEX语句的文件命名为 被执行。 例如：

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中的语句。 该文件应该包含每行一条SQL语句。 以下示例将每个表分配给hot_cache和cold_cache：

CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache

CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache

8.10.2.3 中点插入策略

默认情况下，密钥缓存管理系统使用简单的LRU策略来选择要被驱逐的密钥缓存块，但它也支持更复杂的方法，称为中点插入策略。

当使用中点插入策略时，LRU链分为两部分：hot子列表和 warm 子列表。 两部分之间的划分点并不固定，但密钥缓存管理系统需要注意warm 部分不是“太短”，并且总是至少包含密钥缓存块的key_cache_division_limit百分比。 key_cache_division_limit是结构化键缓存变量的一个组件，因此它的值是可以为每个缓存设置的参数。

当索引块从表中读取到密钥缓存中时，它被放置在 warm 子列表的末尾。 经过一定次数的访问（块访问）后，它被提升到hot子列表。 目前，促进块（3）所需的命中数对于所有索引块是相同的。

升级到hot子列表的块放置在列表的末尾。 该块然后在这个子列表中循环。 如果该块在子列表的开始处停留足够长的时间，则将其降级到warm 子列表。 这个时间由密钥缓存的key_cache_age_threshold组件的值确定。

该阈值规定，对于包含N个块的密钥缓存，在最后N *个key_cache_age_threshold / 100个命中中没有被访问的hot子列表的开始处的块将被移动到warm 子列表的开始处。 然后它成为驱逐的第一个候选对象，因为替换块始终是从warm 子列表的开头取得的。

中点插入策略使您能够将更多值的块始终保存在缓存中。 如果您更喜欢使用简单的LRU策略，请将key_cache_division_limit值设置为默认值100。

当执行需要索引扫描的查询时，中点插入策略有助于提高性能，从而有效地将高价值高级别B树节点对应的所有索引块从缓存中移出。 为避免这种情况，您必须使用中点插入策略，并将key_cache_division_limit设置为远小于100.然后，在索引扫描操作期间，hot子节点中的宝贵频繁命中节点也会保留。

8.10.2.4 index 预加载

如果密钥缓存中有足够的块来保存整个索引的块，或者至少包含与其非叶节点相对应的块，则在开始使用索引块之前预先加载索引块是有意义的。 通过预加载，您可以以最有效的方式将表索引块放入key缓存缓冲区：通过顺序读取磁盘上的索引块。

在没有预加载的情况下，块仍然按照查询的需要放置在密钥缓存中。 尽管这些块将保留在缓存中，因为它们都有足够的缓冲区，但它们是以随机顺序从磁盘中获取的，而不是按顺序获取的。

要将索引预加载到缓存中，请使用LOAD INDEX INTO CACHE语句。 例如，以下语句预加载表t1和t2索引的节点（索引块）：

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

+---------+--------------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+---------+--------------+----------+----------+

| test.t1 | preload_keys | status | OK |

| test.t2 | preload_keys | status | OK |

+---------+--------------+----------+----------+

IGNORE LEAVES修饰符仅导致索引的非叶节点预加载块。 因此，所显示的语句预先加载来自t1的所有索引块，但是仅从t2开始阻止非叶子节点。

如果使用CACHE INDEX语句将索引分配给键缓存，则预加载将索引块放入该缓存中。 否则，索引将加载到默认key缓存中。

8.10.2.5 key cache 块大小

可以使用key_cache_block_size变量为单个键缓存指定块缓冲区的大小。 这允许调整索引文件的I / O操作的性能。

当读取缓冲区的大小等于本机操作系统I / O缓冲区的大小时，可以实现I / O操作的最佳性能。 但是将关键节点的大小设置为等于I / O缓冲区的大小并不总能确保最佳的整体性能。 当读取大叶节点时，服务器拉入大量不必要的数据，有效地防止读取其他叶节点。

要控制MyISAM表的.MYI索引文件中块的大小，请在服务器启动时使用--myisam-block-size选项。

8.10.2.6 重构key缓存

通过更新其参数值，可以随时重新构建密钥缓存。 例如：

mysql> SET GLOBAL cold_cache.key_buffer_size=4*1024*1024;

如果您将key_buffer_size或key_cache_block_size键缓存组件的值分配给与组件当前值不同的值，则服务器将销毁缓存的旧结构并根据新值创建一个新结构。 如果高速缓存包含任何脏块，则在销毁并重新创建高速缓存之前，服务器将它们保存到磁盘。 如果更改其他key缓存参数，则不会发生重构。

重构密钥缓存时，服务器首先将任何脏缓冲区的内容刷新到磁盘。 之后，缓存内容变得不可用。 但是，重构不会阻止需要使用分配给缓存的索引的查询。 相反，服务器使用本机文件系统缓存直接访问表索引。 文件系统缓存的效率不如使用密钥缓存的效率，因此虽然查询执行，但预计会减速。 高速缓存重新构建后，它会再次用于缓存分配给它的索引，并且停止使用文件系统缓存。

8.10.3 mysql 查询缓存

注意：

查询缓存从MySQL 5.7.20开始已弃用，并在MySQL 8.0中删除。

查询缓存将SELECT语句的文本与发送给客户端的相应结果一起存储。 如果稍后收到相同的语句，服务器将从查询缓存中检索结果，而不是再次解析并执行语句。 查询缓存在会话中共享，因此可以发送由一个客户端生成的结果集，以响应另一个客户端发出的相同查询。

查询缓存可以用于您的表不经常更改并且服务器接收到许多相同查询的环境中。 这是许多基于数据库内容生成许多动态页面的Web服务器的典型情况。

查询缓存不会返回陈旧的数据。 当表被修改时，查询缓存中的任何相关条目都会被刷新。

注意：

查询缓存在有多个MySQL服务器更新相同MyISAM表的环境中不起作用。

分区表不支持查询缓存，并且在涉及分区表的查询中自动禁用查询缓存。 查询缓存无法启用此类查询。

查询缓存的一些性能数据如下所示。 这些结果是通过在具有2GB RAM和64MB查询缓存的Linux Alpha 2×500MHz系统上运行MySQL基准套件生成的。

• 如果您正在执行的所有查询都很简单（例如从一行的表中选择一行），但仍然存在差异，从而无法缓存查询，那么使查询缓存处于活动状态的开销为13％。 这可能被认为是最坏的情况。 在现实生活中，查询往往要复杂得多，所以开销通常要低得多。
• 在查询缓存中搜索单行表中的单个行比没有查询缓存快了238％。 这可以被认为接近于被缓存的查询所预期的最小加速比。

要在服务器启动时禁用查询缓存，请将query_cache_size系统变量设置为0.通过禁用查询缓存代码，没有明显的开销。

查询缓存提供了显着提高性能的潜力，但不要以为它会在所有情况下都这样做。 对于某些查询缓存配置或服务器工作负载，实际上可能会看到性能下降：

• 请谨慎对待，查询缓存设置过大，会增加维护缓存所需的开销，可能超出启用缓存的好处。 数十兆字节的大小通常是有益的。 数百兆字节的大小可能不是。
  
• 服务器工作负载对查询缓存效率有重大影响。 几乎完全由一组固定的SELECT语句组成的查询启用缓存更有利，其中频繁的INSERT语句会导致缓存中的结果不断失效。 在某些情况下，解决方法是使用SQL_NO_CACHE选项来防止对使用频繁修改的表的SELECT语句甚至输入缓存的结果。

要验证启用查询缓存是否有益，请在启用和禁用缓存的情况下测试MySQL服务器。 然后定期重新测试，因为查询缓存效率可能随着服务器工作负载的变化而改变.

8.10.3.1 查询缓存如何操作

在解析之前将传入查询与查询缓存中的查询进行比较，因此以下两个查询被查询缓存视为不同：

SELECT * FROM tbl_name

Select * from tbl_name

查询必须完全相同（字节的字节）才能被视为相同。 另外，由于其他原因，相同的查询字符串可能会被视为不同。 使用不同数据库，不同协议版本或不同默认字符集的查询被视为不同的查询，并分别进行缓存。

缓存不用于以下类型的查询：

• 查询是外部查询的子查询
• 查询在存储的函数，触发器或事件的主体内执行

在从查询缓存中获取查询结果之前，MySQL会检查用户是否对涉及的所有数据库和表具有SELECT特权。 如果不是这种情况，则不使用缓存的结果。

如果从查询缓存中返回查询结果，则服务器递增Qcache_hits状态变量，而不是Com_select。

如果表发生更改，则使用该表的所有缓存查询都将无效并从缓存中删除。 这包括使用映射到已更改表的MERGE表的查询。 一个表可以被许多类型的语句改变，例如INSERT，UPDATE，DELETE，TRUNCATE TABLE，ALTER TABLE，DROP TABLE或DROP DATABASE。

使用InnoDB表时，查询缓存也可以在事务中使用。

缓存视图上SELECT查询的结果。

查询缓存适用于SELECT SQL_CALC_FOUND_ROWS ...查询并存储由以下SELECT FOUND_ROWS（）查询返回的值。 即使前面的查询是从缓存中获取的，FOUND_ROWS（）也会返回正确的值，因为找到的行数也存储在缓存中。 SELECT FOUND_ROWS（）查询本身不能被缓存。

使用mysql_stmt_prepare（）和mysql_stmt_execute（）使用二进制协议发布的准备语句受缓存的限制。 与查询缓存中的语句比较是基于扩展后的语句文本？ 参数标记。 该语句仅与使用二进制协议执行的其他缓存语句进行比较。 也就是说，为了查询缓存目的，使用二进制协议发布的预准备语句与使用文本协议发布的预准备语句不同

如果查询包含下表中显示的任何函数，则不能缓存该查询。

AES_DECRYPT() (as of 5.7.4)

AES_ENCRYPT() (as of 5.7.4)

BENCHMARK()

CONNECTION_ID()

CONVERT_TZ()

CURDATE()

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIMESTAMP()

CURRENT_USER()

CURTIME()

DATABASE()

ENCRYPT() with one parameter

FOUND_ROWS()

GET_LOCK()

IS_FREE_LOCK()

IS_USED_LOCK()

LAST_INSERT_ID()

LOAD_FILE()

MASTER_POS_WAIT()

NOW()

PASSWORD()

RAND()

RANDOM_BYTES()

RELEASE_ALL_LOCKS()

RELEASE_LOCK()

SLEEP()

SYSDATE()

UNIX_TIMESTAMP() with no parameters

USER()

UUID()

UUID_SHORT()

查询也不会在这些条件下被缓存：

• 它是指用户定义的函数（UDF）或存储函数。
• 它引用用户变量或本地存储的程序变量。
• 它引用了mysql，INFORMATION_SCHEMA或performance_schema数据库中的表。
• 它指的是任何分区表。
• 它具有以下任何形式：

SELECT ... LOCK IN SHARE MODE

SELECT ... FOR UPDATE

SELECT ... INTO OUTFILE ...

SELECT ... INTO DUMPFILE ...

SELECT * FROM ... WHERE autoincrement_col IS NULL

最后一个表单不被缓存，因为它用作获取最后一个插入ID值的ODBC解决方法。

使用SERIALIZABLE隔离级别的事务中的语句也不能被缓存，因为它们使用LOCK IN SHARE MODE锁定。

• 其使用 TEMPORARY 表
• 其不使用任何表
• 其产生告警
• 用户具有任何涉及表的列级特权

8.10.3.2 查询缓存 SELECT 选项

在SELECT语句中可以指定两个与查询缓存相关的选项：

• SQL_CACHE

如果查询结果可缓存，并且query_cache_type系统变量的值为ON或DEMAND，则查询结果将被缓存。

• SQL_NO_CACHE

服务器不使用查询缓存。 它既不检查查询缓存以查看结果是否已被缓存，也不缓存查询结果。

SELECT SQL_CACHE id, name FROM customer;

SELECT SQL_NO_CACHE id, name FROM customer;

8.10.3.3 查询缓存配置

has_query_cache服务器系统变量指示查询缓存是否可用：

mysql> SHOW VARIABLES LIKE 'have_query_cache';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| have_query_cache | YES |

当使用标准的MySQL二进制文件时，即使禁用了查询缓存，该值也始终为YES。

其他几个系统变量控制查询缓存操作。 这些可以在启动mysqld时在选项文件或命令行中设置。 查询缓存系统变量都具有以query_cache_开头的名称。

要设置查询缓存的大小，请设置query_cache_size系统变量。 将它设置为0会禁用查询缓存，就像设置query_cache_type=0一样。 默认情况下，查询缓存被禁用。 这是通过使用默认大小1M来实现的，其中query_cache_type的默认值为0。

为了显着降低开销，如果您不使用查询缓存，请使用query_cache_type=0启动服务器。

注意：

使用Windows配置向导安装或配置MySQL时，将根据可用的不同配置类型自动为您配置query_cache_size的默认值。 使用Windows配置向导时，由于选定的配置，可能会启用查询缓存（即，设置为非零值）。 查询缓存也由query_cache_type变量的设置来控制。 在配置发生后，检查my.ini文件中设置的这些变量的值。

当您将query_cache_size设置为非零值时，请记住，查询缓存需要至少约40KB的大小来分配其结构。 （具体大小取决于系统架构。）如果设置的值太小，您将收到警告，如下例所示：

mysql> SET GLOBAL query_cache_size = 40000; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G *************************** 1. row ***************************

Level: Warning Code: 1282 Message: Query cache failed to set size 39936;

new query cache size is 0

mysql> SET GLOBAL query_cache_size = 41984; Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| query_cache_size | 41984 |

为使查询缓存实际上能够保存任何查询结果，其大小必须设置得更大：

mysql> SET GLOBAL query_cache_size = 1000000;

mysql> SHOW VARIABLES LIKE 'query_cache_size';

query_cache_size值与最接近的1024字节块对齐。 因此报告的值可能与您分配的值不同。

如果查询缓存大小大于0，那么query_cache_type变量会影响它的工作方式。 该变量可以设置为以下值：

• 值为0或OFF可防止缓存或检索缓存的结果。
• 除了以SELECT SQL_NO_CACHE开头的那些语句之外，值为1或ON启用缓存。
• 值2或DEMAND只会导致以SELECT SQL_CACHE开头的那些语句的缓存。

如果query_cache_size为0，则还应该将query_cache_type变量设置为0.在这种情况下，服务器根本不会获取查询缓存互斥锁，这意味着查询缓存无法在运行时启用，并且查询执行的开销也会降低。

设置GLOBAL query_cache_type值确定进行更改后连接的所有客户端的查询缓存行为。 各个客户端可以通过设置SESSION query_cache_type值来控制自己连接的缓存行为。 例如，客户端可以禁止使用查询缓存来处理它自己的查询，如下所示：

SET SESSION query_cache_type = OFF;

如果在服务器启动时（而不是在运行时用SET语句）设置query_cache_type，则只允许使用数值。

要控制可以缓存的单个查询结果的最大大小，请设置query_cache_limit系统变量。 默认值是1MB。

小心不要将缓存大小设置得太大。 由于线程需要在更新过程中锁定缓存，因此您可能会看到使用非常大的缓存锁定争用问题。

注意：

您可以使用SET语句在命令行或配置文件中使用--maximum-query_cache_size=32M选项来设置查询缓存在运行时可以指定的最大大小。

当查询被缓存时，其结果（发送到客户端的数据）在结果检索期间存储在查询缓存中。 因此，数据通常不会在一个大块中处理。 查询缓存根据需要分配块来存储这些数据，因此当一个块被填充时，将分配一个新的块。 由于内存分配操作成本高昂（按时间），因此查询缓存会为由query_cache_min_res_unit系统变量给出的最小大小分配块。 当查询被执行时，最后的结果块被修剪成实际的数据大小，以便释放未使用的内存。 根据服务器执行的查询类型的不同，调整query_cache_min_res_unit的值可能会有帮助：

--和oracle 的粒度有点类似

• query_cache_min_res_unit的默认值是4KB。 这在大多数情况下应该是足够的。
• 如果有很多查询的结果很少，那么默认的块大小可能会导致内存碎片化，如大量空闲块所示。 由于缺少内存，碎片可能会强制查询缓存修剪（删除）缓存中的查询。 在这种情况下，请减小query_cache_min_res_unit的值。 由于修剪而被删除的空闲块和查询的数量由Qcache_free_blocks和Qcache_lowmem_prunes状态变量的值给出。
• 如果大多数查询的结果很大（查看Qcache_total_blocks和Qcache_queries_in_cache状态变量），则可以通过增加query_cache_min_res_unit来提高性能。 但是，请注意不要太大（请参阅上一个项目）。

8.10.3.4 查询cache 状态和维护

要检查查询缓存是否存在于MySQL服务器中，请使用以下语句：

mysql> SHOW VARIABLES LIKE 'have_query_cache';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| have_query_cache | YES |

您可以对查询缓存进行碎片整理，以更好地利用FLUSH QUERY CACHE语句的内存。 该语句不会从缓存中删除任何查询。

RESET QUERY CACHE语句从查询缓存中删除所有查询结果。 FLUSH TABLES语句也执行此操作。

要监视查询缓存的性能，请使用SHOW STATUS查看缓存状态变量：

SHOW STATUS LIKE 'Qcache%';

SELECT查询的总数由以下公式给出：

Com_select

+ Qcache_hits

+ queries with errors found by parser

Com_select值由以下公式给出：

Qcache_inserts

+ Qcache_not_cached

+ queries with errors found during the column-privileges check

查询缓存使用可变长度块，因此Qcache_total_blocks和Qcache_free_blocks可能指示查询缓存内存碎片。 FLUSH QUERY CACHE之后，只剩下一个空闲块。

每个缓存查询至少需要两个块（一个用于查询文本，一个用于查询结果）。 另外，查询使用的每个表都需要一个块。 但是，如果两个或多个查询使用同一个表，则只需分配一个表块。

Qcache_lowmem_prunes状态变量提供的信息可以帮助您调整查询缓存大小。 它计算从缓存中删除的查询数量，以释放内存以缓存新查询。 查询缓存使用最近最少使用（LRU）策略来决定从缓存中删除哪些查询。

8.10.4 准备好的语句和存储程序的缓存

对于某个客户端可能在会话期间多次执行的某些语句，服务器将该语句转换为内部结构并缓存该结构以在执行期间使用。 缓存使服务器能够更有效地执行，因为它避免了在会话期间再次需要时重新转换语句的开销。 对这些语句进行转换和缓存

• 准备好的语句，既包括在SQL级别处理的语句（使用PREPARE语句），也包括使用二进制客户端/服务器协议（使用mysql_stmt_prepare（）C API函数）处理的语句。 max_prepared_stmt_count系统变量控制服务器缓存的语句总数。 （所有会话中已准备好的语句数量的总和。）
• 存储的程序（存储过程和函数，触发器和事件）。 在这种情况下，服务器转换并缓存整个程序体。 stored_program_cache系统变量指示服务器在每个会话中缓存的已存储程序的大概数量。

服务器为每个会话维护准备好的语句和存储的程序的高速缓存。 其他会话无法访问为一个会话缓存的语句。 当会话结束时，服务器会放弃为其缓存的任何语句。

当服务器使用缓存的内部语句结构时，必须注意结构不会过时。 对于语句使用的对象，可能会发生元数据更改，导致当前对象定义与定义之间的不匹配，如内部语句结构中所示。 DDL语句（如创建，删除，更改，重命名或截断表）或分析，优化或修复表的DDL语句会发生元数据更改。 表内容的更改（例如，使用INSERT或UPDATE）不会更改元数据，SELECT语句也不会 。

这是一个问题的例子。 假设一个客户准备这个语句：

PREPARE s1 FROM 'SELECT * FROM t1';

SELECT *在内部结构中展开为表中列的列表。 如果使用ALTER TABLE修改表中的一组列，则准备的语句将过期。 如果服务器在下次客户端执行s1时未检测到此更改，则准备好的语句将返回不正确的结果。

为避免由准备语句引用的表或视图的元数据更改导致的问题，服务器检测到这些更改并在下一次执行时自动重新声明该语句。 也就是说，服务器会重新解析语句并重建内部结构。 重新分析也发生在从表定义缓存刷新引用的表或视图后，也隐式地为缓存中的新条目腾出空间，或者由FLUSH TABLES显式刷新。

同样的，如果存储程序使用的对象发生更改，则服务器会重新计算程序中受影响的语句。

服务器还检测表达式中对象的元数据更改。 这些可能用于特定于存储程序的语句，如DECLARE CURSOR或流控制语句，如IF，CASE和RETURN。

为避免重新分析整个存储的程序，服务器仅在需要时才在程序中重新分析受影响的语句或表达式。 例子：

• 假设表或视图的元数据已更改。 对访问表或视图的程序中的SELECT *进行重新分析，但对于不访问表或视图的SELECT *则不进行。
• 当语句受到影响时，服务器只在可能的情况下部分解析它。 考虑这个CASE声明：

CASE case_expr

WHEN when_expr1 ...

WHEN when_expr2 ...

WHEN when_expr3 ...

...

END CASE

如果元数据更改仅在when_expr3时影响，则表达式将被重新解析。 case_expr和其他WHEN表达式不重新解析。

重新分析使用原始转换为内部表单的默认数据库和SQL模式。

服务器尝试重新分析最多三次。 如果所有尝试失败都会发生错误。

重新分析是自动的，但在发生时会减少准备好的语句和存储的程序性能。

对于准备好的语句，Com_stmt_reprepare状态变量跟踪重新表示的数量。