數(shù)據(jù)庫(kù)內(nèi)功心法：數(shù)據(jù)庫(kù)基本理論

1、數(shù)據(jù)庫(kù)范式

第一范式：列不可分，eg:【聯(lián)系人】（姓名，性別，電話），一個(gè)聯(lián)系人有家庭電話和公司電話，那么這種表結(jié)構(gòu)設(shè)計(jì)就沒(méi)有達(dá)到 1NF；

第二范式：有主鍵，保證完全依賴。eg:訂單明細(xì)表【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName），Discount（折扣），Quantity（數(shù)量）完全依賴（取決）于主鍵（OderID，ProductID），而 UnitPrice，ProductName 只依賴于 ProductID，不符合2NF；

第三范式：無(wú)傳遞依賴(非主鍵列 A 依賴于非主鍵列 B，非主鍵列 B 依賴于主鍵的情況)，eg:訂單表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主鍵是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依賴的是 CustomerID（非主鍵列），而不是直接依賴于主鍵，它是通過(guò)傳遞才依賴于主鍵，所以不符合 3NF。

2、什么是反模式

范式可以避免數(shù)據(jù)冗余，減少數(shù)據(jù)庫(kù)的空間，減輕維護(hù)數(shù)據(jù)完整性的麻煩。

然而，通過(guò)數(shù)據(jù)庫(kù)范式化設(shè)計(jì)，將導(dǎo)致數(shù)據(jù)庫(kù)業(yè)務(wù)涉及的表變多，并且可能需要將涉及的業(yè)務(wù)表進(jìn)行多表連接查詢，這樣將導(dǎo)致性能變差，且不利于分庫(kù)分表。因此，出于性能優(yōu)先的考量，可能在數(shù)據(jù)庫(kù)的結(jié)構(gòu)中需要使用反模式的設(shè)計(jì)，即空間換取時(shí)間，采取數(shù)據(jù)冗余的方式避免表之間的關(guān)聯(lián)查詢。至于數(shù)據(jù)一致性問(wèn)題，因?yàn)殡y以滿足數(shù)據(jù)強(qiáng)一致性，一般情況下，使存儲(chǔ)數(shù)據(jù)盡可能達(dá)到用戶一致，保證系統(tǒng)經(jīng)過(guò)一段較短的時(shí)間的自我恢復(fù)和修正，數(shù)據(jù)最終達(dá)到一致。

需要謹(jǐn)慎使用反模式設(shè)計(jì)數(shù)據(jù)庫(kù)。一般情況下，盡可能使用范式化的數(shù)據(jù)庫(kù)設(shè)計(jì)，因?yàn)榉妒交臄?shù)據(jù)庫(kù)設(shè)計(jì)能讓產(chǎn)品更加靈活，并且能在數(shù)據(jù)庫(kù)層保持?jǐn)?shù)據(jù)完整性。

有的時(shí)候，提升性能最好的方法是在同一表中保存冗余數(shù)據(jù)，如果能容許少量的臟數(shù)據(jù)，創(chuàng)建一張完全獨(dú)立的匯總表或緩存表是非常好的方法。舉個(gè)例子，設(shè)計(jì)一張“下載次數(shù)表”來(lái)緩存下載次數(shù)信息，可使在海量數(shù)據(jù)的情況下，提高查詢總數(shù)信息的速度。

另外一個(gè)比較典型的場(chǎng)景，出于擴(kuò)展性考慮，可能會(huì)使用 BLOB 和 TEXT 類型的列存儲(chǔ) JSON 結(jié)構(gòu)的數(shù)據(jù)，這樣的好處在于可以在任何時(shí)候，將新的屬性添加到這個(gè)字段中，而不需要更改表結(jié)構(gòu)。但是，這個(gè)設(shè)計(jì)的缺點(diǎn)也比較明顯，就是需要獲取整個(gè)字段內(nèi)容進(jìn)行解碼來(lái)獲取指定的屬性，并且無(wú)法進(jìn)行索引、排序、聚合等操作。因此，如果需要考慮更加復(fù)雜的使用場(chǎng)景，更加建議使用 MongoDB 這樣的文檔型數(shù)據(jù)庫(kù)。

3、數(shù)據(jù)庫(kù)事務(wù)

事務(wù)是一個(gè)不可分割的數(shù)據(jù)庫(kù)操作序列，也是數(shù)據(jù)庫(kù)并發(fā)控制的基本單位，其執(zhí)行的結(jié)果必須使數(shù)據(jù)庫(kù)從一種一致性狀態(tài)變到另一種一致性狀態(tài)。

(1). 事務(wù)的特征

原子性(Atomicity)：事務(wù)所包含的一系列數(shù)據(jù)庫(kù)操作要么全部成功執(zhí)行，要么全部回滾；

