MySQL 存储过程注意事项

老叶茶馆 2023-09-08

212

转发松华老师的最新分享文章

大家好，已经好久没写文章。

今天给大家分享一个，关于存储过程或者udf 方面的性能案例

数据是基于之前的 employees 表改动

CREATE TABLE `ec` (
  `emp_no` varchar(30) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) 


insert into ec select * from employees ;


insert into ec select concat('11',emp_no) ,birth_date,first_name,last_name,gender,hire_date from ec ;
insert into ec select concat('22',emp_no) ,birth_date,first_name,last_name,gender,hire_date from ec ;
insert into ec select concat('33',emp_no) ,birth_date,first_name,last_name,gender,hire_date from ec ;
insert into ec select concat('44',emp_no) ,birth_date,first_name,last_name,gender,hire_date from ec ;

先看下如下两个sql 执行计划可以发现一个走索引全扫描一个走const

root@mysql3306.sock>[employees]>desc select count(1) from ec where emp_no = 99999 ;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | ec    | NULL       | index | PRIMARY       | ix_hd_ec | 3       | NULL | 4657882 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
1 row in set, 4 warnings (0.00 sec)


root@mysql3306.sock>[employees]>desc select count(1) from ec where emp_no = '99999' ;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | ec    | NULL       | const | PRIMARY       | PRIMARY | 122     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

现在开始写如下存储过程

DELIMITER $$ 
create PROCEDURE z1 ( in eno varchar(100))
begin 
declare s1 varchar(1000) ;
declare s2 varchar(1000) ;


set s1=eno ;
set s2=concat('select count(1) from ec where emp_no = ',s1 ) ;


set @s3 = s2 ;
prepare dynaquery from @s3 ;
execute dynaquery ;
deallocate prepare dynaquery ;
end $$
DELIMITER ;


root@mysql3306.sock>[employees]>call z1(99999) ;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (1.45 sec)


Query OK, 0 rows affected (1.45 sec)


root@mysql3306.sock>[employees]>select count(1) from ec where emp_no = 99999 ;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (1.36 sec)

运行结果跟上面的SQL 差不多

现在改写成如下存储过程

DELIMITER $$ 
create PROCEDURE z2 ( in eno varchar(100))
begin 
declare s1 varchar(1000) ;
declare s2 varchar(1000) ;


set s1=eno ;
set s2=concat('select count(1) from ec where emp_no = \'',s1,'\'' ) ;


set @s3 = s2 ;
prepare dynaquery from @s3 ;
execute dynaquery ;
deallocate prepare dynaquery ;
end $$
DELIMITER ;


root@mysql3306.sock>[employees]>call z2(99999) ;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


root@mysql3306.sock>[employees]>select count(1) from ec where emp_no = '99999' ;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

运行结果跟上面的SQL 一样高效，达到了我们预期添加''

再看下面的一种

DELIMITER $$ 
create PROCEDURE z3 ( in eno varchar(100))
begin 
declare s1 varchar(1000) ;
declare s2 varchar(1000) ;


set s1=eno ;
select count(1) from ec where emp_no =  s1  ;
 
end $$
DELIMITER ;


root@mysql3306.sock>[employees]>select count(1) from ec where emp_no = '99999' ;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)


root@mysql3306.sock>[employees]>call z3(99999) ;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)

运行结果跟'' 是一样的。

那有人有疑问，明明可以写z3 这种那为什么还要z1、z2这种动态sql呢？

这是因为动态写法可以省代码，例如如果有个表不是分区表而是按日期的那种单表，运行时间的日期查询对应表等。如果不用动态而用静态sql 需要写很多次。

本次案例是在如下版本中进行的

root@mysql3306.sock>[employees]>\s
--------------
/usr/local/mysql/bin/mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)


Connection id:          29124
Current database:       employees
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.31 MySQL Community Server -

结论如下：使用动态的时候，即使你的参数设定为 varchar 你也一定要显式改变，如果用静态就不需要。

谢谢大家！

全文完。

《深入浅出MGR》视频课程

戳此小程序即可直达B站

https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0

文章推荐：

MySQL的match函数在sp中使用的BUG解析
MySQL 8.0.29 instant DDL 数据腐化问题分析
探索GreatADM：图形化部署MGR的全新体验
芬达：openEuler+GreatSQL+dbops玩转MGR
GreatSQL删除表分区特别慢的原因分析
MySQL对derived table的优化处理与使用限制
MySQL一次大量内存消耗的跟踪
分布式编译系统的搭建
MySQL Shell 8.0.32 for GreatSQL编译安装
MySQL运行时的可观测性
Myloader导入更快吗？并没有。。。

自打有了GIPKs，DBA和开发再也不用battle了
重现一条简单SQL的优化过程

想看更多技术好文，点个“在看”吧！

文章转载自老叶茶馆，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL 存储过程 注意事项

评论

MySQL 存储过程注意事项