淺談SQL診斷的技術(shù)要點(diǎn)

背景

眾所周知，數(shù)據(jù)庫系統(tǒng)是一個(gè)非常復(fù)雜的軟件系統(tǒng)，它給使用者服務(wù)提供簡單接口的同時(shí)，開放了其生命周期中各個(gè)環(huán)節(jié)的可配置性，以定制化地應(yīng)對紛繁復(fù)雜的業(yè)務(wù)場景。這些環(huán)節(jié)包括了數(shù)據(jù)備份、數(shù)據(jù)安全、性能、可用性、升級更新、變更管理等，它們和業(yè)務(wù)項(xiàng)目支持、數(shù)據(jù)邏輯設(shè)計(jì)和終端體驗(yàn)優(yōu)化一起組成了數(shù)據(jù)庫管理人員的所有工作日常。

自從2017下半年數(shù)據(jù)庫業(yè)界提出“自動(dòng)駕駛數(shù)據(jù)庫”（Autonomous Database）這一概念以來，包含華為云在內(nèi)的全球各大云數(shù)據(jù)庫廠商都開始了云數(shù)據(jù)庫在“自動(dòng)優(yōu)化”這一話題上的探索，這期間形成了有關(guān)“自動(dòng)駕駛數(shù)據(jù)庫”的各個(gè)方向要素的基礎(chǔ)理論，并在眾多環(huán)節(jié)落地產(chǎn)生了成果，華為云RDS的數(shù)據(jù)庫SQL診斷功能就是其中之一。

SQL診斷功能將賦能開發(fā)、測試人員守護(hù)自己的SQL質(zhì)量，并將DBA從日常優(yōu)化工作中解放出來，推動(dòng)數(shù)據(jù)庫開發(fā)流程的轉(zhuǎn)變。下面從產(chǎn)品使用和實(shí)現(xiàn)原理兩個(gè)方面，詳細(xì)為大家介紹SQL診斷在華為云DAS的最佳實(shí)踐。

SQL診斷

1 功能入口

數(shù)據(jù)庫管理服務(wù)（DAS）的SQL窗口支持多語句拆分、格式化、執(zhí)行計(jì)劃功能，在它們的基礎(chǔ)上我們加上了優(yōu)化功能，如圖1所示。

通過即將推出的“SQL診斷”按鈕，我們能對輸入到SQL窗口中的語句進(jìn)行診斷，得到診斷建議，如圖2所示。

對于較復(fù)雜的語句，DAS需要較長時(shí)間（超過3秒）才能完成診斷任務(wù)，會提示用戶在后臺任務(wù)列表頁等待診斷結(jié)果。

2 診斷引擎實(shí)現(xiàn)細(xì)節(jié)

討論實(shí)現(xiàn)細(xì)節(jié)前，我們先整體過一遍SQL語句診斷的業(yè)務(wù)流程。

首先用戶輸入一條或多條SQL語句在DAS前臺發(fā)起診斷請求，服務(wù)器收到待診斷語句后創(chuàng)建診斷任務(wù)，進(jìn)行語句解析，獲得任務(wù)涉及的庫、表、字段，在用戶DB上獲取響應(yīng)的Schema信息后，開始有針對性的進(jìn)行庫、表的數(shù)據(jù)采樣，基于我們的語句規(guī)則庫對語句生成語句建議，基于獲取到的Schema信息生成候選索引，根據(jù)候選索引生成執(zhí)行計(jì)劃并計(jì)算執(zhí)行代價(jià)，選擇執(zhí)行代價(jià)最低的執(zhí)行計(jì)劃生成診斷結(jié)果。

上述標(biāo)粗關(guān)鍵字即為SQL診斷服務(wù)的關(guān)鍵步驟，下面我們舉個(gè)例子，來解釋它們每一步都具體做了什么操作。

2.1?語句解析

我們以上文圖中的SQL為例，這是一個(gè)從test_person表中獲取所有phone為13999213411并且gmt_create小于2005-01-01 00:00:00的人員列表的簡單查詢語句。開發(fā)人員和DBA很清楚這條SQL的含義，但是需要解析才能讓機(jī)器明白它的結(jié)構(gòu)化信息：

2.2?Schema信息

從語句解析中得到待診斷語句設(shè)計(jì)的庫名、表名，隨后我們就需要拿到關(guān)于它們的Schema結(jié)構(gòu)信息，在MySQL中我們直接通過系統(tǒng)表information_schema.tables/information_schema. columns/information_schema.statistics獲取。

