乾貨滿滿 | 微服務化的資料庫設計與讀寫分離

資料庫永遠是應用最關鍵的一環，同時越到高併發階段，資料庫往往成為瓶頸，如果資料庫表和索引不在一開始就進行良好的設計，則後期資料庫橫向擴充套件，分庫分表都會遇到困難。

對於網際網路公司來講，一般都會使用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. 另外有一個連結串列，將所有的資料節點串起來，可以順序訪問。

這個定義的比較抽象，我們來看一個具體的例子。

從圖中我們可以看出，這是一個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欄位加主鍵值。因而透過二級索引訪問資料，要訪問兩次索引。

還有一種索引的形式稱為組合索引，或者複合索引，可以在多個列上建立索引。

這種索引的排序規則為，先比較第一列，在第一列相等的情況下，比較第二列，以此類推。

四、資料庫索引的優缺點

資料庫索引的優勢最明顯的就是減少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檢視哪些索引從沒有被使用。

七、讀寫分離的原理

資料庫往往寫少讀多，所以效能最佳化的第一步就是讀寫分離。

主從複製基於主節點上的服務層的日誌實現的，而從節點上有一個IO執行緒讀取這個日誌，然後寫入本地。另有一個執行緒從本地日誌讀取後在從節點重新執行。

如圖是主從非同步複製的流程圖。在主例項寫入引擎後就返回成功，然後將事件發給從例項，在從例項上執行。這種同步方式速度較快，但是在主掛了的時候，如果還沒有複製，則可能存在資料丟失問題。

資料庫同步複製也不同，是當從節點落盤後再返回客戶端，當然這樣會使得效能有所降低，網易資料庫團隊是透過組提交，並行複製等技術將效能提上來。

有了主從複製，在資料庫DAO層可以設定讀寫分離策略，也有透過資料庫中介軟體做這個事情的。

其實資料庫日誌還有很多其他用處，如使用canal(阿里巴巴開源專案: 基於mysql資料庫binlog的增量訂閱&消費)訂閱資料庫的binlog，可以用於更新快取等。

作者|劉超

轉自 | 劉超的通俗雲端計算