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

秋敏君_1798

秋敏君_1798

2021-02-04

3997人浏览

转载

免费学习推荐: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的这种做法,也省去了要我们人为的删除预处理语句。

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载

4、实验猜想

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

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

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

5、实验数据记录  

序号 是否预处理 语句 是否远程数据库 返回数据量 每次实验语句执行总次数 三次实验平均总耗时/单位毫秒
1 是 select * from task where task.taskId in (?) 是 1000 1000 69822
2 否 select * from task where task.taskId in (arr) 是 1000 1000 66778
3 是 select * from task where task.taskId = ? 是 1 1000 1260
4 否 select * from task where task.taskId = id 是 1 1000 951
5 是 select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id 是 2 1000 2130
6 否 select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id 是 2 1000 1480
7 是 select * from task where task.taskId in (?) 否 1000 1000 57051
8 否 select * from task where task.taskId in (arr) 否 1000 1000 56235
9 是 select * from task where task.taskId = ? 否 1 1000 217
10 否 select * from task where task.taskId = id 否 1 1000 204
11 是 select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id 否 2 1000 366
12 否 select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id 否 2 1000 380

6、结论

         实验的数据结果和我预期的相差有点儿大,但经过反复检查测试代码和测试过程,确认测试本身应该没有问题。尊重实验数据,我们得出以下结论:

        1、通过实验5和实验6对比,实验11和实验12对比,可得猜想1是错误的。结论应该是:MySQL预处理和常规查询在简单语句和复杂语句下,都没有显著性的性能差别。

        2、通过实验1和实验2对比,实验7和实验8对比,可得猜想2是错误的。结论应该是:MySQL预处理和常规查询的结果在数据传输上没有显著性的性能差距。

        3、此外,对比远程数据库和本地数据库实验数据。可得结论:MySQL数据库在本地会给数据操作带来显著性的性能提高。

相关免费学习推荐:mysql数据库(视频)

相关文章

数码产品性能查询
数码产品性能查询

该软件包括了市面上所有手机CPU,手机跑分情况,电脑CPU,电脑产品信息等等,方便需要大家查阅数码产品最新情况,了解产品特性,能够进行对比选择最具性价比的商品。

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

60

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

60

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

60

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

40

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

140

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

160

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程