MySQL數據庫總體架構講義

本文主要給大家介紹MySQL數據庫總體架構講義，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續在億速云行業資訊里面關注我的更新文章的。

一、數據庫的總體架構
我們首先來看MySQL數據的總體架構如下：

這是一張非常經典的MySQL的系統架構圖，通過這個圖可以看出MySQL各個部分的功能。
當客戶端連接數據庫的時候，首先面對的是連接池，用于管理用戶的連接，并會做一定的認證和鑒權。
連接了數據庫之后，客戶端會發送SQL語句，而SQL接口這個模塊就是來接受用戶的SQL語句的。
SQL語句往往需要符合嚴格的語法規則，因而要有語法解析器對語句進行語法解析，解析語法的原理如同編譯原理中的學到的那樣，從語句變成語法樹。
對于用戶屬于的查詢可以進行優化，從而可以選擇最快的查詢路徑，這就是優化器的作用。
為了加快查詢速度，會有查詢緩存模塊，如果查詢緩存有命中的查詢結果，查詢語句就可以直接去查詢緩存中取數據。
上面的所有的組件都是數據庫服務層，接下來是數據庫引擎層，當前主流的數據庫引擎就是InnoDB。
對于數據庫有任何的修改，數據庫服務層會有binary log記錄下來，這是主備復制的基礎。
對于數據庫引擎層，一個著名的圖如下：

在存儲引擎層，也有緩存，也有日志，最終數據是落到盤上的。
存儲引擎層的緩存也是用于提高性能的，但是同數據庫服務層的緩存不同，數據庫服務層的緩存是查詢緩存，而數據庫引擎層的緩存讀寫都緩存。數據庫服務層的緩存是基于查詢邏輯的，而數據庫引擎引擎的緩存是基于數據頁的，可以說是物理的。
哪怕是數據的寫入僅僅寫入到了數據庫引擎層中的緩存，對于數據庫服務層來講，就算是已經持久化了，當然這個時候會造成緩存頁和硬盤上的頁的數據的不一致，這種不一致由數據庫引擎層的日志來保證完整性。
所以數據庫引擎層的日志和數據庫服務層的也不同，服務層的日志記錄的是一個個的修改邏輯，而引擎層的日志記錄的是緩存頁和數據頁的物理差異。
二、數據庫的工作流程
在收到一個查詢的時候，MySQL的架構中的各個組件是如此工作的：

