MySQL中關(guān)于prepare原理的詳解

這篇文章主要介紹了mysql prepare的相關(guān)內(nèi)容，包括prepare的產(chǎn)生，在服務(wù)器端的執(zhí)行過程，以及jdbc對prepare的處理以及相關(guān)測試，需要的朋友可以了解下。希望對大家有所幫助。

Prepare的好處?

??? Prepare SQL產(chǎn)生的原因。首先從mysql服務(wù)器執(zhí)行sql的過程開始講起，SQL執(zhí)行過程包括以下階段 詞法分析->語法分析->語義分析->執(zhí)行計劃優(yōu)化->執(zhí)行。詞法分析->語法分析這兩個階段我們稱之為硬解析。詞法分析識別sql中每個詞，語法分析解析SQL語句是否符合sql語法，并得到一棵語法樹（Lex）。對于只是參數(shù)不同，其他均相同的sql，它們執(zhí)行時間不同但硬解析的時間是相同的。而同一SQL隨著查詢數(shù)據(jù)的變化，多次查詢執(zhí)行時間可能不同，但硬解析的時間是不變的。對于sql執(zhí)行時間較短，sql硬解析的時間占總執(zhí)行時間的比率越高。而對于淘寶應(yīng)用的絕大多數(shù)事務(wù)型SQL，查詢都會走索引，執(zhí)行時間都比較短。因此淘寶應(yīng)用db sql硬解析占的比重較大。?

??? Prepare的出現(xiàn)就是為了優(yōu)化硬解析的問題。Prepare在服務(wù)器端的執(zhí)行過程如下

?1）? Prepare 接收客戶端帶”?”的sql, 硬解析得到語法樹(stmt->Lex), 緩存在線程所在的preparestatement cache中。此cache是一個HASH MAP. Key為stmt->id. 然后返回客戶端stmt->id等信息。

?2）? Execute 接收客戶端stmt->id和參數(shù)等信息。注意這里客戶端不需要再發(fā)sql過來。服務(wù)器根據(jù)stmt->id在preparestatement cache中查找得到硬解析后的stmt, 并設(shè)置參數(shù)，就可以繼續(xù)后面的優(yōu)化和執(zhí)行了。

??? Prepare在execute階段可以節(jié)省硬解析的時間。如果sql只執(zhí)行一次，且以prepare的方式執(zhí)行，那么sql執(zhí)行需兩次與服務(wù)器交互（Prepare和execute）, 而以普通（非prepare）方式，只需要一次交互。這樣使用prepare帶來額外的網(wǎng)絡(luò)開銷，可能得不償失。我們再來看同一sql執(zhí)行多次的情況，比如以prepare方式執(zhí)行10次，那么只需要一次硬解析。這時候? 額外的網(wǎng)絡(luò)開銷就顯得微乎其微了。因此prepare適用于頻繁執(zhí)行的SQL。

??? Prepare的另一個作用是防止sql注入，不過這個是在客戶端jdbc通過轉(zhuǎn)義實現(xiàn)的，跟服務(wù)器沒有關(guān)系。
硬解析的比重

?? 壓測時通過perf 得到的結(jié)果，硬解析相關(guān)的函數(shù)比重都比較靠前（MYSQLparse 4.93%, lex_one_token 1.79%, lex_start 1.12%）總共接近8%。因此，服務(wù)器使用prepare是可以帶來較多的性能提升的。

jdbc與prepare?

? jdbc服務(wù)器端的參數(shù)：

?? useServerPrepStmts：默認(rèn)為false. 是否使用服務(wù)器prepare開關(guān)

?jdbc客戶端參數(shù)：

?? cachePrepStmts：默認(rèn)false.是否緩存prepareStatement對象。每個連接都有一個緩存，是以sql為唯一標(biāo)識的LRU cache. 同一連接下，不同stmt可以不用重新創(chuàng)建prepareStatement對象。

? prepStmtCacheSize：LRU cache中prepareStatement對象的個數(shù)。一般設(shè)置為最常用sql的個數(shù)。

? prepStmtCacheSqlLimit：prepareStatement對象的大小。超出大小不緩存。

?Jdbc對prepare的處理過程：?

useServerPrepStmts=true時Jdbc對prepare的處理

? 1）? 創(chuàng)建PreparedStatement對象，向服務(wù)器發(fā)送COM_PREPARE命令，并傳送帶問號的sql. 服務(wù)器返回jdbc stmt->id等信息

? 2）? 向服務(wù)器發(fā)送COM_EXECUTE命令,并傳送參數(shù)信息。

?useServerPrepStmts=false時Jdbc對prepare的處理

? 1）? 創(chuàng)建PreparedStatement對象，此時不會和服務(wù)器交互。

