MySQL設(shè)計(jì)三范式的理解

目前關(guān)系數(shù)據(jù)庫有六種范式：
第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又稱完美范式）。滿足最低要求的范式是第一范式（1NF）。在第一范式的基礎(chǔ)上進(jìn)一步滿足更多規(guī)范要求的稱為第二范式（2NF），其余范式以次類推。一般說來，數(shù)據(jù)庫只需滿足第三范式(3NF）就行了。所以這里就只記錄三范式相關(guān)的知識(shí)。

三范式：
1NF:字段不可分(原子性 字段不可再分,否則就不是關(guān)系數(shù)據(jù)庫);
原子性解釋：數(shù)據(jù)庫表的每一列都是不可分割的原子數(shù)據(jù)項(xiàng)，而不能是集合，數(shù)組，記錄等非原子數(shù)據(jù)項(xiàng)。如果實(shí)體中的某個(gè)屬性有多個(gè)值時(shí)，必須拆分為不同的屬性 。通俗理解即一個(gè)字段只存儲(chǔ)一項(xiàng)信息。

2NF:有主鍵，非主鍵字段依賴主鍵(唯一性 一個(gè)表只說明一個(gè)事物);
即滿足第一范式前提，當(dāng)存在多個(gè)主鍵的時(shí)候，才會(huì)發(fā)生不符合第二范式的情況。

3NF:非主鍵字段不能相互依賴(每列都與主鍵有直接關(guān)系，不存在傳遞依賴);
滿足第三范式（3NF）必須先滿足第二范式（2NF）。簡而言之，第三范式（3NF）要求一個(gè)數(shù)據(jù)庫表中不包含已在其它表中已包含的非主鍵字段。就是說，表的信息，如果能夠被推導(dǎo)出來，就不應(yīng)該單獨(dú)的設(shè)計(jì)一個(gè)字段來存放(能盡量外鍵join就用外鍵join)。很多時(shí)候，我們?yōu)榱藵M足第三范式往往會(huì)把一張表分成多張表。

反三范式
沒有冗余的數(shù)據(jù)庫未必是最好的數(shù)據(jù)庫，有時(shí)為了提高運(yùn)行效率，提高讀性能，就必須降低范式標(biāo)準(zhǔn)，適當(dāng)保留冗余數(shù)據(jù)。具體做法是： 在概念數(shù)據(jù)模型設(shè)計(jì)時(shí)遵守第三范式，降低范式標(biāo)準(zhǔn)的工作放到物理數(shù)據(jù)模型設(shè)計(jì)時(shí)考慮。降低范式就是增加字段，減少了查詢時(shí)的關(guān)聯(lián)，提高查詢效率，因?yàn)樵跀?shù)據(jù)庫的操作中查詢的比例要遠(yuǎn)遠(yuǎn)大于DML的比例。但是反范式化一定要適度，并且在原本已滿足三范式的基礎(chǔ)上再做調(diào)整的。

MySQL視圖篇

視圖是一種虛擬存在的表，對(duì)于使用視圖的用戶來說基本上是透明的。
視圖并不實(shí)際存在于數(shù)據(jù)庫，行和列的數(shù)據(jù)來自于自定義視圖查詢中使用的表，并且是在使用視圖時(shí)動(dòng)態(tài)生成的。
視圖
百度百科定義了什么是視圖，但是對(duì)缺乏相關(guān)知識(shí)的人可能還是難以理解或者只有一個(gè)比較抽象的概念，筆者舉個(gè)例子來解釋下什么是視圖。
朕想要了解皇宮的國庫的相關(guān)情況，想知道酒窖有什么酒，剩多少，窖藏多少年，于是派最信任的高公公去清點(diǎn)，高公公去國庫清點(diǎn)后報(bào)給了朕；朕又想知道藏書情況，于是又派高公公去清點(diǎn)并回來報(bào)告給朕，又想知道金銀珠寶如何，又派高公公清點(diǎn)。。。過一段時(shí)間又想知道藏書情況，高公公還得重新再去清點(diǎn)，皇上問一次，高公公就得跑一次路。
后來皇上覺得高公公不容易，就成立了國庫管理部門，小鄧子負(fù)責(zé)酒窖，小卓子負(fù)責(zé)藏書，而小六子負(fù)責(zé)金庫的清點(diǎn)。。。后來皇上每次想了解國庫就直接問話負(fù)責(zé)人，負(fù)責(zé)人就按照職責(zé)要求進(jìn)行匯報(bào)。

優(yōu)勢：

簡單：不用考慮表的關(guān)聯(lián)，只看到結(jié)果集

