사용계기
프로젝트를 완성한 후 대량의 데이터를 집어넣으면 속도의 차이가 얼마나 나는지 그에 따른 최적화를 진행하기 위해서 대량의 더미 데이터를 집어넣기 위해서 알아보게 되었다.
자바에서 while문을 사용하듯이 mysql에서도 while문을 사용할 수 있다는것을 알게되었는데 바로 프로시저를 이용하여 여러 쿼리를 한번에 실행하는 것이 가능하다.
데이터베이스의 함수와 프로시저의 차이점
함수(Function) : 클라이언트에서 처리, 리턴값 필수, 리턴값 하나만 반환가능
프로시저(Procedure) : 서버로 보내서 처리, 리턴값 선택, 리턴값 여러개 반환가능
SQL 처리를 하는 위치에서의 차이점인데 속도면에서는 프로시저가 더 빠른 성능을 보인다고 한다.
때문에 각각의 용도는 프로시저 같은 경우 실행, 처리를 할 때 주로 사용되고, 함수는 간단한 계산이나 수치 결과를 나타낼 때 주로 사용한다.
용도에 맞는 것을 적절히 사용해야 한다.
사용 방법
CREATE PROCEDURE 200000loopInsert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 200000
DO
INSERT INTO 테이블 명(컬럼 명, 컬럼 명)
VALUES (값1, 값1);
SET i = i + 1;
END WHILE;
END;
CALL 200000loopInsert();
프로시저는 CREATE PROCEDURE 사용할 프로시저 명 (파라미터 ) 메서드로 생성할 수 있다.
그뒤 BEGIN과 END로 그사이 실행될 쿼리문을 작성해주면된다.
나는 20만건의 더미 데이터를 집어넣기 위해 while문을 사용해 동일한 더미 데이터를 생성하여 저장해주었다. (아주 간단한 쿼리문)
CALL 프로시저 명 (파라미터)는 무엇인가요?
중요한것은 프로시저메서드만 정의하면 되는것이 아니라 프로시저를 CALL 프로시저명(파라미터)를 실행해주어야지 우리가 작성한 프로시저가 작동한다는 것이다.
실제 내가 작성한 쿼리
// aritcle 생성
CREATE PROCEDURE 200000_article_loop_insert()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i <= 200000
DO
INSERT INTO article(created_at,last_modified_at,article_status,clicks,content,created_date,is_closed,title,category_id,user_id)
VALUES ('2023-01-06 12:40:38.396000','2023-01-06 12:40:38.396000','POSTING',0,'테스트 입니다.','2023-01-06 12:40:38.396000',false,'대량의 더미 데이터를 삽입중입니다.',1,1);
SET i = i + 1;
END WHILE;
END;
// article과 연관관계가 있는 랜덤 tag 작성
CREATE PROCEDURE 200000_article_tag_insert()
BEGIN
DECLARE i INT DEFAULT 5;
DECLARE j INT DEFAULT 1;
WHILE i <= 200206
DO
INSERT INTO article_tag(article_id, tag_id)
VALUES (i, j);
SET i = i + 1;
SET j = j + 1;
IF j >= 23 THEN
SET j = 1;
END IF;
END WHILE;
END;
// 프로시져 실행
CALL 200000_article_loop_insert();
CALL 200000_article_tag_insert();
// 생성되어있는 프로시져 확인 용
SHOW PROCEDURE STATUS;
// 프로시져 수정시 삭제 후 다시 생성
DROP PROCEDURE 200000_article_loop_insert;
DROP PROCEDURE 200000_article_tag_insert;
추가적인 설명
프로시저는 생성 수정 삭제가 가능하지만, 수정은 ALTER 구문을 사용한다. ALTER 명령은 스토어드 프로시저에서 제공하는 보안 및 작동 방식과 관련된 특성을 수정할 때만 사용가능하다. 내부 로직을 변경하는것은 불가능한것같다. 그래서 이미 존재하는 프로시저를 수정하기 위해서는 삭제 후 다시 생성하는 방식을 이용하고 있다.
//프로시저 목록 확인
SHOW PROCEDURE STATUS;//프로시저 내용 확인
SHOW CREATE PROCEDURE 프로시저이름;//프로시저 삭제
DROP PROCEDURE 프로시저이름;참고 사이트
프로시저 관련 사이트
데이터베이스 조회 명령어 정리 사이트
