面试题：MySQL Schema 性能调优之复杂关联查询与 Schema 重构

Question

在一个社交网络应用中，存在用户表（users）、好友关系表（friendships，记录用户之间的好友关系）、动态表（posts，用户发布的动态）以及点赞表（likes，记录对动态的点赞情况）。现在要进行一个复杂查询：找出用户 A 的所有好友及其点赞过的动态内容。当前的 Schema 在执行该查询时性能很差。请分析可能存在的性能问题，并提出 Schema 重构方案以及相应的性能优化措施。

Answer 1

可能存在的性能问题分析

1. 关联查询过多：从用户表到好友关系表，再到点赞表和动态表，涉及多次 JOIN 操作。在大数据量下，多表 JOIN 会使计算量呈指数级增长。
2. 索引缺失：如果 users、friendships、posts 和 likes 表在关联字段上没有合适的索引，JOIN 操作将进行全表扫描，导致性能低下。例如，friendships 表的 user_id 和 friend_id 字段，likes 表的 user_id 和 post_id 字段等。
3. 数据冗余：若表结构设计不合理，可能存在数据冗余，增加了存储和查询负担。

Schema 重构方案

1. 增加索引：
   • 在 users 表的 user_id 字段上添加主键索引。
   • 在 friendships 表的 user_id 和 friend_id 字段上添加联合索引，以加速好友关系的查找。
   • 在 posts 表的 post_id 字段上添加主键索引，在 user_id 字段上添加索引，便于根据用户查找动态。
   • 在 likes 表的 user_id 和 post_id 字段上添加联合索引，加速点赞信息的查找。
2. 反范式化：考虑在一定程度上进行反范式化，例如在 friendships 表中可以冗余一些用户的基本信息（如用户名等），减少查询时与 users 表的 JOIN 次数。但要注意数据一致性维护。
3. 分区表：对于数据量较大的表，如 posts 和 likes 表，可以按时间或其他维度进行分区。例如，posts 表按月份分区，likes 表按用户 ID 范围分区等，这样在查询时可以减少扫描的数据量。

性能优化措施

1. 查询优化：使用适当的查询语句，例如在 SQL 中利用 JOIN 的优化语法，如 INNER JOIN、LEFT JOIN 等，确保查询逻辑正确且高效。例如：

SELECT posts.content
FROM users
JOIN friendships ON users.user_id = friendships.user_id AND friendships.user_id = 'A'
JOIN likes ON friendships.friend_id = likes.user_id
JOIN posts ON likes.post_id = posts.post_id;

2. 缓存：
   • 对频繁查询的结果进行缓存，例如使用 Redis 等缓存工具。如果用户 A 的好友及其点赞动态查询频繁，可以将结果缓存起来，减少数据库的查询压力。
   • 应用层缓存，在应用程序中设置缓存机制，对于短时间内重复的查询直接返回缓存结果。
3. 异步处理：对于一些非实时要求的操作，如点赞后的更新操作，可以采用异步处理，避免影响主查询的性能。例如使用消息队列（如 RabbitMQ、Kafka 等）将点赞消息发送出去，后台异步处理点赞记录的更新等操作。