解锁 MySQL 性能：掌握查询缓存优化

什么是查询缓存？

查询缓存是 MySQL 中的一项功能，旨在通过缓存 SELECT 查询的结果来提高数据库性能。当再次运行之前执行过的查询时，MySQL 可以快速从该缓存中提取结果，而不是对数据库重新执行它。这不仅加快了数据检索速度，还减少了数据库的负载，使其对于具有一致参数的频繁运行的查询非常高效。

查询缓存的工作原理

每当发出查询时，MySQL 首先查看查询缓存以检查之前是否存储了相同查询的结果。如果存在匹配，MySQL 会绕过通常的查询执行过程并直接提供缓存的结果。这要快得多，因为它避免了查询处理和磁盘访问的耗时步骤，而是利用内存访问的速度。

我们现在转向关键的查询缓存变量 – query_cache_type、query_cache_size、query_cache_limit 和 query_cache_min_res_unit – 以及它们对 MySQL 性能的影响。

1. 查询缓存类型

MySQL中的query_cache_type变量控制查询缓存是启用、禁用还是设置为仅按需操作。

query_cache_type 用法

查询缓存存储 SELECT 语句的文本以及发送到客户端的相应结果。如果稍后收到相同的语句，服务器会从缓存中检索结果，而不是再次解析并执行该语句。启用查询缓存可以通过从内存中提供查询而不是重新执行查询来显着减少获取频繁运行的查询结果所需的时间。

将 MySQL 中的 query_cache_type 变量设置为不同的值决定查询缓存的行为方式：

• 0（关闭） – 禁用查询缓存，尽管仍分配 query_cache_size 字节的缓冲区。

• 1 (ON) – 为所有 SELECT 查询启用查询缓存，除非在查询中指定 SQL_NO_CACHE。

• 2 (DEMAND) – 仅对显式使用 SQL CACHE 子句的查询启用查询缓存。

query_cache_type 配置

Query_cache_type 可以在服务器运行时离线或在线配置。可能首选在线配置，以便进行测试。当服务器重新启动时，query_cache_type 将恢复。

命令行配置：
mysqld>设置全局query_cache_type = XX

将 XX 替换为适合您的数据库需求的值。验证变量是否已更改：

mysqld>显示全局变量，如“query_cache_type”

配置文件：
[mysqld]
查询缓存类型 = XX

将 XX 替换为适合您的数据库需求的值。重新启动 MySQL 服务器。

query_cache_type 注意事项

根据一般建议，对于数据变化不频繁但读取频繁的环境，应该将query_cache_type设置为1（ON）。在高度动态的环境中设置为 0（关闭），在这种环境中，维护缓存的开销可能会超过好处。

不加区别地启用查询缓存可能并不总是能带来性能优势，在某些情况下甚至会降低性能。配置query_cache_type时请考虑以下因素：

• 查询缓存的大小 – 更大的缓存可以容纳更多的查询结果，但需要更多的内存。

• 查询模式 – 经常更改结果或大型结果集的查询可能无法从缓存中受益。

• 缓存失效 – 对缓存表进行更新、插入或删除会使相应的缓存条目失效，从而导致缓存流失。

• 并发 – 由于争用问题，查询缓存不适合高并发工作负载。

• MySQL 版本 – 查询缓存功能已在 MySQL 5.7 中弃用，并在 MySQL 8.0 中删除，因为它有限制并且可能会导致多线程环境中的争用。

2.查询缓存大小

指定分配用于存储缓存查询结果的内存量。这是决定一次可以缓存多少结果的主要因素。

query_cache_size 用法

query_cache_size 变量决定为查询缓存分配的内存量。应根据工作负载的性质和可用内存资源调整该值：

• 小结果集 – 如果您的应用程序经常执行返回小结果集的查询，则较大的查询缓存大小可能会有所帮助。这允许在缓存中存储更多查询，从而减少查询执行的需要。

• 频繁相同查询 – 在重复执行相同查询的场景中，增加 query_cache_size 可以通过缓存这些查询及其结果来提高性能。

• 查询缓存命中率 – 监控查询缓存命中率可以深入了解缓存的有效性。如果命中率较低，增加query_cache_size可能有助于提高缓存效率。

query_cache_size配置

Query_cache_size 可以在服务器运行时离线或在线配置。可能首选在线配置，以便进行测试。当服务器重新启动时，query_cache_size 将恢复。

命令行配置：
mysqld>设置全局query_cache_size = XX

将 XX 替换为适合您的数据库需求的值。验证变量是否已更改：

mysqld>显示全局变量，如“query_cache_size”

配置文件：
_[mysqld]
query_cache_size _= XX

将 XX 替换为适合您的数据库需求的值。重新启动 MySQL 服务器。

query_cache_size 注意事项

query_cache_size 应根据可用内存和工作负载的性质进行设置。设置太大会导致内存耗尽，设置太小可能会限制其有效性。

监控缓存的利用率（命中与插入）将指导适当的大小调整。从中等大小开始，例如 64MB 到 128MB，然后根据性能和可用系统内存进行调整。

配置query_cache_size时请考虑以下因素：

• 查询模式 – 经常更改结果或大型结果集的查询可能无法从缓存中受益。

• 缓存失效 – 对缓存表进行更新、插入或删除会使相应的缓存条目失效，从而导致缓存流失。

• 并发 – 由于争用问题，查询缓存不适合高并发工作负载。

• MySQL 版本 – 由于多线程环境中的限制和争用，查询缓存功能已在 MySQL 5.7 中弃用，并在 MySQL 8.0 中删除..

3.查询缓存限制

此变量设置可以缓存的单个查询结果的最大大小。它可以防止大型查询消耗不成比例的缓存空间。

query_cache_limit 用法

