主機(jī)內(nèi)存為什么不會被MySQL表用光？

服務(wù)端并不需要保存一個完整的結(jié)果集办成。取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程是這樣的：

1. 獲取一行凰浮，寫到net_buffer中。這塊內(nèi)存的大小是由參數(shù)net_buffer_length定義的乏沸，默認(rèn)是16k。
2. 重復(fù)獲取行爪瓜，直到net_buffer寫滿蹬跃，調(diào)用網(wǎng)絡(luò)接口發(fā)出去。
3. 如果發(fā)送成功铆铆，就清空net_buffer蝶缀，然后繼續(xù)取下一行，并寫入net_buffer薄货。
4. 如果發(fā)送函數(shù)返回EAGAIN或WSAEWOULDBLOCK翁都，就表示本地網(wǎng)絡(luò)棧（socket sendbuffer）寫滿了，進(jìn)入等待谅猾。直到網(wǎng)絡(luò)棧重新可寫柄慰，再繼續(xù)發(fā)送鳍悠。

結(jié)論：MySQL是“邊讀邊發(fā)的”，這個概念很重要坐搔。這就意味著藏研，如果客戶端接收得慢，會導(dǎo)致MySQL服務(wù)端由于結(jié)果發(fā)不出去概行，這個事務(wù)的執(zhí)行時間變長蠢挡。

Sending to client 和 Sending data 區(qū)別

image.png

image.png

如果你看到State的值一直處于“Sending to client”，就表示服務(wù)器端的網(wǎng)絡(luò)棧寫滿了凳忙。

實(shí)際上业踏，一個查詢語句的狀態(tài)變化是這樣的（注意：這里，我略去了其他無關(guān)的狀態(tài)）：
MySQL查詢語句進(jìn)入執(zhí)行階段后涧卵，首先把狀態(tài)設(shè)置成“Sending data”勤家；然后，發(fā)送執(zhí)行結(jié)果的列相關(guān)的信息（meta data) 給客戶端艺演；再繼續(xù)執(zhí)行語句的流程却紧；執(zhí)行完成后，把狀態(tài)設(shè)置成空字符串胎撤。也就是說晓殊，“Sending data”并不一定是指“正在發(fā)送數(shù)據(jù)”，而可能是處于執(zhí)行器過程中的任意階
段伤提。

即使該線程是在等鎖巫俺，狀態(tài)也是Sending data

僅當(dāng)一個線程處于“等待客戶端接收結(jié)果”的狀態(tài)，才會顯示"Sending to client"肿男；而如果顯示成“Sending data”介汹，它的意思只是“正在執(zhí)行”。

Buffer Pool

內(nèi)存的數(shù)據(jù)頁是在Buffer Pool (BP)中管理的舶沛，在WAL里Buffer Pool 起到了加速更新的作用嘹承。而實(shí)際上，Buffer Pool 還有一個更重要的作用如庭，就是加速查詢叹卷。
由于有buffer pool 在內(nèi)存中，還未應(yīng)用到磁盤中坪它，此時有一個查詢正好查詢到該部分?jǐn)?shù)據(jù)骤竹，則可以直接從內(nèi)存中讀取，不需要再走一百年磁盤往毡，所以說Buffer Pool由加速查詢效果 蒙揣，而Buffer Pool對查詢的加速效果，依賴于一個重要的指標(biāo)开瞭，即：內(nèi)存命中率懒震。

InnoDB內(nèi)存管理用的是最近最少使用 (Least RecentlyUsed, LRU)算法罩息，這個算法的核心就是淘汰最久未使用的數(shù)據(jù)

LRU

InnoDB管理Buffer Pool的LRU算法，是用鏈表來實(shí)現(xiàn)的挎狸。

image.png

1. 在上圖的狀態(tài)1里扣汪，鏈表頭部是P1断楷，表示P1是最近剛剛被訪問過的數(shù)據(jù)頁锨匆；假設(shè)內(nèi)存里只能放下這么多數(shù)據(jù)頁；
2. 這時候有一個讀請求訪問P3冬筒，因此變成狀態(tài)2恐锣，P3被移到最前面；
3. 狀態(tài)3表示舞痰，這次訪問的數(shù)據(jù)頁是不存在于鏈表中的土榴，所以需要在Buffer Pool中新申請一個數(shù)據(jù)頁P(yáng)x，加到鏈表頭部响牛。但是由于內(nèi)存已經(jīng)滿了玷禽，不能申請新的內(nèi)存。于是呀打，會清空鏈表末尾Pm這個數(shù)據(jù)頁的內(nèi)存矢赁，存入Px的內(nèi)容，然后放到鏈表頭部贬丛。
4. 從效果上看撩银，就是最久沒有被訪問的數(shù)據(jù)頁P(yáng)m，被淘汰了豺憔。
   這個算法乍一看上去沒什么問題额获，但是如果考慮到要做一個全表掃描，會不會有問題呢恭应？
   假設(shè)按照這個算法抄邀，我們要掃描一個200G的表，而這個表是一個歷史數(shù)據(jù)表昼榛，平時沒有業(yè)務(wù)訪問它境肾。

