MySQL 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);

经多次采样测试结果如下

结论：

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

# mysql  # prepare  # 原理  # MySQL中预处理语句prepare、execute与deallocate的使用教程  # php+mysql prepare 与普通查询的性能对比实例讲解  # PHP5 mysqli的prepare准备语句使用说明  # MySQL prepare语句的SQL语法  # 两次  # 执行时间  # 客户端  # 只需要  # 的是  # 是一个  # 是在  # 都有  # 情况下  # 较多  # 淘宝  # 较短  # 越少  # 也就是说  # 有什么  # 也有  # 有个  # 不需要  # 如有  # 为您 

相关文章： c# 服务器GC和工作站GC的区别和设置  c++怎么用jemalloc c++替换默认内存分配器【性能】  如何配置IIS站点权限与局域网访问？  文字头像制作网站推荐软件,醒图能自动配文字吗？  公司门户网站制作公司有哪些,怎样使用wordpress制作一个企业网站？  微课制作网站有哪些,微课网怎么进？  如何快速生成凡客建站的专业级图册？  网站制作软件免费下载安装,有哪些免费下载的软件网站？  建站之星代理费用多少？最新价格详情介绍  相册网站制作软件,图片上的网址怎么复制？  云南网站制作公司有哪些,云南最好的招聘网站是哪个？  网站制作公司,橙子建站是合法的吗？  网站制作大概要多少钱一个,做一个平台网站大概多少钱？  沈阳制作网站公司排名,沈阳装饰协会官方网站？  公司网站设计制作厂家,怎么创建自己的一个网站？  网站网页制作电话怎么打,怎样安装和使用钉钉软件免费打电话？  建站之星Pro快速搭建教程：模板选择与功能配置指南  ,怎么在广州志愿者网站注册？  如何在万网开始建站？分步指南解析  网站建设制作、微信公众号,公明人民医院怎么在网上预约？  网站视频怎么制作,哪个网站可以免费收看好莱坞经典大片？  高性价比服务器租赁——企业级配置与24小时运维服务  电脑免费海报制作网站推荐,招聘海报哪个网站多？  北京制作网站的公司,北京铁路集团官方网站？  建站之星如何开启自定义404页面避免用户流失？  长沙做网站要多少钱,长沙国安网络怎么样？  山东云建站价格为何差异显著？  装修招标网站设计制作流程,装修招标流程？  如何登录建站主机？访问步骤全解析  公司门户网站制作流程,华为官网怎么做？  如何在新浪SAE免费搭建个人博客？  潍坊网站制作公司有哪些,潍坊哪家招聘网站好？  头像制作网站在线制作软件,dw网页背景图像怎么设置？  香港服务器如何优化才能显著提升网站加载速度？  长沙企业网站制作哪家好,长沙水业集团官方网站？  如何高效配置IIS服务器搭建网站？  Python如何创建带属性的XML节点  建站之星如何保障用户数据免受黑客入侵？  建站与域名管理如何高效结合？  学校建站服务器如何选型才能满足性能需求？  网站制作培训多少钱一个月,网站优化seo培训课程有哪些？  哈尔滨网站建设策划,哈尔滨电工证查询网站？  香港服务器租用每月最低只需15元？  网站制作价目表怎么做,珍爱网婚介费用多少？  小程序网站制作需要准备什么资料,如何制作小程序？  建站之星各版本价格是多少？  临沂网站制作公司有哪些,临沂第四中学官网？  5种Android数据存储方式汇总  建站之星如何快速更换网站模板？  如何挑选最适合建站的高性能VPS主机？