MySQL Procedure를 통한 대용량 데이터 핸들링

Procedure 를 통해 방대한 양의 데이터를 핸들링했던 과정에 대해 정리하고자 합니다.

Stored Procedure

Stored Procedure 란?

Stored(저장) + Procedure(절차) = 일련의 절차를 정리해서 저장한 것

일련의 SQL문장을 선언해서 MySQL에 저장하고, 해당 SQL문을 함수처럼 사용하는 것이다.
사전에 준비 한 많은 명령을 함수처럼 호출하여 자동으로 실행할 수 있기 때문에, 작업의 효율성을 높일 수 있다.

단, 제대로 검증되지 않은 저장 프로시저를 실행하는 것은 매우 위험하므로 주의해야한다.

프로시저를 사용해야 하는 이유

• 하나의 요청으로 여러 SQL문 실행이 가능하다. (일괄 작업에 유용)
• 네트워크 소요 시간을 줄일 수 있다. (여러 개의 쿼리를 처리하는 시점에서 네트워크 부하 줄임)
• 보수성이 뛰어나다.
• 쿼리 문을 보호할 수 있다.
• 개발 업무를 구분하여 개발할 수 있다. (DB관련 처리를 API처럼 만들어 제공)

다만, 처리성능과 유지보수, 재사용면에서 좋지 않다는 점은 유의해야한다.

프로젝트 적용 Review

서비스의 데이터들을 추가하는 과정에서 방대한 양의 데이터를 어떻게 핸들링 할 수 있을지 고민하게 되었다.

이때 방대한 양의 데이터를 일련의 일정한 규칙을 따라 insert 하기 위해서,
MySQL에도 for, while 문 등 여러가지 연산을 가능하게 해주는 문법이 있다는 것을 알게 되어 프로시저를 사용하게 되었다.

그리고 프로시저를 사용하여 데이터를 직접 하나하나 insert하지 않고도 (발생할 수 있는 휴먼에러들을 방지하며) 안전하고 편리하게 핸들링할 수 있었다.

코드

(1) 작성한 프로시저 코드

delimiter $$
drop procedure if exists p1;
create procedure p1(title VARCHAR(100), ageGrade INT, photoUrl TEXT, year INT,
                    season INT,  resolution VARCHAR(30), summary TEXT)

begin

DECLARE CstrIDs varchar(150) DEFAULT '논쟁의 중심';
DECLARE GstrIDs varchar(150) DEFAULT '다큐시리즈';
DECLARE AstrIDs varchar(500) DEFAULT ''; 
DECLARE DstrIDs varchar(150) DEFAULT '마크 루이스'; 
DECLARE element varchar(1000);

-- 중복 데이터 무시하고 삽입하기
insert ignore into Video (title, ageGrade, photoUrl, year, season, resolution, summary, previewVideoUrl)
                   values (title, ageGrade, photoUrl, year, season, resolution, summary, photoUrl);

set @V := (select max(videoIdx) from Video);
select @V;

-- While 문1
 WHILE CstrIDs != '' DO
    SET element = SUBSTRING_INDEX(CstrIDs, ',', 1);      
    insert ignore into `Character`(name) values(element);

	set @C := (select characterIdx from `Character` where name = element);
    insert ignore into CharacterContact (characterIdx, videoIdx) values (@C, @V);
    select @C;

    IF LOCATE(',', CstrIDs) > 0 THEN
      SET CstrIDs = SUBSTRING(CstrIDs, LOCATE(',', CstrIDs) + 1);
      
    ELSE
      SET CstrIDs = '';
    END IF;
 END WHILE;

-- While 문2
 WHILE GstrIds != '' DO
    SET element = SUBSTRING_INDEX(GstrIds, ',', 1);      

	set @G := (select genreIdx from Genre where name = element);
    insert ignore into GenreContact (genreIdx, videoIdx) values (@G, @V);
    select @G;

    IF LOCATE(',', GstrIds) > 0 THEN
      SET GstrIds = SUBSTRING(GstrIds, LOCATE(',', GstrIds) + 1);
      
    ELSE
      SET GstrIds = '';
    END IF;
 END WHILE;

-- While 문3
 WHILE AstrIds != '' DO
    SET element = SUBSTRING_INDEX(AstrIds, ',', 1);      
    insert ignore into Actor(name) values(element);

	set @A := (select actorIdx from Actor where name = element);
    insert ignore into ActorParticipate (actorIdx, videoIdx) values (@A, @V);
    select @A;

    IF LOCATE(',', AstrIds) > 0 THEN
      SET AstrIds = SUBSTRING(AstrIds, LOCATE(',', AstrIds) + 1);
      
    ELSE
      SET AstrIds = '';
    END IF;
 END WHILE;

-- While 문4
  WHILE DstrIDs != '' DO
    SET element = SUBSTRING_INDEX(DstrIDs, ',', 1);      
	insert ignore into Director(name) values(element);

	set @D := (select directorIdx from Director where name = element);
    insert ignore into DirectorParticipate (directorIdx, videoIdx) values (@D, @V);
    select @D;

    IF LOCATE(',', DstrIDs) > 0 THEN
      SET DstrIDs = SUBSTRING(DstrIDs, LOCATE(',', DstrIDs) + 1);
      
    ELSE
      SET DstrIDs = '';
    END IF;
  END WHILE;

end $$
delimiter ;

(2) 프로시저 호출 코드

call p1('디어 마이 프렌즈', 15, 'https://~', 2016, 1, 'HD', "노년에 접어들었지만 인생, 아직 저물지 않았다.");

[참고]
https://velog.io/@donghoim/MySQL-%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80-Stored-Procedure
https://heestory217.tistory.com/18
https://epthffh.tistory.com/entry/Mysql-Procedure%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80-%EA%B0%9C%EB%85%90%EA%B3%BC-%EC%98%88%EC%A0%9C