那么，按照這個算法掃描的話褒纲，就會把當(dāng)前的Buffer Pool里的數(shù)據(jù)全部淘汰掉准夷，存入掃描過程中訪問到的數(shù)據(jù)頁的內(nèi)容。也就是說Buffer Pool里面主要放的是這個歷史數(shù)據(jù)表的數(shù)據(jù)莺掠。對于一個正在做業(yè)務(wù)服務(wù)的庫衫嵌，這可不妙。你會看到彻秆，Buffer Pool的內(nèi)存命中率急劇下降楔绞，磁盤
壓力增加结闸，SQL語句響應(yīng)變慢。

故此 酒朵，InnoDB對LRU算法做了改進(jìn)桦锄。

在InnoDB實(shí)現(xiàn)上，按照5:3的比例把整個LRU鏈表分成了young區(qū)域和old區(qū)域蔫耽。圖中LRU_old指向的就是old區(qū)域的第一個位置结耀，是整個鏈表的5/8處。也就是說匙铡，靠近鏈表頭部的5/8是young區(qū)域图甜，靠近鏈表尾部的3/8是old區(qū)域。
改進(jìn)后的LRU算法執(zhí)行流程變成了下面這樣鳖眼。

image.png

1. 上圖中狀態(tài)1黑毅，要訪問數(shù)據(jù)頁P(yáng)3，由于P3在young區(qū)域钦讳，因此和優(yōu)化前的LRU算法一樣矿瘦，將其移到鏈表頭部，變成狀態(tài)2愿卒。
2. 之后要訪問一個新的不存在于當(dāng)前鏈表的數(shù)據(jù)頁缚去，這時候依然是淘汰掉數(shù)據(jù)頁P(yáng)m，但是新插入的數(shù)據(jù)頁P(yáng)x掘猿，是放在LRU_old處病游。
3. 處于old區(qū)域的數(shù)據(jù)頁，每次被訪問的時候都要做下面這個判斷：
   若這個數(shù)據(jù)頁在LRU鏈表中存在的時間超過了1秒稠通，就把它移動到鏈表頭部衬衬；
   如果這個數(shù)據(jù)頁在LRU鏈表中存在的時間短于1秒，位置保持不變改橘。1秒這個時間滋尉，是由參數(shù)innodb_old_blocks_time控制的。其默認(rèn)值是1000飞主，單位毫秒狮惜。
   這個策略，就是為了處理類似全表掃描的操作量身定制的碌识。還是以剛剛的掃描200G的歷史數(shù)據(jù)表為例碾篡，我們看看改進(jìn)后的LRU算法的操作邏輯：
4. 掃描過程中，需要新插入的數(shù)據(jù)頁筏餐，都被放到old區(qū)域;
5. 一個數(shù)據(jù)頁里面有多條記錄开泽，這個數(shù)據(jù)頁會被多次訪問到，但由于是順序掃描魁瞪，這個數(shù)據(jù)頁第一次被訪問和最后一次被訪問的時間間隔不會超過1秒穆律，因此還是會被保留在old區(qū)域惠呼；
6. 再繼續(xù)掃描后續(xù)的數(shù)據(jù)，之前的這個數(shù)據(jù)頁之后也不會再被訪問到峦耘，于是始終沒有機(jī)會移到鏈表頭部（也就是young區(qū)域）剔蹋，很快就會被淘汰出去「ㄋ瑁可以看到泣崩，這個策略最大的收益，就是在掃描這個大表的過程中利朵，雖然也用到了Buffer Pool律想，但是對young區(qū)域完全沒有影響猎莲，從而保證了Buffer Pool響應(yīng)正常業(yè)務(wù)的查詢命中率绍弟。

MYSQL中JOIN

1.Index Nested-Loop Join簡稱NLJ

t1,t2兩個表都有一個主鍵索引id和一個索引a，字段b上無索引著洼。存儲過程往表t2里插入了1000行數(shù)據(jù)樟遣，在表t1里插入的是100行數(shù)據(jù)。

select * from t1 straight_join t2 on (t1.a=t2.a);

image.png

簡單來說就是對于t1表做全表掃描身笤，然后根據(jù)索引a去表t2查找豹悬，每次查找都只掃描一行，所以是t1表大小+t2表大小

