一、范式

1.概念與意義

? 范式（Normal Form）是數據庫設計需遵循的規范，解決“設計隨意導致后期重構困難”問題。主流有 三大范式（1NF、2NF、3NF），還有進階的 BCNF、4NF、5NF 等，范式間是遞進依賴（如 2NF 基于 1NF，3NF 基于 2NF ）。

2.第一范式（1NF）

（1）規則：字段需滿足原子性（不可再拆分）

（2）示例：

? ?如圖，學生表中“student”字段列存儲的數據，明顯還可拆分為姓名、性別、身高，拆分后才符合 1NF。所以應將表結構更改為下圖，才符合1NF：

?（3）不滿足1NF的影響：

? 客戶端語言和表之間無法很好的生成映射關系

? 查詢到數據后，需要處理數據時，還需要對其字段進行額外拆分

? 插入數據時，對于第一個字段的值還需要先拼裝后才能進行寫入

3.第二范式（2NF）

（1）規則：滿足1NF的基礎上，表中所有列必須完全依賴主鍵（一張表只描述一類業務，無冗余關聯）。

（2）示例：觀察1NF中表中 course 、 score 這兩列數據，跟前面的幾列數據實際上依賴關系并不大，所以此時在1NF表基礎上可以再次拆分一下表結構：

student 表

?course 表

score 表?

?結構優化后，之前的一張表被拆分成學生表、課程表、成績表三張，每張表中的 id 字段作為主鍵，其他字段都依賴這個主鍵。無論在那張表中，都可以通過id主鍵確定其他字段的信息，每張表的業務屬性都具備“唯一性”。

（3）不滿足2NF的影響：

? 因字段對主鍵存在部分依賴，會導致：數據冗余（部分信息重復存）

? 更新異常（改一處需動多條）

??插入/刪除異常（操作受主鍵關聯限制，易出錯）

4.第三范式（3NF）

（1）規則：滿足2NF的基礎上,非主鍵字段不傳遞依賴于主鍵（字段間獨立，無間接依賴）。

（2）示例：觀察2NF中 student 表，最后的兩個字段 department（學生所屬的院校） 和 dean （這個院系的院長 ）。一個學生的院長是誰，是取決于學生所在的院系，最后的兩個字段存在依賴關系。所以進一步優化表結構：

department 表

?student 表

? 將原本的學生表拆為院系表、學生表兩張，學生表中則是只存儲一個院系id，由院系表存儲院系相關的所有數據。學生表中的每個非主鍵字段與其他非主鍵字段之間，都是相互獨立的，之間不會再存在任何依賴性，所有的字段都依賴于主鍵。

（3）不滿足3NF的影響：

? 會因字段傳遞依賴，導致數據冗余（同信息重復存）

? 更新異常（改一個值需動多條數據，易漏改）

? 刪除異常（刪數據可能誤刪或殘留臟數據）

? 插入異常（新信息難單獨插入）

? 讓表結構難維護、業務流程易受牽連?

5.三范式小結

1NF：確保原子性，表中每一個列數據都必須是不可再分的字段。

2NF：確保唯一性，每張表都只描述一種業務屬性，一張表只描述一件事。

3NF：確保獨立性，表中除主鍵外，每個字段之間不存在任何依賴，都是獨立的。

經過三范式的設計優化后，整個庫中的所有表結構，會顯得更為優雅，靈活性也會更強。

6.巴斯-科德范式（BCNF）

（1）核心問題：3NF未約束聯合主鍵字段間的依賴。若聯合主鍵中某字段依賴另一字段，仍會導致冗余、更新異常。

（2）示例：這里我們可以通過聯合主鍵，確定學生表中任何一個學生的信息，但是一條學生信息中的班主任（adviser），取決于學生所在的班級，因此班主任字段其實也依賴于班級字段。

? 如果聯合主鍵中的一個字段依賴于另一個字段，會造成不小的問題，使得整張表的維護性變差，因此這里需要進一步調整結構：

? ?原本的學生表則又被拆為了班級表、學生表兩張，在學生表中只存儲班級id，然后使用班級id和學生姓名兩個字段作為聯合主鍵。

?注：第三范式只要求非主鍵字段之間，不能存在依賴關系，但沒要求聯合主鍵中的字段不能存在依賴，因此第三范式并未考慮完善，巴斯-科德范式修正的就是這點，是對第三范式的補充及完善。

7.第四范式（4NF）

