MySQL prepare原理详解
|
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,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);
经多次采样测试结果如下 |