douyin数据库表的设计|青训营笔记

用户5385197202169
150 阅读4分钟

douyin数据库表的设计|青训营笔记

这是我参与「第五届青训营」伴学笔记创作活动的第13天

项目依然开始，记录一下今天的收获啦~

一、方案概述

针对客户端的需求，一共需要6张数据表，建立一个名为douyin的数据库，用于存储六张表。

  • 全局设计

    • 引擎选取：使用InnoDB存储引擎，支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。

      在这里插入图片描述

    • 字体设置：默认使用utf8mb4字符集：utf8mb4是utf8的超集，emoji表情以及部分不常见汉字在utf8下会表现为乱码。

    • 支持软删：用于恢复重要信息。

  • 局部设计

    • 用户表支持用户名唯一索引

       DROP TABLE IF EXISTS `user`;
 CREATE TABLE `user`
 (
     `id`             bigint unsigned NOT NULL AUTO_INCREMENT,
     `created_at`     datetime(3) DEFAULT NULL,
     `updated_at`     datetime(3) DEFAULT NULL,
     `deleted_at`     bigint DEFAULT 0,
     `username`       varchar(20)  NOT NULL,
     `password`       varchar(255) NOT NULL,
     `follow_count`   bigint unsigned DEFAULT '0',
     `follower_count` bigint unsigned DEFAULT '0',
     PRIMARY KEY (`id`),
     KEY              `idx_user_deleted_at` (`deleted_at`),
     UNIQUE (`username`, `deleted_at`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

    • 软删字段支持索引查询

二、遇到的问题以及处理方法

用户表中用户名的唯一索引与软删索引如何共存

  • 唯一索引概念

    • 在数据库层面来约束数据的唯一性。唯一索引可以建立在一个字段上，也可以是在多个字段上。例如在用户表中，用户名是不可以重复的，那么就需要在这个字段上，加上唯一索引进行约束。
    • 唯一索引的性能：比普通索引快一点，不明显，参考阅读（Mysql唯一索引性能问题 - icecrea (gitbook.io)）
    • 由于业务逻辑，故这里使用对用户名字段设置唯一索引

  • 软删概念

    • 软删除也叫做逻辑删除，它并不是真正的把数据删除，而是用一个字段来标记当前数据是否是被删除的。使用软删除的时候，通常会在表中增加一个字段deletedAt表示是否删除。
    • 当使用gorm软删除时，deletedAt字段为datetime类型，那么当执行删除操作时，会把原本的Delete xxx变成Update Set deleteAt=now() where xxx。而在实行查询或者更新操作时，都会在条件中加上deletedAt is null，例如：UPDATE user SET deleted_at='2023-02-07 13:32:43.779' WHERE user.id = 1 AND user.deleted_at IS NULL

  • 两者结合遇到的bug

    • 比如username为cbn的用户被删除了（即delete_at字段不为null了），之后有添加username为cbn的用户就会报错。因为虽然之前的cbn是被删除了，但是实际还是存在表中的，只是我们看不到了而已。
    • 于是可以用联合唯一索引的形式，unique key (username,deleted_at)在username和deleted_at这两个字段上建立唯一索引约束。
    • 但是由于deletedAt 初始化为 null，这样的联合索引会失效，于是不能是时间格式，为此这里将deletedAt的类型改为bigint并且初始化为0
    • 最后采用soft_delete+bigint的方式进行处理即可解决。

  • 那么soft_delete和gorm.Detele有什么区别？

    • gorm.Detele提供的软删除方案则是上面说所的使用时间（time.Time）类型来标记是否删除了，以至于初始化bigint为0时，会向数据库插入0000：00：00：00的时间格式，这个在mysql里面是违法的。
    • soft_delete提供的软删方案是以时间戳（s）类型来标记是否被删除，这就不会生成一个错误的0日期格式，如果需要得到删除的时间需要进行时间戳转化即可。

三、后续考虑的问题

关于软删的字段是否都需要加上？考虑只加在用户等重要的数据表中，不然后面会有诸多垃圾数据，导致查询慢

慎用使用select *，只获取必要的字段，需要显式说明列属性

  • 读取不需要的列会增加CPU、IO消耗
  • 不能有效的利用覆盖索引
avatar
文章
阅读
粉丝