初學後端，如何做好表結構設計？
前言
最近有不少前端和測試轉Go的朋友在交流群裡聊：如何做好表結構設計？
大家關心的問題陽哥必須整理出來，希望對大家有幫助。
先說結論
這篇文章介紹了設計資料庫表結構應該考慮的4個方面，還有優雅設計的6個原則，舉了一個例子分享了我的設計思路，為了提高效能我們也要從多方面考慮快取問題。
收穫最大的還是和大家的交流討論，總結一下：
- 首先，一定要先搞清楚業務需求。比如我的例子中，如果不需要靈活設定，完全可以寫到組態檔中，並不需要單獨設計外來鍵。主表中直接儲存各種篩選標籤名稱（注意維護的問題，要考慮到資料一致性）
- 資料庫表結構設計一定考慮資料量和並行量，我的例子中如果資料量小，可以適當做冗餘設計，降低業務複雜度。
4個方面
設計資料庫表結構需要考慮到以下4個方面：
-
資料庫正規化：通常情況下，我們希望表的資料符合某種正規化，這可以保證資料的完整性和一致性。例如，第一規格化要求表的每個屬性都是原子性的，第二正規化要求每個非主鍵屬性完全依賴於主鍵，第三正規化要求每個非主鍵屬性不依賴於其他非主鍵屬性。
-
實體關係模型（ER模型）：我們需要先根據實際情況畫出實體關係模型，然後再將其轉化為資料庫表結構。實體關係模型通常包括實體、屬性、關係等要素，我們需要將它們轉化為表的形式。
-
資料庫效能：我們需要考慮到資料庫的效能問題，包括表的大小、索引的使用、查詢語句的優化等。
-
資料庫安全：我們需要考慮到資料庫的安全問題，包括表的許可權、使用者角色的設定等。
設計原則
在設計資料庫表結構時，可以參考以下幾個優雅的設計原則：
-
簡單明瞭：表結構應該簡單明瞭，避免過度複雜化。
-
一致性：表結構應該保持一致性，例如命名規範、資料型別等。
-
規範化：儘可能將表規範化，避免資料冗餘和不一致性。
-
效能：表結構應該考慮到效能問題，例如使用適當的索引、避免全表掃描等。
-
安全：表結構應該考慮到安全問題，例如合理設定許可權、避免SQL隱碼攻擊等。
-
擴充套件性：表結構應該具有一定的擴充套件性，例如預留欄位、可延伸的關係等。
最後，需要提醒的是，優雅的資料庫表結構需要在實踐中不斷迭代和優化，不斷滿足實際需求和新的挑戰。
下面舉個範例讓大家更好的理解如何設計表結構，如何引入記憶體，有哪些優化思路：
問題描述
如上圖所示，紅框中的視訊篩選標籤，應該怎麼設計資料庫表結構？除了前臺篩選，還想支援在管理後臺靈活設定這些篩選標籤。
這是一個很好的應用場景，大家可以先自己想一下。不要著急看我的方案。
需求分析
- 可以根據紅框的標籤篩選視訊
- 其中綜合標籤比較特殊，和型別、地區、年份、演員等不一樣
- 綜合是根據業務邏輯取值，並不需要入庫
- 型別、地區、年份、演員等需要入庫
- 設計表結構時要考慮到：
- 方便獲取標籤資訊，方便把標籤資訊快取處理
- 方便根據標籤篩選視訊，方便我們寫後續的業務邏輯
設計思路
- 綜合標籤可以寫到組態檔中（或者寫在前端），這些資訊不需要靈活設定，所以不需要儲存到資料庫中
- 型別、地區、年份、演員都設計單獨的表
- 視訊表中設計標籤表的外來鍵，方便視訊列表篩選取值
- 標籤資訊寫入快取，提高介面響應速度
- 型別、地區、年份、演員表也要支援對資料排序，方便後期管理維護
表結構設計
視訊表
| 欄位 | 註釋 |
|---|---|
| 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 | 排序欄位 |
表結構設計完了，別忘了快取
快取策略
首先這些不會頻繁更新的篩選條件建議使用快取：
- 比較常用的就是redis快取
- 再進階一點，如果你使用docker，可以把這些設定資訊寫入docker容器所在物理機的記憶體中，而不用請求其他節點的redis，進一步降低網路傳輸帶來的耗時損耗
- 篩選條件這類設定資訊，使用者端和伺服器端可以約定一個更新快取的機制，使用者端直接快取設定資訊，進一步提高效能
列表資料自動快取
目前很多框架都是支援自動快取處理的，比如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
官方都做了詳細的介紹，不作為本文的重點。
討論
這篇文章首發在我的公眾號《如何做好表結構設計？》，引起了大家的討論。
也和大家分享一下：
Q1 冗餘設計和一致性問題
提問： 一個表裡做了這麼多外來鍵，如果我要查各自的名稱，勢必要關聯4張表，對於這種存在多外來鍵關聯的這種表，要不要做冗餘呢(直接在主表裡冗餘各自的名稱欄位)？要是保證一致性的話，就勢必會影響效能，如果做冗餘的話，又無法保證一致性
回答：
你看文章的上下文應該知道，文章想解決的是視訊列表篩選問題。
你提到的這個場景是在視訊詳情資訊中，如果要展示這些外來鍵的名稱怎麼設計更好。
我的建議是這樣的：
- 根據需求可以做適當冗餘，比如你的主表資訊量不大，設定資訊修改後同步修改冗餘欄位的成本並不高。
- 或者像我文章中寫的不做冗餘設計，但是會把外來鍵資訊快取，業務查詢從快取中取值。
- 或者將視訊詳情的查詢結果整體進行快取
還是看具體需求，如果這些篩選資訊不變化或者不需要手工管理，甚至不需要設計表，直接寫死在程式碼的組態檔中也可以。進一步降低DB壓力，提高效能。
Q2 why設計外來鍵？
提問：為什麼要設計外來鍵關聯？直接寫到視訊表中不就行了？這麼設計的意義在哪裡？
回答：
- 關鍵問題是想解決管理後臺靈活設定
- 如果沒有這個需求，我們可以直接把篩選條件以組態檔的方式寫死在程式中，降低複雜度。
- 站在我的角度：這個功能的篩選條件變化並不會很大，所以很懂你的意思。也建議像我2.中的方案去做，去和產品經理拉扯嘍~
總結
這篇文章介紹了設計資料庫表結構應該考慮的4個方面，還有優雅設計的6個原則，舉了一個例子分享了我的設計思路，為了提高效能我們也要從多方面考慮快取問題。
收穫最大的還是和大家的交流討論，總結一下：
- 首先，一定要先搞清楚業務需求。比如我的例子中，如果不需要靈活設定，完全可以寫到組態檔中，並不需要單獨設計外來鍵。主表中直接儲存各種篩選標籤名稱（注意維護的問題，要考慮到資料一致性）
- 資料庫表結構設計一定考慮資料量和並行量，我的例子中如果資料量小，可以適當做冗餘設計，降低業務複雜度
本文拋磚引玉，歡迎大家留言交流。