mysql批处理语法 mysql批处理效率

转载

mob64ca140eb362 2023-09-13 12:10:25

文章标签 mysql批处理语法 MySQL 变量绑定 STMT 效率 文章分类 MySQL 数据库

对于MySQL中的预处理和普通查询效率进行比较，实验的结果竟然是预处理效率低于普通查询，这让我很不能接受，整理出来希望大家看看实验和实验结果究竟有没有问题，欢迎拍砖

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

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变量绑定的写法，但没有其实际作用，也就是只能方便编写，不能提高效率

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

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

预处理在有没有cache的情况下的执行效率都不及直接执行…… 我对自己的实验结果不愿接受。。如果说预处理只为了规范下Query，使cache命中率提高的话个人觉得大材小用了，希望有比较了解的人能指出事实究竟是什么样子的——NewSilen

实验准备

　　第一个文件NormalQuery.sql

mysql批处理语法 mysql批处理效率_MySQL

mysql批处理语法 mysql批处理效率_mysql批处理语法_02

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 '\n';

　　第二个sql文件 StmtQuery.sql

mysql批处理语法 mysql批处理效率_MySQL

mysql批处理语法 mysql批处理效率_mysql批处理语法_02

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 '\n';

做几点小说明：

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，得到另外两个结果文件

　　实验结果

详细结果在最后提供了附件下载，有兴趣的朋友可以看下

　　结果分析