MySQL - 高效的设计MySQL库表
文章目錄
- 生猛干貨
- 范式與反范式
- 范式
- 第一范式
- 第二范式
- 第三范式
- 第二范式 VS 第三范式
- 設計符合 2NF 的表
- 范式優缺點
- 反范式
- 范式 VS 反范式
- MySQL 使用原則和設計規范
- 基本設置規則
- 線上系統轉不區分大小寫
- 不建議使用的功能
- 規范命名
- InnoDB 表的注意事項
- 備份表/臨時表等常見表的設計規范
- 字段設計要求
- int(3) int(5) 區別
- 浮點數與定點數區別
- N 解釋
- Char 與 Varchar 類型
生猛干貨
帶你搞定MySQL實戰，輕松對應海量業務處理及高并發需求，從容應對大場面試
范式與反范式
范式
范式是關系數據庫理論的基礎，也是我們在設計數據庫結構過程中所要遵循的規則和指導方法。數據庫的設計范式是數據庫設計所需要滿足的規范。
目前關系數據庫有六種范式：第一范式（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 位。
總結
以上是生活随笔為你收集整理的MySQL - 高效的设计MySQL库表的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MySQL - 锁机制初探
- 下一篇： MySQL - 库表设计之IP和TIME