面试题：MySQL复杂查询场景下缓存表的优化设计

Question

在一个社交网络数据库中，经常需要执行多表联合查询，例如查询用户及其好友发布的所有动态，同时按照点赞数进行排序。请详细说明如何设计缓存表来优化此类复杂查询，要考虑缓存表的构建、维护以及与原表的数据一致性问题。

Answer 1

缓存表构建

1. 确定缓存表结构：
   • 缓存表应包含查询结果所需的关键信息，如用户ID、好友ID、动态ID、动态内容、点赞数等。例如，假设用户表为users，好友关系表为friendships，动态表为posts，点赞表为likes。缓存表cache_user_friend_posts可以设计如下结构：

   CREATE TABLE cache_user_friend_posts (
       id INT AUTO_INCREMENT PRIMARY KEY,
       user_id INT,
       friend_id INT,
       post_id INT,
       post_content TEXT,
       like_count INT,
       INDEX (user_id),
       INDEX (friend_id),
       INDEX (post_id),
       INDEX (like_count)
   );
   

   • 这里添加多个索引以加速基于不同条件的查询，特别是like_count索引用于按点赞数排序。
2. 初始化缓存表：
   • 执行一次初始的多表联合查询，并将结果插入到缓存表中。例如：

   INSERT INTO cache_user_friend_posts (user_id, friend_id, post_id, post_content, like_count)
   SELECT 
       u.id AS user_id,
       f.friend_id,
       p.id AS post_id,
       p.content AS post_content,
       COUNT(l.post_id) AS like_count
   FROM 
       users u
   JOIN 
       friendships f ON u.id = f.user_id
   JOIN 
       posts p ON (u.id = p.user_id OR f.friend_id = p.user_id)
   LEFT JOIN 
       likes l ON p.id = l.post_id
   GROUP BY 
       u.id, f.friend_id, p.id, p.content
   ORDER BY 
       like_count DESC;
   

缓存表维护

1. 增量更新：
   • 新增动态：当有新动态发布时，先判断发布动态的用户是否在缓存表中有相关记录。如果有，直接插入新动态记录到缓存表。例如：

   -- 假设新动态插入到posts表，且发布者为user_id
   INSERT INTO cache_user_friend_posts (user_id, friend_id, post_id, post_content, like_count)
   SELECT 
       u.id AS user_id,
       f.friend_id,
       p.id AS post_id,
       p.content AS post_content,
       0 AS like_count -- 初始点赞数为0
   FROM 
       users u
   JOIN 
       friendships f ON u.id = f.user_id
   JOIN 
       (SELECT * FROM posts WHERE id = NEW.id) p ON (u.id = p.user_id OR f.friend_id = p.user_id);
   

   • 新增点赞：当有新的点赞记录插入到likes表时，更新缓存表中对应的点赞数。例如：

   UPDATE cache_user_friend_posts
   SET like_count = like_count + 1
   WHERE post_id = NEW.post_id;
   

   • 删除点赞：类似地，当删除点赞记录时，更新缓存表中的点赞数：

   UPDATE cache_user_friend_posts
   SET like_count = like_count - 1
   WHERE post_id = OLD.post_id;
   

2. 定期全量更新：
   • 虽然增量更新能处理大部分日常变化，但随着时间推移，数据可能会因为某些异常情况（如缓存表更新失败等）而不一致。因此，定期（如每天凌晨流量低谷时）执行一次全量更新缓存表的操作。

   -- 先清空缓存表
   TRUNCATE TABLE cache_user_friend_posts;
   -- 重新插入数据
   INSERT INTO cache_user_friend_posts (user_id, friend_id, post_id, post_content, like_count)
   SELECT 
       u.id AS user_id,
       f.friend_id,
       p.id AS post_id,
       p.content AS post_content,
       COUNT(l.post_id) AS like_count
   FROM 
       users u
   JOIN 
       friendships f ON u.id = f.user_id
   JOIN 
       posts p ON (u.id = p.user_id OR f.friend_id = p.user_id)
   LEFT JOIN 
       likes l ON p.id = l.post_id
   GROUP BY 
       u.id, f.friend_id, p.id, p.content
   ORDER BY 
       like_count DESC;
   

数据一致性问题

1. 使用事务：在进行缓存表更新操作时，使用数据库事务确保原表和缓存表操作的原子性。例如，在新增动态时，将插入原posts表和插入缓存表的操作放在一个事务中：

   START TRANSACTION;
   INSERT INTO posts (user_id, content) VALUES (?,?);
   SET @new_post_id = LAST_INSERT_ID();
   INSERT INTO cache_user_friend_posts (user_id, friend_id, post_id, post_content, like_count)
   SELECT 
       u.id AS user_id,
       f.friend_id,
       @new_post_id AS post_id,
       p.content AS post_content,
       0 AS like_count -- 初始点赞数为0
   FROM 
       users u
   JOIN 
       friendships f ON u.id = f.user_id
   JOIN 
       (SELECT * FROM posts WHERE id = @new_post_id) p ON (u.id = p.user_id OR f.friend_id = p.user_id);
   COMMIT;
   

2. 错误处理与补偿机制：如果在缓存表更新过程中出现错误，要有相应的错误处理和补偿机制。例如，记录错误日志，并且可以通过重试机制重新执行缓存表更新操作。如果重试多次仍失败，可以人工介入检查并修复数据一致性问题。同时，在系统监控中设置相关指标，以便及时发现数据不一致的情况。