头图

初学后端,如何做好表结构设计?

前言

最近有不少前端和测试转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. 数据库表结构设计一定考虑数据量和并发量,我的例子中如果数据量小,可以适当做冗余设计,降低业务复杂度
本文抛砖引玉,欢迎大家留言交流。

专注Go语言,靠敲代码在北京买房的程序员。

419 声望
168 粉丝
0 条评论
推荐阅读
2 天时间 3 个面试,百度进了 3 面!
昨天和朋友复盘了一下最近的面试经历,分享给大家:关于就业环境忠告:如果不是在二三线买车买房结婚生子了,还是到一线城市去吧。或者换个行业!关于焦虑和摆烂如果你也在焦虑迷茫、精神内耗。找阳哥给你做“心理...

王中阳Go1阅读 415

封面图
花了几个月时间把 MySQL 重新巩固了一遍,梳理了一篇几万字 “超硬核” 的保姆式学习教程!(持续更新中~)
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

民工哥14阅读 2k

封面图
终于卷完了!Redis 打怪升级进阶成神之路(2023 最新版)!
是一种非关系型数据库服务,它能解决常规数据库的并发能力,比如传统的数据库的IO与性能的瓶颈,同样它是关系型数据库的一个补充,有着比较好的高效率与高性能。专注于key-value查询的redis、memcached、ttserver。

民工哥11阅读 989

封面图
golang学习之旅——解开心中的go mod疑惑
在go1.16版本发布后,go module由原来的默认值 auto 变为 on 了,这意味着后续开发中,go更推荐用go module 模式开发,而不是gopath模式开发了。

Keson11阅读 14.8k

算法可视化:一文弄懂 10 大排序算法
在本文中,我们将通过动图可视化加文字的形式,循序渐进全面介绍不同类型的算法及其用途(包括原理、优缺点及使用场景)并提供 Python 和 JavaScript 两种语言的示例代码。除此之外,每个算法都会附有一些技术说...

破晓L7阅读 945

封面图
硬卷完了!MongoDB 打怪升级进阶成神之路( 2023 最新版 )!
前面我们学习:MySQL 打怪升级进阶成神之路、Redis 打怪升级进阶成神之路,然后我们还在继续 NoSQL 的卷王之路。从第一篇文章开始,我们逐步详细介绍了 MogoDB 基础概念、安装和最基本的CURD操作、索引和聚合、工...

民工哥6阅读 553

封面图
「刷起来」Go必看的进阶面试题详解
逃逸分析是Go语言中的一项重要优化技术,可以帮助程序减少内存分配和垃圾回收的开销,从而提高程序的性能。下面是一道涉及逃逸分析的面试题及其详解。

王中阳Go4阅读 1.9k评论 1

封面图

专注Go语言,靠敲代码在北京买房的程序员。

419 声望
168 粉丝
宣传栏