面试题：MariaDB查询缓存失效机制及优化策略

Question

详细说明MariaDB查询缓存的失效机制，当缓存频繁失效影响性能时，你会采取哪些优化策略来改善这种情况？

Answer 1

MariaDB查询缓存的失效机制

1. 表数据变化：当表中的数据发生任何改变，包括插入（INSERT）、更新（UPDATE）、删除（DELETE）操作时，与该表相关的所有查询缓存都会失效。例如，如果对users表执行UPDATE users SET age = age + 1 WHERE id = 1，那么所有涉及users表的查询缓存都会被清除。
2. 结构变化：若表的结构发生改变，如添加列（ALTER TABLE ADD COLUMN）、修改列（ALTER TABLE MODIFY COLUMN）、删除列（ALTER TABLE DROP COLUMN）等操作，相关查询缓存也会失效。比如执行ALTER TABLE products ADD COLUMN new_feature VARCHAR(255)，则与products表有关的查询缓存将全部失效。
3. 查询语句变化：即使查询的逻辑相同，但如果查询语句的写法略有不同，缓存也不会命中。例如，SELECT * FROM orders WHERE order_date > '2023 - 01 - 01'和SELECT * FROM orders WHERE order_date >='2023 - 01 - 02'是不同的查询，缓存不会共享；同样，SELECT column1, column2 FROM table和SELECT column2, column1 FROM table也被视为不同查询，缓存不通用。
4. 系统变量变化：某些影响查询执行的系统变量发生改变时，相关查询缓存会失效。例如，sql_mode系统变量的修改，若从ONLY_FULL_GROUP_BY模式切换到其他模式，可能导致部分依赖特定模式下执行计划的查询缓存失效。

优化策略

1. 合理配置查询缓存
   • 调整缓存大小：通过query_cache_type和query_cache_size系统变量来配置。如果缓存频繁失效，可适当增加query_cache_size，给缓存分配更多内存空间，减少因空间不足导致的缓存无法存储新查询结果的情况。例如，在my.cnf文件中设置query_cache_size = 64M（根据服务器内存实际情况调整）。同时，根据查询模式，将query_cache_type设置为合适的值，0表示禁用查询缓存，1表示开启查询缓存（只要查询可以缓存就缓存），2表示按需缓存（只有查询语句中包含SQL_CACHE关键字才缓存）。
   • 设置缓存粒度：可以通过query_cache_min_res_unit变量来设置缓存分配的最小内存块大小。如果查询结果普遍较小，可适当减小该值，以减少内存浪费；若查询结果较大，则可适当增大。比如设置query_cache_min_res_unit = 4096字节，根据实际查询结果大小优化内存使用。
2. 优化查询语句
   • 避免不必要的查询变化：编写查询时尽量保持一致性，避免因细微差异导致缓存无法命中。例如，统一使用相同的函数调用风格、相同的列顺序等。对于可缓存的查询，尽量复用已有的查询语句结构。
   • 使用存储过程：将复杂的查询逻辑封装在存储过程中，这样每次调用存储过程时，查询语句是固定的，有利于查询缓存命中。同时，存储过程还可以进行预编译，提高执行效率。例如，创建一个存储过程GetUserInfo，内部包含对users表的复杂查询逻辑，应用程序通过调用该存储过程获取数据，保证查询语句的一致性。
3. 表结构和操作优化
   • 减少频繁更新的表：如果某些表的数据更新非常频繁，考虑将其与相对静态的数据表分离。对于频繁更新的表，可采用其他缓存机制（如应用层缓存），避免因这些表的变化导致大量查询缓存失效。例如，将用户的基本信息（相对静态）和用户的操作日志（频繁更新）分别存储在不同的表中。
   • 批量操作：对于插入、更新和删除操作，尽量采用批量方式执行。这样相比单个操作，可以减少表结构变化和数据变化触发查询缓存失效的次数。例如，使用INSERT INTO table (column1, column2) VALUES (value1_1, value1_2), (value2_1, value2_2)一次性插入多条记录。
4. 缓存分层
   • 应用层缓存：在应用程序层面添加缓存，如使用Memcached或Redis。对于频繁查询且数据相对稳定的场景，先从应用层缓存获取数据。只有在应用层缓存未命中时，才查询数据库。这样可以减轻数据库查询缓存的压力，同时减少因数据库查询缓存失效对应用性能的影响。例如，在Web应用中，使用Redis缓存热门文章列表，当用户请求文章列表时，先从Redis中获取，若不存在则查询数据库并将结果存入Redis。
   • 分布式缓存：对于大型应用，可采用分布式缓存方案，如Couchbase等。分布式缓存可以提供更大的缓存容量和更好的扩展性，将查询结果分散存储在多个节点上，降低单个节点的压力，并且可以通过合理的缓存策略减少因局部数据变化导致的缓存失效影响范围。