MySQL prepare原理詳解

2024-07-24 13:14:32

字體：大中小

來源：轉載

供稿：網友

Prepare的好處

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

Prepare的出現就是為了優化硬解析的問題。Prepare在服務器端的執行過程如下

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

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

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

Prepare的另一個作用是防止sql注入，不過這個是在客戶端jdbc通過轉義實現的，跟服務器沒有關系。
硬解析的比重

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

jdbc與prepare

jdbc服務器端的參數：

useServerPrepStmts：默認為false. 是否使用服務器prepare開關

jdbc客戶端參數：

cachePrepStmts：默認false.是否緩存prepareStatement對象。每個連接都有一個緩存，是以sql為唯一標識的LRU cache. 同一連接下，不同stmt可以不用重新創建prepareStatement對象。

prepStmtCacheSize：LRU cache中prepareStatement對象的個數。一般設置為最常用sql的個數。

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

Jdbc對prepare的處理過程：

useServerPrepStmts=true時Jdbc對prepare的處理

1）創建PreparedStatement對象，向服務器發送COM_PREPARE命令，并傳送帶問號的sql. 服務器返回jdbc stmt->id等信息

2）向服務器發送COM_EXECUTE命令,并傳送參數信息。

useServerPrepStmts=false時Jdbc對prepare的處理

1）創建PreparedStatement對象，此時不會和服務器交互。

2）根據參數和PreparedStatement對象拼接完整的SQL，向服務器發送QUERY命令

我們再看參數cachePrepStmts打開時在useServerPrepStmts為true或false時，均緩存PreparedStatement對象。只不過useServerPrepStmts為的true緩存PreparedStatement對象包含服務器的stmt->id等信息，也就是說如果重用了PreparedStatement對象，那么就省去了和服務器通訊（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執行同一語句，并且之間有ps.close();

useServerPrepStmts=false時，服務器會兩次硬解析同一SQL。

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

useServerPrepStmts=true, cachePrepStmts=true時服務器只會硬解析一次SQL。

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

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

測試

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

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 < cnt; i++)    {            stmt.executeQuery(sql);    }     end = new Date();    System.out.println("end:" + df.format(end));    long temp = end.getTime() - begin.getTime();    System.out.println("no perpare interval:" + temp);        // test prepare        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 =?;";    ps = con.prepareStatement(sql);    BigInteger param = new BigInteger("547667559932641");    begin = new Date();    System.out.println("begin:" + df.format(begin));    for (int i = 0; i < cnt; i++)    {      ps.setObject(1, param);      ps.executeQuery();     }     end = new Date();    System.out.println("end:" + df.format(end));    temp = end.getTime() - begin.getTime();    System.out.println("prepare interval:" + temp);

經多次采樣測試結果如下

	非prepare和prepare時間比
useServerPrepStmts=true	0.93
useServerPrepStmts=false	1.01

結論：

useServerPrepStmts=true時，prepare提升7%；

useServerPrepStmts=false時，prepare與非prepare性能相當。

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

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

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

對比可以發現 MYSQLparse lex_one_token在prepare時已優化掉了。

思考

1 開啟cachePrepStmts的問題，前面談到每個連接都有一個緩存，是以sql為唯一標識的LRU cache. 在分表較多，大連接的情況下，可能會個應用服務器帶來內存問題。這里有個前提是ibatis是默認使用prepare的。在mybatis中，標簽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的個數，減少內存消耗。遺憾的是目前集團貌似大多使用的是ibatis 2.0版本，不支持statementType
標簽。

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

3 oracle prepare與mysql prepare的區別：

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

總結

以上就是本文關于MySQL prepare原理詳解的全部內容，感興趣的朋友可以參閱本站其他相關專題，有什么問題或者需要的文章或者書籍和源碼可以隨時留言，小編將竭誠為您解答。感謝大家對本站的支持。

注：相關教程知識閱讀請移步到MYSQL教程頻道。

上一篇：Mysql常用sql語句匯總

下一篇：游戲和服備忘問題簡析