最全的數(shù)據(jù)庫-MySQL知識(shí)匯總4

7、E-R圖

E-R圖也稱實(shí)體-聯(lián)系圖(Entity Relationship Diagram)，提供了表示實(shí)體類型、屬性和聯(lián)系的方法，用來描述現(xiàn)實(shí)世界的概念模型。

E-R方法是“實(shí)體-聯(lián)系方法”（Entity-Relationship Approach）的簡稱。它是描述現(xiàn)實(shí)世界概念結(jié)構(gòu)模型的有效方法，是表示概念模型的一種方式，用矩形表示實(shí)體型，矩形框內(nèi)寫明實(shí)體名；用橢圓表示實(shí)體的屬性，并用無向邊將其與相應(yīng)的實(shí)體型連接起來；用菱形表示實(shí)體型之間的聯(lián)系，在菱形框內(nèi)寫明聯(lián)系名，并用無向邊分別與有關(guān)實(shí)體型連接起來，同時(shí)在無向邊旁標(biāo)上聯(lián)系的類型（1:1,1:n或m:n）。

在ER圖中有如下四個(gè)成分：

矩形框： 表示實(shí)體，在框中記入實(shí)體名。

菱形框： 表示聯(lián)系，在框中記入聯(lián)系名。

橢圓形框： 表示實(shí)體或聯(lián)系的屬性，將屬性名記入框中。對(duì)于主屬性名，則在其名稱下劃一下劃線。

連線： 實(shí)體與屬性之間；實(shí)體與聯(lián)系之間；聯(lián)系與屬性之間用直線相連，并在直線上標(biāo)注聯(lián)系的類型。（對(duì)于一對(duì)一聯(lián)系，要在兩個(gè)實(shí)體連線方向各寫1；對(duì)于一對(duì)多聯(lián)系，要在一的一方寫1，多的一方寫N；對(duì)于多對(duì)多關(guān)系，則要在兩個(gè)實(shí)體連線方向各寫N,M。)

實(shí)體型(Entity)： 具有相同屬性的實(shí)體具有相同的特征和性質(zhì)，用實(shí)體名及其屬性名集合來抽象和刻畫同類實(shí)體;在E-R圖中用矩形表示，矩形框內(nèi)寫明實(shí)體名；比如學(xué)生張三豐、學(xué)生李尋歡都是實(shí)體。如果是弱實(shí)體的話，在矩形外面再套實(shí)線矩形。

屬性(Attribute)： 實(shí)體所具有的某一特性，一個(gè)實(shí)體可由若干個(gè)屬性來刻畫。在E-R圖中用橢圓形表示，并用無向邊將其與相應(yīng)的實(shí)體連接起來；比如學(xué)生的姓名、學(xué)號(hào)、性別、都是屬性。如果是多值屬性的話，在橢圓形外面再套實(shí)線橢圓，如果是派生屬性則用虛線橢圓表示。

聯(lián)系(Relationship)： 聯(lián)系也稱關(guān)系，信息世界中反映實(shí)體內(nèi)部或?qū)嶓w之間的聯(lián)系。實(shí)體內(nèi)部的聯(lián)系通常是指組成實(shí)體的各屬性之間的聯(lián)系；實(shí)體之間的聯(lián)系通常是指不同實(shí)體集之間的聯(lián)系。在E-R圖中用菱形表示，菱形框內(nèi)寫明聯(lián)系名，并用無向邊分別與有關(guān)實(shí)體連接起來，同時(shí)在無向邊旁標(biāo)上聯(lián)系的類型（1 : 1，1 : n或m : n），比如老師給學(xué)生授課存在授課關(guān)系，學(xué)生選課存在選課關(guān)系。如果是弱實(shí)體的聯(lián)系則在菱形外面再套菱形。

聯(lián)系可分為以下 3 種類型：

• 一對(duì)一聯(lián)系(1 ∶1)

例如，一個(gè)班級(jí)有一個(gè)班長，而每個(gè)班長只在一個(gè)班級(jí)任職，則班級(jí)與班長的聯(lián)系是一對(duì)一的。

• 一對(duì)多聯(lián)系(1 ∶N)

例如，某校教師與課程之間存在一對(duì)多的聯(lián)系“教”，即每位教師可以教多門課程，但是每門課程只能由一位教師來教。

• 多對(duì)多聯(lián)系(M ∶N)

例如，學(xué)生與課程間的聯(lián)系(“學(xué) ”)是多對(duì)多的，即一個(gè)學(xué)生可以學(xué)多門課程，而每門課程可以有多個(gè)學(xué)生來學(xué)。聯(lián)系也可能有屬性。例如，學(xué)生“ 學(xué)” 某門課程所取得的成績，既不是學(xué)生的屬性也不是課程的屬性。由于“ 成績” 既依賴于某名特定的學(xué)生又依賴于某門特定的課程，所以它是學(xué)生與課程之間的聯(lián)系“ 學(xué)”的屬性。

