mysql prepare语句,mysqli prepare,MySQL prepare原理详解

本文主要介绍MySQL prepare的相关内容，包括prepare的制作，在服务器端的执行过程，以及jdbc对prepare的处理和相关测试。有需要的朋友可以了解一下。希望对大家有帮助。

Prepare的好处

准备SQL的原因。首先从mysql服务器执行sql的过程说起。SQL执行过程包括以下几个阶段：词法分析-语法分析-语义分析-执行计划优化-执行。词法分析——语法分析——这两个阶段被称为硬解析。词法分析识别sql中的每个单词，语法分析分析sql语句是否符合SQL语法，得到一棵语法树(Lex)。对于参数不同但其他参数相同的sql，执行时间不同，但硬解析时间相同。但是，随着同一个SQL的查询数据的变化，多个查询的执行时间可能不同，但是硬解析时间是不变的。sql的执行时间越短，sql硬解析时间占总执行时间的比例就越高。但对于淘宝使用的绝大多数事务型SQL，查询会被索引，执行时间相对较短。所以淘宝应用db sql硬解析占了很大比重。

Prepare的出现是为了优化解析难的问题。服务器端Prepare的执行过程如下

1)准备接收客户端磁带"？"sql，硬解析以获取语法树(stmt-Lex)，缓存在线程的preparestatement缓存中。这个缓存是一个哈希映射。关键是stmt-id，然后返回客户端的stmt-id等信息。

2) Execute接收客户端的stmt-id和参数等信息。注意，客户端不需要在这里发送sql。根据服务器的stmt-id，在preparestatement缓存中找到硬解析后的stmt，设置好参数，就可以继续后面的优化和执行。

Prepare可以节省执行阶段的硬解析时间。如果sql只执行一次，并且以准备的方式执行，那么sql执行需要与服务器进行两次交互(准备和执行)，而在普通(非准备)方式下，只需要一次交互。这样，使用prepare会带来额外的网络开销，可能得不偿失。我们来看一下同一个sql执行多次的情况，比如以prepare的方式执行10次，那么只需要一次硬解析。此时，额外的网络开销可以忽略不计。因此，prepare适用于频繁执行的SQL。

Prepare的另一个功能是防止sql注入，但这是在客户端通过转义jdbc实现的，与服务器无关。

硬比重

从perf得到的结果来看，与硬分析相关的函数比例相对较高(MySQL parse 4.93%，lex _ one _ token 1.79%，lex _ start 1.12%)，合计接近8%。因此，使用prepare的服务器可以带来更多的性能提升。

使用jdbc准备

jdbc服务器的参数：

UseServerPrepStmts:默认值为false。您想使用服务器准备开关吗？

Jdbc客户端参数：

CachePrepStmts:默认为false。是否缓存prepareStatement对象。每个连接都有一个缓存，它是LRU缓存，以sql作为其唯一标识符。在同一连接下，不同的stmt不必重新创建prepareStatement对象。

PrepStmtCacheSize:LRU缓存中prepareStatement对象的LRU缓存数。一般设置为最常用sql的数量。

psttmtcachesqllimit:prepare statement对象的大小。超过该大小不会被缓存。

Jdbc准备的处理：

useServerPrepStmts=true时Jdbc对prepare的处理

1)创建一个PreparedStatement对象，向服务器发送COM_PREPARE命令，发送问号的sql。服务器返回jdbc stmt-id之类的信息。

2)向服务器发送COM_EXECUTE命令，传输参数信息。

useServerPrepStmts=false时Jdbc对prepare的处理

1)创建一个PreparedStatement对象，该对象此时不会与服务器进行交互。

2)根据参数和PreparedStatement对象拼接完整的SQL，并向服务器发送查询命令

我们再看参数cachePrepStmts打开时在useServerPrepStmts为真实的或错误的时，均缓存准备报表对象。只不过useServerPrepStmts为的真实的缓存准备报表对象包含服务器的stmt-id等信息，也就是说如果重用了准备报表对象，那么就省去了和服务器通讯(COM _准备命令)的开销。而useServerPrepStmts=false是，开启cachePrepStmts缓存准备报表对象只是简单的结构化查询语言解析信息，因此此时开启cachePrepStmts意义不是太大。

我们来开看一段Java 语言(一种计算机语言，尤用于创建网站)语言（一种计算机语言，尤用于创建网站)代码

连接con=空

PreparedStatement ps=null

字符串sql=select * from user where id=？；

PS=con . prepare语句(SQL)；

ps.setInt(1，1)；

PS。执行查询()；

PS。close()；

PS=con . prepare语句(SQL)；

ps.setInt(1，3)；

PS。执行查询()；

PS。close()；

这段代码在同一会话中两次准备执行同一语句，并且之间有PS。close()；

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

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

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

如果两次准备之间没有PS。close()；那么cachePrepStmts=true，cachePrepStmts=false也只需一次硬解析。

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

测试

1)做了一个简单的测试，主要测试准备的效果和useServerPrepStmts参数的影响。

cnt=5000

//没有准备

字符串sql=select商业_订单_id，无序_id，卖方_尼克，买方_尼克，卖方_id，买方_id，拍卖_id，拍卖_标题，拍卖_价格，购买_金额，商业_类型，子商业_类型，失败_原因，支付_状态，物流_状态，out_trade_status，snap_path，gmt_create，status，ifnull(买方_费率_状态，4)买方_费率_状态来自tc_biz_order_0030其中

parent_id=594314511722841或parent _ id=547667559932641 ；

begin=new Date()；

系统。出去。println( begin: df。格式(begin))；

stmt=con . create语句()；

for(int I=0；我（cannot）不能我)

{

stmt。执行查询(SQL)；

}

end=new Date()；

系统。出去。println( end: df。格式(end))；

