GaussDB技術解讀系列之SQL Audit，面嚮應用開發的SQL審核工具

開發者的技術能力良莠不齊，DBA對資料庫知識的局限性導致爛SQL無處不在，而且隨著資料庫的不斷變更或演進，一些好的SQL也可能逐步變成需要優化的爛SQL, 我們要時刻不斷地找尋它們的蹤跡。 ...

本文分享自華為雲社區《GaussDB技術解讀系列之SQL Audit，面嚮應用開發的SQL審核工具》，作者：華為雲資料庫和應用遷移專家。

前言

我們先從一個SQL語句說起（以某傳統單機資料庫為例）。

也許這就是我們業務代碼中潛藏的一個SQL語句，對於一個普通開發者來說，這個語句編寫工整，邏輯清晰，沒有什麼問題，可以直接推到代碼倉中交付上線。但是一個有經驗的開發者或資料庫管理員可能會發現這個SQL存在諸多的優化點：

兩張表的id欄位是否有索引？
like語句不符合最左匹配原則，能否改寫？
test_1表where條件中的create_time判斷不符合單邊原則，無法走索引，可以改寫；
union會對結果集去除，效率較低，能否換成union all？
test_2表的id欄位被函數引用，也可能用不上索引，可以優化；
test_2表是否存在id和name聯合索引，能否加hint，指定特定索引提高查詢性能？

貌似經過上述的分析後，這個SQL可以煥然一新，在該資料庫上飛一樣地跑起來，但這就完了嗎？其實並沒有，在單機資料庫上也許已經優化到了極致，可當我們的資料庫是一個分散式資料庫呢？它可能又會帶來新的性能問題，我們要考慮where條件中的id是否是分佈鍵，concat函數是否會影響運算元的下推…...這一系列的問題都會產生。

這實際就是我們所面臨的現狀，開發者的技術能力良莠不齊，DBA對資料庫知識的局限性導致爛SQL無處不在，而且隨著資料庫的不斷變更或演進，一些好的SQL也可能逐步變成需要優化的爛SQL, 我們要時刻不斷地找尋它們的蹤跡。

SQL Audit審核工具介紹

華為內部有很多業務部門，對傳統單機資料庫、MySQL、PostgreSQL等各種資料庫都有深度的使用，也一直備受爛SQL的困擾，隨著GaussDB在內部業務系統的規模應用，現存SQL在GaussDB中能否高質量運行也面臨挑戰，於是我們開發了SQL Audit工具，根據公司內部各業務部門多年積累的SQL開發規範和GaussDB資料庫的優秀實踐，整理出SQL審核規則上百條，對命名規範、表結構/索引設計、SQL性能優化、分佈鍵及運算元下推等常見影響SQL質量的問題都可以做深入的分析和審核，同時我們又開發了一些插件，直接集成到開發的流水線中，自動從代碼倉獲取SQL語句，做到一鍵審核。

SQL審核的核心流程可以分為以下三個階段：

SQL獲取：即我們能從哪些渠道獲取到需要審核的SQL, 獲取能力決定了我們能否對開發中的代碼做更全面的審核；

SQL語法解析：是針對具體的每一條SQL做語法樹的生成和分析；

SQL規則審核：是拆解SQL語句的每一部分，和相關審核規則項逐一做匹配，找出待優化或風險點，最終形成審核報告。

SQL獲取

客戶通過SQL訪問資料庫的渠道多種多樣，客戶端工具、命令行、SQL腳本、應用代碼…...

代碼開發又可以採用JDBC、ODBC、底層API調用等各種方式，SQL語句既可以直接在代碼中拼接，也可以通過配置文件（如：Mybatis），還可以通過ORM框架（如：Hibernate ）訪問資料庫，所以如果想要獲取到客戶的全部SQL是一件非常困難的事情。

SQL Audit對當前大部分SQL使用場景進行了支持，而且還在持續擴大SQL能獲取的範圍，力求能夠全面地將客戶使用的所有SQL全部審核到，下圖是當前SQL Audit工具支持的SQL獲取範圍。

手動輸入

手動輸入為客戶提供了一個簡單、易操作的平臺，客戶可以隨時把自己編寫的SQL語句輸入到SQL Audit工具中進行審核，根據審核結果直接對語句做調整，同時也可以將一個.sql文件整體上傳上來，進行批量的審核。

源代碼

源代碼是爛SQL最主要的來源，但因其編程語言多種多樣（C/C++/JAVA/GO/PYTHON/SHELL…...），編寫方式也千奇百怪，所以很難將每種場景的SQL都獲取完整，我們將代碼中的SQL分成了三類：

1) 源碼拼接SQL

通過拼接的方式生成SQL語句，拼接的過程可能會引入很多變數，這種情況無法獲取到完整的SQL，所以通過靜態文件提取SQL的方式會有很大缺陷，SQL Audit工具支持對Java代碼做語法解析，提取裡面的SQL，對於其他語言的代碼目前暫不支持。

2) 無SQL的ORM框架

例如Hibernate、SQLAlchemy等這些ORM框架無法從代碼中獲取到SQL語句，SQLAudit工具提供了基於Java二進位改寫技術，在JVM運行時動態監聽JDBC API，獲取SQL語句。

3) 配置SQL的ORM

很多業務系統基於Mybatis框架搭建訪問資料庫的能力，Mybatis通過註解或配置文件的方式編寫SQL語句，SQL Audit工具能夠對Mybatis的註解和配置文件進行深度解析，提取SQL成功率達99%以上。

資料庫對象

資料庫表結構、索引、約束的設計以及存儲過程、函數等PL/SQL的編寫對資料庫的性能起決定性作用，SQL Audit工具可以連接到資料庫，獲取資料庫中的全部對象定義，從設計的規範性（如：命名規範、長度/大小寫限制）、合理性（如：索引是否合理）和性能等方面進行考量，給出審核建議。

資料庫日誌

為了更全面地獲取到發生在資料庫的SQL語句，從資料庫本身的日誌層面著手也是一個比較可行的方案，解析資料庫的redo、開啟資料庫審計日誌、查詢SQL緩存區等方式都能夠有效獲取到運行SQL，SQL Audit工具也支持通過資料庫日誌獲取SQL語句的能力。

流量抓取

為瞭解決從源代碼中無法獲取全部SQL的問題，我們開發了基於流量抓取的SQL審核能力，它能極大提升對SQL獲取的完整度。IP+埠作為資料庫對外的統一入口，基本可以包含客戶業務和運維所產生的全部SQL語句，通過對資料庫伺服器埠的旁路監聽，獲取到網路協議包，經過對資料庫網路協議解析和重覆SQL過濾，得到有效的SQL語句，最後將這些SQL傳入SQL Audit工具進行審核。

SQL解析

SQL解析的過程就是將SQL語句按照語法規則解析成語法樹的過程，一般的解析過程分為詞法解析和語法解析，然後生成語法樹，大部分對SQL語句分析的工具都是直接遍歷語法樹實現的，SQL Audit工具沒有直接解析語法樹，而是增加了一個處理過程，將語法樹解析成Java描述類，後面所有的審核規則都是基於這個語法描述類進行，這樣大大提高對審核規則的開發效率，同時降低了開發難度。

SQL審核

豐富的審核規則

審核的核心是審核規則，而審核規則的核心是對資料庫的理解+對客戶業務開發理解的實踐經驗總結，我們結合GaussDB資料庫的最佳實踐+公司內外部客戶的實際使用場景，整理出審核規則數百條，目前產品中已支持規則78條，包含了SQL開發過程中常見的規範和性能問題，後續會有更多的規則持續豐富到產品中。

SQL Audit同時提供了模板配置功能，客戶可以根據自身業務場景靈活地選擇需要審核的規則。

深度審核

SQL Audit審核流程如下圖所示：

當一個SQL輸入到SQL Audit中後，首先會對SQL進行語法解析，然後根據SQL中所依賴的表、視圖等對象，去資料庫中獲取元數據信息（列信息、索引信息等），如果這個SQL語句的性能可能受執行計劃的影響，則會再從資料庫中獲取該語句的執行計劃，綜合上述全部信息，逐一匹配每一個相關的規則進行審核，最終輸出全部違反規則項。

實踐案例

華為雲內部某系統有一部分的業務代碼是基於JAVA的Mybatis框架開發，在將資料庫替換到GaussDB的過程中有大量的SQL做了相容性改造，為保證改造後的SQL能夠高質量地在GaussDB資料庫中運行，該系統通過SQL Audit工具對整個代碼倉進行全面審核，同時在流水線中部署了SQL Audit審核插件，持續對增量代碼進行看護。SQL Audit發現了大量的不規範和低性能的SQL，提前規避了風險SQL流入到生產環境，開發人員根據SQL Audit的審核報告對代碼進行了優化，業務切換到GaussDB後持續穩定運行。

以其中一個任務為例，該任務涉及SQL總數有1881個，審核出有問題的SQL有300多個。