面試中吃了幾次數(shù)據(jù)庫上面的虧，做個(gè)總結(jié)（關(guān)系型數(shù)據(jù)庫）

數(shù)據(jù)庫知識點(diǎn)

感覺面試的必問的一塊內(nèi)容了，但一開始重新看數(shù)據(jù)庫的時(shí)候，就看了下數(shù)據(jù)庫的一些屬性，SQL規(guī)則，有哪幾種鎖，ACID，索引。
結(jié)果到了面試的時(shí)候就發(fā)現(xiàn)完全不是這么簡單了...
一個(gè)一個(gè)來，慢慢補(bǔ)充。

• 數(shù)據(jù)庫事務(wù)（ACID怎么實(shí)現(xiàn)的，隔離級別，實(shí)際應(yīng)用）
• 鎖（什么時(shí)候怎么加）
• 索引（有幾種，怎么用）
• 存儲結(jié)構(gòu)（數(shù)據(jù)放在哪，存儲過程）
• SQL執(zhí)行過程
• 數(shù)據(jù)庫模塊

數(shù)據(jù)庫事務(wù)

定義:數(shù)據(jù)庫事務(wù)是構(gòu)成單一邏輯工作單元的操作集合（通俗的說就是SQL執(zhí)行單元）

事務(wù)四大特征（ACID）

• 原子性：事務(wù)是最小單位，不可再分
  上面是廢話，說的現(xiàn)實(shí)一點(diǎn)應(yīng)該是，只有事務(wù)中所有的操作都成功，事務(wù)才會(huì)提交。如果某個(gè)失敗，則必須要會(huì)退到事務(wù)執(zhí)行之前的狀態(tài)，執(zhí)行成功的SQL需要被撤銷。
  如何實(shí)現(xiàn)？ --回滾日志
  想要保證事務(wù)的原子性，就需要在異常發(fā)生時(shí)，對已經(jīng)執(zhí)行的操作進(jìn)行回滾，而在 MySQL 中，恢復(fù)機(jī)制是通過回滾日志（undo log）實(shí)現(xiàn)的，所有事務(wù)進(jìn)行的修改都會(huì)先記錄到這個(gè)回滾日志中，然后在對數(shù)據(jù)庫中的對應(yīng)行進(jìn)行寫入。

  回滾日志除了能夠在發(fā)生錯(cuò)誤或者用戶執(zhí)行 ROLLBACK 時(shí)提供回滾相關(guān)的信息，它還能夠在整個(gè)系統(tǒng)發(fā)生崩潰、數(shù)據(jù)庫進(jìn)程直接被殺死后，當(dāng)用戶再次啟動(dòng)數(shù)據(jù)庫進(jìn)程時(shí)，還能夠立刻通過查詢回滾日志將之前未完成的事務(wù)進(jìn)行回滾，這也就需要回滾日志必須先于數(shù)據(jù)持久化到磁盤上，是我們需要先寫日志后寫數(shù)據(jù)庫的主要原因。
  回滾日志并不能將數(shù)據(jù)庫物理地恢復(fù)到執(zhí)行語句或者事務(wù)之前的樣子；它是邏輯日志，當(dāng)回滾日志被使用時(shí)，它只會(huì)按照日志邏輯地將數(shù)據(jù)庫中的修改撤銷掉看，可以理解為，我們在事務(wù)中使用的每一條 INSERT 都對應(yīng)了一條 DELETE，每一條 UPDATE 也都對應(yīng)一條相反的 UPDATE 語句。

