面试题：MySQL高并发场景下Schema设计与关联查询的极致优化

Question

在一个社交平台中，有用户表users（id, username），动态表posts（id, user_id, post_content, post_time），点赞表likes（id, user_id, post_id），评论表comments（id, user_id, post_id, comment_content, comment_time）。假设该平台每天有大量的动态发布、点赞和评论操作，现在要查询出点赞数和评论数总和排名前10的用户及其相关数据（用户名、动态数、点赞总数、评论总数）。请从Schema设计、索引优化、查询语句优化等多方面阐述你的解决方案，以应对高并发场景下的性能需求。

Answer 1

Schema设计优化

1. 分表策略：
   • 由于每天有大量操作，对于大表如posts、likes、comments可以采用水平分表。例如按时间（如按月）对posts表进行分表，likes和comments表可以按user_id或post_id的哈希值进行分表，减少单表数据量，提高查询性能。
2. 增加冗余字段：
   • 在users表中增加post_count（动态数）、like_count（点赞总数）、comment_count（评论总数）字段。每次有新动态发布、点赞或评论操作时，相应更新这些字段。这样查询用户相关数据时可以直接从users表获取，减少多表关联查询。

索引优化

1. users表：
   • id字段建立主键索引，加速基于用户ID的查询。
2. posts表：
   • id字段建立主键索引。
   • user_id字段建立普通索引，方便通过用户ID查询其发布的动态。
   • post_time字段建立索引，便于按时间范围查询动态。
3. likes表：
   • id字段建立主键索引。
   • user_id和post_id字段分别建立普通索引，若经常需要按用户或动态查询点赞情况，还可以建立联合索引(user_id, post_id)或(post_id, user_id)。
4. comments表：
   • id字段建立主键索引。
   • user_id和post_id字段分别建立普通索引，同样可考虑建立联合索引(user_id, post_id)或(post_id, user_id)，便于查询用户对特定动态的评论或某动态的所有评论。

查询语句优化

1. 若采用冗余字段方案：

SELECT 
    username, 
    post_count, 
    like_count, 
    comment_count
FROM 
    users
ORDER BY 
    like_count + comment_count DESC
LIMIT 10;

2. 若未采用冗余字段方案：

SELECT 
    u.username, 
    COUNT(DISTINCT p.id) AS post_count, 
    COUNT(l.id) AS like_count, 
    COUNT(c.id) AS comment_count
FROM 
    users u
LEFT JOIN 
    posts p ON u.id = p.user_id
LEFT JOIN 
    likes l ON u.id = l.user_id
LEFT JOIN 
    comments c ON u.id = c.user_id
GROUP BY 
    u.id, u.username
ORDER BY 
    like_count + comment_count DESC
LIMIT 10;

在高并发场景下，还可以考虑使用缓存（如Redis）来缓存热门用户的数据，减少数据库查询压力。同时，对数据库进行读写分离，将读操作分担到从库，提高整体性能。