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