介绍MySQL数据库的预处理(prepared statement)性能测试


本文摘自PHP中文网,作者coldplay.xixi,侵删。

免费学习推荐:mysql视频教程

1、预处理干了什么

当我们提交一条数据库语句时,语句到达数据库服务那边,数据库服务需要解析这条sql语句,比如说语法检查,查询条件先后优化,然后才执行。对于预处理,简单来说就是把客户端与数据库服务原本一次交互的分成两次。首先,提交数据库语句,让数据库服务先解析这条语句。其次,提交参数,调用语句并执行。这样对于多次重复执行的语句来说,可以提交并解析一次数据库语句就可以了,然后不断的调用刚刚解析过得语句并执行。这样就省去了多次解析同一条语句的时间。从而达到提高效率的目的。

预处理语句支持占位符(place holder),通过绑定占位符的方式提交参数。一个非常重要的一点是,能与占位符绑定的只能是值,而不能是sql语句的一些关键词。例如语句:“select * from student where student.id = ?”。如果放入占位符(?)中的是“1 or 1=1”,那么“1 or 1=1”就会被当成一个值,即用``符号包括起来,最终这条非法的语句就出错了。从而达到放sql注入的漏洞(sql injestion)。

预处理机制主要的三步骤:

1、将语句进行预处理

2、执行语句

3、析构掉预处理语句。

2、关于`performance_schema`.`prepared_statements_instances` 表的介绍

运行sql脚本:show global variable like ‘%prepare%’。 可以看到一个叫‘performance_schema_max_prepared_statement_instances的系统变量。其值为0表示不启用预处理语句性能数据记录表`performance_schema`.`prepared_statements_instances`;-1表示记录的数量动态处理;其他正整数值则表示performance_schema_max_prepared_statement_instances记录的最大条数。

表`performance_schema`.`prepared_statements_instances`又是什么呢?它是用来记录预处理语句的一些基本信息和性能数据。比如预处理语句的ID,预处理语句的名字,预处理语句的具体语句内容,预处理语句被执行的次数,每次执行耗时,每条预处理语句所属的线程id等。当我们创建一条预处理语句时,就会插入一条数据到这张表里。预处理语句是基于连接的,连接断开,则预处理语句自动删除。但`performance_schema`.`prepared_statements_instances`表是全局的,它与数据库连接没关系。有了这些数据,我们就可以知道,1、代码中执行的语句是否真的做了预处理,2、通过了解预处理语句的执行情况来决定业务中是否需要把一个语句进行预处理。

3、qt prepare函数说明

根据我自己本身的项目需求,这次测试的客户端代码使用的是Qt。这里记录一个关键的函数:QSqlQuery类的prepare函数。调用prepare函数即是向数据库提交一个创建预处理语句的命令。意味着调用期间,是会与数据库服务进行一次交互的。需要注意的是,当同一个QSqlQuery类对象调用第二次prepare时,会将第一次调用prepare创建的预处理语句删除掉,然后再创建一条预处理语句,即便是这两条预处理语句是一模一样的。在调用QSqlQuery的exec函数时,也会将QSqlQuery先前创建的预处理语句删除掉。所以在查询结束,关闭掉连接,或者查询又执行了其他语句,从而导致`performance_schema`.`prepared_statements_instances`表没有相关预处理语句的记录,就会误认为预处理语句创建失败。其实Qt的这种做法,也省去了要我们人为的删除预处理语句。

4、实验猜想

常规执行的语句和预处理后执行的语句不同点在于,在多次执行的情况下,预处理语句只需解析一次sql语句,而之后多花时间在传输参数和绑定参数上。预处理语句在返回结果时,使用的是二进制传输协议,而普通语句使用的是文本格式的传输协议。因此我们做出以下猜想并验证。

1、如果执行的是简单语句,那么普通执行和预处理执行性能上差别不大。预处理语句在重复执行复杂的语句情况下才展现出优势。

2、在查询结果集是大数据量的情况下,预处理语句会展现出性能优势。

5、实验数据记录

阅读剩余部分

相关阅读 >>

详解mysql数据库之事务及存储引擎

mysql字符串如何转date日期

怎样修改mysql列的数据类型?

mysql正则匹配模糊法查询某个字段

怎么在命令行打开mysql

mysql中如何进行子查询?

mysql 如何写循环语句

mysql常见优化方案汇总

mysql什么情况下会死锁

远程连接mysql失败了怎么办

更多相关阅读请进入《mysql》频道 >>


数据库系统概念 第6版
书籍

数据库系统概念 第6版

机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。



打赏

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码打赏,您说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

分享从这里开始,精彩与您同在

评论

管理员已关闭评论功能...