当查询结果超过query_cache_limit时，结果不会被缓存。这可以防止过大或资源密集型查询用可能不会经常重用的结果填充缓存。通过为query_cache_limit设置适当的值，可以确保只缓存更小、更常用的查询结果，从而优化内存的使用。

query_cache_limit 配置

Query_cache_limit 可以在服务器运行时离线或在线配置。可能首选在线配置，以便进行测试。当服务器重新启动时，query_cache_limit 将恢复。

命令行配置：
mysqld>设置全局query_cache_limit = XX

将 XX 替换为适合您的数据库需求的值。验证变量是否已更改：

mysqld>显示全局变量，如“query_cache_limit”

配置文件：
[mysqld]
query_cache_limit = XX

将 XX 替换为适合您的数据库需求的值。重新启动 MySQL 服务器。

query_cache_limit 注意事项

通常建议将 query_cache_limit 设置为 1MB 到 4MB 之间，具体取决于查询的性质和可用缓存大小。需要注意的是，将query_cache_limit设置得太低可能会导致有用的查询结果被排除在缓存之外，从而降低查询缓存的有效性。

4.query_cache_min_res_unit

MySQL 中的 query_cache_min_res_unit 变量确定查询缓存分配的块的最小大小（以字节为单位）。此设置通过控制缓存结果的粒度来影响查询缓存的效率。

query_cache_min_res_unit 用法

当查询结果存储在查询缓存中时，会占用一定的内存。 query_cache_min_res_unit 变量定义为这些缓存结果分配的内存块的最小大小。如果查询结果小于这个值，它仍然会占用query_cache_min_res_unit定义的最小大小。

query_cache_min_res_unit 配置

Query_cache_min_res_unit 可以在服务器运行时离线或在线配置。可能首选在线配置，以便进行测试。当服务器重新启动时，query_cache_min_res_unit 将恢复。

命令行配置：
mysqld>设置全局 query_cache_min_res_unit = XX

将

XX 替换为适合您的数据库需求的值。验证变量是否已更改：

mysqld>显示全局变量，如“query_cache_min_res_unit” _

配置文件： _[mysqld]
query_cache_min_res_unit
= XX

将

XX 替换为适合您的数据库需求的值。重新启动 MySQL 服务器。

query_cache_min_res_unit 注意事项

配置 query_cache_min_res_unit 涉及将变量设置为合适的值，以平衡内存消耗和缓存效率。应根据工作负载中查询结果的平均大小来选择该值。

• 较小的值可能会导致更有效的内存使用，但可能会因更多缓存条目而增加开销。

• 相反，较大的值可能会减少缓存条目的数量，但可能会导致较小查询结果的内存浪费。

分析您的工作负载以确定查询结果的平均大小。根据此分析调整query_cache_min_res_unit的值，以达到内存消耗和缓存效率之间的平衡。对于大多数设置，该大小将介于 16MB 和 64MB 之间。

只需 4 个步骤即可启用和调整查询缓存

从 MySQL 5.7.20 开始，查询缓存已被弃用，并在 MySQL 8.0 中完全删除。如果您的 MySQL 版本仍然能够使用 query_cache，则必须启用它，因为默认情况下它是禁用的。要在 MySQL、MariaDB 或 Percona 中启用和配置查询缓存，您通常需要访问服务器的 my.cnf 或 my.ini 文件。这是一个逐步的方法：

1.启用查询缓存 – 将 q​​uery_cache_type 设置为 1 或 2。将 query_cache_type 或 query_cache_size 设置为零将始终禁用缓存。对于选择性缓存（建议大多数用例），您可以使用：

query_cache_type = 1

设置缓存大小 – 定义query_cache_size。起始点可能是总可用内存的 10-20%，但这需要根据您的工作负载进行调整：

query_cache_size = 100M

3.定义结果大小限制 – 配置 query_cache_limit 来控制存储结果的大小。这可能从几兆字节开始，具体取决于您的典型查询大小：

query_cache_limit = 2M

4.调整最小结果单位 – 根据您的需求修改query_cache_min_res_unit。将其减少到默认值以下可以帮助更有效地利用缓存空间，特别是如果您预计有很多小查询：

query_cache_min_res_unit = 512

通过运行状况检查监控查询缓存

QCache Fragmentation 是 MySQL 中 Query Cache 性能的一个关键指标。此功能旨在存储 SELECT 查询的结果，以便可以快速满足重复的请求，而无需重新运行查询，从而提高性能。然而，随着时间的推移，查询缓存可能会变得碎片化，从而导致效率降低。

计算QCache碎片

查看我们全面的运行状况检查文档，其中包含有关如何计算 QCache 碎片的信息和分步说明。

计算完 QCache Fragmentation 和 QcacheDeleteRate 后，您需要解释结果。理想情况下，QCache Fragmentation 应小于 10，QcacheDeleteRate 应小于 20。

建议采取的行动

如果QCache Fragmentation很高，您可能需要调整Query Cache的大小以减少碎片。如果 QcacheDeleteRate 较高，您可能需要增加查询缓存的大小或优化查询以减少 INSERT 的数量。

轻松优化您的查询缓存

调整 MySQL 查询缓存涉及调整多项设置以优化数据库性能，从管理内存使用到减少查询时间。虽然这里讨论的变量形成了良好的基础，但有效的管理需要根据实际系统负载和性能进行持续监控和更新。

为了简化此过程，请考虑使用像 Releem 这样强大的管理工具来自动执行这些调整。这样的工具可以持续监控您的系统性能并实时动态更新query_cache设置。

这让您有时间专注于更广泛的目标，同时 Releem 处理复杂的查询缓存优化。

以上是解锁 MySQL 性能：掌握查询缓存优化的详细内容。更多信息请关注PHP中文网其他相关文章！