原理
SQL解析与优化是属于编译器范畴,和C等其他语言的解析没有本质的区别。其中分为,词法分析、语法和语义分析、优化、执行代码生成。对应到MySQL的部分,如下图
词法分析
SQL解析由词法分析和语法/语义分析两个部分组成。词法分析主要是把输入转化成一个个Token。其中Token中包含Keyword(也称symbol)和非Keyword。例如,SQL语句 select username from userinfo,在分析之后,会得到4个Token,其中有2个Keyword,分别为select和from:
关键字 | 非关键字 | 关键字 | 非关键字 |
---|---|---|---|
select | username | from | userinfo |
通常情况下,词法分析可以使用Flex来生成,但是MySQL并未使用该工具,而是手写了词法分析部分(据说是为了效率和灵活性,参考此文)。具体代码在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)},
语法分析
语法分析就是生成语法树的过程。这是整个解析过程中最精华,最复杂的部分,不过这部分MySQL使用了Bison来完成。a)语法分析树
SQL语句:
select username, ismale from userinfo where age > 20 and level > 5 and 1 = 1
对于未接触过编译器实现的同学,肯定会好奇如何才能生成这样的语法树。其背后的原理都是编译器的范畴,可以参考维基百科的一篇文章,以及该链接中的参考书籍从工程的角度来说,学会如何使用Bison去构建语法树,来解决实际问题,对我们的工作也许有更大帮助。下面我就以Bison为基础,探讨该过程。
b)MySQL语法分析树生成过程
全部的源码在sql/sql_yacc.yy中,在MySQL5.6中有17K行左右代码。这里列出涉及到SQL:
select username, ismale from userinfo where age > 20 and level > 5 and 1 = 1
select /*select语句入口*/:
select_init
{
LEX *lex= Lex;
lex->sql_command= SQLCOM_SELECT;
}
;
select_init:
SELECT_SYM /*select 关键字*/ 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 字句*/
| into select_from
| select_from into
;
select_from:
FROM join_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;
}
| FROM DUAL_SYM where_clause opt_limit_clause
/* oracle compatibility: oracle always requires FROM clause,
and DUAL is system table without 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 /*各种表达式*/
{
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 in rule expr: 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;
}
}
else if (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;
}
}
在大家浏览上述代码的过程,会发现Bison中嵌入了C++的代码。通过C++代码,把解析到的信息存储到相关对象中。例如表信息会存储到TABLE_LIST中,order_list存储order by子句里的信息,where字句存储在Item中。有了这些信息,再辅助以相应的算法就可以对SQL进行更进一步的处理了。
c)核心数据结构及其关系在SQL解析中,最核心的结构是SELECT_LEX,其定义在sql/sql_lex.h中。下面仅列出与上述例子相关的部分。
SQL特征生成
为了确保数据库,这一系统基础组件稳定、高效运行,业界有很多辅助系统。比如慢查询系统、中间件系统。这些系统采集、收到SQL之后,需要对SQL进行归类,以便统计信息或者应用相关策略。归类时,通常需要获取SQL特征。比如SQL:
select username, ismale from userinfo where age > 20 and level > 5;
SQL特征为:
select username, ismale from userinfo where age > ? and level > ?
业界著名的慢查询分析工具pt-query-digest,通过正则表达式实现这个功能但是这类处理办法Bug较多。接下来就介绍如何使用SQL解析,完成SQL特征的生成。
SQL特征生成分两部分组成。
a) 生成Token数组
b) 根据Token数组,生成SQL特征
首先回顾在词法解析章节,我们介绍了SQL中的关键字,并且每个关键字都有一个16位的整数对应,而非关键字统一用ident表示,其也对应了一个16位整数。如下表:
标识 | select | from | where | > | ? | and | ident |
---|---|---|---|---|---|---|---|
整数 | 728 | 448 | 878 | 463 | 893 | 272 | 476 |
将一个SQL转换成特征的过程:
原SQL | select | username | from | userinfo | where | age | > | 20 |
---|---|---|---|---|---|---|---|---|
SQL特征 | select | ident:length:value | from | ident:length:value | where | ident:length:value | > | ? |
在SQL解析过程中,可以很方便的完成Token数组的生成。而一旦完成Token数组的生成,就可以很简单的完成SQL特征的生成。SQL特征被广泛用于各个系统中,比如pt-query-digest需要根据特征对SQL归类,然而其基于正则表达式的实现有诸多bug。下面列举几个已知bug。
原始SQL | pt-query-digest生成的特征 | SQL解析器生成的特征 |
---|---|---|
select * from email_template2 where id = 1 | select * from mail_template? where id = ? | select * from email_template2 where id = ? |
REPLACE INTO a VALUES('INSERT INTO foo VALUES (1),(2)') | replace into a values(\'insert into foo values(?+) | replace into a values (?) |
因此可以看出SQL解析的优势是很明显的。
《数据库查询优化器的艺术:原理解析与SQL性能优化》