MySQL中Stmt 预处理提高效率问题的小研究
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年的帖子中看到：
http://www.itpub.net/thread-1210292-1-1.html
http://cuda.itpub.net/redirect.php?fid=73&tid=1210572&goto=nextnewset
另一种说法是MySQL中的变量绑定是能确实提高效率的，这个是希望有的，那到底有木有，还是自己去试验下吧。
试验是在本机进行的，数据量比较小，具体数字并不具有实际意义，但是，能用来说明一些问题，数据库版本是mysql-5.1.57-win32免安装版。
本着对数据库不是很熟悉的态度^_^,试验过程中走了不少弯路，此文以结论为主，就不列出实验的设计过程，文笔不好，文章写得有点枯燥，写出来是希望有人来拍砖，因为我得出的结论是：预处理在有没有cache的情况下的执行效率都不及直接执行…… 我对自己的实验结果不愿接受。。如果说预处理只为了规范下Query，使cache命中率提高的话个人觉得大材小用了，希望有比较了解的人能指出事实究竟是什么样子的——NewSilen
实验准备
第一个文件NormalQuery.sql
代码如下:
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';
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，得到另外两个结果文件
实验结果
详细结果在最后提供了附件下载，有兴趣的朋友可以看下
结果分析
每一个SQL文件中执行了一百个查询语句，没有重复的查询语句，不存在查询cache，统计执行SQL的平均时间得出如下结果
从结果中可以看出，无论是先执行还是后执行，NormalQuery中的语句都比使用预处理语句的要快一些=.=！
那再来看看每一句查询具体的情况，Normal和Stmt的query各执行了两百次，每一步的详细信息如下：
从这里面可以看出，第一个，normalquery比stmtquery少一个步骤，第二个，虽然stmt在不少步骤上是优于normal的,但在executing一步上输掉太多，最后结果上也是落败
最后，再给出一个查询缓存的实验结果，具体步骤就不列了
在查询缓存的时候，Normal完胜……
写在最后
大概情况就是这样，我回忆了一下，网上说预处理可以提高效率的，基本都是用编程的方式去执行查询，不知道这个有没有关系，基础有限，希望园子里的大牛能看到，帮忙解惑
实验结果附件
MySQL预处理实验结果
相关推荐
-
mysqli预处理编译的深入理解
记得以前php点点通也写过mysqli的预处理的php教程,那时候只是看书乱写的,没懂原理,数月过后,突然明白了很多: 想想看.假如我们要插入很多1000个用户,你怎么做,for循环?还是mysqli处理多条sql? no!这些处理很慢的,php里面有很多操作mysql数据库的函数,无非是把sql语句传递给mysql数据库,真正处理sql语句的是mysql,mysql数据库是要编译sql语句进行执行的,上面这两种操作会对相同的sql语句进行多次编译,有这必要吗?程序员总是很聪明的,于是有了mys
-
PHP mysqli扩展库 预处理技术的使用分析
1.使用mysqli扩展库 预处理技术 mysqli stmt 向数据库添加3个用户 复制代码 代码如下: <?php //mysqli扩展库 预处理技术 mysqli stmt 向数据库添加3个用户 //1.创建mysqli对象 $mysqli = new MySQLi("localhost","root","root","test"); if($mysqli->connect_error){
-
PDO预处理语句PDOStatement对象使用总结
PDO对预处理语句的支持需要使用PDOStatement类对象,但该类对象并不是通过NEW关键字实例化出来的,而是通过PDO对象中的prepare()方法,在数据库服务器中准备好一个预处理的SQL语句后直接返回的.如果通过之前执行PDO对象中的query()方法返回的PDOStatement类对象,只代表的是一个结果集对象.而如果通过执行PDO对象中的prepare()方法产生的PDOStatement类对象,则为一个查询对象,能定义和执行参数化的SQL命令.PDOStatement类中的全部成
-
MySQL prepare语句的SQL语法
MySQL prepare语法: PREPARE statement_name FROM preparable_SQL_statement; /*定义*/ EXECUTE statement_name [USING @var_name [, @var_name] ...]; /*执行预处理语句*/ {DEALLOCATE | DROP} PREPARE statement_name /*删除定义*/ ; PREPARE语句用于预备一个语句,并指定名称statement_name,以后引用该语句.
-
理解Mysql prepare预处理语句
MySQL 5.1对服务器一方的预制语句提供支持.如果您使用合适的客户端编程界面,则这种支持可以发挥在MySQL 4.1中实施的高效客户端/服务器二进制协议的优势.候选界面包括MySQL C API客户端库(用于C程序).MySQL Connector/J(用于Java程序)和MySQL Connector/NET.例如,C API可以提供一套能组成预制语句API的函数调用.其它语言界面可以对使用了二进制协议(通过在C客户端库中链接)的预制语句提供支持.对预制语句,还有一个SQL界面可以利用.与
-
php+mysqli预处理技术实现添加、修改及删除多条数据的方法
本文实例讲述了php+mysqli预处理技术实现添加.修改及删除多条数据的方法.分享给大家供大家参考.具体分析如下: 首先来说说为什么要有预处理(预编译)技术?举个例子:假设要向数据库添加100个用户,按常规思路,就是向数据库发送100个执行请求,此时,按照 mysql 数据库的工作原理,它需要对每一条执行语句进行编译(这里就有100次).所以,这里的效率是非常低的. 预处理(预编译)技术的作用,就是减少编译的次数和时间,以提高效果.通过一个案例来说明,预处理(预编译)技术是如何做到的(好吧,先
-
php+mysqli使用预处理技术进行数据库查询的方法
本文实例讲述了php+mysqli使用预处理技术进行数据库查询的方法.分享给大家供大家参考.具体如下: 代码有些难度,需要基础知识比较扎实才能好理解,代码先放上来: 这里实现查询所有 id>5 的 id,title,contents值: 复制代码 代码如下: <?php $mysqli = new MySQLi("localhost","root","123456","liuyan"); if(!$mysqli){
-
PHP5 mysqli的prepare准备语句使用说明
mysqli对prepare的支持对于大访问量的网站是很有好处的,它极大地降低了系统开销,而且保证了创建查询的稳定性和安全性.prepare准备语句分为绑定参数和绑定结果,下面将会一一介绍! (1)绑定参数 看下面php代码: 复制代码 代码如下: <?php //创建连接 $mysqli=new mysqli("localhost","root","","volunteer"); //检查连接是否被创建 if (mys
-
MySQL中Stmt 预处理提高效率问题的小研究
复制代码 代码如下: 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里插入大量的数据大约1000w,目测会比较耗时.所以现在就像测试一下到底用什么插入数据的方法比较快捷高效. 下面就针对每一种方法分别测试不同数据量下的插入效率. 测试数据库的基本与操作如下: mysql> create database test; Query OK, 1 row affected (0.02 sec) mysql> use test; Database changed mysql> create table mytable(id
-
查找MySQL中查询慢的SQL语句方法
如何在mysql查找效率慢的SQL语句呢?这可能是困然很多人的一个问题,MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件,通过查看这个日志文件定位效率较低的SQL .下面介绍MySQL中如何查询慢的SQL语句 一.MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句 1,slow_query_log 这
-
浅谈MySQL中group_concat()函数的排序方法
group_concat()函数的参数是可以直接使用order by排序的.666.. 下面通过例子来说明,首先看下面的t1表. 比如,我们要查看每个人的多个分数,将该人对应的多个分数显示在一起,分数要从高到底排序. 可以这样写: SELECT username,GROUP_CONCAT(score ORDER BY score DESC) AS myScore FROM t1 GROUP BY username; 效果如下: 以上这篇浅谈MySQL中group_concat()函数的排序方法就
-
全面了解mysql中utf8和utf8mb4的区别
一.简介 MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode.好在utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换.当然,为了节省空间,一般情况下使用utf8也就够了. 二.内容描述 那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了.三个字节的 UT
-
关于Python中的向量相加和numpy中的向量相加效率对比
直接使用Python来实现向量的相加 # -*-coding:utf-8-*- #向量相加 def pythonsum(n): a = range(n) b = range(n) c = [] for i in range(len(a)): a[i] = i**2 b[i] = i**3 c.append(a[i]+b[i]) return a,b,c print pythonsum(4),type(pythonsum(4)) for arg in pythonsum(4): print arg
-
解决python写入mysql中datetime类型遇到的问题
刚开始使用python,还不太熟练,遇到一个datetime数据类型的问题: 在mysql数据库中,有一个datetime类型的字段用于存储记录的日期时间值.python程序中有对应的一个datetime变量dt. 现在需要往mysql数据库中添加记录,每次添加时,将datetime型变量dt写入mysql数据库tablename表中exTime字段里. 问题,如何写入?调试时,总是无法写入. 运行环境:windows10 python 3.6 mysql5.6.38 运行结果提示: Proce
-
python3实现往mysql中插入datetime类型的数据
昨天在这个上面找了好久的错,嘤嘤嘤~ 很多时候我们在爬取数据存储的时候都需要将当前时间作为一个依据,在python里面没有时间类型可以直接拿来就用的.我们只需要在存储之前将时间类型稍作修饰就行. datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") 如: #插入产品信息 insert_good_sql = """ INSERT INTO T_GOOD(good_name, good_type, img_
-
MySQL中的binary类型使用操作
本文主要向大家介绍了MySQL数据库之MySQL的binary类型操作,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助. 示例数据表: CREATE TABLE test_bin ( bin_id BINARY(16) NOT NULL ) Engine=InnoDB; 插入数据(内容是一个32位的UUID字符串值): INSERT INTO test_bin(bin_id) VALUES(UNHEX('FA34E10293CB42848573A4E39937F479'));
-
查询mysql中执行效率低的sql语句的方法
一些小技巧1. 如何查出效率低的语句?在MySQL下,在启动参数中设置 --log-slow-queries=[文件名],就可以在指定的日志文件中记录执行时间超过long_query_time(缺省为10秒)的SQL语句.你也可以在启动配置文件中修改long query的时间,如: 复制代码 代码如下: # Set long query time to 8 seconds long_query_time=8 2. 如何查询某表的索引?可使用SHOW INDEX语句,如: 复制代码 代码如下