MySQL prepare原理详解
Prepare的好处
PrepareSQL产生的原因。首先从mysql服务器执行sql的过程开始讲起，SQL执行过程包括以下阶段词法分析->语法分析->语义分析->执行计划优化->执行。词法分析->语法分析这两个阶段我们称之为硬解析。词法分析识别sql中每个词，语法分析解析SQL语句是否符合sql语法，并得到一棵语法树（Lex）。对于只是参数不同，其他均相同的sql，它们执行时间不同但硬解析的时间是相同的。而同一SQL随着查询数据的变化，多次查询执行时间可能不同，但硬解析的时间是不变的。对于sql执行时间较短，sql硬解析的时间占总执行时间的比率越高。而对于淘宝应用的绝大多数事务型SQL，查询都会走索引，执行时间都比较短。因此淘宝应用dbsql硬解析占的比重较大。
Prepare的出现就是为了优化硬解析的问题。Prepare在服务器端的执行过程如下
1） Prepare接收客户端带”?”的sql,硬解析得到语法树(stmt->Lex),缓存在线程所在的preparestatementcache中。此cache是一个HASHMAP.Key为stmt->id.然后返回客户端stmt->id等信息。
2） Execute接收客户端stmt->id和参数等信息。注意这里客户端不需要再发sql过来。服务器根据stmt->id在preparestatementcache中查找得到硬解析后的stmt,并设置参数，就可以继续后面的优化和执行了。
Prepare在execute阶段可以节省硬解析的时间。如果sql只执行一次，且以prepare的方式执行，那么sql执行需两次与服务器交互（Prepare和execute）,而以普通（非prepare）方式，只需要一次交互。这样使用prepare带来额外的网络开销，可能得不偿失。我们再来看同一sql执行多次的情况，比如以prepare方式执行10次，那么只需要一次硬解析。这时候 额外的网络开销就显得微乎其微了。因此prepare适用于频繁执行的SQL。
Prepare的另一个作用是防止sql注入，不过这个是在客户端jdbc通过转义实现的，跟服务器没有关系。
硬解析的比重
压测时通过perf得到的结果，硬解析相关的函数比重都比较靠前（MYSQLparse4.93%,lex_one_token1.79%,lex_start1.12%）总共接近8%。因此，服务器使用prepare是可以带来较多的性能提升的。
jdbc与prepare
jdbc服务器端的参数：
useServerPrepStmts：默认为false.是否使用服务器prepare开关
jdbc客户端参数：
cachePrepStmts：默认false.是否缓存prepareStatement对象。每个连接都有一个缓存，是以sql为唯一标识的LRUcache.同一连接下，不同stmt可以不用重新创建prepareStatement对象。
prepStmtCacheSize：LRUcache中prepareStatement对象的个数。一般设置为最常用sql的个数。
prepStmtCacheSqlLimit：prepareStatement对象的大小。超出大小不缓存。
Jdbc对prepare的处理过程：
useServerPrepStmts=true时Jdbc对prepare的处理
1） 创建PreparedStatement对象，向服务器发送COM_PREPARE命令，并传送带问号的sql.服务器返回jdbcstmt->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代码
Connectioncon=null; PreparedStatementps=null; Stringsql="select*fromuserwhereid=?"; 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;
//noprepare
Stringsql="selectbiz_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_statusfromtc_biz_order_0030where"+
"parent_id=594314511722841orparent_id=547667559932641;";
begin=newDate();
System.out.println("begin:"+df.format(begin));
stmt=con.createStatement();
for(inti=0;i
经多次采样测试结果如下
非prepare和prepare时间比
useServerPrepStmts=true
0.93
useServerPrepStmts=false
1.01
结论：
useServerPrepStmts=true时，prepare提升7%；
useServerPrepStmts=false时，prepare与非prepare性能相当。
如果将语句简化为select*fromtc_biz_order_0030whereparent_id=?。那么测试的结论useServerPrepStmts=true时，prepare仅提升2%；sql越简单硬解析的时间就越少，prepare的提升就越少。
注意：这个测试是在单个连接，单条sql的理想情况下进行的，线上会出现多连接多sql,还有sql执行频率，sql的复杂程度等不同，因此prepare的提升效果会随具体环境而变化。
2）prepare前后的perftop对比
以下为非prepare
6.46%mysqldmysqld[.]_Z10MYSQLparsePv
3.74%mysqldlibc-2.12.so[.]__memcpy_ssse3
2.50%mysqldmysqld[.]my_hash_sort_utf8
2.15%mysqldmysqld[.]cmp_dtuple_rec_with_match
2.05%mysqldmysqld[.]_ZL13lex_one_tokenPvS_
1.46%mysqldmysqld[.]buf_page_get_gen
1.34%mysqldmysqld[.]page_cur_search_with_match
1.31%mysqldmysqld[.]_ZL14build_templateP19row_prebuilt_structP3THDP5TABLEj
1.24%mysqldmysqld[.]rec_init_offsets
1.11%mysqldlibjemalloc.so.1[.]free
1.09%mysqldmysqld[.]rec_get_offsets_func
1.01%mysqldlibjemalloc.so.1[.]malloc
0.96%mysqldlibc-2.12.so[.]__strlen_sse42
0.93%mysqldmysqld[.]_ZN4JOIN8optimizeEv
0.91%mysqldmysqld[.]_ZL15get_hash_symbolPKcjb
0.88%mysqldmysqld[.]row_search_for_mysql
0.86%mysqld[kernel.kallsyms][k]tcp_recvmsg
以下为perpare
3.46%mysqldlibc-2.12.so[.]__memcpy_ssse3
2.32%mysqldmysqld[.]cmp_dtuple_rec_with_match
2.14%mysqldmysqld[.]_ZL14build_templateP19row_prebuilt_structP3THDP5TABLEj
1.96%mysqldmysqld[.]buf_page_get_gen
1.66%mysqldmysqld[.]page_cur_search_with_match
1.54%mysqldmysqld[.]row_search_for_mysql
1.44%mysqldmysqld[.]btr_cur_search_to_nth_level
1.41%mysqldlibjemalloc.so.1[.]free
1.35%mysqldmysqld[.]rec_init_offsets
1.32%mysqld[kernel.kallsyms][k]kfree
1.14%mysqldlibjemalloc.so.1[.]malloc
1.08%mysqld[kernel.kallsyms][k]fget_light
1.05%mysqldmysqld[.]rec_get_offsets_func
0.99%mysqldmysqld[.]_ZN8Protocol24send_result_set_metadataEP4ListI4ItemEj
0.90%mysqldmysqld[.]sync_array_print_long_waits
0.87%mysqldmysqld[.]page_rec_get_n_recs_before
0.81%mysqldmysqld[.]_ZN4JOIN8optimizeEv
0.81%mysqldlibc-2.12.so[.]__strlen_sse42
0.78%mysqldmysqld[.]_ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array
0.72%mysqld[kernel.kallsyms][k]tcp_recvmsg
0.63%mysqldlibpthread-2.12.so[.]__pthread_getspecific_internal
0.63%mysqld[kernel.kallsyms][k]sk_run_filter
0.60%mysqldmysqld[.]_Z19find_field_in_tableP3THDP5TABLEPKcjbPj
0.60%mysqldmysqld[.]page_check_dir
0.57%mysqldmysqld[.]_Z16dispatch_command19enum_server_commandP3THDP
对比可以发现MYSQLparselex_one_token在prepare时已优化掉了。
思考
1开启cachePrepStmts的问题，前面谈到每个连接都有一个缓存，是以sql为唯一标识的LRUcache.在分表较多，大连接的情况下，可能会个应用服务器带来内存问题。这里有个前提是ibatis是默认使用prepare的。在mybatis中，标签statementType可以指定某个sql是否是使用prepare.
statementTypeAnyoneofSTATEMENT,PREPAREDorCALLABLE.ThiscausesMyBatistouseStatement,PreparedStatementorCallableStatementrespectively.Default:PREPARED.
这样可以精确控制只对频率较高的sql使用prepare，从而控制使用preparesql的个数，减少内存消耗。遗憾的是目前集团貌似大多使用的是ibatis2.0版本，不支持statementType
标签。
2服务器端preparecache是一个HASHMAP.Key为stmt->id,同时也是每个连接都维护一个。因此也有可能出现内存问题，待实际测试。如有必要需改造成Key为sql的全局cache，这样不同连接的相同preparesql可以共享。
3oracleprepare与mysqlprepare的区别：
mysql与oracle有一个重大区别是mysql没有oracle那样的执行计划缓存。前面我们讲到SQL执行过程包括以下阶段词法分析->语法分析->语义分析->执行计划优化->执行。oracle的prepare实际上包括以下阶段：词法分析->语法分析->语义分析->执行计划优化，也就是说oracle的prepare做了更多的事情，execute只需要执行即可。因此，oracle的prepare比mysql更高效。
总结
以上就是本文关于MySQLprepare原理详解的全部内容，感兴趣的朋友可以参阅本站其他相关专题，有什么问题或者需要的文章或者书籍和源码可以随时留言，小编将竭诚为您解答。感谢大家对本站的支持。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。