MySQL INSERT 성능 향상

(2009/08/15 08:07 작성)

출처: http://www.lovelgw.com/Blog/225

MySQL INSERT 성능 향상

MySQL 테이블에 INSERT 구문을 수행 시킬때 성능을 향상시킬 수 있는 방법입니다. 

여러 데이터를 INSERT 구문으로 수행시 VALUES 리스트를 다중으로 사용하는것이 개별적으로 INSERT것보다 빠릅니다. 비어 있지 않은 테이블에 INSERT를 수행한다면 my.ini 파일 안에 bulk_insert_buffer_size 를 변경하여 속도를 개선 시킬 수 있습니다. 

INSERT INTO `테이블` (필드1, 필드2) VALUES ('값1', '값2'),('값1', '값2')...('값1', '값2');

# My.ini 파일 내에 bulk insert buffer size를 변경하고 MySQL을 재시작 합니다.
bulk_insert_buffer_size = 64M 



여러 클라이언트에서 대량의 데이터를 삽입을 한다면 INSERT DELAYED 명령문을 사용하면 속도를 개선 할 수 있습니다. 서버로 부터 수행 응답을 받은 후 큐에 적재 후 테이블에 사용이 되지 않을때 테이블에 삽입이 됩니다.  INSERT DELAYED 는 MyISAM, MEMORY, ARCHIVE 테이블에서 사용 가능합니다. 그외 테이블에서는 성능 저하가 발생 할 수 있습니다. affected row 값은 테이블이 바쁘지 않을때는 정확한 값을 출력을 하나 그렇지 않을때는 1의 값을 반환을 합니다. DELAYED 이외에 LOW_PRIORITY, IGNORE, HIGI_PRIORITY 등을 이용하여 작업의 순위, 중복 오류등을 무시 할 수 있습니다. 

LOW_PRIORITY : 최 하위 순위로 INSERT구문을 수행합니다. 다른 클라이언트의 SELECT가
종료된 후 수행합니다.

IGNORE : 키 중복 (PRIMARY KEY, UNIQUE KEY)의 오류를 무시하고 진행합니다. 

HIGH_PRIORITY : 최 우선 작업으로 INSERT구문을 수행합니다.

INSERT DELAYED 구문을 수행할때에는 Slave replaction 와는 다른 데이터를 가질 수 있습니다.




INSERT DELAYED INTO `테이블` (필드1, 필드2) VALUES ('값1', '값2'),('값1', '값2')...('값1', '값2');

대량의 데이터를 삽입을 할때는 LOAD DATA INFILE 을 사용합니다. 

대량의 데이터를 INSERT 구문을 이용해서 삽입을 하는것 보다, 필드 구분자로 분리가된 LOAD DATA INFILE 을 이용하면 INSERT 구문보다 매우 빠른 동작을 합니다. 

Slave Replaction 에 동일한 구문이 전달이 가능하고 INSERT가 가능합니다. 




-- 필드 구분이 , (쉼표) 로 되어 있고 라인 구분이 개행으로 되어 있을때 아래와 같이 수행하면 됩니다.

LOAD DATA INFILE '파일경로' INTO TABLE `테이블명` FIELDS TERMINATED BY ','LINES TERMINATED By '\r'

INDEX가 많이 사용된 테이블에 대량으로 INSERT 구문 시 INDEX를 비활성화 한 후 수행을 하는것이 좋습니다. 한개의 열을 삽입하고 인덱스를 계산 후 다시 다른 행을 삽입하는 것 보다는 전체 키를 비 활성화 한 후에 데이터 입력이 종료되고 다시 키를 활성화 시키는것이 전체적으로 빠른 수행을 할 수 있습니다.




-- 테이블의 INDEX를 비활성화 시킵니다.

ALTER TABLE `테이블` DISABLE KEYS;

-- 대량 INSERT 구문을 수행합니다.

INSERT INTO `테이블` VALUES ('값1', '값2')....;

.....

INSERT INTO `테이블` VALUES ('값1', '값2')....;

-- 수행이 종료된 후 다시 INDEX를 활성화 시킵니다. 

ALTER TABLE `테이블` ENABLE KEYS;

트랜젝션이 지원하지 않는 테이블에 PROCEDURE , FUNCTION , 다중 쿼리문 에서 수행되는 INSERT연산의 속도를 향상 시키기 위해서는 테이블 잠금을 실행하여 속도를 향상 시킬 수 있습니다. 인덱스 버퍼 플러시를 매번 수행하지 않고 모든 INSERT 구문의 모든 열이 삽입 되었을때 인덱스 버퍼 플러시가 이루어져 성능향상이 됩니다. 




-- 테이블의 WRITE 잠금을 수행합니다.

LOCK TABLES `테이블` WRITE;

-- 대량 INSERT 구문을 수행합니다.

INSERT INTO `테이블` VALUES ('값1', '값2')....;

.....

INSERT INTO `테이블` VALUES ('값1', '값2')....;

-- 테이블의 WRITE 잠금을 해제 합니다.

UNLOCK TABLES `테이블` WRITE;

트랜젝션이 지원하는 테이블에서는 START TRANSACTION 과 COMMIT을 활용하면 성능이 향상이 됩니다. 

위와 같이 MySQL INSERT 수행시 성능을 향상시킬 수 있습니다. 
참고 : MySQL 메뉴얼 (Speed of INSERT Statements)