MySQL8.0實戰(zhàn)(二) - 數(shù)據(jù)庫的建模

0 Github

1 簡介

數(shù)據(jù)庫設(shè)計(Database Design)是指對于一個給定的應(yīng)用環(huán)境皮迟,構(gòu)造最優(yōu)的數(shù)據(jù)庫模式,建立數(shù)據(jù)庫及其應(yīng)用系統(tǒng)桑寨,使之能夠有效地存儲數(shù)據(jù)伏尼,滿足各種用戶的應(yīng)用需求(信息要求和處理要求)。在數(shù)據(jù)庫領(lǐng)域內(nèi)尉尾,常常把使用數(shù)據(jù)庫的各類系統(tǒng)統(tǒng)稱為數(shù)據(jù)庫應(yīng)用系統(tǒng)爆阶。
數(shù)據(jù)庫設(shè)計的設(shè)計內(nèi)容包括:需求分析、概念結(jié)構(gòu)設(shè)計沙咏、邏輯結(jié)構(gòu)設(shè)計辨图、物理結(jié)構(gòu)設(shè)計、數(shù)據(jù)庫的實施和數(shù)據(jù)庫的運行和維護(hù)肢藐。

2 數(shù)據(jù)庫建模五部曲

3 需求總結(jié)

◆ 課程的屬性:{主標(biāo)題,副標(biāo)題,方向,分類故河,難度最新最熱,時長,簡介,人數(shù),需知,收獲,講師名講師職位,課程圖片綜合評分,內(nèi)容實用,簡潔易懂,邏輯清晰}

◆ 課程列表的屬性:{章名,小節(jié)名, 說明,小節(jié)時長,章節(jié)URL,視頻格式}

◆ 講師的屬性:{講師昵稱,說明吆豹,性別,省,市,職位說明,經(jīng)驗,積分,關(guān)注人數(shù),粉絲人數(shù)}

◆ 問答評論屬性:{類型,標(biāo)題,內(nèi)容鱼的,關(guān)聯(lián)章節(jié),瀏覽量,發(fā)布時間,用戶昵稱}

◆ 筆記的屬性:{用戶昵稱,關(guān)聯(lián)章節(jié) 筆記標(biāo)題,筆記內(nèi)容,發(fā)布時間}理盆。

◆ 用戶的屬性:{用戶昵稱密碼,說明,性別,省凑阶,市,職位猿规,說明,經(jīng)驗,積分,關(guān)注人數(shù)粉絲人數(shù)}

◆評價的屬性:{用戶,課程主標(biāo)題,內(nèi)容,綜合評分,內(nèi)容實用,簡潔易懂,邏輯清晰,發(fā)布時間}

4 寬表模式

  • 百度百科定義
    從字面意義上講就是字段比較多的數(shù)據(jù)庫表宙橱。通常是指業(yè)務(wù)主題相關(guān)的指標(biāo)姨俩、維度、屬性關(guān)聯(lián)在一起的一張數(shù)據(jù)庫表师郑。由于把不同的內(nèi)容都放在同一張表存儲哼勇,寬表已經(jīng)不符合三范式的模型設(shè)計規(guī)范,隨之帶來的主要壞處就是數(shù)據(jù)的大量冗余呕乎,與之相對應(yīng)的好處就是查詢性能的提高與便捷。這種寬表的設(shè)計廣泛應(yīng)用于數(shù)據(jù)挖掘模型訓(xùn)練前的數(shù)據(jù)準(zhǔn)備陨晶,通過把相關(guān)字段放在同一張表中猬仁,可以大大提高數(shù)據(jù)挖掘模型訓(xùn)練過程中迭代計算時的效率問題。

◆ 課程的屬性:{主標(biāo)題,副標(biāo)題,方向,分類難度最新先誉,最熱,時長,簡介,人數(shù),需知,收獲,講師名,講師職位,課程圖片綜合評分湿刽,內(nèi)容實用,簡潔易懂,邏輯清晰}

  • 實例


