[DB] db에 데이터넣기, 인덱스

*  db에 데이터 넣기

• 초안
• 문제 : connection timeout error

-- User 테이블 생성
CREATE TABLE IF NOT EXISTS `User` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nickname` VARCHAR(30) NOT NULL,
  `rank` INT NOT NULL CHECK (`rank` BETWEEN 1 AND 10),
  `money` BIGINT NOT NULL CHECK (`money` BETWEEN 1 AND 99999999),
  `start_date` DATE NOT NULL,
  `last_login` DATETIME
);

-- 인덱스 생성
CREATE INDEX idx_user_rank ON `User`(`rank`);
CREATE INDEX idx_user_money ON `User`(`money`);
CREATE INDEX idx_user_start_date ON `User`(`start_date`);
CREATE INDEX idx_user_nickname ON `User`(`nickname`);

DELIMITER //
CREATE FUNCTION random_korean_name() RETURNS VARCHAR(30)
NOT DETERMINISTIC
NO SQL
BEGIN
  DECLARE surnames VARCHAR(100) DEFAULT '김,이,박,최,정,강,조,윤,장,임';
  DECLARE names VARCHAR(200) DEFAULT '준,민,서,예,지,현,우,아,은,진,선,영,주,혜,성,민,정,수,윤,태';
  DECLARE surname VARCHAR(10);
  DECLARE name1 VARCHAR(10);
  DECLARE name2 VARCHAR(10);
  
  SET surname = SUBSTRING_INDEX(SUBSTRING_INDEX(surnames, ',', FLOOR(1 + RAND() * 10)), ',', -1);
  SET name1 = SUBSTRING_INDEX(SUBSTRING_INDEX(names, ',', FLOOR(1 + RAND() * 20)), ',', -1);
  SET name2 = SUBSTRING_INDEX(SUBSTRING_INDEX(names, ',', FLOOR(1 + RAND() * 20)), ',', -1);
  
  RETURN CONCAT(surname, name1, name2);
END //
DELIMITER ;

-- 랜덤 데이터 생성 및 삽입을 위한 프로시저
DELIMITER //
CREATE PROCEDURE generate_user_data(IN num_records INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  
  WHILE i < num_records DO
    INSERT INTO `User` (nickname, `rank`, money, start_date, last_login)
    VALUES (
      random_korean_name(),
      FLOOR(1 + RAND() * 10),
      FLOOR(1 + RAND() * 99999999),
      DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 365 * 3) DAY),
      IF(RAND() < 0.8, 
         DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 180) DAY), 
         NULL)
    );
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

-- 데이터 생성 실행
CALL generate_user_data(3000);

-- 통계 업데이트
ANALYZE TABLE `User`;

6000초로 임시 설정.

• 개선

-- 1. 임시 테이블 생성
CREATE TABLE IF NOT EXISTS `User_Temp` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nickname` VARCHAR(30) NOT NULL,
  `rank` INT NOT NULL,
  `money` BIGINT NOT NULL,
  `start_date` DATE NOT NULL,
  `last_login` DATETIME
) ENGINE=InnoDB;

-- 2. 랜덤 데이터 생성 및 삽입을 위한 프로시저
DELIMITER //
CREATE PROCEDURE generate_user_data_optimized(IN num_records INT, IN batch_size INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  
  -- 트랜잭션 시작
  START TRANSACTION;
  
  WHILE i < num_records DO
    INSERT INTO `User_Temp` (nickname, `rank`, money, start_date, last_login)
    SELECT
      random_korean_name(),
      FLOOR(1 + RAND() * 10),
      FLOOR(1 + RAND() * 99999999),
      DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 365 * 3) DAY),
      IF(RAND() < 0.8, 
         DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 180) DAY), 
         NULL)
    FROM (SELECT @rn:=@rn+1 AS rn FROM (SELECT @rn:=0) AS r, 
          information_schema.columns LIMIT batch_size) AS t
    WHERE t.rn <= batch_size;
    
    SET i = i + batch_size;
    
    -- 일정 간격으로 커밋
    IF i % (batch_sizeid * 10) = 0 THEN
      COMMIT;
      START TRANSACTION;
    END IF;
  END WHILE;
  
  -- 남은 데이터 커밋
  COMMIT;
END //
DELIMITER ;

-- 3. 데이터 생성 실행 (배치 크기: 10000)
CALL generate_user_data_optimized(3000000, 5000);

-- 4. 임시 테이블에서 실제 테이블로 데이터 이동
INSERT INTO `User` SELECT * FROM `User_Temp`;

-- 5. 임시 테이블 삭제
DROP TABLE `User_Temp`;

-- 6. 통계 업데이트
ANALYZE TABLE `User`;

1200만??

• 마스터클래스 내용
  • 프로시저보다는 파이썬이나 js를 이용하는게 좋음
  • 이유 : 프로시저는 컴파일후 실행이 아니므로 느림
  •  

인덱스가 없는행은 완전탐색

인덱스가 걸린행은 빠르다

• UNIQUE 제약조건 이 걸린 행 -> 자동 indexing된다.
• bulk insert등을 실행하면 빨리 넣을수있다. (insert문 모아서 보내기)