[[179304]]

在編寫MySQL存儲過程的過程中，我們會時不時地需要對某些存儲過程進行優(yōu)化，其目的是確保代碼的可讀性、正確性及運行性能。本文以作者實際工作為背景，介紹了對某一個MySQL存儲過程優(yōu)化的整個過程。

在本文中，需要被優(yōu)化的存儲過程如下：

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結(jié)構(gòu)如下：

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結(jié)構(gòu)如下：

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

從兩個表的結(jié)構(gòu)可以看出，tb_testnum和tb_testnum_tmp所包含的字段完全相同，存儲過程pr_dealtestnum的作用是根據(jù)輸入?yún)?shù)將tb_testnum_tmp表的數(shù)據(jù)插入到tb_testnum表中。

很明顯，雖然能夠?qū)崿F(xiàn)預(yù)期的功能，但存儲過程pr_dealtestnum的代碼還有改進的地方。下面，我們一步一步來對其進行優(yōu)化。

優(yōu)化一

存儲過程pr_dealtestnum的主體是一條insert語句，但這條insert語句里面又包含了select語句，這樣的編寫是不規(guī)范的。因此，我們要把這條insert語句拆分成兩條語句，即先把數(shù)據(jù)從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'; 

優(yōu)化二

在向tb_testnum表插入數(shù)據(jù)之前，要判斷該條數(shù)據(jù)在表中是否已經(jīng)存在了，如果存在，則不再插入數(shù)據(jù)。同理，在從tb_testnum_tmp表中查詢數(shù)據(jù)之前，要先判斷該條數(shù)據(jù)在表中是否存在，如果存在，才能從表中查找數(shù)據(jù)。修改之后的存儲過程如下：

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

優(yōu)化三

不管向tb_testnum表插入數(shù)據(jù)的操作執(zhí)行成功與否，都應(yīng)該有一個標識值來表示執(zhí)行的結(jié)果，這樣也方便開發(fā)人員對程序流程的追蹤和調(diào)試。也就是說，在每條leave語句之前，都應(yīng)該有一個返回值，我們?yōu)榇硕x一個輸出參數(shù)。修改之后的存儲過程如下：

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

優(yōu)化四

我們注意到“insert into tb_testnum values(p_boxnumber,p_usertype);”語句中，tb_testnum表之后沒有列出具體的字段名，這個也是不規(guī)范的。如果在以后的軟件版本中，tb_testnum表中新增了字段，那么這條insert語句極有可能會報錯。因此，規(guī)范的寫法是無論tb_testnum表中有多少字段，在執(zhí)行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'; 

優(yōu)化五

在執(zhí)行insert語句之后，要用MySQL中自帶的@error_count參數(shù)來判斷插入數(shù)據(jù)是否成功，方便開發(fā)人員跟蹤執(zhí)行結(jié)果。如果該參數(shù)的值不為0，表示插入失敗，那么我們就用一個返回參數(shù)值來表示操作失敗。修改之后的存儲過程如下：

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

結(jié)束語

從上面可以看出，一個短短的存儲過程，就有這么多需要優(yōu)化的地方，看來存儲過程的編寫也不是一件很簡單的事情。確實，我們在編寫代碼(不僅僅是存儲過程)的時候，一定要從代碼的功能、可讀性、性能等多方面來考慮，這樣才能夠?qū)懗鰞?yōu)美的、具備較長生命周期的代碼，進而開發(fā)出高質(zhì)量的軟件產(chǎn)品。

【本文是51CTO專欄作者周兆熊的原創(chuàng)文章，作者微信公眾號：周氏邏輯(logiczhou)】