mysql prepare原理-阿里云开发者社区

开发者社区> zysql> 正文

mysql prepare原理

简介: mysql prepare 原理
+关注继续查看

注:2013年的老文章

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. Keystmt->id. 然后返回客户端stmt->id等信息。

 2)  Execute 接收客户端stmt->id和参数等信息。注意这里客户端不需要再发sql过来。服务器根据stmt->idpreparestatement cache中查找得到硬解析后的stmt, 并设置参数,就可以继续后面的优化和执行了。

    Prepareexecute阶段可以节省硬解析的时间。如果sql只执行一次,且以prepare的方式执行,那么sql执行需两次与服务器交互(Prepareexecute而以普通(非prepare)方式,只需要一次交互。这样使用prepare带来额外的网络开销,可能得不偿失。我们再来看同一sql执行多次的情况,比如以prepare方式执行10次,那么只需要一次硬解析。这时候  额外的网络开销就显得微乎其微了。因此prepare适用于频繁执行的SQL

    Prepare的另一个作用是防止sql注入,不过这个是在客户端jdbc通过转义实现的,跟服务器没有关系。 

硬解析的比重

   交易买家库 tcbyer压测时,通过perf 得到的结果。

   getTfsFile.do?filePath=TB10jg0FFXXXXcfXp


   硬解析相关的函数比重都比较靠前(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 cacheprepareStatement对象的个数。一般设置为最常用sql的个数。

   prepStmtCacheSqlLimit:prepareStatement对象的大小。超出大小不缓存。

  Jdbcprepare的处理过程: 

  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代码

1
2
3
4
5
6
7
8
9
10
11
            Connection con = null;
            PreparedStatement ps = null;
            String sql = "select * from user where id=?";
            ps = con.prepareStatement(sql);            
            ps.setInt(11);‍‍            
            ps.executeQuery();            
            ps.close();            
            ps = con.prepareStatement(sql);            
            ps.setInt(13);            
            ps.executeQuery();            
            ps.close();

   这段代码在同一会话中两次prepare执行同一语句,并且之间有ps.close();

    useServerPrepStmts=false时,服务器会两次硬解析同一SQL

    useServerPrepStmts=true, cachePrepStmts=false时服务器仍然会两次硬解析同一SQL

    useServerPrepStmts=true, cachePrepStmts=true时服务器只会硬解析一次SQL

 

    如果两次prepare之间没有ps.close();那么cachePrepStmts=truecachePrepStmts=false也只需一次硬解析. 

    因此,客户端对同一sql,频繁分配和释放PreparedStatement对象的情况下,开启cachePrepStmts参数是很有必要的。

测试

   1)做了一个简单的测试,主要测试prepare的效果和useServerPrepStmts参数的影响.

 

1
<span style="font-family: 宋体, SimSun; font-size: 14px;">        cnt = 5000;<br>        // no prepare<br>        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 " +<br>        "parent_id = 594314511722841 or parent_id =547667559932641;";<br><br>        begin = new Date();<br>        System.out.println("begin:" + df.format(begin));<br>        <br>        stmt = con.createStatement();<br>        for (int i = 0; i < cnt; i++)<br>        {         <br>            stmt.executeQuery(sql);<br>        } <br>               <br>        end = new Date();<br>        System.out.println("end:" + df.format(end));<br>        <br>        long temp end.getTime() - begin.getTime();<br>        System.out.println("no perpare interval:" temp);<br>        <br>        <br>        // test prepare       <br>        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 " +<br>                "parent_id = 594314511722841 or parent_id =?;";<br>        ps = con.prepareStatement(sql);<br>        BigInteger param = new BigInteger("547667559932641");<br>        <br>        begin = new Date();<br>        System.out.println("begin:" + df.format(begin));<br>        <br>        for (int i = 0; i < cnt; i++)<br>        {<br>         ps.setObject(1, param);<br>            ps.executeQuery(); <br>        } <br>   <br>        end = new Date();<br>        System.out.println("end:" + df.format(end));<br>        <br>        temp end.getTime() - begin.getTime();<br>        System.out.println("prepare interval:" temp);<br></span>


经多次采样测试结果如下:


非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

标签。

    服务器端prepare cache是一个HASH MAP. Keystmt->id,同时也是每个连接都维护一个。因此也有可能出现内存问题,待实际测试。如有必要需改造成Keysql的全局cache,这样不同连接的相同prepare sql可以共享。 

    3 oracle prepare与mysql prepare的区别:

      mysql与oracle有一个重大区别是mysql没有oracle那样的执行计划缓存。前面我们讲到SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行。oracle的prepare实际上包括以下阶段:词法分析->语法分析->语义分析->执行计划优化,也就是说oracle的prepare做了更多的事情,execute只需要执行即可。因此,oracle的prepare比mysql更高效。


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
9217 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
11119 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10763 0
使用SSH远程登录阿里云ECS服务器
远程连接服务器以及配置环境
2450 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
12916 0
腾讯云服务器 设置ngxin + fastdfs +tomcat 开机自启动
在tomcat中新建一个可以启动的 .sh 脚本文件 /usr/local/tomcat7/bin/ export JAVA_HOME=/usr/local/java/jdk7 export PATH=$JAVA_HOME/bin/:$PATH export CLASSPATH=.
4600 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
6848 0
+关注
zysql
张远 MySQL/MyRocks
34
文章
10
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载