乐趣区

关于后端:初学后端如何做好表结构设计

jiezi

前言

最近有不少前端和测试转 Go 的敌人私信我： 如何做好表结构设计？

大家关怀的问题阳哥必须整理出来，心愿对大家有帮忙。

先说论断

这篇文章介绍了设计数据库表构造应该思考的 4 个方面，还有优雅设计的 6 个准则，举了一个例子分享了我的设计思路，为了进步性能咱们也要从多方面思考缓存问题。

播种最大的还是和大家的交换探讨，总结一下：

  1. 首先，肯定要先搞清楚业务需要。比方我的例子中，如果不须要灵便设置，齐全能够写到配置文件中，并不需要独自设计外键。主表中间接保留各种筛选标签名称（留神保护的问题，要思考到数据一致性）
  2. 数据库表结构设计肯定思考数据量和并发量，我的例子中如果数据量小，能够适当做冗余设计，升高业务复杂度。

4 个方面

设计数据库表构造须要思考到以下 4 个方面：

  1. 数据库范式 ：通常状况下，咱们心愿表的数据合乎某种范式，这能够保证数据的完整性和一致性。例如，第一范式要求表的每个属性都是原子性的，第二范式要求每个非主键属性齐全依赖于主键，第三范式要求每个非主键属性不依赖于其余非主键属性。
  2. 实体关系模型（ER 模型）：咱们须要先依据理论状况画出实体关系模型，而后再将其转化为数据库表构造。实体关系模型通常包含实体、属性、关系等因素，咱们须要将它们转化为表的模式。
  3. 数据库性能 ：咱们须要思考到数据库的性能问题，包含表的大小、索引的应用、查问语句的优化等。
  4. 数据库安全 ：咱们须要思考到数据库的平安问题，包含表的权限、用户角色的设置等。

设计准则

在设计数据库表构造时，能够参考以下几个优雅的设计准则：

  1. 简单明了 ：表构造应该简单明了，防止适度复杂化。
  2. 一致性 ：表构造应该放弃一致性，例如命名标准、数据类型等。
  3. 规范化 ：尽可能将表规范化，防止数据冗余和不一致性。
  4. 性能 ：表构造应该思考到性能问题，例如应用适当的索引、防止全表扫描等。
  5. 平安 ：表构造应该思考到平安问题，例如正当设置权限、防止 SQL 注入等。
  6. 扩展性 ：表构造应该具备肯定的扩展性，例如预留字段、可扩大的关系等。

最初，须要揭示的是，优雅的数据库表构造须要在实践中一直迭代和优化，一直满足理论需要和新的挑战。

上面举个示例让大家更好的了解如何设计表构造，如何引入内存，有哪些优化思路：

问题形容

如上图所示，红框中的视频筛选标签，应该怎么设计数据库表构造？ 除了前台筛选，还想反对在治理后盾灵便配置这些筛选标签。

这是一个很好的利用场景，大家能够先本人想一下。不要焦急看我的计划。

需要剖析

  1. 能够依据红框的标签筛选视频
  2. 其中综合标签比拟非凡，和类型、地区、年份、演员等不一样
  3. 综合是依据业务逻辑取值，并不需要入库
  4. 类型、地区、年份、演员等须要入库
  5. 设计表构造时要思考到：
  6. 不便获取标签信息，不便把标签信息缓存解决
  7. 不便依据标签筛选视频，不便咱们写后续的业务逻辑

设计思路

  1. 综合标签能够写到配置文件中（或者写在前端），这些信息不须要灵便配置，所以不须要保留到数据库中
  2. 类型、地区、年份、演员都设计独自的表
  3. 视频表中设计标签表的外键，不便视频列表筛选取值
  4. 标签信息写入缓存，进步接口响应速度
  5. 类型、地区、年份、演员表也要反对对数据排序，不便前期治理保护

表结构设计

视频表

字段 正文
id 视频主键 id
type_id 类型表外键 id
area_id 地区表外键 id
year_id 年份外键 id
actor_id 演员外键 id

其余和视频间接相干的字段（比方名称）我就省略不写了

类型表

字段 正文
id 类型主键 id
name 类型名称
sort 排序字段

地区表

字段 正文
id 类型主键 id
name 类型名称
sort 排序字段

年份表

字段 正文
id 类型主键 id
name 类型名称
sort 排序字段

原以为年份字段不须要排序，要么是年份正序排列，要么是年份倒序排列，所以不须要 sort 字段。

认真看了看需要，还有“10 年代”还是须要灵便配置的呀~

演员表

字段 正文
id 类型主键 id
name 类型名称
sort 排序字段

表结构设计完了，别忘了缓存

缓存策略

