MariaDB 10.4.17配置16M查询缓存 慢日志所有查询QC_hit均为No原因咨询

现象 #

慢查询日志中所有记录的QC_Hit字段均为No，已为查询缓存分配16M内存，数据库版本为10.4.17-MariaDB，慢查询样例如下：

Thread_id: 478  Schema: schema_one  QC_hit: No
Query_time: 3.725316  Lock_time: 0.000030  Rows_sent: 10  Rows_examined: 442611
Rows_affected: 0  Bytes_sent: 2703

排查优先级：先确认配置问题，再核对业务逻辑 #

配置类问题（占这类故障的70%以上） #

分配query_cache_size内存不代表查询缓存功能已经启用，逐个核对以下参数：

• 全局开关校验：执行SHOW VARIABLES LIKE 'query_cache_type';查看状态
  • 值为0/OFF：查询缓存完全关闭，哪怕分配了内存也不会工作，这是最常见的漏配项
  • 值为2/DEMAND：只有显式带SQL_CACHE hint的查询才会进入缓存，普通查询直接跳过
  • 值为1/ON：才是默认对所有符合条件的查询开启缓存
• 功能可用性校验：执行SHOW VARIABLES LIKE 'have_query_cache';，如果返回值为NO，说明当前10.4版本的MariaDB在编译时就移除了查询缓存模块，任何参数配置都不会生效。注意MariaDB从10.4版本开始已经将查询缓存标记为废弃特性，部分官方发行版默认编译时不带该模块。
• 内存有效性校验：执行SHOW VARIABLES LIKE 'query_cache_size';确认返回值为16777216（即16M）。注意16M的缓存空间本身偏小，配合默认4K的query_cache_min_res_unit块大小，很容易产生大量内存碎片，当查询结果需要的连续内存块大于剩余可用连续空间时，结果不会被写入缓存，自然无法命中。
• 限制参数校验：
  • 核对query_cache_limit值：如果单条查询结果集大小超过该阈值，不会被缓存，默认值为1M，当前样例中查询返回字节数仅2703，远低于默认值，但要确认是否被人为改小
  • 核对query_cache_wlock_invalidate值：如果设为ON，当表被加写锁时，所有针对该表的查询会直接跳过缓存读底层表，会大幅提升不命中概率，默认值为OFF

业务/查询逻辑类问题 #

如果配置全部校验通过，以下场景会导致查询无法命中缓存：

• 字节级匹配规则不满足：查询缓存以SQL语句的原始字节流作为匹配key，语句大小写、空格、注释、客户端字符集、协议版本任何一点不一致，都不会命中。比如select * from user和SELECT * FROM user会被识别为两个完全不同的查询。
• 查询包含非确定性函数：语句中用到NOW()、RAND()、UUID()、CURRENT_USER()这类每次执行返回值可能变化的函数时，查询结果不会被缓存。
• 表级缓存频繁失效：只要查询涉及的表上存在未提交的写操作（INSERT/UPDATE/DELETE/DDL等），该表关联的所有查询缓存条目会被立即标记失效，写操作提交后相关缓存会被全部清空。如果业务写频率较高，缓存条目刚写入就被清理，会出现全量不命中的情况。
• 查询显式跳过缓存：语句中带SQL_NO_CACHE hint时，会主动不走查询缓存。
• 查询涉及临时表、系统库表、权限表，或者是存储过程、触发器、定时任务内部执行的语句，默认不会进入查询缓存。

快速验证方法 #

连续执行以下两条语句两次，观察第二次执行时的状态值：

SELECT SQL_CACHE 1;
SHOW STATUS LIKE 'Qcache_hits';

如果第二次执行时Qcache_hits的计数值没有上涨，说明查询缓存功能本身未生效，属于配置问题；如果计数值正常上涨，说明功能正常，不命中是业务SQL本身不符合缓存规则导致。

补充说明：查询缓存本身在写多读少的高并发场景下存在严重的锁竞争问题，每次表写操作都要遍历清理对应表的所有缓存条目，性能开销远大于缓存带来的收益，MariaDB 10.4之后的版本已经正式移除了该特性，生产环境更建议关闭查询缓存，改用应用层本地缓存或者独立缓存服务做查询结果加速。

内容的提问来源于stack exchange，提问作者Bannarisamy Shanmugam