MySQL存儲過程的優(yōu)化實(shí)例

更新時間：2016年07月26日 11:23:22 投稿：daisy

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

前言

在數(shù)據(jù)庫的開發(fā)過程中，經(jīng)常會遇到復(fù)雜的業(yè)務(wù)邏輯和對數(shù)據(jù)庫的操作，這個時候就會用存儲過程來封裝數(shù)據(jù)庫操作。如果項(xiàng)目的存儲過程較多，書寫又沒有一定的規(guī)范，將會影響以后的系統(tǒng)維護(hù)困難和大存儲過程邏輯的難以理解，另外如果數(shù)據(jù)庫的數(shù)據(jù)量大或者項(xiàng)目對存儲過程的性能要求很，就會遇到優(yōu)化的問題，否則速度有可能很慢，經(jīng)過親身經(jīng)驗(yàn)，一個經(jīng)過優(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的代碼還有改進(jìn)的地方。

下面，我們一步一步來對其進(jìn)行優(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)該有一個標(biāo)識值來表示執(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語句極有可能會報(bào)錯。因此，規(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)化的地方，看來存儲過程的編寫也不是一件很簡單的事情。確實(shí)，我們在編寫代碼(不僅僅是存儲過程)的時候，一定要從代碼的功能、可讀性、性能等多方面來考慮，這樣才能夠?qū)懗鰞?yōu)美的、具備較長生命周期的代碼，進(jìn)而開發(fā)出高質(zhì)量的軟件產(chǎn)品。希望本文能對大家學(xué)習(xí)MySQL存儲過程有所幫助，也謝謝大家對腳本之家的支持。

您可能感興趣的文章: