摘要

在企業(yè)級(jí)運(yùn)維工作中,，我們面對(duì)的往往是多種數(shù)據(jù)庫(kù),，數(shù)十甚至數(shù)百套數(shù)據(jù)庫(kù)，需要在有異動(dòng)時(shí)能快速響應(yīng),，更需要能提前感知,，在問(wèn)題產(chǎn)生前就找出風(fēng)險(xiǎn)點(diǎn)并消除,。在數(shù)據(jù)庫(kù)運(yùn)維中，大多數(shù)問(wèn)題都是由SQL引發(fā)的,，今天和大家分享的內(nèi)容就是一種能夠同時(shí)對(duì)數(shù)百套Oracle,、MySQL、DB2,、SQL Server、PostgreSQL,、OceanBase數(shù)據(jù)庫(kù)抓取TopSQL,，分析SQL質(zhì)量，找出風(fēng)險(xiǎn)點(diǎn)的方法,。

為什么要做,？

一直以來(lái)，大部分?jǐn)?shù)據(jù)庫(kù)性能問(wèn)題都是由應(yīng)用開(kāi)發(fā)產(chǎn)生的不良SQL,、不良表設(shè)計(jì)(低效,、不規(guī)范)所致，并且在測(cè)試,、開(kāi)發(fā)過(guò)程中缺乏有效的審核管理和流程規(guī)范,，導(dǎo)致SQL問(wèn)題不能及時(shí)有效解決。當(dāng)應(yīng)用發(fā)布上線以后,，爆發(fā)各種性能問(wèn)題和安全問(wèn)題,，DBA陷于各種救火工作。

質(zhì)量管理的核心思想是提前把事情做對(duì),，通過(guò)一定的質(zhì)量管理投入來(lái)分析SQL質(zhì)量,，優(yōu)化問(wèn)題SQL，將SQL問(wèn)題消滅在萌芽階段,，避免影響業(yè)務(wù)高效穩(wěn)定運(yùn)行,。

做哪些？

既然SQL質(zhì)量分析是很值得做的事情,，那要做哪些,？是將所有運(yùn)行的SQL均進(jìn)行分析？這樣量太大,，成本太高,。是隨機(jī)采樣SQL進(jìn)行分析？這樣覆蓋率有限,，很容易錯(cuò)過(guò)嚴(yán)重風(fēng)險(xiǎn),。

按照我的經(jīng)驗(yàn)，想把SQL質(zhì)控做好,，需要合理地分配時(shí)間和精力,，面面俱到不如重點(diǎn)突破,，把80%的資源花在能出關(guān)鍵效益的20%的方面。哪如何確定這20%的重點(diǎn)SQL呢,？可以從TopSQL入手,。 

從執(zhí)行次數(shù)、執(zhí)行時(shí)長(zhǎng),、消耗資源等不同維度,，提取TopSQL作為分析對(duì)象。提取Top 10,，Top 20還是Top 50,？每小時(shí)，每天還是每業(yè)務(wù)周期提取一次,？分析時(shí)采用專家模型還是機(jī)器學(xué)習(xí)模型,？展示時(shí)列表式，報(bào)表式,，圖形式還是告警式,？這些都可以隨著需求場(chǎng)景，靈活設(shè)定,。

如何做,？

通過(guò)采集、分析,、展示三大環(huán)節(jié),，就可以實(shí)現(xiàn)大規(guī)模多種數(shù)據(jù)庫(kù)熱點(diǎn)SQL質(zhì)量分析的目標(biāo)。

采集：實(shí)現(xiàn)按需拿到要分析的SQL,。需要包含采集任務(wù)管控,，按頻率調(diào)度，合適的采集腳本,。

分析：實(shí)現(xiàn)對(duì)SQL進(jìn)行全面分析,，判斷其有無(wú)風(fēng)險(xiǎn)，具體的風(fēng)險(xiǎn)情況,。需要包含SQL的詞法解析,，語(yǔ)義識(shí)別，提取支撐分析的元數(shù)據(jù),、統(tǒng)計(jì)信息,、執(zhí)行計(jì)劃等，以專家經(jīng)驗(yàn)?zāi)Ｐ?，機(jī)器學(xué)習(xí)模型,，或二者結(jié)合進(jìn)行風(fēng)險(xiǎn)分析。