4.1 模式存在的問題

4.1.1 更新異常

修改一行中某列的值時,同時修改了多行數(shù)據(jù)

例如當(dāng)使用



想修改其職位時,不止影響一條數(shù)據(jù)
那么,我們再加個限定條件



就可以只修改一行數(shù)據(jù),因此我們可以將主標(biāo)題作為該數(shù)據(jù)表的唯一標(biāo)識,即主鍵!
通過主鍵更新數(shù)據(jù),雖然可以避免數(shù)據(jù)的更新異常,但也可能會造成表中的數(shù)據(jù)不一致現(xiàn)象,比如該實例中,講師的職稱就會產(chǎn)生多義.

4.1.2 插入異常

部分?jǐn)?shù)據(jù)由于缺失主鍵信息而無法寫入表中

例如,我們想新增Java開發(fā)方向的課程



由于執(zhí)行該語句時,PK為空,即違反了PK非空且唯一的約束條件,因此該語句無法成功.

4.1.3 刪除異常

刪除某一數(shù)據(jù)時不得不刪除另一數(shù)據(jù)

例如,我們想刪除數(shù)據(jù)庫方向


我們只是單純想刪除數(shù)據(jù)庫方向而已,但該語句卻將許多課程也刪除了,這并不符合我們的預(yù)期.

4.1.4 數(shù)據(jù)冗余

相同的數(shù)據(jù)在一個表中出現(xiàn)了多次

那么是不是這么多問題就意味著寬表一無是處呢?存在即合理!

4.2 模式的適用場景

配合列存儲的數(shù)據(jù)報表應(yīng)用

由于寬表中,所有數(shù)據(jù)存在于一個表中,因此在查詢時,無需多表查詢,SQL執(zhí)行效率較高,且存在的上述問題在報表應(yīng)用中都不是大問題

既然寬表不適合我們的當(dāng)前業(yè)務(wù),那么怎么尋找合適的方法呢?

5 數(shù)據(jù)庫設(shè)計范式

5.1 第一范式

表中的所有字段都是不可再分的

例如以下實例中的聯(lián)系方式是一個復(fù)合屬性,明顯就違反了該范式,在數(shù)據(jù)庫中是無法分離出來的

我們只需對其進(jìn)行簡單的改動即可


即標(biāo)準(zhǔn)的二維表.

5.2 第二范式

前提

標(biāo)準(zhǔn)的二維表,即第一范式成立

表中必須存在業(yè)務(wù)主鍵,并且非主鍵依賴于全部業(yè)務(wù)主鍵

例如如下博客表實例


  • 使用用戶字段作為PK是否可行呢?
    顯然一個用戶會對應(yīng)多個博客記錄,且章節(jié)標(biāo)題也能為多個用戶編輯,所以單列字段PK失效

  • 使用<用戶,章節(jié),標(biāo)題>的復(fù)合PK
    然而用戶積分字段也只和用戶字段依賴,并不依賴于整體的PK,所以依舊不符合第二范式

  • 拆分將依賴的字段單獨成表



從上面,我們也可以發(fā)現(xiàn):

  • 若表的PK只有一個字段組成,那么它本就符合第二范式
  • 若是多個字段組成,則需考量是否符合第二范式

5.3 第三范式

表中的非主鍵列之間不能相互依賴

依舊看看課程表


首先,一個字段的PK顯然符合第二范式,大部分字段也只依賴于PK,然而對于講師職稱字段其實是依賴于講師名的,所以不符合第三范式.

  • 將不與PK形成依賴關(guān)系的字段直接提出單獨成表即可



6 課程實體的邏輯建模

屬性

{主標(biāo)題,副標(biāo)題,方向,分類,難度,最新,最熱,時長,簡介,人數(shù),需知,收獲,講師名講師職位,課程圖片綜合評分,內(nèi)容實用,簡潔易懂,邏輯清晰}

