如何深入揭秘DBbrain智能優(yōu)化引擎

今天就跟大家聊聊有關(guān)如何深入揭秘DBbrain智能優(yōu)化引擎，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

DBbrain的SQL優(yōu)化原理和實現(xiàn)。

前言

在之前的幾期診斷日的分享中，分別介紹了如何使用DBbrain自助處理數(shù)據(jù)庫主從復(fù)制延遲、CPU使用率過高、字符集不匹配的場景。本期的將為大家分享DBbrain的SQL優(yōu)化原理和實現(xiàn)。主要看點如下：

1.深入揭秘DBbrain智能優(yōu)化引擎架構(gòu)及原理

2.DBbrain推出業(yè)內(nèi)首個SQL優(yōu)化效果對比功能

為了便于大家理解DBbrain的SQL優(yōu)化功能的使用場景和設(shè)計背景，先簡單聊一聊SQL性能較差與數(shù)據(jù)庫性能聯(lián)系——我們通常把性能較差的SQL稱之為慢SQL，一般我們可通過設(shè)置slow_query_log參數(shù)設(shè)置為ON，來捕獲執(zhí)行時間超過一定數(shù)值（由long_query_time參數(shù)控制）的SQL語句。表現(xiàn)上來理解就是執(zhí)行時間過長的SQL，但廣義上消耗資源過多、執(zhí)行計劃不夠優(yōu)秀的SQL同樣具有影響數(shù)據(jù)庫性能的潛在隱患，可能只是因為資源足夠空閑（緊急升配往往能夠臨時掩蓋性能問題）或者數(shù)據(jù)量不夠大，所以這幾類SQL的執(zhí)行時間并沒有太長，但在特定場景下卻會放大其對數(shù)據(jù)庫性能的影響。而一般80%的數(shù)據(jù)庫性能問題都是由于SQL性能所導(dǎo)致的，所以如何進行SQL的優(yōu)化、SQL優(yōu)化的效果就成為了數(shù)據(jù)庫性能提升的關(guān)鍵因素。那么接下來就為大家揭秘，DBbrain的智能優(yōu)化引擎是如何進行SQL優(yōu)化的。

基于規(guī)則和代價估算的SQL優(yōu)化建議

DBbrain的SQL優(yōu)化引擎獨立于數(shù)據(jù)庫，避免對原生數(shù)據(jù)庫引擎進行侵入。它的主要組件包括SQL解析和校驗、基于規(guī)則的SQL重寫、查詢條件選擇度/代價估算、SQL子句檢查以及建議生成器。除此之外，Connector組件負責(zé)與目標(biāo)數(shù)據(jù)庫交互，同步SQL優(yōu)化所需配置和表結(jié)構(gòu)定義相關(guān)信息以及SQL代價估算。

1.SQL解析和校驗

負責(zé)解析輸入SQL語句，將提取涉及到的庫表交給Connector組件獲取表定義，并校驗相關(guān)字段名、類型以及字符集（出于性能考慮，DBbrain不支持MyISAM表以及視圖）。

2.SQL重寫

數(shù)據(jù)庫優(yōu)化器都具有重寫組件。它一般在選擇索引，生成執(zhí)行計劃之前，通過對原SQL語句進行無語義差別的變換，使得SQL語句更加簡潔，方便后續(xù)組件更好的選擇執(zhí)行計劃。執(zhí)行計劃選擇是在當(dāng)前給定條件下盡力選擇最佳執(zhí)行路徑，而SQL重寫、增加合適的索引則是為執(zhí)行計劃選擇創(chuàng)造更好物理條件。數(shù)據(jù)庫自身具有一定重寫功能，因此SQL優(yōu)化建議也需要識別這些規(guī)則，并通過變換將查詢條件和實際庫表進行關(guān)聯(lián)。下面舉個例子來說明一下SQL 重寫的原理：SQL在數(shù)據(jù)庫中的執(zhí)行路徑往往和開發(fā)人員在寫的結(jié)構(gòu)不太相同，比如開發(fā)人員看到的如下SQL語句：

在數(shù)據(jù)庫中的視圖卻是如下的執(zhí)行流程：

DBbrain的SQL優(yōu)化功目的就是幫助數(shù)據(jù)庫尋找最佳執(zhí)行路徑，將其執(zhí)行路徑優(yōu)化成更為簡潔和高效的視圖。從SQL解析上看，查詢條件字段"value"是和a關(guān)聯(lián)，但a僅僅是子查詢的別名。從無語義差別的角度，該查詢條件是可以下推到子查詢，和庫表dbbrain_1直接關(guān)聯(lián)。

但是數(shù)據(jù)庫自身重寫功能通常具有片面性，實現(xiàn)并不完善。在某些特定場景下，顯示的更改SQL語句，可以大幅度提高執(zhí)行性能。比如：條件下推聚合子查詢，exists變換為join，條件合并等。實現(xiàn)SQL變化的最大前提條件是無語義差別的，保證查詢結(jié)果正確。這些變化是基于預(yù)先設(shè)定好的規(guī)則。

3.選擇度計算

