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网别的相干文章！
