对一个MySQL存储过程的优化

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 在编写MySQL存储过程的过程中,我们会时不时地需要对某些存储过程进行优化,其目的是确保代码的可读性、正确性及运行性能。

在编写MySQL存储过程的过程中,我们会时不时地需要对某些存储过程进行优化,其目的是确保代码的可读性、正确性及运行性能。本文以作者实际工作为背景,介绍了对某一个MySQL存储过程优化的整个过程。

在本文中,需要被优化的存储过程如下:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
   in    p_boxnumber    varchar(30)
)
pr_dealtestnum_label:begin

       insert into tb_testnum select boxnumber,usertype from tb_testnum_tmp where boxnumber= p_boxnumber;

       leave pr_dealtestnum_label;
end;
//
delimiter ;
select 'create procedure pr_dealtestnumok';

在存储过程中使用到的表tb_testnum结构如下:

drop table if exists tb_testnum;

create table tb_testnum
(
   boxnumber  varchar(30)  not null,
   usertype   int          not null                                                                                 
);
create unique index idx1_tb_testnum ontb_testnum(boxnumber);

在存储过程中使用到的另外一张表tb_testnum_tmp结构如下:

drop table if exists tb_testnum_tmp;

create table tb_testnum_tmp
(
   boxnumber  varchar(30)  not null,
   usertype   int          not null    
);
create unique index idx1_tb_testnum_tmp ontb_testnum_tmp(boxnumber);

从两个表的结构可以看出,tb_testnum和tb_testnum_tmp所包含的字段完全相同,存储过程pr_dealtestnum的作用是根据输入参数将tb_testnum_tmp表的数据插入到tb_testnum表中。

很明显,虽然能够实现预期的功能,但存储过程pr_dealtestnum的代码还有改进的地方。下面,我们一步一步来对其进行优化。

优化一
存储过程pr_dealtestnum的主体是一条insert语句,但这条insert语句里面又包含了select语句,这样的编写是不规范的。因此,我们要把这条insert语句拆分成两条语句,即先把数据从tb_testnum_tmp表中查找出来,再插入到tb_testnum表中。修改之后的存储过程如下:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
   in    p_boxnumber    varchar(30)
)
pr_dealtestnum_label:begin
       declare  p_usertype    int;

       select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;

       insert into tb_testnum values(p_boxnumber,p_usertype);

       leave pr_dealtestnum_label;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

优化二
在向tb_testnum表插入数据之前,要判断该条数据在表中是否已经存在了,如果存在,则不再插入数据。同理,在从tb_testnum_tmp表中查询数据之前,要先判断该条数据在表中是否存在,如果存在,才能从表中查找数据。修改之后的存储过程如下:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
   in    p_boxnumber    varchar(30)
)
pr_dealtestnum_label:begin
       declare  p_usertype   int;
       declare  p_datacount  int;

       select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
       if p_datacount > 0 then
       begin
           select usertype into p_usertype fromtb_testnum_tmp where boxnumber=p_boxnumber;
       end;
       else
       begin
           leave pr_dealtestnum_label;
       end;
       end if;

       select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
       if p_datacount = 0 then
       begin
           insert into tb_testnum values(p_boxnumber,p_usertype);
           leave pr_dealtestnum_label;
       end;
       else
       begin
           leave pr_dealtestnum_label;
       end;
       end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

优化三
不管向tb_testnum表插入数据的操作执行成功与否,都应该有一个标识值来表示执行的结果,这样也方便开发人员对程序流程的追踪和调试。也就是说,在每条leave语句之前,都应该有一个返回值,我们为此定义一个输出参数。修改之后的存储过程如下:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
   in    p_boxnumber  varchar(30),
   out   p_result     int  -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
       declare  p_usertype   int;
       declare  p_datacount  int;

       select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
       if p_datacount > 0 then
       begin
           select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
       end;
       else
       begin
           set p_result = 1;
           leave pr_dealtestnum_label;
       end;
       end if;

       select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
       if p_datacount = 0 then
       begin
           insert into tb_testnum values(p_boxnumber,p_usertype);
           set p_result = 0;
           leave pr_dealtestnum_label;
       end;
       else
       begin
           set p_result = 2;
           leave pr_dealtestnum_label;
       end;
       end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

优化四
我们注意到“insert into tb_testnum values(p_boxnumber,p_usertype);”语句中,tb_testnum表之后没有列出具体的字段名,这个也是不规范的。如果在以后的软件版本中,tb_testnum表中新增了字段,那么这条insert语句极有可能会报错。因此,规范的写法是无论tb_testnum表中有多少字段,在执行insert操作时,都要列出具体的字段名。修改之后的存储过程如下:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
   in    p_boxnumber  varchar(30),
   out   p_result     int   -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
       declare  p_usertype   int;
       declare  p_datacount  int;

       select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
       if p_datacount > 0 then
       begin
           select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
       end;
       else
       begin
           set p_result = 1;
           leave pr_dealtestnum_label;
       end;
       end if;

       select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
       if p_datacount = 0 then
       begin
           insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype);
           set p_result = 0;
           leave pr_dealtestnum_label;
       end;
       else
       begin
           set p_result = 2;
           leave pr_dealtestnum_label;
       end;
       end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

优化五
在执行insert语句之后,要用MySQL中自带的@error_count参数来判断插入数据是否成功,方便开发人员跟踪执行结果。如果该参数的值不为0,表示插入失败,那么我们就用一个返回参数值来表示操作失败。修改之后的存储过程如下:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
    in    p_boxnumber varchar(30),
    out   p_result    int   -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
       declare  p_usertype   int;
       declare  p_datacount  int;

       select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
       if p_datacount> 0 then
       begin
           select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
       end;
       else
       begin
           set p_result = 1;
           leave pr_dealtestnum_label;
       end;
       end if;

       select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
       if p_datacount = 0then
       begin
           insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype);
           if @error_count<>0 then
           begin
               set p_result= 3;
           end;
           else
           begin
               set p_result= 0;
           end;
           end if;
       end;
       else
       begin
           set p_result = 2;
       end;
       end if;

       leave pr_dealtestnum_label;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

结束语
从上面可以看出,一个短短的存储过程,就有这么多需要优化的地方,看来存储过程的编写也不是一件很简单的事情。确实,我们在编写代码(不仅仅是存储过程)的时候,一定要从代码的功能、可读性、性能等多方面来考虑,这样才能够写出优美的、具备较长生命周期的代码,进而开发出高质量的软件产品。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
27天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
1月前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
71 3
|
1月前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
73 1
|
2月前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
94 9
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
194 1
|
1月前
|
存储 SQL NoSQL
|
2月前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
67 5
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
79 0
|
2月前
|
存储 监控 关系型数据库
MySQL并发控制与管理:优化数据库性能的关键
【10月更文挑战第17天】MySQL并发控制与管理:优化数据库性能的关键
225 0
|
7天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。