? 2） 根據(jù)參數(shù)和PreparedStatement對象拼接完整的SQL，向服務(wù)器發(fā)送QUERY命令

? 我們再看參數(shù)cachePrepStmts打開時在useServerPrepStmts為true或false時，均緩存PreparedStatement對象。只不過useServerPrepStmts為的true緩存PreparedStatement對象包含服務(wù)器的stmt->id等信息，也就是說如果重用了PreparedStatement對象，那么就省去了和服務(wù)器通訊（COM_PREPARE命令）的開銷。而useServerPrepStmts=false是，開啟cachePrepStmts緩存PreparedStatement對象只是簡單的sql解析信息，因此此時開啟cachePrepStmts意義不是太大。

我們來開看一段java代碼?

Connection?con?=?null;  ??????PreparedStatement?ps?=?null;  ??????String?sql?=?"select?*?from?user?where?id=?";  ??????ps?=?con.prepareStatement(sql);??????  ??????ps.setInt(1,?1);????????  ??????ps.executeQuery();??????  ??????ps.close();??????  ??????ps?=?con.prepareStatement(sql);??????  ??????ps.setInt(1,?3);??????  ??????ps.executeQuery();??????  ??????ps.close();

這段代碼在同一會話中兩次prepare執(zhí)行同一語句，并且之間有ps.close();

??? useServerPrepStmts=false時，服務(wù)器會兩次硬解析同一SQL。

?? useServerPrepStmts=true, cachePrepStmts=false時服務(wù)器仍然會兩次硬解析同一SQL。

?? useServerPrepStmts=true, cachePrepStmts=true時服務(wù)器只會硬解析一次SQL。

?? 如果兩次prepare之間沒有ps.close();那么cachePrepStmts=true，cachePrepStmts=false也只需一次硬解析.?

?? 因此，客戶端對同一sql,頻繁分配和釋放PreparedStatement對象的情況下，開啟cachePrepStmts參數(shù)是很有必要的。

測試

? 1)做了一個簡單的測試，主要測試prepare的效果和useServerPrepStmts參數(shù)的影響.????

cnt?=?5000;  ????//?no?prepare  ????String?sql?=?"select?biz_order_id,out_order_id,seller_nick,buyer_nick,seller_id,buyer_id,auction_id,auction_title,auction_price,buy_amount,biz_type,sub_biz_type,fail_reason,pay_status,logistics_status,out_trade_status,snap_path,gmt_create,status,ifnull(buyer_rate_status,?4)?buyer_rate_status?from?tc_biz_order_0030?where?"?+  ????"parent_id?=?594314511722841?or?parent_id?=547667559932641;";  ????begin?=?new?Date();  ????System.out.println("begin:"?+?df.format(begin));  ????stmt?=?con.createStatement();  ????for?(int?i?=?0;?i?<p>經(jīng)多次采樣測試結(jié)果如下</p>

結(jié)論：

useServerPrepStmts=true時，prepare提升7%；

useServerPrepStmts=false時，prepare與非prepare性能相當(dāng)。

如果將語句簡化為select * from tc_biz_order_0030 where parent_id =?。那么測試的結(jié)論useServerPrepStmts=true時，prepare僅提升2%；sql越簡單硬解析的時間就越少，prepare的提升就越少。

注意：這個測試是在單個連接，單條sql的理想情況下進(jìn)行的，線上會出現(xiàn)多連接多sql,還有sql執(zhí)行頻率，sql的復(fù)雜程度等不同，因此prepare的提升效果會隨具體環(huán)境而變化。

2）prepare 前后的perf top 對比?

以下為非prepare

6.46%??mysqld?mysqld???????[.]?_Z10MYSQLparsePv  ???3.74%??mysqld?libc-2.12.so????[.]?__memcpy_ssse3  ???2.50%??mysqld?mysqld???????[.]?my_hash_sort_utf8  ???2.15%??mysqld?mysqld???????[.]?cmp_dtuple_rec_with_match  ???2.05%??mysqld?mysqld???????[.]?_ZL13lex_one_tokenPvS_  ???1.46%??mysqld?mysqld???????[.]?buf_page_get_gen  ???1.34%??mysqld?mysqld???????[.]?page_cur_search_with_match  ???1.31%??mysqld?mysqld???????[.]?_ZL14build_templateP19row_prebuilt_structP3THDP5TABLEj  ???1.24%??mysqld?mysqld???????[.]?rec_init_offsets  ???1.11%??mysqld?libjemalloc.so.1??[.]?free  ???1.09%??mysqld?mysqld???????[.]?rec_get_offsets_func  ???1.01%??mysqld?libjemalloc.so.1??[.]?malloc  ???0.96%??mysqld?libc-2.12.so????[.]?__strlen_sse42  ???0.93%??mysqld?mysqld???????[.]?_ZN4JOIN8optimizeEv  ???0.91%??mysqld?mysqld???????[.]?_ZL15get_hash_symbolPKcjb  ???0.88%??mysqld?mysqld???????[.]?row_search_for_mysql  ???0.86%??mysqld?[kernel.kallsyms]??[k]?tcp_recvmsg