（1）核心問題：表中存在多值依賴（一個字段值由多個字段共同決定，如用戶權限依賴“用戶+角色” ），導致冗余和操作異常。

（2）示例：

各字段含義：

? user_name：用戶名

? role：角色信息

ROOT：超級管理員角色

ADMIN：管理員角色

USER：普通用戶角色

? permission：權限信息

*：超級管理員擁有的權限級別，*表示所有

BACKSTAGE：管理員擁有的權限級別，表示可操作后臺

LOGIN：普通用戶擁有的權限級別，表示可登錄訪問平臺

? 一個用戶可以擁有多個角色，同時一個角色可以擁有多個權限，所以此時無法單獨根據用戶名去確定權限值，權限值必須依賴用戶、角色兩個字段來決定，這種一個字段的值取決于多個字段才能確定的情況，就被稱為多值依賴

需進一步將表格拆分為：

users 表

roles 表

?permissions 表

users_roles 表

roles_permissions 表

? 將原本的用戶角色權限表，拆分成了用戶表、角色表、權限表、用戶角色關系表、角色權限關系表。一方面用戶表、角色表、權限表中都不會有數據冗余，第二方面無論是要刪除亦或新增一個角色、權限時，都不會影響其他表。后面的兩張關系表，主要是為了維護用戶、角色、權限三者之間的關系。

?8.第五范式

（1）規則：建立在4NF的基礎上，進一步消除表中的連接依賴，直到表中的連接依賴都是主鍵所蘊含的。

（2）實際價值：解決“無損連接”理論問題，生產環境極少用到（場景罕見、難察覺 ），了解即可。

9.第六范式

（1）概念：域鍵范式，也被稱之為終極范式，但目前也僅有學術機構在研究，在生產環境中實際的用途也不大

10.反范式

（1）概念：不遵循數據庫范式設計的結構，稱為反范式結構。

（2）優缺點：

優點

? 消除數據冗余，節省存儲空間；

? 表結構清晰，簡化 SQL 操作、減少出錯。

缺點（范式級別過高時）

? 數據分散到多張表，聯表查詢需求暴增；

? 聯表過多可能引發索引失效，嚴重拖慢業務系統性能。

（3）設計意義：因范式過高會導致性能問題，設計庫表時無需 100% 遵循范式；當“破壞范式對業務的好處＞壞處”時，主動采用反范式設計。

注：不以規則為絕對標準，業務優先：能支撐業務需求、帶來實際價值的設計，就是好設計。

二、數據庫范式設計總結

1.范式設計的整體認知

（1）?復雜度與代價：范式級別越高，理解難度越大，為滿足范式付出的設計代價（如拆分表數量、性能開銷）也越大。

（2）?實際項目應用：一般項目中，滿足到第三范式（3NF）或巴斯 - 科德范式（BCNF） 即可，繼續追求更高范式易因過度精細設計導致整體性能下降。

（3）靈活權衡原則：控制在第三范式級別，可平衡數據冗余與性能影響；若打破范式對業務更有利，也可違背范式設計。

2.庫表結構設計的合理性對比

（1）不合理設計的問題：會造成數據冗余，浪費存儲空間；不便于常規 SQL 操作（如插入、刪除），甚至引發異常。

（2）合理設計的好處：節省空間（內存和磁盤）；數據劃分合理，數據庫性能高且數據完整；結構便于維護和常規SQL操作

（3）各范式遞進關系：呈包含關系，從第一范式（1NF）到域鍵范式（終極范式），級別越高約束越嚴格，如 1NF?2NF?3NF?BCNF?4NF?5NF?域鍵范式 。

（4）各范式概念：

? 1NF（第一范式）：原子性，字段值不可再分。

? 2NF（第二范式）：唯一性，字段依賴主鍵，每行數據描述同一業務屬性。

? 3NF（第三范式）：獨立性，非主屬性不傳遞依賴于主鍵。

? BCNF（巴斯 - 科德范式）：主鍵字段獨立性，聯合主鍵字段間無依賴性。

? 4NF（第四范式）：多值依賴，表中一個字段值由多個字段決定。

? 5NF（第五范式）：無連接依賴，表中字段數據間不存在連接依賴關系。

? 域鍵范式（終極范式）：追求庫表設計的終極完美范式，目前多處于學術研究階段 。

三、MySQL數據庫賬戶及授權

1.密碼策略

（1）密碼插件：

關鍵差異： caching_sha2_password 更安全， mysql_native_password 驗證速度快，但算法弱