條件選擇度計算是索引建議核心，它決定了索引字段順序以及驅(qū)動表的選擇。條件字段的選擇度計算依賴于表的統(tǒng)計信息，并需要對庫表進行數(shù)據(jù)抽樣。DBbrain會默認隨機抽取200~1000條數(shù)據(jù)。這些數(shù)據(jù)是應(yīng)用了crc32函數(shù)的校驗碼，避免獲取用戶原始數(shù)據(jù)，用戶不必擔(dān)心數(shù)據(jù)安全問題。

4.條件/子句檢查

根據(jù)MySQL引擎規(guī)則，識別出order by/ aggregate條件、project(輸出字段），并合理利用索引；與此同時，識別出不合理條件或使用方式，提示用戶。比如非前置like匹配，數(shù)字作用于字符條件字段等。

5.SQL優(yōu)化建議生成

SQL優(yōu)化建議包括索引優(yōu)化建議和SQL重新優(yōu)化建議。

優(yōu)化前后的執(zhí)行計劃對比及效果評估

傳統(tǒng)的手動優(yōu)化SQL，極度考驗DBA的知識儲備和實戰(zhàn)經(jīng)驗積累，優(yōu)化后一般只能通過explain的改變來預(yù)估SQL優(yōu)化效果，而大多數(shù)研發(fā)和運維目前使用的市面上的SQL優(yōu)化工具更是只能根據(jù)理論分析得出優(yōu)化結(jié)果。這樣一來我們?nèi)绾?strong>精確驗證SQL優(yōu)化的效果好壞？大多數(shù)情況下都只能通過執(zhí)行后觀察業(yè)務(wù)延遲的表象來判斷（也可以采用較為復(fù)雜的測試環(huán)境變更，旁路流量的方式驗證），但這樣的驗證方式不僅效率低下，而且在變更前、變更中、變更后均對數(shù)據(jù)庫性能存在極高的風(fēng)險。

但這些已經(jīng)是過去式了，現(xiàn)在不用擔(dān)心啦！騰訊云數(shù)據(jù)庫DBbrain團隊歷經(jīng)多年的技術(shù)探索和研發(fā)后，終于推出了業(yè)內(nèi)第一款基于執(zhí)行代價分析的SQL性能優(yōu)化效果對比功能，能夠在未執(zhí)行變更前對變更效果進行預(yù)估，讓用戶能預(yù)知變更的優(yōu)化效果，更加放心的根據(jù)優(yōu)化建議進行變更，同時也通過此類技術(shù)的結(jié)果反饋不斷優(yōu)化自身SQL優(yōu)化引擎的精準(zhǔn)性。

在不更改用戶數(shù)據(jù)庫的前提下，DBbrain智能優(yōu)化引擎能夠?qū)o出的SQL優(yōu)化建議進行效果評估。SQL代價估算引擎在該功能中起到主要作用。通過分析SQL相關(guān)庫表的統(tǒng)計信息、OPTIMIZER_SWITCH配置、以及索引字段區(qū)分度估算，對優(yōu)化后的SQL語句待機進行整體代價估計。下面我們通過一個現(xiàn)網(wǎng)真實案例進行展示：

1、優(yōu)化效果提前預(yù)知

DBbrain智能優(yōu)化引擎通過代價對比，直觀呈現(xiàn)出SQL優(yōu)化后降低99.19%的效果，也可通過優(yōu)化前后的執(zhí)行計劃比對進一步驗證優(yōu)化的效果

2、智能建議省時省力

DBbrain智能優(yōu)化引擎給出的SQL重寫+增加索引相結(jié)合的建議對SQL進行性能優(yōu)化

3、輔助用戶理解優(yōu)化

為了輔助用戶更好的理解優(yōu)化，DBbrain還提供表結(jié)構(gòu)信息的展示，讓信息更加直觀明了。

基于全量審計日志負載評估和優(yōu)化

一般對實際系統(tǒng)的優(yōu)化需要全局的把握，而不是僅僅針對有一個SQL語句，雖然增加索引可以提高查詢性能，但同時也會增加磁盤開銷，降低寫入性能。針對不同SQL語句，可能給出針對某一個表的重復(fù)索引；同時我們還需要考慮SQL的執(zhí)行頻度，對系統(tǒng)的整體負載影響，有時候單個SQL的掃描行數(shù)不高，但是因頻率過高也會成為主要問題點。

而DBbrain的評估優(yōu)化是基于全量審計日志的負載，實時計算SQL掃描行數(shù)，定位主要問題SQL語句并給出優(yōu)化建議，因此優(yōu)化是整體的，全方位的。

新功能速遞

最后，也為大家進行一下DBbrain的新功能速遞。DBbrain正式發(fā)布新版，新功能包括：

1.全面支持只讀實例、災(zāi)備實例，涵蓋所有性能診斷和優(yōu)化功能；

2.新增多種主從復(fù)制故障、異常、隱患的排查和優(yōu)化；

3.全面優(yōu)化用戶體驗，提供“用戶級”和“實例級”功能；

• 用戶級功能：實例概覽、實例管理（實例列表、告警匯總）、全實例監(jiān)控；

• 實例級功能：異常診斷、實時會話、慢SQL分析、SQL優(yōu)化、空間分析、健康報告、審計日志分析。

4.SQL優(yōu)化能力持續(xù)升級。

看完上述內(nèi)容，你們對如何深入揭秘DBbrain智能優(yōu)化引擎有進一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。