作圖步驟：

1. 確定所有的實(shí)體集合。
2. 選擇實(shí)體集應(yīng)包含的屬性。
3. 確定實(shí)體集之間的聯(lián)系。
4. 確定實(shí)體集的關(guān)鍵字，用下劃線在屬性上表明關(guān)鍵字的屬性組合。
5. 確定聯(lián)系的類型，在用線將表示聯(lián)系的菱形框聯(lián)系到實(shí)體集時(shí)，在線旁注明聯(lián)系的類型。

8、數(shù)據(jù)庫的七種鎖

1. 行鎖（Record Locks） ：行鎖一定是作用在索引上的。
2. 間隙鎖（Gap Locks）：鎖在本質(zhì)上是不區(qū)分共享間隙鎖或互斥間隙鎖的，而且間隙鎖是不互斥的，即兩個(gè)事務(wù)可以同時(shí)持有包含共同間隙的間隙鎖。這里的共同間隙包括兩種場景：其一是兩個(gè)間隙鎖的間隙區(qū)間完全一 樣；其二是一個(gè)間隙鎖包含的間隙區(qū)間是另一個(gè)間隙鎖包含間隙區(qū)間的 子集 。間隙鎖本質(zhì)上是用于 阻止其他事務(wù)在該間隙內(nèi)插入新記錄 ，而 自身事務(wù)是允許在該間隙內(nèi)插入數(shù)據(jù)的 。也就是說間隙鎖的應(yīng)用場景包括并發(fā) 讀取 、并發(fā) 更新 、并發(fā)刪除和并發(fā) 插入 。在RU和RC兩種隔離級(jí)別下，即使你使用select ... in share mode或select ... for update，也無法防止 幻讀 （讀后寫的場景）。因?yàn)檫@兩種隔離級(jí)別下只會(huì)有 行鎖 ，而不會(huì)有 間隙鎖 。這也是為什么示例中要規(guī)定隔離級(jí)別為RR的原因。
3. 臨鍵鎖（Next-key Locks）：臨鍵鎖是行鎖+間隙鎖，即臨鍵鎖是是一個(gè)左開右閉的區(qū)間，比如（3，5]。InnoDB的默認(rèn)事務(wù)隔離級(jí)別是RR，在這種級(jí)別下，如果你使用select ... in share mode或者select ... for update語句，那么InnoDB會(huì)使用臨鍵鎖，因而可以防止 幻讀 ；但即使你的隔離級(jí)別是RR，如果你這是使用普通的select語句，那么InnoDB將是快照讀，不會(huì)使用任何鎖，因而還是無法防止 幻讀 。
4. 共享鎖/排他鎖（Shared and Exclusive Locks） ：共享鎖/排他鎖都只是 行鎖 ，與間隙鎖無關(guān)，這一點(diǎn)很重要，后面還會(huì)強(qiáng)調(diào)這一點(diǎn)。其中共享鎖是一個(gè)事務(wù)并發(fā)讀取某一行記錄所需要持有的鎖，比如select ... in share mode；排他鎖是一 個(gè)事務(wù)并發(fā)更新或刪除某一行記錄所需要持有的鎖，比如select ... for update。不過這里需要重點(diǎn)說明的是，盡管共享鎖/排他鎖是行鎖，與間隙鎖無關(guān)，但一個(gè)事務(wù)在請(qǐng)求共享鎖/排他鎖時(shí)，獲取到的結(jié)果卻可能是 行鎖 ，也可能是 間隙鎖 ，也可能是 臨鍵鎖 ，這取決于數(shù)據(jù)庫的隔離級(jí)別以及查詢的 數(shù)據(jù)是否存在 。關(guān)于這一點(diǎn)，后面分析場景一和場景二的時(shí)候還會(huì)提到。
5. 意向共享鎖/意向排他鎖（Intention Shared and Exclusive Locks） ：意向共享鎖/意向排他鎖屬于 表鎖 ，且取得意向共享鎖/意向排他鎖是取得共享鎖/排他鎖的 前置條件 。
6. 插入意向鎖（Insert Intention Locks） ：插入意向鎖是一種特殊的間隙鎖，但不同于間隙鎖的是，該鎖只用于并發(fā)插入操作。如果說間隙鎖鎖住的是一個(gè)區(qū)間，那么插入意向鎖鎖住的就是一個(gè)點(diǎn)。因而從這個(gè)角度來說，插入意向鎖確實(shí)是一種特殊的間隙鎖。與間隙鎖的另一個(gè)非常重要的差別是：盡管插入意向鎖也屬于 間隙鎖 ，但兩個(gè)事務(wù)卻不能在同一時(shí)間內(nèi)一個(gè)擁有間隙鎖，另一個(gè)擁有該間隙區(qū)間內(nèi)的插入意向鎖（當(dāng)然，插入意向鎖如果不在間隙鎖區(qū)間內(nèi)則是可以的）。這里我們再回顧一下共享鎖和排他鎖：共享鎖用于讀取操作，而排他鎖是用于更新或刪除操作。也就是說插入意向鎖、共享鎖和排他鎖涵蓋了常用的增刪改查四個(gè)動(dòng)作。
7. 自增鎖（Auto-inc Locks） ：增鎖是一種特殊的表級(jí)鎖，主要用于事務(wù)中插入自增字段，也就是我們最常用的自增主鍵id。通過innodb_autoinc_lock_mode參數(shù)可以設(shè)置自增主鍵的生成策略。為了便于介紹innodb_autoinc_lock_mode參數(shù)，我們先將需要用到自增鎖的Insert語句進(jìn)行分類：

