小抖音数据库的实现 | 青训营笔记

CN千石
199 阅读5分钟

这是我参与「第五届青训营 」伴学笔记创作活动的第 12 天 本次数据库使用mysql8.0版本，innodb引擎，字符集utf8mb4.

设计要点

关注于文档设计，文档之外的功能并没有涉及讨论。

考虑到性能原因以及参考了开发规范，未设置外键约束。

所有表的主键统一字段为id，创建时间和更新时间统一为create_time和update_time

逻辑删除使用deleted_at

以下共设计user表在内的六张表，下面将一一赘述

User

通过观察查看用户信息接口，整合到了组成该表所需要的信息字段，除了主键默认的索引之外，还设置了user_name作为普通索引。未使用触发器

建表sql如下：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户的主键id',
  `user_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名设置二级索引',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户的登录密码',
  `follow_count` int(11) NOT NULL DEFAULT 0 COMMENT '用户关注数',
  `follower_count` int(11) NOT NULL DEFAULT 0 COMMENT '用户粉丝数',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` timestamp NULL DEFAULT NULL COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_name`(`user_name`) USING BTREE COMMENT '针对用户名设计了普通索引'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

Video

也是通过文档啊，将字段分析提取出来。没有别的注意的点

索引方面：

  • 针对视频标题添加索引
  • 针对视频作者添加索引
  • 针对作者id和id设置联合索引
DROP TABLE IF EXISTS `video`;
CREATE TABLE `video`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '作品的id',
  `title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '作品标题',
  `author_id` int(11) NOT NULL COMMENT '作者的id',
  `play_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '视频资源的url',
  `cover_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '封面的url',
  `favorite_count` int(11) NOT NULL DEFAULT 0 COMMENT '点赞数量',
  `comment_count` int(11) NOT NULL DEFAULT 0 COMMENT '评论数量',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` timestamp NULL DEFAULT NULL COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `title`(`title`) USING BTREE COMMENT '针对视频标题添加索引',
  INDEX `author_id`(`author_id`) USING BTREE COMMENT '针对视频作者设置索引',
  INDEX `id`(`id`, `author_id`) USING BTREE COMMENT '针对id和作者id设置联合索引'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

favourite和comment

点赞表和评论表的设计

点赞表的设计是遵循了主流设计，评论功能，因为看到文档中并没有提及子评论的功能，因此只考虑了视频内容下的评论。点赞表有自己的status字段，所以没有加逻辑删除

索引方面

  • 点赞表针对点赞用户设置索引
  • 针对userid和videoid设置联合索引

触发器

  • 点赞和评论都会增加video表的对应字段自增
  • 取消点赞会自减
DROP TABLE IF EXISTS `favourite`;
CREATE TABLE `favourite`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '点赞表的主键',
  `user_id` int(11) NOT NULL COMMENT '点赞人的id',
  `video_id` int(11) NOT NULL COMMENT '视频id',
  `status` int(1) NOT NULL COMMENT '点赞状态1为点赞2为取消',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE COMMENT '针对用户设置索引',
  INDEX `user_id_2`(`user_id`, `video_id`) USING BTREE COMMENT '针对userid和videoid设置联合索引'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;


DROP TRIGGER IF EXISTS `unfav_after`;
delimiter ;;
CREATE TRIGGER `unfav_after` AFTER UPDATE ON `favourite` FOR EACH ROW BEGIN
UPDATE video SET favorite_count = favorite_count-1 WHERE id = new.video_id;
END
;;
delimiter ;

DROP TRIGGER IF EXISTS `fav_after`;
delimiter ;;
CREATE TRIGGER `fav_after` AFTER INSERT ON `favourite` FOR EACH ROW BEGIN
UPDATE video SET favorite_count = favorite_count+1 WHERE id = new.video_id;
END
;;
delimiter ;

DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论表id',
  `video_id` int(11) NOT NULL COMMENT '视频id',
  `user_id` int(11) NOT NULL COMMENT '评论用户的id',
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '评论内容',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` timestamp NULL DEFAULT NULL COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `video_id`(`video_id`) USING BTREE COMMENT '为被评论视频设置索引'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci；



DROP TRIGGER IF EXISTS `com_after`;
delimiter ;;
CREATE TRIGGER `com_after` AFTER INSERT ON `comment` FOR EACH ROW BEGIN
UPDATE video SET comment_count = comment_count + 1 WHERE id = new.video_id;
END
;;
delimiter ;

Follow

增加了判断是否为互相关注的字段，防止额外查询。

未添加触发器，本表逻辑，建议在orm框架中使用事务处理。

索引方面：

  • 给user_id设置索引，查询关注列表时使用此索引
  • 给to_user_id设置索引，查询粉丝列表时使用此索引
DROP TABLE IF EXISTS `follow`;
CREATE TABLE `follow`  (
  `id` int(11) NOT NULL COMMENT '关注表的主键id',
  `user_id` int(11) NOT NULL COMMENT '关注人的id',
  `to_user_id` int(11) NOT NULL COMMENT '被关注人id',
  `status` int(1) NOT NULL COMMENT '是否为互相关注',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` timestamp NULL DEFAULT NULL COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE COMMENT '关注列表使用此索引',
  INDEX `to_user_id`(`to_user_id`) USING BTREE COMMENT '粉丝列表使用此索引'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

Message

也是根据文档设计的表啊。可以满足文档给出的接口

没有额外注意的了

索引方面也是根据user_id和to_user_id设置联合索引

DROP TABLE IF EXISTS `message`;
CREATE TABLE `message`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` int(11) NOT NULL COMMENT '发送人id',
  `to_user_id` int(11) NOT NULL COMMENT '接收方id',
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '发送内容',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id`, `to_user_id`) USING BTREE COMMENT '查询聊天记录设置索引'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
avatar
文章
阅读
粉丝