• 持久性：一旦事務(wù)提交成功后，事務(wù)中所有的數(shù)據(jù)操作都必須被持久化到數(shù)據(jù)庫中
  即數(shù)據(jù)能夠被安全存儲在磁盤上。
  注意：當(dāng)事務(wù)已經(jīng)被提交之后，就無法再次回滾了，唯一能夠撤回已經(jīng)提交的事務(wù)的方式就是創(chuàng)建一個(gè)相反的事務(wù)對原操作進(jìn)行『補(bǔ)償』，這也是事務(wù)持久性的體現(xiàn)之一。
  如何實(shí)現(xiàn)？ --重做日志
  與原子性一樣，事務(wù)的持久性也是通過日志來實(shí)現(xiàn)的，MySQL 使用重做日志（redo log）實(shí)現(xiàn)事務(wù)的持久性，重做日志由兩部分組成，一是內(nèi)存中的重做日志緩沖區(qū)，因?yàn)橹刈鋈罩揪彌_區(qū)在內(nèi)存中，所以它是易失的，另一個(gè)就是在磁盤上的重做日志文件，它是持久的。
  當(dāng)我們在一個(gè)事務(wù)中嘗試對數(shù)據(jù)進(jìn)行修改時(shí)，它會(huì)先將數(shù)據(jù)從磁盤讀入內(nèi)存，并更新內(nèi)存中緩存的數(shù)據(jù)，然后生成一條重做日志并寫入重做日志緩存，當(dāng)事務(wù)真正提交時(shí)，MySQL 會(huì)將重做日志緩存中的內(nèi)容刷新到重做日志文件，再將內(nèi)存中的數(shù)據(jù)更新到磁盤上。
  在 InnoDB 中，重做日志都是以 512 字節(jié)的塊的形式進(jìn)行存儲的，同時(shí)因?yàn)閴K的大小與磁盤扇區(qū)大小相同，所以重做日志的寫入可以保證原子性，不會(huì)由于機(jī)器斷電導(dǎo)致重做日志僅寫入一半并留下臟數(shù)據(jù)。
  除了所有對數(shù)據(jù)庫的修改會(huì)產(chǎn)生重做日志，因?yàn)榛貪L日志也是需要持久存儲的，它們也會(huì)創(chuàng)建對應(yīng)的重做日志，在發(fā)生錯(cuò)誤后，數(shù)據(jù)庫重啟時(shí)會(huì)從重做日志中找出未被更新到數(shù)據(jù)庫磁盤中的日志重新執(zhí)行以滿足事務(wù)的持久性。

回滾日志和重做日志

到現(xiàn)在為止我們了解了 MySQL 中的兩種日志，回滾日志（undo log）和重做日志（redo log）；在數(shù)據(jù)庫系統(tǒng)中，事務(wù)的原子性和持久性是由事務(wù)日志（transaction log）保證的，在實(shí)現(xiàn)時(shí)也就是上面提到的兩種日志，前者用于對事務(wù)的影響進(jìn)行撤銷，后者在錯(cuò)誤處理時(shí)對已經(jīng)提交的事務(wù)進(jìn)行重做，它們能保證兩點(diǎn)：

1. 發(fā)生錯(cuò)誤或者需要回滾的事務(wù)能夠成功回滾（原子性）；
2. 在事務(wù)提交后，數(shù)據(jù)沒來得及寫會(huì)磁盤就宕機(jī)時(shí)，在下次重新啟動(dòng)后能夠成功恢復(fù)數(shù)據(jù)（持久性）；

在數(shù)據(jù)庫中，這兩種日志經(jīng)常都是一起工作的，我們可以將它們整體看做一條事務(wù)日志，其中包含了事務(wù)的 ID、修改的行元素以及修改前后的值。

事務(wù)日志

• 隔離性
  事務(wù)的隔離性是數(shù)據(jù)庫處理數(shù)據(jù)的基礎(chǔ)之一，如果沒有數(shù)據(jù)庫的事務(wù)之間沒有隔離性，就會(huì)發(fā)生在級聯(lián)回滾（并行情況下的事務(wù)依賴）等問題，造成性能上的巨大損失。
  如何實(shí)現(xiàn)？ --隔離級別
  所以說數(shù)據(jù)庫的隔離性和一致性其實(shí)是一個(gè)需要開發(fā)者去權(quán)衡的問題，為數(shù)據(jù)庫提供什么樣的隔離性層級也就決定了數(shù)據(jù)庫的性能以及可以達(dá)到什么樣的一致性；

  1. RAED UNCOMMITED：使用查詢語句不會(huì)加鎖，可能會(huì)讀到未提交的行（Dirty Read）；
  2. READ COMMITED：只對記錄加記錄鎖，而不會(huì)在記錄之間加間隙鎖，所以允許新的記錄插入到被鎖定記錄的附近，所以再多次使用查詢語句時(shí)，可能得到不同的結(jié)果（Non-Repeatable Read）；
  3. REPEATABLE READ：多次讀取同一范圍的數(shù)據(jù)會(huì)返回第一次查詢的快照，不會(huì)返回不同的數(shù)據(jù)行，但是可能發(fā)生幻讀（Phantom Read）；
  4. SERIALIZABLE：InnoDB 隱式地將全部的查詢語句加上共享鎖，解決了幻讀的問題；

  大部分的數(shù)據(jù)庫中都使用了 READ COMMITED 作為默認(rèn)的事務(wù)隔離級別，但是 MySQL 使用了 REPEATABLE READ 作為默認(rèn)配置；

隔離級別的實(shí)現(xiàn)

鎖
MySQL 和常見數(shù)據(jù)庫中的鎖都分為兩種，共享鎖（Shared）和互斥鎖（Exclusive），前者也叫讀鎖，后者叫寫鎖。

讀鎖與寫鎖

