第一章 需求分析
設(shè)計簡介
根據(jù)業(yè)務(wù)需要妙啃，結(jié)合選用的DBMS疾党，設(shè)計出最有的數(shù)據(jù)存儲模型并建立好數(shù)據(jù)庫中的表結(jié)構(gòu)及表與表之間的關(guān)系使之有效的存儲和高效的訪問音诫。?
在系統(tǒng)設(shè)計開始就應(yīng)該對數(shù)據(jù)庫進(jìn)行良好的設(shè)計，這樣才能保證以后對業(yè)務(wù)發(fā)展的需要進(jìn)行改進(jìn)雪位，保證系統(tǒng)的穩(wěn)定性竭钝。?
設(shè)計步驟
? ? 需求分析：數(shù)據(jù)庫需求的作用點（數(shù)據(jù)是什么，數(shù)據(jù)有哪些屬性茧泪，數(shù)據(jù)屬性的特點）
? ? 邏輯設(shè)計：用ER圖進(jìn)行建模
? ? 物理設(shè)計：選擇數(shù)據(jù)庫管理系統(tǒng)蜓氨，根據(jù)數(shù)據(jù)庫自身的特點把邏輯設(shè)計轉(zhuǎn)換為物理設(shè)計
? ? 維護(hù)優(yōu)化：對新需求進(jìn)行見表，索引優(yōu)化队伟，大表拆分
需求分析重要性
? ? 1、了解系統(tǒng)中所要存儲的數(shù)據(jù)
? ? 2幽勒、了解數(shù)據(jù)存儲的特點
? ? 3嗜侮、了解數(shù)據(jù)的生命周期
需要了解的問題：
? ? 實體與實體之間的關(guān)系（一對一，一對多啥容，多對多）
? ? 實體所包含的屬性
? ? 哪些屬性或?qū)傩缘慕M合可以唯一標(biāo)識一個實體
實例：小型電子商務(wù)網(wǎng)站
模塊：用戶模塊锈颗，商品模塊，訂單模塊咪惠，購物車模塊击吱，供應(yīng)商模塊
用戶模塊：用于記錄注冊用戶信息
? ? 包括屬性：用戶名、密碼遥昧、電話覆醇、郵箱、身份證號炭臭、地址永脓、姓名、昵稱
? ? 可選唯一標(biāo)識屬性：用戶名鞋仍、身份證常摧、電話
? ? 存儲特點：隨系統(tǒng)上線時間逐漸增加，需要永久存儲
商品模塊：用于記錄網(wǎng)站中所有銷售的商品信息
? ? 包括屬性：商品編碼、商品名稱落午、商品描述谎懦、商品品類、供應(yīng)商名稱溃斋、重量党瓮、有效期、價格盐类。寞奸。。
? ? 可選唯一標(biāo)識屬性：（商品名稱在跳、供應(yīng)商名稱）組合枪萄、（商品編碼）
? ? 存儲特點：對于下線商品可以歸檔存儲
訂單模塊：用于用戶訂購商品的信息
? ? 包括屬性：訂單號、用戶姓名猫妙、用戶電話瓷翻、收貨地址、商品編號割坠、商品名稱齐帚、數(shù)量、價格彼哼、訂單狀態(tài)对妄、支付狀態(tài)、訂單類型敢朱。剪菱。。
? ? 可選唯一標(biāo)識屬性：訂單號
? ? 存儲特點：永久存儲（分表拴签、分庫存儲）
購物車模塊：用于 保存用戶購物時選擇的商品
? ? 包括屬性：用戶名孝常、商品編號、商品名稱蚓哩、商品價格构灸、商品描述、商品分類岸梨、加入時間喜颁、商品數(shù)量。盛嘿。洛巢。
? ? 可選唯一標(biāo)識：（用戶名、商品編號次兆、加入時間）稿茉、（購物車編號）
? ? 存儲特點：不用永久存儲（設(shè)置歸檔、清理規(guī)則）
供應(yīng)商模塊：用于保存所銷售商品的供應(yīng)商信息
? ? 包括屬性：供應(yīng)商編號、供應(yīng)商名稱漓库、聯(lián)系人恃慧、電話、營業(yè)執(zhí)照號渺蒿、地址痢士、法人。茂装。怠蹂。
? ? 可選唯一標(biāo)識：（供應(yīng)商編號），（營業(yè)執(zhí)照號）
? ? 存儲特點：永久存儲
第二章 邏輯設(shè)計
E-R圖
邏輯設(shè)計是做什么的
? ? 1少态、將需求轉(zhuǎn)化為數(shù)據(jù)庫的邏輯模型
? ? 2城侧、通過E-R圖的形式對邏輯模型進(jìn)行展示
? ? 3、同所選用的具體的DBMS系統(tǒng)無關(guān)
名詞解釋
? ? 關(guān)系：一個關(guān)系對應(yīng)通常所說的一張表
? ? 元組：表中的一行即為一個元組
? ? 屬性：表中的一列即為一個屬性彼妻；每一個屬性都有一個名稱嫌佑，稱為屬性名
? ? 候選碼：表中的某個屬性組，它可以確定一個元組
? ? 主碼：一個關(guān)系有多個候選碼侨歉，選定其中一個為主碼
? ? 域：屬性的取值范圍
? ? 分量：元組中的一個屬性值
設(shè)計范式概要
什么是數(shù)據(jù)庫設(shè)計范式
常見的數(shù)據(jù)庫設(shè)計范式包括：第一范式屋摇，第二范式，第三范式幽邓，BC范式炮温，第四、第五范式
這也是目前我們大多數(shù)數(shù)據(jù)庫設(shè)計所要遵循的范式
數(shù)據(jù)操作異常及數(shù)據(jù)冗余
? 操作異常：
? ? 插入異常：如果某實體隨著另一個實體的存在而存在颊艳，即缺少某個實體時無法表示這個實體茅特，那么這個表就存在插入異常
? ? 更新異常：如果更改表所對應(yīng)的某個實體實例的單獨屬性時，需要將多行更新棋枕，那么就說這個表存在更新異常。
? ? 刪除異常：如果刪除表中的某一行來反應(yīng)某實體實例妒峦，失效時導(dǎo)致另一個不同實體實例信息丟失重斑，那么這個表中就存在刪除異常。
? 數(shù)據(jù)冗余：
? ? 是指相同的數(shù)據(jù)在多個地方存在肯骇，或者說表中的某個列可以由其他列計算得到窥浪，這樣就說表中存在著數(shù)據(jù)冗余。
第一范式（1NF）：
定義：數(shù)據(jù)庫表中的所有字段都是單一屬性笛丙，不可再分的漾脂。這個單一屬性是由基本的數(shù)據(jù)類型所構(gòu)成的，如整數(shù)胚鸯，浮點數(shù)骨稿，字符串等；
換句話說 ?第一范式要求數(shù)據(jù)庫中的表都是二維表。
第二范式（2NF）
? 定義：數(shù)據(jù)庫中的表中不存在非關(guān)鍵字段對任一候選關(guān)鍵字段的部分函數(shù)依賴坦冠。
部分函數(shù)依賴是指存在著組合關(guān)鍵字中的某一關(guān)鍵字決定非關(guān)鍵字的情況形耗。
換句話說：所有單關(guān)鍵字段的表都符合第二范式
由于供應(yīng)商和商品之間是多對多的關(guān)系，所以只有使用商品名稱和供應(yīng)商名稱才可以唯一標(biāo)識出一件商品辙浑。也就是商品名稱和供應(yīng)商名稱是一組組合關(guān)鍵字激涤。
上表中存在以下的部分函數(shù)依賴關(guān)系
（商品名稱）->（價格，描述判呕，重量倦踢，商品有效期）
（供應(yīng)商名稱）->（供應(yīng)商電話）
存在的問題：插入異常、刪除異常侠草、更新異常辱挥、數(shù)據(jù)冗余
第三范式（3NF）?
定義：第三范式是在第二范式的基礎(chǔ)上定義的，如果數(shù)據(jù)表中不存在非關(guān)鍵字段梦抢，對任意候選關(guān)鍵字段的傳遞函數(shù)依賴則符合第三范式般贼。
存在以下傳遞函數(shù)依賴關(guān)系：
（商品名稱）->（分類）->（分類描述)
也就是說存在非關(guān)鍵字段“分類描述”
對關(guān)鍵字段“商品名稱”的傳遞函數(shù)依賴
存在問題：（分類，分類描述）對于每一個商品都會進(jìn)行記錄奥吩，所以存在著數(shù)據(jù)冗余哼蛆。同時也存在著數(shù)據(jù)的插入，更新及刪除異常
BC范式
Boyce.Codd范式（BCNF）
定義：在第三范式的基礎(chǔ)上霞赫，數(shù)據(jù)庫表中如果不存在任何字段對任一候選關(guān)鍵字段的傳遞函數(shù)依賴則符合BC范式腮介。
也就是說如果是復(fù)合關(guān)鍵字，則復(fù)合關(guān)鍵字之間也不能存在函數(shù)依賴關(guān)系端衰。
（以商品同供應(yīng)商的關(guān)系表來說明BCNF）
假定：供應(yīng)商聯(lián)系人只能受雇于一家供應(yīng)商叠洗，每家供應(yīng)商可以供應(yīng)多個商品，則存在如下決定關(guān)系：
（供應(yīng)商旅东，商品ID）->（聯(lián)系人灭抑，商品數(shù)量）
（聯(lián)系人，商品ID）->（供應(yīng)商抵代，商品數(shù)量）
存在下列關(guān)系因此不符合BCNF要求：
（供應(yīng)商）->（供應(yīng)商聯(lián)系人）
（供應(yīng)商聯(lián)系人）->（供應(yīng)商）
并且存在數(shù)據(jù)操作異常及數(shù)據(jù)冗余
第三章 物理設(shè)計
物理設(shè)計要做什么
? ? 1腾节、選擇合適的數(shù)據(jù)庫管理系統(tǒng)
? ? 2、定義數(shù)據(jù)庫荤牍、表及字段的命名規(guī)范
? ? 3案腺、根據(jù)所選的DBMS系統(tǒng)選擇合適的字段類型（效率，功能康吵，需求）
? ? 4劈榨、反范式化設(shè)計（冗余）
選擇哪種數(shù)據(jù)庫
成本、版權(quán)晦嵌、功能（性能）同辣、操作系統(tǒng)拷姿、開發(fā)語言、應(yīng)用場景
mysql常用的存儲引擎
開源數(shù)據(jù)庫邑闺，只要符合mysql存儲協(xié)議跌前，任何人都可以開發(fā)存儲引擎
主要使用Innodb存儲引擎
表及字段的命名規(guī)范
所有對象命名應(yīng)該遵循下述原則：
? ? 1、可讀性原則：使用大寫和小寫格式化的庫對象名字已獲得良好的可讀性陡舅。
例如：使用CustAddress而不是custaddress來提高可讀性抵乓。
（這里要注意有些DBS系統(tǒng)對表名的大小寫是敏感的）
? ? 2、表意性原則：對象的名字應(yīng)該能夠描述它所標(biāo)識的對象靶衍。
例如：對于表灾炭，表的名稱應(yīng)該能夠體現(xiàn)表中存儲的數(shù)據(jù)內(nèi)容；
對于存儲過程颅眶，存儲過程名稱應(yīng)該能夠體現(xiàn)存儲過程的功能蜈出。
? ? 3、長名原則：盡可能少使用或者不使用縮寫涛酗，適用于數(shù)據(jù)庫（DATABASE）名之外的任一對象铡原。
字段類型選擇原則
? ? 列的數(shù)據(jù)類型一方面影響數(shù)據(jù)存儲空間的開銷，另一方面也會影響數(shù)據(jù)查詢性能商叹。當(dāng)一個列可以選擇多種數(shù)據(jù)類型時燕刻，應(yīng)該優(yōu)先考慮數(shù)字類型，其次是日期或者二進(jìn)制類型，最后是字符類型。對于相同級別的數(shù)據(jù)類型，應(yīng)該優(yōu)先選擇占用空間小的數(shù)據(jù)類型狂鞋。
如何具體選擇字段類型
char與varchar如何選擇
? ? 原則：
? ? ? ? 1字管、如果列中要存儲的數(shù)據(jù)長度差不多是一致的硫戈，則應(yīng)該考慮用char；否則應(yīng)該考慮用varchar。
? ? ? ? 2、如果劣種的最大數(shù)據(jù)長度小于50Byte猩谊，則一般也考慮用char。
? ? ? ? 3、一般不宜定義大于50Byte的char類型列。
utf8每個字符占三個字節(jié)
decimal與float類型如何選擇
? ? 原則：
? ? ? ? 1、decimal用于存儲精確數(shù)據(jù)，而float只能用于存儲非精確數(shù)據(jù)七蜘。
? ? ? ? 2损搬、由于float的存儲空間開銷一般比decimal刑吖亍（精確到7位小數(shù)只需要4個字節(jié)，而精確到15位小數(shù)只需要8字節(jié)）故非精確數(shù)據(jù)優(yōu)先選擇float類型搂鲫。
數(shù)據(jù)庫設(shè)計的其他注意事項
時間類型存儲：
? ? 1擦酌、使用int來存儲時間字段的優(yōu)缺點
? ? ? ? 優(yōu)點：字段長度比datetime小睁搭。
? ? ? ? 缺點：使用不方便，要進(jìn)行函數(shù)轉(zhuǎn)換笼平。
? ? ? ? 限制：只能存儲到2038-1-19 ?11：14：07即2^32為2147483648
? ? 2园骆、需要存儲的時間粒度
? ? ? ? 年 ?月 ?日 ?時 ?分 ?秒 ?周
數(shù)據(jù)庫設(shè)計其他注意事項
? ? 如何選擇主鍵
? ? ? ? 1、區(qū)分業(yè)務(wù)主鍵和數(shù)據(jù)庫主鍵寓调，業(yè)務(wù)主鍵用于標(biāo)識業(yè)務(wù)數(shù)據(jù)遇伞，進(jìn)行表與表之間的關(guān)聯(lián)；數(shù)據(jù)庫主鍵為了優(yōu)化數(shù)據(jù)存儲（Inoodb會生成6個字節(jié)的隱含主鍵）
? ? ? ? 2捶牢、根據(jù)數(shù)據(jù)庫的類型鸠珠，考慮主鍵是否要順序增長，有些數(shù)據(jù)庫是按主鍵的順序邏輯存儲的
? ? ? ? 3秋麸、逐漸的字段類型所占空間要盡可能的小渐排，對于使用聚集索引方式存儲的表，每個索引后都會附加主鍵信息灸蟆。
? ? 避免使用外鍵約束
? ? ? ? 1驯耻、降低數(shù)據(jù)導(dǎo)入的效率
? ? ? ? 2、增加維護(hù)成本
? ? ? ? 3炒考、雖然不建議使用外鍵約束可缚，但是相關(guān)聯(lián)的列上一定要建立索引
? ? 避免使用觸發(fā)器
? ? ? ? 1、降低數(shù)據(jù)導(dǎo)入的效率
? ? ? ? 2斋枢、可能會出現(xiàn)意想不到的數(shù)據(jù)異常帘靡。
? ? ? ? 3、使業(yè)務(wù)邏輯變得復(fù)雜
? ? 關(guān)于預(yù)留字段
? ? ? ? 1瓤帚、無法準(zhǔn)確的知道預(yù)留字段的類型描姚。
? ? ? ? 2、無法準(zhǔn)確的知道預(yù)留字段中所存儲的內(nèi)容戈次。
? ? ? ? 3轩勘、后期維護(hù)預(yù)留字段所需要的成本，同增加一個字段所需要的成本是相同的怯邪。
? ? ? ? 4绊寻、嚴(yán)禁使用預(yù)留字段
反范式化表設(shè)計
? ? 什么是反范式化
? ? ? ? 反范式化是針對范式化而言的，所謂的反范式化就是為了性能和讀取效率的考慮而適當(dāng)?shù)膶Φ谌妒降囊筮M(jìn)行違反悬秉，而允許存在少量的數(shù)據(jù)冗余澄步。換句話說反范式化就是使用空間來換取時間。
? ? 為什么反范式化
? ? ? ? 1搂捧、減少表的關(guān)聯(lián)數(shù)量
? ? ? ? 2驮俗、增加數(shù)據(jù)的讀取效率
? ? ? ? 3、反范式化一定要適度
第四章 維護(hù)和優(yōu)化
維護(hù)和優(yōu)化要做什么
? ? 1允跑、維護(hù)數(shù)據(jù)字典
? ? 2王凑、維護(hù)索引
? ? 3、維護(hù)表結(jié)構(gòu)
? ? 4聋丝、在適當(dāng)?shù)臅r候?qū)Ρ磉M(jìn)行水平拆分或垂直拆分
如何維護(hù)數(shù)據(jù)字典
? ? 1索烹、使用第三方工具對數(shù)據(jù)字典進(jìn)行維護(hù)
? ? 2、利用數(shù)據(jù)庫本身的備注字段來維護(hù)數(shù)據(jù)字典弱睦。以mysql為例
CREATE TABLE costomer(
cust_id INT AUTO_INCREMENT NOT NULL COMMENT '自增ID'百姓，
cust_name VARCHAR(10) NOT NULL COMMENT '客戶姓名',
PRIMARY KEY (cust_id)
) COMMENT '客戶表'
? ? 3、導(dǎo)出數(shù)據(jù)字典
SELECT
a.table_name,b.TABLE_COMMENT,a.COLUMN_NAME,
a.COLUMN_TYPE,a.COLUMN_COMMENTFROM
information_schema.COLUMENS a JOIN information_schema.
TABLE b ON a.table_schema=b.table_schema AND
a.table_name=b.table_name
WHERE a.table_name='customer'
如何維護(hù)索引
? ? 如何選擇合適的列建立索引况木？
? ? ? ? 1垒拢、出現(xiàn)在WHERE從句旬迹，GROUP BY 從句，ORDER BY 從句中的列
? ? ? ? 2求类、可選擇性高的列要放到索引的前面
? ? ? ? 3奔垦、索引中不要包括太長的數(shù)據(jù)類型
? ? 注意事項
? ? ? ? 1、索引并不是越多越好尸疆，過多的索引不但會降低寫效率椿猎，而且會降低讀的效率
? ? ? ? 2、頂起維護(hù)索引碎片
? ? ? ? 3寿弱、在SQL語句中不要使用強(qiáng)制索引關(guān)鍵字
數(shù)據(jù)庫中適合的操作
如何維護(hù)表結(jié)構(gòu)
注意事項：
? ? 1犯眠、使用在線變更表結(jié)構(gòu)的工具
? ? ? ? MySql5.5之前可以使用pt-online-schema-change
? ? ? ? MySql5.6之后本身支持在線表結(jié)構(gòu)的變更
? ? 2、同時對數(shù)據(jù)字典進(jìn)行維護(hù)
? ? 3症革、控制表的寬度和大小
數(shù)據(jù)庫中適合的操作
? ? 1筐咧、批量操作VS逐條操作
? ? 2、禁止使用SELECT *這樣查詢
? ? 3地沮、控制使用用戶自定義函數(shù)
? ? 4嗜浮、不要使用數(shù)據(jù)庫中的全文索引
表的垂直拆分和水平拆分