2.3?數(shù)據(jù)采樣

我們的數(shù)據(jù)采樣算法來源于《Random sampling for histogram construction: how much is enough?》，隨機(jī)采樣的數(shù)量可以用如下公式來確定：

其中，R是采樣行數(shù)，是差別率（即1-為可信度），是表記錄數(shù)，是直方圖大小。用于衡量采樣直方圖與真實(shí)表記錄數(shù)據(jù)的誤差因子。由于log函數(shù)的存在，R對N的依賴較弱，這樣對大數(shù)據(jù)量表的采樣比較友好。

我們對公式中的可調(diào)節(jié)參數(shù)有一套反饋機(jī)制，可以做到依據(jù)代價(jià)計(jì)算的準(zhǔn)確性動(dòng)態(tài)調(diào)整。此外，工程上的采樣操作比較耗時(shí)，我們引入了主動(dòng)采樣來提高采樣在整個(gè)診斷過程中的效率。

2.4?生成語句建議

目前我們的規(guī)則庫涵蓋了華為云數(shù)據(jù)庫專家對安全、HINT、排序、翻頁、數(shù)據(jù)類型、字符集等在編寫SQL語句時(shí)的各個(gè)方面的最佳實(shí)踐，以下圖中的SQL語句為例，通過語句解析，發(fā)現(xiàn)該語句結(jié)構(gòu)命中了三條規(guī)則，我們?yōu)樗扇龡l語句建議。

2.5?生成候選索引

在語句解析后，我們可以獲取用戶輸入SQL語句的所有QueryStatement，通常一條復(fù)雜的SQL語句包含多個(gè)QueryStatement。每個(gè)QueryStatement的Where條件謂詞、Group By、Order By等涉及字段都將用于索引生成算法。

此過程中我們會做一些細(xì)節(jié)處理，例如：不考慮主鍵字段，因?yàn)樵贛ySQL? InnoDB引擎中非主鍵索引和主鍵已經(jīng)有映射關(guān)系；操作符為OR時(shí)，不考慮兩側(cè)謂詞在索引合并（Index Merge）情況下的候選索引；在已有前文所述的采樣數(shù)據(jù)時(shí)，過濾性低于一定閾值的字段將被直接舍棄，不參與候選索引的生成，等等。

2.6?計(jì)算執(zhí)行代價(jià)

代價(jià)計(jì)算是整個(gè)索引推薦流程的重點(diǎn)，我們采用了與MySQL內(nèi)核相似的算法，來計(jì)算根據(jù)所有候選索引生成執(zhí)行路徑的執(zhí)行代價(jià)。依據(jù)每一條原有索引和新生成候選索引的結(jié)構(gòu)，得到謂詞的先后比較順序，然后在采樣數(shù)據(jù)的基礎(chǔ)上，估算謂詞的過濾性及比較次數(shù)，進(jìn)而估算其耗時(shí)。

此過程中，因?yàn)镾QL查詢中不必要的“回表”會導(dǎo)致額外的執(zhí)行成本，我們著重區(qū)分了覆蓋索引與常規(guī)索引的代價(jià)計(jì)算邏輯。

另外，索引并不是越多越好，尤其是多個(gè)字段的聯(lián)合索引，會帶來不小的額外磁盤占用，因此在代價(jià)公式中我們還以一定權(quán)重加入了磁盤占用成本。

2.7 生成索引建議

通過上一步的計(jì)算，我們根據(jù)帶來最小執(zhí)行代價(jià)的候選索引生成DDL語句，形成一個(gè)有效的索引建議。

總結(jié)

本文對華為云數(shù)據(jù)管理服務(wù)（DAS）的SQL語句診斷功能做了簡單介紹，并簡要講述了SQL診斷的關(guān)鍵步驟和部分細(xì)節(jié)，希望大家能對SQL診斷的整體原理有初步認(rèn)識。

DAS的SQL診斷功能預(yù)計(jì)在2019年一季度面向用戶推出，歡迎關(guān)注，與提供寶貴意見~

此外，后續(xù)我們還將發(fā)布一系列技術(shù)文章，進(jìn)一步討論SQL診斷技術(shù)，敬請期待、品鑒。

數(shù)據(jù)庫

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。