•   欢迎来到21NN网.
  •   请记住本站网址www.21nn.cn

MySQL中关于prepare道理的详解【MySQL教程】,prepare,MySQL,详解

摘要: 这篇文章重要引见了MySQLprepare的相干内容,包括prepare的发生,在服务器端的实行历程,以及jdbc对prepare的处置惩罚以及相干测试,须要的朋侪能够了解下。愿望对人人有所协助。...
这篇文章重要引见了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=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道理的详解的细致内容,更多请关注ki4网别的相干文章!

分享到:

发表评论

评论列表

还没有评论,快来说点什么吧~

公众号二维码

微信公众号