安全：用戶只能訪問被允許查詢的結(jié)果集

數(shù)據(jù)獨(dú)立：可以屏蔽表結(jié)構(gòu)的變化給用戶帶來的影響

視圖的基本操作

創(chuàng)建
CREATE VIEW <視圖名> AS <SELECT語句>

修改(修改視圖是指修改 MySQL 數(shù)據(jù)庫中存在的視圖，當(dāng)基本表的某些字段發(fā)生變化時(shí)，可以通過修改視圖來保持與基本表的一致性。) 如果對(duì)視圖增加或刪除記錄，實(shí)際上是對(duì)基本表增加或刪除記錄。
ALTER VIEW <視圖名> AS <SELECT語句>

刪除
DROP VIEW <視圖名1> [ , <視圖名2> …]

查看視圖
DESCRIBE 視圖名；

一、事務(wù)的基本要素（ACID）

1、A****原子性（Atomicity）：事務(wù)開始后所有操作，要么全部做完，要么全部不做，不可能停滯在中間環(huán)節(jié)。事務(wù)執(zhí)行過程中出錯(cuò)，會(huì)回滾到事務(wù)開始前的狀態(tài)，所有的操作就像沒有發(fā)生一樣。也就是說事務(wù)是一個(gè)不可分割的整體，就像化學(xué)中學(xué)過的原子，是物質(zhì)構(gòu)成的基本單位。
　　2、C****一致性（Consistency）：事務(wù)開始前和結(jié)束后，數(shù)據(jù)庫的完整性約束沒有被破壞 。比如A向B轉(zhuǎn)賬，不可能A扣了錢，B卻沒收到。
　　3、I****隔離性（Isolation）：同一時(shí)間，只允許一個(gè)事務(wù)請(qǐng)求同一數(shù)據(jù)，不同的事務(wù)之間彼此沒有任何干擾。比如A正在從一張銀行卡中取錢，在A取錢的過程結(jié)束前，B不能向這張卡轉(zhuǎn)賬。
　　 4、D****持久性（Durability）：事務(wù)完成后，事務(wù)對(duì)數(shù)據(jù)庫的所有更新將被保存到數(shù)據(jù)庫，不能回滾。

二、事務(wù)的并發(fā)問題

1、**臟讀：**事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后B回滾操作，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)

2、**不可重復(fù)讀：**事務(wù) A 多次讀取同一數(shù)據(jù)，事務(wù) B 在事務(wù)A多次讀取的過程中，對(duì)數(shù)據(jù)作了更新并提交，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)，結(jié)果 不一致。

3、**幻讀：**系統(tǒng)管理員A將數(shù)據(jù)庫中所有學(xué)生的成績從具體分?jǐn)?shù)改為ABCDE等級(jí)，但是系統(tǒng)管理員B就在這個(gè)時(shí)候插入了一條具體分?jǐn)?shù)的記錄，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒有改過來，就好像發(fā)生了幻覺一樣，這就叫幻讀。

小結(jié)：不可重復(fù)讀的和幻讀很容易混淆，不可重復(fù)讀側(cè)重于修改，幻讀側(cè)重于新增或刪除。解決不可重復(fù)讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表

mysql默認(rèn)的事務(wù)隔離級(jí)別為repeatable-read

1、事務(wù)隔離級(jí)別為讀提交時(shí)，寫數(shù)據(jù)只會(huì)鎖住相應(yīng)的行

2、事務(wù)隔離級(jí)別為可重復(fù)讀時(shí)，如果檢索條件有索引（包括主鍵索引）的時(shí)候，默認(rèn)加鎖方式是next-key 鎖；如果檢索條件沒有索引，更新數(shù)據(jù)時(shí)會(huì)鎖住整張表。一個(gè)間隙被事務(wù)加了鎖，其他事務(wù)是不能在這個(gè)間隙插入記錄的，這樣可以防止幻讀。

3、事務(wù)隔離級(jí)別為串行化時(shí)，讀寫數(shù)據(jù)都會(huì)鎖住整張表

4、隔離級(jí)別越高，越能保證數(shù)據(jù)的完整性和一致性，但是對(duì)并發(fā)性能的影響也越大。

