MySQL用的在溜，不知道業務如何設計也白搭！！！

MySQL業務設計

• 作者: 博學谷狂野架構師
• GitHub：GitHub地址 （有我精心準備的130本電子書PDF）

只分享干貨、不吹水，讓我們一起加油！??

邏輯設計

范式設計

范式概述

第一范式：當關系模式R的所有屬性都不能在分解為更基本的資料單位時，稱R是滿足第一范式的，簡記為1NF，滿足第一范式是關系模式規范化的最低要求，否則，將有很多基本操作在這樣的關系模式中實作不了，

第二范式：如果關系模式R滿足第一范式，并且R得所有非主屬性都完全依賴于R的每一個候選關鍵屬性，稱R滿足第二范式，簡記為2NF，

第三范式：設R是一個滿足第一范式條件的關系模式，X是R的任意屬性集，如果X非傳遞依賴于R的任意一個候選關鍵字，稱R滿足第三范式，簡記為3NF，

第一范式

• 資料庫表中的所有欄位都只具有單一屬性，
• 單一屬性的列是由基本資料型別所構成的，
• 設計出來的表都是簡單的二維表，

示例

解決辦法

name-age列具有兩個屬性，一個name，一個 age不符合第一范式，把它拆分成兩列，

第二范式

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

示例

有兩張表：訂單表，產品表

解決辦法

一個訂單有多個產品，所以訂單的主鍵為【訂單ID】和【產品ID】組成的聯合主鍵，這樣2個主鍵不符合第二范式，而且產品ID和訂單ID沒有強關聯，故，把訂單表進行拆分為訂單表與訂單與商品的中間表，

第三范式

指每一個非主屬性既不部分依賴于也不傳遞依賴于業務主鍵，也就是在第二范式的基礎上消除了非主鍵對主鍵的傳遞依賴，

示例

解決辦法

其中

客戶編號 和訂單編號管理 關聯

客戶姓名 和訂單編號管理 關聯

客戶編號 和 客戶姓名 關聯

如果客戶編號發生改變，用戶姓名也會改變，這樣不符合第三大范式，應該把客戶姓名這一列洗掉

范式設計實戰

按要求設計一個電子商務網站的資料庫結構，本網站只銷售圖書類產品，需要具備以下功能：

• 用戶登陸 商品展示 供應商管理
• 用戶管理 商品管理 訂單銷售

用戶登陸及用戶管理

• 用戶必須注冊并登陸系統才能進行網上交易，用戶名用來作為用戶資訊的業務主鍵
• 同一時間一個用戶只能在一個地方登陸

只有一個業務主鍵，一定是符合第二范式，沒有屬性和業務主鍵存在傳遞依賴的關系，符合第三范式，

商品資訊

一個商品可以屬于多個分類，故，商品名稱和分類應該是組合主鍵，會有大量冗余，不符合第二范式，應該把分類資訊單獨存放

解決辦法

另外再建立一個中間表把分類資訊和商品資訊進行關聯

最后的三張表如下

供應商管理功能

符合三大范式，不需要修改，但假如增加新的一列【銀行支行】，這樣隨著銀行賬戶的變化，銀行支行也會編號，不符合第三大范式

在線銷售功能

有多個業務主鍵，不符合第二范式，訂單商品單價、訂單數量、訂單金額存在傳遞依賴關系，不符合第三范式，需要拆解

解決辦法

創建一個訂單關聯表，將商品分類和商品名稱拆解出來

這時候，【訂單商品分類】與【訂單商品名】有依賴關聯，故合并如下

表匯總

查詢練習

撰寫SQL查詢出每一個用戶的訂單總金額（用戶名，訂單總金額）

COPYSELECT a.單用戶名, sum(d.商品價格 * b.商品數量)
FROM 訂單表 a
JOIN 訂單分類關聯表 b ON a.訂單編號 = b.訂單編號
JOIN 商品分類關聯表 c ON c.商品分類ID = b.商品分類ID
JOIN 商品資訊表 d ON d.商品名稱 = c.商品名稱
GROUP BY a.下單用戶名

撰寫SQL查詢出下單用戶和訂單詳情（訂單編號，用戶名，手機號，商品名稱，商品數量，商品價格）

COPYSELECT a.訂單編號, e.用戶名, e.手機號, d.商品名稱, c.商品數量, d.商品價格
FROM 訂單表 a
JOIN 訂單分類關聯表 b ON a.訂單編號 = b.訂單編號
JOIN 商品分類關聯表 c ON c.商品分類ID = b.商品分類ID
JOIN 商品資訊表 d ON d.商品名稱 = c.商品名稱
JOIN 用戶資訊表 e ON e.用戶名 = a.下單用戶

存在的問題

• 大量的表關聯非常影響查詢的性能
• 完全符合范式化的設計有時并不能得到良好得SQL查詢性能

反范式設計

什么叫反范式化設計

• 反范式化是針對范式化而言得，在前面介紹了資料庫設計得范式
• 所謂得反范式化就是為了性能和讀取效率得考慮而適當得對資料庫設計范式得要求進行違反
• 允許存在少量得冗余，換句話來說反范式化就是使用空間來換取時間

商品資訊反范式設計

下面是范式設計的商品資訊表

