3—办理业务的流程
最后更新于：2022-04-01 16:04:42
Alex第二天带了身份到银行找了小张，要办理个开户业务。
## 1）语法结构Lex
Alex：“小张，我要开个账户。”
小张：“麻烦填写个开户申请单。”
Alex：“好的，我顺便问个问题，开户要填写开户申请单，兑换港币也要写申请单，汇款等也需要填写，这里面既有共性，又有差异性，是如何管理的。”
小张：“嗯，差不多，都是提供写一些表单填写，假设申请创建表，那么就会用到create_info申请表（数据结构），如下面的代码。
~~~
create:
CREATE opt_table_options TABLE_SYM opt_if_not_exists table_ident
{
THD *thd= YYTHD;
LEX *lex= thd->lex;
lex->sql_command= SQLCOM_CREATE_TABLE;
if (!lex->select_lex.add_table_to_list(thd, $5, NULL,
TL_OPTION_UPDATING,
TL_WRITE))
MYSQL_YYABORT;
lex->alter_info.reset();
lex->col_list.empty();
lex->change=NullS;
bzero((char*) &lex->create_info,sizeof(lex->create_info));
lex->create_info.options=$2 | $4;
lex->create_info.db_type= ha_default_handlerton(thd);
lex->create_info.default_table_charset= NULL;
lex->name.str= 0;
lex->name.length= 0;
}
~~~
而某个类型的select需要填写current_select申请表，select_lex申请表。见下面的代码：
~~~
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;
}
~~~
”
Alex：“嗯，差不多。如果alter，insert等字句，也会使用到相应的数据结构，维护了一个数据结构LEX，里面有各种各样的字段，根据用户的请求，会填充相应的字段。当然这里面也会有共性的内容，比如身份证复印信息等等。”
小张：“是这样的，在输入的语句转化为LEX结构这一块比较简单，通过阅读yy文件就可以了，但是最好设置断点调试一些重要的函数，比如下面的代码中，有必要在mysql_init_select函数的实现体设置断点调试该函数的功能，该函数的实现体见sql/sql_parse.cpp文件，同时建议将该文件中的各个函数看一下。另外强调一下，yy文件对应的cpp文件设置断点默认情况是无效的，需要特殊设置调试lib，这里不推荐调试。掌握yy文件以及sql_parse.cpp文件就完全满足了。这里不列出每个字段的含义，建议直接查看LEX的定义文件，使用的代码是5.1.7，文件为sql/sql_lex.h
~~~
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
;
~~~
”
## 2）THD结构
Alex：“好的，那我再继续深问下，办理业务的这里内容给你了，你如果在办理的过程中，做到有序。”
小张：“好吧，我们现在先假设一个场景，alex从8楼到1楼办理招商银行账户开户，这样的一个行为，alex如何执行的，同时假想下数据库可能是如何执行的。”
Alex：“好的，如下表。从表中我们可以看出，alex在执行该操作的过程中需要维护很多信息，这些信息数据库是怎么维护的呢，比如执行查找的时候，数据库表t1正在被其他用户上了X锁。
<table class="MsoTableGrid" style="border-collapse: collapse; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext;" border="1" cellspacing="0" cellpadding="0"><tbody><tr style="mso-yfti-irow: 0; mso-yfti-firstrow: yes;"><td style="padding-right: 5.4pt; padding-left: 5.4pt; padding-bottom: 0cm; width: 142pt; padding-top: 0cm; background-color: transparent; mso-border-alt: solid windowtext .5pt; border: windowtext 1pt solid;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="font-size: medium;"><span style="color: blue; font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">事项</span><span style="color: blue;" lang="EN-US"/></span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: windowtext 1pt solid; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="color: blue;" lang="EN-US"><span style="font-size: small;"><span style="font-size: medium;">Alex</span></span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: windowtext 1pt solid; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="font-size: medium;"><span style="color: blue; font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">数据库</span><span style="color: blue;" lang="EN-US"/></span></span></p></td></tr><tr style="mso-yfti-irow: 1;"><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 142pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">语句</span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="font-size: medium;"><span lang="EN-US">alex</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">从</span><span lang="EN-US">8</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">楼到</span><span lang="EN-US">1</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">楼办理招商银行账户开户</span></span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: medium;">Select id,name from t1 where id=1</span></span></p></td></tr><tr style="mso-yfti-irow: 2;"><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 142pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">语法分析</span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">通过</span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">通过</span></span></p></td></tr><tr style="mso-yfti-irow: 3;"><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 142pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">各种检查</span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">是否带了身份证、招商银行现在是否是办公时间等等</span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="font-size: medium;"><span lang="EN-US">T1</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">表是否存在，</span><span lang="EN-US">id,name</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">字段是否存在，是否有查询权限等等。</span></span></span></p></td></tr><tr style="mso-yfti-irow: 4;"><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 142pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">查询优化</span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="font-size: medium;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">从备选方案里面选择：电梯直接下、走楼梯、先坐电梯上去逆行然后达到</span><span lang="EN-US">1</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">楼，……</span></span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">从备选方案中选择：全表扫描、非聚集索引查找、聚集所引查找，……</span></span></p></td></tr><tr style="mso-yfti-irow: 5;"><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 142pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">确定方案</span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">走电梯下去办理</span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">全表扫描</span></span></p></td></tr><tr style="mso-yfti-irow: 6; mso-yfti-lastrow: yes;"><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 142pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">执行</span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><span style="font-size: medium;">获取电梯这个互斥资源，到达银行取得号码排队，……</span></span></p></td><td style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ffffff; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ffffff; width: 142.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" width="189" valign="top"><p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="font-size: medium;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">取得第一条记录，判断是否满足</span><span lang="EN-US">id=1</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">的条件，如果符合则丢入结果集。对所有的记录执行一遍。将结果集返回用户</span></span></span></p></td></tr></tbody></table>
”
小张：“这个问题问得好，确实我们要维护很多信息。比如开户的过程中，我们需要维护类似的信息。在数据库中是类似的，mysql创建每个线程来响应对应的1个客户的请求。该线程维护的这个结构为该进程所独有，生命周期是线程的生命周期。这里面保存了重要的客户的申请信息Lex，锁信息等等。具体的字段定义见sql_class.h。”