展示：以適合的方式將SQL及其風(fēng)險(xiǎn)點(diǎn),，對(duì)象信息,、統(tǒng)計(jì)信息,、執(zhí)行計(jì)劃進(jìn)行展示。

1. 關(guān)鍵點(diǎn)是什么,？
在整個(gè)實(shí)現(xiàn)路徑中,，要做的事情很多，但有三個(gè)最為關(guān)鍵的要點(diǎn),，即精準(zhǔn)捕獲TopSQL,，全面深入的分析模型，豐富的信息及知識(shí)庫(kù),。我們對(duì)這些深入討論,。

2. Oracle中如何獲取TopSQL？
可以從AWR中提取,，可以從ASH中提取，也可以從shared pool（v$sql等視圖）中提取,，還可以通過(guò)日志分析提取等,，方式較多，考慮實(shí)現(xiàn)成本和效果,，推薦從AWR中提取,。

3. MySQL中如何獲取TopSQL？
可以分析慢日志提取,，可以分析通用日志提取,，也可以從performance schema中提取等，方式較多,，考慮實(shí)現(xiàn)成本和效果,，推薦從performance schema中提取。

4. DB2中如何獲取TopSQL,？
可以從sysibmadm下的視圖中提取,，可以從MON_GET系列表函數(shù)中提取，也可以通過(guò)日志解析提取等,，方式較多,，考慮實(shí)現(xiàn)成本和效果，推薦從sysibmadm下的視圖或MON_GET系列表函數(shù)中提取,。

5. SQL Server中如何獲取TopSQL,？
可以從dm_exec系列函數(shù)中提取，也可以通過(guò)日志解析提取等,，方式較多,，考慮實(shí)現(xiàn)成本和效果，推薦從dm_exec系列函數(shù)中提取,。

6. PostgreSQL中如何獲取TopSQL,？
可以從pg_stat_statements中提取,，也可以通過(guò)日志解析提取等，方式較多,，考慮實(shí)現(xiàn)成本和效果,，推薦從pg_stat_statements中提取。

7. OceanBase中如何獲取TopSQL,？
OceanBase因?yàn)槟壳肮_(kāi)的資料有限,，方式較少，已知的可以通過(guò)Plan Cache 系列視圖中提取,。

8. 如何做SQL解析,？
在拿到SQL后，首先要進(jìn)行SQL解析,，可以自研,，也可以基于開(kāi)源組件，比如Antlr,、JSqlParser,、Apache Calcite等二次開(kāi)發(fā)。要達(dá)成的目標(biāo),，都是對(duì)各種數(shù)據(jù)庫(kù)的SQL進(jìn)行詞法分析,、語(yǔ)法分析，生成語(yǔ)法樹(shù),。
 

上圖為Calcite架構(gòu)

無(wú)論哪種方式,，均需兼容Oracle、MySQL,、DB2,、SQL Server、PostgreSQL,、OceanBase等各種數(shù)據(jù)庫(kù)的SQL語(yǔ)法區(qū)別,，增強(qiáng)元數(shù)據(jù)特征，提取源生執(zhí)行計(jì)劃,。

這里舉個(gè)例子,，比如SQL語(yǔ)句：

在經(jīng)過(guò)解析后，生成的語(yǔ)法樹(shù)是下面的樣子,。

9. 如何做SQL分析,？

在SQL分析時(shí)，主流有兩種做法,，專家經(jīng)驗(yàn)方式,，機(jī)器學(xué)習(xí)方式。

專家經(jīng)驗(yàn)方式，如上圖,，主要就是根據(jù)專家經(jīng)驗(yàn),，將SQL可能存在的風(fēng)險(xiǎn)提煉出來(lái)，總結(jié)出每種風(fēng)險(xiǎn)的特征,、危害,、檢測(cè)算法，即規(guī)則,，最后形成規(guī)則庫(kù),，比如：

規(guī)則，是有效經(jīng)驗(yàn)的濃縮,，越豐富可以發(fā)現(xiàn)的風(fēng)險(xiǎn)越多,。應(yīng)支持用戶自定義規(guī)則。

