一文詳解SQL解析與應(yīng)用

數(shù)據(jù)庫(kù)作為核心的基礎(chǔ)組件，是需要重點(diǎn)保護(hù)的對(duì)象。任何一個(gè)線上的不慎操作，都有可能給數(shù)據(jù)庫(kù)帶來(lái)嚴(yán)重的故障，從而給業(yè)務(wù)造成巨大的損失。

為了避免這種損失，一般會(huì)在管理上下功夫，比如為研發(fā)人員制定數(shù)據(jù)庫(kù)開(kāi)發(fā)規(guī)范；新上線的SQL，需要DBA進(jìn)行審核；維護(hù)操作需要經(jīng)過(guò)領(lǐng)導(dǎo)審批等等。而且如果希望能夠有效地管理這些措施，需要有效的數(shù)據(jù)庫(kù)培訓(xùn)，還需要DBA細(xì)心的進(jìn)行SQL審核。很多中小型創(chuàng)業(yè)公司可以通過(guò)設(shè)定規(guī)范、進(jìn)行培訓(xùn)、完善審核流程來(lái)管理數(shù)據(jù)庫(kù)。

隨著美團(tuán)點(diǎn)評(píng)的業(yè)務(wù)不斷發(fā)展和壯大，上述措施的實(shí)施成本越來(lái)越高。如何更多的依賴技術(shù)手段，來(lái)提高效率，越來(lái)越受到重視。業(yè)界已有不少基于MySQL源碼開(kāi)發(fā)的SQL審核、優(yōu)化建議等工具，極大的減輕了DBA的SQL審核負(fù)擔(dān)。那么我們能否繼續(xù)擴(kuò)展MySQL的源碼，來(lái)輔助DBA和研發(fā)人員來(lái)進(jìn)一步提高效率呢？比如，更全面的SQL優(yōu)化功能；多維度的慢查詢分析；輔助故障分析等。要實(shí)現(xiàn)上述功能，其中最核心的技術(shù)之一就是SQL解析。

現(xiàn)狀與場(chǎng)景

SQL解析是一項(xiàng)復(fù)雜的技術(shù)，一般都是由數(shù)據(jù)庫(kù)廠商來(lái)掌握，當(dāng)然也有公司專門(mén)提供SQL解析的API。

由于這幾年MySQL數(shù)據(jù)庫(kù)中間件的興起，需要支持讀寫(xiě)分離、分庫(kù)分表等功能，就必須從SQL中抽出表名、庫(kù)名以及相關(guān)字段的值。因此像Java語(yǔ)言編寫(xiě)的Druid，C語(yǔ)言編寫(xiě)的MaxScale，Go語(yǔ)言編寫(xiě)的Kingshard等，都會(huì)對(duì)SQL進(jìn)行部分解析。而真正把SQL解析技術(shù)用于數(shù)據(jù)庫(kù)維護(hù)的產(chǎn)品較少，主要有如下幾個(gè)：

美團(tuán)點(diǎn)評(píng)開(kāi)源的SQLAdvisor。它基于MySQL原生態(tài)詞法解析，結(jié)合分析SQL中的where條件、聚合條件、多表Join關(guān)系給出索引優(yōu)化建議。

上述產(chǎn)品都有非常合適的應(yīng)用場(chǎng)景，在業(yè)界也被廣泛使用。但是SQL解析的應(yīng)用場(chǎng)景遠(yuǎn)遠(yuǎn)沒(méi)有被充分發(fā)掘，比如：

基于表粒度的慢查詢報(bào)表。比如，一個(gè)Schema中包含了屬于不同業(yè)務(wù)線的數(shù)據(jù)表，那么從業(yè)務(wù)線的角度來(lái)說(shuō)，其希望提供表粒度的慢查詢報(bào)表。

生成SQL特征。將SQL語(yǔ)句中的值替換成問(wèn)號(hào)，方便SQL歸類。雖然可以使用正則表達(dá)式實(shí)現(xiàn)相同的功能，但是其Bug較多，可以參考pt-query-digest。比如pt-query-digest中，會(huì)把遇到的數(shù)字都替換成“？”，導(dǎo)致無(wú)法區(qū)別不同數(shù)字后綴的表。

高危操作確認(rèn)與規(guī)避。比如，DBA不小心Drop數(shù)據(jù)表，而此類操作，目前還無(wú)有效的工具進(jìn)行回滾，尤其是大表，其后果將是災(zāi)難性的。

SQL合法性判斷。為了安全、審計(jì)、控制等方面的原因，美團(tuán)點(diǎn)評(píng)不會(huì)讓研發(fā)人員直接操作數(shù)據(jù)庫(kù)，而是提供RDS服務(wù)。尤其是對(duì)于數(shù)據(jù)變更，需要研發(fā)人員的上級(jí)主管進(jìn)行業(yè)務(wù)上的審批。如果研發(fā)人員，寫(xiě)了一條語(yǔ)法錯(cuò)誤的SQL，而RDS無(wú)法判斷該SQL是否合法，就會(huì)造成不必要的溝通成本。