9、數(shù)據(jù)庫高并發(fā)的解決方案

1. 在web服務(wù)框架中加入緩存。在服務(wù)器與數(shù)據(jù)庫層之間加入緩存層，將高頻訪問的數(shù)據(jù)存入緩存中，減少數(shù)據(jù)庫的讀取負(fù)擔(dān)。
2. 增加數(shù)據(jù)庫索引。提高查詢速度。（不過索引太多會(huì)導(dǎo)致速度變慢，并且數(shù)據(jù)庫的寫入會(huì)導(dǎo)致索引的更新，也會(huì)導(dǎo)致速度變慢）
3. 主從讀寫分離，讓主服務(wù)器負(fù)責(zé)寫，從服務(wù)器負(fù)責(zé)讀。
4. 將數(shù)據(jù)庫進(jìn)行拆分，使得數(shù)據(jù)庫的表盡可能小，提高查詢的速度。
5. 使用分布式架構(gòu)，分散計(jì)算壓力。

10、SQL語句的內(nèi)部致性過程

• 連接器：客戶端先通過連接器連接到 MySQL 服務(wù)器。
• 緩存：連接器權(quán)限驗(yàn)證通過之后，先查詢是否有查詢緩存，如果有緩存（之前執(zhí)行過此語句）則直接返回緩存數(shù)據(jù)，如果沒有緩存則進(jìn)入分析器。
• 分析器：分析器會(huì)對(duì)查詢語句進(jìn)行語法分析和詞法分析，判斷 SQL 語法是否正確，如果查詢語法錯(cuò)誤會(huì)直接返回給客戶端錯(cuò)誤信息，如果語法正確則進(jìn)入優(yōu)化器。
• 優(yōu)化器：優(yōu)化器是對(duì)查詢語句進(jìn)行優(yōu)化處理，例如一個(gè)表里面有多個(gè)索引，優(yōu)化器會(huì)判別哪個(gè)索引性能更好。
• 執(zhí)行器：優(yōu)化器執(zhí)行完就進(jìn)入執(zhí)行器，執(zhí)行器就開始執(zhí)行語句進(jìn)行查詢比對(duì)了，直到查詢到滿足條件的所有數(shù)據(jù)，然后進(jìn)行返回。

11、為什么要分庫分表

數(shù)據(jù)庫中的數(shù)據(jù)量不一定是可控的，在未進(jìn)行分庫分表的情況下，隨著時(shí)間和業(yè)務(wù)的發(fā)展，庫中的表會(huì)越來越多，表中的數(shù)據(jù)量也會(huì)越來越大，相應(yīng)地，數(shù)據(jù)操作，增刪改查的開銷也會(huì)越來越大；另外，由于無法進(jìn)行分布式式部署，而一臺(tái)服務(wù)器的資源（CPU、磁盤、內(nèi)存、IO等）是有限的，最終數(shù)據(jù)庫所能承載的數(shù)據(jù)量、數(shù)據(jù)處理能力都將遭遇瓶頸。

12、分布式事務(wù)

分布式事務(wù)用于在分布式系統(tǒng)中保證不同節(jié)點(diǎn)之間的數(shù)據(jù)一致性。分布式事務(wù)的實(shí)現(xiàn)有很多種，最具有代表性的是由Oracle Tuxedo系統(tǒng)提出的XA分布式事務(wù)協(xié)議。

XA協(xié)議包含兩階段提交（2PC）和三階段提交（3PC）兩種 ：

