欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL prepare原理详解

程序员文章站 2023-02-17 23:30:57
prepare的好处      prepare sql产生的原因。首先从mysql服务器执行sql的过程开始讲起,sql执行过...

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原理详解的全部内容,感兴趣的朋友可以参阅本站其他相关专题,有什么问题或者需要的文章或者书籍和源码可以随时留言,小编将竭诚为您解答。感谢大家对本站的支持。