3---辦理業務的流程 · 思考mysql內核之初級系列

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">事項</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">Alex</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">數據庫</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">語句</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">alex從8樓到1樓辦理招商銀行賬戶開戶</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">Select id,name from t1 where id=1</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">語法分析</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">通過</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">通過</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">各種檢查</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">是否帶了身份證、招商銀行現在是否是辦公時間等等</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">T1表是否存在，id,name字段是否存在，是否有查詢權限等等。</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">查詢優化</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">從備選方案里面選擇：電梯直接下、走樓梯、先坐電梯上去逆行然后達到1樓，……</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">從備選方案中選擇：全表掃描、非聚集索引查找、聚集所引查找，……</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">確定方案</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">走電梯下去辦理</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">全表掃描</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">執行</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">獲取電梯這個互斥資源，到達銀行取得號碼排隊，……</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">取得第一條記錄，判斷是否滿足id=1的條件，如果符合則丟入結果集。對所有的記錄執行一遍。將結果集返回用戶</td></tr></tbody></table> ” ? 小張：“這個問題問得好，確實我們要維護很多信息。比如開戶的過程中，我們需要維護類似的信息。在數據庫中是類似的，mysql創建每個線程來響應對應的1個客戶的請求。該線程維護的這個結構為該進程所獨有，生命周期是線程的生命周期。這里面保存了重要的客戶的申請信息Lex，鎖信息等等。具體的字段定義見sql_class.h。”