<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                [toc] # 六、數據庫 ## 1、數據庫基礎 ### 1.1 數據庫三范式 范式是具有最小冗余的表結構。3 范式具體如下 #### 1.1.1 第一范式(列都是不可再分) 第一范式的目標是確保每列的原子性:如果每列都是不可再分的最小數據單元(也稱為最小的原子單元),則滿足第一范式(1NF) <img src="https://i.loli.net/2021/01/04/AlVPIu4FEiTYkDp.png" alt="image-20210104142755373" style="zoom:60%;" /> #### 1.1.2 第二范式(每個表只描述一件事) 首先滿足第一范式,并且表中非主鍵列不存在對主鍵的部分依賴。 **第二范式要求每個表只描述一件事情**。 <img src="https://i.loli.net/2021/01/04/ef13FwnNPvyrLsp.png" alt="image-20210104142910198" style="zoom: 67%;" /> #### 1.1.3 第三范式(不存在對非主鍵列的傳遞依賴) 第三范式定義是,滿足第二范式,并且表中的列不存在對非主鍵列的傳遞依賴。除了主鍵訂單編號外,顧客姓名依賴于非主鍵顧客編號。 <img src="https://i.loli.net/2021/01/04/abLRQxXyfgrnzUO.png" alt="image-20210104143039648" style="zoom:50%;" /> ### 1.2 數據庫事務 事務(TRANSACTION)是作為單個邏輯工作單元執行的一系列操作, 這些操作作為一個整體一起向系統提交,要么都執行、要么都不執行 。 #### 1.2.1 數據庫事務特性 (ACID) ##### 1.2.1.1 原子性(Atomicity) 原子性是指事務包含的所有操作要么全部成功,要么全部失敗回滾。 ##### 1.2.1.2 一致性(Consistency) 一致性是指事務必須使數據庫從一個一致性狀態變換到另一個一致性狀態,也就是說一個事務執行之前和執行之后都必須處于一致性狀態。 ``` 拿轉賬來說,假設用戶A和用戶B兩者的錢加起來一共是5000,那么不管A和B之間如何轉賬,轉幾次賬,事務結束后兩個用戶的錢相加起來應該還得是5000,這就是事務的一致性。 ``` ##### 1.2.1.3 隔離性(Isolation) 隔離性是當多個用戶并發訪問數據庫時,比如操作同一張表時,數據庫為每一個用戶開啟的事務,不能被其他事務的操作所干擾,多個并發事務之間要相互隔離。 ``` 即要達到這么一種效果:對于任意兩個并發的事務T1和T2,在事務T1看來,T2要么在T1開始之前就已經結束,要么在T1結束之后才開始,這樣每個事務都感覺不到有其他事務在并發地執行。 ``` ##### 1.2.1.4 持久性(Durability) 持久性是指一個事務一旦被提交了,那么對數據庫中的數據的改變就是永久性的,即便是在數據庫系統遇到故障的情況下也不會丟失提交事務的操作。 #### 1.2.2 臟讀、不可重復讀、幻讀 ##### 臟讀(Dirty Read) A事務執行過程中,B事務讀取了A事務的修改。但是由于某些原因,A事務可能沒有完成提交,發生RollBack了操作,則B事務所讀取的數據就會是不正確的。這個未提交數據就是臟讀(Dirty Read)。臟讀產生的流程如下: <img src="https://i.loli.net/2021/01/04/5rsdMkSzKWj4cp3.jpg" alt="image-20200111153612588" style="zoom: 50%;" /> ##### 不可重復讀(Nonrepeatable Read) B事務讀取了兩次數據,在這兩次的讀取過程中A事務修改了數據,B事務的這兩次讀取出來的 數據不一樣 。B事務這種讀取的結果,即為不可重復讀(Nonrepeatable Read)。不可重復讀的產生的流程如下: <img src="https://i.loli.net/2020/04/20/5YbVK8NgF1lnBxP.jpg" alt="5YbVK8NgF1lnBxP" style="zoom:99%;" /> 不可重復讀有一種特殊情況,兩個事務更新同一條數據資源,后完成的事務會造成先完成的事務更新丟失。這種情況就是大名鼎鼎的**第二類丟失更新**。主流的數據庫已經默認屏蔽了第一類丟失更新問題(即:后做的事務撤銷,發生回滾造成已完成事務的更新丟失),但我們編程的時候仍需要特別注意第二類丟失更新。它產生的流程如下: > ##### 第一類丟失更新(Lost Update) > > 在完全未隔離事務的情況下,兩個事務更新同一條數據資源,某一事務完成,另一事務異常終止,回滾造成第一個完成的更新也同時丟失 。這個問題現代關系型數據庫已經不會發生。 **第二類丟失更新** <img src="https://i.loli.net/2021/01/04/RIqT7WYvbsxFpj3.jpg" alt="image-20200111153920684" style="zoom:50%;" /> 可以明顯看出事務A的更新被事務B所覆蓋,更新丟失。 ##### 幻讀(Phantom Read) B事務讀取了兩次數據,在這兩次的讀取過程中A事務添加了數據,B事務的這兩次讀取出來的集合不一樣。幻讀產生的流程如下: <img src="https://i.loli.net/2021/01/04/hqxg3Oi9WeoLVcy.jpg" alt="image-20200111154008090" style="zoom: 50%;" /> #### 1.2.3 實際情況演示 在下面我會使用 2 個命令行mysql ,模擬多線程(多事務)對同一份數據的臟讀問題。 MySQL 命令行的默認配置中事務都是自動提交的,即執行SQL語句后就會馬上執行 COMMIT 操作。如果要顯式地開啟一個事務需要使用命令:`START TARNSACTION`。 我們可以通過下面的命令來設置隔離級別。 ```sql SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE] ``` 我們再來看一下我們在下面實際操作中使用到的一些并發控制語句: - `START TARNSACTION` |`BEGIN`:顯式地開啟一個事務。 - `COMMIT`:提交事務,使得對數據庫做的所有修改成為永久性。 - `ROLLBACK`:回滾會結束用戶的事務,并撤銷正在進行的所有未提交的修改。 ##### 臟讀(讀未提交) ![img](https://my-blog-to-use.oss-cn-beijing.aliyuncs.com/2019-31-1臟讀(讀未提交)實例.jpg) ##### 避免臟讀(讀已提交) ![img](https://my-blog-to-use.oss-cn-beijing.aliyuncs.com/2019-31-2讀已提交實例.jpg) ##### 不可重復讀 還是剛才上面的讀已提交的圖,雖然避免了讀未提交,但是卻出現了,一個事務還沒有結束,就發生了 不可重復讀問題。 ![img](https://my-blog-to-use.oss-cn-beijing.aliyuncs.com/2019-32-1不可重復讀實例.jpg) ##### 可重復讀 ![img](https://my-blog-to-use.oss-cn-beijing.aliyuncs.com/2019-33-2可重復讀.jpg) ##### 防止幻讀(可重復讀) ![img](https://my-blog-to-use.oss-cn-beijing.aliyuncs.com/2019-33防止幻讀(使用可重復讀).jpg) 一個事務對數據庫進行操作,這種操作的范圍是數據庫的全部行,然后第二個事務也在對這個數據庫操作,這種操作可以是插入一行記錄或刪除一行記錄,那么第一個是事務就會覺得自己出現了幻覺,怎么還有沒有處理的記錄呢? 或者 怎么多處理了一行記錄呢? 幻讀和不可重復讀有些相似之處 ,但是不可重復讀的重點是修改,幻讀的重點在于新增或者刪除。 ### 1.3 數據庫隔離級別 為了解決上面提及的并發問題,主流關系型數據庫都會提供四種事務隔離級別。 #### 1.3.1 讀未提交(Read Uncommitted) >讀未提交,顧名思義,就是一個事務可以讀取另一個未提交事務的數據。 ``` 事例:老板要給程序員發工資,程序員的工資是3.6萬/月。但是發工資時老板不小心按錯了數字,按成3.9萬/月,該錢已經打到程序員的戶口,但是事務還沒有提交,就在這時,程序員去查看自己這個月的工資,發現比往常多了3千元,以為漲工資了非常高興。但是老板及時發現了不對,馬上回滾差點就提交了的事務,將數字改成3.6萬再提交。 ``` ``` 分析:實際程序員這個月的工資還是3.6萬,但是程序員看到的是3.9萬。他看到的是老板還沒提交事務時的數據。這就是臟讀。 ``` **那怎么解決臟讀呢?Read committed!讀提交,能解決臟讀問題。** #### 1.3.2 讀已提交(Read Committed)[處理臟讀] 這是大多數數據庫系統的默認隔離級別(但不是MySQL默認的)。 > 讀提交,顧名思義,就是一個事務要等另一個事務提交后才能讀取數據。 ``` 事例:程序員拿著信用卡去享受生活(卡里當然是只有3.6萬),當他埋單時(程序員事務開啟),收費系統事先檢測到他的卡里有3.6萬,就在這個時候!!程序員的妻子要把錢全部轉出充當家用,并提交。當收費系統準備扣款時,再檢測卡里的金額,發現已經沒錢了(第二次檢測金額當然要等待妻子轉出金額事務提交完)。程序員就會很郁悶,明明卡里是有錢的… ``` ``` 分析:這就是讀提交,若有事務對數據進行更新(UPDATE)操作時,讀操作事務要等待這個更新操作事務提交后才能讀取數據,可以解決臟讀問題。但在這個事例中,出現了一個事務范圍內兩個相同的查詢卻返回了不同數據,這就是不可重復讀。 ``` #### 1.3.3 可重復讀(Repeatable Read)[處理臟讀、不可重復讀、] 這是MySQL的默認事務隔離級別,它確保同一事務的多個實例在并發讀取數據時,會看到同樣的數據行。這種隔離級別可以防止除幻讀外的其他問題。 > 重復讀,就是在開始讀取數據(事務開啟)時,不再允許修改操作 ``` 事例:程序員拿著信用卡去享受生活(卡里當然是只有3.6萬),當他埋單時(事務開啟,不允許其他事務的UPDATE修改操作),收費系統事先檢測到他的卡里有3.6萬。這個時候他的妻子不能轉出金額了。接下來收費系統就可以扣款了。 ``` ``` 分析:重復讀可以解決不可重復讀問題。寫到這里,應該明白的一點就是,不可重復讀對應的是修改,即UPDATE操作。但是可能還會有幻讀問題。因為幻讀問題對應的是插入INSERT操作,而不是UPDATE操作。 ``` #### 1.3.4 可串行化(Serializable)[處理臟讀、不可重復讀、幻讀] > Serializable 是最高的事務隔離級別,在該級別下,事務串行化順序執行,可以避免臟讀、不可重復讀與幻讀。但是這種事務隔離級別效率低下,比較耗數據庫性能,一般不使用。 這是最高的隔離級別,它通過強制事務排序,使之不可能相互沖突,從而解決幻讀、第二類更新丟失問題。在這個級別,可以解決上面提到的所有并發問題,但可能導致大量的超時現象和鎖競爭,通常數據庫不會用這個隔離級別,我們需要其他的機制來解決這些問題:樂觀鎖和悲觀鎖。 ``` 事例:程序員某一天去消費,花了2千元,然后他的妻子去查看他今天的消費記錄(全表掃描FTS,妻子事務開啟),看到確實是花了2千元,就在這個時候,程序員花了1萬買了一部電腦,即新增INSERT了一條消費記錄,并提交。當妻子打印程序員的消費記錄清單時(妻子事務提交),發現花了1.2萬元,似乎出現了幻覺,這就是幻讀。 ``` ![gjles84WTq1D9By](https://i.loli.net/2020/04/20/gjles84WTq1D9By.jpg) ### 1.4 數據庫鎖 #### 1.4.1 封鎖 封鎖是實現并發控制的一個非常重要的技術。所謂封鎖就是事務T在對某個數據對象例如表、記錄等操作之前,先向系統發出請求,對其加鎖。加鎖后事務T就對該數據對象有了一定的控制,在事務T釋放它的鎖之前,其他事務不能更新此數據對象。例如,事務T1要修改A,若在讀出A之前先鎖住A,其他事務就不能再讀取和修改A了,直到T1修改并寫回A解除了對A的封鎖為止。這樣,就不會丟失T1的修改。 確切的控制由封鎖的類型決定。基本的封鎖類型有兩種:**排他鎖**( exclusive locks,簡稱X鎖)和**共享鎖**( share locks,簡稱S鎖) ##### 1.4.1.1 X鎖(排他寫鎖) **X鎖(排他寫鎖)**:若事務T1對數據對象A加上X鎖,則只允許T讀取和修改A,其他任何事物都不能再對A加任何類型的鎖,直到T釋放A上的鎖為止。這就保證了其他事務在T釋放A上的鎖之前不能再讀取和修改A ##### 1.4.1.2 s鎖(共享讀鎖) **s鎖(共享讀鎖)**:阻塞寫鎖若事務T對數據A加上S鎖,則事務T可以讀A但是不能修改A,其他事務只能對A加S鎖而不能加X鎖直到T釋放A上的S鎖為止。這就保證了其他食物可以讀A,但在T釋放A上的S鎖之前不能對A進行任何修改。 #### 1.4.2 封鎖協議(解決臟讀不可重復讀) ##### 1.4.2.1 一級封鎖協議 **一級封鎖協議**:事務T在對數據對象A進行修改之前,必須對其加X鎖,直至事務結束才釋放。事務結束包括正常結束( COMMIT)和非正常結束( ROLLBACK)在一級加鎖協議中,如果僅僅是對數據進行讀操作而不進行修改, 是不需要進行加鎖的。所以**只能避免修改丟失而不能避免不可重復讀和臟讀** ##### 1.4.2.2 二級封鎖協議 **二級封鎖協議**:在一級加鎖協議的基礎上增加事務T在讀取數據R之前必須先對其加S鎖,讀完后即可釋放S鎖。二級加鎖協議除防止了丟失修改,還可進一步防止讀臟數據。例如事務T1正在對數據對象R進行修改,此前已經對R加上了X鎖,此時事務T2想讀取R,就必須對R加上S鎖,但是T2發現R已經被T1加上了X鎖,于是T2只能等待T1釋放了在R上加的鎖之后才能對R加S鎖并讀取。這能防止T2讀取到T1未提交的數據,從而**避免了臟讀**。 但是在二級封鎖協議中,由于讀完數據后即可釋放S鎖,所以它**不能保證可重復讀**。 ##### 1.4.2.3 三級封鎖協議 **三級封鎖協議**:三級封鎖協議是指,在一級封鎖協議的基礎上增加事務T在讀取數據R之前對其加S鎖直至事務結束才釋放三級封鎖協議除了防止丟失修改和讀“臟”數據之外,還進一步**防止了不可重復讀** #### 1.4.3 死鎖活鎖 - **活鎖**:如果事務T1封鎖了數據R,事務T2又請求封鎖R,于是T2等待。T3也請求封鎖R,當T1釋放了R上的鎖之后系統首先批準了T3的請求,T2繼續等待;然后T4又請求封鎖R,T3在釋放R上的鎖之后系統又批準了T4的請求,T2有可能永遠等待,這就是活鎖的情形 **避免活鎖的簡單方法就是采用先來先服務的策略。**當多個事務請求封鎖同一數據對象時,封鎖子系統按請求鎖的先后次序對事務進行排隊,數據對象上的鎖一旦釋放就批準批準申請隊列中第一個事務獲得鎖 - **死鎖**:事務T1封鎖了數據R1,事務T2封鎖了數據R2;同時,事務T1請求封鎖R2,因為T2已經封鎖了R2,所以T1只能等待。T2也請求封鎖R1,由于R1被T1封鎖了,R2也只能等待。由于它們互相等待,T1和T2兩個事務永遠也不能結束,于是就形成了死鎖。 --------------- **解決死鎖的方法** ------------------- - **死鎖的預防** 在數據庫中,產生死鎖的原因是兩個或多個事務都已經封鎖了一些數據對象,然后又都請求對已被事務封鎖的對象加鎖,從而出現死鎖。防止死瑣的發生其實就是要破壞產生死鎖的條件。預防死鎖發生通常有以下兩種方法。 **一次封鎖法**:一次封鎖法要求每個事務必須一次將所有要使用的數據全部加鎖,否則就不能繼續執行下去。一次封鎖法雖然可以有效防止死鎖的發生,但是增加了鎖的粒度,從而降低了系統的并發性。并且數據庫是不斷變化的,所以事先很難精確地確定每個事務所需進行加鎖的對象,為此只能擴大封鎖范圍,將事務在執行過程中可能需要封鎖的數據對象全部加鎖,這就進一步降低了并發度 **順序封鎖法**:順序封鎖法是預先對數據對象規定一個封鎖順序,所有事務都按這個順序實施封鎖。例如在B樹結構的索引中,可規定封鎖的順序必須是從根節點開始,然后是下一級的子節點,逐級封鎖。順序封鎖法可以有效地避免死鎖,但是要實現順序封鎖法十分的困難,因為很難事先確定每一個事務要封鎖哪些對象,因此也就很難按規定的順序去實施加鎖。 由此可見數據庫中不適合預防死鎖,只適合進行死鎖的診斷與解除 - **死鎖的診斷與解除** 數據庫系統中診斷死鎖的方法與操作系統類似,一般使用**超時法**或**事務等待圖法** **超時法**:如果一個事務的等待時間超過了規定的時限,那么就認為其發生了死鎖。超時法實現簡單,但其不足也十分明顯,一是有可能誤判了死鎖,如事務因為其他原因而使等待時間超過時限,系統就會誤認為發生了死鎖;二是若時限設置得太長,則不能及時發現死鎖。 **事務等待圖法**:事務等待圖是一個有向圖G=(T,U),T為結點的集合,每個結點表示正在運行的事務:U為邊的集合,每條邊表示事務等待的情況。若T1等待T2,則在T1,T2之間畫一條有向邊,從T1指向T2。事務等待圖動態地反應了所有事務的等待情況。并發控制子系統周期性(比如每隔數秒)生成事務等待圖,并進行檢測。如果發現圖中存在回路,則表示系統中出現了死鎖。 數據庫管理系統的并發控制系統一旦檢測到系統中存在死鎖,就要設法解除。通常采用的方法是選擇一個處理死鎖代價最小的事務,將其撤銷,釋放此事務持有的所有的鎖,使其他事務得以繼續運行下去。當然,對撤銷的事務所進行的數據修改必須加以恢復。 #### 1.4.4 兩段鎖協議 申請不釋放釋放不申請 - 在對任何數據進行讀、寫操作之前,首先要申請并獲得對該數據的封鎖; - 在釋放一個鎖之后,事務不再申請和獲得任何其他封鎖 所謂兩段鎖的含義是,事務分為兩個階段:第一個階段是獲得封鎖,也稱為拓展階段,在這個階段,事務可以申請獲得任何數據項上的任何類型的鎖,但是不能釋放鎖;第二個階段是釋放封鎖,也稱為收縮階段,在這個階段,事務可以釋放任何數據項上的任何類型的鎖,但是不能再申請任何鎖 #### 1.4.5 樂觀鎖 樂觀鎖認為一個用戶讀數據的時候,別人不會去寫自己所讀的數據;悲觀鎖就剛好相反,覺得自己讀數據庫的時候,別人可能剛好在寫自己剛讀的數據,其實就是持一種比較保守的態度;時間戳就是不加鎖,通過時間戳來控制并發出現的問題。 #### 1.4.6 悲觀鎖 悲觀鎖就是在讀取數據的時候,為了不讓別人修改自己讀取的數據,就會先對自己讀取的數據加鎖,只有自己把數據讀完了,才允許別人修改那部分數據,或者反過來說,就是自己修改某條數據的時候,不允許別人讀取該數據,只有等自己的整個事務提交了,才釋放自己加上的鎖,才允許其他用戶訪問那部分數據。 **時間戳** 時間戳就是在數據庫表中單獨加一列時間戳,比如“TimeStamp”, 每次讀出來的時候,把該字段也讀出來,當寫回去的時候,把該字段加 1,提交之前 ,跟數據庫的該字段比較一次,如果比數據庫的值大的話,就允許保存,否則不允許保存,這種處理方法雖然不使用數據庫系統提供的鎖機制,但是這種方法可以大大提高數據庫處理的并發量,以上悲觀鎖所說的加“鎖”,其實分為幾種鎖,分別是: 排它鎖(寫鎖)和共享鎖(讀鎖) 。 #### 1.4.7 行級鎖 行級鎖是一種排他鎖,防止其他事務修改此行;在使用以下語句時, Oracle 會自動應用行級鎖: 1. INSERT、 UPDATE、 DELETE、 SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT]; 2. SELECT … FOR UPDATE 語句允許用戶一次鎖定多條記錄進行更新 3. 使用 COMMIT 或 ROLLBACK 語句釋放鎖。 ##### InnoDB的行鎖模式及加鎖方法 InnoDB實現了以下兩種類型的行鎖。 - 共享鎖(S):允許一個事務去讀一行,阻止其他事務獲得相同數據集的排他鎖。 - 排他鎖(X):允許獲得排他鎖的事務更新數據,阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。 另外,為了允許行鎖和表鎖共存,實現多粒度鎖機制,InnoDB還有兩種內部使用的意向鎖(Intention Locks),這兩種意向鎖都是表鎖。 - 意向共享鎖(IS):事務打算給數據行加行共享鎖,事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。 - 意向排他鎖(IX):事務打算給數據行加行排他鎖,事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。 上述鎖模式的兼容情況具體如表20-6所示。 ![image-20210104160452650](https://i.loli.net/2021/01/04/FSYVL6TgNzrvP1Q.png) **如果一個事務請求的鎖模式與當前的鎖兼容,InnoDB就將請求的鎖授予該事務;反之,如果兩者不兼容,該事務就要等待鎖釋放。**意向鎖是InnoDB自動加的,不需用戶干預。對于UPDATE、DELETE和INSERT語句,InnoDB會自動給涉及數據集加排他鎖(X);對于普通SELECT語句,InnoDB不會加任何鎖;事務可以通過以下語句顯示給記錄集加共享鎖或排他鎖。 ? 共享鎖(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 ? 排他鎖(X):SELECT * FROM table_name WHERE ... FOR UPDATE。 用SELECT ... IN SHARE MODE獲得共享鎖,主要用在需要數據依存關系時來確認某行記錄是否存在,并確保沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果當前事務也需要對該記錄進行更新操作,則很有可能造成死鎖,對于鎖定行記錄后需要進行更新操作的應用,應該使用SELECT... FOR UPDATE方式獲得排他鎖。 <img src="https://i.loli.net/2021/01/04/rD5A9EK2CJRhdYq.png" alt="image-20210104161236719" style="zoom: 80%;" /> ##### InnoDB行鎖實現方式 <font color="orange">***InnoDB行鎖是通過給索引上的索引項加鎖來實現的***</font>,這一點MySQL與Oracle不同,后者是<font color="orange"> ***通過在數據塊中對相應數據行加鎖來實現的。InnoDB這種行鎖實現特點意味著:只有通過索引條件檢索數據,InnoDB才使用行級鎖,否則,InnoDB將使用表鎖!***</font> **在實際應用中,要特別注意InnoDB行鎖的這一特性,不然的話,可能導致大量的鎖沖突,從而影響并發性能。**下面通過一些實際例子來加以說明。 <font color="darkred">**(1)在不通過索引條件查詢的時候,InnoDB確實使用的是表鎖,而不是行鎖。**</font> 在如表20-9所示的例子中,開始tab_no_index表沒有索引: ```sql mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb; Query OK, 0 rows affected (0.15 sec) mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 ``` ![image-20210104161601461](https://i.loli.net/2021/01/04/1ZbuBF5cx4ztIes.png) 在如表20 -9所示的例子中,看起來session_1只給一行加了排他鎖,但session_2在請求其他行的排他鎖時,卻出現了鎖等待!原因就是在沒有索引的情況下,InnoDB只能使用表鎖。當我們給其增加一個索引后,InnoDB就只鎖定了符合條件的行,如表20-10所示。 創建tab_with_index表,id字段有普通索引: ```sql mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb; Query OK, 0 rows affected (0.15 sec) mysql> alter table tab_with_index add index id(id); Query OK, 4 rows affected (0.24 sec) Records: 4 Duplicates: 0 Warnings: 0 ``` ![image-20210104161809773](https://i.loli.net/2021/01/04/Kl7S4L8N1bI26jJ.png) <font color='darkred'>(2) ***由于MySQL的行鎖是針對索引加的鎖,不是針對記錄加的鎖,所以雖然是訪問不同行的記錄,但是如果是使用相同的索引鍵,是會出現鎖沖突的。***</font>應用設計的時候要注意這一點。 在如表20-11所示的例子中,表tab_with_index的id字段有索引,name字段沒有索引: ```sql mysql> alter table tab_with_index drop index name; Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into tab_with_index values(1,'4'); Query OK, 1 row affected (0.00 sec) mysql> select * from tab_with_index where id = 1; +------+------+ | id | name | +------+------+ | 1 | 1 | | 1 | 4 | +------+------+ 2 rows in set (0.00 sec) ``` ![image-20210104162157238](https://i.loli.net/2021/01/04/3BLMd4cUOnFVuq1.png) (3)當表有多個索引的時候,不同的事務可以使用不同的索引鎖定不同的行,另外,不論是使用主鍵索引、唯一索引或普通索引,InnoDB都會使用行鎖來對數據加鎖。 在如表20-12所示的例子中,表tab_with_index的id字段有主鍵索引,name字段有普通索引: ```sql mysql> alter table tab_with_index add index name(name); Query OK, 5 rows affected (0.23 sec) Records: 5 Duplicates: 0 Warnings: 0 ``` ![image-20210104162259651](https://i.loli.net/2021/01/04/Mi1OhntgcE2ya6b.png) (4)即便在條件中使用了索引字段,但是否使用索引來檢索數據是由MySQL通過判斷不同執行計劃的代價來決定的,如果MySQL認為全表掃描效率更高,比如對一些很小的表,它就不會使用索引,這種情況下InnoDB將使用表鎖,而不是行鎖。 ***因此,在分析鎖沖突時,別忘了檢查SQL的執行計劃,以確認是否真正使用了索引。***關于MySQL在什么情況下不使用索引的詳細討論,參見本章“索引問題”一節的介紹。 在下面的例子中,檢索值的數據類型與索引字段不同,雖然MySQL能夠進行數據類型轉換,但卻不會使用索引,從而導致InnoDB使用表鎖。通過用explain檢查兩條SQL的執行 **計劃,我們可以清楚地看到了這一點。** **例子中tab_with_index表的name字段有索引,但是name字段是varchar類型的,如果where條件中不是和varchar類型進行比較,則會對name進行類型轉換,而執行的全表掃描。** ```sql mysql> alter table tab_no_index add index name(name); Query OK, 4 rows affected (8.06 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> explain select * from tab_with_index where name = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tab_with_index type: ALL possible_keys: name key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from tab_with_index where name = '1' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tab_with_index type: ref possible_keys: name key: name key_len: 23 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) ``` #### 1.4.8 表級鎖 表示對當前操作的整張表加鎖,它實現簡單,資源消耗較少,被大部分 MySQL 引擎支持。最常使用的 MYISAM 與 INNODB 都支持表級鎖定。表級鎖定分為表共享讀鎖(共享鎖)與表獨占寫鎖(排他鎖)。 ##### 什么時候使用表鎖 對于InnoDB表,在絕大部分情況下都應該使用行級鎖,因為事務和行鎖往往是我們之所以選擇InnoDB表的理由。但在個別特殊事務中,也可以考慮使用表級鎖。 **? 第一種情況是:事務需要更新大部分或全部數據,表又比較大,如果使用默認的行鎖,不僅這個事務執行效率低,而且可能造成其他事務長時間鎖等待和鎖沖突,這種情況下可以考慮使用表鎖來提高該事務的執行速度。** **? 第二種情況是:事務涉及多個表,比較復雜,很可能引起死鎖,造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表,從而避免死鎖、減少數據庫因事務回滾帶來的開銷。** 當然,應用中這兩種事務不能太多,否則,就應該考慮使用MyISAM表了。 在InnoDB下,使用表鎖要注意以下兩點。 **(1)使用LOCK TABLES雖然可以給InnoDB加表級鎖,但必須說明的是,表鎖不是由InnoDB存儲引擎層管理的,而是由其上一層──MySQL Server負責的,僅當autocommit=0、innodb_table_locks=1(默認設置)時,InnoDB層才能知道MySQL加的表鎖,MySQL Server也才能感知InnoDB加的行鎖,這種情況下,InnoDB才能自動識別涉及表級鎖的死鎖;否則,InnoDB將無法自動檢測并處理這種死鎖。有關死鎖,下一小節還會繼續討論。** **(2)在用 LOCK TABLES對InnoDB表加鎖時要注意,要將AUTOCOMMIT設為0,否則MySQL不會給表加鎖;事務結束前,不要用UNLOCK TABLES釋放表鎖,因為UNLOCK TABLES會隱含地提交事務;COMMIT或ROLLBACK并不能釋放用LOCK TABLES加的表級鎖,必須用UNLOCK TABLES釋放表鎖。**正確的方式見如下語句: 例如,如果需要寫表t1并從表t讀,可以按如下做: ```sql SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES; ``` #### 1.4.9 頁級鎖 頁級鎖是 MySQL 中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快,但沖突多,行級沖突少,但速度慢。所以取了折衷的頁級,一次鎖定相鄰的一組記錄。 BDB 支持頁級鎖 #### 1.4.10 間隙鎖(Next-Key鎖) 當我們用**范圍條件**而不是相等條件檢索數據,并請求共享或排他鎖時,InnoDB會給符合條件的已有數據記錄的索引項加鎖;對于鍵值在條件范圍內但并不存在的記錄,叫做“間隙(GAP)”,InnoDB也會對這個“間隙”加鎖,這種鎖機制就是所謂的間隙鎖(Next-Key鎖)。 舉例來說,假如emp表中只有101條記錄,其empid的值分別是 1,2,...,100,101,下面的SQL: Select * from emp where empid > 100 for update; 是一個范圍條件的檢索,InnoDB不僅會對符合條件的empid值為101的記錄加鎖,也會對empid大于101(這些記錄并不存在)的“間隙”加鎖。 InnoDB使用間隙鎖的目的,**一方面是為了防止幻讀,以滿足相關隔離級別的要求**,對于上面的例子,要是不使用間隙鎖,如果其他事務插入了empid大于100的任何記錄,那么本事務如果再次執行上述語句,就會發生幻讀;**另外一方面,是為了滿足其恢復和復制的需要**。 很顯然,在使用范圍條件檢索并鎖定記錄時,InnoDB這種加鎖機制會阻塞符合條件范圍內鍵值的并發插入,這往往會造成嚴重的鎖等待。因此,在實際應用開發中,尤其是并發插入比較多的應用,我們要盡量優化業務邏輯,盡量使用相等條件來訪問更新數據,避免使用范圍條件。 還要特別說明的是,InnoDB除了通過范圍條件加鎖時使用間隙鎖外,如果使用相等條件請求給一個不存在的記錄加鎖,InnoDB也會使用間隙鎖! 在如表20-13所示的例子中,假如emp表中只有101條記錄,其empid的值分別是1,2,......,100,101。 ![image-20210104162706225](https://i.loli.net/2021/01/04/LpY7kTnWlez93yF.png) ## 2、索引 ### 2.1 什么是索引? 數據庫索引,是數據庫管理系統(DBMS)中一個排序的數據結構,以協助快速查詢、更新數據庫表中數據。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105111604792.png" alt="image-20210105111604792" style="zoom:67%;" /> ### 2.2 索引存儲模型推演 #### 2.2.1 二分查找 其實這個就是二分查找的一種思想,也叫**折半查找**,每一次,我們都把候選數據縮小了一半。如果**數據已經排過序的話**,這種方式效率比較高。所以第一個,我們可以考慮用**有序數組**作為索引的數據結構。`有序數組`的**等值查詢**和**比較查詢**效率非常高,但是更新數據的時候會出現一個問題,可能要挪動大量的數據(改變 index),所以只適合存儲靜態的數據。為了支持頻繁的修改,比如插入數據,我們需要采用鏈表。鏈表的話,如果是單鏈表,它的查找效率還是不夠高。 #### 2.2.2 二叉查找樹誕 **有沒有可以使用二分查找的鏈表呢?** 為了解決這個問題,BST(Binary Search Tree)也就是我們所說的二叉查找樹誕生了。 二叉查找樹的特點是什么?左子樹所有的節點都小于父節點,右子樹所有的節點都大于父節點。投影到平面以后,就是一個有序的線性表。 二叉查找樹既能夠實現快速查找,又能夠實現快速插入。但是二叉查找樹有一個問題:就是它的**查找耗時是和這棵樹的深度相關的**,在最壞的情況下時間復雜度會退化成O(n)。還是剛才的這一批數字,如果我們插入的數據剛好是有序的,2、6、11、13、17、22。個時候我們的二叉查找樹變成了什么樣了呢?它會變成鏈表(我們把這種樹叫做“斜樹”),這種情況下不能達到加快檢索速度的目的,和順序查找效率是沒有區別的。 <img src="https://i.loli.net/2021/01/05/7PojBMZYEQvRUNG.png" alt="image-20210105121015808" style="zoom:67%;" /> 造成它傾斜的原因是什么呢?因為左右子樹深度差太大,這棵樹的左子樹根本沒有節點——也就是它**不夠平衡**。 所以,我們有沒有左右子樹深度相差不是那么大,更加平衡的樹呢?這個就是**平衡二叉樹**,叫做 Balanced binary search trees,或者 **AVL 樹**(AVL 是發明這個數據結構的人的名字)。 #### 2.2.3 平衡二叉樹(AVL Tree) AVL Trees (Balanced binary search trees)**平衡二叉樹**的定義:左右子樹深度差絕對值**不能超過 1**。 是什么意思呢?比如左子樹的深度是 2,右子樹的深度只能是 1 或者 3。這個時候我們再按順序插入 1、2、3、4、5、6,一定是這樣,不會變成一棵“斜樹” <img src="https://i.loli.net/2021/01/05/agFi9Qd6GZ5TKMU.png" alt="image-20210105121436828" style="zoom:50%;" /> 那它的平衡是怎么做到的呢?怎么保證左右子樹的深度差不能超過 1 呢?插入 1、2、3。我們注意看:當我們插入了 1、2 之后,如果按照二叉查找樹的定義,3 肯定是要在2 的右邊的,這個時候根節點 1 的右節點深度會變成 2,但是左節點的深度是 0,因為它沒有子節點,所以就會違反平衡二叉樹的定義。 那應該怎么辦呢?因為它是右節點下面接一個右節點,**右-右型**,所以這個時候我們**要把 2 提上去**,這個操作叫做**左旋**。 <img src="https://i.loli.net/2021/01/05/Zd51UGreDmBhuFP.png" alt="image-20210105121622332" style="zoom:50%;" /> 同樣的,如果我們插入 7、6、5,這個時候會變成**左左型**,就會發生**右旋**操作,把 6提上去。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105121644748.png" alt="image-20210105121644748" style="zoom:50%;" /> 所以為了保持平衡,AVL 樹在插入和更新數據的時候執行了一系列的計算和調整的操作。平衡的問題我們解決了,那么平衡二叉樹作為索引怎么查詢數據?在平衡二叉樹中,一個節點,它的大小是一個固定的單位,作為索引應該存儲什么內容? 它應該存儲三塊的內容: - 第一個是**索引的鍵值**。比如我們在 id 上面創建了一個索引,我在用 where id =1 的條件查詢的時候就會找到索引里面的 id 的這個鍵值。 - 第二個是**數據的磁盤地址**,因為索引的作用就是去查找數據的存放的地址。 - 第三個,因為是**二叉樹**,它必須還要有**左子節點**和**右子節點**的**引用**,這樣我們才能找到下一個節點。比如大于 26 的時候,走右邊,到下一個樹的節點,繼續判斷。 <img src="https://i.loli.net/2021/01/05/GsZFONAeBX97gbo.png" alt="image-20210105121820008" style="zoom: 40%;" /> 在分析用 AVL 樹存儲索引數據之前,我們先來學習一下 InnoDB 的邏輯存儲結構。 #### 2.2.4 InnoDB邏輯存儲結構 MySQL 的存儲結構分為 5 級:表空間、段、簇、頁、行。 <img src="https://i.loli.net/2021/01/05/QsWeqVylYPCNrv5.png" alt="image-20210105122151788" style="zoom:67%;" /> ##### 2.2.4.1 表空間 Table Space `表空間`可以看做是 InnoDB 存儲引擎邏輯結構的最高層,**所有的數據**都存放在**表空間中**。分為:**系統表空間**、**獨占表空間**、**通用表空間**、**臨時表空間**、**Undo 表空間** ##### 2.2.4.2 段 Segment 表空間是由各個段組成的,常見的`段`有**數據段**、**索引段**、**回滾段**等的概念。**一個 ibd 文件(獨立表空間文件)里面會由很多個段組成**。創建一個索引會創建兩個段,**索引段**:leaf node segment,**數據段**:non-leaf node segment。索引段管理**非葉子節點**的數據。數據段管理**葉子節點**的數據。也就是說,一個表的段數,就是索引的個數乘以 2。 ##### 2.2.4.3 簇 Extent 一個段(Segment)又由很多的簇(也可以叫區)組成,每個區的大小是 **1MB**(64個連續的頁)。 每一個段至少會有一個簇,一個段所管理的空間大小是無限的,可以一直擴展下去,但是**擴展的最小單位就是簇**。 ##### 2.2.4.4 頁 Page **為了高效管理物理空間,對簇進一步細分,就得到了頁。**簇是由連續的頁(Page)組成的空間,一個簇中有 **64 個連續的頁**。 (1MB/16KB=64)。這些頁面在**物理上**和**邏輯上**都是連續的。跟大多數數據庫一樣,InnoDB 也有頁的概念(也可以稱為塊),每個頁默認 16KB。**頁**是 InnoDB 存儲引擎磁盤管理的**最小單位**,通過 innodb_page_size 設置。 >一個表空間最多擁有 2^32 個頁,默認情況下一個頁的大小為 16KB,也就是說一個表空間最多存儲 64TB 的數據。 注意,文件系統中,也有頁的概念。操作系統和內存打交道,最小的單位是頁 Page。文件系統的內存頁通常是 4K。 <img src="https://i.loli.net/2021/01/05/UkGvXOhFaHn9qRy.png" alt="image-20210105123136525" style="zoom: 50%;" /> ```sql SHOW VARIABLES LIKE 'innodb_page_size'; ``` 假設一行數據大小是 1K,那么一個數據頁可以放 16 行這樣的數據。舉例:一個頁放 3 行數據。 <img src="https://i.loli.net/2021/01/05/fCAODWJbtSj6MFz.png" alt="image-20210105123231225" style="zoom:67%;" /> 往表中插入數據時,如果一個頁面已經寫完,產生一個新的葉頁面。如果一個簇的所有的頁面都被用完,會從當前頁面所在段新分配一個簇。**如果數據不是連續的,往已經寫滿的頁中插入數據,會導致葉頁面分裂:** <img src="https://i.loli.net/2021/01/05/D6iIAo71aZuxPWB.png" alt="image-20210105123603118" style="zoom:50%;" /> ##### 2.2.4.5 行 Row InnoDB 存儲引擎是面向行的(row-oriented),也就是說數據的存放按行進行存放。DYNAMIC Row Format(5.7 默認) <img src="https://i.loli.net/2021/01/05/EjFJWcyBTg4zIVb.png" alt="image-20210105135856450" style="zoom:67%;" /> innodb_file_format 在配置文件中指定;row_format 則在創建數據表時指定。 ```sql show variables like "%innodb_file_format%"; SET GLOBAL innodb_file_format=Barracuda; ``` 在創建表的時候可以指定行格式。 ```sql CREATE TABLE tf1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; --查看行格式: SHOW TABLE STATUS LIKE 'student' \G; ``` <img src="https://i.loli.net/2021/01/05/AZd1zXHsRu8xN3U.png" alt="image-20210105140129869" style="zoom:50%;" /> 下面我們繼續看一下,用 AVL 樹存儲索引數據,會有什么樣的問題。 #### 2.2.5 AVL樹用于存儲索引數據 首先,**索引的數據**,是放在**硬盤上**的。查看數據和索引的大小: ```sql select CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data_len, CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'MB') as index_len from information_schema.TABLES where table_schema='gupao' and table_name='user_innodb'; ``` 當我們用樹的結構來存儲索引的時候,**訪問一個節點**就要跟磁盤之間**發生一次 IO**。InnoDB 操作磁盤的最小的單位是一**頁**(或者叫一個**磁盤塊**),**大小是 16K**(16384 字節)。那么,一個樹的節點就是 16K 的大小。*如果我們一個節點只存一個鍵值+數據+引用,例如整形的字段,可能只用了十幾個或者幾十個字節,它遠遠達不到 16K 的容量,所以訪問一個樹節點,進行一次 IO 的時候,浪費了大量的空間。*所以如果每個節點存儲的數據太少,從索引中找到我們需要的數據,就要訪問更多的節點,意味著**跟磁盤交互次數就會過多**。如果是機械硬盤時代,每次從磁盤讀取數據需要 10ms 左右的尋址時間,交互次數越多,消耗的時間就越多。 <img src="https://i.loli.net/2021/01/05/QWtX8dcIlug92CP.png" alt="image-20210105140604113" style="zoom:50%;" /> 比如上面這張圖,我們一張表里面有 6 條數據,當我們查詢 id=37 的時候,要查詢兩個子節點,就需要跟磁盤交互 3 次,如果我們有幾百萬的數據呢?這個時間更加難以估計。所以我們的解決方案是什么呢?第一個就是讓每個節點存儲更多的數據。第二個,節點上的關鍵字的數量越多,我們的指針數也越多,也就是意味著可以有 更多的**分叉(我們把它叫做“路數”)**。因為分叉數越多,樹的深度就會減少(根節點是 0)。這樣,我們的樹是不是從原來的高瘦高瘦的樣子,變成了矮胖矮胖的樣子?這個時候,我們的樹就不再是**二叉了,而是多叉,或者叫做多路。** #### 2.2.6 多路平衡查找樹(B Tree) Balanced Tree這個就是我們的多路平衡查找樹,叫做 B Tree(B 代表平衡)跟 AVL 樹一樣,B 樹在枝節點和葉子節點**存儲鍵值**、**數據地址**、**節點引用**。它有一個特點:分叉數(路數)永遠比關鍵字數多 1。比如我們畫的這棵樹,每個節點存儲兩個關鍵字,那么就會有三個指針指向三個子節點。 <img src="https://i.loli.net/2021/01/05/l7qmUQ3X9vogLHO.png" alt="image-20210105140930571" style="zoom:67%;" /> 那 B Tree 又是怎么實現一個節點存儲多個關鍵字,還保持平衡的呢?跟 AVL 樹有什么區別?比如 Max Degree(路數)是 3 的時候,我們**插入數據** 1、2、3,在插入 3 的時候,本來應該在第一個磁盤塊,但是如果一個節點有三個關鍵字的時候,意味著有 4 個指針,子節點會變成 4 路,所以這個時候必須進行**分裂**。把中間的數據 2 提上去,把 1 和 3 變成 2 的子節點。如果**刪除節點**,會有相反的**合并**的操作。注意這里是分裂和合并,跟 AVL 樹的左旋和右旋是不一樣的。我們繼續插入 4 和 5,B Tree 又會出現分裂和合并的操作。 <img src="https://i.loli.net/2021/01/05/8ATGqFmCRprUNaH.png" alt="image-20210105141104177" style="zoom:50%;" /> 在更新索引的時候會有大量的索引的結構的調整,所以 解釋了為什么我們不要在頻繁更新的列上建索引,或者為什么不要更新主鍵。**節點的分裂和合并,其實就是 InnoDB 頁的分裂和合并。** #### 2.2.7 B+樹(加強版多路平衡查找樹) B Tree 的效率已經很高了,為什么 MySQL 還要對 B Tree 進行改良,最終使用了B+Tree 呢?總體上來說,這個 B 樹的改良版本解決的問題比 B Tree 更全面。我們來看一下 InnoDB 里面的 B+樹的存儲結構: ![image-20210105141349904](https://i.loli.net/2021/01/05/8NVdqWyfnwvFLRP.png) MySQL 中的 B+Tree 有幾個特點: 1、它的關鍵字的數量是跟路數相等的; 2、B+Tree 的根節點和枝節點中都不會存儲數據,只有葉子節點才存儲數據。搜索到關鍵字不會直接返回,會到最后一層的葉子節點。比如我們搜索 id=28,雖然在第一層直接命中了,但是全部的數據在葉子節點上面,所以我還要繼續往下搜索,一直到葉子節點。 3、B+Tree 的每個葉子節點增加了一個指向相鄰葉子節點的指針,它的最后一個數據會指向下一個葉子節點的第一個數據,形成了一個有序鏈表的結構。 4、它是根據左閉右開的區間 [ )來檢索數據。 >舉個例子:假設一條記錄是 1K,一個葉子節點(一頁)可以存儲 16 條記錄。非葉子節點可以存儲多少個指針?假設索引字段是 bigint 類型,長度為 8 字節。指針大小在 InnoDB 源碼中設置為6 字節,這樣一共 14 字節。非葉子節點(一頁)可以存儲 16384/14=1170 個這樣的單元(鍵值+指針),代表有 1170 個指針。樹 深 度 為 2 的 時 候 , 有 1170^2 個 葉 子 節 點 , 可 以 存 儲 的 數 據 為1170*1170*16=21902400。 ![image-20210105141506796](https://i.loli.net/2021/01/05/WbgkaD39Vqp4rhm.png) 在查找數據時一次頁的查找代表一次 IO,也就是說,一張 2000 萬左右的表,查詢數據最多需要訪問 3 次磁盤。 所以在 InnoDB 中 B+ 樹深度一般為 1-3 層,它就能滿足千萬級的數據存儲。 **總結一下,InnoDB 中的 B+Tree 的特點:** 1)它是 B Tree 的變種,B Tree 能解決的問題,它都能解決。**B Tree 解決的兩大問題是什么**?(每個節點存儲更多關鍵字;路數更多) 2)掃庫、掃表能力更強(如果我們要對表進行**全表掃描**,只需要**遍歷葉子節點就可以了**,不需要遍歷整棵 B+Tree 拿到所有的數據) 3) B+Tree 的**磁盤讀寫能力**相對于 B Tree 來說**更強**(根節點和枝節點不保存數據區,所以一個節點可以保存更多的關鍵字,一次磁盤加載的關鍵字更多) 4)**排序能力更強**(因為**葉子節點**上有下一個數據區的指針,**數據形成了鏈表**) 5)**效率更加穩定**(B+Tree 永遠是在葉子節點拿到數據,所以 **IO 次數是穩定的**) #### 2.2.8 索引方式:真的是用的 B+Tree嗎? 在 Navicat 的工具中,創建索引,索引方式有兩種,**Hash** 和 **B Tree**。 **HASH**:以 KV 的形式檢索數據,也就是說,它會根據索引字段生成**哈希碼**和指針,指針指向數據。 <img src="https://i.loli.net/2021/01/05/2EQriNMupZbhYFj.png" alt="image-20210105142120458" style="zoom:67%;" /> **哈希索引有什么特點呢?** - 時間復雜度是 O(1),查詢速度比較快。因為哈希索引里面的數據不是按順序存儲的,所以不能用于排序。 - 查詢數據時根據鍵值計算哈希碼,只支持等值查詢(= IN),不支持范圍查詢(> < >= <= between and)。 - 如果字段重復值很多的時候,會出現大量的哈希沖突(采用拉鏈法解決),效率會降低。 InnoDB 內部使用哈希索引來實現自適應哈希索引特性。 這句話的意思是 InnoDB 只支持顯式創建 B+Tree 索引,對于一些熱點數據頁, InnoDB 會自動建立自適應 Hash 索引,也就是在 B+Tree 索引基礎上建立 Hash 索引, 這個過程對于客戶端是不可控制的,隱式的。 ### 2.3 MySql有哪些索引? ■數據結構角度 1. BTREE 2. HASH 3. FULLTEXT 4. R-Tree ■物理存儲角度 1. 聚集索引( clustered index) 2. 非聚集索引(non- clustered index ■從邏輯角度 1. **普通索引**:僅加速查詢 2. **唯一索引**:加速査詢+列值唯一(可以有null) 3. **主鍵索引**:加速查詢+列值唯一(不可以有null)+表中只有一個 4. **組合索引**:多列值組成一個索引,專門用于組合搜索,其效率大于索引合并 5. **全文索引**:對文本的內容進行分詞,進行搜索 在 InnoDB 里面,索引類型有三種,普通索引、唯一索引(主鍵索引是特殊的唯一索引)、全文索引。 - 普通索引(Normal):也叫非唯一索引,是最普通的索引,沒有任何的限制。 - 唯一索引(Unique):唯一索引要求鍵值不能重復。另外需要注意的是,主鍵索引是一種特殊的唯一索引,它還多了一個限制條件,要求鍵值不能為空。主鍵索引用 primay key創建。 - 全文索引(Fulltext):針對比較大的數據,比如我們存放的是消息內容,有幾 KB 的數據的這種情況,如果要解決 like 查詢效率低的問題,可以創建全文索引。只有文本類型的字段才可以創建全文索引,比如 char、varchar、text。 #### 2.3.1 常見索引類型 ##### 2.3.1.1 主鍵索引(Primary Key) 數據表的主鍵列使用的就是主鍵索引。 一張數據表有只能有一個主鍵,并且主鍵不能為null,不能重復。 在mysql的InnoDB的表中,當沒有顯示的指定表的主鍵時,InnoDB會自動先檢查表中是否有唯一索引的字段,如果有,則選擇該字段為默認的主鍵,否則InnoDB將會自動創建一個6Byte的自增主鍵。 ##### 2.3.1.2 二級索引(輔助索引) **二級索引又稱為輔助索引,是因為二級索引的葉子節點存儲的數據是主鍵。也就是說,通過二級索引,可以定位主鍵的位置。** 唯一索引,普通索引,前綴索引等索引屬于二級索引。 ![image-20210105143733634](https://i.loli.net/2021/01/05/mpFHXJPxGYb7dZl.png) InnoDB 中,主鍵索引和輔助索引是有一個主次之分的。輔助索引存儲的是輔助索引和主鍵值。如果使用輔助索引查詢,會根據主鍵值在主鍵索引中查詢,最終取得數據。 **PS:不懂的同學可以暫存疑,慢慢往下看,后面會有答案的,也可以自行搜索。** 1. **唯一索引(Unique Key)** :唯一索引也是一種約束。**唯一索引的屬性列不能出現重復的數據,但是允許數據為NULL,一張表允許創建多個唯一索引。** 建立唯一索引的目的大部分時候都是為了該屬性列的數據的唯一性,而不是為了查詢效率。 2. **普通索引(Index)** :**普通索引的唯一作用就是為了快速查詢數據,一張表允許創建多個普通索引,并允許數據重復和NULL。** 3. **前綴索引(Prefix)** :前綴索引只適用于字符串類型的數據。前綴索引是對文本的前幾個字符創建索引,相比普通索引建立的數據更小, 因為只取前幾個字符。 4. **全文索引(Full Text)** :全文索引主要是為了檢索大文本數據中的關鍵字的信息,是目前搜索引擎數據庫使用的一種技術。Mysql5.6之前只有MYISAM引擎支持全文索引,5.6之后InnoDB也支持了全文索引。 ##### 2.3.1.3 聚集索引與非聚集索引 ###### 2.3.1.3.1 聚集索引 **聚集索引即索引結構和數據一起存放的索引。主鍵索引屬于聚集索引。**索引鍵值的邏輯順序跟表數據行的物理存儲順序是一致的 在 Mysql 中,InnoDB引擎的表的 `.ibd`文件就包含了該表的索引和數據,對于 InnoDB 引擎表來說,該表的索引(B+樹)的每個非葉子節點存儲索引,葉子節點存儲索引和索引對應的數據。 ###### 2.3.1.3.2 聚集索引的優點 聚集索引的查詢速度非常的快,因為整個B+樹本身就是一顆多叉平衡樹,葉子節點也都是有序的,定位到索引的節點,就相當于定位到了數據。 ###### 2.3.1.3.3 聚集索引的缺點 1. **依賴于有序的數據** :因為B+樹是多路平衡樹,如果索引的數據不是有序的,那么就需要在插入時排序,如果數據是整型還好,否則類似于字符串或UUID這種又長又難比較的數據,插入或查找的速度肯定比較慢。 2. **更新代價大** : 如果對索引列的數據被修改時,那么對應的索引也將會被修改, 而且況聚集索引的葉子節點還存放著數據,修改代價肯定是較大的, 所以對于主鍵索引來說,主鍵一般都是不可被修改的。 ##### 2.3.1.4 非聚集索引 **非聚集索引即索引結構和數據分開存放的索引。** **二級索引屬于非聚集索引。** >MYISAM引擎的表的.MYI文件包含了表的索引, >該表的索引(B+樹)的每個葉子非葉子節點存儲索引, >葉子節點存儲索引和索引對應數據的指針,指向.MYD文件的數據。 > >**非聚集索引的葉子節點并不一定存放數據的指針, >因為二級索引的葉子節點就存放的是主鍵,根據主鍵再回表查數據。** ###### 2.3.1.4.1 非聚集索引的優點 **更新代價比聚集索引要小** 。非聚集索引的更新代價就沒有聚集索引那么大了,非聚集索引的葉子節點是不存放數據的 ###### 2.3.1.4.2 非聚集索引的缺點 1. 跟聚集索引一樣,非聚集索引也依賴于有序的數據 2. **可能會二次查詢(回表)** :這應該是非聚集索引最大的缺點了。 當查到索引對應的指針或主鍵后,可能還需要根據指針或主鍵再到數據文件或表中查詢。 這是Mysql的表的文件截圖: ![Mysql表文件截圖](C:\Users\chenm\Desktop\JavaGuide\media\pictures\database\Mysql索引文件截圖.png) 聚集索引和非聚集索引: ![B+樹](C:\Users\chenm\Desktop\JavaGuide\media\pictures\database\B+樹索引.png) ##### 2.3.1.5 非聚集索引一定回表查詢嗎(覆蓋索引)? **非聚集索引不一定回表查詢。** >試想一種情況,用戶準備使用SQL查詢用戶名,而用戶名字段正好建立了索引。 ````text SELECT name FROM table WHERE username='guang19'; ```` >那么這個索引的key本身就是name,查到對應的name直接返回就行了,無需回表查詢。 **即使是MYISAM也是這樣,雖然MYISAM的主鍵索引確實需要回表, 因為它的主鍵索引的葉子節點存放的是指針。但是如果SQL查的就是主鍵呢?** ```text SELECT id FROM table WHERE id=1; ``` 主鍵索引本身的key就是主鍵,查到返回就行了。這種情況就稱之為覆蓋索引了。 ##### 2.3.1.6覆蓋索引 如果一個索引包含(或者說覆蓋)所有需要查詢的字段的值,我們就稱之為“覆蓋索引”。我們知道在InnoDB存儲引擎中,如果不是主鍵索引,葉子節點存儲的是主鍵+列值。最終還是要“回表”,也就是要通過主鍵再查找一次。這樣就會比較慢覆蓋索引就是把要查詢出的列和索引是對應的,不做回表操作! **覆蓋索引即需要查詢的字段正好是索引的字段,那么直接根據該索引,就可以查到數據了, 而無需回表查詢。** >如主鍵索引,如果一條SQL需要查詢主鍵,那么正好根據主鍵索引就可以查到主鍵。 > >再如普通索引,如果一條SQL需要查詢name,name字段正好有索引, >那么直接根據這個索引就可以查到數據,也無需回表。 覆蓋索引: ![B+樹覆蓋索引](C:\Users\chenm\Desktop\JavaGuide\media\pictures\database\B+樹覆蓋索引.png) #### 2.3.2 主鍵和唯一索引區別? 本質區別,主鍵是一種約束,唯一索引是一種索引。 - 主鍵不能有空值(非空+唯一),唯一索引可以為空 - 主鍵可以是其他表的外鍵,唯一索引不可以。 - 一個表只能有一個主鍵,唯一索引可以多個。 - 都可以建立聯合主鍵或聯合唯一索引 - 主鍵-》聚簇索引,唯一索引->非聚簇索引。 #### 2.3.3 索引不生效的情況? - 使用不等于查詢 - NULL值 - 列參與了數學運算或者函數 - 在字符串like時左邊是通配符比如%xxx - 當mysq分析全表掃描比使用索引快的時候不使用索引 - 當使用聯合索引前面一個條件為范圍查詢后面的即使符合最左前綴原則,也無法使用索引 #### 2.3.4 Mysql索引使用兩種數據結構 MySQL索引使用的數據結構主要有**BTree索引** 和 **哈希索引** 。對于哈希索引來說,底層的數據結構就是哈希表,因此在絕大多數需求為單條記錄查詢的時候,可以選擇哈希索引,查詢性能最快;其余大部分場景,建議選擇BTree索引。 ##### Hash索引和 B+樹索引優劣分析 **Hash索引定位快** Hash索引指的就是Hash表,最大的優點就是能夠在很短的時間內,根據Hash函數定位到數據所在的位置,這是B+樹所不能比的。 **Hash沖突問題** 知道HashMap或HashTable的同學,相信都知道它們最大的缺點就是Hash沖突了。不過對于數據庫來說這還不算最大的缺點。 **Hash索引不支持順序和范圍查詢(Hash索引不支持順序和范圍查詢是它最大的缺點。** 試想一種情況: ````text SELECT * FROM tb1 WHERE id < 500; ```` B+樹是有序的,在這種范圍查詢中,優勢非常大,直接遍歷比500小的葉子節點就夠了。而Hash索引是根據hash算法來定位的,難不成還要把 1 - 499的數據,每個都進行一次hash計算來定位嗎?這就是Hash最大的缺點了。 MySQL的BTree索引使用的是B樹中的B+Tree,但對于主要的兩種存儲引擎的實現方式是不同的。 - **MyISAM:** B+Tree葉節點的data域存放的是數據記錄的地址。在索引檢索的時候,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,則取出其 data 域的值,然后以 data 域的值為地址讀取相應的數據記錄。這被稱為“非聚簇索引”。 - **InnoDB:** 其數據文件本身就是索引文件。相比MyISAM,索引文件和數據文件是分離的,其表數據文件本身就是按B+Tree組織的一個索引結構,樹的葉節點data域保存了完整的數據記錄。這個索引的key是數據表的主鍵,因此InnoDB表數據文件本身就是主索引。這被稱為“聚簇索引(或聚集索引)”。而其余的索引都作為輔助索引,輔助索引的data域存儲相應記錄主鍵的值而不是地址,這也是和MyISAM不同的地方。**在根據主索引搜索時,直接找到key所在的節點即可取出數據;在根據輔助索引查找時,則需要先取出主鍵的值,再走一遍主索引。** **因此,在設計表的時候,不建議使用過長的字段作為主鍵,也不建議使用非單調的字段作為主鍵,這樣會造成主索引頻繁分裂。** PS:整理自《Java工程師修煉之道》 #### 2.3.5 MyISAM和InnoDB實現BTree索引方式的區別 ##### 2.3.5.1.B樹和B+樹 ###### 1.B樹 > B樹是一種多路搜索樹。 > > 1. 定義任意非葉子結點最多只有M個兒子,且M>2。 > 2. 根結點的兒子數為[2, M]。 > 3. 除根結點以外的非葉子結點的兒子數為[M/2, M]。(結點豐滿程度至少為一半) > 4. 每個結點存放至少M/2-1(取上整)和至多M-1個關鍵字;(至少2個關鍵字)。 > 5. 非葉子結點的關鍵字個數=指向兒子的指針個數-1。 > 6. 非葉子結點的關鍵字:K[1], K[2], …, K[M-1],且K[i] <= K[i+1]。 > 7. 非葉子結點的指針:P[1], P[2], …,P[M](其中P[1]指向關鍵字小于K[1]的子樹,P[M]指向關鍵字大于K[M-1]的子樹,其它P[i]指向關鍵字屬于(K[i-1], K[i])的子樹)。 > 8. 所有葉子結點位于同一層。 下圖是一個M=4階的B樹。 ![image-20200117202550521](https://tva1.sinaimg.cn/large/006tNbRwgy1gaztx1zsklj30mw07pwhg.jpg) B樹的搜索,從根結點開始,對結點內的關鍵字(有序)序列進行二分查找,如果命中則結束,否則進入查詢關鍵字所屬范圍的兒子結點;重復,直到所對應的是葉子結點。 查找文件29的過程: 1. 根據根結點指針找到文件目錄的根磁盤塊1,將其中的信息導入內存。(磁盤IO操作1次) 2. 此時內存中有兩個文件名17,35和三個存儲其他磁盤頁面地址的數據。根據算法我們發現17<29<35,因此我們找到指針p2。 3. 根據p2指針,我們定位到磁盤塊3,并將其中的信息導入內存。(磁盤IO操作2次) 4. 此時內存中有兩個文件名26,30和三個存儲其他磁盤頁面地址的數據。根據算法我們發現26<29<30,因此我們找到指針p2。 5. 根據p2指針,我們定位到磁盤塊8,并將其中的信息導入內存。(磁盤IO操作3次) 6. 此時內存中有兩個文件名28,29。根據算法我們查找到文件29,并定位了該文件內存的磁盤地址。 ```java B樹的特性: 1.關鍵字分布在整顆樹的所有節點。 2.任何一個關鍵字出現且只出現在一個結點中。 3.搜索有可能在非葉子結點結束。 4.其搜索性能等價于在關鍵字全集內做一次二分查找。 ``` ###### 2.B+樹 ![image-20200117211239354](https://i.loli.net/2021/01/04/idFjBlDk5m49ASJ.jpg) 下圖是一個M=3階的B+樹。 ![image-20200117203127225](https://tva1.sinaimg.cn/large/006tNbRwgy1gazu2wblmhj30ji05wq34.jpg) 一般在數據庫系統或文件系統中使用的B+Tree結構都在經典B+Tree的基礎上進行了優化,增加了順序訪問指針。 ![image-20200117203153572](https://tva1.sinaimg.cn/large/006tNbRwgy1gazu3ckoz1j30kk05mglt.jpg) B+樹與B樹的差異 ```JAVA B+樹是B樹的一種變形樹,總結起來,數據庫索引的B+樹與B樹的差異在于: 1.非葉子結點的子樹指針與關鍵字個數相同。 2.非葉子結點的子樹指針P[i],指向關鍵字值屬于[K[i],K[i+1])的子樹(注意,區間是前閉后開)。 3.為所有葉子結點增加一個鏈指針。 4.所有關鍵字都在葉子結點出現。 ``` B+樹的特性: ```JAVA 1. 所有關鍵字都出現在葉子結點的鏈表中,且鏈表中的關鍵字是有序的。 2. 搜索只在葉子結點命中。 3. 非葉子結點相當于是葉子結點的索引,葉子結點是存儲關鍵字數據的數據層。 ``` ###### 3.B樹/B+樹做索引的原因分析 一般來說,磁盤I/O次數可以用于評價索引結構的優劣。在B-Tree中查找,可知檢索一次最多需要訪問h個節點(上文舉例查找文件29的過程)。數據庫系統的設計者巧妙利用了磁盤預讀原理,將一個節點的大小設為等于一個頁,這樣每個節點只需要一次I/O就可以完全載入。 為了達到這個目的,在實際實現中,B樹還使用如下技巧: 1. 每次新建節點時,直接申請一個頁的空間,這樣就保證一個節點物理上也存儲在一個頁里,加之計算機存儲分配都是按頁對齊的,就實現了一個節點只需一次I/O。 2. B樹中一次檢索最多需要h-1次I/O(根節點常駐內存)。一般實際應用中,出度d(樹的分叉數)是非常大的數字,通常超過100;h非常小,通常不超過3。 綜上所述,用B樹作為索引結構效率是非常高的。 紅黑樹或者平衡二叉樹的其他樹結構, 1. h明顯要深的多,執行效率低。 2. 邏輯上很近的節點(父子)物理上可能很遠,無法利用局部性, 3. 每個節點存儲的數據量太小了,對磁盤空間造成浪費,帶來頻繁的IO操作。 所以其他樹結構的效率明顯比B樹差很多。 ##### 2.3.5.2.Innodb索引結構基礎知識 ###### 1 各種樹形結構 **<font color="darkred">1. 搜索二叉樹</font>**:每個節點有兩個子節點,數據量的增大必然導致高度的快速增加,顯然這個不適合作為大量數據存儲的基礎結構。 **<font color="darkred">2. B樹</font>:**一棵m階B樹是一棵平衡的m路搜索樹。最重要的性質是每個非根節點所包含的關鍵字個數 j 滿足: $$(\frac{m}{2}-1)\leq j \leq (m-1)$$ ;<u>一個節點的子節點數量會比關鍵字個數多1</u>,這樣關鍵字就變成了子節點的分割標志。由于數據同時存在于葉子節點和非葉子結點中,無法簡單完成按順序遍歷B樹中的關鍵字,必須用中序遍歷的方法。 **<font color="darkred">3. B+樹</font>:**一棵m階B樹是一棵平衡的m路搜索樹。最重要的性質是每個非根節點所包含的關鍵字個數 j 滿足:$$(\frac{m}{2}-1)\leq j \leq (m)$$;<u>子樹的個數最多可以與關鍵字一樣多。</u>非葉節點存儲的是子樹里最小的關鍵字。同時數據節點只存在于葉子節點中,且葉子節點間增加了橫向的指針,這樣順序遍歷所有數據將變得非常容易。 **<font color="darkred">4. B*樹</font>:**一棵m階B樹是一棵平衡的m路搜索樹。最重要的兩個性質是:①每個非根節點所包含的關鍵字個數 j 滿足$$(\frac{2m}{3}-1)\leq j \leq (m)$$ 。②非葉節點間添加了橫向指 ![image-20200117154537601](https://tva1.sinaimg.cn/large/006tNbRwgy1gazltmryt9j317f0u0ds4.jpg) 先看B樹的分裂,下圖的紅色值即為每次新插入的節點。每當一個節點滿后,就需要發生分裂,并且有可能引起分裂想根節點方向的傳遞,(甚至傳遞到root節點,導致root節點也分裂,從而引起樹的高度增加) ![image-20200117155616198](https://tva1.sinaimg.cn/large/006tNbRwgy1gazm4lfbv6j31gq08qgog.jpg) **<font color="darkred">B+樹的分裂</font>**:當一個結點滿時,分配一個新的結點,并將原結點中1/2的數據復制到新結點,最后在父結點中增加新結點的指針;B+樹的分裂只影響原結點和父結點,而不會影響兄弟結點,所以它不需要指向兄弟節點的指針。 ![image-20200117155718598](https://tva1.sinaimg.cn/large/006tNbRwgy1gazm5oa0f7j31gi0ewgnz.jpg) <font color="darkred">?**B\*樹的分裂:**</font>當一個結點滿時,如果它的下一個兄弟結點未滿,那么將一部分數據移到兄弟結點中,再在原結點插入關鍵字,最后修改父結點中兄弟結點的關鍵字(因為兄弟結點的關鍵字范圍改變了)。如果兄弟也滿了,則在原結點與兄弟結點之間增加新結點,并各復制1/3的數據到新結點,最后在父結點增加新結點的指針。可以看到B*樹的分裂非常巧妙,因為B*樹要保證分裂后的節點還要2/3滿,如果采用B+樹的方法,只是簡單的將已滿的節點一分為二,會導致每個節點只有1/2滿,這不滿足B*樹的要求了。所以B*樹采取的策略是在本節點滿后,繼續插入兄弟節點(這也是為什么B*樹需要在非葉子節點加一個兄弟間的鏈表),直到把兄弟節點也塞滿,然后拉上兄弟節點一起湊份子,自己和兄弟節點各出資1/3成立新節點,這樣的結果是3個節點剛好是2/3滿,達到B*樹的要求,皆大歡喜。 ![image-20200117160253866](https://tva1.sinaimg.cn/large/006tNbRwgy1gazmbikdznj31mk0fcjuy.jpg) B+樹適合作為數據庫的基礎結構,完全是因為計算機的內存-機械硬盤兩層存儲結構。內存可以完成快速的隨機訪問(隨機訪問即給出任意一個地址,要求返回這個地址存儲的數據)但是容量較小。而硬盤的隨機訪問要經過機械動作(1磁頭移動 2盤片轉動),訪問效率比內存低幾個數量級,但是硬盤容量較大。典型的數據庫容量大大超過可用內存大小,這就決定了在B+樹中檢索一條數據很可能要借助幾次磁盤IO操作來完成。如下圖所示:通常向下讀取一個節點的動作可能會是一次磁盤IO操作,不過非葉節點通常會在初始階段載入內存以加快訪問速度。同時為提高在節點間橫向遍歷速度,真實數據庫中可能會將圖中藍色的CPU計算/內存讀取優化成二叉搜索樹(InnoDB中的page directory機制)。 ![image-20200117160440861](https://tva1.sinaimg.cn/large/006tNbRwgy1gazmdcm8kaj31nc0fmaen.jpg) 真實數據庫中的B+樹應該是非常扁平的。對于一個22.1G容量的表,B+樹的高度是3,如果要把非葉節點全部加載到內存也只需要少于18.8M的內存。 ###### 2 Mysql的存儲引擎和索引 數據庫必須有索引,沒有索引則檢索過程變成了順序查找,O(n)的時間復雜度幾乎是不能忍受的。我們非常容易想象出一個只有單關鍵字組成的表如何使用B+樹進行索引,只要將關鍵字存儲到樹的節點即可。當數據庫一條記錄里包含多個字段時,一棵B+樹就只能存儲主鍵,如果檢索的是非主鍵字段,則主鍵索引失去作用,又變成順序查找了。這時應該在第二個要檢索的列上建立第二套索引。 這個索引由獨立的B+樹來組織。有兩種常見的方法可以解決多個B+樹訪問同一套表數據的問題,**一種叫做聚簇索引(clustered index ),一種叫做非聚簇索引(secondary index)**。這兩個名字雖然都叫做索引,但這并不是一種單獨的索引類型,而是一種數據存儲方式。對于聚簇索引存儲來說,行數據和主鍵B+樹存儲在一起,輔助鍵B+樹只存儲輔助鍵和主鍵,主鍵和非主鍵B+樹幾乎是兩種類型的樹。對于非聚簇索引存儲來說,主鍵B+樹在葉子節點存儲指向真正數據行的指針,而非主鍵。 ``` InnoDB使用的是聚簇索引,將主鍵組織到一棵B+樹中,而行數據就儲存在葉子節點上,若使用"where id = 14"這樣的條件查找主鍵,則按照B+樹的檢索算法即可查找到對應的葉節點,之后獲得行數據。若對Name列進行條件搜索,則需要兩個步驟:第一步在輔助索引B+樹中檢索Name,到達其葉子節點獲取對應的主鍵。第二步使用主鍵在主索引B+樹種再執行一次B+樹檢索操作,最終到達葉子節點即可獲取整行數據。 MyISM使用的是非聚簇索引,非聚簇索引的兩棵B+樹看上去沒什么不同,節點的結構完全一致只是存儲的內容不同而已,主鍵索引B+樹的節點存儲了主鍵,輔助鍵索引B+樹存儲了輔助鍵。表數據存儲在獨立的地方,這兩顆B+樹的葉子節點都使用一個地址指向真正的表數據,對于表數據來說,這兩個鍵沒有任何差別。由于索引樹是獨立的,通過輔助鍵檢索無需訪問主鍵的索引樹。 ``` 為了更形象說明這兩種索引的區別,我們假想一個表如下圖存儲了4行數據。其中Id作為主索引,Name作為輔助索引。圖示清晰的顯示了聚簇索引和非聚簇索引的差異。 <img src="https://tva1.sinaimg.cn/large/006tNbRwgy1gazmrxzzp6j30wn0u07wh.jpg" alt="image-20200117161839227" style="zoom:67%;" /> 我們重點關注聚簇索引,看上去聚簇索引的效率明顯要低于非聚簇索引,因為每次使用輔助索引檢索都要經過兩次B+樹查找,這不是多此一舉嗎?聚簇索引的優勢在哪? 1 由于行數據和葉子節點存儲在一起,這樣主鍵和行數據是一起被載入內存的,找到葉子節點就可以立刻將行數據返回了,如果按照主鍵Id來組織數據,獲得數據更快。 2 輔助索引使用主鍵作為"指針" 而不是使用地址值作為指針的好處是,減少了當出現行移動或者數據頁分裂時輔助索引的維護工作,使用主鍵值當作指針會讓輔助索引占用更多的空間,換來的好處是InnoDB在移動行時無須更新輔助索引中的這個"指針"。也就是說行的位置(實現中通過16K的Page來定位,后面會涉及)會隨著數據庫里數據的修改而發生變化(前面的B+樹節點分裂以及Page的分裂),使用聚簇索引就可以保證不管這個主鍵B+樹的節點如何變化,輔助索引樹都不受影響。 ###### 3 Page結構 理解InnoDB的實現不得不提Page結構,Page是整個InnoDB存儲的最基本構件,也是InnoDB磁盤管理的最小單位,與數據庫相關的所有內容都存儲在這種Page結構里。Page分為幾種類型,常見的頁類型有數據頁(B-tree Node)Undo頁(Undo Log Page)系統頁(System Page) 事務數據頁(Transaction System Page)等。單個Page的大小是16K(編譯宏UNIV_PAGE_SIZE控制),每個Page使用一個32位的int值來唯一標識,這也正好對應InnoDB最大64TB的存儲容量(16Kib * 2^32 = 64Tib)。一個Page的基本結構如下圖所示: ![image-20200117162532442](https://tva1.sinaimg.cn/large/006tNbRwgy1gazmz3763mj31oy0u0teg.jpg) 每個Page都有通用的頭和尾,但是中部的內容根據Page的類型不同而發生變化。Page的頭部里有我們關心的一些數據,上圖把Page的頭部詳細信息顯示出來: 我們重點關注和數據組織結構相關的字段:Page的頭部保存了兩個指針,分別指向前一個Page和后一個Page,頭部還有Page的類型信息和用來唯一標識Page的編號。根據這兩個指針我們很容易想象出Page鏈接起來就是一個雙向鏈表的結構。 ![image-20200117162620581](https://tva1.sinaimg.cn/large/006tNbRwgy1gazmzw2uphj31mm0bimym.jpg) 再看看Page的主體內容,我們主要關注行數據和索引的存儲,他們都位于Page的User Records部分,User Records占據Page的大部分空間,User Records由一條一條的Record組成,每條記錄代表索引樹上的一個節點(非葉子節點和葉子節點)。在一個Page內部,單鏈表的頭尾由固定內容的兩條記錄來表示,字符串形式的"Infimum"代表開頭,"Supremum"代表結尾。這兩個用來代表開頭結尾的Record存儲在System Records的段里,這個System Records和User Records是兩個平行的段。InnoDB存在4種不同的Record,它們分別是1主鍵索引樹非葉節點 2主鍵索引樹葉子節點 3輔助鍵索引樹非葉節點 4輔助鍵索引樹葉子節點。這4種節點的Record格式有一些差異,但是它們都存儲著Next指針指向下一個Record。后續我們會詳細介紹這4種節點,現在只需要把Record當成一個存儲了數據同時含有Next指針的單鏈表節點即可。 ![image-20200117163247677](https://tva1.sinaimg.cn/large/006tNbRwgy1gazn6qfszxj317u0u07mx.jpg) User Record在Page內以單鏈表的形式存在,最初數據是按照插入的先后順序排列的,但是隨著新數據的插入和舊數據的刪除,數據物理順序會變得混亂,但他們依然保持著邏輯上的先后順序 把User Record的組織形式和若干Page組合起來,就看到了稍微完整的形式。 ![image-20200117163354303](https://tva1.sinaimg.cn/large/006tNbRwgy1gazn7r81ncj31r40ccwh9.jpg) 現在看下如何定位一個Record: - 1 通過根節點開始遍歷一個索引的B+樹,通過各層非葉子節點最終到達一個Page,這個Page里存放的都是葉子節點。 - 2 在Page內從"Infimum"節點開始遍歷單鏈表(這種遍歷往往會被優化),如果找到該鍵則成功返回。如果記錄到達了"supremum",說明當前Page里沒有合適的鍵,這時要借助Page的Next Page指針,跳轉到下一個Page繼續從"Infimum"開始逐個查找。 ![image-20200117163527660](https://tva1.sinaimg.cn/large/006tNbRwgy1gazn9cxab5j31ko0dgdj0.jpg) 詳細看下不同類型的Record里到底存儲了什么數據,根據B+樹節點的不同,User Record可以被分成四種格式,下圖種按照顏色予以區分。 - **1 主索引樹非葉節點(綠色)** + 1 子節點存儲的主鍵里最小的值(Min Cluster Key on Child),這是B+樹必須的,作用是在一個Page里定位到具體的記錄的位置。 + 2 最小的值所在的Page的編號(Child Page Number),作用是定位Record。 - **2 主索引樹葉子節點(黃色)** -  1 主鍵(Cluster Key Fields),B+樹必須的,也是數據行的一部分 -  2 除去主鍵以外的所有列(Non-Key Fields),這是數據行的除去主鍵的其他所有列的集合。   這里的1和2兩部分加起來就是一個完整的數據行。 - **3 輔助索引樹非葉節點非(藍色)** - 1 子節點里存儲的輔助鍵值里的最小的值(Min Secondary-Key on Child),這是B+樹必須的,作用是在一個Page里定位到具體的記錄的位置。  - 2 主鍵值(Cluster Key Fields),非葉子節點為什么要存儲主鍵呢?因為輔助索引是可以不唯一的,但是B+樹要求鍵的值必須唯一,所以這里把輔助鍵的值和主鍵的值合并起來作為在B+樹中的真正鍵值,保證了唯一性。但是這也導致在輔助索引B+樹中非葉節點反而比葉子節點多了4個字節。(即下圖中藍色節點反而比紅色多了4字節) - 3 最小的值所在的Page的編號(Child Page Number),作用是定位Record。 - **4 輔助索引樹葉子節點(紅色)** - 1 輔助索引鍵值(Secondary Key Fields),這是B+樹必須的。 - 2 主鍵值(Cluster Key Fields),用來在主索引樹里再做一次B+樹檢索來找到整條記錄。 ![image-20200117164250086](https://tva1.sinaimg.cn/large/006tNbRwgy1gaznh4erwwj31bj0u01kx.jpg) 下面是本篇最重要的部分了,結合B+樹的結構和前面介紹的4種Record的內容,我們終于可以畫出一幅全景圖。由于輔助索引的B+樹與主鍵索引有相似的結構,這里只畫出了主鍵索引樹的結構圖,只包含了"主鍵非葉節點"和"主鍵葉子節點"兩種節點,也就是上圖的的綠色和黃色的部分。 ![image-20200117164413499](https://tva1.sinaimg.cn/large/006tNbRwgy1gaznijwgotj31680u01kx.jpg) 把上圖還原成下面這個更簡潔的樹形示意圖,這就是B+樹的一部分。注意Page和B+樹節點之間并沒有一一對應的關系,Page只是作為一個Record的保存容器,它存在的目的是便于對磁盤空間進行批量管理,上圖中的編號為47的Page在樹形結構上就被拆分成了兩個獨立節 ![image-20200117165309458](https://tva1.sinaimg.cn/large/006tNbRwgy1gaznrs97ljj31l80e6whe.jpg) ##### 2.3.5.3.MyISAM ###### 1. 磁盤存儲 MyISAM在磁盤存儲上有三個文件,每個文件名以表名開頭,擴展名指出文件類型。 - .frm:用于存儲表的定義。 - .MYD:用于存放數據。 - .MYI:用于存放表索引。 ###### 2. 主鍵索引 MyISAM引擎使用B+樹作為索引結果,葉節點的data域存放的是數據記錄的地址。 <img src="https://i.loli.net/2021/01/05/tB8fmYNjlZXcykP.png" alt="image-20210105161444788" style="zoom:50%;" /> MyISAM索引文件和數據文件是分離的,索引文件僅保存記錄所在頁的指針(物理位置),通過這些地址來讀取頁,進而讀取被索引的行。 樹中葉子保存的是對應行的物理位置。通過該值,存儲引擎能順利地進行回表查詢,得到一行完整記錄。同時,每個葉子頁也保存了指向下一個葉子頁的指針。從而方便葉子節點的范圍遍歷。 ###### 3. 輔助索引 在MyISAM中,主鍵索引和輔助索引在結構上沒有任何區別,只是主鍵索引要求key是唯一的,而輔助索引的key可以重復。 <img src="https://i.loli.net/2021/01/05/aZsvymQXwDtGJkf.png" alt="image-20210105161456613" style="zoom:50%;" /> ##### 2.3.5.4.Innodb MySQL5.5開始支持InnoDB引擎,并將其作為默認數據庫引擎。 ###### 1. 磁盤存儲 Innodb有兩種存儲方式,共享表空間存儲和多表空間存儲。 Innodb只有表結構文件和數據文件。 表結構文件和MyISAM一樣,以表名開頭,擴展名是.frm。 數據文件與存儲方式有關: - 如果使用共享表空間,那么所有表的數據文件和索引文件都保存在一個表空間里,一個表空間可以有多個文件,通過innodb_data_file_path和innodb_data_home_dir參數設置共享表空間的位置和名字,一般共享表空間的名字叫ibdata1-n。 - 如果使用多表空間,那么每個表都有一個表空間文件用于存儲每個表的數據和索引,文件名以表名開頭,以.ibd為擴展名。 ###### 2. 主鍵索引 Innodb主鍵索引中,既存儲了主鍵值,又存儲了行數據。(聚簇索引) ![image-20200117204341815](https://i.loli.net/2021/01/05/7VFE8n2Yc3tOlTp.jpg) ###### 3. 輔助索引 對于輔助索引,InnoDB采用的方式是在葉子頁中保存主鍵值,通過這個主鍵值來回表(上圖)查詢到一條完整記錄,因此按輔助索引檢索實際上進行了二次查詢,效率肯定是沒有按照主鍵檢索高的。 ![image-20200117204414816](https://tva1.sinaimg.cn/large/006tNbRwgy1gazug7al17j30l506et90.jpg) ##### 2.3.5.5.Innodb與MyISAM的區別 | | Innodb索引 | MyISAM索引 | | -------------- | ------------------------------------------------------------ | ------------------------------------------------------------ | | 1.存儲結構 | 存儲方式不同結構不同 | 三個文件frm(表結構)、MYD(表數據)、MYI(表索引) | | 2.事務支持 | 支持事務 | 不支持事務(MyISAM強調性能) | | 3.外鍵和主鍵 | 支持外鍵(但Innodb必須有主鍵,若沒指定主鍵,自動生成6字節主鍵) | 不支持外鍵 | | 4.鎖 | 行級鎖(但行級鎖只有在where子句是對主鍵篩選才生效,非主鍵會鎖全表)InnoDB 比較適合于插入和更新操作比較多的情況 | 表級鎖(MyISAM 則適用于頻繁的查詢的情況) | | 5.索引 | Innodb也是用B+樹作為索引結構,數據表本身就是按照b+樹組織,葉節點key值為數據記錄的主鍵,data域為完整的數據記錄,輔助索引data域保存的是數據記錄的主鍵。 | MyISAM使用B+樹作為索引結構,葉節點保存的是存儲數據的地址,主鍵索引key值唯一,輔助索引key可以重復,二者在結構上相同。 | | 6.全文檢索 | 不支持全文檢索 | 支持全文檢索 | | 7.表的具體行數 | 不保存表的具體行數,也就是說,執行select count(*) from table 的時候,Innodb要掃描一遍整表來計算 | `select count(*) from table` ,MyISAM 只要簡單的讀出保存好的行數。因為 MyISAM 內置了一個計數器, count(*) 時它直接從計數器中讀。 | #### 2.3.6 覆蓋索引介紹 ##### 什么是覆蓋索引 如果一個索引包含(或者說覆蓋)所有需要查詢的字段的值,我們就稱之為“覆蓋索引”。我們知道InnoDB存儲引擎中,如果不是主鍵索引,葉子節點存儲的是主鍵+列值。最終還是要“回表”,也就是要通過主鍵再查找一次。這樣就會比較慢覆蓋索引就是把要查詢出的列和索引是對應的,不做回表操作! ##### 覆蓋索引使用實例 現在我創建了索引(username,age),我們執行下面的 sql 語句 ```sql select username , age from user where username = 'Java' and age = 22 ``` 在查詢數據的時候:要查詢出的列在葉子節點都存在!所以,就不用回表。 #### 2.3.7 為什么索引能提高查詢速度 > 地址: https://juejin.im/post/5b55b842f265da0f9e589e79 ##### 2.3.7.1 MySQL 的基本存儲結構 MySQL的基本存儲結構是頁(記錄都存在頁里邊): ![img](http://my-blog-to-use.oss-cn-beijing.aliyuncs.com/18-10-2/28559421.jpg) ![](http://my-blog-to-use.oss-cn-beijing.aliyuncs.com/18-10-2/82053134.jpg) - **各個數據頁可以組成一個雙向鏈表** - **每個數據頁中的記錄又可以組成一個單向鏈表** - 每個數據頁都會為存儲在它里邊兒的記錄生成一個頁目錄,在通過主鍵查找某條記錄的時候可以在頁目錄中使用二分法快速定位到對應的槽,然后再遍歷該槽對應分組中的記錄即可快速找到指定的記錄 - 以其他列(非主鍵)作為搜索條件:只能從最小記錄開始依次遍歷單鏈表中的每條記錄。 所以說,如果我們寫select * from user where indexname = 'xxx'這樣沒有進行任何優化的sql語句,默認會這樣做: 1. **定位到記錄所在的頁:需要遍歷雙向鏈表,找到所在的頁** 2. **從所在的頁內中查找相應的記錄:由于不是根據主鍵查詢,只能遍歷所在頁的單鏈表了** 很明顯,在數據量很大的情況下這樣查找會很慢!這樣的時間復雜度為O(n)。 ##### 2.3.7.2 使用索引之后 索引做了些什么可以讓我們查詢加快速度呢?其實就是將無序的數據變成有序(相對): ![](http://my-blog-to-use.oss-cn-beijing.aliyuncs.com/18-10-2/5373082.jpg) 要找到id為8的記錄簡要步驟: ![](http://my-blog-to-use.oss-cn-beijing.aliyuncs.com/18-10-2/89338047.jpg) 很明顯的是:沒有用索引我們是需要遍歷雙向鏈表來定位對應的頁,現在通過 **“目錄”** 就可以很快地定位到對應的頁上了!(二分查找,時間復雜度近似為O(logn)) 其實底層結構就是B+樹,B+樹作為樹的一種實現,能夠讓我們很快地查找出對應的記錄。 ### 2.4 索引設計原則? #### 2.4.1 列的離散(sàn)度 第一個叫做**列的離散度**,我們先來看一下列的離散度的公式:count(distinct(column_name)) : count(*),列的全部不同值和所有數據行的比例。數據行數相同的情況下,分子越大,列的離散度就越高。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105144317915.png" alt="image-20210105144317915" style="zoom: 67%;" /> 如果列的重復值越多,離散度就越低,重復值越少,離散度就越高。了解了離散度的概念之后,我們再來思考一個問題,我們在 name 上面建立索引和在 gender 上面建立索引有什么區別。當我們用在 gender 上建立的索引去檢索數據的時候,由于重復值太多,需要掃描的行數就更多。例如,我們現在在 gender 列上面創建一個索引,然后看一下執行計劃。 ```sql ALTER TABLE user_innodb DROP INDEX idx_user_gender; ALTER TABLE user_innodb ADD INDEX idx_user_gender (gender); -- 耗時比較久 EXPLAIN SELECT * FROM `user_innodb` WHERE gender = 0; ``` ![image-20210105144449282](https://i.loli.net/2021/01/05/U7pSrMs8gtqWlD5.png) 而 name 的離散度更高,比如“青山”的這名字,只需要掃描一行。 ```sql ALTER TABLE user_innodb DROP INDEX idx_user_name; ALTER TABLE user_innodb ADD INDEX idx_user_name (name); EXPLAIN SELECT * FROM `user_innodb` WHERE name = '青山'; ``` ![image-20210105144530599](https://i.loli.net/2021/01/05/CMq5frRAgiHFcXj.png) MySQL 的優化器發現走索引跟使用全表掃描差不了多少的時候,就算建了索引,也不一定會走索引。 #### 2.4.2 聯合索引最左匹配 前面我們說的都是針對**單列**創建的索引,但有的時候我們的**多條件查詢**的時候,也會建立**聯合索引**。單列索引可以看成是特殊的聯合索引。比如我們在 user 表上面,給 name 和 phone 建立了一個聯合索引。 ```sql ALTER TABLE user_innodb DROP INDEX comidx_name_phone; ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone); ``` <img src="https://i.loli.net/2021/01/05/rUTP13aBwJ6EVnz.png" alt="image-20210105144803466" style="zoom:67%;" /> **聯合索引**在 B+Tree 中是**復合的數據結構**,它是按照從左到右的順序來建立搜索樹的(name 在左邊,phone 在右邊)。name 是有序的,phone 是無序的。**當 name 相等的時候,phone 才是有序的。**這個時候我們使用 where name= '青山' and phone = '136xx '去查詢數據的時候,B+Tree 會優先比較 name 來確定下一步應該搜索的方向,往左還是往右。如果 name相同的時候再比較 phone。 #### 2.4.3 什么時候用到聯合索引 建立聯合索引的時候,一定要把**最常用**的列放在**最左邊**。 - 1)使用兩個字段,可以用到聯合索引: ```sql EXPLAIN SELECT * FROM user_innodb WHERE name= '權亮' AND phone = '15204661800'; ``` ![image-20210105145155344](https://i.loli.net/2021/01/05/q47S5BKMvu3OWnw.png) - 2)使用左邊的 name 字段,可以用到聯合索引: ```sql EXPLAIN SELECT * FROM user_innodb WHERE name= '權亮' ``` ![image-20210105145244605](https://i.loli.net/2021/01/05/WBTEv9ekVmPsnxC.png) - 3)使用右邊的 phone 字段,無法使用索引,全表掃描: ```sql EXPLAIN SELECT * FROM user_innodb WHERE phone = '15204661800' ``` ![image-20210105145317087](https://i.loli.net/2021/01/05/PxJVKrukZIt9Oqe.png) #### 2.4.4 如何創建聯合索引 有一天我們的 DBA 找到我,說我們的項目里面有兩個查詢很慢。 ```sql SELECT * FROM user_innodb WHERE name= ? AND phone = ?; SELECT * FROM user_innodb WHERE name= ?; --按照我們的想法,一個查詢創建一個索引,所以我們針對這兩條 SQL 創建了兩個索引,這種做法覺得正確嗎? CREATE INDEX idx_name on user_innodb(name); CREATE INDEX idx_name_phone on user_innodb(name,phone); ``` 當我們創建一個聯合索引的時候,按照**最左匹配原則**,用左邊的字段 name 去查詢的時候,也能用到索引,所以**第一個索引完全沒必要**。相當于建立了兩個聯合索引(name),(name,phone)。如果我們創建三個字段的索引 index(a,b,c),相當于創建三個索引:index(a)、index(a,b)、index(a,b,c)用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引的。不能不用第一個字段,不能中斷。 #### 2.4.5 覆蓋索引 **回表** **非主鍵索引**,我們先通過索引找到**主鍵索引的鍵值**,再通過主鍵值查出索引里面沒有的數據,它比基于主鍵索引的查詢多**掃描了一棵索引樹**,這個過程就叫回表。例如:select * from user_innodb where name = '青山'; ![image-20210105145944396](https://i.loli.net/2021/01/05/ioNYn5U9xhXEl2A.png) 在輔助索引里面,不管是單列索引還是聯合索引,**如果 select 的數據列只用從索引中就能夠取得**,不必從數據區中讀取,這時候使用的索引就叫做**覆蓋索引**,這樣就**避免了回表**。 ### 2.5 索引的創建與使用 因為索引對于改善查詢性能的作用是巨大的,所以我們的目標是盡量使用索引。 #### 2.5.1 索引的創建 - 在用于 **where 判斷**,**order 排序**和 **join 的(on)**字段上創建索引 - 索引的**個數不要過多**。(浪費空間,更新變慢) - **區分度低的字段**,例如性別,不要建索引(離散度太低,導致掃描行數過多) - **頻繁更新**的值,不要作為**主鍵**或者**索引**(**頁分裂**) - 組合索引把**散列性高**(區分度高)的值放在**前面**。 - 創建復合索引,而不是修改單列索引(index(a,b,c)不需要index(a),index(a,b)) ## 3、一條查詢SQL語句是如何執行的 <img src="https://i.loli.net/2021/01/04/bWMX1NwJ5Qks8eT.png" alt="image-20210104190448725" style="zoom:50%;" /> ### 3.1 通信協議 MySQL 必須要運行一個服務,監聽默認的 3306 端口。 在我們開發系統跟第三方對接的時候,必須要弄清楚的有兩件事。 - 第一個就是通信協議,比如我們是用 HTTP 還是 WebService 還是 TCP? - 第二個是消息格式,比如我們用 XML 格式,還是 JSON 格式,還是定長格式?報文 頭長度多少,包含什么內容,每個字段的詳細含義。 #### 3.1.1 通信協議 MySQL 是支持多種通信協議的,可以使用**同步/異步**的方式,支持**長連接/短連接**。 這里我們拆分來看。第一個是通信類型。 ##### 通信類型:同步或者異步 **同步通信的特點:** 1. 同步通信依賴于被調用方,受限于被調用方的性能。也就是說,應用操作數據庫, 線程會阻塞,等待數據庫的返回。 2. 一般只能做到一對一,很難做到一對多的通信 **異步跟同步相反:** 1. 異步可以避免應用阻塞等待,但是不能節省 SQL 執行的時間 2. 如果異步存在并發,每一個 SQL 的執行都要單獨建立一個連接,避免數據混亂 異步通信還帶來了編碼的復雜度,所以一般不建議使用。如果要異步,必須使用連接池,排隊從連接池獲取連接而不是創建新連接。一般來說我們連接數據庫都是同步連接。 ##### 連接方式:長連接或者短連接 MySQL 既支持短連接,也支持長連接。短連接就是操作完畢以后,馬上 close 掉。長連接可以保持打開,減少服務端創建和釋放連接的消耗,后面的程序訪問的時候還可以使用這個連接。一般我們會在連接池中使用長連接。 ```sql show global variables like 'wait_timeout'; -- 非交互式超時時間,如 JDBC 程序 show global variables like 'interactive_timeout'; -- 交互式超時時間,如數據庫工具 默認都是 28800 秒,8 小時。 ``` 查看 MySQL 當前有多少個連接? <img src="https://i.loli.net/2021/01/04/eKVbvUFqPachC9I.png" alt="image-20210104191312381" style="zoom:50%;" /> - Threads_cached:緩存中的線程連接數。 - Threads_connected:當前打開的連接數 - Threads_created:為處理連接創建的線程數。 - Threads_running:非睡眠狀態的連接數,通常指并發連 **MySQL 服務允許的最大連接數是多少呢?** ```sql show variables like 'max_connections'; 在 5.7 版本中默認是 151 個,最大可以設置成 16384(2^14) ``` ##### 通信協議 **第一種是 Unix Socket。** 比如我們在 Linux 服務器上,如果沒有指定-h 參數,它就用 socket 方式登錄(省略了-S /var/lib/mysql/mysql.sock) <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210104191721330.png" alt="image-20210104191721330" style="zoom:50%;" /> 它不用通過網絡協議,也可以連接到 MySQL 的服務器,它需要用到服務器上的一個物理文件(/var/lib/mysql/mysql.sock)。 **第二種方式,TCP/IP 協議** 如果指定-h 參數,就會用第二種方式,TCP/IP 協議。 ```sql mysql -h192.168.8.211 -uroot -p123456 ``` 另外還有命名管道(Named Pipes)和內存共享(Share Memory)的方式,這兩種通信方式只能在 Windows 上面使用,一般用得比較少。 #### 3.1.2 通信方式 <img src="https://i.loli.net/2021/01/04/blmYz8rL2XF4qIG.png" alt="image-20210104192008686" style="zoom:50%;" /> ##### 單工(遙控器) 在兩臺計算機通信的時候,數據的傳輸是單向的。生活中的類比:遙控器。 ##### 半雙工(對講機) 在兩臺計算機之間,數據傳輸是雙向的,你可以給我發送,我也可以給你發送,但是在這個通訊連接里面,同一時間只能有一臺服務器在發送數據,也就是你要給我發的話,也必須等我發給你完了之后才能給我發。生活中的類比:對講機。 ##### 全雙工(打電話) 數據的傳輸是雙向的,并且可以同時傳輸。生活中的類比:打電話。 **MySQL 使用了半雙工的通信方式?** 要么是客戶端向服務端發送數據,要么是服務端向客戶端發送數據,這兩個動作不能同時發生。所以客戶端發送 SQL 語句給服務端的時候,(在一次連接里面)數據是不能分成小塊發送的,不管你的 SQL 語句有多大,都是一次性發送 ### 3.2 查詢緩存 MySQL 內部自帶了一個緩存模塊。MySQL 的緩存默認是關閉的。默認關閉的意思就是不推薦使用,為什么 MySQL 不推薦使用它自帶的緩存呢? 主要是因為 MySQL 自帶的緩存的應用場景有限 - 第一個是它要求 SQL 語句必須一模一樣,中間多一個空格,字母大小寫不同都被認為是不同的的 SQL。 - 第二個是表里面任何一條數據發生變化的時候,這張表所有緩存都會失效,所以對于有大量數據更新的應用,也不適合 ### 3.3 語法解析和預處理 #### 3.3.1 詞法解析 詞法分析就是把一個完整的 SQL 語句打碎成一個個的單詞 #### 3.3.2 語法解析 語法分析會對 SQL 做一些語法檢查,比如單引號有沒有閉合,然后根據 MySQL 定義的語法規則,根據 SQL 語句生成一個數據結構。這個數據結構我們把它叫做解析樹(select_lex)。 <img src="https://i.loli.net/2021/01/04/DloxWu4zCN6yXmV.png" alt="image-20210104193052181" style="zoom: 50%;" /> #### 3.3.3 預處理器 解析 SQL 的環節里面有個預處理器。它會檢查生成的解析樹,解決解析器無法解析的語義。比如,它會檢查表和列名是否存在,檢查名字和別名,保證沒有歧義。預處理之后得到一個新的解析樹。 ### 3.4 查詢優化與查詢執行計劃 #### 3.4.1 優化器 數據庫最終執行的 SQL 是不是就是我們發送的 SQL?這個答案是否定的.查詢優化器的目的就是根據解析樹生成不同的執行計劃(Execution Plan),然后選擇一種最優的執行計劃,MySQL 里面使用的是基于開銷(cost)的優化器,那種執行計劃開銷最小,就用哪種。 **MySQL 的優化器能處理哪些優化類型呢?** 1、當我們對多張表進行關聯查詢的時候,以哪個表的數據作為基準表。 2、有多個索引可以使用的時候,選擇哪個索引。 ### 3.5 存儲引擎 #### 3.5.1 存儲引擎的基本介紹 在關系型數據庫里面,數據是放在什么結構里面的?(放在表 Table 里面的)我們可以把這個表理解成 Excel 電子表格的形式。所以我們的表在存儲數據的同時,還要組織數據的存儲結構,這個存儲結構就是由我們的存儲引擎決定的,所以我們也可以把存儲引擎叫做表類型。在 MySQL 里面,支持多種存儲引擎,他們是可以替換的,所以叫做**插件式的存儲引擎**。 ![image-20210104193815553](https://i.loli.net/2021/01/04/xaAwWXTfNUGK37F.png) 每一張表都可以指定它的存儲引擎,而不是一個數據庫只能使用一個存儲引擎。存儲引擎的使用是以表為單位的。而且,創建表之后還可以修改存儲引擎。默認情況下,每個數據庫有一個自己文件夾 任何一個存儲引擎都有一個 frm 文件,這個是表結構定義文件。 ![image-20210104193920694](https://i.loli.net/2021/01/04/pT734Q6rLZ9UxhW.png) #### 3.5.2 存儲引擎比較 MyISAM 和 InnoDB 是我們用得最多的兩個存儲引擎,在 MySQL 5.5 版本之前,默認的存儲引擎是 MyISAM,它是 MySQL 自帶的。MyISAM 的前身是 ISAM(Indexed Sequential Access Method:利用索引,順序存取數據的方法)。5.5 版本之后默認的存儲引擎改成了 InnoDB,InnoDB 支持事務,支持行級別的鎖 ##### (1)MyISAM(3 個文件) 應用范圍比較小。表級鎖定限制了讀/寫的性能,因此在 Web 和數據倉庫配置中,它通常用于只讀或以讀為主的工作。 **特點:** - 支持表級別的鎖(插入和更新會鎖表)。不支持事務。 - 擁有較高的插入(insert)和查詢(select)速度。 - 存儲了表的行數(count 速度更快)。(怎么快速向數據庫插入 100 萬條數據?我們有一種先用 MyISAM 插入數據,然后修改存儲引擎為 InnoDB 的操作。) 適合:只讀之類的數據分析的項目。 ##### (2)InnoDB(2 個文件) mysql 5.7 中的默認存儲引擎。InnoDB 是一個事務安全(與 ACID 兼容)的 MySQL存儲引擎,它具有提交、回滾和崩潰恢復功能來保護用戶數據。InnoDB 行級鎖(不升級為更粗粒度的鎖)和 Oracle 風格的一致非鎖讀提高了多用戶并發性和性能。InnoDB 將用戶數據存儲在聚集索引中,以減少基于主鍵的常見查詢的 I/O。為了保持數據完整性,InnoDB 還支持外鍵引用完整性約束。 **特點:** - 支持事務,支持外鍵,因此數據的完整性、一致性更高。 - 支持行級別的鎖和表級別的鎖。 - 支持讀寫并發,寫不阻塞讀(MVCC)。 - 特殊的索引存放方式,可以減少 IO,提升查詢效率。 適合:經常更新的表,存在并發讀寫或者有事務處理的業務系統。 ##### (3)Memory(1 個文件) 將所有數據存儲在 RAM 中,以便在需要快速查找非關鍵數據的環境中快速訪問。這個引擎以前被稱為堆引擎。其使用案例正在減少;InnoDB 及其緩沖池內存區域提供了一種通用、持久的方法來將大部分或所有數據保存在內存中,而 ndbcluster 為大型分布式數據集提供了快速的鍵值查找。 **特點:** 把數據放在內存里面,讀寫的速度很快,但是數據庫重啟或者崩潰,數據會全部消失。只適合做臨時表。將表中的數據存儲到內存中。 ##### (4)CSV(3 個文件) 帶有逗號分隔值的文本文件。csv 表允許以 csv 格式導入或轉儲數據,以便與讀寫相同格式的腳本和應用程序交換數據。因為 csv 表沒有索引,所以通常在正常操作期間將數據保存在 innodb 表中,并且只在導入或導出階段使用 csv 表。 **特點:** 不允許空行,不支持索引。格式通用,可以直接編輯,適合在不同數據庫之間導入導出。 ##### (5)Archive(2 個文件) 這些緊湊的未索引的表用于存儲和檢索大量很少引用的歷史、存檔或安全審計信息。 **特點:** 不支持索引,不支持 update delete。 ### 3.6 執行引擎 誰使用執行計劃去操作存儲引擎呢?這就是我們的執行引擎,它利用存儲引擎提供的相應的 API 來完成操作。 為什么我們修改了表的存儲引擎,操作方式不需要做任何改變?因為不同功能的存儲引擎實現的 API 是相同的。最后把數據返回給客戶端,即使沒有結果也要返回。 ## 4、MySQL 體系結構總結 ### 4.1 模塊詳解 ![image-20210104195733615](https://i.loli.net/2021/01/04/wUplKjW5ZQqux8T.png) 1、 **Connector**:用來支持各種語言和 SQL 的交互,比如 PHP,Python,Java 的JDBC; 2、 **Management Serveices & Utilities**:系統管理和控制工具,包括備份恢復、MySQL 復制、集群等等; 3、 **Connection Pool**:連接池,管理需要緩沖的資源,包括用戶密碼權限線程等等; 4、 **SQL Interface**:用來接收用戶的 SQL 命令,返回用戶需要的查詢結果 5、 **Parser**:用來解析 SQL 語句; 6、 **Optimizer**:查詢優化器; 7、 **Cache and Buffer**:查詢緩存,除了行記錄的緩存之外,還有表緩存,Key 緩存,權限緩存等等; 8、 **Pluggable Storage Engines**:插件式存儲引擎,它提供 API 給服務層使用,跟具體的文件打交道。 ### 4.2 架構分層 總體上,我們可以把 MySQL 分成三層,跟客戶端對接的連接層,真正執行操作的服務層,和跟硬件打交道的存儲引擎層(參考 MyBatis:接口、核心、基礎)。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210104195946368.png" alt="image-20210104195946368" style="zoom:50%;" /> #### 4.2.1 連接層 我們的客戶端要連接到 MySQL 服務器 3306 端口,必須要跟服務端建立連接,那么管理所有的連接,驗證客戶端的身份和權限,這些功能就在連接層完成。 #### 4.2.2 服務層 連接層會把 SQL 語句交給服務層,這里面又包含一系列的流程:比如查詢緩存的判斷、根據 SQL 調用相應的接口,對我們的 SQL 語句進行詞法和語法的解析(比如關鍵字怎么識別,別名怎么識別,語法有沒有錯誤等等)。 然后就是優化器,MySQL 底層會根據一定的規則對我們的 SQL 語句進行優化,最后再交給執行器去執行。 #### 4.2.3 存儲引擎 存儲引擎就是我們的數據真正存放的地方,在 MySQL 里面支持不同的存儲引擎。再往下就是內存或者磁盤。 ## 5、一條更新 SQL 是如何執行的 新流程和查詢流程有什么不同呢?基本流程也是一致的,也就是說,它也要經過解析器、優化器的處理,最后交給執行器。區別就在于拿到符合條件的數據之后的操作。 ### 5.1 緩沖池 Buffer Pool 首先,InnnoDB 的**數據都是放在磁盤上**的,InnoDB 操作數據有一個**最小的邏輯單位**,叫做**頁**(索引頁和數據頁)。我們對于數據的操作,不是每次都直接操作磁盤,因為**磁盤的速度太慢了**。InnoDB 使用了一種**緩沖池的技術**,也就是把磁盤**讀到的頁**放到一塊內存區域里面。這個內存區域就叫 **Buffer Pool**。 <img src="https://i.loli.net/2021/01/05/rcVsTkJImHuQwi5.png" alt="image-20210105095150977" style="zoom:67%;" /> 下一次**讀取**相同的頁,先判斷是不是在緩沖池里面,如果是,就直接讀取,不用再次訪問磁盤。 **修改數據**的時候,先修改緩沖池里面的頁。內存的數據頁和磁盤數據**不一致**的時候,我們把它叫做**臟頁**。InnoDB 里面有**專門的后臺線程**把 Buffer Pool 的數據寫入到磁盤,每隔一段時間就一次性地把多個修改寫入磁盤,這個動作就叫做**刷臟**。 ### 5.2 InnoDB 內存結構 <img src="https://i.loli.net/2021/01/05/bPHs8936Cr7dlqz.png" alt="image-20210105095635490" style="zoom:67%;" /> Buffer Pool 主要分為 3 個部分: Buffer Pool、Change Buffer、Adaptive Hash Index,另外還有一個(redo)log buffer。 #### 5.2.1 Buffer Pool Buffer Pool 緩存的是**頁面信息**,包括**數據頁**、**索引頁**。Buffer Pool 默認大小是 128M(134217728 字節),可以調整。***InnoDB 用 LRU算法來管理緩沖池(鏈表實現,不是傳統的 LRU,分成了 young 和 old),經過淘汰的*** ***數據就是熱點數據。***內存緩沖區對于提升讀寫性能有很大的作用。當需要更新一個數據頁時,如果數據頁在 Buffer Pool 中存在,那么就直接更新好了。否則的話就需要從磁盤加載到內存,再對內存的數據頁進行操作。也就是說,如果沒有命中緩沖池,至少要產生一次磁盤 IO,有沒有優化的方式呢? ```sql SHOW STATUS LIKE '%innodb_buffer_pool%'; --查看服務器狀態,里面有很多跟 Buffer Pool 相關的信息: ``` <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105095934305.png" alt="image-20210105095934305" style="zoom:67%;" /> #### 5.2.2 Change Buffer 寫緩沖 如果這個數據頁**不是唯一索引**,**不存在數據重復**的情況,也就**不需要從磁盤加載**索引頁判斷數據是不是重復(**唯一性檢查**)。這種情況下可以**先把修改記錄在內存的緩沖池中,從而提升更新語句(Insert、Delete、Update)執行速度。**[這一塊區域就是 Change Buffer。5.5 之前叫 Insert Buffer 插入緩沖,現在也能支持 delete 和 update。] 最后把 Change Buffer 記錄到數據頁的操作叫做 merge。什么時候發生 merge?有幾種情況: 1. 在訪問這個數據頁的時候 2. 或者通過后臺線程、或者數據庫 shut down、 3. redo log 寫滿時觸發。 如果數據庫大部分索引都是非唯一索引,并且業務是寫多讀少,不會在寫數據后立刻讀取,就可以使用 Change Buffer(寫緩沖)。寫多讀少的業務,調大這個值: ```sql SHOW VARIABLES LIKE 'innodb_change_buffer_max_size'; --代表 Change Buffer 占 Buffer Pool 的比例,默認 25%。 ``` #### 5.2.3 Adaptive Hash Index 索引應該是放在磁盤的,為什么要專門把一種哈希的索引放到內存? #### 5.2.4 (redo)Log Buffer 如果 Buffer Pool 里面的臟頁還沒有刷入磁盤時,數據庫宕機或者重啟,這些數據丟失。如果寫操作寫到一半,甚至可能會破壞數據文件導致數據庫不可用。為了避免這個問題,**InnoDB 把所有對頁面的修改操作**專門寫入一個**日志文件**,并且在數據庫啟動時從這個文件進行**恢復操作**(實現 **crash-safe**)——用它來實現**事務的持久性**。 <img src="https://i.loli.net/2021/01/05/CUFZa21oVHIerku.png" alt="image-20210105102441327" style="zoom:50%;" /> 這個文件就是磁盤的 **redo log**(叫做**重做日志**),對應于**/var/lib/mysql/目錄下的ib_logfile0 和 ib_logfile1,每個 48M**。這 種 日 志 和 磁 盤 配 合 的 整 個 過 程 , 其 實 就 是 MySQL 里 的 **WAL 技 術**(Write-Ahead Logging),它的關鍵點就是**先寫日志,再寫磁盤**。 同樣是寫磁盤,為什么不直接寫到 db file 里面去?為什么先寫日志再寫磁盤?我們先來了解一下**隨機 I/O** 和**順序 I/O** 的概念。 - 磁盤的最小組成單元是**扇區**,通常是 512 個字節。 - 操作系統和內存打交道,最小的單位是**頁 Page**。 - 操作系統和磁盤打交道,讀寫磁盤,最小的單位是**塊 Bloc**k。 <img src="https://i.loli.net/2021/01/05/26pTWlG87iZ5tbo.png" alt="image-20210105102725157" style="zoom:67%;" /> > 如果我們所需要的數據是隨機分散在不同頁的不同扇區中,那么找到相應的數據需要等到磁臂旋轉到指定的頁,然后盤片尋找到對應的扇區,才能找到我們所需要的一塊數據,一次進行此過程直到找完所有數據,這個就是**隨機 IO**,讀取數據速度較慢。 > > 假設我們已經找到了第一塊數據,并且其他所需的數據就在這一塊數據后邊,那么就不需要重新尋址,可以依次拿到我們所需的數據,這個就叫**順序 IO** **刷盤**是**隨機 I/O**,而**記錄日志**是**順序 I/O**,順序 I/O 效率更高。因此先把修改寫入日志,可以延遲刷盤時機,進而提升系統吞吐。當然 redo log 也不是每一次都直接寫入磁盤,在 Buffer Pool 里面有一塊內存區域(Log Buffer)專門用來保存即將要寫入日志文件的數據,默認 16M,它一樣可以節省磁盤 IO。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105103436854.png" alt="image-20210105103436854" style="zoom:50%;" /> 需要注意:**redo log 的內容**主要是用于**崩潰恢復**。磁盤的數據文件,數據來自 bufferpool。redo log 寫入磁盤,不是寫入數據文件。在我們寫入數據到磁盤的時候,操作系統本身是有緩存的。flush 就是把操作系統緩log buffer 寫入磁盤的時機,由一個參數控制,默認是 1 ```sql SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; ``` <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105103647785.png" alt="image-20210105103647785" style="zoom:67%;" /> <img src="https://i.loli.net/2021/01/05/p4CRN1FyjALaMeX.png" alt="image-20210105103726011" style="zoom:67%;" /> 這是內存結構的第 4 塊內容,redo log,它又分成**內存**和**磁盤**兩部分。redo log 有什么特點? 1. redo log 是 **InnoDB 存儲引擎實現的**,并不是所有存儲引擎都有。 2. 不是記錄數據頁更新之后的狀態,而是記錄這個頁**做了什么改動**,屬于**物理日志**。 3. redo log 的**大小是固定**的,**前面的內容會被覆蓋**。 <img src="https://i.loli.net/2021/01/05/YQDboOqmvASt2UJ.png" alt="image-20210105103945181" style="zoom:50%;" /> check point 是當前要覆蓋的位置。如果 write pos 跟 check point 重疊,說明 redolog 已經寫滿,這時候需要同步 redo log 到磁盤中。 ### 5.3 InnoDB 磁盤結構 表空間可以看做是 InnoDB 存儲引擎邏輯結構的最高層,所有的數據都存放在表空間中。InnoDB 的表空間分為 5 大類。 #### 5.3.1 系統表空間 system tablespace 在默認情況下 InnoDB 存儲引擎有一個**共享表空間**(對應文件/var/lib/mysql/ibdata1),也叫**系統表空間**。InnoDB 系統表空間包含 InnoDB **數據字典**和**雙寫緩沖區**,(Change Buffer 和 Undo Logs),如果沒有指定 file-per-table,也包含**用戶創建的表和索引數據**。 1. undo 在后面介紹,因為有獨立的表空間 2. 數據字典:由內部系統表組成,存儲表和索引的元數據 3. 雙寫緩沖(InnoDB 的一大特性) `InnoDB 的頁`和`操作系統的頁`**大小不一致**,InnoDB 頁大小一般為 **16K**,操作系統頁大小為 **4K**,**InnoDB 的頁寫入到磁盤時,一個頁需要分 4 次寫**。 <img src="C:\Users\chenm\AppData\Roaming\Typora\typora-user-images\image-20210105104925870.png" alt="image-20210105104925870" style="zoom:67%;" /> 如果存儲引擎正在寫入頁的數據到磁盤時發生了宕機,可能出現頁只寫了一部分的情況,比如只寫了 4K,就宕機了,這種情況叫做**部分寫失效(partial page write)**,可能會導致數據丟失。我們不是有 redo log 嗎?但是有個問題,**如果這個頁本身已經損壞了**,用它來做崩潰恢復是沒有意義的。所以在對于應用 redo log 之前,需要一個**頁的副本**。如果出現了寫入失效,就用頁的副本來還原這個頁,然后再應用 redo log。這個頁的副本就是 **double write,InnoDB 的雙寫技術**。通過它實現了數據頁的可靠性。 跟 redo log 一樣,double write 由兩部分組成,一部分是內存的 double write,一個部分是磁盤上的 double write。因為 double write 是順序寫入的,不會帶來很大的開銷。在默認情況下,所有的表共享一個系統表空間,這個文件會越來越大,而且它的空間不會收縮。 #### 5.3.2 獨占表空間 file-per-table tablespaces 我們可以讓**每張表獨占一個表空間**。這個開關通過 innodb_file_per_table 設置,默認開啟。 開啟后,則每張表會開辟一個表空間,這個文件就是數據目錄下的 ibd 文件(如/var/lib/mysql/gupao/user_innodb.ibd),存放表的索引和數據。但是其他類的數據,如回滾(undo)信息,插入緩沖索引頁、系統事務信息,二次寫緩沖(Double write buffer)等還是存放在原來的共享表空間內。 #### 5.3.3 通用表空間 general tablespaces 通用表空間也是一種**共享的表空間**,跟 ibdata1 類似。可以創建一個通用的表空間,用來存儲不同數據庫的表,數據路徑和文件可以自定義。語法: ```sql create tablespace ts2673 add datafile '/var/lib/mysql/ts2673.ibd' file_block_size=16K engine=innodb; ``` 在創建表的時候可以指定表空間,用 ALTER 修改表空間可以轉移表空間。 ```sql create table t2673(id integer) tablespace ts2673; ``` 不同表空間的數據是可以移動的。 刪除表空間需要先刪除里面的所有表: ```sql drop table t2673; drop tablespace ts2673; ``` #### 5.3.4 臨時表空間 temporary tablespaces 存儲臨時表的數據,包括**用戶創建的臨時表**,和**磁盤的內部臨時表**。對應數據目錄下的 ibtmp1 文件。當數據服務器正常關閉時,該表空間被刪除,下次重新產生。 #### 5.3.5 undo log tablespace undo log(**撤銷日志或回滾日志**)記錄了**事務發生之前**的**數據狀態**(不包括 select)。如果修改數據時出現異常,可以用 undo log 來實現**回滾操作**(**保持原子性**)。執行 undo 的時候,僅僅是將數據從**邏輯上**恢復至事務之前的狀態,而不是從物理頁面上操作實現的,屬于**邏輯格式**的日志。 **redo Log 和 undo Log 與事務密切相關,統稱為事務日志。** undo Log 的數據默認在系統表空間 ibdata1 文件中,因為共享表空間不會自動收縮,也可以單獨創建一個 undo 表空間。`show global variables like '%undo%';` #### 5.3.6 更新總結 總結一下一個更新操作的流程,這是一個簡化的過程。name 原值是 qingshan。 ```sql update user set name = 'penyuyan' where id=1; ``` 1、事務開始,從內存或磁盤取到這條數據,返回給 Server 的執行器; 2、執行器修改這一行數據的值為 penyuyan; 3、記錄 name=qingshan 到 undo log; (用于回滾) 4、記錄 name=penyuyan 到 redo log;(用于恢復) 5、調用存儲引擎接口,在內存(Buffer Pool)中修改 name=penyuyan; 6、事務提交。 內存和磁盤之間,工作著很多后臺線程。 ### 5.4 后臺線程 后臺線程的主要作用是負責刷新內存池中的數據和把修改的數據頁刷新到磁盤。后臺線程分為:master thread,IO thread,purge thread,page cleaner thread。 - **master thread** 負責刷新緩存數據到磁盤并協調調度其它后臺進程。 - **IO thread** 分為 insert buffer、log、read、write 進程。分別用來處理 insert buffer、重做日志、讀寫請求的 IO 回調。 - **purge thread** 用來回收 undo 頁。 - **page cleaner thread** 用來刷新臟頁。 ### 5.5 Bin Log 除了 InnoDB 架構中的日志文件,MySQL 的 Server 層也有一個日志文件,叫做binlog,它可以被所有的存儲引擎使用。binlog 以**事件的形式**記錄了所有的 **DDL** 和 **DML** 語句(因為它記錄的是操作而不是數據值,屬于**邏輯日志**),可以用來做**主從復制**和**數據恢復**。 跟 redo log 不一樣,它的**文件內容是可以追加的,沒有固定大小限制**。在開啟了 binlog 功能的情況下,我們可以把 binlog 導出成 SQL 語句,把**所有的操作重放一遍**,來實現**數據的恢復**。binlog 的另一個功能就是用來實**現主從復制**,它的原理就是從服務器讀取主服務器的 binlog,然后執行一遍。 有了這兩個日志之后,我們來看一下一條更新語句是怎么執行的: ![image-20210105110947569](https://i.loli.net/2021/01/05/eY75wErXuxVQnks.png) > 例如一條語句:update teacher set name='盆魚宴' where id=1; > 1、先查詢到這條數據,如果有緩存,也會用到緩存。 > 2、把 name 改成盆魚宴,然后調用引擎的 API 接口,寫入這一行數據到內存,同時記錄 redo log。這時 redo log 進入 prepare 狀態,然后告訴執行器,執行完成了,可以隨時提交。 > 3、執行器收到通知后記錄 binlog,然后調用存儲引擎接口,設置 redo log 為 commit狀態。 > 4、更新完成。 這張圖片的重點(沒必要背下來): - 先記錄到內存,再寫日志文件。 - 記錄 redo log 分為兩個階段。 - 存儲引擎和 Server 記錄不同的日志 - 先記錄 redo,再記錄 binlog。
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看