資料庫優化之結構設計

設計好處

• 良好的資料庫邏輯設計和實體設計師資料庫獲得高性能的基礎
• 範式化設計和反範式化設計（減少備援、減少異常、讓資料組織的更加和諧）
• 優化目的
  • 減少資料備援（盡量）
  • 盡量避免資料維護中出現更新、插入和删除等異常
    • 插入：如果表中的某個實體随着另一個實體而存在
    • 更新：如果更改表中的某個實體的單獨屬性時，需要對多表進行更新
    • 删除：如果删除表中的某一時則會導緻其他實體的消失

設計過程

• 需求分析
  • 全面了解産品設計的需求
  • 存儲需求（比如一對多，多對一等）
  • 資料處理需求
  • 資料的安全性和完整性
• 邏輯分析
  • 設計資料的邏輯存儲結構
  • 資料實體之前的邏輯關系，解決資料備援和資料維護異常
• 實體設計
  • 根據所使用資料特點設計表結構
• 維護優化
  • 對索引、存儲結構等進行優化
• 範式化
  • 設計沒有資料備援和資料維護異常的資料庫結構
• 反範式化
  • 針對範式化而言的，在前面介紹了資料庫設計的範式，所謂的反範式化就是為了性能和讀取效率的考慮而适當的對資料庫設計範式的要求進行違法，而允許存在少量的資料備援，換句話來說反範式化就是使用空間來換取時間

本篇重點解釋實體設計、範式化與反範式化化各自優缺點；其他将在文章《資料庫優化》系列一一講明；

範式化與反範式化

• 範式化設計的優缺點
  • 優點
    • 盡量減少資料備援
    • 範式化的更新操作比反範式化更快
    • 範式化的表通常比反範式更小
  • 缺點
    • 對于查詢需要對多個表進行關聯（mysql限制不能超過10張表）
    • 更難進行索引優化
• 反範式化設計的優缺點
  • 優點
    • 減少表的關聯
    • 更好的進行索引優化
  • 缺點
    • 存在資料備援及資料庫維護異常
    • 對資料修改需要更多的成本

設計範式化要求的三範式：

第一範式

* 資料庫表中的所有字段都隻具有單一屬性

* 單一屬性的列是由基本的資料類型所構成的

* 設計及出來的表都是簡單的二維表

第二範式

* 要求一個表中隻具有一個業務主鍵，也就是說符合第二範式的表中不能存在非主鍵列對隻對部分主鍵的依賴關系

第三範式

* 資料不能存在傳遞關系，即每個屬性都跟主鍵有直接關系而不是間接關系

實體設計

• 實體設計的内容
  • 定義資料庫、表及字段的命名規範
  • 選擇合适的存儲引擎
  • 為表中的字段選擇合适的資料類型
  • 建立資料庫結構
• 定義資料庫、表及字段的命名規範
  • 可讀性原則
  • 表意行原則
  • 長名原則
• 選擇合适的存儲引擎

存儲引擎	事務	鎖粒度	主要應用	忌用
MyISAM	不支援	支援并發插入的表級鎖	SELECT、INSERT	讀寫操作頻繁
MRG_MYISAM	不支援	支援并發插入的表級鎖	分段歸檔，資料倉庫	全局查找過多的場景
Innodb	支援	支援MVCC的行級鎖	事務處理	無
Archive	不支援	行級鎖	日志記錄，隻支援insert，select	需要随機讀取，更新，删除
Ndb cluster	支援	行級鎖	高可用性	大部分應用

• 為表中的字段選擇合适的資料類型（資料頁）
  • 當一個列可以選擇多種資料類型時，應該優先考慮數字類型，其次是日期或二進制類型，最後是字元類型。對于相同級别的資料類型，應該優先選擇占用空間小的資料類型
• 如何為Innodb選擇主鍵
  • 主鍵應該盡可能的小
  • 主鍵應該是順序增長的
  • Innodb的主鍵和業務主鍵可以不同；

相關連結

《資料庫優化之執行個體和故事》、《 資料庫優化之什麼影響性能》

作者：不動峰

部落格園：http://www.cnblogs.com/mylly/

版權所有，歡迎保留原文連結進行轉載：)