我們顯然可以將其拆分如下:

課程表

主標(biāo)題(PK),副標(biāo)題,方向,分類,難度,上線時間,學(xué)習(xí)人數(shù),時長褐耳,簡介,需知,收獲,講師昵稱,課程圖片,綜合評分诈闺,內(nèi)容實用,簡潔易懂,邏輯清晰

講師表

講師名及講師的職稱

其中最新屬性即對應(yīng)著上線時間計算得出,業(yè)務(wù)上可規(guī)定時間段判斷是否為最新
最熱屬性即可以學(xué)習(xí)人數(shù)字段排序來反映

課程方向表

課程方向名稱(PK) : 在課程表中有對應(yīng)的方向字段
添加時間

課程分類表

分類名稱(PK) : 在課程表中有對應(yīng)的方向字段
添加時間

課程難度表

課程難度(PK) : 在課程表中有對應(yīng)的方向字段
添加時間

7 課程列表實體的邏輯建模

屬性

[章節(jié)名,小節(jié)名](聯(lián)合PK)
說明,小節(jié)時長,章節(jié)URL,視頻格式

其中,說明其實只依賴于章節(jié)名
小節(jié)時長铃芦,小節(jié)URL,視頻格式都只依賴于小節(jié)名
違反第二范式,所以需要拆分字段

課程章節(jié)表

章節(jié)名(PK),說明,章節(jié)編號

課程與章節(jié)的聯(lián)系表

主標(biāo)題,章節(jié)名

課程小節(jié)表

小節(jié)名稱(PK),小節(jié)視頻url,視頻格式,小節(jié)時長,小節(jié)編號

課程章節(jié)與小節(jié)的聯(lián)系表

主標(biāo)題,章節(jié)名,小節(jié)名

8 講師實體的邏輯建模

屬性

講師名,密碼,性別,省,市,職稱,說明,經(jīng)驗,積分,關(guān)注數(shù),粉絲數(shù)

講師表

講師名(PK),密碼,性別,省,市,職稱,說明,經(jīng)驗,積分,關(guān)注數(shù),粉絲數(shù)

9 用戶實體的邏輯建模

屬性

用戶昵稱雅镊,密碼,性別刃滓,省市,職位,說明,經(jīng)驗仁烹,積分,關(guān)注數(shù),粉絲數(shù)

用戶表V1.0

用戶昵稱(PK),密碼咧虎,性別卓缰,省市,職位,說明,經(jīng)驗,積分,關(guān)注數(shù),粉絲數(shù)

和講師表基本相同,且講師其實也是一種用戶,講師的信息就會被存儲兩次,造成數(shù)據(jù)的冗余.,于是就難以保持?jǐn)?shù)據(jù)一致性!考慮合并!

用戶表V2.0

用戶昵稱(PK)砰诵,密碼征唬,性別,省市,職位,說明,經(jīng)驗茁彭,積分,關(guān)注數(shù),粉絲數(shù),講師標(biāo)識

10 問答評論實體的邏輯建模

屬性

類型,標(biāo)題,內(nèi)容關(guān)聯(lián)章節(jié),瀏覽量,發(fā)布時間,用戶昵稱

其中標(biāo)題文字是共享的,無法保持一致
同一用戶在不同章節(jié)提出的問題也可能相同
因此決定采用標(biāo)題+用戶昵稱+關(guān)聯(lián)章節(jié)作為PK

評論表

如何記錄關(guān)聯(lián)章節(jié)字段呢?
是不是只能用課程章節(jié)的PK來記錄呢?
因此,不得不將課程章節(jié)的關(guān)聯(lián)表PK加入


[標(biāo)題,課程主標(biāo)題,課程章名,小節(jié)名稱,用戶呢稱](PK)
父評論(被回復(fù)的問題/標(biāo)題)
標(biāo)題,內(nèi)容,類型,瀏覽量,發(fā)布時間

11 筆記實體的邏輯建模

