对一个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结构如下：

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

结束语

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

【本文是专栏作者周兆熊的原创文章，作者微信公众号：周氏逻辑(logiczhou)】

本文名称：对一个MySQL存储过程的优化
转载来源：http://www.aqpgk.com/article/dhiispd.html

新闻中心

其他资讯