在分析時(shí),，主要是用規(guī)則庫(kù)中每個(gè)規(guī)則算法對(duì)SQL進(jìn)行比對(duì),，確定出其風(fēng)險(xiǎn)點(diǎn)。還可以再根據(jù)風(fēng)險(xiǎn)點(diǎn)清單給SQL生成質(zhì)量評(píng)分,。

機(jī)器學(xué)習(xí)方式,，如上圖，主要就是通過(guò)Agent與環(huán)境不斷試錯(cuò)的過(guò)程的方式進(jìn)行交互,，尋找最優(yōu)決策策略,，以最大化與環(huán)境交互獲得的累計(jì)獎(jiǎng)勵(lì),。在數(shù)學(xué)上可建模為馬爾科夫決策過(guò)程（MDP,，Markov Decision Process），用于在系統(tǒng)狀態(tài)具有馬爾可夫性質(zhì)的環(huán)境中模擬智能體可實(shí)現(xiàn)的隨機(jī)策略與回報(bào),，要素包括狀態(tài),、動(dòng)作、策略和獎(jiǎng)勵(lì),。

實(shí)際執(zhí)行時(shí)間作為Reward進(jìn)行訓(xùn)練,，即執(zhí)行策略的調(diào)整。目標(biāo)是找到查詢執(zhí)行時(shí)間最小的執(zhí)行計(jì)劃,。

10. 為何要建立知識(shí)庫(kù),？

如果你長(zhǎng)期進(jìn)行SQL質(zhì)量分析、SQL優(yōu)化相關(guān)工作,，就會(huì)發(fā)現(xiàn),，SQL雖然可以千奇百怪，寫成各種各樣,，但其底層原理,，高效的原則卻是相同的。所以建立單位內(nèi)部SQL相關(guān)的知識(shí)庫(kù)，不僅能總結(jié)過(guò)去,，還可以指導(dǎo)未來(lái),。在分析出風(fēng)險(xiǎn)SQL后，給不擅長(zhǎng)優(yōu)化的同事提供資源支撐,。建議知識(shí)庫(kù)至少涵蓋SQL知識(shí)背景,、SQL優(yōu)化原則、SQL優(yōu)化原理,、SQL優(yōu)化示例,、SQL編寫規(guī)范等，并隨著業(yè)務(wù)發(fā)展不斷積累更新,。

11. 怎么樣支撐大規(guī)模多種數(shù)據(jù)庫(kù)的并SQL質(zhì)量分析,？
為了實(shí)現(xiàn)大規(guī)模多種數(shù)據(jù)庫(kù)并行的SQL質(zhì)量的評(píng)估和管控，要做到外部架構(gòu)松耦合,，內(nèi)部架構(gòu)高性能,、高可用、隨需擴(kuò)展,。比如可以考慮整個(gè)平臺(tái)部署架構(gòu)示意如下：

平臺(tái)工作區(qū),，即大規(guī)模多種數(shù)據(jù)庫(kù)熱點(diǎn)SQL質(zhì)量分析平臺(tái)，分為應(yīng)用層和數(shù)據(jù)層,。應(yīng)用層由反向應(yīng)用代理,、應(yīng)用服務(wù)、數(shù)據(jù)緩存組每種組件均無(wú)單故障點(diǎn),，并可水平擴(kuò)展,。數(shù)據(jù)層由數(shù)據(jù)代理、MySQL主從復(fù)制\集群組成,，每種組件均無(wú)單故障點(diǎn),，并可水平擴(kuò)展。各個(gè)組件的協(xié)作圖示意如下：

至此,，我們的整個(gè)方案思路和要點(diǎn)已經(jīng)介紹完畢,，有能力或精力的朋友們，可以嘗試落地實(shí)踐,，在SQL質(zhì)量分析和管控方面更近一步,。也可以采用成熟的SQL質(zhì)量管控平臺(tái)-SQM。SQM將SQL質(zhì)控按場(chǎng)景工具化,、自動(dòng)化,，和DevOps過(guò)程集成起來(lái)，可以高效的實(shí)現(xiàn)質(zhì)控目標(biāo),。

- end -