屬性

用戶昵稱,關(guān)聯(lián)章節(jié),筆記標(biāo)題,筆記內(nèi)容总寒,發(fā)布時間

和評論實體差不多,分析不再贅述

筆記表

[筆記標(biāo)題,課程主標(biāo)題,課程章名,小節(jié)名稱,用戶呢稱](PK)
內(nèi)容,發(fā)布時間

12 評價實體的邏輯建模

屬性

用戶呢稱;課程主標(biāo)題,內(nèi)容,綜合評分,內(nèi)容實用,簡潔易懂,邏輯清晰,發(fā)布時間

評價表

[用戶呢稱;課程主標(biāo)題](PK)
內(nèi)容,綜合評分,內(nèi)容實用,簡潔易懂,邏輯清晰尉间,發(fā)布時間

只有選擇/購買了課程的用戶才能評價!!!
需要用戶與所選課程的關(guān)聯(lián)關(guān)系表

用戶選課表

[用戶呢稱;課程主標(biāo)題](PK)
選課時間,累積聽課時長

13 小結(jié)




14 范式化暴露的問題

如果我們想要查詢出一門課程包括所有章節(jié)和小節(jié)的相關(guān)信息



那么這些信息又是如何存儲的呢,需要查詢哪些表呢?如下所示


我們就要關(guān)聯(lián)5個表,查詢效率極低!且查詢課程信息的需求很大!
為了提高性能,我們還需要對表結(jié)構(gòu)進(jìn)行優(yōu)化操作

15 反范式化設(shè)計

空間換時間的思想

15.1 課程章節(jié)表反范式化設(shè)計


上述表存在一對多的關(guān)系

所以可以并不需要關(guān)聯(lián)關(guān)系表,而是呢可以直接把課程表和課程&章節(jié)聯(lián)系表合并


成為新的課程章節(jié)表
[主標(biāo)題,章節(jié)名](PK),說明,章節(jié)編號

雖然違反了第二范式,但是減少了一個表的查詢,提高了查詢性能,在頻繁查詢操作的系統(tǒng)中,這很值得!

經(jīng)過反范式化后,我們只需要查詢?nèi)齻€表即可


15.2 反范式化設(shè)計小結(jié)

課程相關(guān)表數(shù)量 5 -> 3


16 常用存儲引擎

17 InnoDB存儲引擎的特點

  • 事務(wù)型存儲引擎支持ACID
  • 數(shù)據(jù)按主鍵聚集存儲
  • 支持行級鎖及MVCC
  • 支持Btree和自適應(yīng)Hash索引
  • 支持全文和空間索引

18 根據(jù) InnoDB特性優(yōu)化后的表邏輯結(jié)構(gòu)

通過數(shù)據(jù)冗余避免數(shù)據(jù)不一致


課程章節(jié)表:{章節(jié)ID(PK),課程ID偿乖,章節(jié)名稱,章節(jié)說明,章節(jié)編號}

課程小節(jié)表:{小節(jié)ID(PK),課程ID,章節(jié)ID,小節(jié)名稱,小節(jié)視頻url,視頻格式击罪,小節(jié)時長,小節(jié)編號}。

課程方向表:{課程方向ID(PK),課程方向名稱,填加時間}

課程分類表:{課程分類ID(PK),分類名稱,填加時間}

課程難度表:{課程難度ID(PK) ,課程難度贪薪,填加時間}

用戶表:{用戶ID(PK)媳禁,用戶昵稱,密碼,性別画切,省市,職位,說明竣稽,經(jīng)驗,積分,關(guān)注
人數(shù),粉絲人數(shù),講師標(biāo)識}

問答評論表:{評論ID(PK),父評論ID ,課程ID,章節(jié)ID,小節(jié)ID ,評論標(biāo)題,用戶
ID,內(nèi)容,類型,瀏覽量,發(fā)布時間}

