Prepare的好處 

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

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

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

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

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

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

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

jdbc與prepare 

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

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

 jdbc客戶端參數(shù)：

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

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

  prepStmtCacheSqlLimit：prepareStatement對(duì)象的大小。超出大小不緩存。

 Jdbc對(duì)prepare的處理過程： 

useServerPrepStmts=true時(shí)Jdbc對(duì)prepare的處理

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

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

 useServerPrepStmts=false時(shí)Jdbc對(duì)prepare的處理

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

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

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

我們來(lái)開看一段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();

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

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

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

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

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

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

測(cè)試

  1)做了一個(gè)簡(jiǎn)單的測(cè)試，主要測(cè)試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 < 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);

經(jīng)多次采樣測(cè)試結(jié)果如下

結(jié)論：

useServerPrepStmts=true時(shí)，prepare提升7%；

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

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

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

2）prepare 前后的perf top 對(duì)比 

以下為非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

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

思考

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

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

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

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

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

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

總結(jié)

以上就是本文關(guān)于MySQL prepare原理詳解的全部?jī)?nèi)容，感興趣的朋友可以參閱本站其他相關(guān)專題，有什么問題或者需要的文章或者書籍和源碼可以隨時(shí)留言，小編將竭誠(chéng)為您解答。感謝大家對(duì)本站的支持。

最新評(píng)論