讀鎖保證了讀操作可以并發(fā)執(zhí)行，相互不會(huì)影響，而寫鎖保證了在更新數(shù)據(jù)庫數(shù)據(jù)時(shí)不會(huì)有其他的事務(wù)訪問或者更改同一條記錄造成不可預(yù)知的問題。

時(shí)間戳
除了鎖，另一種實(shí)現(xiàn)事務(wù)的隔離性的方式就是通過時(shí)間戳，例如 PostgreSQL 會(huì)為每一條記錄保留兩個(gè)字段；讀時(shí)間戳中保存了所有訪問該記錄的事務(wù)中的最大時(shí)間戳，而記錄行的寫時(shí)間戳中保存了將記錄改到當(dāng)前值的事務(wù)的時(shí)間戳。

時(shí)間戳

使用時(shí)間戳實(shí)現(xiàn)事務(wù)的隔離性時(shí)，往往都會(huì)使用樂觀鎖，先對數(shù)據(jù)進(jìn)行修改，在寫回時(shí)再去判斷當(dāng)前值，也就是時(shí)間戳是否改變過，如果沒有改變過，就寫入，否則生成一個(gè)新的時(shí)間戳并再次更新數(shù)據(jù)。

多版本和快照隔離
通過維護(hù)多個(gè)版本的數(shù)據(jù)，數(shù)據(jù)庫可以允許事務(wù)在數(shù)據(jù)被其他事務(wù)更新時(shí)對舊版本的數(shù)據(jù)進(jìn)行讀取，很多數(shù)據(jù)庫都對這一機(jī)制進(jìn)行了實(shí)現(xiàn)；因?yàn)樗械淖x操作不再需要等待寫鎖的釋放，所以能夠顯著地提升讀的性能，MySQL 和 PostgreSQL 都對這一機(jī)制進(jìn)行自己的實(shí)現(xiàn)，也就是 MVCC，雖然各自實(shí)現(xiàn)的方式有所不同，MySQL 就通過文章中提到的回滾日志實(shí)現(xiàn)了 MVCC，保證事務(wù)并行執(zhí)行時(shí)能夠不等待互斥鎖的釋放而直接獲取數(shù)據(jù)。

• 一致性
  數(shù)據(jù)庫對于 ACID 中的一致性的定義是這樣的：如果一個(gè)事務(wù)原子地在一個(gè)一致的數(shù)據(jù)庫中獨(dú)立運(yùn)行，那么在它執(zhí)行之后，數(shù)據(jù)庫的狀態(tài)一定是一致的。對于這個(gè)概念，它的第一層意思就是對于數(shù)據(jù)完整性的約束，包括主鍵約束、引用約束以及一些約束檢查等等，在事務(wù)的執(zhí)行的前后以及過程中不會(huì)違背對數(shù)據(jù)完整性的約束，所有對數(shù)據(jù)庫寫入的操作都應(yīng)該是合法的，并不能產(chǎn)生不合法的數(shù)據(jù)狀態(tài)。
  也就是說，數(shù)據(jù)庫 ACID 中的一致性對事務(wù)的要求不止包含對數(shù)據(jù)完整性以及合法性的檢查，還包含應(yīng)用層面邏輯的正確。

鎖

這里又要分 MyISAM 和 InnoDB，表鎖與行鎖，共享鎖和排他鎖，意向鎖，記錄鎖，間隙鎖，意向鎖等。鎖詳解
意向鎖：

• 意向共享鎖（IS）事務(wù)打算給數(shù)據(jù)行共享鎖;
  事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖
• 意向排他鎖（IX）事務(wù)打算給數(shù)據(jù)行加排他鎖;
  事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖

間隙鎖（Next-Key鎖）：
當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù),并請求共享或排他鎖時(shí),InnoDB會(huì)給符合條件的已有數(shù)據(jù)的索引項(xiàng)加鎖;
對于鍵值在條件范圍內(nèi)但并不存在的記錄,叫做“間隙(GAP)”,InnoDB也會(huì)對這個(gè)“間隙”加鎖,這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）.
間隙鎖的目的：

• 防止幻讀,以滿足相關(guān)隔離級別的要求
• 滿足其恢復(fù)和復(fù)制的需要
  在使用范圍條件檢索并鎖定記錄時(shí)；InnoDB 這種加鎖機(jī)制會(huì)阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入,這往往會(huì)造成嚴(yán)重的鎖等待;
  因此,在實(shí)際開發(fā)中,尤其是并發(fā)插入較多的應(yīng)用;我們要盡量優(yōu)化業(yè)務(wù)邏輯,盡量使用相等條件來訪問更新數(shù)據(jù),避免使用范圍條件。

