面试题：MySQL范式在复杂业务中的深度应用

Question

有一个社交网络应用，包含用户关系（好友、粉丝等）、动态发布、评论、点赞等复杂业务。请详细描述如何在遵循MySQL范式的基础上，设计出既满足业务需求又具备良好扩展性和性能的数据库架构。同时，说明在这个过程中如何处理范式带来的多表关联复杂性与查询性能之间的矛盾，以及对数据完整性和一致性的保障策略。

Answer 1

数据库架构设计

1. 用户表（users）
   • 字段：用户ID（user_id，主键，自增）、用户名（username，唯一索引）、密码（password）、注册时间（register_time）等基本信息。
   • 作用：存储用户的基本资料，用于身份验证和标识。
2. 用户关系表
   • 好友关系表（friends）：
     • 字段：关系ID（relation_id，主键，自增）、用户ID1（user_id1，外键，关联users表的user_id）、用户ID2（user_id2，外键，关联users表的user_id）、关系建立时间（create_time）。
     • 作用：记录用户之间的好友关系，user_id1和user_id2地位平等，避免重复记录。
   • 粉丝关系表（followers）：
     • 字段：关系ID（relation_id，主键，自增）、粉丝ID（follower_id，外键，关联users表的user_id）、被关注用户ID（followed_id，外键，关联users表的user_id）、关注时间（follow_time）。
     • 作用：记录用户之间的关注关系，区分粉丝和被关注者。
3. 动态表（posts）
   • 字段：动态ID（post_id，主键，自增）、用户ID（user_id，外键，关联users表的user_id）、动态内容（content）、发布时间（publish_time）、图片/视频链接（media_url，可为空）等。
   • 作用：存储用户发布的动态信息。
4. 评论表（comments）
   • 字段：评论ID（comment_id，主键，自增）、动态ID（post_id，外键，关联posts表的post_id）、用户ID（user_id，外键，关联users表的user_id）、评论内容（comment_content）、评论时间（comment_time）。
   • 作用：记录对动态的评论信息。
5. 点赞表（likes）
   • 字段：点赞ID（like_id，主键，自增）、动态ID（post_id，外键，关联posts表的post_id）、用户ID（user_id，外键，关联users表的user_id）、点赞时间（like_time）。
   • 作用：记录用户对动态的点赞信息。

处理范式带来的多表关联复杂性与查询性能之间的矛盾

1. 合理使用索引
   • 在关联字段（如外键）上创建索引，如users表的user_id在其他关联表作为外键时，应创建索引，这样可以加速连接操作。例如，在friends表的user_id1和user_id2字段，followers表的follower_id和followed_id字段，posts表的user_id字段，comments表的post_id和user_id字段，likes表的post_id和user_id字段上都创建索引。
   • 对于经常用于查询条件的字段，如posts表的publish_time字段，如果经常按照发布时间查询动态，也应创建索引。
2. 适当冗余字段
   • 在不严重破坏数据完整性的前提下，可适当冗余一些字段。例如，在posts表中冗余用户的用户名，这样在展示动态列表时，无需再关联users表查询用户名，减少多表关联。但需要注意在用户信息更新时，同步更新冗余字段。
3. 使用视图（View）
   • 对于一些复杂且常用的查询，可以创建视图。例如，要查询某个用户的所有好友发布的动态及点赞数、评论数，可创建视图，将多表关联的逻辑封装在视图中，简化查询语句，同时MySQL会对视图查询进行一定的优化。
4. 分库分表
   • 随着数据量增长，可进行分库分表。例如按用户ID范围进行水平分表，将不同用户的数据分散到不同表中，减少单表数据量，提升查询性能。同时，不同业务模块（如用户关系、动态相关）可分到不同数据库，降低单个数据库的压力。

对数据完整性和一致性的保障策略

1. 数据库约束
   • 主键约束：在各表的主键字段（如user_id、post_id等）上设置主键约束，确保每条记录的唯一性。
   • 外键约束：通过外键关联确保数据的一致性。例如，friends表中的user_id1和user_id2、followers表中的follower_id和followed_id等外键，保证引用的用户ID在users表中存在，防止出现孤立数据。
   • 唯一性约束：如users表的username字段设置唯一性约束，避免用户名重复。
2. 事务处理
   • 对于涉及多个表数据修改的操作，如用户发布动态并同时记录发布者信息到posts表和关联到users表，要使用事务处理。确保要么所有操作都成功执行，要么都回滚，保证数据的一致性。例如，在插入一条评论时，同时更新动态的评论数，这两个操作应在一个事务内完成。
3. 定期数据校验与修复
   • 定期运行脚本来检查数据的完整性，如检查外键关联是否正确，是否存在孤立数据等。对于发现的问题数据，根据业务规则进行修复，如删除孤立数据或重新建立正确的关联。