1. 2PC: 在XA分布式事務(wù)的第一階段，作為事務(wù)協(xié)調(diào)者的節(jié)點(diǎn)會(huì)首先向所有的參與者節(jié)點(diǎn)發(fā)送Prepare請(qǐng)求。在接到Prepare請(qǐng)求之后，每一個(gè)參與者節(jié)點(diǎn)會(huì)各自執(zhí)行與事務(wù)有關(guān)的數(shù)據(jù)更新，寫入U(xiǎn)ndo Log和Redo Log。如果參與者執(zhí)行成功，暫時(shí)不提交事務(wù)，而是向事務(wù)協(xié)調(diào)節(jié)點(diǎn)返回“完成”消息。

   當(dāng)事務(wù)協(xié)調(diào)者接到了所有參與者的返回消息，整個(gè)分布式事務(wù)將會(huì)進(jìn)入第二階段。
   在XA分布式事務(wù)的第二階段，如果事務(wù)協(xié)調(diào)節(jié)點(diǎn)在之前所收到都是正向返回，那么它將會(huì)向所有事務(wù)參與者發(fā)出Commit請(qǐng)求。
   接到Commit請(qǐng)求之后，事務(wù)參與者節(jié)點(diǎn)會(huì)各自進(jìn)行本地的事務(wù)提交，并釋放鎖資源。當(dāng)本地事務(wù)完成。提交后，將會(huì)向事務(wù)協(xié)調(diào)者返回“完成”消息。

當(dāng)事務(wù)協(xié)調(diào)者接收到所有事務(wù)參與者的“完成”反饋，整個(gè)分布式事務(wù)完成。

注意:

在XA的第一階段，如果某個(gè)事務(wù)參與者反饋失敗消息，說明該節(jié)點(diǎn)的本地事務(wù)執(zhí)行不成功，必須回滾。于是在第二階段，事務(wù)協(xié)調(diào)節(jié)點(diǎn)向所有的事務(wù)參與者發(fā)送Abort請(qǐng)求。接收到Abort請(qǐng)求之后，各個(gè)事務(wù)參與者節(jié)點(diǎn)需要在本地進(jìn)行事務(wù)的回滾操作，回滾操作依照Undo Log來進(jìn)行。

XA兩階段提交的不足

1. 性能問題

XA協(xié)議遵循強(qiáng)一致性。在事務(wù)執(zhí)行過程中，各個(gè)節(jié)點(diǎn)占用著數(shù)據(jù)庫資源，只有當(dāng)所有節(jié)點(diǎn)準(zhǔn)備完畢，事務(wù)協(xié)調(diào)者才會(huì)通知提交，參與者提交后釋放資源。這樣的過程有著非常明顯的性能問題。

2. 協(xié)調(diào)者單點(diǎn)故障問題

事務(wù)協(xié)調(diào)者是整個(gè)XA模型的核心，一旦事務(wù)協(xié)調(diào)者節(jié)點(diǎn)掛掉，參與者收不到提交或是回滾通知，參與者會(huì)一直處于中間狀態(tài)無法完成事務(wù)。

3. 丟失消息導(dǎo)致的不一致問題。

在XA協(xié)議的第二個(gè)階段，如果發(fā)生局部網(wǎng)絡(luò)問題，一部分事務(wù)參與者收到了提交消息，另一部分事務(wù)參與者沒收到提交消息，那么就導(dǎo)致了節(jié)點(diǎn)之間數(shù)據(jù)的不一致。

為了解決上訴問題，提供下面幾重分布式事務(wù)解決方案

1. XA三階段提交

XA三階段提交在兩階段提交的基礎(chǔ)上增加了CanCommit階段，并且引入了超時(shí)機(jī)制。一旦事物參與者遲遲沒有接到協(xié)調(diào)者的commit請(qǐng)求，會(huì)自動(dòng)進(jìn)行本地commit。這樣有效解決了協(xié)調(diào)者單點(diǎn)故障的問題。但是性能問題和不一致的問題仍然沒有根本解決。

2. MQ事務(wù)

利用消息中間件來異步完成事務(wù)的后一半更新，實(shí)現(xiàn)系統(tǒng)的最終一致性。這個(gè)方式避免了像XA協(xié)議那樣的性能問題。

3. TCC事務(wù)

TCC事務(wù)是Try、Commit、Cancel三種指令的縮寫，其邏輯模式類似于XA兩階段提交，但是實(shí)現(xiàn)方式是在代碼層面來人為實(shí)現(xiàn)。

既然能看到文末，我相信你一定是個(gè)非常有潛力的選手，你的耐心和你的專注度已經(jīng)超過了同齡人，點(diǎn)贊收藏，需要用到的時(shí)候翻出來再好好復(fù)習(xí)復(fù)習(xí)。

如果這樣看起來不爽的話，你也可以后臺(tái)私信我，發(fā)你PDF版的，下載下來慢慢看哈哈。