初学后端，如何做好表结构设计？

淩亂°似流年 2024-03-25 22:18 30阅读 0赞

### 前言 ###

最近有不少前端和测试转Go的朋友在[交流群][Link 1]里聊：**如何做好表结构设计？**

大家关心的问题阳哥必须整理出来，希望对大家有帮助。

### 先说结论 ###

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

收获最大的还是和大家的交流讨论，总结一下：

1.  首先，一定要先搞清楚业务需求。比如我的例子中，如果不需要灵活设置，完全可以写到配置文件中，并不需要单独设计外键。主表中直接保存各种筛选标签名称（注意维护的问题，要考虑到数据一致性）
2.  数据库表结构设计一定考虑数据量和并发量，我的例子中如果数据量小，可以适当做冗余设计，降低业务复杂度。

### 4个方面 ###

设计数据库表结构需要考虑到以下4个方面：

1.  **数据库范式**：通常情况下，我们希望表的数据符合某种范式，这可以保证数据的完整性和一致性。例如，第一范式要求表的每个属性都是原子性的，第二范式要求每个非主键属性完全依赖于主键，第三范式要求每个非主键属性不依赖于其他非主键属性。
2.  **实体关系模型（ER模型）**：我们需要先根据实际情况画出实体关系模型，然后再将其转化为数据库表结构。实体关系模型通常包括实体、属性、关系等要素，我们需要将它们转化为表的形式。
3.  **数据库性能**：我们需要考虑到数据库的性能问题，包括表的大小、索引的使用、查询语句的优化等。
4.  **数据库安全**：我们需要考虑到数据库的安全问题，包括表的权限、用户角色的设置等。

### 设计原则 ###

在设计数据库表结构时，可以参考以下几个优雅的设计原则：

1.  **简单明了**：表结构应该简单明了，避免过度复杂化。
2.  **一致性**：表结构应该保持一致性，例如命名规范、数据类型等。
3.  **规范化**：尽可能将表规范化，避免数据冗余和不一致性。
4.  **性能**：表结构应该考虑到性能问题，例如使用适当的索引、避免全表扫描等。
5.  **安全**：表结构应该考虑到安全问题，例如合理设置权限、避免SQL注入等。
6.  **扩展性**：表结构应该具有一定的扩展性，例如预留字段、可扩展的关系等。

**最后，需要提醒的是，优雅的数据库表结构需要在实践中不断迭代和优化，不断满足实际需求和新的挑战。**

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

### 问题描述 ###

![97a6cecfdb49d8327a5f0620fdbc3751.png][]

如上图所示，红框中的视频筛选标签，应该怎么设计数据库表结构？**除了前台筛选，还想支持在管理后台灵活配置这些筛选标签。**

这是一个很好的应用场景，大家可以先自己想一下。不要着急看我的方案。

### 需求分析 ###

1.  可以根据红框的标签筛选视频
2.  其中综合标签比较特殊，和类型、地区、年份、演员等不一样

*  综合是根据业务逻辑取值，并不需要入库
 *  类型、地区、年份、演员等需要入库

1.  设计表结构时要考虑到：

*  方便获取标签信息，方便把标签信息缓存处理
 *  方便根据标签筛选视频，方便我们写后续的业务逻辑

### 设计思路 ###

1.  综合标签可以写到配置文件中（或者写在前端），这些信息不需要灵活配置，所以不需要保存到数据库中
2.  类型、地区、年份、演员都设计单独的表
3.  视频表中设计标签表的外键，方便视频列表筛选取值
4.  标签信息写入缓存，提高接口响应速度
5.  类型、地区、年份、演员表也要支持对数据排序，方便后期管理维护

### 表结构设计 ###

#### 视频表 ####

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>注释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>视频主键id</td> 
  </tr> 
  <tr> 
   <td>type_id</td> 
   <td>类型表外键id</td> 
  </tr> 
  <tr> 
   <td>area_id</td> 
   <td>地区表外键id</td> 
  </tr> 
  <tr> 
   <td>year_id</td> 
   <td>年份外键id</td> 
  </tr> 
  <tr> 
   <td>actor_id</td> 
   <td>演员外键id</td> 
  </tr> 
 </tbody> 
</table>

