MySQL prepare原理详解
Prepare的好处
Prepare SQL产生的原因。首先从mysql服务器执行sql的过程开始讲起，SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行。词法分析->语法分析这两个阶段我们称之为硬解析。词法分析识别sql中每个词，语法分析解析SQL语句是否符合sql语法，并得到一棵语法树（Lex）。对于只是参数不同，其他均相同的sql，它们执行时间不同但硬解析的时间是相同的。而同一SQL随着查询数据的变化，多次查询执行时间可能不同，但硬解析的时间是不变的。对于sql执行时间较短，sql硬解析的时间占总执行时间的比率越高。而对于淘宝应用的绝大多数事务型SQL，查询都会走索引，执行时间都比较短。因此淘宝应用db sql硬解析占的比重较大。
Prepare的出现就是为了优化硬解析的问题。Prepare在服务器端的执行过程如下
1） Prepare 接收客户端带”"htmlcode">
Connection con = null;
PreparedStatement ps = null;
String sql = "select * from user where id=";
ps = con.prepareStatement(sql);
ps.setInt(1, 1);"htmlcode">
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 ="htmlcode">
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变量