长温度=结束。gettime()-begin。gettime()；

系统。出去。println( no perpare interval: temp )；

//测试准备

sql=select商业_订单_id，外出_订单_id，卖方_尼克，买方_尼克，卖方_id，买方_id，拍卖_id，拍卖_标题，拍卖_价格，购买_金额，商业_类型，子商业_类型，失败_原因，支付_状态，物流_状态，外出_贸易_状态，快照_路径，gmt _创建，状态，如果null(买方_汇率_状态，4)买方_汇率_状态来自tc_biz_order_0030其中

parent_id=594314511722841或者parent_id=？

PS=con . prepare语句(SQL)；

大整数param=新的大整数( 547667559932641 )；

begin=new Date()；

系统。出去。println( begin: df。格式(begin))；

for(int I=0；我（cannot）不能我)

{

ps.setObject(1，param)；

PS。执行查询()；

}

end=new Date()；

系统。出去。println( end: df。格式(end))；

temp=结束。gettime()-begin。gettime()；

System.out.println(准备间隔： temp )；

经多次采样测试结果如下

非准备和准备时间比

useServerPrepStmts=true

0.93

useServerPrepStmts=false

1.01

结论：

useServerPrepStmts=true时，准备提升7%;

useServerPrepStmts=false时，准备与非准备性能相当。

如果将语句简化为select * from tc_biz_order_0030其中parent_id=？那么测试的结论useServerPrepStmts=true时，准备仅提升2%;结构化查询语言越简单硬解析的时间就越少，准备的提升就越少。

注意：这个测试是在单个连接，单条结构化查询语言的理想情况下进行的，线上会出现多连接多sql，还有结构化查询语言执行频率，sql的复杂程度等不同，因此准备的提升效果会随具体环境而变化。

2)准备前后的顶部穿孔对比

以下为非准备

6.46% mysqld mysqld [.] _Z10MYSQLparsePv

mysqld libc-2.12.so [.] __memcpy_ssse3

2.50% mysqld mysqld [.]我的散列排序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 [.]_ ZL 14构建_模板p19行_预构建_结构P3 thdp 5表j

1.24% mysqld mysqld [.]记录_初始化_偏移

mysqld libjemalloc.so.1 .]免费

1.09% mysqld mysqld [.] rec_get_offsets_func

mysqld libjemalloc.so.1 .] malloc

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【内核。kall syms][k]TCP _ recvmsg

以下为佩尔帕雷

mysqld libc-2.12.so .] __memcpy_ssse3

2.32% mysqld mysqld [.] cmp_dtuple_rec_with_match

2.14% mysqld mysqld [.]_ ZL 14构建_模板p19行_预构建_结构P3 thdp 5表j

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 _ n _ level

mysqld libjemalloc.so.1 .]免费

mysqld mysqld [.]记录_初始化_偏移

1.32% mysqld[内核。kall syms][k]kfree

mysqld libjemalloc.so.1 .] malloc

1.08% mysqld[内核。kall syms][k]fget _ light

mysqld mysqld [.] rec_get_offsets_func

0.99% mysqld mysqld [.]_ Zn 8协议24 send _ result _ set _ metadata EP 4 list 4 itemej

0.90% mysqld mysqld [.]同步_数组_打印_长时间等待

0.87% mysqld mysqld [.] page_rec_get_n_recs_before

0.81% mysqld mysqld [.] _ZN4JOIN8optimizeEv

mysqld libc-2.12.so [.] __strlen_sse42

0.78% mysqld mysqld [.]_ ZL 20 make _ join _ statistics sp 4 join p10 table _ listp 4 item p16 ST _ dynamic _ array

0.72% mysqld【内核。kall syms][k]TCP _ recvmsg

0.63% mysqld libpthread-2.12.so [.]_ _ pthread _ get specific _ internal

0.63% mysqld【内核。kall syms][k]sk _运行_过滤器

0.60% mysqld mysqld [.]_ z19 find _ field _ in _ table P3 thdp 5 tablepkcjbpj

0.60% mysqld mysqld [.]页面_检查_目录

0.57% mysqld mysqld [.]_ z16调度命令19枚举服务器命令3 TDP

对比可以发现MYSQLparse lex_one_token在准备时已优化掉了。

思考

一开启cachePrepStmts的问题，前面谈到每个连接都有一个缓存，是以结构化查询语言为唯一标识的LRU缓存。在分表较多，大连接的情况下，可能会个应用服务器带来内存问题。这里有个前提是伊巴蒂斯是默认使用准备的。在米巴蒂斯中，标签语句类型可以指定某个结构化查询语言是否是使用准备好。

语句键入任何一个语句，准备好的或可调用的。这导致米巴蒂斯分别使用报表、准备好的报表或可调用语句.默认：已准备好。

这样可以精确控制只对频率较高的结构化查询语言使用准备，从而控制使用准备结构化查询语言的个数，减少内存消耗。遗憾的是目前集团貌似大多使用的是ibatis 2.0版本，不支持语句类型

标签。

2服务器端准备缓存是一个哈希映射。钥匙为stmt-id，同时也是每个连接都维护一个。因此也有可能出现内存问题，待实际测试。如有必要需改造成钥匙为结构化查询语言的全局缓存，这样不同连接的相同准备结构化查询语言可以共享。

3甲骨文准备与关系型数据库准备的区别：

mysql和oracle的一个重要区别是，mysql没有与oracle相同的执行计划缓存。前面提到的SQL执行过程包括以下几个阶段：词法分析-语法分析-语义分析-执行计划优化-执行。oracle的prepare其实包括以下几个阶段：词法分析-语法分析-语义分析-执行计划优化，也就是说Oracle的prepare做的事情更多，execute只需要执行。所以oracle的prepare比mysql的效率高。