我的主機(jī)內(nèi)存只有100G，現(xiàn)在要全表掃描一個(gè)200G大表，會(huì)不會(huì)把DB主機(jī)的內(nèi)存用光？
成都創(chuàng)新互聯(lián)是一家專注于成都網(wǎng)站制作、做網(wǎng)站與策劃設(shè)計(jì),尼木網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)十余年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:尼木等地區(qū)。尼木做網(wǎng)站價(jià)格咨詢:13518219792
邏輯備份時(shí)，可不就是做整庫掃描嗎？若這樣就會(huì)把內(nèi)存吃光，邏輯備份不是早就掛了？
所以大表全表掃描，看起來應(yīng)該沒問題。這是為啥呢？
假設(shè)，我們現(xiàn)在要對(duì)一個(gè)200G的InnoDB表db1. t，執(zhí)行一個(gè)全表掃描。當(dāng)然，你要把掃描結(jié)果保存在客戶端，會(huì)使用類似這樣的命令：
InnoDB數(shù)據(jù)保存在主鍵索引上，所以全表掃描實(shí)際上是直接掃描表t的主鍵索引。這條查詢語句由于沒有其他判斷條件，所以查到的每一行都可以直接放到結(jié)果集，然后返回給客戶端。
那么，這個(gè)“結(jié)果集”存在哪里呢？
服務(wù)端無需保存一個(gè)完整結(jié)果集。取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程是這樣的：
查詢結(jié)果發(fā)送流程
可見：
所以MySQL其實(shí)是“邊讀邊發(fā)”。這意味著，若客戶端接收得慢，會(huì)導(dǎo)致MySQL服務(wù)端由于結(jié)果發(fā)不出去，這個(gè)事務(wù)的執(zhí)行時(shí)間變長。
比如下面這個(gè)狀態(tài)，就是當(dāng)客戶端不讀 socket receive buffer 內(nèi)容時(shí)，在服務(wù)端show processlist看到的結(jié)果。
若看到State一直是“Sending to client”，說明服務(wù)器端的網(wǎng)絡(luò)棧寫滿了。
若客戶端使用–quick參數(shù)，會(huì)使用mysql_use_result方法：讀一行處理一行。假設(shè)某業(yè)務(wù)的邏輯較復(fù)雜，每讀一行數(shù)據(jù)以后要處理的邏輯若很慢，就會(huì)導(dǎo)致客戶端要過很久才取下一行數(shù)據(jù)，可能就會(huì)出現(xiàn)上圖結(jié)果。
因此，對(duì)于正常的線上業(yè)務(wù)來說，若一個(gè)查詢的返回結(jié)果不多，推薦使用mysql_store_result接口，直接把查詢結(jié)果保存到本地內(nèi)存。
當(dāng)然前提是查詢返回結(jié)果不多。如果太多，因?yàn)閳?zhí)行了一個(gè)大查詢導(dǎo)致客戶端占用內(nèi)存近20G，這種情況下就需要改用mysql_use_result接口。
若你在自己負(fù)責(zé)維護(hù)的MySQL里看到很多個(gè)線程都處于“Sending to client”，表明你要讓業(yè)務(wù)開發(fā)同學(xué)優(yōu)化查詢結(jié)果，并評(píng)估這么多的返回結(jié)果是否合理。
若要快速減少處于這個(gè)狀態(tài)的線程的話，可以將net_buffer_length設(shè)置更大。
有時(shí)，實(shí)例上看到很多查詢語句狀態(tài)是“Sending data”，但查看網(wǎng)絡(luò)也沒什么問題，為什么Sending data要這么久？
一個(gè)查詢語句的狀態(tài)變化是這樣的：
即“Sending data”并不一定是指“正在發(fā)送數(shù)據(jù)”，而可能是處于執(zhí)行器過程中的任意階段。比如，你可以構(gòu)造一個(gè)鎖等待場(chǎng)景，就能看到Sending data狀態(tài)。
讀全表被鎖：
Sending data狀態(tài)
可見session2是在等鎖，狀態(tài)顯示為Sending data。
所以，查詢的結(jié)果是分段發(fā)給客戶端，因此掃描全表，查詢返回大量數(shù)據(jù)，并不會(huì)把內(nèi)存打爆。
以上是server層的處理邏輯，在InnoDB引擎里又是怎么處理？
InnoDB內(nèi)存的一個(gè)作用，是保存更新的結(jié)果，再配合redo log，避免隨機(jī)寫盤。
內(nèi)存的數(shù)據(jù)頁是在Buffer Pool (簡稱為BP)管理，在WAL里BP起加速更新的作用。
BP還能加速查詢。
而BP對(duì)查詢的加速效果，依賴于一個(gè)重要的指標(biāo)，即：內(nèi)存命中率。
可以在show engine innodb status結(jié)果中，查看一個(gè)系統(tǒng)當(dāng)前的BP命中率。一般情況下，一個(gè)穩(wěn)定服務(wù)的線上系統(tǒng)，要保證響應(yīng)時(shí)間符合要求的話，內(nèi)存命中率要在99%以上。
執(zhí)行show engine innodb status ，可以看到“Buffer pool hit rate”字樣，顯示的就是當(dāng)前的命中率。比如下圖命中率，就是100%。
若所有查詢需要的數(shù)據(jù)頁都能夠直接從內(nèi)存得到，那是最好的，對(duì)應(yīng)命中率100%。
InnoDB Buffer Pool的大小是由參數(shù) innodb_buffer_pool_size確定，一般建議設(shè)置成可用物理內(nèi)存的60%~80%。
在大約十年前，單機(jī)的數(shù)據(jù)量是上百個(gè)G，而物理內(nèi)存是幾個(gè)G；現(xiàn)在雖然很多服務(wù)器都能有128G甚至更高的內(nèi)存，但是單機(jī)的數(shù)據(jù)量卻達(dá)到了T級(jí)別。
所以，innodb_buffer_pool_size小于磁盤數(shù)據(jù)量很常見。若一個(gè) Buffer Pool滿了，而又要從磁盤讀入一個(gè)數(shù)據(jù)頁，那肯定是要淘汰一個(gè)舊數(shù)據(jù)頁的。
使用的最近最少使用 (Least Recently Used, LRU)算法，淘汰最久未使用數(shù)據(jù)。
InnoDB管理BP的LRU算法，是用鏈表實(shí)現(xiàn)的：
最終就是最久沒有被訪問的數(shù)據(jù)頁P(yáng)m被淘汰。
若此時(shí)要做一個(gè)全表掃描，會(huì)怎樣？若要掃描一個(gè)200G的表，而這個(gè)表是一個(gè)歷史數(shù)據(jù)表，平時(shí)沒有業(yè)務(wù)訪問它。
那么，按此算法掃描，就會(huì)把當(dāng)前BP里的數(shù)據(jù)全部淘汰，存入掃描過程中訪問到的數(shù)據(jù)頁的內(nèi)容。也就是說BP里主要放的是這個(gè)歷史數(shù)據(jù)表的數(shù)據(jù)。
對(duì)于一個(gè)正在做業(yè)務(wù)服務(wù)的庫，這可不行呀。你會(huì)看到，BP內(nèi)存命中率急劇下降，磁盤壓力增加，SQL語句響應(yīng)變慢。
所以，InnoDB不能直接使用原始的LRU。InnoDB對(duì)其進(jìn)行了優(yōu)化。
InnoDB按5:3比例把鏈表分成New區(qū)和Old區(qū)。圖中LRU_old指向的就是old區(qū)域的第一個(gè)位置，是整個(gè)鏈表的5/8處。即靠近鏈表頭部的5/8是New區(qū)域，靠近鏈表尾部的3/8是old區(qū)域。
改進(jìn)后的LRU算法執(zhí)行流程：
該策略，就是為了處理類似全表掃描的操作量身定制。還是掃描200G歷史數(shù)據(jù)表：
可以看到，這個(gè)策略最大的收益，就是在掃描這個(gè)大表的過程中，雖然也用到了BP，但對(duì)young區(qū)完全沒有影響，從而保證了Buffer Pool響應(yīng)正常業(yè)務(wù)的查詢命中率。
MySQL采用的是邊算邊發(fā)的邏輯，因此對(duì)于數(shù)據(jù)量很大的查詢結(jié)果來說，不會(huì)在server端保存完整的結(jié)果集。所以，如果客戶端讀結(jié)果不及時(shí)，會(huì)堵住MySQL的查詢過程，但是不會(huì)把內(nèi)存打爆。
而對(duì)于InnoDB引擎內(nèi)部，由于有淘汰策略，大查詢也不會(huì)導(dǎo)致內(nèi)存暴漲。并且，由于InnoDB對(duì)LRU算法做了改進(jìn)，冷數(shù)據(jù)的全表掃描，對(duì)Buffer Pool的影響也能做到可控。
全表掃描還是比較耗費(fèi)IO資源的，所以業(yè)務(wù)高峰期還是不能直接在線上主庫執(zhí)行全表掃描的。
MySQL 8.0.16 已經(jīng)發(fā)布，它像往常一樣增強(qiáng)了組復(fù)制 Group Replication 功能。
這篇文章介紹了 MySQL 8.0.16 為 Group Replication 帶來的新功能：
Message fragmentation（信息碎片化）。
背景
Group Replication 目前使用 XCom（一種組通信引擎），特點(diǎn)：原子性，組員狀態(tài)檢測(cè)等。每個(gè)成員的組復(fù)制插件先將信息轉(zhuǎn)發(fā)到本地 XCom，再由 XCom 最終以相同的順序?qū)⑿畔鬟f給每個(gè)組成員的 Group Replication 插件。
XCom 由單線程實(shí)現(xiàn)。當(dāng)一些成員廣播信息過大時(shí)，XCom 線程必須花費(fèi)更多的時(shí)間來處理那個(gè)大信息。如果成員的 XCom 線程忙于處理大信息的時(shí)間過長，它可能會(huì)去查看其他成員的 XCom 實(shí)例。例如，忙碌的成員失效。如果是這樣，該組可以從該組中驅(qū)逐忙碌的成員。
MySQL 8.0.13 新增??group_replication_member_expel_timeout??系統(tǒng)變量，您可以通過它來調(diào)整將成員從組中驅(qū)逐的時(shí)間。例如，懷疑成員失敗，但成員實(shí)際上忙于處理大信息，給成員足夠的時(shí)間來完成處理。在這種情況下，是否為成員增加驅(qū)逐超時(shí)的設(shè)置是一種權(quán)衡。有可能等了很久，該成員實(shí)際真的失效了。
Message fragmentation（信息碎片化）
MySQL 8.0.16 的 Group Replication 插件新增用來處理大信息的功能：信息碎片化。
簡而言之，您可以為成員的廣播信息指定最大值。超過最大值的信息將分段為較小的塊傳播。
您可以使用? group_replication_communication_max_message_size??系統(tǒng)變量指定允許的信息最大值（默認(rèn)值為10 MiB）。
示例
讓我們用一個(gè)例子來解釋新功能。圖1顯示了當(dāng)綠色成員向組廣播信息時(shí)，新功能是如何處理的。
圖1 對(duì)傳出信息進(jìn)行分段
1. 如果信息大小超過用戶允許的最大值（group_replication_communication_max_message_size），則該成員會(huì)將信息分段為不超過最大值的塊。
2. 該成員將每個(gè)塊廣播到該組，即將每個(gè)塊單獨(dú)轉(zhuǎn)發(fā)到XCom。
XCom 最終將這些塊提供給組成員。下面三張圖展示出了中間綠色成員發(fā)送大信息時(shí)工作的新特征。
圖2a 重新組合傳入的信息：第一個(gè)片段
3. 成員得出結(jié)論，傳入的信息實(shí)際上是一個(gè)更大信息的片段。
4. 成員緩沖傳入的片段，因?yàn)樗麄冋J(rèn)為片段是仍然不完整的信息的一部分。（片段包含必要的元數(shù)據(jù)以達(dá)到這個(gè)結(jié)論。）
圖2b 重新組合傳入的信息：第二個(gè)片段
5. 見上面的第3步。
6. 見上面的第4步。
圖2c 重新組合傳入的信息：最后一個(gè)片段
7. 成員得出結(jié)論，傳入的信息實(shí)際上是一個(gè)更大信息的片段。
8. 成員得出結(jié)論，傳入的片段是最后一個(gè)缺失的塊，重新組合原始信息，然后對(duì)其進(jìn)行處理，傳輸完畢。
結(jié)論
MySQL 8.0.16 已經(jīng)發(fā)布后，組復(fù)制現(xiàn)在可以確保組內(nèi)交換的信息大小不超過用戶定義的閾值。這可以防止組內(nèi)誤判而驅(qū)逐成員。
問題
我們有一個(gè) SQL，用于找到?jīng)]有主鍵 / 唯一鍵的表，但是在 MySQL 5.7 上運(yùn)行特別慢，怎么辦？
實(shí)驗(yàn)
我們搭建一個(gè) MySQL 5.7 的環(huán)境，此處省略搭建步驟。
寫個(gè)簡單的腳本，制造一批帶主鍵和不帶主鍵的表：
執(zhí)行一下腳本：
現(xiàn)在執(zhí)行以下 SQL 看看效果：
...
執(zhí)行了 16.80s，感覺是非常慢了。
現(xiàn)在用一下 DBA 三板斧，看看執(zhí)行計(jì)劃：
感覺有點(diǎn)慘，由于 information_schema.columns 是元數(shù)據(jù)表，沒有必要的統(tǒng)計(jì)信息。
那我們來 show warnings 看看 MySQL 改寫后的 SQL：
我們格式化一下 SQL：
可以看到 MySQL 將
select from A where A.x not in (select x from B) //非關(guān)聯(lián)子查詢
轉(zhuǎn)換成了
select from A where not exists (select 1 from B where B.x = a.x) //關(guān)聯(lián)子查詢
如果我們自己是 MySQL，在執(zhí)行非關(guān)聯(lián)子查詢時(shí)，可以使用很簡單的策略：
select from A where A.x not in (select x from B where ...) //非關(guān)聯(lián)子查詢:1. 掃描 B 表中的所有記錄，找到滿足條件的記錄，存放在臨時(shí)表 C 中，建好索引2. 掃描 A 表中的記錄，與臨時(shí)表 C 中的記錄進(jìn)行比對(duì)，直接在索引里比對(duì)，
而關(guān)聯(lián)子查詢就需要循環(huán)迭代：
select from A where not exists (select 1 from B where B.x = a.x and ...) //關(guān)聯(lián)子查詢掃描 A 表的每一條記錄 rA： ? ? 掃描 B 表，找到其中的第一條滿足 rA 條件的記錄。
顯然，關(guān)聯(lián)子查詢的掃描成本會(huì)高于非關(guān)聯(lián)子查詢。
我們希望 MySQL 能先"緩存"子查詢的結(jié)果（緩存這一步叫物化，MATERIALIZATION），但MySQL 認(rèn)為不緩存更快，我們就需要給予 MySQL 一定指導(dǎo)。
...
可以看到執(zhí)行時(shí)間變成了 0.67s。
整理
我們?cè)\斷的關(guān)鍵點(diǎn)如下：
\1. 對(duì)于 information_schema 中的元數(shù)據(jù)表，執(zhí)行計(jì)劃不能提供有效信息。
\2. 通過查看 MySQL 改寫后的 SQL，我們猜測(cè)了優(yōu)化器發(fā)生了誤判。
\3. 我們?cè)黾恿?hint，指導(dǎo) MySQL 正確進(jìn)行優(yōu)化判斷。
但目前我們的實(shí)驗(yàn)僅限于猜測(cè)，猜中了萬事大吉，猜不中就無法做出好的診斷。
重慶分公司
重慶分公司
