導(dǎo)讀

數(shù)據(jù)庫(kù)作為核心的基礎(chǔ)組件，是需要重點(diǎn)保護(hù)的對(duì)象著恩。任何一個(gè)線上的不慎操作院尔，都有可能給數(shù)據(jù)庫(kù)帶來(lái)嚴(yán)重的故障，從而給業(yè)務(wù)造成巨大的損失喉誊。為了避免這種損失邀摆，一般會(huì)在管理上下功夫。比如為研發(fā)人員制定數(shù)據(jù)庫(kù)開發(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源碼開發(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)然也有公司專門提供SQL解析的API鹿寨。由于這幾年MySQL數(shù)據(jù)庫(kù)中間件的興起，需要支持讀寫分離薪夕、分庫(kù)分表等功能脚草，就必須從SQL中抽出表名、庫(kù)名以及相關(guān)字段的值原献。因此像Java語(yǔ)言編寫的Druid馏慨，C語(yǔ)言編寫的MaxScale，Go語(yǔ)言編寫的Kingshard等姑隅，都會(huì)對(duì)SQL進(jìn)行部分解析写隶。而真正把SQL解析技術(shù)用于數(shù)據(jù)庫(kù)維護(hù)的產(chǎn)品較少，主要有如下幾個(gè)：

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

去哪兒開源的Inception。側(cè)重于根據(jù)內(nèi)置的規(guī)則趁矾，對(duì)SQL進(jìn)行審核耙册。

阿里的Cloud DBA。根據(jù)官方文檔介紹毫捣，其也是提供SQL優(yōu)化建議和改寫详拙。

上述產(chǎn)品都有非常合適的應(yīng)用場(chǎng)景，在業(yè)界也被廣泛使用培漏。但是SQL解析的應(yīng)用場(chǎng)景遠(yuǎn)遠(yuǎn)沒有被充分發(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ā)人員蜒茄，寫了一條語(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的解析原理。

在這里順便給大家推薦一個(gè)架構(gòu)交流群：617434785变隔，里面會(huì)分享一些資深架構(gòu)師錄制的視頻錄像：有Spring规伐，MyBatis，Netty源碼分析匣缘，高并發(fā)猖闪、高性能、分布式孵户、微服務(wù)架構(gòu)的原理萧朝，JVM性能優(yōu)化這些成為架構(gòu)師必備的知識(shí)體系。還能領(lǐng)取免費(fèi)的學(xué)習(xí)資源夏哭。相信對(duì)于已經(jīng)工作和遇到技術(shù)瓶頸的碼友检柬，在這個(gè)群里會(huì)有你需要的內(nèi)容。

原理

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

圖1 SQL解析原理

詞法分析

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并未使用該工具切揭，而是手寫了詞法分析部分（據(jù)說(shuō)是為了效率和靈活性狞甚，參考此文）。具體代碼在sql/lex.h和sql/sql_lex.cc文件中廓旬。

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

{ "&&",???????SYM(AND_AND_SYM)},

{ "<",????????SYM(LT)},

{ "<=",???????SYM(LE)},

{ "<>",???????SYM(NE)},

{ "!=",???????SYM(NE)},

{ "=",????????SYM(EQ)},

{ ">",????????SYM(GT_SYM)},

{ ">=",???????SYM(GE)},

{ "<<",???????SYM(SHIFT_LEFT)},

{ ">>",???????SYM(SHIFT_RIGHT)},

{ "<=>",???????SYM(EQUAL_SYM)},

{ "ACCESSIBLE",???SYM(ACCESSIBLE_SYM)},

{ "ACTION",?????SYM(ACTION)},

{ "ADD",???????SYM(ADD)},

{ "AFTER",??????SYM(AFTER_SYM)},

{ "AGAINST",?????SYM(AGAINST)},

{ "AGGREGATE",????SYM(AGGREGATE_SYM)},

{ "ALL",???????SYM(ALL)},

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

語(yǔ)法分析

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

a）語(yǔ)法分析樹

SQL語(yǔ)句:

selectusername,?ismalefromuserinfowhereage?>?20?andlevel>?5?and?1?=?1

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

圖2 語(yǔ)法樹

對(duì)于未接觸過(guò)編譯器實(shí)現(xiàn)的同學(xué)离福，肯定會(huì)好奇如何才能生成這樣的語(yǔ)法樹。其背后的原理都是編譯器的范疇炼蛤，可以參考維基百科的一篇文章妖爷，以及該鏈接中的參考書籍。本人也是在學(xué)習(xí)MySQL源碼過(guò)程中理朋，閱讀了部分內(nèi)容絮识。由于編譯器涉及的內(nèi)容過(guò)多，本人經(jīng)歷和時(shí)間有限嗽上，不做過(guò)多探究次舌。從工程的角度來(lái)說(shuō)，學(xué)會(huì)如何使用Bison去構(gòu)建語(yǔ)法樹兽愤，來(lái)解決實(shí)際問(wèn)題彼念，對(duì)我們的工作也許有更大幫助。下面我就以Bison為基礎(chǔ)浅萧，探討該過(guò)程逐沙。

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

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

selectusername,?ismalefromuserinfowhereage?>?20?andlevel>?5?and?1?=?1

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

select/*select語(yǔ)句入口*/:

??????????select_init??

??????????{??

????????????LEX?*lex=?Lex;??

????????????lex->sql_command=?SQLCOM_SELECT;??

??????????}??

????????;??

select_init:??

SELECT_SYM?/*select關(guān)鍵字*/?select_init2

????????|?'('?select_paren?')'?union_opt??

????????;??

select_init2:??

??????????select_part2??

??????????{?

????????????LEX?*lex=?Lex;??

????????????SELECT_LEX?*?sel=?lex->current_select;??

????????????if?(lex->current_select->set_braces(0))??

????????????{??

??????????????my_parse_error(ER(ER_SYNTAX_ERROR));??

??????????????MYSQL_YYABORT;??

????????????}??

????????????if?(sel->linkage?==?UNION_TYPE?&&??

????????????????sel->master_unit()->first_select()->braces)??

????????????{??

??????????????my_parse_error(ER(ER_SYNTAX_ERROR));??

??????????????MYSQL_YYABORT;??

????????????}??

??????????}??

??????????union_clause??

????????;?

select_part2:??

??????????{??

????????????LEX?*lex=?Lex;??

????????????SELECT_LEX?*sel=?lex->current_select;??

????????????if?(sel->linkage?!=?UNION_TYPE)??

??????????????mysql_init_select(lex);??

????????????lex->current_select->parsing_place=?SELECT_LIST;??

??????????}??

??????????select_options?select_item_list?/*解析列名*/??

??????????{??

Select->parsing_place=?NO_MATTER;

??????????}??

??????????select_into?select_lock_type??

????????;??

select_into:??

??????????opt_order_clause?opt_limit_clause?{}??

|into

|?select_from?/*from字句*/

|intoselect_from

|?select_frominto

????????;??

select_from:??

FROMjoin_table_list?/*解析表名*/?where_clause?/*where字句*/?group_clause?having_clause

??????????opt_order_clause?opt_limit_clause?procedure_analyse_clause??

??????????{??

Select->context.table_list=

Select->context.first_name_resolution_table=

Select->table_list.first;

??????????}??

|FROMDUAL_SYM?where_clause?opt_limit_clause

/*?oracle?compatibility:?oracle?always?requiresFROMclause,

and?DUALissystemtablewithout?fields.

Is"SELECT?1?FROM?DUAL"?any?better?than?"SELECT?1"??

??????????Hmmm?:)?*/??

????????;??

where_clause:??

/*?empty?*/??{Select->where=?0;?}

|WHERE

??????????{??

Select->parsing_place=?IN_WHERE;

??????????}??

??????????expr?/*各種表達(dá)式*/??

??????????{??

SELECT_LEX?*select=Select;

select->where=?$3;

select->parsing_place=?NO_MATTER;

????????????if?($3)??

??????????????$3->top_level_item();??

??????????}??

????????;??

/*?all?possible?expressions?*/??

expr:??

???????????|?expr?and?expr?%prec?AND_SYM??

??????????{??

/*?See?comments?inruleexpr:?expr?or?expr?*/

????????????Item_cond_and?*item1;??

????????????Item_cond_and?*item3;??

????????????if?(is_cond_and($1))??

????????????{??

??????????????item1=?(Item_cond_and*)?$1;??

??????????????if?(is_cond_and($3))??

??????????????{??

????????????????item3=?(Item_cond_and*)?$3;??

????????????????/*??

??????????????????(X1?AND?X2)?AND?(Y1?AND?Y2)?==>?AND?(X1,?X2,?Y1,?Y2)??

????????????????*/?

?????????????????item3->add_at_head(item1->argument_list());??

????????????????$$?=?$3;??

??????????????}??

else

??????????????{??

????????????????/*??

??????????????????(X1?AND?X2)?AND?Y?==>?AND?(X1,?X2,?Y)??

????????????????*/??

item1->add($3);

????????????????$$?=?$1;??

??????????????}??

????????????}??

elseif?(is_cond_and($3))

????????????{??

??????????????item3=?(Item_cond_and*)?$3;??

??????????????/*??

????????????????X?AND?(Y1?AND?Y2)?==>?AND?(X,?Y1,?Y2)??

??????????????*/??

??????????????item3->add_at_head($1);??

??????????????$$?=?$3;??

????????????}??

else

????????????{??

??????????????/*?X?AND?Y?*/??

??????????????$$?=?new?(YYTHD->mem_root)?Item_cond_and($1,?$3);??

??????????????if?($$?==?NULL)??

????????????????MYSQL_YYABORT;??

????????????}??

??????????}??

在大家瀏覽上述代碼的過(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)一步的處理了江醇。

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

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

圖3 SQL解析樹結(jié)構(gòu)

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

圖4 where條件

SQL解析的應(yīng)用

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

無(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)分析以下四種情況：

a）1=1 and (m > 3 and n > 4)

b）1=2 and (m > 3 and n > 4)

c）1=1 or (m > 3 and n > 4)

d）1=2 or (m > 3 and n > 4)

圖5 無(wú)用條件去除a

圖6 無(wú)用條件去除b

圖7 無(wú)用條件去除c

圖8 無(wú)用條件去除d

如果對(duì)其代碼實(shí)現(xiàn)有興趣的同學(xué)，需要對(duì)MySQL中的一個(gè)重要數(shù)據(jù)結(jié)構(gòu)Item類有所了解粗井。因?yàn)槠浔容^復(fù)雜尔破，所以MySQL官方文檔，專門介紹了Item類浇衬。阿里的MySQL小組懒构，也有類似的文章。如需更詳細(xì)的了解耘擂，就需要去查看源碼中sql/item_*等文件胆剧。

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：

selectusername,?ismalefromuserinfowhereage?>?20?andlevel>?5诞外；

SQL特征為：

selectusername,?ismalefromuserinfowhereage?>???andlevel>??

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

SQL特征生成分兩部分組成铝条。

a) 生成Token數(shù)組

b) 根據(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ò)程中鉴腻，從一開始的茫然無(wú)措到有章可循，也總結(jié)了一些心得體會(huì)百揭，在這里跟大家分享一下爽哎。

首先，閱讀相關(guān)書籍器一，書籍能給我們一個(gè)系統(tǒng)的認(rèn)識(shí)解析器和優(yōu)化器的角度课锌。但是該類針對(duì)MySQL的書籍市面上很少，目前中文作品可以看下《數(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ì)量。

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