死鎖：
注意：ＭyISAM表鎖是deadlock free的，這是因?yàn)椋蛓ISAM總是一次性獲得所需的全部鎖，要么全部滿足，要么等待，因此不會(huì)出現(xiàn)死鎖。
但在InnoDB中，除單個(gè)SQL組成的事務(wù)外，鎖是逐步獲得的，這就決定了InnoDB發(fā)生死鎖是可能的。
發(fā)生死鎖后，InnoDB一般都能自動(dòng)檢測到，并使一個(gè)事務(wù)釋放鎖并退回，另一個(gè)事務(wù)獲得鎖，繼續(xù)完成事務(wù)。
這里列舉兩種死鎖情況：

• 一個(gè)用戶A 訪問表A(鎖住了表A),然后又訪問表B；另一個(gè)用戶B 訪問表B(鎖住了表B)，然后企圖訪問表A；這時(shí)用戶A由于用戶B已經(jīng)鎖住表B，它必須等待用戶B釋放表B才能繼續(xù)，同樣用戶B要等用戶A釋放表A才能繼續(xù)，這就死鎖就產(chǎn)生了。
• 用戶A查詢一條紀(jì)錄，然后修改該條紀(jì)錄；這時(shí)用戶B修改該條紀(jì)錄，這時(shí)用戶A的事務(wù)里鎖的性質(zhì)由查詢的共享鎖企圖上升到獨(dú)占鎖，而用戶B里的獨(dú)占鎖由于A 有共享鎖存在所以必須等A釋放掉共享鎖，而A由于B的獨(dú)占鎖而無法上升的獨(dú)占鎖也就不可能釋放共享鎖，于是出現(xiàn)了死鎖。

可以設(shè)計(jì)索引，并盡量使用索引訪問數(shù)據(jù)，使加鎖更精確，從而減少鎖沖突的機(jī)會(huì)。

數(shù)據(jù)庫索引為什么采用B+樹？

1. B+樹的磁盤讀寫代價(jià)更低：B+樹的內(nèi)部節(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針，因此其內(nèi)部節(jié)點(diǎn)相對B樹更小，如果把所有同一內(nèi)部節(jié)點(diǎn)的關(guān)鍵字存放在同一盤塊中，那么盤塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多，一次性讀入內(nèi)存的需要查找的關(guān)鍵字也就越多，相對IO讀寫次數(shù)就降低了。
2. B+樹的查詢效率更加穩(wěn)定：由于非終結(jié)點(diǎn)并不是最終指向文件內(nèi)容的結(jié)點(diǎn)，而只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引。所以任何關(guān)鍵字的查找必須走一條從根結(jié)點(diǎn)到葉子結(jié)點(diǎn)的路。所有關(guān)鍵字查詢的路徑長度相同，導(dǎo)致每一個(gè)數(shù)據(jù)的查詢效率相當(dāng)。
3. 由于B+樹的數(shù)據(jù)都存儲在葉子結(jié)點(diǎn)中，分支結(jié)點(diǎn)均為索引，方便掃庫，只需要掃一遍葉子結(jié)點(diǎn)即可，但是B樹因?yàn)槠浞种ЫY(jié)點(diǎn)同樣存儲著數(shù)據(jù)，我們要找到具體的數(shù)據(jù)，需要進(jìn)行一次中序遍歷。

數(shù)據(jù)庫是怎么實(shí)現(xiàn)鎖的？

在InnoDB內(nèi)部用uint32類型數(shù)據(jù)表示鎖的類型, 最低的 4 個(gè) bit 表示 lock_mode, 5-8 bit 表示 lock_type(目前只用了 5 和 6 位，大小為 16 和 32 ，表示 LOCK_TABLE 和 LOCK_REC), 剩下的高位 bit 表示行鎖的類型record_lock_type;

1. lock_mode:
   lock_is/lock_ix/lock_s/lock_x
   （表級意向共享鎖，表級意向排他鎖，行共享鎖，行排他鎖）
2. record_lock_type：
   LOCK_ORDINARY(next-key lock，鎖住記錄本身和記錄之前的 gap，當(dāng)用RR隔離級別的時(shí)候，為了防止當(dāng)前讀語句的幻讀使用)
   LOCK_GAP（間隙鎖，只鎖住索引記錄之間或者第一條索引記錄前或者最后一條索引記錄之后的范圍，并不鎖住記錄本身）
   LOCK_REC_NOT_GAP（記錄鎖，僅鎖住記錄行，不鎖范圍）
   LOCK_INSERT_INTENTION（插入意向鎖，當(dāng)插入索引記錄的時(shí)候用來判斷是否有其他事務(wù)的范圍鎖沖突，如果有就需要等待）

關(guān)于具體SQL的加鎖分析，可參考 《非常好的加鎖邏輯分析》