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

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的效率高。

总结

以上就是本文中关于MySQL prepare原理的详细解释。感兴趣的朋友可以参考本站其他相关话题。如果您有任何问题或需要文章或书籍和源代码,可以随时留言,边肖将很乐意为您解答。感谢您对本网站的支持。

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