客戶端同數據庫服務層建立TCP連接，連接管理模塊會建立連接，并請求一個連接線程。如果連接池中有空閑的連接線程，則分配給這個連接，如果沒有，在沒有超過最大連接數的情況下，創建新的連接線程負責這個客戶端。
在真正的操作之前，還需要調用用戶模塊進行授權檢查，來驗證用戶是否有權限。通過后，方才提供服務，連接線程開始接收并處理來自客戶端的SQL語句。
連接線程接收到SQL語句之后，將語句交給SQL語句解析模塊進行語法分析和語義分析。
如果是一個查詢語句，則可以先看查詢緩存中是否有結果，如果有結果可以直接返回給客戶端。
如果查詢緩存中沒有結果，就需要真的查詢數據庫引擎層了，于是發給SQL優化器，進行查詢的優化。如果是表變更，則分別交給insert、update、delete、create、alter處理模塊進行處理。
接下來就是請求數據庫引擎層，打開表，如果需要的話獲取相應的鎖。
接下來的處理過程就到了數據庫引擎層，例如InnoDB。
在數據庫引擎層，要先查詢緩存頁中有沒有相應的數據，如果有則可以直接返回，如果沒有就要從磁盤上去讀取。
當在磁盤中找到相應的數據之后，則會加載到緩存中來，從而使得后面的查詢更加高效，由于內存有限，多采用變通的LRU表來管理緩存頁，保證緩存的都是經常訪問的數據。
獲取數據后返回給客戶端，關閉連接，釋放連接線程，過程結束。
三、數據庫索引的原理
在整個過程中，最容易稱為瓶頸點的是數據的讀寫，往往意味著要順序或者隨機讀寫磁盤，而讀寫磁盤的速度往往是比較慢的。
如果加快這個過程呢？相信大家都猜到了就是建立索引。
為什么索引能夠加快這個過程呢？
相信大家都逛過美食城，里面眾多家餐館琳瑯滿目，如果你不著急呢，肚子不餓，對搜索的性能沒有要求，就可以在商場里面慢慢逛，逛一家看一家，知道找到自己想吃的餐館。但是當你餓了，或者你們約好了餐館，你一定想直奔那個餐館，這個時候，你往往會去看樓層的索引圖，快速的查找你目標餐館的位置，找到后，直奔主題，就會大大節約時間，這就是索引的作用。
所以索引就是通過值，快速的找到它的位置，從而可以快速的訪問。
索引的另外一個作用就是不用真正的查看數據，就能夠做一些判斷，例如商場里面有沒有某個餐館，你看一下索引就知道了，不必真的到商場里面逛一圈，再如找出所有的川菜館，也是只要看索引就可以了，不用一家一家川菜館跑。
那么在MySQL中，索引是如何工作的呢？
MySQL的索引結構，往往是一棵B+樹。
一棵M階B+樹具有如下的性質：
1.節點分索引節點和數據節點。索引節點相當于B樹的內部節點，所有的索引節點組成一棵B樹，具有B樹的所有的特性。在索引節點中，存放著Key和指針，并不存放具體的元素。數據節點相當與B樹的外部節點，B樹的外部節點為空，在B+樹中被利用了起來，用于存放真正的數據元素，里面包含了Key和元素的其他信息，但是沒有指針。
2.整棵索引節點組成的B樹僅僅用來查找具有某個Key的數據元素位于哪個外部節點。在索引節點中找到了Key，事情沒有結束，要繼續找到數據節點，然后將數據節點中的元素讀出來，或者二分查找，或者順序掃描來尋找真正的數據元素。
3.M這個階數僅僅用來控制索引節點部分的度，至于每個數據節點包含多少元素，與M無關。
4.另外有一個鏈表，將所有的數據節點串起來，可以順序訪問。
這個定義的比較抽象，我們來看一個具體的例子。
1.jpg
從圖中我們可以看出，這是一個3階B+樹，而一個外部數據節點最多包含5項。如果插入的數據在數據節點，如果不引起分裂和合并，則索引節點組成的B樹就不會變。
如果在71到75的外部節點插入一項76，則引起分裂，71、72、73成為一個數據節點，74、75、76成為一個數據節點，而對于索引節點來講相當于插入一個Key為74的過程。
如果在41到43的外部節點中刪除43，則引起合并，41、42、61、62、63合并成一個節點，對于索引節點來講，相當于刪除Key為60的過程。
查找的時候，由于B+樹層高很小，所以能夠比較快速的定位，例如我們要查找值62，在根節點發現大于40則訪問右面，小于70則訪問左面，大于60則訪問右面，在葉子節點的第二個，就找到了62，成功定位。
在MySQL的InnoDB中，有兩種類型的B+樹索引，一種稱為聚簇索引，一種稱為二級索引。
聚簇索引的葉子節點就是數據節點，往往是主鍵作為聚簇索引，二級索引的葉子節點存放的是KEY字段加主鍵值。因而通過二級索引訪問數據，要訪問兩次索引。
1.jpg
還有一種索引的形式稱為組合索引，或者復合索引，可以在多個列上建立索引。
1.jpg
這種索引的排序規則為，先比較第一列，在第一列相等的情況下，比較第二列，以此類推。
四、數據庫索引的優缺點
數據庫索引的優勢最明顯的就是減少I/O，下面分析幾種場景。
對于=條件的字段，可以直接通過查找B+樹的方式，通過很少的硬盤讀取次數（相當于B+樹層高），就能夠到達葉子節點，然后直接定位到數據的位置。
對于范圍的字段，由于B+樹里面都是排好序的，范圍可以很快的通過樹進行定位。
同理對于orderby、group by、distinct/max、min，由于B+樹是排好序的，也是能夠很快的得到結果的。
還有一個常見的場景稱為索引覆蓋數據。例如A, B兩個字段作為條件字段，常出現A=a AND B=b，同時select C、D時候，往往會建聯合索引（A、B），是一個二級索引，所以搜索的時候，通過二級索引的B+樹能夠很快的找到相應的葉子節點和記錄，但是記錄中有的是聚簇索引的ID，所以還需要查找一次聚簇索引的B+樹，找到真正的表中的記錄，然后在記錄中，將C、D讀取出來。如果建立聯合索引的時候為（A、B、C、D），則在二級索引的B+樹中就有了所有的數據，可以直接返回了，減少了一次搜索樹的過程。
當然索引肯定是有代價的，天下沒有免費的午餐。
索引帶來的好處多是讀的效率的提高，而索引帶來的代價就是寫的效率的降低。
插入和修改數據，都有可能意味著索引的改變。
插入的時候，往往會在主鍵上建設聚簇索引，因而主鍵最好使用自增長，這樣插入的數據就總是在最后，而且是順序的，效率比較高。主鍵不要使用UUID，這樣順序比較隨機，會帶來隨機的寫入，效率比較差。主鍵不要使用和業務有關，因為與業務相關意味著會被更新，將面臨著一次刪除和重新插入，效率會比較差。
通過上面對于B+樹的原理的介紹，我們可以看出B+樹的分裂代價還是比較大的，而分裂往往就產生于插入的過程中。
而對于數據的修改，則基本相當于刪除再插入，代價也比較大。
對于一些字符串的列的二級索引，往往會造成隨機的寫入和讀取，對I/O的壓力也比較大。
五、解讀數據庫軍規背后的原理
了解了這兩種索引的原理，我們就能夠解釋為什么很多所謂的數據庫的軍規長這個樣子了。下面我們來一一解釋。
什么情況下應該使用組合索引而非單獨索引呢？
假設有條件語句A=a AND B=b，如果A和B是兩個單獨的索引，在AND條件下只有一個索引起作用，對于B則要逐個判斷，而如果使用組合索引（A、B），只要遍歷一棵樹就可以了，大大增加了效率。但是對于A=a OR B=b，由于是或的關系，因而組合索引是不起作用的，因而可以使用單獨索引，這個時候，兩個索引可以同時起作用。
為什么索引要有區分度，組合索引中應該講有區分度的放在前面？
如果沒有區分度，例如用性別，相當于把整個大表分成兩部分，查找數據還是需要遍歷半個表才能找到，使得索引失去了意義。
如果有組合索引，還需要單列索引嗎？
如果組合索引是（A、B），則對于條件A=a，是可以用上這個組合索引的，因為組合索引是先按照第一列進行排序的，所以沒必要對于A單獨建立一個索引，但是對于B=b就用不上了，因為只有在第一列相同的情況下，才比較第二列，因而第二列相同的，可以分布在不同的節點上，沒辦法快速定位。
索引是越多越好嗎？
當然不是，只有在必要的地方添加索引，索引不但會使得插入和修改的效率降低，而且在查詢的時候，有一個查詢優化器，太多的索引會讓優化器困惑，可能沒有辦法找到正確的查詢路徑，從而選擇了慢的索引。
為什么要使用自增主鍵？
因為字符串主鍵和隨機主鍵會使得數據隨機插入，效率比較差，主鍵應該少更新，避免B+樹和頻繁合并和分裂。
為什么盡量不使用NULL？
NULL在B+樹里面比較難以處理，往往需要特殊的邏輯進行處理，反而降低了效率。
為什么不要在更新頻繁的字段上建立索引？
更新一個字段意味著相應的索引也要更新，更新往往意味著刪除然后再插入，索引本來是一種事先在寫的階段形成一定的數據結構，從而使得在讀的階段效率較高的方式，但是如果一個字段是寫多讀少，則不建議使用索引。
為什么在查詢條件里面不要使用函數？
例如ID+1=10這種條件，索引是事先寫入的時候生成好的，ID+1這種操作在查詢階段，索引無能為例，沒辦法把所有的索引都先做一個計算，然后再比較吧，代價太大了，因而應該使用ID=10-1。
為什么不要使用NOT等負向查詢條件？
你可以想象一下，對于一棵B+樹，跟節點是40，如果你的條件是等于20，就去左面查，你的條件等于50，就去右面查，但是你的條件是不等于66，索引應該咋辦？還不是遍歷一遍才知道。
為什么模糊查詢不要以通配符開頭？
對于一棵B+樹來講，如果根是字符def，如果通配符在后面，例如abc%，則應該搜索左面，例如efg%，則應該搜索右面，如果通配符在前面%abc，則不知道應該走哪一面，還是都掃描一遍吧。
為什么OR要改成IN，或者使用Union？
OR查詢條件的優化往往比較難找到最佳的路徑，尤其是OR的條件比較多的時候，尤其如此，對于同一個字段，使用IN就好一些，數據庫會對IN里面的條件進行排序，并統一通過二分搜索的方法處理。對于不同的字段，使用Union，則可以讓每一個子查詢都使用索引。
為什么數據類型應該盡量小，常用整型來代替字符型，長字符類型可以考慮使用前綴索引？
因為數據庫是按照頁存放的，每一頁的大小是一樣的，如果數據類型比較大，則頁數會比較多，每一頁放的數據會比較少，樹的高度會比較高，因而搜索數據要讀取的I/O數目會比較多，插入的時候節點也容易分裂，效率會降低。使用整型來代替字符型多是這個考慮，整型對于索引有更高的效率，例如IP地址等。如果有長字符類型需要使用索引進行查詢，為了不要使得索引太大，可以考慮將字段的前綴進行索引，而非整個字段。
六、查詢優化的方法論
要找到需要優化的SQL語句，首先要收集有問題的SQL語句。
MySQL 數據庫提供了慢SQL日志功能，通過參數slow_query_log，獲取執行時間超過一定閾值的SQL語錄列表。
沒有使用索引的SQL語句，可以通過long_queries_not_using_indexes參數開啟。
min_examined_row_limit，掃描記錄數大于該值的SQL語句才會被記入慢SQL日志。
找到有問題的語句，接下來就是通過explainSQL，獲取SQL的執行計劃，是否通過索引掃描記錄，可以通過創建索引來優化執行效率。是否掃描記錄數過多。是否持鎖時間過長，是否存在鎖沖突。返回的記錄數是否較多。
接下來可以定制化的優化。沒有被索引覆蓋的過濾條件涉及的字段，在區分度較大的字段上創建索引，如果涉及多個字段，盡量創建聯合索引。
掃描記錄數非常多，返回記錄數不多，區分度較差，重新評估SQL語句涉及的字段，選擇區分度高的多個字段創建索引。
掃描記錄數非常多，返回記錄數也非常多，過濾條件不強，增加SQL過濾條件
schema_redundant_indexes查看有哪些冗余索引。
如果多個索引涉及字段順序一致，則可以組成一個聯合索引schema_unused_indexes查看哪些索引從沒有被使用。
七、讀寫分離的原理
數據庫往往寫少讀多，所以性能優化的第一步就是讀寫分離。
1.jpg
主從復制基于主節點上的服務層的日志實現的，而從節點上有一個IO線程讀取這個日志，然后寫入本地。另有一個線程從本地日志讀取后在從節點重新執行。
2.jpg
如圖是主從異步復制的流程圖。在主實例寫入引擎后就返回成功，然后將事件發給從實例，在從實例上執行。這種同步方式速度較快，但是在主掛了的時候，如果還沒有復制，則可能存在數據丟失問題。
3.jpg
數據庫同步復制也不同，是當從節點落盤后再返回客戶端，當然這樣會使得性能有所降低，網易數據庫團隊是通過組提交，并行復制等技術將性能提上來。
有了主從復制，在數據庫DAO層可以設置讀寫分離策略，也有通過數據庫中間件做這個事情的。
其實數據庫日志還有很多其他用處，如使用Canal（阿里巴巴開源項目：基于MySQL數據庫Binlog的增量訂閱&消費）訂閱數據庫的Binlog，可以用于更新緩存等。

看了以上關于MySQL數據庫總體架構講義，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。