筆記表:{筆記ID(PK),課程ID,章節(jié)ID,小節(jié)ID筆記標(biāo)題,用戶呢稱,筆記內(nèi)容,
發(fā)布時間}

評價表:{評價ID(PK)霍弹,用戶ID,課程ID,內(nèi)容綜合評分,內(nèi)容實用,簡潔易懂,邏
輯清晰,發(fā)布時間}

用戶選課表:{用戶選課ID(PK),用戶ID,課程ID,選課時間毫别,累積聽課時長}

19 常用的整數(shù)類型

20 常用的浮點類型

  • 例如:


實戰(zhàn)實數(shù)類型的特點

  • 建立測試數(shù)據(jù)庫


  • 新建表


  • 插入數(shù)據(jù)至t表中


  • 查詢和


  • 和的結(jié)果



    所以只有decimal是精確的浮點類型

21 常用的時間類型

實戰(zhàn)時間類型的特點

  • 新建表


  • 插入數(shù)據(jù)


  • 查詢結(jié)果


  • 由于北京時間是東八區(qū),因此我們更改時區(qū)


  • 新的查詢結(jié)果


    這就是timestamp具有時區(qū)性的特點

22 字符串類型的特點

23 如何為數(shù)據(jù)選擇合適的的數(shù)據(jù)類型

23.1 優(yōu)先選擇符合存儲數(shù)據(jù)需求的最小數(shù)據(jù)類型

INET_ATON( '255.255.255.255' ) = 4294967295
INET_ NTOA(4294967295) ='255.255.255.255'

23.2 謹(jǐn)慎使用ENUM,TEXT字符串類型

23.2.1 ENUM 的遷移

數(shù)據(jù)遷移的時候,它幾乎不可能被其他數(shù)據(jù)庫所支持典格,如果 ENUM 里面是字符串岛宦,對于其他數(shù)據(jù)庫來說就更郁悶了,還不能設(shè)為tinyint等類型的字段

23.2.2 ENUM 的索引

純數(shù)字類型的不建議用枚舉類型耍缴,這是因為在 ENUM 內(nèi)部維護(hù)有一個隱形的索引砾肺,也是按數(shù)字排列的,容易混淆防嗡;添加枚舉值也是一個問題变汪,如果添加在最后還好,如果添加在中間什么位置的話蚁趁,原來的隱藏索引將不再起作用

23.2.3 ENUM 字段 的NULL 值

ENUM 字段默認(rèn)是可以插入 NULL 值的裙盾,這個就比較尷尬了,而且沒有辦法優(yōu)化

23.2.4 插入的值

如果插入的值比ENUM設(shè)定的值大他嫡,會默認(rèn)保存成接近的那個值番官;插入的值不能包含函數(shù),不能傳遞參數(shù)

所以如果插入的值是數(shù)字型的钢属,建議用tinyint鲤拿,如果插入的值是字符型的,建議用char署咽。如果真想用 ENUM 也是可以得近顷,前提是要了解到 ENUM 的弊端,就可以有效規(guī)避這些問題

23.4 同財務(wù)相關(guān)的數(shù)值型數(shù)據(jù),必需使用decimal類型宁否。

24 為項目表們選擇合適的數(shù)據(jù)類型

24.1 課程表


24.2 章節(jié)表

24.3 小節(jié)表

24.4 課程分類表

24.5 課程難度表

24.5 課程方向表

24.6 用戶表


24.7 問答評論表

24.8 筆記表

24.9 用戶選課表

30 如何為表和列選擇合適的名字

  • 所有數(shù)據(jù)庫對像名稱必須使用小寫字母可選用下劃線分割
  • 所有數(shù)據(jù)庫對像名稱定義禁止使用MySQL保留關(guān)建字
  • 數(shù)據(jù)庫對像的命名要能做到見名識義,并且最好不要超過32個字
  • 臨時庫表必須以tmp為前綴并以日期為后綴
  • 用于備份的庫,表必須以bak為前綴并以日期為后綴
  • 所有存儲相同數(shù)據(jù)的列名和列類型必須一致窒升。