因此為了讓所有有需要的業(yè)務(wù)都能方便地使用SQL解析功能，我們認(rèn)為應(yīng)該具有如下特性：

直接暴露SQL解析接口，使用盡量簡(jiǎn)單。比如：輸入SQL，則輸出表名、特征和優(yōu)化建議。

接口的使用不依賴于特定的語(yǔ)言，否則維護(hù)和使用的代價(jià)太高。比如：以HTTP等方式提供服務(wù)。

千里之行，始于足下，下面我先介紹下SQL的解析原理。

原理

SQL解析與優(yōu)化是屬于編譯器范疇，和C語(yǔ)言等其他語(yǔ)言的解析沒(méi)有本質(zhì)的區(qū)別。其中分為詞法分析、語(yǔ)法和語(yǔ)義分析、優(yōu)化、執(zhí)行代碼生成。對(duì)應(yīng)到MySQL的部分，如下圖：

SQL解析原理

1、詞法分析

SQL解析由詞法分析和語(yǔ)法/語(yǔ)義分析兩個(gè)部分組成。詞法分析主要是把輸入轉(zhuǎn)化成一個(gè)個(gè)Token。其中Token中包含Keyword（也稱symbol）和非Keyword。例如：SQL語(yǔ)句select username from userinfo，在分析之后，會(huì)得到4個(gè)Token，其中有2個(gè)Keyword，分別為select和from：

通常情況下，詞法分析可以使用Flex來(lái)生成。

但是MySQL并未使用該工具，而是手寫(xiě)了詞法分析部分。具體代碼在sql/lex.h和sql/sql_lex.cc文件中。

MySQL中的Keyword定義在sql/lex.h中，如下為部分Keyword：

詞法分析的核心代碼在sql/sql_lex.c文件中的MySQLLex→lex_one_Token，有興趣的同學(xué)可以下載源碼研究。

2、語(yǔ)法分析

語(yǔ)法分析就是生成語(yǔ)法樹(shù)的過(guò)程。這是整個(gè)解析過(guò)程中最精華、最復(fù)雜的部分，不過(guò)這部分MySQL使用了Bison來(lái)完成。即使如此，如何設(shè)計(jì)合適的數(shù)據(jù)結(jié)構(gòu)以及相關(guān)算法，去存儲(chǔ)和遍歷所有的信息，也是值得在這里研究的。

語(yǔ)法分析樹(shù)

SQL語(yǔ)句：

select username， ismale from userinfo where age 》 20 and level 》 5 and 1 = 1

會(huì)生成如下語(yǔ)法樹(shù)：

語(yǔ)法樹(shù)

對(duì)于未接觸過(guò)編譯器實(shí)現(xiàn)的同學(xué)，肯定會(huì)好奇如何才能生成這樣的語(yǔ)法樹(shù)，不過(guò)其背后的原理都是編譯器的范疇，大家可以參考維基百科的一篇文章，以及該鏈接中的參考書(shū)籍。本人也是在學(xué)習(xí)MySQL源碼過(guò)程中，閱讀了部分內(nèi)容。

由于編譯器涉及的內(nèi)容過(guò)多，本人經(jīng)歷和時(shí)間有限，不做過(guò)多探究。從工程的角度來(lái)說(shuō)，學(xué)會(huì)如何使用Bison去構(gòu)建語(yǔ)法樹(shù)，來(lái)解決實(shí)際問(wèn)題，對(duì)我們的工作也許有更大幫助。下面我就以Bison為基礎(chǔ)，探討該過(guò)程。

MySQL語(yǔ)法分析樹(shù)生成過(guò)程

全部的源碼在sql/sql_yacc.yy中，在MySQL5.6中有17K行左右代碼。這里列出涉及到SQL：

select username， ismale from userinfo where age 》 20 and level 》 5 and 1 = 1

解析過(guò)程的部分代碼摘錄出來(lái)。其實(shí)有了Bison之后，SQL解析的難度也沒(méi)有想象的那么大。特別是這里給出了解析的脈絡(luò)之后。

代碼示下：

上下拉動(dòng)可完整查看

在大家瀏覽上述代碼的過(guò)程，會(huì)發(fā)現(xiàn)Bison中嵌入了C++的代碼。通過(guò)C++代碼，把解析到的信息存儲(chǔ)到相關(guān)對(duì)象中。例如表信息會(huì)存儲(chǔ)到TABLE_LIST中，order_list存儲(chǔ)order by子句里的信息，where字句存儲(chǔ)在Item中。有了這些信息，再輔助以相應(yīng)的算法就可以對(duì)SQL進(jìn)行更進(jìn)一步的處理了。

核心數(shù)據(jù)結(jié)構(gòu)及其關(guān)系

在SQL解析中，最核心的結(jié)構(gòu)是SELECT_LEX，其定義在sql/sql_lex.h中。下面僅列出與上述例子相關(guān)的部分。

SQL解析樹(shù)結(jié)構(gòu)