一致性(Consistency)：事務(wù)的執(zhí)行結(jié)果必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)到另一個(gè)一致性狀態(tài)；

隔離性(Isolation)：并發(fā)執(zhí)行的事務(wù)之間不能相互影響；

持久性(Durability)：事務(wù)一旦提交，對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變是永久性的。

(2). 事務(wù)并發(fā)帶來(lái)的問(wèn)題

臟讀：一個(gè)事務(wù)讀取了另一個(gè)事務(wù)未提交的數(shù)據(jù)；

不可重復(fù)讀：不可重復(fù)讀的重點(diǎn)是修改，同樣條件下兩次讀取結(jié)果不同，也就是說(shuō)，被讀取的數(shù)據(jù)可以被其它事務(wù)修改；

幻讀：幻讀的重點(diǎn)在于新增或者刪除，同樣條件下兩次讀出來(lái)的記錄數(shù)不一樣。

(3). 隔離級(jí)別

隔離級(jí)別決定了一個(gè)session中的事務(wù)可能對(duì)另一個(gè)session中的事務(wù)的影響。

ANSI標(biāo)準(zhǔn)定義了4個(gè)隔離級(jí)別，MySQL的InnoDB都支持，分別是：

READ UNCOMMITTED（未提交讀）：最低級(jí)別的隔離，通常又稱為dirty read，它允許一個(gè)事務(wù)讀取另一個(gè)事務(wù)還沒(méi)commit的數(shù)據(jù)，這樣可能會(huì)提高性能，但是會(huì)導(dǎo)致臟讀問(wèn)題；

READ COMMITTED（提交讀）：在一個(gè)事務(wù)中只允許對(duì)其它事務(wù)已經(jīng)commit的記錄可見(jiàn)，該隔離級(jí)別不能避免不可重復(fù)讀問(wèn)題；

REPEATABLE READ（可重復(fù)讀）：在一個(gè)事務(wù)開(kāi)始后，其他事務(wù)對(duì)數(shù)據(jù)庫(kù)的修改在本事務(wù)中不可見(jiàn)，直到本事務(wù)commit或rollback。但是，其他事務(wù)的insert/delete操作對(duì)該事務(wù)是可見(jiàn)的，也就是說(shuō)，該隔離級(jí)別并不能避免幻讀問(wèn)題。在一個(gè)事務(wù)中重復(fù)select的結(jié)果一樣，除非本事務(wù)中update數(shù)據(jù)庫(kù)。

SERIALIZABLE（可串行化）：最高級(jí)別的隔離，只允許事務(wù)串行執(zhí)行。

MySQL默認(rèn)的隔離級(jí)別是REPEATABLE READ。

4、什么是存儲(chǔ)過(guò)程？有哪些優(yōu)缺點(diǎn)？

存儲(chǔ)過(guò)程是事先經(jīng)過(guò)編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段SQL語(yǔ)句的集合。進(jìn)一步地說(shuō)，存儲(chǔ)過(guò)程是由一些T-SQL語(yǔ)句組成的代碼塊，這些T-SQL語(yǔ)句代碼像一個(gè)方法一樣實(shí)現(xiàn)一些功能（對(duì)單表或多表的增刪改查），然后再給這個(gè)代碼塊取一個(gè)名字，在用到這個(gè)功能的時(shí)候調(diào)用他就行了。存儲(chǔ)過(guò)程具有以下特點(diǎn)：

• 存儲(chǔ)過(guò)程只在創(chuàng)建時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯，而一般 SQL 語(yǔ)句每執(zhí)行一次就編譯一次，所以使用存儲(chǔ)過(guò)程可提高數(shù)據(jù)庫(kù)執(zhí)行效率；

• 當(dāng)SQL語(yǔ)句有變動(dòng)時(shí)，可以只修改數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程而不必修改代碼；

• 減少網(wǎng)絡(luò)傳輸，在客戶端調(diào)用一個(gè)存儲(chǔ)過(guò)程當(dāng)然比執(zhí)行一串SQL傳輸?shù)臄?shù)據(jù)量要??；

• 通過(guò)存儲(chǔ)過(guò)程能夠使沒(méi)有權(quán)限的用戶在控制之下間接地存取數(shù)據(jù)庫(kù)，從而確保數(shù)據(jù)的安全。

5、簡(jiǎn)單說(shuō)一說(shuō)drop、delete與truncate的區(qū)別

SQL中的drop、delete、truncate都表示刪除，但是三者有一些差別：

Delete用來(lái)刪除表的全部或者一部分?jǐn)?shù)據(jù)行，執(zhí)行delete之后，用戶需要提交(commmit)或者回滾(rollback)來(lái)執(zhí)行刪除或者撤銷刪除， delete命令會(huì)觸發(fā)這個(gè)表上所有的delete觸發(fā)器；