如何選擇驅(qū)動表和被驅(qū)動表液荸？
在這個join語句執(zhí)行過程中瞻佛，驅(qū)動表是走全表掃描，而被驅(qū)動表是走樹搜索假設(shè)被驅(qū)動表的行數(shù)是M娇钱。每次在被驅(qū)動表查一行數(shù)據(jù)伤柄，要先搜索索引a，再搜索主鍵索引文搂。每次搜索一棵樹近似復(fù)雜度是以2為底的M的對數(shù)适刀，記為log M，所以在被驅(qū)動表上查一行的時間復(fù)
雜度是 2log M煤蹭。假設(shè)驅(qū)動表的行數(shù)是N笔喉，執(zhí)行過程就要掃描驅(qū)動表N行，然后對于每一行硝皂，到被驅(qū)動表上匹配一次常挚。
因此整個執(zhí)行過程，近似復(fù)雜度是 N+N2*log M稽物。

顯然奄毡，N對掃描行數(shù)的影響更大，因此應(yīng)該讓小表來做驅(qū)動表姨裸。這點(diǎn)和常識相符秧倾。

2.Simple Nested-Loop Join

sql語句：

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表t2的字段b上沒有索引怨酝，因此再用圖2的執(zhí)行流程時，每次到t2去匹配的時候那先，就要做一次全表掃描农猬。

如果你沒覺得這個影響有那么“顯然”， 可以這么理解：N擴(kuò)大1000倍的話售淡，掃描行數(shù)就會擴(kuò)大1000倍斤葱；而M擴(kuò)大1000倍，掃描行數(shù)擴(kuò)大不到10倍揖闸。

所以該算法顯得很笨重揍堕，故MYSQL使用了另一個叫作“Block Nested-Loop Join”的算法，簡稱BNL汤纸。

3.Block Nested-Loop Join

這時候衩茸，被驅(qū)動表上沒有可用的索引，算法的流程是這樣的：

1. 把表t1的數(shù)據(jù)讀入線程內(nèi)存join_buffer中贮泞，由于我們這個語句中寫的是select *楞慈，因此是把整個表t1放入了內(nèi)存；
2. 掃描表t2啃擦，把表t2中的每一行取出來囊蓝，跟join_buffer中的數(shù)據(jù)做對比，滿足join條件的令蛉，作為結(jié)果集的一部分返回聚霜。

image.png

可以看到，在這個過程中珠叔，對表t1和t2都做了一次全表掃描蝎宇，因此總的掃描行數(shù)是1100。由于join_buffer是以無序數(shù)組的方式組織的运杭，因此對表t2中的每一行夫啊，都要做100次判斷，總共需要在內(nèi)存中做的判斷次數(shù)是：100*1000=10萬次辆憔。
前面我們說過撇眯，如果使用Simple Nested-Loop Join算法進(jìn)行查詢，掃描行數(shù)也是10萬行虱咧。因此熊榛，從時間復(fù)雜度上來說，這兩個算法是一樣的腕巡。但是玄坦，Block Nested-Loop Join算法的這10萬次判斷是內(nèi)存操作，速度上會快很多，性能也更好煎楣。
和之前的算法的差距就是該算法將數(shù)據(jù)存到內(nèi)存中豺总。當(dāng)然內(nèi)存放不下的情況下就會分段放。
執(zhí)行過程就變成了：

1. 掃描表t1，順序讀取數(shù)據(jù)行放入join_buffer中，放完第88行join_buffer滿了穆端，繼續(xù)第2步；
2. 掃描表t2表伦，把t2中的每一行取出來，跟join_buffer中的數(shù)據(jù)做對比慷丽，滿足join條件的蹦哼，作為結(jié)果集的一部分返回；
3. 清空join_buffer要糊；
4. 繼續(xù)掃描表t1纲熏，順序讀取最后的12行數(shù)據(jù)放入join_buffer中，繼續(xù)執(zhí)行第2步杨耙。

執(zhí)行流程變成：

image.png

4.能不能使用join語句赤套？

1. 如果可以使用IndexNested-Loop Join算法，也就是說可以用上被驅(qū)動表上的索引珊膜，其實(shí)是沒問題的；
2. 如果使用Block Nested-Loop Join算法宣脉，掃描行數(shù)就會過多车柠。尤其是在大表上的join操作，這樣可能要掃描被驅(qū)動表很多次塑猖，會占用大量的系統(tǒng)資源竹祷。所以這種join盡量不要用。

同時在決定哪個表做驅(qū)動表的時候羊苟，應(yīng)該是兩個表按照各自的條件過濾塑陵，過濾完成之后，計算參與join的各個字段的總數(shù)據(jù)量蜡励，數(shù)據(jù)量小的那個表令花，就是“小表”，應(yīng)該作為驅(qū)動表凉倚。

BKA Batched Key Access

BKA算法原理：將外層循環(huán)的行/結(jié)果集存入join buffer兼都，內(nèi)存循環(huán)的每一行數(shù)據(jù)與整個buffer中的記錄做比較，

可以減少內(nèi)層循環(huán)的掃描次數(shù).