MySQL prepare原理詳解

Prepare的好處 

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

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

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

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

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

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

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

jdbc與prepare 

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

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

 jdbc客戶端參數(shù)：

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

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

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

 Jdbc對prepare的處理過程： 

useServerPrepStmts=true時Jdbc對prepare的處理

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

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

 useServerPrepStmts=false時Jdbc對prepare的處理

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

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

  我們再看參數(shù)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執(zhí)行同一語句，并且之間有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參數(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  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)多次采樣測試結果如下