文章目錄
- 範式與反範式
-
- 範式
-
- 第一範式
- 第二範式
- 第三範式
- 第二範式 VS 第三範式
- 設計符合 2NF 的表
- 範式優缺點
- 反範式
- 範式 VS 反範式
- MySQL 使用原則和設計規範
-
- 基本設置規則
-
- 線上系統轉不區分大小寫
- 不建議使用的功能
- 規範命名
- InnoDB 表的注意事項
- 備份表/臨時表等常見表的設計規範
- 欄位設計要求
-
- int(3) int(5) 區別
- 浮點數與定點數區別
- N 解釋
- Char 與 Varchar 類型
範式與反範式
範式
範式是關係資料庫理論的基礎,也是我們在設計資料庫結構過程中所要遵循的規則和指導方法。資料庫的設計範式是資料庫設計所需要滿足的規範。
目前關係資料庫有六種範式:第一範式(1NF)、第二範式(2NF)、第三範式(3NF)、巴斯-科德範式(BCNF)、第四範式(4NF)和第五範式(5NF,又稱完美範式)。
滿足最低要求的叫第一範式,簡稱 1NF。在第一範式基礎上進一步滿足一些要求的為第二範式,簡稱 2NF。其餘依此類推。各種範式呈遞次規範,越高的範式資料庫冗餘越小。通常所用到的只是前三個範式,即:第一範式(1NF),第二範式(2NF),第三範式(3NF)。
第一範式
第一範式無重複的列,表中的每一列都是拆分的基本數據項,即列不能夠再拆分成其他幾列,強調的是列的原子性.。
如果在實際場景中,一個聯繫人有家庭電話和公司電話,那麼以「姓名、性別、電話」為表頭的表結構就沒有達到 1NF。要符合 1NF 我們只需把電話列拆分,讓表頭變為姓名、性別、家庭電話、公司電話即可。
第二範式
第二範式屬性完全依賴於主鍵,首先要滿足它符合 1NF,另外還需要包含兩部分內容
- 表必須有一個主鍵;
- 沒有包含在主鍵中的列必須完全依賴於主鍵,而不能只依賴於主鍵的一部分。即要求實體的屬性完全依賴於主關鍵字。所謂完全依賴是指不能存在僅依賴主關鍵字一部分的屬性。
第三範式
第三範式屬性不傳遞依賴於其他非主屬性,首先需要滿足 2NF,另外非主鍵列必須直接依賴於主鍵,不能存在傳遞依賴。即不能存在:非主鍵列 A 依賴於非主鍵列 B,非主鍵列 B 依賴於主鍵的情況。
第二範式 VS 第三範式
- 第二範式:非主鍵列是否依賴主鍵(包括一列通過某一列間接依賴主鍵),要是有依賴關係就是第二範式;
- 第三範式:非主鍵列是否直接依賴主鍵,不能是那種通過傳遞關係的依賴。要是符合這種依賴關係就是第三範式。
通過對前三個範式的了解,我們知道 3NF 是 2NF 的子集,2NF 是 1NF 的子集。
設計符合 2NF 的表
以訂單信息表為例,講述如何設計一個符合 2NF 的表
首先,我們看原始的訂單信息表,如下圖所示
圖中,以訂單編號和商品編號作為聯合主鍵,商品名稱、單位、價格等信息不與主鍵相關,只與編號相關,違反了第二範式。 應該對訂單信息表進行拆分,商品信息單獨一張表,訂單項目一張表,如下所示,拆分分成 3 張表。
- 包含客戶信息的訂單信息表
- 包含商品詳情的商品信息表
- 包含訂單詳情的訂單詳情表
範式優缺點
【優點 】
- 避免數據冗餘,減少維護數據完整性的麻煩;
- 減少資料庫的空間;
- 數據變更速度快
【缺點】
- 按照範式的規範設計的表,等級越高的範式設計出來的表數量越多。
- 獲取數據時,表關聯過多,性能較差。
表的數量越多,查詢所需要的時間越多。也就是說所用的範式越高,對數據操作的性能越低。
反範式
範式是普適的規則,滿足大多數的業務場景的需求。對於一些特殊的業務場景,範式設計的表,無法滿足性能的需求。此時,就需要根據業務場景,在範式的基礎之上進行靈活設計,也就是反範式設計。
反範式設計主要從三方面考慮:
- 業務場景
- 響應時間
- 欄位冗餘
反範式設計就是用空間來換取時間,提高業務場景的響應時間,減少多表關聯。主要的優點如下
- 允許適當的數據冗餘,業務場景中需要的數據幾乎都可以在一張表上顯示,避免關聯
- 可以設計有效的索引
- 範式 VS 反範式
範式化模型:
- 數據沒有冗餘,更新容易
- 當表的數量比較多,查詢數據需要多表關聯時,會導致查詢性能低下
反範式化模型:
- 冗餘將帶來很好的讀取性能,因為不需要 join 很多表
- 雖然需要維護冗餘數據,但是對磁碟空間的消耗是可以接受的
MySQL 使用原則和設計規範
聊完範式,接下來我們看看 MySQL 使用中的一些使用原則和設計規範。
MySQL 雖然具有很多特性並提供了很多功能,但是有些特性會嚴重影響它的性能,比如,在資料庫里進行計算,寫大事務、大 SQL、存儲大欄位等。
想要發揮 MySQL 的最佳性能,需要遵循 3 個基本使用原則
- 首先是需要讓 MySQL 回歸存儲的基本職能:MySQL 資料庫只用於數據的存儲,不進行數據的複雜計算,不承載業務邏輯,確保存儲和計算分離
- 其次是查詢數據時,儘量單表查詢,減少跨庫查詢和多表關聯
- 還有就是要杜絕大事務、大 SQL、大批量、大欄位等一系列性能殺手。
大事務,運行步驟較多,涉及的表和欄位較多,容易造成資源的爭搶,甚至形成死鎖。一旦事務回滾,會導致資源占用時間過長
- 大 SQL,複雜的 SQL 意味著過多的表的關聯,MySQL 資料庫處理關聯超過 3 張表以上的 SQL 時,占用資源多,性能低下
- 大批量,意味著多條 SQL 一次性執行完成,必須確保進行充分的測試,並且在業務低峰時段或者非業務時段執行
- 大欄位,blob、text 等大欄位,儘量少用。必須要用時,儘量與主業務表分離,減少對這類欄位的檢索和更新
基本設置規則
- 必須指定默認存儲引擎為 InnoDB,並且禁用 MyISAM 存儲引擎,隨著 MySQL 8.0 版本的發布,所有的數據字典表都已經轉換成了 InnoDB,MyISAM 存儲引擎已成為了歷史。
- 默認字符集 UTF8mb4,以前版本的 UTF8 是 UTF8mb3,未包含個別特殊字符,新版本的 UTF8mb4 包含所有字符,官方強烈建議使用此字符集。
- 關閉區分大小寫功能。設置 lower_case_tables_name=1,即可關閉區分大小寫功能,即大寫字母 T 和小寫字母 t 一樣
線上系統轉不區分大小寫
如何讓系統中區分大小寫的庫錶轉換為不區分大小寫的庫表呢?因為要修改底層數據,還是比較麻煩的,操作步驟如下。
- MySQL dump 導出資料庫。
- 修改參數 lower_case_tables_name=1。
- 導入備份數據時,必須停止資料庫,停止業務,影響非常大。
- 開啟 per-table 表空間,開啟後,每張業務表會單獨創建一個獨立於系統表空間的表空間,便於空間的回收,數據的遷移
不建議使用的功能
- 存儲過程、觸發器、視圖、event。為了存儲計算分離,這類功能儘量在程序中實現。這些功能非常不完整,調試、排錯、監控都非常困難,相關數據字典也不完善,存在潛在的風險。一般在生產資料庫中,禁止使用。
- lob、text、enum、set。這些欄位類型,在 MySQL 資料庫的檢索性能不高,很難使用索引進行優化。如果必須使用這些功能,一般採取特殊的結構設計,或者與程序結合使用其他的欄位類型替代。比如:set 可以使用整型(0,1,2,3)、注釋功能和程序的檢查功能集合替代。
規範命名
命名規範如下,命名時的字符取值範圍為:az,09 和 _(下畫線)。
- 所有表名小寫,不允許駝峰式命名;
- 允許使用 -(橫線)和 (空格);如下圖所示,當使用 -(橫線),後台默認會轉化成 @002d;
- 不允許使用其他特殊字符作為名稱,減少潛在風險。
資料庫庫名的命名規則必須遵循「見名知意」的原則,即庫名規則為「資料庫類型代碼 + 項目簡稱 + 識別代碼 + 序號」。
表名的命名規則分為:
- 單表僅使用 a~z、_;
- 分表名稱為「表名_編號」;
- 業務表名代表用途、內容:子系統簡稱_業務含義_後綴。
常見業務表類型有:
- 臨時表,tmp;
- 備份表,bak;
- 字典表,dic;
- 日誌表,log。
欄位名精確,遵循「見名知意」的原則,格式:名稱_後綴。
- 避免普遍簡單、有歧義的名稱。
- 用戶表中,用戶名的欄位為 UserName 比 Name 更好。
- 布爾型的欄位,以助動詞(has/is)開頭。
用戶是否有留言 hasmessage,用戶是否通過檢查 ischecked 等。
常見後綴如下:
- 流水號/無意義主鍵,後綴為 id,比如 task_id;
- 時間,後綴為 time,insert_time。
程序帳號與資料庫名稱保持一致。如果所有的程序帳號都是 root@『%』,密碼也一樣,很容易錯連到其他的資料庫,造成誤操作。
索引命名格式,主要為了區分哪些對象是索引:
- 前綴_表名(或縮寫)_欄位名(或縮寫);
- 主鍵必須使用前綴「pk_」;
- UNIQUE 約束必須使用前綴「uk_」;
- 普通索引必須使用前綴「idx_」。
資料庫規範庫表欄位的命名,能夠提高資料庫的易讀性,為資料庫表設計打下基礎。下面我們具體看看錶設計的一些規則。
- 顯式指定需要的屬性;
創建表時顯示指定字符集、存儲引擎、注釋信息等。
- 不同系統之間,統一規範;
不同表之間的相同欄位或者關聯欄位,欄位類型/命名要保持一致;庫表字符集和前端程序、中間件必須保持一致的 UTF8mb4。
InnoDB 表的注意事項
- 主鍵列,UNSIGNED 整數,使用 auto_increment;禁止手動更新 auto_increment,可以刪除。
- 必須添加 comment 注釋。
- 必須顯示指定的 engine。
- 表必備三欄位:id、 xxx_create、 xxx_modified。
- id 為主鍵,類型為 unsigned bigint 等數字類型;
- xxx_create、xxx_modified 的類型均為 datetime 類型,分別記錄該條數據的創建時間、修改時間。
備份表/臨時表等常見表的設計規範
- 備份表,表名必須添加 bak 和日期,主要用於系統版本上線時,存儲原始數據,上線完成後,必須及時刪除。
- 臨時表,用於存儲中間業務數據,定期優化,及時降低表碎片。
- 日誌類表,首先考慮不入庫,保存成文件,其次如果入庫,明確其生命周期,保留業務需求的數據,定期清理。
- 大欄位表,把主鍵欄位和大欄位,單獨拆分成表,並且保持與主表主鍵同步,儘量減少大欄位的檢索和更新。
- 大表,根據業務需求,從垂直和水平兩個維度進行拆分
垂直拆分:
- 按列關聯度
水平拆分:
- 按照時間、地域、範圍等;
- 冷熱數據(歷史數據歸檔)
欄位設計要求
- 根據業務場景需求,選擇合適的類型,最短的長度;確保欄位的寬度足夠用,但也不要過寬。所有欄位必須為 NOT NULL,空值則指定 default 值,空值難以優化,查詢效率低。比如:人的年齡用 unsigned tinyint(範圍 0~255,人的壽命不會超過 255 歲);海龜就必須是 smallint,但如果是太陽的年齡,就必須是 int;如果是所有恆星的年齡都加起來,那麼就必須使用 bigint。
- 表欄位數少而精,儘量不加冗餘列。
- 單實例表個數必須控制在 2000 個以內。
- 單表分表個數必須控制在 1024 個以內。
- 單表欄位數上限控制在 20~50 個
【禁用 ENUM、SET 類型】
- 兼容性不好,性能差。
解決方案:使用 TINYINT,在 COMMENT 信息中標明被枚舉的含義。is_disable TINYINT UNSIGNED DEFAULT 『0』 COMMENT '0:啟用 1:禁用 2:異常』。
【禁用列為 NULL 】
- MySQL 難以優化 NULL 列;
- NULL 列加索引,需要額外空間;
- 含 NULL 複合索引無效。
解決方案:在列上添加 NOT NULL DEFAULT 預設值
【禁止 VARBINARY、BLOB 存儲圖片、文件等】
- 禁止在資料庫中存儲大文件,例如照片,可以將大文件存儲在對象存儲系統中,資料庫中存儲路徑。
不建議使用 TEXT/BLOB:
- 處理性能差;
- 行長度變長;
- 全表掃描代價大。
解決方案:拆分成單獨的表
存儲字節越小,占用空間越小。儘量選擇合適的整型,如下圖所示。
- 主鍵列,無負數,建議使用 INT UNSIGNED 或者 BIGINT UNSIGNED;預估欄位數字取值會超過 42 億,使用 BIGINT 類型。
- 短數據使用 TINYINT 或 SMALLINT,比如:人類年齡,城市代碼。
- 使用 UNSIGNED 存儲非負數值,擴大正數的範圍。
int(3) int(5) 區別
- 正常顯示沒有區別。
- 3 和 5 僅是最小顯示寬度而已。
- 有 zerofill 等擴展屬性時則顯示有區別。
浮點數與定點數區別
浮點數與定點數區別,如下圖所示。
- 浮點數:float、double(或 real)
- 定點數:decimal(或 numberic)
從上圖中可以觀察到:
- 浮點數存在誤差問題;
- 儘量避免進行浮點數比較;
- 對貨幣等對精度敏感的數據,應該使用定點數
N 解釋
字符集都為 UTF8mb4,中文存儲占三個字節,而數據或字母,則只占一個字節。
下面看一下字符類型中 N 的解釋。
- CHAR(N) 和 VARCHAR(N) 的長度 N,不是字節數,是字符數。
- username 列可以存多少個漢字,占用多少個字節
- username 最多能存儲 40 個字符,占用 120 個字節
Char 與 Varchar 類型
存儲字符串長度相同的全部使用 Char 類型;字符長度不相同的使用 Varchar 類型,不預先分配存儲空間,長度不要超過 255。
Char 和 Varchar 占用空間的對比,如下圖所示。
Varchar 值存儲為 1 字節或 2 字節長度前綴加數據
如果值不超過 255 個字節,則列使用一個字節長度
如果值可能需要超過 255 個字節,則列使用兩個字節長度
為什麼超過 255 個字節時,必須使用兩個字節長度。
- 2的8次方=256,1 個字節是 8 位;
- 2的16次方=65535,2 個字節是 16 位。