原文地址: http://www.reibang.com/p/a7823880e6d9
本文為慕課網(wǎng)的《數(shù)據(jù)庫設計那些事》的學習筆記,系統(tǒng)的整理了數(shù)據(jù)庫設計的相關概念和整個流程,并加入了一些內容和鏈接來補充.
1. 數(shù)據(jù)庫設計簡介
建立數(shù)據(jù)庫應用系統(tǒng)的設計看作檢大廈的過程枷恕。大廈建立之前滓鸠，要先設計把还、繪制圖紙、按照圖紙建設大廈选脊。數(shù)據(jù)庫相當于我們大廈的地基。
1.1 什么是數(shù)據(jù)庫設計凹耙？
就是根據(jù)業(yè)務系統(tǒng)的具體要求嗦篱，結合我們所選用的數(shù)據(jù)庫管理系統(tǒng)，為這個業(yè)務系統(tǒng)構造出最優(yōu)的數(shù)據(jù)存儲模型纹腌，并建立好數(shù)據(jù)庫中的表結構及表與表之間的關聯(lián)關系的過程霎终。使之能有效的對應用系統(tǒng)中的數(shù)據(jù)進行存儲，并可以高效地對已經(jīng)存儲的數(shù)據(jù)進行訪問升薯。
1.2 常見的數(shù)據(jù)庫管理系統(tǒng)
- 關系型: mysql莱褒、oracle、sql server涎劈、postgres等
- 非關系型: mongo广凸、memcache、redis
1.3 為什么要進行數(shù)據(jù)庫設計蛛枚？
數(shù)據(jù)庫系統(tǒng)是應用系統(tǒng)存儲數(shù)據(jù)的關鍵組成部分谅海，是系統(tǒng)穩(wěn)定運行的基礎，決定著系統(tǒng)是否可以高效地運行蹦浦。
- 優(yōu)良的設計：減少數(shù)據(jù)冗余扭吁，避免數(shù)據(jù)維護異常，節(jié)約空間盲镶，高效訪問智末。
- 糟糕的設計：存在大量的數(shù)據(jù)冗余，存在數(shù)據(jù)插入徒河、更新系馆、刪除異常，浪費大量存儲空間顽照，訪問數(shù)據(jù)低效由蘑。
2. 數(shù)據(jù)庫設計的步驟
- 數(shù)據(jù)庫設計的步驟:
需求分析-〉邏輯設計-〉物理設計-〉維護優(yōu)化 - 數(shù)據(jù)庫需求的作用點：數(shù)據(jù)是什么、數(shù)據(jù)有哪些屬性代兵、數(shù)據(jù)和屬性各自的特點有哪些
- 邏輯設計：使用ER圖對數(shù)據(jù)庫進行邏輯建模尼酿，之后就可以在大多數(shù)數(shù)據(jù)庫管理系統(tǒng)中進行表的設計
- 物理設計: 根據(jù)數(shù)據(jù)庫自身的特點把邏輯設計轉換為物理設計。
- 維護設計：根據(jù)新的需求從新進行相關的邏輯設計植影、建表裳擎、索引優(yōu)化、大表拆分等等
2.1 需求分析
需求分析過程中需要了解的重要內容,直接進行表設計導致設計出來的表不是最優(yōu)化的思币，必須首先進行需求分析鹿响。
- 了解系統(tǒng)中所要存儲的數(shù)據(jù)有哪些
- 了解數(shù)據(jù)的存儲特點：比如有些有時效性羡微，定期歸檔和清理。還有些數(shù)據(jù)增長很快惶我，數(shù)據(jù)量也很大妈倔，但他不是系統(tǒng)的核心數(shù)據(jù)，分庫绸贡、分表方式存儲盯蝴。比如日志定期歸檔和清理
- 了解數(shù)據(jù)的生命周期
要搞清一些問題:
- 實體（對象）與實體之間的關系 實體與表的對應關系、實體與實體的對應關系 1對1听怕，隊多捧挺，多對多，
- 實體所包含的屬性有什么尿瞭？
- 哪些屬性或屬性的組合可以唯一標識一個實體闽烙，
示例:
購物車模塊:
用于保存用戶購物是選對的商品
包括屬性：用戶名、商品編號筷厘、商品名稱鸣峭、商品價格宏所、商品描述酥艳、商品分類，加入時間爬骤，商品數(shù)量
可選唯一標識：（用戶名充石、商品編號、加入時間）霞玄、（購物車編號）
存儲特點：不用永久存儲（設置歸檔骤铃、清理規(guī)則）
供應商模塊:
用于保存所銷售商品的供應商信息
包括屬性：供應商上編號、供應商名稱坷剧、聯(lián)系人惰爬、電話、營業(yè)執(zhí)照號惫企、地址撕瞧、法人。狞尔。丛版。
可選唯一標識：（供應商編號），（營業(yè)執(zhí)照號）
存儲特點：永久存儲
2.2 邏輯設計
- 將需求轉化為數(shù)據(jù)庫的邏輯模型
- 通過ER圖的形式對邏輯模型進行展示
- 同所選用的具體的數(shù)據(jù)庫管理系統(tǒng)無關
2.3 物理設計
流程:
- 選擇合適的數(shù)據(jù)庫管理系統(tǒng)(DBMS: oracle,mysql等).
- 定義數(shù)據(jù)庫独令、表及字段的命名規(guī)范.
- 根據(jù)所選的DBMS系統(tǒng)選擇合適的字段類型.
- 反規(guī)范設計.
2.3.1 mysql常見存儲引擎(以mysql為例)
2.3.2 表以及字段的命名規(guī)范
- 可讀性原則: 使用大寫和小寫來格式化的庫對象名字以獲得良好的可讀性.例如：使用CustAddress 而不是custaddress
- 表意性原則: 名稱體現(xiàn)意義
- 長名原則：盡可能少使用或者不使用縮寫
2.3.3 字段類型的選擇原則
- 考慮存儲開銷
- 考慮查詢性能
2.3.4 數(shù)據(jù)庫設計其他注意事項
- a. 如何選擇主鍵:
- 區(qū)分業(yè)務主鍵和數(shù)據(jù)庫主鍵
- 業(yè)務主鍵用于標識業(yè)務數(shù)據(jù)，進行表與表之間的關聯(lián)
- 數(shù)據(jù)庫主鍵為了優(yōu)化數(shù)據(jù)庫存儲（Innodb會生成6個字節(jié)的隱含主鍵）
- 根據(jù)數(shù)據(jù)庫的類型州胳，考慮主鍵是否要順序增長
- 有些數(shù)據(jù)庫是按主鍵的順序邏輯存儲的
- 主鍵的字段類型所占空間要盡可能的小
- 對于使用聚集索引方式存儲的表记焊，每個索引后都會附加主鍵信息
- 區(qū)分業(yè)務主鍵和數(shù)據(jù)庫主鍵
- b. 避免使用外鍵約束, 原因：
- 降低數(shù)據(jù)導入的效率
- 增加維護成本
- 雖然不建議使用外鍵約束，但是相關關聯(lián)的列上一定要建立索引
- c. 避免使用觸發(fā)器, 原因：
- 降低數(shù)據(jù)導入的效率
- 可能會出現(xiàn)意想不到的數(shù)據(jù)異常
- 使業(yè)務邏輯變的復雜
- d. 關于預留字段, 原因：
- 無法準確的知道預留字段的類型
- 無法準確的知道預留字段中所存儲的內容
- 后期維護預留字段所要的成本栓撞，同增加一個字段所需要的成本是相同的
- 嚴禁使用預留字段
2.3.5 反范式化表設計
反范式化：允許存在少量的數(shù)據(jù)冗余遍膜，適當對第三范式進行違反,
目的：使用空間來換取時間，提高性能和讀寫效率瓤湘。
為什么反范式化:
- 減少表的關聯(lián)數(shù)量
- 增加數(shù)據(jù)的讀取效率
- 反范式化一定要適度
示例:
2.4 維護優(yōu)化
維護和優(yōu)化的工作：
- 維護數(shù)據(jù)字典
- 維護索引
- 維護表結構
- 在適當?shù)臅r候對表進行水平拆分和垂直拆分
2.4.1 如何維護數(shù)據(jù)字典
- 使用第三方工具對數(shù)據(jù)字典進行維護
- 利用數(shù)據(jù)庫本身的備注字段來維護數(shù)據(jù)字典
2.4.2 如何維護索引
如何選擇合適的列建立索引:
- 出現(xiàn)在WHERE從句瓢颅，GROUP BY從句，ORDER BY從句中的列
- 可選性高的列要放到索引的前面
- 索引中不要包括太長的數(shù)據(jù)庫類型
注意事項: - 索引并不是越多越好弛说，過多的索引不但會降低寫效率挽懦，而且會降低讀的效率
- 定期維護索引碎片
- 在SQL 語句中不要使用強制索引關鍵字
2.4.3 維護表結構
注意事項
- 使用在線變更表結構的工具
- 同時對數(shù)據(jù)庫字典進行維護
- 控制表的寬度和大小
數(shù)據(jù)庫中適合的操作 - 批量操作,逐條操作都有,推薦使用批量操作
- 禁止使用
Select *這樣的查詢 - 控制使用用戶自定義函數(shù)
- 不要使用數(shù)據(jù)庫中的全文索引
2.4.4 對表進行拆分
表的垂直拆分: 為了控制表的寬度可以進行表的垂直拆分
1.經(jīng)常一起查詢的列放到在一起
2.text，blob等大字段拆分到附加表中
表的水平拆分: 為了控制表的大小可以進行表的水平拆分
- 通過主鍵hash key的操作(按模取值)
- 時區(qū)段域拆分
- 表業(yè)務類型拆分
2.4.5 其他
數(shù)據(jù)庫分區(qū):
- range分區(qū)
- list分區(qū)
- hash分區(qū)
- key分區(qū)
- 子分區(qū)
