為更好的幫助DBA運維資料庫，騰訊雲将于每月12日開展DBbrain診斷日，騰訊雲進階産品經理迪B哥為大家解析經典資料庫運維難題，結合騰訊雲資料庫智能管家DBbrain的能力，為大家提供問題優化思路和方法，玩轉資料庫！
本期診斷日主要分享内容：DBbrain的SQL優化原理和實作。
前言
在之前的幾期診斷日的分享中，分别介紹了如何使用DBbrain自助處理資料庫主從複制延遲、CPU使用率過高、字元集不比對的場景。本期的将為大家分享DBbrain的SQL優化原理和實作。主要看點如下：
1.深入揭秘DBbrain智能優化引擎架構及原理
2.DBbrain推出業内首個SQL優化效果對比功能
為了便于大家了解DBbrain的SQL優化功能的使用場景和設計背景，先簡單聊一聊SQL性能較差與資料庫性能聯系——我們通常把性能較差的SQL稱之為慢SQL，一般我們可通過設定slow_query_log參數設定為ON，來捕獲執行時間超過一定數值（由long_query_time參數控制）的SQL語句。表現上來了解就是執行時間過長的SQL，但廣義上消耗資源過多、執行計劃不夠優秀的SQL同樣具有影響資料庫性能的潛在隐患，可能隻是因為資源足夠空閑（緊急升配往往能夠臨時掩蓋性能問題）或者資料量不夠大，是以這幾類SQL的執行時間并沒有太長，但在特定場景下卻會放大其對資料庫性能的影響。而一般80%的資料庫性能問題都是由于SQL性能所導緻的，是以如何進行SQL的優化、SQL優化的效果就成為了資料庫性能提升的關鍵因素。那麼接下來就為大家揭秘，DBbrain的智能優化引擎是如何進行SQL優化的。
基于規則和代價估算的SQL優化建議
DBbrain的SQL優化引擎獨立于資料庫，避免對原生資料庫引擎進行侵入。它的主要元件包括SQL解析和校驗、基于規則的SQL重寫、查詢條件選擇度/代價估算、SQL子句檢查以及建議生成器。除此之外，Connector元件負責與目标資料庫互動，同步SQL優化所需配置和表結構定義相關資訊以及SQL代價估算。
1.SQL解析和校驗
負責解析輸入SQL語句，将提取涉及到的庫表交給Connector元件擷取表定義，并校驗相關字段名、類型以及字元集（出于性能考慮，DBbrain不支援MyISAM表以及視圖）。
2.SQL重寫
資料庫優化器都具有重寫元件。它一般在選擇索引，生成執行計劃之前，通過對原SQL語句進行無語義差别的變換，使得SQL語句更加簡潔，友善後續元件更好的選擇執行計劃。執行計劃選擇是在目前給定條件下盡力選擇最佳執行路徑，而SQL重寫、增加合适的索引則是為執行計劃選擇創造更好實體條件。
資料庫自身具有一定重寫功能，是以SQL優化建議也需要識别這些規則，并通過變換将查詢條件和實際庫表進行關聯。下面舉個例子來說明一下SQL 重寫的原理：
SQL在資料庫中的執行路徑往往和開發人員在寫的結構不太相同，比如開發人員看到的如下SQL語句：
在資料庫中的視圖卻是如下的執行流程：
DBbrain的SQL優化功目的就是幫助資料庫尋找最佳執行路徑，将其執行路徑優化成更為簡潔和高效的視圖。從SQL解析上看，查詢條件字段"value?imageView2/2/w/1620"是和a關聯，但a僅僅是子查詢的别名。從無語義差别的角度，該查詢條件是可以下推到子查詢，和庫表dbbrain_1直接關聯。
但是資料庫自身重寫功能通常具有片面性，實作并不完善。在某些特定場景下，顯示的更改SQL語句，可以大幅度提高執行性能。比如：條件下推聚合子查詢，exists變換為join，條件合并等。實作SQL變化的最大前提條件是無語義差别的，保證查詢結果正确。這些變化是基于預先設定好的規則。
3.選擇度計算
條件選擇度計算是索引建議核心，它決定了索引字段順序以及驅動表的選擇。條件字段的選擇度計算依賴于表的統計資訊，并需要對庫表進行資料抽樣。DBbrain會預設随機抽取200~1000條資料。這些資料是應用了crc32函數的校驗碼，避免擷取使用者原始資料，使用者不必擔心資料安全問題。
4.條件/子句檢查
根據MySQL引擎規則，識别出order by/ aggregate條件、project(輸出字段），并合理利用索引；與此同時，識别出不合理條件或使用方式，提示使用者。比如非前置like比對，數字作用于字元條件字段等。
5.SQL優化建議生成
SQL優化建議包括索引優化建議和SQL重新優化建議。
優化前後的執行計劃對比及效果評估
傳統的手動優化SQL，極度考驗DBA的知識儲備和實戰經驗積累，優化後一般隻能通過explain的改變來預估SQL優化效果，而大多數研發和運維目前使用的市面上的SQL優化工具更是隻能根據理論分析得出優化結果。這樣一來我們如何精确驗證SQL優化的效果好壞？大多數情況下都隻能通過執行後觀察業務延遲的表象來判斷（也可以采用較為複雜的測試環境變更，旁路流量的方式驗證），但這樣的驗證方式不僅效率低下，而且在變更前、變更中、變更後均對資料庫性能存在極高的風險。
但這些已經是過去式了，現在不用擔心啦！騰訊雲資料庫DBbrain團隊曆經多年的技術探索和研發後，終于推出了業内第一款基于執行代價分析的SQL性能優化效果對比功能，能夠在未執行變更前對變更效果進行預估，讓使用者能預知變更的優化效果，更加放心的根據優化建議進行變更，同時也通過此類技術的結果回報不斷優化自身SQL優化引擎的精準性。
在不更改使用者資料庫的前提下，DBbrain智能優化引擎能夠對給出的SQL優化建議進行效果評估。SQL代價估算引擎在該功能中起到主要作用。通過分析SQL相關庫表的統計資訊、OPTIMIZER_SWITCH配置、以及索引字段區分度估算，對優化後的SQL語句待機進行整體代價估計。下面我們通過一個現網真實案例進行展示：
1、優化效果提前預知
DBbrain智能優化引擎通過代價對比，直覺呈現出SQL優化後降低99.19%的效果，也可通過優化前後的執行計劃比對進一步驗證優化的效果
2、智能建議省時省力
DBbrain智能優化引擎給出的SQL重寫+增加索引相結合的建議對SQL進行性能優化
3、輔助使用者了解優化
為了輔助使用者更好的了解優化，DBbrain還提供表結構資訊的展示，讓資訊更加直覺明了。
基于全量審計日志負載評估和優化
一般對實際系統的優化需要全局的把握，而不是僅僅針對有一個SQL語句，雖然增加索引可以提高查詢性能，但同時也會增加磁盤開銷，降低寫入性能。針對不同SQL語句，可能給出針對某一個表的重複索引；同時我們還需要考慮SQL的執行頻度，對系統的整體負載影響，有時候單個SQL的掃描行數不高，但是因頻率過高也會成為主要問題點。
而DBbrain的評估優化是基于全量審計日志的負載，實時計算SQL掃描行數，定位主要問題SQL語句并給出優化建議，是以優化是整體的，全方位的。
新功能速遞
在本文的最後，也為大家進行一下DBbrain的新功能速遞。DBbrain正式釋出新版，新功能包括：
1.全面支援隻讀執行個體、災備執行個體，涵蓋所有性能診斷和優化功能；
2.新增多種主從複制故障、異常、隐患的排查和優化；
3.全面優化使用者體驗，提供“使用者級”和“執行個體級”功能；
- 使用者級功能：執行個體概覽、執行個體管理（執行個體清單、告警彙總）、全執行個體監控；
- 執行個體級功能：異常診斷、實時會話、慢SQL分析、SQL優化、空間分析、健康報告、審計日志分析。
4.SQL優化能力持續更新。
歡迎投稿
工作中遇到棘手故障不知道怎麼辦？歡迎投稿到診斷日，被選中的案例将由騰訊雲資深專家“會診”，并在DBbrain診斷日線上分析教學，幫您提供解決方案。投稿即有機會獲得企鵝公仔，問題被選中即得騰訊雲資料庫千元代金券~投稿請關注“騰訊雲資料庫”官方微信後，回複“投稿”即可。
直播預告
往期課程，請關注“騰訊雲資料庫”官方微信後，回複“迪B課堂”即可檢視~
特惠體驗雲資料庫