我們經(jīng)常會遇到操作一張大表，發(fā)現(xiàn)操作時間過長或影響在線業(yè)務了，想要回退大表操作的場景。在我們停止大表操作之后，等待回滾是一個很漫長的過程，盡管你可能對知道一些縮短時間的方法，處于對生產(chǎn)環(huán)境數(shù)據(jù)完整性的敬畏，也會選擇不做介入。最終選擇不作為的原因大多源于對操作影響的不確定性。實踐出真知，下面針對兩種主要提升事務回滾速度的方式進行驗證，一種是提升操作可用內(nèi)存空間，一種是通過停實例，禁用 redo 回滾方式進行進行驗證。
創(chuàng)新互聯(lián)公司于2013年開始，先為萬榮等服務建站，萬榮等地企業(yè)，進行企業(yè)商務咨詢服務。為萬榮企業(yè)網(wǎng)站制作PC+手機+微官網(wǎng)三網(wǎng)同步一站式服務解決您的所有建站問題。
仔細閱讀過官方手冊的同學，一定留意到了對于提升大事務回滾效率，官方提供了兩種方法：一是增加 innodb_buffer_pool_size 參數(shù)大小，二是合理利用 innodb_force_recovery=3 參數(shù)，跳過事務回滾過程。第一種方式比較溫和，innodb_buffer_pool_size 參數(shù)是可以動態(tài)調(diào)整的，可行性也較高。第二種方式相較之下較暴力，但效果較好。
兩種方式各有自己的優(yōu)點，第一種方式對線上業(yè)務系統(tǒng)影響較小，不會中斷在線業(yè)務。第二種方式效果更顯著，會短暫影響業(yè)務連續(xù)，回滾所有沒有提交的事務。
1、數(shù)據(jù)表 collect ( id, title ,info ,vtype) 就這4個字段，其中 title 用定長，info 用text, id 是逐漸，vtype是tinyint，vtype是索引。這是一個基本的新聞系統(tǒng)的簡單模型?，F(xiàn)在往里面填充數(shù)據(jù)，填充10萬篇新聞。
2、最后collect 為 10萬條記錄，數(shù)據(jù)庫表占用硬盤1.6G。OK ,看下面這條sql語句：select id,title from collect limit 1000,10; 很快；基本上0.01秒就OK，再看下面的select id,title from collect limit 90000,10; 從9萬條開始分頁。
3、8-9秒完成。
4、看下面一條語句:select id from collect order by id limit 90000,10; 很快，0.04秒就OK。因為用了id主鍵做索引當然快。
我的主機內(nèi)存只有100G，現(xiàn)在要全表掃描一個200G大表，會不會把DB主機的內(nèi)存用光？
邏輯備份時，可不就是做整庫掃描嗎？若這樣就會把內(nèi)存吃光，邏輯備份不是早就掛了？
所以大表全表掃描，看起來應該沒問題。這是為啥呢？
假設，我們現(xiàn)在要對一個200G的InnoDB表db1. t，執(zhí)行一個全表掃描。當然，你要把掃描結(jié)果保存在客戶端，會使用類似這樣的命令：
InnoDB數(shù)據(jù)保存在主鍵索引上，所以全表掃描實際上是直接掃描表t的主鍵索引。這條查詢語句由于沒有其他判斷條件，所以查到的每一行都可以直接放到結(jié)果集，然后返回給客戶端。
那么，這個“結(jié)果集”存在哪里呢？
服務端無需保存一個完整結(jié)果集。取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程是這樣的：
查詢結(jié)果發(fā)送流程
可見：
所以MySQL其實是“邊讀邊發(fā)”。這意味著，若客戶端接收得慢，會導致MySQL服務端由于結(jié)果發(fā)不出去，這個事務的執(zhí)行時間變長。
比如下面這個狀態(tài)，就是當客戶端不讀 socket receive buffer 內(nèi)容時，在服務端show processlist看到的結(jié)果。
若看到State一直是“Sending to client”，說明服務器端的網(wǎng)絡棧寫滿了。
若客戶端使用–quick參數(shù)，會使用mysql_use_result方法：讀一行處理一行。假設某業(yè)務的邏輯較復雜，每讀一行數(shù)據(jù)以后要處理的邏輯若很慢，就會導致客戶端要過很久才取下一行數(shù)據(jù)，可能就會出現(xiàn)上圖結(jié)果。
因此，對于正常的線上業(yè)務來說，若一個查詢的返回結(jié)果不多，推薦使用mysql_store_result接口，直接把查詢結(jié)果保存到本地內(nèi)存。
當然前提是查詢返回結(jié)果不多。如果太多，因為執(zhí)行了一個大查詢導致客戶端占用內(nèi)存近20G，這種情況下就需要改用mysql_use_result接口。
若你在自己負責維護的MySQL里看到很多個線程都處于“Sending to client”，表明你要讓業(yè)務開發(fā)同學優(yōu)化查詢結(jié)果，并評估這么多的返回結(jié)果是否合理。
若要快速減少處于這個狀態(tài)的線程的話，可以將net_buffer_length設置更大。
有時，實例上看到很多查詢語句狀態(tài)是“Sending data”，但查看網(wǎng)絡也沒什么問題，為什么Sending data要這么久？
一個查詢語句的狀態(tài)變化是這樣的：
即“Sending data”并不一定是指“正在發(fā)送數(shù)據(jù)”，而可能是處于執(zhí)行器過程中的任意階段。比如，你可以構(gòu)造一個鎖等待場景，就能看到Sending data狀態(tài)。
讀全表被鎖：
Sending data狀態(tài)
可見session2是在等鎖，狀態(tài)顯示為Sending data。
所以，查詢的結(jié)果是分段發(fā)給客戶端，因此掃描全表，查詢返回大量數(shù)據(jù)，并不會把內(nèi)存打爆。
以上是server層的處理邏輯，在InnoDB引擎里又是怎么處理？
InnoDB內(nèi)存的一個作用，是保存更新的結(jié)果，再配合redo log，避免隨機寫盤。
內(nèi)存的數(shù)據(jù)頁是在Buffer Pool (簡稱為BP)管理，在WAL里BP起加速更新的作用。
BP還能加速查詢。
而BP對查詢的加速效果，依賴于一個重要的指標，即：內(nèi)存命中率。
可以在show engine innodb status結(jié)果中，查看一個系統(tǒng)當前的BP命中率。一般情況下，一個穩(wěn)定服務的線上系統(tǒng)，要保證響應時間符合要求的話，內(nèi)存命中率要在99%以上。
執(zhí)行show engine innodb status ，可以看到“Buffer pool hit rate”字樣，顯示的就是當前的命中率。比如下圖命中率，就是100%。
若所有查詢需要的數(shù)據(jù)頁都能夠直接從內(nèi)存得到，那是最好的，對應命中率100%。
InnoDB Buffer Pool的大小是由參數(shù) innodb_buffer_pool_size確定，一般建議設置成可用物理內(nèi)存的60%~80%。
在大約十年前，單機的數(shù)據(jù)量是上百個G，而物理內(nèi)存是幾個G；現(xiàn)在雖然很多服務器都能有128G甚至更高的內(nèi)存，但是單機的數(shù)據(jù)量卻達到了T級別。
所以，innodb_buffer_pool_size小于磁盤數(shù)據(jù)量很常見。若一個 Buffer Pool滿了，而又要從磁盤讀入一個數(shù)據(jù)頁，那肯定是要淘汰一個舊數(shù)據(jù)頁的。
使用的最近最少使用 (Least Recently Used, LRU)算法，淘汰最久未使用數(shù)據(jù)。
InnoDB管理BP的LRU算法，是用鏈表實現(xiàn)的：
最終就是最久沒有被訪問的數(shù)據(jù)頁Pm被淘汰。
若此時要做一個全表掃描，會怎樣？若要掃描一個200G的表，而這個表是一個歷史數(shù)據(jù)表，平時沒有業(yè)務訪問它。
那么，按此算法掃描，就會把當前BP里的數(shù)據(jù)全部淘汰，存入掃描過程中訪問到的數(shù)據(jù)頁的內(nèi)容。也就是說BP里主要放的是這個歷史數(shù)據(jù)表的數(shù)據(jù)。
對于一個正在做業(yè)務服務的庫，這可不行呀。你會看到，BP內(nèi)存命中率急劇下降，磁盤壓力增加，SQL語句響應變慢。
所以，InnoDB不能直接使用原始的LRU。InnoDB對其進行了優(yōu)化。
InnoDB按5:3比例把鏈表分成New區(qū)和Old區(qū)。圖中LRU_old指向的就是old區(qū)域的第一個位置，是整個鏈表的5/8處。即靠近鏈表頭部的5/8是New區(qū)域，靠近鏈表尾部的3/8是old區(qū)域。
改進后的LRU算法執(zhí)行流程：
該策略，就是為了處理類似全表掃描的操作量身定制。還是掃描200G歷史數(shù)據(jù)表：
可以看到，這個策略最大的收益，就是在掃描這個大表的過程中，雖然也用到了BP，但對young區(qū)完全沒有影響，從而保證了Buffer Pool響應正常業(yè)務的查詢命中率。
MySQL采用的是邊算邊發(fā)的邏輯，因此對于數(shù)據(jù)量很大的查詢結(jié)果來說，不會在server端保存完整的結(jié)果集。所以，如果客戶端讀結(jié)果不及時，會堵住MySQL的查詢過程，但是不會把內(nèi)存打爆。
而對于InnoDB引擎內(nèi)部，由于有淘汰策略，大查詢也不會導致內(nèi)存暴漲。并且，由于InnoDB對LRU算法做了改進，冷數(shù)據(jù)的全表掃描，對Buffer Pool的影響也能做到可控。
全表掃描還是比較耗費IO資源的，所以業(yè)務高峰期還是不能直接在線上主庫執(zhí)行全表掃描的。
問題
我們有一個 SQL，用于找到?jīng)]有主鍵 / 唯一鍵的表，但是在 MySQL 5.7 上運行特別慢，怎么辦？
實驗
我們搭建一個 MySQL 5.7 的環(huán)境，此處省略搭建步驟。
寫個簡單的腳本，制造一批帶主鍵和不帶主鍵的表：
執(zhí)行一下腳本：
現(xiàn)在執(zhí)行以下 SQL 看看效果：
...
執(zhí)行了 16.80s，感覺是非常慢了。
現(xiàn)在用一下 DBA 三板斧，看看執(zhí)行計劃：
感覺有點慘，由于 information_schema.columns 是元數(shù)據(jù)表，沒有必要的統(tǒng)計信息。
那我們來 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)子查詢時，可以使用很簡單的策略：
select from A where A.x not in (select x from B where ...) //非關(guān)聯(lián)子查詢:1. 掃描 B 表中的所有記錄，找到滿足條件的記錄，存放在臨時表 C 中，建好索引2. 掃描 A 表中的記錄，與臨時表 C 中的記錄進行比對，直接在索引里比對，
而關(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)子查詢的掃描成本會高于非關(guān)聯(lián)子查詢。
我們希望 MySQL 能先"緩存"子查詢的結(jié)果（緩存這一步叫物化，MATERIALIZATION），但MySQL 認為不緩存更快，我們就需要給予 MySQL 一定指導。
...
可以看到執(zhí)行時間變成了 0.67s。
整理
我們診斷的關(guān)鍵點如下：
\1. 對于 information_schema 中的元數(shù)據(jù)表，執(zhí)行計劃不能提供有效信息。
\2. 通過查看 MySQL 改寫后的 SQL，我們猜測了優(yōu)化器發(fā)生了誤判。
\3. 我們增加了 hint，指導 MySQL 正確進行優(yōu)化判斷。
但目前我們的實驗僅限于猜測，猜中了萬事大吉，猜不中就無法做出好的診斷。
重慶分公司
重慶分公司