首先这些不会频繁更新的筛选条件倡议应用缓存：

  1. 比拟罕用的就是 redis 缓存
  2. 再进阶一点，如果你应用 docker，能够把这些配置信息写入 docker 容器所在物理机的内存中，而不必申请其余节点的 redis，进一步升高网络传输带来的耗时损耗
  3. 筛选条件这类配置信息，客户端和服务端能够约定一个更新缓存的机制，客户端间接缓存配置信息，进一步提高性能

列表数据主动缓存

目前很多框架都是反对主动缓存解决的，比方 goframe 和 go-zero

goframe

能够应用 ORM 链式操作 - 查问缓存

示例代码：

package main

import (
    "time"

    "github.com/gogf/gf/v2/database/gdb"
    "github.com/gogf/gf/v2/frame/g"
    "github.com/gogf/gf/v2/os/gctx"
)

func main() {
    var (db  = g.DB()
        ctx = gctx.New())

    // 开启调试模式，以便于记录所有执行的 SQL
    db.SetDebug(true)

    // 写入测试数据
    _, err := g.Model("user").Ctx(ctx).Data(g.Map{
        "name": "xxx",
        "site": "https://xxx.org",
    }).Insert()

    // 执行 2 次查问并将查问后果缓存 1 小时，并可执行缓存名称 (可选)
    for i := 0; i < 2; i++ {r, _ := g.Model("user").Ctx(ctx).Cache(gdb.CacheOption{
            Duration: time.Hour,
            Name:     "vip-user",
            Force:    false,
        }).Where("uid", 1).One()
        g.Log().Debug(ctx, r.Map())
    }

    // 执行更新操作，并清理指定名称的查问缓存
    _, err = g.Model("user").Ctx(ctx).Cache(gdb.CacheOption{
        Duration: -1,
        Name:     "vip-user",
        Force:    false,
    }).Data(gdb.Map{"name": "smith"}).Where("uid", 1).Update()
    if err != nil {g.Log().Fatal(ctx, err)
    }

    // 再次执行查问，启用查问缓存个性
    r, _ := g.Model("user").Ctx(ctx).Cache(gdb.CacheOption{
        Duration: time.Hour,
        Name:     "vip-user",
        Force:    false,
    }).Where("uid", 1).One()
    g.Log().Debug(ctx, r.Map())
}

go-zero

DB 缓存机制

go-zero 缓存设计之长久层缓存

官网都做了具体的介绍，不作为本文的重点。

探讨

这篇文章首发在我的公众号《如何做好表结构设计？》，引起了大家的探讨。

也和大家分享一下：

Q1 冗余设计和一致性问题

发问：一个表里做了这么多外键，如果我要查各自的名称，势必要关联 4 张表，对于这种存在多外键关联的这种表，要不要做冗余呢 (间接在主表里冗余各自的名称字段)？要是保障一致性的话，就势必会影响性能，如果做冗余的话，又无奈保障一致性

答复：

你看文章的上下文应该晓得，文章想解决的是视频列表筛选问题。

你提到的这个场景是在视频详情信息中，如果要展现这些外键的名称怎么设计更好。

我的倡议是这样的：

  1. 依据需要能够做适当冗余，比方你的主表信息量不大，配置信息批改后同步批改冗余字段的老本并不高。
  2. 或者像我文章中写的不做冗余设计，然而会把外键信息缓存，业务查问从缓存中取值。
  3. 或者将视频详情的查问后果整体进行缓存

还是看具体需要，如果这些筛选信息不变动或者不须要手工治理，甚至不须要设计表，间接写死在代码的配置文件中也能够。进一步升高 DB 压力，进步性能。

Q2 why 设计外键？

发问：为什么要设计外键关联？间接写到视频表中不就行了？这么设计的意义在哪里？

答复：

  1. 关键问题是想解决治理后盾灵便配置
  2. 如果没有这个需要，咱们能够间接把筛选条件以配置文件的形式写死在程序中，升高复杂度。
  3. 站在我的角度：这个性能的筛选条件变动并不会很大，所以很懂你的意思。也倡议像我 2. 中的计划去做，去和产品经理拉扯喽~

总结

这篇文章介绍了设计数据库表构造应该思考的 4 个方面，还有优雅设计的 6 个准则，举了一个例子分享了我的设计思路，为了进步性能咱们也要从多方面思考缓存问题。

播种最大的还是和大家的交换探讨，总结一下：

  1. 首先，肯定要先搞清楚业务需要。比方我的例子中，如果不须要灵便设置，齐全能够写到配置文件中，并不需要独自设计外键。主表中间接保留各种筛选标签名称（留神保护的问题，要思考到数据一致性）
  2. 数据库表结构设计肯定思考数据量和并发量，我的例子中如果数据量小，能够适当做冗余设计，升高业务复杂度

本文抛砖引玉，欢送大家留言交换。

退出移动版