MySQL中關於prepare原理的詳解

黄舟
發布: 2017-10-04 09:27:14
原創
2868 人瀏覽過

這篇文章主要介紹了MySQL prepare的相關內容,包括prepare的產生,在伺服器端的執行過程,以及jdbc對prepare的處理以及相關測試,需要的朋友可以了解下。希望對大家有幫助。

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=true0.93
useServerPrepStmts=false1.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: PREPAEPA.

這樣可以精確控制只對頻率較高的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原理的詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板
關於我們 免責聲明 Sitemap
PHP中文網:公益線上PHP培訓,幫助PHP學習者快速成長!