MySQL中关于prepare原理的详解(2)-木庄网络博客

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原理的详解的详细内容，更多文章请关注木庄网络博客！

返回前面的内容