（2）查看數據庫當前密碼策略的語句：show?variables like '%password%';（查看所有密碼相關變量）

（從上往下?）表中每一行的意思：

? 是否自動生成RSA密鑰對文件

? 哈希輪數，數值越大安全性越強

? RSA 私鑰文件

? RSA 公鑰文件

??MySQL密碼過期時間,單位為天

??超時斷開

??隨機密碼長度

??是否支持代理用戶控制認證插件

??不允許用戶使用最近n次使用過的密碼

??修改密碼時是否需要提供當前用戶使用的密碼，OFF不需要，ON需要

??不允許用戶使用最近n天內使用過的密碼

??該變量通常為空，它可能用于特定的報告或審計目的

（3）查看密碼設置策略：show variables like 'validate_password%';

注：若顯示為空，則表示未裝插件，輸入安裝插件語句即可：Iinstall component 'file://component_validate_password';

（從上往下?）表中每一行的意思：

? 用于控制密碼修改時字符變化比例的要求

??能將密碼設置成當前用戶名

??插件用于驗證密碼強度的字典文件路徑，默認為空

??密碼最小長度，默認為8，有最小值為4的限制

??密碼至少要包含的小寫字母和大寫字母的個數

??密碼至少要包含的數字個數

??密碼強度檢查等級

??密碼必須包含的特殊字符個數

（4）密碼強度檢查等級解釋（validate_password.policy） ：

（5）設置密碼強度檢查的語句：

設置密碼驗證強度檢查策略：set global validate_password.policy=等級強度;

設置至少要包含大/小寫字母的個數：set global validate_password.mixed_case_count=個數;

設置至少要包含數字的個數：set?global validate_password.number_count=個數;

設置至少包含特殊字符個數：set global validate_password.special_char_count=個數;

設置密碼長度：set global validate_password.length=長度值;

修改后，可刷新權限，使其生效：flush privileges;

（6）修改密碼的語句：

先查看 root 用戶信息：select user, host, plugin from mysql.user;

再修改其密碼：alter user 'root'@'來源地址' identified by '新密碼';（來源地址—本地主機為localhost，遠程主機為%）

最后刷新權限即可

2.登錄賬戶管理

（1）管理原則：MySQL8遵循“先創建賬戶，再賦予權限”的流程，清晰分離賬戶創建與權限配置步驟

（2）關鍵操作命令：

3.新建登錄賬戶

（1）新建賬戶的語句：create ?user ?'用戶名'@'來源地址' ?identified ?by ?'密碼';

注：登錄主機為? localhost（本地登錄） 或? ?%（遠程登錄）

?退出當前賬戶，登錄新建的賬戶，看是否能成功進入（只能查看有限庫）：

4.賬戶授權

（1）賬戶權限管理原則：

最小化賦權：生產環境僅授予必要權限（如 select ），避免高危操作（ update / insert ）

分離角色：區分管理賬號（ root ）與應用賬號（業務用戶）

定期清理：刪除/回收閑置賬戶權限

（2）常見的用戶權限：

（3）查看權限的語句：?show grants;（查看當前用戶） show grants for ?'用戶名'@'來源地址';（查看其他賬戶權限）

上例顯示 haha 賬戶只有 usage 默認權限，即連接登錄權限

（4）賦予賬戶權限的語句：grant 權限列表 on 數據庫名.表名 to '用戶名'@'來源地址' ;

?賦予 haha 賬戶查詢mysql庫的權限

查看 user 表的權限信息：

登錄被賦予權限的賬戶，測試一下：

（5）新建遠程賬戶（指定網段）：create user ?'test1'@'網段' identified by '密碼';

（6）新建高權限賬戶（含賦權能力）：

?但是，all 所有權限中不包含給賬戶賦權的權限 grant：

給 haha 賬戶賦予給別的賬戶賦權的權限：

若賦權不成功則先給 root 賬戶增加 system_user 權限

5.回收權限

（1）作用：取消用戶已授權的數據庫操作權限，細化權限管理

（2）語句：revoke ?權限列表/all ?on ?庫名.表名 ?from ?'用戶名'@'來源地址';

回收 select 權限

回收所有權限?

6.刪除賬戶

（1）作用：徹底清除MySQL用戶，連帶其權限配置，清理無用賬戶

（2）語句：drop user '用戶名'@'來源地址';?drop ?user 用戶名（來源地址為%，可不寫來源地址）