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_tmptb表中的資料。 很明顯，雖然能夠實現預期的功能，但預存程序

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

總結

從上面可以看出，一個短短的儲存過程，就有這麼多需要優化的地方，看來儲存過程的編寫也不是一件很簡單的事情。確實，我們在編寫程式碼(不只是預存程序)的時候，一定要從程式碼的功能、可讀性、效能等多方面來考慮，這樣才能夠寫出優美的、具備較長生命週期的程式碼，進而開發出高品質的軟體產品。希望本文能對大家學習MySQL儲存過程有所幫助，也謝謝大家對的支持。