其他和视频直接相关的字段（比如名称）我就省略不写了

#### 类型表 ####

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>注释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>类型主键id</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>类型名称</td> 
  </tr> 
  <tr> 
   <td>sort</td> 
   <td>排序字段</td> 
  </tr> 
 </tbody> 
</table>

#### 地区表 ####

#### 年份表 ####

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

仔细看了看需求，还有“10年代”还是需要灵活配置的呀~

#### 演员表 ####

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

### 缓存策略 ###

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

![bed0d780bb0e1a2f0c52f33c22787964.png][]

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

#### 列表数据自动缓存 ####

目前很多框架都是支持自动缓存处理的，比如goframe和go-zero

##### goframe #####

可以使用[ORM链式操作-查询缓存][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缓存机制][DB]

[go-zero缓存设计之持久层缓存][go-zero]

官方都做了详细的介绍，不作为本文的重点。

### 讨论 ###

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

也和大家分享一下：

#### Q1 冗余设计和一致性问题 ####

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

##### 回答： #####

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

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

我的建议是这样的：

1.  根据需求可以做适当冗余，比如你的主表信息量不大，配置信息修改后同步修改冗余字段的成本并不高。
2.  或者像我文章中写的不做冗余设计，但是会把外键信息缓存，业务查询从缓存中取值。
3.  或者将视频详情的查询结果整体进行缓存

**还是看具体需求，如果这些筛选信息不变化或者不需要手工管理，甚至不需要设计表，直接写死在代码的配置文件中也可以。进一步降低DB压力，提高性能。**

#### Q2 why设计外键？ ####

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

##### 回答： #####

1.  关键问题是想解决管理后台灵活配置
2.  如果没有这个需求，我们可以直接把筛选条件以配置文件的方式写死在程序中，降低复杂度。
3.  站在我的角度：这个功能的筛选条件变化并不会很大，所以很懂你的意思。也建议像我2.中的方案去做，去和产品经理拉扯喽~

### 总结 ###

收获最大的还是和大家的交流讨论，总结一下：

1.  首先，一定要先搞清楚业务需求。比如我的例子中，如果不需要灵活设置，完全可以写到配置文件中，并不需要单独设计外键。主表中直接保存各种筛选标签名称（注意维护的问题，要考虑到数据一致性）
2.  数据库表结构设计一定考虑数据量和并发量，我的例子中如果数据量小，可以适当做冗余设计，降低业务复杂度

> 本文抛砖引玉，欢迎大家留言交流。

### 文章首发 ###

我的文章会首发在我的公众号：[程序员升职加薪之旅][Link 3]，欢迎大家关注，第一时间收到最新内容。

### 一起学习 ###

我的所有文章都会首发在我的 [**学习小圈子**][Link 3] ，欢迎加入我们，一起学习进步，一起升职加薪。

[Link 1]: https://mp.weixin.qq.com/s?__biz=MzIyNjM0MzQyNg==&mid=2247486565&idx=2&sn=76e0d850c0a7d2cfe398ca2c1391c4d9&chksm=e870a308df072a1e73c42f4cdb99a636614ad8eff839168fb1bc3241156899e2a9420ed4cea9&scene=21#wechat_redirect
[97a6cecfdb49d8327a5f0620fdbc3751.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5f6d492cb40d4723b0312faa7987091e.png
[bed0d780bb0e1a2f0c52f33c22787964.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/d214c7ae165942558504fe59b72956a3.png
[ORM_-]: https://goframe.org/pages/viewpage.action?pageId=1114346
[DB]: https://go-zero.dev/cn/docs/blog/cache/cache
[go-zero]: https://go-zero.dev/cn/docs/blog/cache/redis-cache
[Link 2]: https://mp.weixin.qq.com/s/SYQTlvbLvbsSwFi2Q_kI1Q
[Link 3]: https://mp.weixin.qq.com/s?__biz=MzIyNjM0MzQyNg==&mid=2247486565&idx=2&sn=76e0d850c0a7d2cfe398ca2c1391c4d9&chksm=e870a308df072a1e73c42f4cdb99a636614ad8eff839168fb1bc3241156899e2a9420ed4cea9&token=400433917&lang=zh_CN&scene=21#wechat_redirect