[MySQL] Bulk Data Loading for InnoDB Tables

□ Auto Commit Mode

InnoDB로 데이터를 가져올 때 오토커밋 모드는 모든 삽입에 대해 디스크로 로그 플러시를 수행하므로 오프시킵니다. 가져 오기 작업 중에 오토 커밋을 비활성화하려면 SET autocommit 및 COMMIT 문으로 감쌉니다.

SET autocommit=0;... SQL import statements ...

COMMIT;

mysqldump 옵션 --opt는 SET autocommit 및 COMMIT 문으로 랩핑하지 않아도 InnoDB 테이블로 빠르게 가져 올수 있도록 덤프 파일을 작성합니다.

 

□ UNIQUE Key 제약해제

보조 키에 UNIQUE 제약 조건이 있는 경우, 가져 오기 세션 중에 고유성 검사를 일시적으로 해제하여 테이블 가져 오기 속도를 높일 수 있습니다.

SET unique_checks=0;... SQL import statements ...

SET unique_checks=1;

큰 테이블의 경우 InnoDB는 변경 버퍼를 사용하여 보조 인덱스 레코드를 일괄 적으로 쓸 수 있기 때문에 많은 디스크 I/O를 절약 할 수 있습니다. 데이터에 중복 키가 없는지 확인합니다.

 

□ FOREIGN KEY 일시적 비활성화

테이블에 FOREIGN KEY 제약 조건이 있는 경우 가져 오기 세션 동안 외래키 검사를 해제하여 테이블 가져 오기 속도를 높일 수 있습니다.

SET foreign_key_checks=0;... SQL import statements ...

SET foreign_key_checks=1;

For big tables, this can save a lot of disk I/O.

큰 테이블의 경우 많은 디스크 I/O를 절약할 수 있습니다.

 

□ 다중행 INSERT(Extended Insert) 사용

여러 행을 삽입해야 하는 경우 다중행 INSERT 구문을 사용하여 클라이언트와 서버간의 통신 오버 헤드를 줄입니다.

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

이 팁은 InnoDB 테이블뿐만 아니라 모든 테이블에 삽입하는 데 유효합니다.

 

□ innodb_autoinc_lock_mode 변경

자동 증가 컬럼이 있는 테이블에 대량 삽입을 수행할 때는 innodb_autoinc_lock_mode를 기본값 1대신  2로 설정합니다.

링크 : innodb_autoinc_lock_mode 환경

 

□ Primary Key 컬럼의 데이터 정렬

대량 삽입을 수행 할 때 PRIMARY KEY순서로 행을 삽입하는 것이 더 빠릅니다. InnoDB 테이블은 클러스터된 인덱스를 사용하므로 PRIMARY KEY 순서대로 데이터를 사용하는 것이 비교적 빠릅니다. PRIMARY KEY 순서로 대량 삽입을 수행하는 것은 버퍼 풀에 완전히 맞지 않는 테이블에 특히 중요합니다.

 

□ InnoDB FULLTEXT 인덱스에 대한 성능 향상법

InnoDB FULLTEXT 인덱스에 데이터를로드 할 때 최적의 성능을 얻으려면 다음 단계를 따르십시오.

▷ 테이블 작성시 BIGINT UNSIGNED NOT NULL 유형의 FTS_DOC_ID 열을 FTS_DOC_ID_INDEX라는 고유 인덱스로 정의합니다. 예를 들면 다음과 같습니다.

CREATE TABLE t1 (

FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,

title varchar(255) NOT NULL DEFAULT '',

text mediumtext NOT NULL,

PRIMARY KEY (`FTS_DOC_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);

 

노트

테이블 작성시 FTS_DOC_ID 컬럼을 추가 할 때 FULLTEXT 인덱스 컬럼이 업데이트될 때 FTS_DOC_ID 컬럼이 업데이트되는지 확인합니다. FTS_DOC_ID는 각 INSERT 또는 UPDATE마다 단순하게 증가해야합니다. 테이블 생성시 FTS_DOC_ID를 추가하지 않고 InnoDB가 DOC ID를 관리하게하는 경우 InnoDB는 다음 CREATE FULLTEXT INDEX 호출로 FTS_DOC_ID를 숨겨진 열로 추가합니다. 그러나이 방법에는 성능에 영향을주는 테이블을 다시 작성해야합니다.

 

▷ 데이터를 테이블에 로드합니다.

 

▷ 데이터가 로드된 후 FULLTEXT 인덱스를 만듭니다.