面试题：MySQL WHERE子句专家级优化问题

Question

在一个大型社交平台数据库中，`posts` 表存储用户发布的帖子（包含 `post_id`、`user_id`、`content`、`post_date`、`likes_count`、`comments_count` 等字段），`comments` 表存储帖子的评论（包含 `comment_id`、`post_id`、`user_id`、`comment_content`、`comment_date` 等字段）。需求是查询近一周内发布且点赞数大于100同时评论数大于50的帖子及其评论数量大于10的评论信息。请详细说明 `WHERE` 子句的编写方式以及优化策略，同时考虑索引的创建和使用，如何确保在高并发场景下查询效率？

Answer 1

WHERE子句编写方式

SELECT p.post_id, p.content, p.likes_count, p.comments_count, c.comment_id, c.comment_content
FROM posts p
JOIN comments c ON p.post_id = c.post_id
WHERE p.post_date >= CURDATE() - INTERVAL 1 WEEK
  AND p.likes_count > 100
  AND p.comments_count > 50
  AND (SELECT COUNT(*) FROM comments sub_c WHERE sub_c.post_id = p.post_id) > 10;

优化策略

1. 索引创建：
   • 在 posts 表的 post_date、likes_count、comments_count 字段上创建复合索引 CREATE INDEX idx_post_date_likes_comments ON posts (post_date, likes_count, comments_count);。这样在查询近一周内发布且点赞数和评论数满足条件的帖子时，能利用索引快速定位。
   • 在 comments 表的 post_id 字段上创建索引 CREATE INDEX idx_comment_post_id ON comments (post_id);，以便在连接 posts 表和 comments 表以及子查询统计评论数量时提高效率。
2. 子查询优化：上述子查询使用了相关子查询，可以考虑将其改写为 JOIN 方式，提高查询效率。例如：

SELECT p.post_id, p.content, p.likes_count, p.comments_count, c.comment_id, c.comment_content
FROM posts p
JOIN comments c ON p.post_id = c.post_id
JOIN (
    SELECT post_id
    FROM comments
    GROUP BY post_id
    HAVING COUNT(*) > 10
) sub ON p.post_id = sub.post_id
WHERE p.post_date >= CURDATE() - INTERVAL 1 WEEK
  AND p.likes_count > 100
  AND p.comments_count > 50;

高并发场景下保证查询效率

1. 读写分离：使用主从复制架构，主库负责写操作，从库负责读操作。这样可以分散读压力，避免高并发读操作影响写操作性能。
2. 缓存机制：引入缓存系统（如 Redis），将热门帖子和评论信息缓存起来。在高并发情况下，优先从缓存中读取数据，减少数据库压力。对于写操作，更新数据库后及时更新缓存。
3. 数据库连接池：使用连接池管理数据库连接，避免高并发场景下频繁创建和销毁数据库连接带来的性能开销。可以使用如 HikariCP 等高性能连接池。
4. 分布式架构：将数据库按照一定规则（如按用户ID范围、按地区等）进行分片，减少单个数据库节点的负载，提高整体的并发处理能力。