商品資訊和分類資訊經常一起查詢，所以把分類資訊也放到商品表里面，冗余存放，

在線銷售功能反范式

下面是在線銷售功能的范式設計

首先來看訂單表

• 查詢訂單資訊要關聯查詢到用戶表，但用戶表的電話是可能改變的，而且查詢訂單的時候經常查詢到用戶的電話
• 查詢訂單經常會查詢到訂單金額，所以把訂單金額也冗余進來

新設計的訂單表如下

再來看訂單關聯表

• 和商品資訊反范式設計一樣，查詢訂單的時候經常查詢商品分類，所以把商品分類和訂單名冗余進來
• 商品的單價可能會編號，如果關聯查詢查詢只能查詢到最新的商品價格，而查詢不到下訂單時候的價格，并且商品單價經常會查詢， 所以把訂單單價也冗余進來

新設計的商品關聯表如下

查詢練習

撰寫SQL查詢出每一個用戶的訂單總金額

COPYSELECT 下單用戶名, sum(訂單金額)
FROM 訂單表
GROUP BY 下單用戶名;

撰寫SQL查詢出下單用戶和訂單詳情

COPY
SELECT  a.單用戶名, sum(d.商品價格 * b.商品數量)
FROM   訂單表 a
JOIN 訂單分類關聯表 b ON a.訂單編號 = b.訂單編號
JOIN 商品分類關聯表 c ON c.商品分類ID = b.商品分類ID
JOIN 商品資訊表 d ON d.商品名稱 = c.商品名稱
GROUP BY  a.下單用戶名;

總結

不能完全按照范式得要求進行設計，考慮以后如何使用表

范式化設計優缺點

優點

• 可以盡量得減少資料冗余
• 范式化的更新操作比反范式化更快
• 范式化的表通常比反范式化的表更小

缺點

• 對于查詢需要對多個表進行關聯
• 更難進行索引優化

反范式化設計優缺點

優點

• 可以減少表的關聯
• 可以更好的進行索引優化

缺點

• 存在資料冗余及資料維護例外
• 對資料的修改需要更多的成本

物理設計

命名規范

資料庫、表、欄位的命名要遵守可讀性原則

使用大小寫來格式化的庫物件名字以獲得良好的可讀性

例如：使用custAddress而不是custaddress來提高可讀性，

資料庫、表、欄位的命名要遵守表意性原則

物件的名字應該能夠描述它所表示的物件

例如：對于表，表的名稱應該能夠體現表中存盤的資料內容；對于存盤程序存盤程序應該能夠體現存盤程序的功能，

資料庫、表、欄位的命名要遵守長名原則

盡可能少使用或者不使用縮寫

存盤引擎選擇

資料型別選擇

當一個列可以選擇多種資料型別時

• 優先考慮數字型別
• 其次是日期、時間型別
• 最后是字符型別
• 對于相同級別的資料型別，應該優先選擇占用空間小的資料型別
• 對精度有要求的時候，選擇精度高的資料型別， int<float<double<decimal.

浮點型別

注意float 和double 是非精度型別，如果是和金額相關盡量用decimal

COPYselect  sum(c1), sum(c2), sum(c3)  from  test_numberic;

日期型別

面試經常問道 timestamp 型別 與 datetime區別

• datetime型別在5.6中欄位長度是5個位元組
• datetime型別在5.5中欄位長度是8個位元組
• timestamp 和時區有關，而datetime無關

COPYDROP TABLE IF EXISTS `test_time`;
CREATE TABLE `test_time`  (
  `c1` datetime(6) NULL DEFAULT NULL,
  `c2` timestamp(6) NULL DEFAULT NULL,
  `c3` time(6) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
insert into  test_time  VALUES(NOW(),NOW(),NOW());
COPYmysql> select * from test_time;
+----------------------------+----------------------------+-----------------+
| c1                         | c2                         | c3              |
+----------------------------+----------------------------+-----------------+
| 2019-12-25 14:44:22.000000 | 2019-12-25 14:44:22.000000 | 14:44:22.000000 |
+----------------------------+----------------------------+-----------------+
1 row in set (0.00 sec)

set time_zone="-10:00"

mysql> select * from test_time;
+----------------------------+----------------------------+-----------------+
| c1                         | c2                         | c3              |
+----------------------------+----------------------------+-----------------+
| 2019-12-25 14:44:22.000000 | 2019-12-24 20:44:22.000000 | 14:44:22.000000 |
+----------------------------+----------------------------+-----------------+
1 row in set (0.00 sec)

字串型別

字串型別所需的存盤和值范圍

定義與變長區別 (CHAR VS VARCHAR)

字串型別相關注意事項

• 在BLOB和TEXT列上創建索引時，必須制定索引前綴的長度
• VARCHAR和VARBINARY必須長度是可選的
• BLOB和TEXT列不能有默認值
• BLOB和TEXT列排序時只使用該列的前max_sort_length個位元組

COPYmysql> show variables like 'max_sort_length';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_sort_length | 1024  |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)

本文由傳智教育博學谷狂野架構師教研團隊發布，

如果本文對您有幫助，歡迎關注和點贊；如果您有任何建議也可留言評論或私信，您的支持是我堅持創作的動力，

轉載請注明出處！

標籤：Java

上一篇：Java的初始化塊

下一篇：返回列表