Mysql必读MySQL存储过程的优化实例

《Mysql必读MySQL存储过程的优化实例》要点：
本文介绍了Mysql必读MySQL存储过程的优化实例，希望对您有用。如果有疑问，可以联系我们。

前言MYSQL入门

在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用存储过程来封装数据库操作.如果项目的存储过程较多,书写又没有一定的规范,将会影响以后的系统维护困难和大存储过程逻辑的难以理解,另外如果数据库的数据量大或者项目对存储过程的性能要求很,就会遇到优化的问题,否则速度有可能很慢,经过亲身经验,一个经过优化过的存储过程要比一个性能差的存储过程的效率甚至高几百倍.下面介绍某一个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结构如下：MYSQL入门

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结构如下：MYSQL入门

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表中.MYSQL入门

很明显,虽然能够实现预期的功能,但存储过程pr_dealtestnum的代码还有改进的地方.MYSQL入门

下面,我们一步一步来对其进行优化.MYSQL入门

优化一 MYSQL入门

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

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';

优化二
MYSQL入门

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

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';

优化三
MYSQL入门

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

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';

优化四
MYSQL入门

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!