上面圖示中，列名username、ismale存儲(chǔ)在item_list中，表名存儲(chǔ)在table_list中，條件存儲(chǔ)在where中。其中以where條件中的Item層次結(jié)構(gòu)最深，表達(dá)也較為復(fù)雜，如下圖所示：

where條件

SQL解析的應(yīng)用

為了更深入的了解SQL解析器，這里給出2個(gè)應(yīng)用SQL解析的例子：

1、無(wú)用條件去除

“無(wú)用條件去除”屬于優(yōu)化器的邏輯優(yōu)化范疇，僅僅根據(jù)SQL本身以及表結(jié)構(gòu)即可完成，其優(yōu)化的情況較多，代碼在sql/sql_optimizer.cc文件中的remove_eq_conds函數(shù)。為了避免過(guò)于繁瑣的描述，以及大段代碼的粘貼，這里通過(guò)圖片來(lái)分析以下四種情況：

1=1 and （m 》 3 and n 》 4）

1=2 and （m 》 3 and n 》 4）

1=1 or （m 》 3 and n 》 4）

1=2 or （m 》 3 and n 》 4）

無(wú)用條件去除a：

無(wú)用條件去除b

無(wú)用條件去除c

無(wú)用條件去除d

如果對(duì)其代碼實(shí)現(xiàn)有興趣的同學(xué)，需要對(duì)MySQL中的一個(gè)重要數(shù)據(jù)結(jié)構(gòu)Item類有所了解。因?yàn)槠浔容^復(fù)雜，所以MySQL官方文檔專門(mén)介紹了Item類。

參考鏈接：https://dev.mysql.com/doc/internals/en/item-class.html

阿里的MySQL小組也有類似的文章。如需更詳細(xì)的了解，就需要去查看源碼中sql/item_*等文件。

2、SQL特征生成

為了確保數(shù)據(jù)庫(kù)這一系統(tǒng)基礎(chǔ)組件穩(wěn)定、高效運(yùn)行，業(yè)界有很多輔助系統(tǒng)。比如慢查詢系統(tǒng)、中間件系統(tǒng)。這些系統(tǒng)采集、收到SQL之后，需要對(duì)SQL進(jìn)行歸類，以便統(tǒng)計(jì)信息或者應(yīng)用相關(guān)策略。歸類時(shí)，通常需要獲取SQL特征。比如SQL：

select username， ismale from userinfo where age 》 20 and level 》 5；

SQL特征為：

select username， ismale from userinfo where age 》 ？ and level 》 ？

業(yè)界著名的慢查詢分析工具pt-query-digest，通過(guò)正則表達(dá)式實(shí)現(xiàn)這個(gè)功能，但是這類處理辦法Bug較多。接下來(lái)就介紹如何使用SQL解析，完成SQL特征的生成。

SQL特征生成分兩部分組成：

生成Token數(shù)組；

根據(jù)Token數(shù)組，生成SQL特征。

首先回顧在詞法解析章節(jié)，我們介紹了SQL中的關(guān)鍵字，并且每個(gè)關(guān)鍵字都有一個(gè)16位的整數(shù)對(duì)應(yīng)，而非關(guān)鍵字統(tǒng)一用ident表示，其也對(duì)應(yīng)了一個(gè)16位整數(shù)。如下表：

將一個(gè)SQL轉(zhuǎn)換成特征的過(guò)程：

在SQL解析過(guò)程中，可以很方便的完成Token數(shù)組的生成。而一旦完成Token數(shù)組的生成，就可以很簡(jiǎn)單的完成SQL特征的生成。SQL特征被廣泛用于各個(gè)系統(tǒng)中，比如pt-query-digest需要根據(jù)特征對(duì)SQL歸類，然而其基于正則表達(dá)式的實(shí)現(xiàn)有諸多Bug。下面列舉幾個(gè)已知的Bug：

學(xué)習(xí)建議

最近，在對(duì)SQL解析器和優(yōu)化器探索的過(guò)程中，從一開(kāi)始的茫然無(wú)措到有章可循，也總結(jié)了一些心得體會(huì)，在這里跟大家分享一下：

首先，閱讀相關(guān)書(shū)籍，書(shū)籍能給我們一個(gè)系統(tǒng)的認(rèn)識(shí)解析器和優(yōu)化器的角度。但是該類針對(duì)MySQL的書(shū)籍市面上很少，目前中文作品可以看下《數(shù)據(jù)庫(kù)查詢優(yōu)化器的藝術(shù)：原理解析與SQL性能優(yōu)化》；

其次，要閱讀源碼，但是最好以某個(gè)版本為基礎(chǔ)，比如MySQL5.6.23，因?yàn)镾QL解析、優(yōu)化部分的代碼在不斷變化，尤其是在跨越大的版本時(shí)，改動(dòng)力度大；

再次，多使用GDB調(diào)試，驗(yàn)證自己的猜測(cè)，檢驗(yàn)閱讀質(zhì)量；

最后，需要寫(xiě)相關(guān)代碼驗(yàn)證，只有寫(xiě)出來(lái)了才能算真正的掌握。