前言
在數(shù)據(jù)庫的開發(fā)過程中，經(jīng)常會(huì)遇到復(fù)雜的業(yè)務(wù)邏輯和對(duì)數(shù)據(jù)庫的操作，這個(gè)時(shí)候就會(huì)用存儲(chǔ)過程來封裝數(shù)據(jù)庫操作。如果項(xiàng)目的存儲(chǔ)過程較多，書寫又沒有一定的規(guī)范，將會(huì)影響以后的系統(tǒng)維護(hù)困難和大存儲(chǔ)過程邏輯的難以理解，另外如果數(shù)據(jù)庫的數(shù)據(jù)量大或者項(xiàng)目對(duì)存儲(chǔ)過程的性能要求很，就會(huì)遇到優(yōu)化的問題，否則速度有可能很慢，經(jīng)過親身經(jīng)驗(yàn)，一個(gè)經(jīng)過優(yōu)化過的存儲(chǔ)過程要比一個(gè)性能差的存儲(chǔ)過程的效率甚至高幾百倍。下面介紹某一個(gè)MySQL存儲(chǔ)過程優(yōu)化的整個(gè)過程。
在本文中，需要被優(yōu)化的存儲(chǔ)過程如下：
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ǔ)過程中使用到的表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ǔ)過程中使用到的另外一張表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ǔ)過程pr_dealtestnum的作用是根據(jù)輸入?yún)?shù)將tb_testnum_tmp表的數(shù)據(jù)插入到tb_testnum表中。
很明顯，雖然能夠?qū)崿F(xiàn)預(yù)期的功能，但存儲(chǔ)過程pr_dealtestnum的代碼還有改進(jìn)的地方。
下面，我們一步一步來對(duì)其進(jìn)行優(yōu)化。
優(yōu)化一
存儲(chǔ)過程pr_dealtestnum的主體是一條insert語句，但這條insert語句里面又包含了select語句，這樣的編寫是不規(guī)范的。因此，我們要把這條insert語句拆分成兩條語句，即先把數(shù)據(jù)從tb_testnum_tmp表中查找出來，再插入到tb_testnum表中。修改之后的存儲(chǔ)過程如下：
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ǔ)過程如下：
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í)值來表示執(zhí)行的結(jié)果，這樣也方便開發(fā)人員對(duì)程序流程的追蹤和調(diào)試。也就是說，在每條leave語句之前，都應(yīng)該有一個(gè)返回值，我們?yōu)榇硕x一個(gè)輸出參數(shù)。修改之后的存儲(chǔ)過程如下：
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表之后沒有列出具體的字段名，這個(gè)也是不規(guī)范的。如果在以后的軟件版本中，tb_testnum表中新增了字段，那么這條insert語句極有可能會(huì)報(bào)錯(cuò)。因此，規(guī)范的寫法是無論tb_testnum表中有多少字段，在執(zhí)行insert操作時(shí)，都要列出具體的字段名。修改之后的存儲(chǔ)過程如下：
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，表示插入失敗，那么我們就用一個(gè)返回參數(shù)值來表示操作失敗。修改之后的存儲(chǔ)過程如下：
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ǔ)過程，就有這么多需要優(yōu)化的地方，看來存儲(chǔ)過程的編寫也不是一件很簡(jiǎn)單的事情。確實(shí)，我們?cè)诰帉懘a(不僅僅是存儲(chǔ)過程)的時(shí)候，一定要從代碼的功能、可讀性、性能等多方面來考慮，這樣才能夠?qū)懗鰞?yōu)美的、具備較長生命周期的代碼，進(jìn)而開發(fā)出高質(zhì)量的軟件產(chǎn)品。希望本文能對(duì)大家學(xué)習(xí)MySQL存儲(chǔ)過程有所幫助，也謝謝大家對(duì)VeVb武林網(wǎng)的支持。
新聞熱點(diǎn)
疑難解答
圖片精選
