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

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

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

前言

在數(shù)據(jù)庫的開發(fā)過程中，經(jīng)常會遇到復雜的業(yè)務邏輯和對數(shù)據(jù)庫的操作，這個時候就會用存儲過程來封裝數(shù)據(jù)庫操作。如果項目的存儲過程較多，書寫又沒有一定的規(guī)范，將會影響以后的系統(tǒng)維護困難和大存儲過程邏輯的難以理解，另外如果數(shù)據(jù)庫的數(shù)據(jù)量大或者項目對存儲過程的性能要求很，就會遇到優(yōu)化的問題，否則速度有可能很慢，經(jīng)過親身經(jīng)驗，一個經(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)預期的功能，但存儲過程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í)行成功與否，都應該有一個標識值來表示執(zhí)行的結(jié)果，這樣也方便開發(fā)人員對程序流程的追蹤和調(diào)試。也就是說，在每條leave語句之前，都應該有一個返回值，我們?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)品。希望本文能對大家學習MySQL存儲過程有所幫助，也謝謝大家對腳本之家的支持。

您可能感興趣的文章:

mysql優(yōu)化系列 DELETE子查詢改寫優(yōu)化
有個采用子查詢的DELETE執(zhí)行得非常慢，改寫成SELECT后執(zhí)行卻很快，最后把這個子查詢DELETE改寫成JOIN優(yōu)化過程
2016-08-08
MySQL?緩存機制與架構(gòu)解析(最新推薦)
本文詳細介紹了MySQL的緩存機制和整體架構(gòu),包括一級緩存（InnoDB?Buffer?Pool）和二級緩存（Query?Cache）,文章還探討了SQL查詢執(zhí)行全流程,并分析了MySQL?8.0移除查詢緩存的原因,最后,提出了應用層緩存和InnoDB緩沖池優(yōu)化的建議,感興趣的朋友跟隨小編一起看看吧
2025-02-02
幾個MySQL高頻面試題的解答
這篇文章主要介紹了幾個MySQL高頻面試題的解答，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下
2021-01-01
mysql學習筆記之數(shù)據(jù)引擎
插件式存儲引擎是MySQL數(shù)據(jù)庫最重要的特征之一，用戶可以根據(jù)應用的需要尋找如何存儲和索引數(shù)據(jù)、是否使用事務等。MySQL默認支持多種存儲引擎，以適用于不同領域的數(shù)據(jù)庫應用需求，用戶可以通過選擇選擇不同的存儲引擎提供應用的效率，提供靈活的存儲
2017-02-02
MySQL觸發(fā)器使用詳解
本文主要詳細介紹了mysql數(shù)據(jù)庫的觸發(fā)器的相關知識，非常的全面，有需要的小伙伴參考下吧。
2015-01-01
MySQL連接時出現(xiàn)2003錯誤的實現(xiàn)
本文主要介紹了MySQL連接時出現(xiàn)2003錯誤的實現(xiàn)，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2022-05-05
linux下使用RPM安裝mysql5.7.17
這篇文章主要為大家詳細介紹了linux下使用RPM安裝mysql5.7.17的相關代碼，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2017-03-03
MySql中的Full?Text?Search全文索引優(yōu)化
這篇文章主要為大家介紹了MySql中的Full?Text?Search全文索引優(yōu)化示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪
2023-05-05
MSSQL output使用
存儲過程 output 輸出參數(shù) 可以是一個字符串
2009-05-05
解決seata不能使用mysql8版本的問題方法
這篇文章主要介紹了解決seata不能使用mysql8版本的問題方法，文中通過示例和圖文介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2020-12-12