面试题：MySQL基本配置完成后，如何检查查询缓存的性能并进行调整？

Question

假设MySQL完成基本配置，阐述查询缓存性能检查的主要指标以及对应的调整方法，包括如何查看查询缓存的命中率，以及当命中率不理想时，应该从哪些方面进行优化？

Answer 1

查询缓存性能检查主要指标及调整方法

1. 查询缓存命中率：
   • 查看方法：可以通过 SHOW STATUS LIKE 'Qcache%'; 命令查看相关状态变量。其中，Qcache_hits 表示查询缓存命中次数，Qcache_inserts 表示向查询缓存中插入的查询次数。命中率计算公式为：(Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%。
   • 优化方法：
     • 查询语句优化：确保查询语句尽可能精确，避免模糊查询（如使用 LIKE '%keyword%'），因为这种查询通常无法命中缓存。尽量使用索引，提高查询效率，使相同的查询更容易命中缓存。
     • 数据变动频率：如果数据库中数据更新频繁，查询缓存命中率可能较低。可以考虑减少不必要的数据更新，或者在更新数据后，合理清理查询缓存（通过 RESET QUERY CACHE 命令），避免缓存中存在过期数据。
     • 查询缓存配置：调整 query_cache_type 系统变量，其有三个取值：0（OFF，不使用查询缓存）、1（ON，开启查询缓存）、2（DEMAND，只有在查询语句中显式使用 SQL_CACHE 提示时才使用查询缓存）。根据业务场景合理设置该参数，若查询变化多样且数据更新频繁，可考虑设置为 0 或 2。同时，通过 query_cache_limit 设置单个查询结果能够缓存的最大大小，通过 query_cache_size 设置查询缓存的总大小，合理调整这两个参数以提高命中率。
2. 查询缓存内存使用情况：
   • 查看方法：同样通过 SHOW STATUS LIKE 'Qcache%'; 查看，Qcache_free_memory 表示查询缓存当前空闲的内存大小，Qcache_total_blocks 表示查询缓存中总的块数，Qcache_free_blocks 表示查询缓存中空闲的块数。
   • 优化方法：
     • 调整缓存大小：若 Qcache_free_memory 过小，可能需要增加 query_cache_size；若 Qcache_free_blocks 过多且碎片化严重，可以通过 FLUSH QUERY CACHE 命令整理缓存内存，减少碎片化。
3. 查询缓存失效频率：
   • 查看方法：虽然没有直接的状态变量显示失效频率，但可以结合 Qcache_inserts 和数据更新操作的频率来推断。如果数据更新频繁，且查询缓存命中率低，很可能查询缓存失效频繁。
   • 优化方法：
     • 分区缓存：对于不同类型的查询，可以考虑逻辑上进行分区缓存，比如按表或业务模块进行分区。这样在某部分数据更新时，只失效对应分区的缓存，而不是整个查询缓存。
     • 缓存粒度控制：尽量将缓存粒度设置得更细，例如只缓存部分结果集，而不是整个查询结果，这样在数据部分变动时，缓存的失效范围更小。