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