对一个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
2016-06-23