Truncate刪除表中的所有數(shù)據(jù)，這個(gè)操作不能回滾，也不會(huì)觸發(fā)這個(gè)表上的觸發(fā)器，TRUNCATE比delete更快，占用的空間更??；

Drop命令從數(shù)據(jù)庫(kù)中刪除表，所有的數(shù)據(jù)行，索引和權(quán)限也會(huì)被刪除，所有的DML觸發(fā)器也不會(huì)被觸發(fā)，這個(gè)命令也不能回滾。

因此，在不再需要一張表的時(shí)候，用drop；在想刪除部分?jǐn)?shù)據(jù)行時(shí)候，用delete；在保留表而刪除所有數(shù)據(jù)的時(shí)候用truncate。

6、什么叫視圖？游標(biāo)是什么？

視圖是一種虛擬的表，通常是有一個(gè)表或者多個(gè)表的行或列的子集，具有和物理表相同的功能，可以對(duì)視圖進(jìn)行增，刪，改，查等操作。特別地，對(duì)視圖的修改不影響基本表。相比多表查詢，它使得我們獲取數(shù)據(jù)更容易。

游標(biāo)是對(duì)查詢出來(lái)的結(jié)果集作為一個(gè)單元來(lái)有效的處理。游標(biāo)可以定在該單元中的特定行，從結(jié)果集的當(dāng)前行檢索一行或多行?？梢詫?duì)結(jié)果集當(dāng)前行做修改。一般不使用游標(biāo)，但是需要逐條處理數(shù)據(jù)的時(shí)候，游標(biāo)顯得十分重要。

在操作mysql的時(shí)候，我們知道MySQL檢索操作返回一組稱為結(jié)果集的行。這組返回的行都是與 SQL語(yǔ)句相匹配的行（零行或多行）。使用簡(jiǎn)單的 SELECT語(yǔ)句，例如，沒(méi)有辦法得到第一行、下一行或前 10行，也不存在每次一行地處理所有行的簡(jiǎn)單方法（相對(duì)于成批地處理它們）。有時(shí)，需要在檢索出來(lái)的行中前進(jìn)或后退一行或多行。這就是使用游標(biāo)的原因。游標(biāo)（cursor）是一個(gè)存儲(chǔ)在MySQL服務(wù)器上的數(shù)據(jù)庫(kù)查詢，它不是一條 SELECT語(yǔ)句，而是被該語(yǔ)句檢索出來(lái)的結(jié)果集。在存儲(chǔ)了游標(biāo)之后，應(yīng)用程序可以根據(jù)需要滾動(dòng)或?yàn)g覽其中的數(shù)據(jù)。游標(biāo)主要用于交互式應(yīng)用，其中用戶需要滾動(dòng)屏幕上的數(shù)據(jù)，并對(duì)數(shù)據(jù)進(jìn)行瀏覽或做出更改。

7、什么是觸發(fā)器？

觸發(fā)器是與表相關(guān)的數(shù)據(jù)庫(kù)對(duì)象，在滿足定義條件時(shí)觸發(fā)，并執(zhí)行觸發(fā)器中定義的語(yǔ)句集合。觸發(fā)器的這種特性可以協(xié)助應(yīng)用在數(shù)據(jù)庫(kù)端確保數(shù)據(jù)庫(kù)的完整性。

8、超鍵、候選鍵、主鍵、外鍵

• 超鍵：在關(guān)系中能唯一標(biāo)識(shí)元組的屬性集稱為關(guān)系模式的超鍵。一個(gè)屬性可以為作為一個(gè)超鍵，多個(gè)屬性組合在一起也可以作為一個(gè)超鍵。超鍵包含候選鍵和主鍵。

• 候選鍵：是最小超鍵，即沒(méi)有冗余元素的超鍵。

• 主鍵：數(shù)據(jù)庫(kù)表中對(duì)儲(chǔ)存數(shù)據(jù)對(duì)象予以唯一和完整標(biāo)識(shí)的數(shù)據(jù)列或?qū)傩缘慕M合。一個(gè)數(shù)據(jù)列只能有一個(gè)主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。

• 外鍵：在一個(gè)表中存在的另一個(gè)表的主鍵稱此表的外鍵。

9、什么是事務(wù)？什么是鎖？

• 事務(wù)：就是被綁定在一起作為一個(gè)邏輯工作單元的 SQL 語(yǔ)句分組，如果任何一個(gè)語(yǔ)句操作失敗那么整個(gè)操作就被失敗，以后操作就會(huì)回滾到操作前狀態(tài)，或者是上有個(gè)節(jié)點(diǎn)。為了確保要么執(zhí)行，要么不執(zhí)行，就可以使用事務(wù)。要將有組語(yǔ)句作為事務(wù)考慮，就需要通過(guò) ACID 測(cè)試，即原子性，一致性，隔離性和持久性。

