Mysql 대량 INSERT의 속도 개선을 위한 다양한 방법

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);

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;

* 그외 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;