圍剿慢SQL，工行MySQL研發管控和治理實踐
本文根據魏亞東老師在〖2021 DAMS中國數據智能管理峰會〗現場演講內容整理而成。
講師介紹
魏亞東，中國工商銀行 軟件開發中心三級經理，資深架構師，杭州研發部數據庫專家團隊牽頭人和開發中心安全團隊成員，負責技術管理、數據庫、安全相關工作，目前負責SaaS云產品建設。2009年加入中國工商銀行軟件開發中心，致力于推動管理創新、效能提升，提供全面技術管控，推動自動化實施，實現業務價值的高質量快速交付;同時作為技術專家，為生產安全提供技術支持。
分享概要
- 風險介紹
- 風險防范
- 方法論
- 確保可落地
- 生產防控
- 生產防控
- SRE管控體系
- 未來暢想
大家好，感謝dbaplus社群給我這個和大家交流的機會，今天分享的主題是《工行MySQL研發管控和治理實踐》。之前我在各種分享過程中，也多次強調過只有對自己來說最合適的產品，而沒有所謂最好的產品，雖然每種數據庫產品都把自己形容得很好，但實際上也都是針對一些特定場景去做的開發，即每種產品都有自己適用的場景，沒有可以包打天下的產品。
一、風險介紹
首先介紹MySQL的痛點。因為MySQL是免費的，所以我們在使用過程中必須承擔它所帶來的風險，換句話說，這就是生命中不可承受之痛。比如慢SQL在業界就算是一種通病，不論哪個公司，只要用了MySQL數據庫，慢SQL就必然是所有問題的重中之重。
像阿里在一篇介紹SRE團隊建設與職能分工的文章中曾提及：在SRE建設過程中，他們發現，慢SQL已經成為了一種挑戰：數據庫出現瓶頸，無法支撐業務發展;慢SQL的數量呈爆發式增長，應用穩定性岌岌可危等。
而對于工行來說，同樣遇到類似的問題，可以說是不可避免的問題：
數據庫性能急劇下降，CPU占用100%
大家可以看圖中左下角位置，這其實就是一個CPU在慢SQL影響下急劇飆升的情況。大量數據掃描會導致CPU飚高，1個線程最高可以把一個CPU吃滿，如果并發線程多的話，整體的CPU使用率就會急劇飆升，后果很嚴重。大家都知道，InnoDB有一個線程池innodb_thread_concurrency，這個池是有大小上限的，如果它用盡了，就會導致整個交易堵塞。以前再快的交易最后都會變慢。平時零點幾秒就可以解決的事情，最后可能需要幾秒、十幾秒，這實際上就引發出一種暴風式的連鎖反應。之前我所在的研發部就有一個MySQL應用，其拆分為4個群組，其中一個群組就是因為一個慢SQL，導致服務器完全不可用，無法對外提供服務。最后緊急進行了主備切換才解決了生產穩定性的問題。
主從復制時間延遲，影響RPO和RTO時效性，存在生產隱患
涉及主備切換時通常做法都會檢查主備的不一致性，確保備庫追平主庫后才做切換。我們金融行業對高可用性要求非常嚴謹，RPO需要小于60秒。但慢SQL會導致60秒內無法完成切換，之前我們有應用一個交易的主從復制時間增至24小時都未結束，所以后面我們會采取一系列措施對這方面進行強化治理。
讀寫分離存在過期讀，影響數據一致性
對于傳統公司來說，如果想做讀寫分離，應該是用一些不變的或者極少變化的數據去做。但我們有的應用因為對讀寫分離機制了解不深，也沒有考慮主從復制的時效性，把一些時效性要求非常高的數據從備庫去讀，這就導致了數據的不一致，引發生產隱患。最后領導只能選擇“一刀切”，原則上禁止我們將MySQL備庫作為讀庫進行讀寫分離，因為即便使用數據分布式訪問中間層，比如MyCAT、愛可生的DBLE、阿里的 TDDL或網易的DDB等，如果把備庫當作讀庫，還是會存在過期讀這種生產隱患。
二、風險防范
之前我在歷次分享中多次強調過，免費的午餐并不好吃。無數的案例告訴我們，慢SQL如果不加處理，最后很容易引發一個血案。大家如果看過電影《無極》的話，應該知道它又被稱為“一個饅頭引發的血案”。
工行的MySQL數據庫實例近8000個，云化占比在90%以上，慢SQL數量呈爆發式增長，一條慢SQL就可以導致服務不可用，降低用戶幸福指數。而對于金融行業來說，社會聲譽性是必須要考慮的關鍵因素之一，當服務不可用之后，很容易存在擠兌的風潮。我們幾年前上了CCTV其實也是同樣的道理，工行代表的是社會穩定性的基礎。
從我們治理的統計結果來看，還是相當有成效的。大家可以看到，我們單個事務超過10萬的大事務的報警次數，通過治理，由年初的每月500萬次左右，逐月下降至目前的100萬次左右，問題收斂趨勢明顯，但數量級擺在那兒，所以我們還是任重而道遠。用屈原的話說就是“路漫漫其修遠兮，吾將上下而求索”。
工行的治理實踐大概分4個階段，我們基本上是基于自動化的流水線，也就是DevOps去做這些事情的。
1、設計階段
首先，在設計階段我們會規范一些設計指引。大家如果有做過編碼，或者本身就是數據庫領域的專家的話就會知道，我們必須要夯實我們的方法論，而這個方法論就代表著我們設計指引的處理。比如數據庫必須要建主鍵等，這其實都屬于我們方法論的層面。
然后就是元數據管理，將數據標準按照應用、產品線等進行規范，抽取制定數據標準，形成元數據字典。現在流行一個概念叫元宇宙，而元數據我們把它簡稱為Meta。Facebook最近也改名為 Meta，這說明我們在10年前就已經預見到了這種問題。
第三是建立能力提升課程。我們將數據庫的使用人分成三級。第一級是基礎開發人員，他需要滿足一些MySQL常見的使用方式;第二級我們定位為DBA的處理，DBA需要分析InnoDB的本質，進行語句調優等;而第三級就是高階，我們希望他去對一些底層的邏輯進行處理，比如通過查看MySQL的源代碼，判斷死鎖等，從深處進行發掘，幫助大家快速定位和解決問題。
最后我們落實自動化處理，建立表結構設計工具。我們在Excel的基礎上做了一個元數據表結構設計工具，同時將它與我們的元數據管理系統進行連接。我們規定了業務線的數據標準，所有基于這條業務線的應用都必須滿足所制定的數據基礎，這就是我們所謂的元數據的概念。這樣一來，后續我們的數據治理，包括從數據湖中撈取數據等都能保持一致的數據標準，對我們進行一些數據挖掘、上下游聯動，以及后面會提到的根因分析，都會起到非常好的作用。
2、編碼層面
在編碼層面，我們強調規范的自動化。如果只有規范而沒有落地執行，那就相當于紙上談兵，永遠無法落于實處。所以我們基于SonarQube擴展建立一些規則，在開發階段就去檢查大家的語法是否正確、是否存在安全隱患等，防患于未然。
同時，考慮到一些安全性相關的問題，這其中還包含了SQL注入的檢查。比如MyBatis到底用“$”還是“#”號去處理等問題，防止SQL注入的風險。
還有就是對SQL寫法的規則。這里會講到我們經歷過的一個案例：開發人員在update的時候少了一個逗號，用and進行了連接，并由此引發了一個更新血案。因此后來我們在本地基于SonarLint的插件做了一個擴展，將SonarLint安裝在開發人員的本地。因為SonarLint跟SonarQube是完整的一個契合體，這樣一來，云端的規則制定以后，我們本地的規則也會進行同步，避免了開發人員自己再去安裝插件的過程，減少對開發人員的工作干擾。大家看阿里的開發手冊，p3c的一些組件檢查規則現在也在Sonar中進行了一些擴展。
3、測試階段
測試階段包含兩個方面的內容，一個是安全測試，一個是性能測試。
因為很多時候我們都必須預估交易量，例如預測半年內交易量的增長趨勢，同時判斷會不會有其他場景的影響等，所以我們必須進行壓測，這部分我們可以借助一些壓測平臺進行數據庫的壓測。
同時，針對安全測試我們也建立了DevSecOps這條黃金管道流水線，通過一些白盒測試(SAST)、黑盒測試(DAST)還有IAST等交互式的測試，來實現安全測試。
大家如果有關注“OWASP Top 10榜單”的話就能看到，注入風險的排名其實是比較高的。
4、交付后
最后是交付后。在這個階段我們會進行SRE的管理。SRE這個概念由Google首先提出，大家也正在實踐過程中。但從個人角度來看，我們其實提概念要大于實施。所以參照Google的實踐體系，我們重建了一個自己的SRE的管理體系，這其中就包含了慢SQL的監控治理。
接下來是對生產案例的分析。當出現一個生產案例以后，我們會分析它的解決路徑、記錄并分析在解決過程中遇到的好或不好的地方、思考后續的改進方法等，將它們匯總成一個生產問題分析文檔，定期在整個基地內部組織各部門進行學習，以規避相同的問題再度出現。因為我相信，不管你是否關注，只要看了文檔，有了一定的印象以后，后面如果再出現類似的場景就會去下意識地進行規避，我們把這稱為潛移默化。
第三部分是AIOps的根因分析。現在業界比較流行“1-5-10”這個概念，即一分鐘發現問題，五分鐘定位問題，十分鐘解決問題。雖然這個理念很好，但從目前來看，“一”現在正在努力，“五”和“十”還屬于更加高階的處理，后面會簡單再說一下。
同時，我們在生產上會建立一個慢SQL的查殺機制，將一些大事務提前進行kill掉以進行規避，將慢SQL對生產的一些風險提前扼殺。
理論說起來很簡單，但關鍵在于，大家是否愿意把它作為一個體系去做。也就是說，我們并不希望只是將它當成一個工具去做，而是希望建成一個研發管控的完整生態體系。只有形成了生態圈，最后才能做好治理管控。
三、方法論
首先是方法論的建立。俗話說萬事開頭難，我們的體系要想建立，就必須有方法論作為基石。這部分我們大致分為四步：
第一我們會做一些規范的說明。比如：每個表必須建立主鍵;禁止給庫、表、字段單獨設置排序規則等。
第二是量化，通過精細化的理性思維去規范、約束大家。中國人其實感性思維占多數，比如我們做菜的時候會說：“我們加少許醬油。”這個“少許”到底是多少?日本人可能會說5克或10克來具體定量，但中國人喜歡說少許。而對于我們來說，就是把掃描命中比等進行量化，要求聯機情況下rows_examined:rows_sent<100:1，規定事務大小undo<10萬等。不同的公司有不同的標準，這是我們對于自己的要求。
19年我們在建這個體系時，正在跟愛可生公司合作，因此有機會了解到他們對大事務的規范是在1萬條以內。所以，因為不同的公司系統硬件等條件不同，我們必須要根據所在企業實際情況去進行考量。
第三，我們要學會避坑。因為MySQL畢竟是一個開源免費的產品，所以它會有很多bug。比如大表的truncate，它其實會引發MySQL服務hang死的現象，最后大家可以在MySQL.err文件中發現一條 page_cleaner臟刷記錄的警告，同時還有它超過了多長時間，以及一些LRU的提示。還有就是禁用replace into。因為replace into它的風險大概有三種，我相信大家應該也都遇見過，這里的話就不細說了。
第四，是易理解。通過一些方式，告訴他們怎樣去理解，讓他們既知其然，也知其所以然。對此我們會做一些條款的解讀，方便用戶知道規范制定的原因，以及可能會引發的問題等。
通過這四步，把方法論的基石建立起來，同時我們會基于SonarQube擴展一定的規則。大家從圖中右上角的截圖可以看到，我們現在已經在質量門禁中把這些規則加進去了。
四、確保可落地
規范建立以后，如果依賴于人，那就有可能會產生許多不確定的額外風險，所以我們要通過自動化的質量門禁來解決這個問題。我們建立了一個重自動化、輕指引的質量門禁系統。這樣一來，不管有沒有問題，門禁系統都會幫你進行把控，提前將風險扼殺。
如果大家有做過代碼復核或其他一些規范工作的話就會明白，人在看到東西非常多時，就會下意識地產生逃避的想法，這樣很容易就會把一個本來可以控制住的場景直接忽略。像我們是基于druid擴展Sonarqube的插件，實現mapper.xml文件的掃描分析，并做到了本地檢查規則與云端同步。
我們目前已經建立了27條規則，后續也還在逐步完善豐富。從目前來看，已經涵蓋了所有開發人員最常見的一些問題。
這里有一條業界可能沒有的規范，就是：MySQL的Update語句的SET中，出現and可能是一個問題，根據SQL標準語法來看，原則上多個字段的更新中應該用逗號進行分割。在現實情況中，一般用逗號的人比較多，但確實也有開發人員中間用and去做分割。可以看圖中備注舉例的語句，這樣會導致它一個字段被更新成一個不可靠的布爾值，最后導致結果跟預期完全不同，導致很嚴重的生產問題產生。所以針對該情況我們進行了一些額外的擴展。同時，對于replace into，我們也不建議去使用。還有truncate，這部分我們建議大家用Drop + create去解決這方面的問題。
五、生產防控
生產上我們會通過監控自動查殺。但實際上，如果大家學過博弈論的話就會發現：是否查殺?到底怎么保持?這其實很難平衡，可能要通過實際場景去評估到底要不要去做。
接下來是慢SQL的治理。我們在2021年上半年大概發現了6670條慢SQL并落實版本優化，但正確率其實并不高，后續我們會考慮優化一些慢SQL的治理模型，將我們的準確率從20%再進行提升。
因為20%就意味著，我們的開發人員可能要花80%的無效時間去處理這方面的問題，顯然違背了二八原則。雖然這個過程很痛苦，但我們確實收到一定的經營效果。本質上我們可以看到performance_schema可以用于監控MySQL運行過程中的資源消耗、資源等待等情況。這應該是MySQL 5.7以后會有的一個視圖，這個視圖中有一個表statements_summary_by_digest，它會記錄每一條SQL的執行次數、時間，以及掃描的數據量等，通過對它們時間差的對比，獲取真正的處理時間，從而判斷語句是否存在問題。
Oracle的awr報告也是這樣的道理，它是通過兩個快照之間的差去進行比較。對于我們來說，比如8:00、9:00，我們的時間是一個語句。如圖所示，它在8點執行了200次，執行時間是1800秒，掃描的記錄數大概是1億8000萬;第二個是在9點，執行次數是300次，執行時間是2700秒。通過時間差可以判斷，8:00~9:00之間只執行了100次，執行時間為900秒，那相當于一次執行花費了9秒，這其實就是一個標準的慢SQL。
雖然我不清楚在座各位所屬應用對慢SQL的判定標準，默認應該是大于10秒的才算慢SQL。但對于我們金融行業來說，實際上超過一秒都屬于慢SQL的處理范圍。同時我們可以看到，它的掃描記錄數大概是9000萬，這樣算來，它的掃描命中比例是900000：1，也就是說，我需要掃描90萬條數據才能找到一條數據，這樣的掃描比有很大的問題，顯然存在效率問題，而我們的規范要求是100:1。
接下來是自動查殺。我們可以設置，當聯機超過閾值時自動執行kill。但這樣的做法在批量聯機混合時存在風險，因為我們現在的批量聯機用戶實際上都是混合在一起的，而我們允許批量耗時超過10秒，這就很容易出現誤殺。針對這一點，我們可以通過show processlist命令查看或者是通過 ps.threads去跟進線程的執行情況。
針對上述情況，我們做了兩件事情：第一是推聯機用戶跟批量用戶的分離，針對不同用戶進行差異性處理。對于聯機用戶我們會進行自動查殺;而對于批量用戶，因為我們判斷它語句執行所花費的時間是合理的，所以就暫時先不管。
MySQL其實并不適合OLAP的應用，按照我的理解，我們可以通過大數據平臺，也就是Hive、Spark，或者通過一些其他的方式去處理。這實際上就代表：MySQL僅局限于OLTP，其他像一些大數據平臺等去做一些OLAP的處理，做到權責分離。
看到這里大家可能會說HTAP，我覺得這是一個老概念，屬于新瓶裝舊酒，以前Oracle其實就是HTAP數據庫，但MySQL不行，所以需要結合實際業務場景做到功能和場景的分離。
六、生產防控
接下來是處理大事務的納什均衡，也就是對監控和自動查殺的危害進行分析。因為我們現在的主從控制模式都是基于行復制的模式，所以，針對這種行復制模式，如果是大事務，我們binlog寫入、傳輸，還有在備機的回放速度都會很慢。我們之前有應用被發現24小時都沒有在備庫完成重演，最后查出原因：因為這個表是新建的，所以它沒有建主鍵，相當于每次都去做全掃，同時因為它是一個大事務，最后就導致24小時都沒有回放完成。而解決方案也很簡單，把事務量降低，同時對表進行增加主鍵的處理。
然后就是交易寫入堵塞，以及當主庫出現故障時面臨的、切與不切之間的博弈。在這種大事務情況下，如果有些應用比較著急，那就會切，但這樣可能會導致一些庫的數據沒有重演完成，使業務存在風險;但如果選擇不切，而是等它自然結束的話，就有可能滿足不了我們3個9的高可用度。在這樣兩難的情況下，切與不切都會導致問題。最后只能靠上帝或者靠領導拍板。但無論最后的決定是什么，我們都可能會面對在這方面受到業務或者客戶的投訴的情況，所以，根據我的親身經歷，我認為這種風險是非常巨大的。
為此我們做了自動查殺。大家可以看到，MySQL可以通過執行show engine innodb status這個命令，對事務進行監控。在事務還沒有結束時，會提示這個事務更新的記錄數，而/G是我們對結果進行格式化，大家有機會的話可以嘗試一下。
如果超過設定好的閾值，我們會自動執行kill。但在執行這個操作時我們需要規避一刀切的情況，所以我們現在僅進行一些小規模的試點。在試點完成以后才會進行大規模的處理，說白了就是不敢強上，因為強上很容易出問題。
關于日志的導入問題如圖所示，這個事務里有一個導入操作，目前已經導入了3,245,700多條數據，這個大事務會影響我們很多事務的處理，我們就可以通過后臺自動把這個事務終止掉，避免問題進一步擴大。
七、SRE管控體系
一般情況下，從Google對SRE的解釋來看，它實際上是從生產運維層面去進行生產事件的處理和回溯。但是從開發團隊的角度，因為對于金融行業來說，運維團隊和開發團隊并不一樣，而且相互間容易存在掣肘。
這里我們分為三部分進行分析：
1、生產應急 運維分析
1)牽頭生產應急響應。
協助處理緊急生產問題，審核相關變更方案，確保問題閉環，形成總結文檔(包含影響、結果、待提升項、做的好的地方、處理時間線)，定期更新“發布CheckList”。
牽頭重點問題復盤，組織專項排查治理，涉及性能容量、賬務一致性、分布式體系等，確保范圍無疏漏，制定方案、審核整改計劃。
2)分析確認運維效果
建立測試和生產巡檢機制，通過每日巡檢、重要節日深度巡檢、投產前風險評估多層次巡檢方式以保障生產運行安全。
觀察生產運維效果情況，確認SLI、SLO和SLA等是否滿足設定目標，輔助產品經理形成運維分析報告。
組織建設進階要求的相關配套工具，包括質量門禁、白名單管控、代碼掃描、自動運維平臺和全息監控平臺的數據分析等。
2、研發階段
1)規范升級研發流程。
需求分析階段提交待提升項給產品經理，落實版本計劃安排。
設計階段指導架構師完成非功能性需求評估，涉及監控預警、灰度方案、發布方案、安全可信、應急預案、SLI等;以未來視角指導架構師完成性能和容量規劃;指導架構師形成技術架構的未來規劃，提供可執行的路線路，推進架構轉型;做好標準化方案和組件。
3、發布階段
在發布時，我們會根據CheckList，對相關實現情況進行核對勾選，在確保所有指標都達標以后，才允許它進行正式發布。這樣就能夠在應用質量方面有較大的提升。
八、未來暢想
最后簡單介紹一下我們對未來的暢想，也就是我們現在正在做的事情。
上文提到，我們一直在做“1-5-10”。但是目前還處于“1”穩步推進，“5”和“10”艱難實現的階段。
針對一分鐘定位我們發現，我們其實并沒有那么多采集指標，所以對一些數據可以加大采集，比如常規數據：CPU、內存......等，目前對于中間件系統基礎監控數據已經完成處理了，像網絡、QPS、連接數等。
針對MySQL數據庫，其實它有一些性能指標可以進行高密度采集，也有一些影響數據庫性能的要素指標需要進行低密度采集。這部分主要分為兩類，第一類是像ps.threads這種可以高密度地處理的，同時show engine innodb status，這樣我們可以快速發現到底用哪些死鎖，或迅速了解某個事務處理的數據量，以及哪些線程在做哪些事情等。這些是我們要做高密度采集的事情。低密度采集也就是我們提到過的摘要信息的處理，每小時進行一次快照的數據分析，同時通過events_statements_history，對數據進行定時采集。
通過高密度采集，我們可以快速進行一分鐘定位，查出出現問題的具體位置。
接下來是五分鐘預判。我們工行的實驗室之前有用“邏輯回歸加孤立森林算法進行異常檢測，用圖算法進行根因定位”對全鏈路進行一些根因的分析和追溯。但通過我們之前跟某Top企業交流時發現，根因定位這個方法實際的正確率只在40%左右，波動檢測精度可達到80以上，但該算法屬于監督算法，需要針對性訓練，無法大規模普及，且模型并沒有達到我們所預想的標準。之前阿里在中臺有一篇宣傳文章，介紹說它的五分鐘預判準確率可以達到90%，雖然我不太相信這個值，但他們的模型應該是相對比較好的。
十分鐘自愈這部分算是我們對未來的期望，我們希望未來在發現問題以后，系統可以直接自行處理。這部分其實我們現在在做的一些自動化運維可以達到這個要求。比如當數據庫發現問題以后，經過一些檢測，自動進行主從切換。這其實也屬于十分鐘自愈的范疇。
我們只能說還在路上，但是未來具體是什么樣的?目前我們也只能猜測。希望未來會更加美好!
