資料庫設計規範

整理一下過去設計資料庫的一下經驗

基礎規範

1. 資料庫依照環境命名，例如資料庫的名稱叫做 abc，那在 DEV 環境下我們就叫做 abc_dev, 在 UAT 環境下我們就叫做 abc_uat，在 prod 的環境下就叫做 abc_prod
2. 資料庫裡面的命名 (database, table, column) 都用小寫 (snake_case), 例如: user_id
3. 禁止使用 NULL，不要花時間而外去處理 NULL，每一個 column 都需要有預設值
4. 必須使用UTF8字符集
5. 資料庫裡面的時間必須是用 UTC 時間，由程式來針對使用者想要的時區做轉換並顯示
6. 表必須有主鍵 (primary key)，例如自增主鍵或 UUID
7. 留意當表裡面的筆數量百萬筆以上的時候，新增 column 或新增索引需需要考量 table 會被 lock 的問題 (可透過 mysql 8 的 instant ddl 來解決)

Index 設計

1. 單表索引建議控制在5個以內
2. 建立組合索引，必須把區分度高的字段放在前面，因為在 WHERE 條件，先後順序有差
3. 在使用 WHERE 查詢的時候，第一個條件要有在 index 裡面, 這樣才能使用到 index
4. 禁用 foregin key，對 performance 不好，對開發時也不方便 (FK會導致表與表之間耦合，update與delete操作都會涉及相關聯的表，十分影響sql 的性能，甚至會造成死鎖。高並發情況下容易造成數據庫性能，大數據高並發業務場景數據庫使用以性能優先)
5. 一般索引命名 idx_xxx，唯一索引命名 uniq_xxx

預設值

1. Datetime: '1970-01-01 00:00:00'

SQL語句注意事項

1. 少用負向查詢，以及%開頭的模糊查詢 (負向查詢條件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，會導致全表掃瞄)，所以需要用正向的方式，正向可以使用到 index
2. 注意大表使用JOIN查詢的場景，大表使用子查詢，另外可以用硬碟空間來換取時間，例如: 可以把會員名稱寫入到訂單的 table, 這樣要撈出訂單的時候，就不需要因為顯示訂單的創建人而需要 Join 會員的 table