MySQL 더미데이터 삽입하기

쿼리 튜닝을 위한 첫 단계

쿼리 최적화를 진행하고자 한다면 가장 첫 번째로 데이터가 있어야 한다. 하지만 사이드프로젝트에서 몇 십만, 몇 백만 개의 데이터가 있을리가 만무하다.

조회 쿼리 성능개선을 위해 인덱스를 건다 하더라도 데이터 건수가 너무 작다면 인덱스를 걸어도 테이블 풀 스캔이 발생하며, 아무래도 데이터가 별로 없다보면 쿼리 최적화를 진행한다 하더라도 성능이 확연하게 드러났다라는 지표가 명확하게 보이지 않는다.

위의 이유로 어떻게 대용량 더미데이터를 넣을 지 고민하던 와중, 프로시저를 활용하기로 했다.

프로시저로 데이터를 삽입하기

다음은 현재 사이드 프로젝트의 ERD이다. 기능확장을 추가적으로 하지 않았기 때문에 도메인은 현재 user, board, comment, region 총 4개로 구성되어있다.

region 테이블에는 우리나라의 전체 행정구역을 계층 구조로 넣어놓은 상태다. 총 20084개의 지역데이터가 이미 region 테이블에 삽입되어있다. 그래서 남은 user와 board 더미데이터를 프로시저를 활용해 만들어보겠다.

60만개 User 더미데이터 생성

User와 Region은 1:N 관계다.

Region은 총 20084개가 있기때문에 region_id는 1부터 20084 사이 랜덤값을 줬다.

DELIMITER //
DROP PROCEDURE IF EXISTS generate_users_data//

CREATE PROCEDURE generate_users_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE randRegionID INT;
    DECLARE randDate DATETIME;

    WHILE i <= 600000 DO
        SET randRegionID = FLOOR(1 + (RAND() * 20084));
        SET randDate = NOW();

        INSERT INTO user (id, nickname, accountID, region_id, password, createAt, updateAt, deleteAt)
        VALUES (
            i,
            CONCAT('닉네임', i),
            CONCAT('abc', i),
            randRegionID,
            MD5(RAND()),
            randDate,
            randDate,
            NULL
        );

        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

프로시저를 실행해보니 총 600000건의 user데이터가 들어간 것을 확인해볼 수 있다.

70만개 Board 더미데이터 생성

Board와 User는 N:1 관계이며, Board와 Region은 N:1 관계다.
User가 생성한 Board에는 region_id도 들어가 있는데, User가 속해있는 Region과 Board의 region_id가 같아야 하므로, user_id의 region_id 가져와 region_id를 삽입했다.

User는 60만개가 있기때문에 user_id는 1부터 60만 사이 랜덤값을 줬다.

게시물의 Category와 게시물의 status는 enum타입이기 때문에 랜덤으로 값을 줬다.

board 제목(StuffName)과 내용(StuffContent) 같은 경우는 한글을 랜덤으로 돌려 생성하였다.

DELIMITER //

DROP PROCEDURE IF EXISTS generate_board_data//

CREATE PROCEDURE generate_board_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE randStuffName VARCHAR(10);
    DECLARE randStuffContent VARCHAR(20);
    DECLARE randStuffPrice INT;
    DECLARE tradingPlaces VARCHAR(255) DEFAULT 'Seoul';
    DECLARE randStatus ENUM('CLOSED', 'OPEN');
    DECLARE randCategory ENUM('패션의류/잡화', '뷰티', '출산/유아동', '식품', '주방용품', '생활용품', '홈인테리어', '가전디지털', '스포츠/레저', '자동차용품', '완구/취미', '문구/오피스', '반려동물용품', '헬스/건강식품');
    DECLARE randLikesCount INT;
    DECLARE imageUrl VARCHAR(255) DEFAULT 'https://s3.example.com/image.jpg';
    DECLARE randUserId INT;
    DECLARE randRegionId INT;
    DECLARE randCreateAt DATETIME;
    DECLARE randUpdateAt DATETIME;
    DECLARE randDeleteAt DATETIME;
    DECLARE useDeleteAt BOOLEAN;
    DECLARE koreanWords VARCHAR(255) DEFAULT '가나다라마바사아자차카타파하';

    WHILE i <= 700000 DO
        SET randUserId = FLOOR(1 + (RAND() * 600000)); 
        SELECT region_id INTO randRegionId FROM user WHERE id = randUserId LIMIT 1;

        SET randStuffName = SUBSTRING(koreanWords, FLOOR(1 + (RAND() * (CHAR_LENGTH(koreanWords) - 10))), FLOOR(5 + (RAND() * 6)));
        SET randStuffContent = SUBSTRING(koreanWords, FLOOR(1 + (RAND() * (CHAR_LENGTH(koreanWords) - 20))), FLOOR(10 + (RAND() * 11)));

        SET randStuffPrice = FLOOR(500 + (RAND() * 999500)); 
        SET randStatus = IF(RAND() < 0.5, 'CLOSED', 'OPEN');
        SET randCategory = ELT(FLOOR(1 + (RAND() * 14)), '패션의류/잡화', '뷰티', '출산/유아동', '식품', '주방용품', '생활용품', '홈인테리어', '가전디지털', '스포츠/레저', '자동차용품', '완구/취미', '문구/오피스', '반려동물용품', '헬스/건강식품');
        SET randLikesCount = FLOOR(RAND() * 10000);

        SET randCreateAt = ADDDATE('2022-12-31', FLOOR(RAND() * 426)); 
        SET randUpdateAt = IF(RAND() < 0.5, randCreateAt, ADDDATE(randCreateAt, 1));

        SET useDeleteAt = RAND() < 0.7; 
        IF useDeleteAt THEN
            SET randDeleteAt = ADDDATE(IF(RAND() < 0.5, randCreateAt, randUpdateAt), 2);
        ELSE
            SET randDeleteAt = NULL;
        END IF;

				INSERT INTO board (id, stuffName, stuffContent, stuffPrice, tradingPlace, stuffCategory, status, likesCount, imageUrl, user_id, region_id, createAt, updateAt, deleteAt)
				VALUES (
				    i,
				    randStuffName,
				    randStuffContent,
				    randStuffPrice,
				    tradingPlaces,
				    randCategory,
				    randStatus,
				    randLikesCount,
				    imageUrl,
				    randUserId,
				    randRegionId,
				    randCreateAt,
				    randUpdateAt,
				    randDeleteAt
				);
			

        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

마찬가지로 board 프로시저를 실행했고, 총 700000개의 데이터가 들어간 것을 확인할 수 있다.