如何解決臟讀、不可重復(fù)讀和幻讀問題
（1） 臟讀：修改時(shí)加排他鎖，直到事務(wù)提交后才釋放，讀取時(shí)加共享鎖，讀取完釋放事務(wù)1讀取數(shù)據(jù)時(shí)加上共享鎖后（這樣在事務(wù)1讀取數(shù)據(jù)的過程中，其他事務(wù)就不會(huì)修改該數(shù)據(jù)），不允許任何事物操作該數(shù)據(jù)，只能讀取，之后1如果有更新操作，那么會(huì)轉(zhuǎn)換為排他鎖，其他事務(wù)更無權(quán)參與進(jìn)來讀寫，這樣就防止了臟讀問題。
（2）不可重復(fù)讀：讀取數(shù)據(jù)時(shí)加共享鎖，寫數(shù)據(jù)時(shí)加排他鎖，都是事務(wù)提交才釋放鎖。讀取時(shí)候不允許其他事物修改該數(shù)據(jù)，不管數(shù)據(jù)在事務(wù)過程中讀取多少次，數(shù)據(jù)都是一致的，避免了不可重復(fù)讀問題
（3）幻讀問題：采用Gap Lock模式，鎖定檢索范圍為只讀，這樣就避免了幻讀。

事務(wù)隔離級(jí)別：

（1）READ_UNCOMMITTED
　　這是事務(wù)最低的隔離級(jí)別，它充許另外一個(gè)事務(wù)可以看到這個(gè)事務(wù)未提交的數(shù)據(jù)。
　　解決第一類丟失更新的問題，但是會(huì)出現(xiàn)臟讀、不可重復(fù)讀、幻讀、第二類丟失更新的問題。
（2）READ_COMMITTED
　　保證一個(gè)事務(wù)修改的數(shù)據(jù)提交后才能被另外一個(gè)事務(wù)讀取，即另外一個(gè)事務(wù)不能讀取該事務(wù)未提交的數(shù)據(jù)。
　　解決第一類丟失更新和臟讀的問題，但會(huì)出現(xiàn)不可重復(fù)讀、幻讀、第二類丟失更新的問題
（3）REPEATABLE_READ
　　保證一個(gè)事務(wù)相同條件下前后兩次獲取的數(shù)據(jù)是一致的
解決第一類丟失更新，臟讀、不可重復(fù)讀、第二類丟失更新的問題，但會(huì)出幻讀。
（4）SERIALIZABLE
　　事務(wù)被處理為順序執(zhí)行。解決所有問題

**

存儲(chǔ)過程

**
存儲(chǔ)過程是一組為了完成特定功能的 SQL 語句集合。
使用存儲(chǔ)過程的目的是將常用或復(fù)雜的工作預(yù)先用 SQL 語句寫好并用一個(gè)指定名稱存儲(chǔ)起來，這個(gè)過程經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫服務(wù)器中，因此稱為存儲(chǔ)過程。
存儲(chǔ)過程通常有如下優(yōu)點(diǎn)：

封裝性
存儲(chǔ)過程被創(chuàng)建后，可以在程序中被多次調(diào)用，而不必重新編寫該存儲(chǔ)過程的 SQL 語句，并且數(shù)據(jù)庫專業(yè)人員可以隨時(shí)對(duì)存儲(chǔ)過程進(jìn)行修改，而不會(huì)影響到調(diào)用它的應(yīng)用程序源代碼。

可增強(qiáng) SQL 語句的功能和靈活性
存儲(chǔ)過程可以用流程控制語句編寫，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運(yùn)算。

可減少網(wǎng)絡(luò)流量
由于存儲(chǔ)過程是在服務(wù)器端運(yùn)行的，且執(zhí)行速度快，因此當(dāng)客戶計(jì)算機(jī)上調(diào)用該存儲(chǔ)過程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)用語句，從而可降低網(wǎng)絡(luò)負(fù)載。

高性能
存儲(chǔ)過程執(zhí)行一次后，產(chǎn)生的二進(jìn)制代碼就駐留在緩沖區(qū)，在以后的調(diào)用中，只需要從緩沖區(qū)中執(zhí)行二進(jìn)制代碼即可，從而提高了系統(tǒng)的效率和性能。

提高數(shù)據(jù)庫的安全性和數(shù)據(jù)的完整性
使用存儲(chǔ)過程可以完成所有數(shù)據(jù)庫操作，并且可以通過編程的方式控制數(shù)據(jù)庫信息訪問的權(quán)限。

存儲(chǔ)過程基本語法

CREATE PROCEDURE <過程名> ( [過程參數(shù)[,…] ] ) <過程體>
[過程參數(shù)[,…] ] 格式
[ IN | OUT | INOUT ] <參數(shù)名> <類型>


刪除存儲(chǔ)過程

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <過程名>

整理中，持續(xù)更新，學(xué)習(xí)！！！

總結(jié)

以上是生活随笔為你收集整理的数据库（三范式，视图，事务隔离级别，存储过程）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。