• 鎖：在所以的 DBMS 中，鎖是實(shí)現(xiàn)事務(wù)的關(guān)鍵，鎖可以保證事務(wù)的完整性和并發(fā)性。與現(xiàn)實(shí)生活中鎖一樣，它可以使某些數(shù)據(jù)的擁有者，在某段時(shí)間內(nèi)不能使用某些數(shù)據(jù)或數(shù)據(jù)結(jié)構(gòu)。當(dāng)然鎖還分級(jí)別的。

10、數(shù)據(jù)庫(kù)鎖機(jī)制

數(shù)據(jù)庫(kù)鎖定機(jī)制簡(jiǎn)單來(lái)說(shuō)就是數(shù)據(jù)庫(kù)為了保證數(shù)據(jù)的一致性而使各種共享資源在被并發(fā)訪問(wèn)，訪問(wèn)變得有序所設(shè)計(jì)的一種規(guī)則。MySQL各存儲(chǔ)引擎使用了三種類型（級(jí)別）的鎖定機(jī)制：行級(jí)鎖定，頁(yè)級(jí)鎖定和表級(jí)鎖定。

• 表級(jí)鎖定（table-level）：表級(jí)別的鎖定是MySQL各存儲(chǔ)引擎中最大顆粒度的鎖定機(jī)制。該鎖定機(jī)制最大的特點(diǎn)是實(shí)現(xiàn)邏輯非常簡(jiǎn)單，帶來(lái)的系統(tǒng)負(fù)面影響最小。所以獲取鎖和釋放鎖的速度很快。由于表級(jí)鎖一次會(huì)將整個(gè)表鎖定，所以可以很好的避免困擾我們的死鎖問(wèn)題。當(dāng)然，鎖定顆粒度大所帶來(lái)最大的負(fù)面影響就是出現(xiàn)鎖定資源爭(zhēng)用的概率也會(huì)最高，致使并大度大打折扣。表級(jí)鎖分為讀鎖和寫鎖。

• 頁(yè)級(jí)鎖定（page-level）：頁(yè)級(jí)鎖定的特點(diǎn)是鎖定顆粒度介于行級(jí)鎖定與表級(jí)鎖之間，所以獲取鎖定所需要的資源開(kāi)銷，以及所能提供的并發(fā)處理能力也同樣是介于上面二者之間。另外，頁(yè)級(jí)鎖定和行級(jí)鎖定一樣，會(huì)發(fā)生死鎖。

• 行級(jí)鎖定（row-level）：行級(jí)鎖定最大的特點(diǎn)就是鎖定對(duì)象的顆粒度很小，也是目前各大數(shù)據(jù)庫(kù)管理軟件所實(shí)現(xiàn)的鎖定顆粒度最小的。由于鎖定顆粒度很小，所以發(fā)生鎖定資源爭(zhēng)用的概率也最小，能夠給予應(yīng)用程序盡可能大的并發(fā)處理能力而提高一些需要高并發(fā)應(yīng)用系統(tǒng)的整體性能。雖然能夠在并發(fā)處理能力上面有較大的優(yōu)勢(shì)，但是行級(jí)鎖定也因此帶來(lái)了不少弊端。由于鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來(lái)的消耗自然也就更大了。此外，行級(jí)鎖定也最容易發(fā)生死鎖。InnoDB的行級(jí)鎖同樣分為兩種，共享鎖和排他鎖，同樣InnoDB也引入了意向鎖（表級(jí)鎖）的概念，所以也就有了意向共享鎖和意向排他鎖，所以InnoDB實(shí)際上有四種鎖，即共享鎖（S）、排他鎖（X）、意向共享鎖（IS）、意向排他鎖（IX）；

在MySQL數(shù)據(jù)庫(kù)中，使用表級(jí)鎖定的主要是MyISAM，Memory，CSV等一些非事務(wù)性存儲(chǔ)引擎，而使用行級(jí)鎖定的主要是Innodb存儲(chǔ)引擎和NDBCluster存儲(chǔ)引擎，頁(yè)級(jí)鎖定主要是BerkeleyDB存儲(chǔ)引擎的鎖定方式。

而意向鎖的作用就是當(dāng)一個(gè)事務(wù)在需要獲取資源鎖定的時(shí)候，如果遇到自己需要的資源已經(jīng)被排他鎖占用的時(shí)候，該事務(wù)可以需要鎖定行的表上面添加一個(gè)合適的意向鎖。如果自己需要一個(gè)共享鎖，那么就在表上面添加一個(gè)意向共享鎖。而如果自己需要的是某行（或者某些行）上面添加一個(gè)排他鎖的話，則先在表上面添加一個(gè)意向排他鎖。意向共享鎖可以同時(shí)并存多個(gè)，但是意向排他鎖同時(shí)只能有一個(gè)存在。