如何进行MySQL Stmt预处理提高效率问题的研究

共计 2684 个字符，预计需要花费 7 分钟才能阅读完成。

如何进行 MySQL Stmt 预处理提高效率问题的研究，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

在 oracle 数据库中，有一个变量绑定的用法，很多人都比较熟悉，可以调高数据库效率，应对高并发等，好吧，这其中并不包括我，当同事问我 MySQL 中有没有类似的写法时，我是很茫然的，于是就上网查，找到了如下一种写法：

view sourceprint?DELIMITER $$ 

  set @stmt = select userid,username from myuser where userid between ? and ?; 

  prepare s1 from @stmt; 

  set @s1 = 2; 

  set @s2 = 100; 

  execute s1 using @s1,@s2; 

  deallocate prepare s1; 

$$ 

DELIMITER ;

  用这种形式写的查询，可以随意替换参数，给出代码的人称之为预处理，我想这个应该就是 MySQL 中的变量绑定吧……但是，在查资料的过程中我却听到了两种声音，一种是，MySQL 中有类似 Oracle 变量绑定的写法，但没有其实际作用，也就是只能方便编写，不能提高效率，这种说法在几个 09 年的帖子中看到：

另一种说法是 MySQL 中的变量绑定是能确实提高效率的，这个是希望有的，那到底有木有，还是自己去试验下吧。

试验是在本机进行的，数据量比较小，具体数字并不具有实际意义，但是，能用来说明一些问题，数据库版本是 mysql-5.1.57-win32 免安装版。

本着对数据库不是很熟悉的态度 ^_^, 试验过程中走了不少弯路，此文以结论为主，就不列出实验的设计过程，文笔不好，文章写得有点枯燥，写出来是希望有人来拍砖，因为我得出的结论是：预处理在有没有 cache 的情况下的执行效率都不及直接执行…… 我对自己的实验结果不愿接受。。如果说预处理只为了规范下 Query，使 cache 命中率提高的话个人觉得大材小用了，希望有比较了解的人能指出事实究竟是什么样子的——NewSilen

实验准备

第一个文件 NormalQuery.sql

NormalQuery
Set profiling=1;Select * From MyTable where DictID = 100601000004;Select DictID from MyTable limit 1,100;Select DictID from MyTable limit 2,100;/* 从 limit 1，100 到 limit 100,100 此处省略重复代码 */……Select DictID from MyTable limit 100,100;SELECT query_id,seq,STATE,10000*DURATION FROM information_schema.profiling INTO OUTFILE d:/NormalResults.csv FIELDS TERMINATED BY , LINES TERMINATED BY
;
第二个 sql 文件 StmtQuery.sql

StmtQuery
Set profiling=1;Select * From MyTable where DictID = 100601000004;set @stmt = Select DictID from MyTable limit ?,?;prepare s1 from @stmt;set @s = 100;set @s1 = 101;set @s2 = 102;……set @s100 =200;execute s1 using @s1,@s;execute s1 using @s2,@s;……execute s1 using @s100,@s;SELECT query_id,seq,STATE,10000*DURATION FROM information_schema.profiling INTO OUTFILE d:/StmtResults.csv FIELDS TERMINATED BY , LINES TERMINATED BY
;
做几点小说明：

1. Set profiling=1; 执行此语句之后，可以从 information_schema.profiling 这张表中读出语句执行的详细信息，其实包含不少内容，包括我需要的时间信息，这是张临时表，每新开一个会话都要重新设置 profiling 属性才能从这张表中读取数据

2. Select * From MyTable where DictID = 100601000004;

这行代码貌似和我们的实验没什么关系，本来我也是这么认为的，之所以加这句，是我在之前的摸索中发现，执行过程中有个步骤是 open table，如果是第一次打开某张表，那时间是相当长的，所以在执行后面的语句前，我先执行了这行代码打开试验用的表

3. MySQL 默认在 information_schema.profiling 表中保存的查询历史是 15 条，可以修改 profiling_history_size 属性来进行调整，我希望他大一些让我能一次取出足够的数据，不过最大值只有 100，尽管我调整为 150，最后能够查到的也只有 100 条，不过也够了

4. SQL 代码我没有全列出来，因为查询语句差不多，上面代码中用省略号表示了，最后的结果是两个 csv 文件，个人习惯，你也可以把结果存到数据库进行分析

实验步骤

重启数据库，执行文件 NormalQuery.sql，执行文件 StmtQuery.sql，得到两个结果文件

再重启数据库，执行 StmtQuery.sql，执行文件 NormalQuery.sql，得到另外两个结果文件

实验结果分析

每一个 SQL 文件中执行了一百个查询语句，没有重复的查询语句，不存在查询 cache，统计执行 SQL 的平均时间得出如下结果

从结果中可以看出，无论是先执行还是后执行，NormalQuery 中的语句都比使用预处理语句的要快一些 =.=！

那再来看看每一句查询具体的情况，Normal 和 Stmt 的 query 各执行了两百次，每一步的详细信息如下：

从这里面可以看出，第一个，normalquery 比 stmtquery 少一个步骤，第二个，虽然 stmt 在不少步骤上是优于 normal 的, 但在 executing 一步上输掉太多，最后结果上也是落败

  最后，再给出一个查询缓存的实验结果，具体步骤就不列了

在查询缓存的时候，Normal 完胜……

关于如何进行 MySQL Stmt 预处理提高效率问题的研究问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注丸趣 TV 行业资讯频道了解更多相关知识。