31 總結(jié)

工程師的必備技能

1、前奏:【業(yè)務(wù)分析】欲善其事慕匠,必三思而行饱须;
2、高潮:【邏輯設(shè)計】范式化VS反范式化台谊;
3蓉媳、結(jié)束:【物理設(shè)計】存儲引擎&數(shù)據(jù)類型&命名規(guī)約譬挚。

內(nèi)容綜述

  • 數(shù)據(jù)庫的邏輯設(shè)計規(guī)范
  • MySQL的常用存儲引擎及其選擇方法
  • MySQL的常用數(shù)據(jù)類型及其選擇方法
  • 如何為表選擇適合的存儲類型
  • 如何為表起一個好名

參考

數(shù)據(jù)庫設(shè)計
MySQL慎用 ENUM 字段

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市酪呻,隨后出現(xiàn)的幾起案子减宣,更是在濱河造成了極大的恐慌,老刑警劉巖玩荠,帶你破解...
    沈念sama閱讀 212,383評論 6 493
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件漆腌,死亡現(xiàn)場離奇詭異,居然都是意外死亡阶冈,警方通過查閱死者的電腦和手機闷尿,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,522評論 3 385
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來女坑,“玉大人填具,你說我怎么就攤上這事〈移” “怎么了灌旧?”我有些...
    開封第一講書人閱讀 157,852評論 0 348
  • 文/不壞的土叔 我叫張陵,是天一觀的道長绰筛。 經(jīng)常有香客問我,道長描融,這世上最難降的妖魔是什么铝噩? 我笑而不...
    開封第一講書人閱讀 56,621評論 1 284
  • 正文 為了忘掉前任,我火速辦了婚禮窿克,結(jié)果婚禮上骏庸,老公的妹妹穿的比我還像新娘。我一直安慰自己年叮,他們只是感情好具被,可當(dāng)我...
    茶點故事閱讀 65,741評論 6 386
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著只损,像睡著了一般一姿。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上跃惫,一...
    開封第一講書人閱讀 49,929評論 1 290
  • 那天叮叹,我揣著相機與錄音,去河邊找鬼爆存。 笑死蛉顽,一個胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的先较。 我是一名探鬼主播携冤,決...
    沈念sama閱讀 39,076評論 3 410
  • 文/蒼蘭香墨 我猛地睜開眼悼粮,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了曾棕?” 一聲冷哼從身側(cè)響起扣猫,我...
    開封第一講書人閱讀 37,803評論 0 268
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎睁蕾,沒想到半個月后苞笨,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 44,265評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡子眶,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,582評論 2 327
  • 正文 我和宋清朗相戀三年瀑凝,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片臭杰。...
    茶點故事閱讀 38,716評論 1 341
  • 序言:一個原本活蹦亂跳的男人離奇死亡粤咪,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出渴杆,到底是詐尸還是另有隱情寥枝,我是刑警寧澤,帶...
    沈念sama閱讀 34,395評論 4 333
  • 正文 年R本政府宣布磁奖,位于F島的核電站囊拜,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏比搭。R本人自食惡果不足惜冠跷,卻給世界環(huán)境...
    茶點故事閱讀 40,039評論 3 316
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望身诺。 院中可真熱鬧蜜托,春花似錦、人聲如沸霉赡。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,798評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽穴亏。三九已至蜂挪,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間嗓化,已是汗流浹背锅劝。 一陣腳步聲響...
    開封第一講書人閱讀 32,027評論 1 266
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留蟆湖,地道東北人故爵。 一個月前我還...
    沈念sama閱讀 46,488評論 2 361
  • 正文 我出身青樓,卻偏偏與公主長得像,于是被迫代替她去往敵國和親诬垂。 傳聞我的和親對象是個殘疾皇子劲室,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,612評論 2 350

推薦閱讀更多精彩內(nèi)容