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_tmp 테이블의 데이터를 tb_testnum 테이블에 삽입합니다.

분명히 예상한 기능을 달성할 수 있지만 저장 프로시저 pr_dealtestnum의 코드에는 여전히 개선의 여지가 있습니다.

아래에서는 단계별로 최적화해보겠습니다.

최적화 1

저장 프로시저 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';

최적화 2

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

최적화 3

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

최적화 4

"insert into tb_testnum values(p_boxnumber,p_usertype);” 문에서 tb_testnum 테이블 뒤에 특정 필드 이름이 나열되지 않았으며 이 역시 불규칙합니다. 향후 소프트웨어 버전에서는 tb_testnum 테이블에 새 필드가 추가되면 이 삽입 문은 오류를 보고할 가능성이 높습니다. 따라서 표준 작성 방법은 tb_testnum 테이블에 필드가 몇 개 있더라도 세부 정보를 작성하는 것입니다. 삽입 작업을 수행할 때 나열되어야 합니다. 수정된 저장 프로시저는 다음과 같습니다.

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

최적화 5

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 저장 프로시저를 배우는 모든 사람에게 도움이 되기를 바라며 지원해 주셔서 감사합니다.