以下為perpare?

3.46%??mysqld?libc-2.12.so????[.]?__memcpy_ssse3  ???2.32%??mysqld?mysqld???????[.]?cmp_dtuple_rec_with_match  ???2.14%??mysqld?mysqld???????[.]?_ZL14build_templateP19row_prebuilt_structP3THDP5TABLEj  ???1.96%??mysqld?mysqld???????[.]?buf_page_get_gen  ???1.66%??mysqld?mysqld???????[.]?page_cur_search_with_match  ???1.54%??mysqld?mysqld???????[.]?row_search_for_mysql  ???1.44%??mysqld?mysqld???????[.]?btr_cur_search_to_nth_level  ???1.41%??mysqld?libjemalloc.so.1??[.]?free  ???1.35%??mysqld?mysqld???????[.]?rec_init_offsets  ???1.32%??mysqld?[kernel.kallsyms]??[k]?kfree  ???1.14%??mysqld?libjemalloc.so.1??[.]?malloc  ???1.08%??mysqld?[kernel.kallsyms]??[k]?fget_light  ???1.05%??mysqld?mysqld???????[.]?rec_get_offsets_func  ???0.99%??mysqld?mysqld???????[.]?_ZN8Protocol24send_result_set_metadataEP4ListI4ItemEj  ???0.90%??mysqld?mysqld???????[.]?sync_array_print_long_waits  ???0.87%??mysqld?mysqld???????[.]?page_rec_get_n_recs_before  ???0.81%??mysqld?mysqld???????[.]?_ZN4JOIN8optimizeEv  ???0.81%??mysqld?libc-2.12.so????[.]?__strlen_sse42  ???0.78%??mysqld?mysqld???????[.]?_ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array  ???0.72%??mysqld?[kernel.kallsyms]??[k]?tcp_recvmsg  ???0.63%??mysqld?libpthread-2.12.so?[.]?__pthread_getspecific_internal  ???0.63%??mysqld?[kernel.kallsyms]??[k]?sk_run_filter  ???0.60%??mysqld?mysqld???????[.]?_Z19find_field_in_tableP3THDP5TABLEPKcjbPj  ???0.60%??mysqld?mysqld???????[.]?page_check_dir  ???0.57%??mysqld?mysqld???????[.]?_Z16dispatch_command19enum_server_commandP3THDP

?對比可以發(fā)現(xiàn) MYSQLparse lex_one_token在prepare時已優(yōu)化掉了。

思考

? 1 開啟cachePrepStmts的問題，前面談到每個連接都有一個緩存，是以sql為唯一標(biāo)識的LRU cache. 在分表較多，大連接的情況下，可能會個應(yīng)用服務(wù)器帶來內(nèi)存問題。這里有個前提是ibatis是默認(rèn)使用prepare的。 在mybatis中，標(biāo)簽statementType可以指定某個sql是否是使用prepare.

statementType Any one of STATEMENT, PREPARED or CALLABLE. This causes MyBatis to use Statement, PreparedStatement orCallableStatement respectively. Default: PREPARED.

這樣可以精確控制只對頻率較高的sql使用prepare，從而控制使用prepare sql的個數(shù)，減少內(nèi)存消耗。遺憾的是目前集團(tuán)貌似大多使用的是ibatis 2.0版本，不支持statementType
標(biāo)簽。

??? 2 服務(wù)器端prepare cache是一個HASH MAP. Key為stmt->id,同時也是每個連接都維護(hù)一個。因此也有可能出現(xiàn)內(nèi)存問題，待實際測試。如有必要需改造成Key為sql的全局cache，這樣不同連接的相同prepare sql可以共享。?

?? 3 oracle prepare與mysql prepare的區(qū)別：

???? mysql與oracle有一個重大區(qū)別是mysql沒有oracle那樣的執(zhí)行計劃緩存。前面我們講到SQL執(zhí)行過程包括以下階段 詞法分析->語法分析->語義分析->執(zhí)行計劃優(yōu)化->執(zhí)行。oracle的prepare實際上包括以下階段：詞法分析->語法分析->語義分析->執(zhí)行計劃優(yōu)化，也就是說oracle的prepare做了更多的事情，execute只需要執(zhí)行即可。因此，oracle的prepare比mysql更高效。

總結(jié)