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

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

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

前言

在數(shù)據(jù)庫(kù)的開(kāi)發(fā)過(guò)程中，經(jīng)常會(huì)遇到復(fù)雜的業(yè)務(wù)邏輯和對(duì)數(shù)據(jù)庫(kù)的操作，這個(gè)時(shí)候就會(huì)用存儲(chǔ)過(guò)程來(lái)封裝數(shù)據(jù)庫(kù)操作。如果項(xiàng)目的存儲(chǔ)過(guò)程較多，書(shū)寫(xiě)又沒(méi)有一定的規(guī)范，將會(huì)影響以后的系統(tǒng)維護(hù)困難和大存儲(chǔ)過(guò)程邏輯的難以理解，另外如果數(shù)據(jù)庫(kù)的數(shù)據(jù)量大或者項(xiàng)目對(duì)存儲(chǔ)過(guò)程的性能要求很，就會(huì)遇到優(yōu)化的問(wèn)題，否則速度有可能很慢，經(jīng)過(guò)親身經(jīng)驗(yàn)，一個(gè)經(jīng)過(guò)優(yōu)化過(guò)的存儲(chǔ)過(guò)程要比一個(gè)性能差的存儲(chǔ)過(guò)程的效率甚至高幾百倍。下面介紹某一個(gè)MySQL存儲(chǔ)過(guò)程優(yōu)化的整個(gè)過(guò)程。

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

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

在存儲(chǔ)過(guò)程中使用到的表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);

在存儲(chǔ)過(guò)程中使用到的另外一張表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);

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

很明顯，雖然能夠?qū)崿F(xiàn)預(yù)期的功能，但存儲(chǔ)過(guò)程pr_dealtestnum的代碼還有改進(jìn)的地方。

下面，我們一步一步來(lái)對(duì)其進(jìn)行優(yōu)化。

優(yōu)化一

存儲(chǔ)過(guò)程pr_dealtestnum的主體是一條insert語(yǔ)句，但這條insert語(yǔ)句里面又包含了select語(yǔ)句，這樣的編寫(xiě)是不規(guī)范的。因此，我們要把這條insert語(yǔ)句拆分成兩條語(yǔ)句，即先把數(shù)據(jù)從tb_testnum_tmp表中查找出來(lái)，再插入到tb_testnum表中。修改之后的存儲(chǔ)過(guò)程如下：

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ù)。修改之后的存儲(chǔ)過(guò)程如下：

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)該有一個(gè)標(biāo)識(shí)值來(lái)表示執(zhí)行的結(jié)果，這樣也方便開(kāi)發(fā)人員對(duì)程序流程的追蹤和調(diào)試。也就是說(shuō)，在每條leave語(yǔ)句之前，都應(yīng)該有一個(gè)返回值，我們?yōu)榇硕x一個(gè)輸出參數(shù)。修改之后的存儲(chǔ)過(guò)程如下：

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);”語(yǔ)句中，tb_testnum表之后沒(méi)有列出具體的字段名，這個(gè)也是不規(guī)范的。如果在以后的軟件版本中，tb_testnum表中新增了字段，那么這條insert語(yǔ)句極有可能會(huì)報(bào)錯(cuò)。因此，規(guī)范的寫(xiě)法是無(wú)論tb_testnum表中有多少字段，在執(zhí)行insert操作時(shí)，都要列出具體的字段名。修改之后的存儲(chǔ)過(guò)程如下：

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語(yǔ)句之后，要用MySQL中自帶的@error_count參數(shù)來(lái)判斷插入數(shù)據(jù)是否成功，方便開(kāi)發(fā)人員跟蹤執(zhí)行結(jié)果。如果該參數(shù)的值不為0，表示插入失敗，那么我們就用一個(gè)返回參數(shù)值來(lái)表示操作失敗。修改之后的存儲(chǔ)過(guò)程如下：

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

從上面可以看出，一個(gè)短短的存儲(chǔ)過(guò)程，就有這么多需要優(yōu)化的地方，看來(lái)存儲(chǔ)過(guò)程的編寫(xiě)也不是一件很簡(jiǎn)單的事情。確實(shí)，我們?cè)诰帉?xiě)代碼(不僅僅是存儲(chǔ)過(guò)程)的時(shí)候，一定要從代碼的功能、可讀性、性能等多方面來(lái)考慮，這樣才能夠?qū)懗鰞?yōu)美的、具備較長(zhǎng)生命周期的代碼，進(jìn)而開(kāi)發(fā)出高質(zhì)量的軟件產(chǎn)品。希望本文能對(duì)大家學(xué)習(xí)MySQL存儲(chǔ)過(guò)程有所幫助，也謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章: