MySQL數(shù)據(jù)庫設(shè)計和規(guī)范化

目錄

1 數(shù)據(jù)庫設(shè)計

1.1 函數(shù)依賴

1.2 異常

1.3 范式

1.4 范式優(yōu)化流程示例

1.4.1 優(yōu)化到滿足1NF

1.4.2 優(yōu)化到滿足2NF

1.4.3 優(yōu)化到滿足3NF

2 數(shù)據(jù)庫規(guī)范

2.1 數(shù)據(jù)庫命名規(guī)范

2.2 數(shù)據(jù)庫設(shè)計規(guī)范

2.3 SQL 語句書寫規(guī)范

1 數(shù)據(jù)庫設(shè)計

1.1 函數(shù)依賴

記 A->B 表示 A 函數(shù)決定 B，也可以說 B 函數(shù)依賴于 A。

如果 {A1，A2，... ，An} 是關(guān)系的一個或多個屬性的集合，該集合函數(shù)決定了關(guān)系的其它所有屬性并且是最小的，那么該集合就稱為候選鍵?？梢詮暮蜻x鍵中隨機選出一個作為主鍵。

對于 A->B，如果能找到 A 的真子集 A'，使得 A'-> B，那么 A->B 就是部分函數(shù)依賴，否則就是完全函數(shù)依賴。

對于 A->B，B->C，則 A->C 是一個傳遞函數(shù)依賴。

1.2 異常

一個簡單的學生選課表如下：

不符合范式的關(guān)系，會產(chǎn)生很多異常，主要有以下四種異常：

1.冗余異常（Redundancy Anomaly）

在這個例子中，張三的名字多次出現(xiàn)，造成了數(shù)據(jù)冗余。

2.更新異常（Update Anomaly）

假設(shè)張三教授的名字改了（比如改名為張山）。在上面的表中，你需要更新所有與張三相關(guān)的行。如果忘記更新某一行，將導致數(shù)據(jù)不一致。

3.插入異常（Insert Anomaly）

假設(shè)一個新學生注冊但還未選課。由于學生ID、課程ID和教師名稱都是該表的組成部分，如果沒有課程和教師信息，你將無法插入這個新學生的記錄。

4.刪除異常（Delete Anomaly）

如果一個課程被取消，你可能會刪除所有選這門課的學生的記錄。但是，如果這樣做，與這些學生有關(guān)的其他課程信息也會被刪除，導致大量信息丟失。

1.3 范式

在關(guān)系型數(shù)據(jù)庫設(shè)計中，范式是確保數(shù)據(jù)結(jié)構(gòu)化的邏輯規(guī)則。遵循范式的目標是減少數(shù)據(jù)冗余、避免數(shù)據(jù)異常，同時提高數(shù)據(jù)完整性。下面是常見的范式和它們的主要規(guī)則：

1.第一范式 (1NF):

• 每個列都必須有唯一的值。
• 每個列的值必須是原子的，不可再分。
• 每個記錄必須能由一個主鍵唯一標識。

2.第二范式 (2NF):

• 完全滿足1NF。
• 去除部分依賴，即確保每個非主鍵字段都完全函數(shù)依賴于主鍵。如果是復合主鍵，則要確保沒有字段只依賴于復合主鍵的部分。

3.第三范式 (3NF):

• 完全滿足2NF。
• 沒有傳遞依賴，即非主鍵字段之間不應該存在依賴關(guān)系。?

1.4 范式優(yōu)化流程示例

假設(shè)我們有一個表，其中包含以下數(shù)據(jù)：

學生表

此表違反了以下范式：

• 1NF：因為課程、教師和教師郵箱的列包含了多個值。
• 2NF：因為教師和他們的郵箱是根據(jù)課程決定的，而不是直接依賴于學生名。
• 3NF：因為教師的郵箱是依賴于教師的，而不是直接依賴于學生或課程。

1.4.1 優(yōu)化到滿足1NF

將每個多值屬性拆分為單獨的行：

學生課程表

1.4.2 優(yōu)化到滿足2NF

將與課程和教師相關(guān)的信息從學生表中分離：

學生表

課程教師表

學生課程關(guān)聯(lián)表

1.4.3 優(yōu)化到滿足3NF

將教師和他們的電子郵件分開，因為電子郵件是直接依賴于教師的：

教師表

課程表

2 數(shù)據(jù)庫規(guī)范

2.1 數(shù)據(jù)庫命名規(guī)范

1. 可讀性和一致性: 命名應明確、簡單且一致。避免使用不明確的縮寫或命名。
2. 大小寫: 通常，數(shù)據(jù)庫對象（如表名、列名等）應該使用小寫，并用下劃線分隔單詞（例如，student_info）。
3. 前綴和后綴: 使用前綴或后綴來區(qū)分數(shù)據(jù)庫對象的類型（例如，tbl_students 表示學生表，tbl_students_20220707_bak表示學生表在20220707的備份，tbl_students_tmp表示存儲學生表的一些臨時記錄，uk_tbl_students表是這是學生表的唯一索引）。
4. 復數(shù)與單數(shù): 表名通常使用單數(shù)（例如，student），除非單個記錄表示多個實體（例如，orders）。
5. 限制長度：通常應該對庫名、表名、字段名限制長度，防止數(shù)據(jù)庫遷移出現(xiàn)問題。

2.2 數(shù)據(jù)庫設(shè)計規(guī)范

1. 主鍵和外鍵: 每個表應有一個明確定義的主鍵，建議該主鍵沒有業(yè)務(wù)含義。外鍵可有可無，如果在小數(shù)據(jù)量且不涉及到分布式的情況下可以有，如果在分布式數(shù)據(jù)庫或者數(shù)據(jù)量非常大的情況下，使用外鍵可能不是好的選擇，此時可以在應用層維護數(shù)據(jù)的完整性）。
2. 范式: 設(shè)計應遵循數(shù)據(jù)庫范式，至少達到第三范式，以減少數(shù)據(jù)冗余和維護問題。
3. 索引: 根據(jù)查詢需求適當創(chuàng)建索引，但不應過度使用，以免影響更新操作的性能。詳細的索引設(shè)計參考上一篇文檔。
4. 數(shù)據(jù)類型: 選擇最適合數(shù)據(jù)的數(shù)據(jù)類型。例如，使用 INT 存儲整數(shù)，使用 VARCHAR 存儲可變長度的字符串。在多種數(shù)據(jù)類型都可以使用時，選擇字節(jié)數(shù)最少的數(shù)據(jù)類型，比如日期可以存儲為varchar/char , 也可以存儲為date，但date所占的字節(jié)數(shù)更少，性能更好。
5. 注釋：表和字段必須有 comments，注釋信息必須保證完整、明確和準確；表和字段含義發(fā)生變更時，comments必須做同步修改。
6. 除臨時表以外，其他表必須有 CREATED_DATE（記錄創(chuàng)建日期，中文注釋是“創(chuàng)建時間”）、MODIFIED_DATE（記錄修改日期，中文注釋是“修改時間”）。

• CREATED_DATE 與 MODIFIED_DATE 字段類型均為 DATETIME。
• 在插入數(shù)據(jù)時，必須將 CREATED_DATE 與 MODIFIED_DATE 字段寫入為CURRENT_TIMESTAMP。
• 在更新數(shù)據(jù)時，必須將 MODIFIED_DATE 字段修改為 CURRENT_TIMESTAMP。

7.字符集： 若無特殊需求，字符集使用 utf8mb4 ，排序規(guī)則使用utf8mb4_bin。

8.存儲引擎：若無特殊需求，使用 InnoDB 存儲引擎。

2.3 SQL 語句書寫規(guī)范

1. 大小寫: SQL 關(guān)鍵字通常大寫（例如，SELECT、FROM、WHERE）以增加可讀性。
2. 縮進和對齊: 使用縮進和新行來使復雜的 SQL 語句更易讀。例如：

 ?SELECT student_name,
 ? ? ? ?  course_name
 ? FROM ? students
 ? JOIN ? courses ON students.course_id = courses.id
 ? WHERE  student_name LIKE 'A%'

3.別名: 使用別名來簡化復雜的表或列名，但別名應簡潔并具有描述性。

4.注釋: 使用注釋來解釋復雜的查詢邏輯或特殊的操作。

5.子查詢與連接: 在可行的情況下，優(yōu)先使用連接（JOIN）而非子查詢。

6.防止大事務(wù): UPDATE、DELETE 語句一定要有明確的 WHERE 條件，對于大表，該條件上一定要有索引。

7.顯式的指明字段名稱: 查詢指定字段名可以提高性能，插入指定字段名可以防止出錯。