資料庫設計-物理設計

資料庫設計

物理設計：

1. 物理設計要做什麼？

• • 選擇合適的資料庫管理系統。
  • 考慮因素：成本，業務場景，開發語言，功能，操作系統等。

2.定義資料庫，表及欄位，要符合命名規範。

• • 選擇存儲引擎：這裡以MySQL為例

• • 表及欄位命名規範
    1. 可讀性：命名可讀性強
       • 舉例：列名nickname和nick_name相比，後者更加清晰明瞭，看起來更加舒服一點。
    2. 表意性：見名知意
       • 舉例：列名col1和user_name相比，後者更加具有直觀性，可以讓我們一眼就知道當前列名所代表的意思和可能的數據類型
    3. 敏感性：不能與資料庫專有欄位命名衝突
       • 舉例：MySQL中 有user表，所以我們自己創建用戶表時，儘量不使用user命名，可以加一個首碼比如資料庫縮寫_user。

3.根據選擇的資料庫管理系統選擇合適的數據類型

• • 常用數據類型及占用空間

• • 欄位類型的選擇的原則

1. 當一個列可以選擇多種數據類型的時候，優先考慮數字類型，其次是日期類型和二進位類型，最後是字元類型。
2. 對於相同的數據類型，應考慮占用空間較小的數據類型。

• • 欄位類型的選擇的原則依據

1. 在對數據進行比較（查詢條件，JOIN條件及排序）操作時，同樣的數據，字元處理往往比數字處理效率要低，因為字元要參考數據字典進行比較，數字就不需要。
2. 在資料庫中，數據處理以頁為單位，列的長度越小，一頁中存儲的數據就越多，載入相同的數據時的頁數就相對較小，速度會更快。

• • 如何具體選擇欄位類型？

1. char和varchar該如何選擇？
   • char用於數據長度差不多是一致的，基本都在一個小區間內波動或者列中最大數據長度小於50位元組。
   • varchar用於數據長度變化較大，不能預知其具體長度的數據。
2. decimal和float該如何選擇？
   • decimal用於存儲精確數據，精度最高，但是占用空間很大。
   • float占用空間比decimal小，適用於非精確數據，但會丟失數據精度。
3. 時間類型如何存儲？
   • 使用int：int 是從 1970 年開始累加的，但是 int 支持的範圍是 1901-12-13 到 2038-01-19 03:14:07，如果需要更大的範圍需要設置為 bigInt。但是這個時間不包含毫秒，如果需要毫秒，還需要定義為浮點數。
   • 使用timestamp：記錄經常變化的更新 / 創建 / 發佈 / 日誌時間 / 購買時間 / 登錄時間 / 註冊時間等，並且是近來的時間，夠用，時區自動處理，比如說做海外購或者業務可能拓展到海外。
   • 使用datetime：記錄固定時間如伺服器執行計劃任務時間 / 健身鍛煉計劃時間等，在任何時區都是需要一個固定的時間要做某個事情。

• • 資料庫設計的其他註意事項

1. 如何選擇主鍵？
   • 區分業務主鍵和資料庫主鍵：
     • 業務主鍵：用於標識業務數據，進行表與表之間的關聯。
     • 資料庫主鍵：為了優化數據存儲和查找。若沒有設置資料庫主鍵，則InnoDB引擎會自動生成6個位元組的隱含主鍵。
   • 考慮主鍵是否要自動順序增長：部分資料庫是按照主鍵的順序邏輯存儲的。
   • 主鍵的欄位類型所占用的空間要儘可能小：對於使用聚集索引方式存儲的表，每個索引都會附加上主鍵信息。
2. 避免使用外鍵（避免使用資料庫來提供外鍵約束功能）：限於互聯網項目
   • 在高併發業務中，使用外鍵約束會降低數據導入的效率，增加維護成本。
   • 建議使用邏輯外鍵，事實上在資料庫中並沒有設置外鍵約束，但在項目上都認為這是外鍵。由程式來維護外鍵約束，而不是資料庫伺服器本身來實現該功能。
   • 相關聯的列要建立索引，增加查找效率。
   • 該怎麼創建表就怎麼創建表，只是沒有了FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)該條外鍵約束命令。


例如：使用資料庫創建外鍵約束：

CREATE TABLE `m_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用戶名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `m_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `total_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `user_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  CONSTRAINT `for_indx_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　 　　不使用資料庫創建外鍵約束：

CREATE TABLE `m_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用戶名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `m_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `total_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `user_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　　　3.避免使用觸發器

• • 觸發器是一個隱藏的存儲過程,因為它不需要參數，不需要顯示調用，往往在你不知情的情況下已經做了很多操作,無形中增加了系統的複雜性。
  • 涉及到複雜的邏輯的時候，觸發器的嵌套是避免不了的，如果再涉及幾個存儲過程，再加上事務等等，很容易出現死鎖現象。
  • 存儲過程的致命傷在於移植性，存儲過程不能跨庫移植，在後期系統升級維護時難度加大。

　　　　4.謹慎使用預留欄位

• • 無法準確的知道預留欄位的類型。
  • 無法知道預留欄位中所存儲的內容。
  • 後期維護預留欄位的成本高。

　　　　　　建議：

1. 1. “按需設計”，在經過詳細有效的分析之後，在數據表中只放置必要的欄位，而不要留出大量的備用欄位。
   2. 如果數量很少，而且信息的性質與原表密切相關，那麼就可以直接在原表上動態增加欄位，並將相關的數據更新進去
   3. 如果數量較大，或者並非是原表對象至關重要的屬性，那麼就可以新增一個表，然後通過鍵值連接起來。

4.反範式化設計。

• • 什麼是反範式化？
    • 適當的違反的範式的要求，允許少量的數據冗餘，用空間換取時間。
  • 優點：增加查詢效率。

　　以上僅為本人學習過程中的知識總結，若有錯誤，還請諸位不吝賜教。