MySQL 튜닝 : 대량 데이터 수정/삽입 시 인덱스 제거

인덱스가 있는 테이블에 대량의 데이터를 수정하거나 삽입하게 되면 인덱스도 모두 수정 및 생성하게 되어 속도가 느려집니다. 

 

다음은 인덱스가 있는 테이블에 2만건의 샘플 데이터를 넣는 쿼리문입니다.

 

 

DROP TABLE IF EXISTS t;
-- 테이블 생성
CREATE TABLE T (
	ID INT PRIMARY KEY,
	A INT,
	B INT,	
	C INT,
	TYPE CHAR(2)
);
-- 인덱스 생성
CREATE INDEX IDX_1 ON T(TYPE, B,A);

DROP PROCEDURE IF EXISTS INSERTDATA;

-- 프로시저 생성 
DELIMITER $$

CREATE PROCEDURE INSERTDATA()
BEGIN
	DECLARE id INT;
    DECLARE type CHAR;
	SET id = 1;
    SET type = 'A';
	WHILE id < 20000 DO 
		INSERT T VALUES( id, id/10, id/100, id/1000, type);
		SET id = id + 1;
        CASE type WHEN 'A' THEN SET type = 'B';
        ELSE SET type = 'A' ;
        END CASE;
	END WHILE;
END$$ 
DELIMITER ;

-- 프로시저 실행
CALL INSERTDATA();

총 2개의 인덱스가 생성되었습니다.

UPDATE T SET TYPE = 'C' WHERE ID > 5000;

14999 row(s) affected Rows matched: 14999  Changed: 14999  Warnings: 00.641 sec

 

UPDATE 하면서 TYPE이 구성 칼럼으로 있는 IDX_1로 함께 수정하기 때문에 시간이 오래 걸립니다.

다음은 인덱스 IDX_1을 제거후 같은 양의 데이터를 UPDATE하는 쿼리문입니다. 

ALTER TABLE T DROP INDEX IDX_1;

UPDATE T SET TYPE = 'F' WHERE ID < 15000;

14999 row(s) affected Rows matched: 14999  Changed: 14999  Warnings: 00.172 sec

수행시간이 0.172초로 줄어든 것을 확인할 수 있습니다.

그리고 IDX_1을 다시 생성해줍니다.

CREATE INDEX IDX_1 ON T2(TYPE, B,A)
0 row(s) affected Records: 0  Duplicates: 0  Warnings: 00.140 sec

인덱스를 재생성 해준 시간을 더해도 인덱스가 있는 상태에서 UPDATE한 것보다 시간이 적게 걸림을 확인할 수 있습니다.