一條SQL的奇妙旅行
10
2020-09
今天距2021年112天
這是ITester軟件測(cè)試小棧第157次推文
點(diǎn)擊上方藍(lán)字“ITester軟件測(cè)試小棧“關(guān)注我，每周一、三、五早上 08:30準(zhǔn)時(shí)推送，每月不定期贈(zèng)送技術(shù)書籍。
微信公眾號(hào)后臺(tái)回復(fù)“資源”、“測(cè)試工具包”領(lǐng)取測(cè)試資源，回復(fù)“微信群”一起進(jìn)群打怪。
本文2006字，閱讀約需6分鐘
工作中我們經(jīng)常查詢數(shù)據(jù)庫,用一個(gè)查詢,得到想要的數(shù)據(jù)。可有想過,我們得到答案經(jīng)過了哪些磨難？經(jīng)歷了哪些誘惑？
以下將以一條SQL的執(zhí)行過程來了解 MySQL 整體架構(gòu)，對(duì)MySQL有一個(gè)全面，清晰的認(rèn)知，F(xiàn)or造航母。
開始旅行
客戶端發(fā)送一條查詢給服務(wù)器,包含客戶端相關(guān)信息(IP、用戶、密碼)，服務(wù)器完成驗(yàn)證。
報(bào)告太君，自己人，可以放行! 
執(zhí)行查詢語句的時(shí)候,會(huì)先查詢緩存,我們會(huì)發(fā)現(xiàn)某個(gè)查詢，查詢第二次的時(shí)候非常快便是這個(gè)原因(MySQL8.0 廢除這個(gè)功能,太雞肋)。
......
第一步：解析你的語法，主要是關(guān)鍵字；
單詞別寫錯(cuò)了，寫錯(cuò)了，我可不會(huì)干活。
第二步：解析涉及到的對(duì)象是否存在；
人都沒有，跟空氣聊個(gè)啥呢？
第三步：涉及到的對(duì)象用戶是否有對(duì)應(yīng)的權(quán)限。
哎呀，不給錢就不給看，不給看。
當(dāng)語法與語義都沒有問題權(quán)限也匹配，此時(shí)數(shù)據(jù)庫便開始真正為你服務(wù)了，根據(jù)一定得算法規(guī)則，對(duì)你的查詢進(jìn)行優(yōu)化，尋找最優(yōu)的執(zhí)行計(jì)劃。
國家分配的跟自己找的肯定還是不一樣的，多數(shù)情況下，還是自己找的好。
先判斷數(shù)據(jù)是否在緩沖池中，若在，直接返回，若不在，則先從磁盤文件中加載到內(nèi)存。
嘻嘻，反正今天要定了。
數(shù)據(jù)返回是一邊查詢，一邊返回，并不是一次返回，雖然看上去是一下突然返回的。
BTW，你看見的不一定是真的。
旅行圖如下：
MySQL體系結(jié)構(gòu)
最外層客戶端：各種語言API連接數(shù)據(jù)庫。
包含連接池，身份驗(yàn)證，查詢緩存。
解析器，優(yōu)化器，跨存儲(chǔ)引擎的函數(shù)，存儲(chǔ)過程，觸發(fā)器，視圖，SQL接口，管理服務(wù)工具組件。
不同存儲(chǔ)引擎即數(shù)據(jù)的存取方式不同。
文件系統(tǒng)，底層存儲(chǔ)數(shù)據(jù)的磁盤。
MySQL體系架構(gòu)圖如下：
InnoDB存儲(chǔ)引擎三大特性
B+樹的高度一般為3~4層,故需要3~4次的查詢，如果觀察到建立哈希索引可以帶來速度提升,則建立哈希索引,稱之為自適應(yīng)哈希索引(Adaptive Hash Index,AHI) AHI是通過緩沖池的B+樹頁構(gòu)造而來,因此建立的速度很快,而且不需要對(duì)整張表構(gòu)建哈希索引。InnoDB存儲(chǔ)引擎會(huì)自動(dòng)根據(jù)訪問的頻率和模式來自動(dòng)地為某些熱點(diǎn)頁建立哈希索引 --來自INNODB 技術(shù)內(nèi)幕（人工智能趕腳有沒有）。
缺點(diǎn): 跟普通索引一樣需要額外開銷維護(hù)。
對(duì)于非聚集類索引的插入和更新操作，不是每一次都直接插入到索引頁中，而是先插入到內(nèi)存中。具體做法是：如果該索引頁在緩沖池中，直接插入；否則，先將其放入插入緩沖區(qū)中，再以一定的頻率和索引頁合并，這時(shí)，就可以將同一個(gè)索引頁中的多個(gè)插入合并到一個(gè)IO操作中，大大提高寫性能（一定是非聚集索引）。
缺點(diǎn)：可能導(dǎo)致數(shù)據(jù)庫宕機(jī)后實(shí)例恢復(fù)時(shí)間變長，占用太多緩沖池內(nèi)存。
當(dāng)MySQL將臟數(shù)據(jù)flush到data file的時(shí)候, 先使用memcopy 將臟數(shù)據(jù)復(fù)制到內(nèi)存中的double write buffer ，通過double write buffer再分2次，每次寫入1MB到共享表空間，然后馬上調(diào)用fsync函數(shù)，同步到磁盤上，避免緩沖帶來的問題（前倆個(gè)是提升性能，雙寫主要保證數(shù)據(jù)頁的可用性）。
InnoDB存儲(chǔ)引擎內(nèi)存結(jié)構(gòu)圖如下：
回復(fù)"資源"取干貨
個(gè)人微信：Cc2015123
添加請(qǐng)注明來意 :)
<b id="afajh"><abbr id="afajh"></abbr></b>