您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL数据库的SQL预处理技术,优化数据库必备

时间:2019-08-30 09:55:41  来源:  作者:

概述

今天主要分享下MySQL的SQL预处理方面内容,也是属于优化必须会的一方面,建议大家都能掌握。


一、SQL 语句的执行处理

1、即时 SQL

一条 SQL 在 DB 接收到最终执行完毕返回,大致的过程如下:

精心总结的MySQL数据库的SQL预处理技术,优化数据库必备

 

  • 1. 词法和语义解析;
  • 2. 优化 SQL 语句,制定执行计划;
  • 3. 执行并返回结果;

如上,一条 SQL 直接是走流程处理,一次编译,单次运行,此类普通语句被称作 Immediate Statements (即时 SQL)。

2、预处理 SQL

但是,绝大多数情况下,某需求某一条 SQL 语句可能会被反复调用执行,或者每次执行的时候只有个别的值不同(比如 select 的 where 子句值不同,update 的 set 子句值不同,insert 的 values 值不同)。如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等,则效率就明显不行了。

所谓预编译语句就是将此类 SQL 语句中的值用占位符替代,可以视为将 SQL 语句模板化或者说参数化,一般称这类语句叫Prepared Statements。

预编译语句的优势在于归纳为:一次编译、多次运行,省去了解析优化等过程;此外预编译语句能防止 SQL 注入。

注意:

虽然可能是通过预处理 SQL 的方式一定程度的提高了效率,但是对于优化而言,最优的执行计划不是光靠 SQL 语句的模板化来实现的,往往还是需要通过具体值来预估出成本代价。


二、Prepared SQL Statement Syntax

MySQL 官方将 prepare、execute、deallocate 统称为 PREPARE STATEMENT。翻译也就习惯的称其为预处理语句。

MySQL 预处理语句的支持版本较早,所以我们目前普遍使用的 MySQL 版本都是支持这一语法的。

语法

# 定义预处理语句
PREPARE stmt_name FROM preparable_stmt;
# 执行预处理语句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
# 删除(释放)定义
{DEALLOCATE | DROP} PREPARE stmt_name;

1、利用字符串定义预处理 SQL (直角三角形计算)

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hwb';
mysql> SET @a = 3;
mysql> SET @b = 4; 
mysql> EXECUTE stmt1 USING @a, @b;
mysql> DEALLOCATE PREPARE stmt1; 
精心总结的MySQL数据库的SQL预处理技术,优化数据库必备

 

2、利用变量定义预处理 SQL (直角三角形计算)

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hwb';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @c = 6;
mysql> SET @d = 8;
mysql> EXECUTE stmt2 USING @c, @d;
mysql> DEALLOCATE PREPARE stmt2;
精心总结的MySQL数据库的SQL预处理技术,优化数据库必备

 

3、解决无法传参问题

我们知道,对于 LIMIT 子句中的值,必须是常量,不得使用变量,也就是说不能使用:SELECT * FROM TABLE LIMIT @skip, @numrows; 如此,这里就可以用 PREPARE 语句解决此问题。

mysql> SET @skip = 1; 
mysql> SET @numrows = 3;
mysql> SELECT host,user FROM mysql.user LIMIT @skip, @numrows;
mysql> PREPARE stmt3 FROM "SELECT host,user FROM mysql.user LIMIT ?, ?";
mysql> EXECUTE stmt3 USING @skip, @numrows;
mysql> DEALLOCATE PREPARE stmt3;
精心总结的MySQL数据库的SQL预处理技术,优化数据库必备

 

如此一来,结合2中介绍的利用变量定义预处理 SQL 也就基本解决了传参时语法报错问题了,类似的:用变量传参做表名时,MySQL 会把变量名当做表名,这样既不是本意,也会是语法错误,在 SQL Server 的解决办法是利用字符串拼接穿插变量进行传参,再将整条 SQL 语句作为变量,最后是用 sp_executesql 调用该拼接 SQL 执行,而 Prepared SQL Statement 可谓异曲同工之妙。

mysql> SET @table = 'mysql.user';
mysql> SET @s = CONCAT('SELECT HOST,USER FROM ', @table);
mysql> PREPARE stmt4 FROM @s;
mysql> EXECUTE stmt4;
mysql> DROP PREPARE stmt4;
精心总结的MySQL数据库的SQL预处理技术,优化数据库必备

 

 


三、预处理 SQL 使用注意点

1、stmt_name 作为 preparable_stmt 的接收者,唯一标识,不区分大小写。

2、preparable_stmt 语句中的 ? 是个占位符,所代表的是一个字符串,不需要将 ? 用引号包含起来。

3、定义一个已存在的 stmt_name ,原有的将被立即释放,类似于变量的重新赋值。

4、PREPARE stmt_name 的作用域是session级

精心总结的MySQL数据库的SQL预处理技术,优化数据库必备

 

可以通过 max_prepared_stmt_count 变量来控制全局最大的存储的预处理语句。

mysql> show variables like 'max_prepared%';
精心总结的MySQL数据库的SQL预处理技术,优化数据库必备

 


最后,预处理编译 SQL 是占用资源的,所以在使用后注意及时使用 DEALLOCATE PREPARE 释放资源,这也是一个好习惯。



Tags:SQL预处理   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
概述今天主要分享下MySQL的SQL预处理方面内容,也是属于优化必须会的一方面,建议大家都能掌握。一、SQL 语句的执行处理1、即时 SQL一条 SQL 在 DB 接收到最终执行完毕返回,大致...【详细内容】
2019-08-30  Tags: SQL预处理  点击:(190)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
相关文章
    无相关信息
最新更新
栏目热门
栏目头条