[MySQL] 대량 데이터 INSERT 시 속도 개선 방법

MySQL 로그 이미지

 

 

1. LOAD DATA 

- Bulk Insert방법중 최대 속도인 처리방법
- 데이터를 파일로 로드해서 insert 하는 방법
- my.cnf에서 local-infile=1 설정이 되어 있어야 사용가능
- 단 데이터 내용안에 특수문자가 누락되는 경우가 많아서 특문을 별도 처리해야 함

LOAD DATA LOCAL INFILE '/work/data.txt' INTO TABLE TEST (c1,c2,c3,c4);

 

[그림-1] LOAD DATA LOCAL 예시

 

2. MULTI-VALUE  INSERTS

- 쿼리문 하나에 n개의 데이터를 입력해야함
- 단 데이터 갯수가 너무 많아져서 쿼리길이기 길어지는 경우를 대비하여  max-allowed-packet 길이를 조절해야 함.

INSERT INTO [Table Name] VALUES (1,...), (2,...), (3,...), (4,...) ...;

 

3. MULTI LINE INSERT

 - 기존 코드를 최소한으로 수정하고 처리가능
 - 여러개의 테이블에 INSERT 하는 경우에 잘 사용 가능함

$query .="INSERT INTO [Table Name1] VALUES (1,...);";
$query .="INSERT INTO [Table Name2] VALUES (T2,...);";

 

4. TRANSACTION 처리

 - 단지 BEGIN과 END로만 처리 가능
 - 트랜젝션중 너무 많은 쿼리가 들어갈 경우  TRANSACTION BUFFER가 커지면 속도가 저하됨

 

BEGIN;
INSERT INTO .....;
INSERT INTO .....;
....
END;
BEGIN;
INSERT INTO .....;
INSERT INTO .....;
....
END;

4. 그외 INSERT 속도 개선을 위한 설정값

1) INDEX 비활성화

- 대량의 INSERT 실행중 INDEXING 처리를 비활성화 하고 INSERT 작업 완료후 INDEXING 처리를 하면 데이터 1개씩 IDEXING 처리하는 비효율을 줄일 수 있다.

ALTER TABLE [Table Name1] DISABLE KEYS;
INSERT INTO [Table Name1] VALUES (1,...);
....
COMMIT;
ALTER TABLE [Table Name1] ENABLE KEYS;

 

2) AUTO-COMMIT 비활성화후 처리

3) 유니크 INDEX 비활성화후 처리

4) 참조키 비활성화 

SET autocommit = 0; //AUTO-COMMIT
SET unique_checks = 0; //유니크 INDEX
SET foregin_key_checks = 0;
INSERT INTO [Table Name1] VALUES (1,...);
....
COMMIT;
SET foregin_key_checks = 1;
SET unique_checks = 1;
SET autocommit = 1;