INSERT INTO ... SELECT ... FROM 형태의 쿼리 사용시 주의 사항

INSERT INTO target_table

SELECT ... FROM source_table1, source_table2 WHERE ...

이 형태의 쿼리는 간단한 통계나 집계를 생성할 때 자주 사용된다.

일반적인 MySQL InnoDB 테이블에 대한 SELECT 쿼리는 Lock을 걸지 않으며, 

LOCK IN SHARE MODE 또는 FOR UPDATE 가 사용된 SELECT 문장에 대해서만 Lock이 필요하다.

하지만, 이 쿼리를 실행하게 되면 source_table1과 source_table2의 조회 대상 레코드에도 Read Lock이 걸리게 된다.

만약, 하위 SELECT 쿼리의 조회 범위나 처리 작업이 복잡하다면, 실시간 서비스에 영향을 미칠 수 있게 된다.

또한, 이 쿼리는 Lock 모드에서 SELECT를 실행하기 때문에 부분적으로 MVCC를 무시하고 최종으로 Commit된 

레코드를 읽게 되기 때문에 REPATABLE_READ 모드에서 실행되어도 실제적으로는 READ_COMMITED 모드로 작동하게 된다.

이러한 문제점은 MySQL의 GapLock과 연관이 있으며, 아래와 같이 2가지 방법으로 이러한 조회 테이블의 Lock을 피할 수 있다.

• innodb_locks_unsafe_for_binlog 시스템 변수 값을 ON으로 설정
• ROW-based replication과 READ-COMMITTED Isolation level 사용

하지만, 두 가지 대안 모두 현재로써는 좋은 해결책이 아닌 것으로 보인다.

첫 번째는 Master와 Slave의 데이터 부정합을 유발하게 될 것이며, 두 번째 Row-based replication은 아직 적용하기에는 시기 상조인듯하다.

(참고로 두 번째 방법은 이 쿼리를 위한 전용 옵션이 아니라, MySQL에서 GapLock을 제거하는 방법이기도 하다.)

그래서 이러한 부분을 해결하기 위한 방법으로는 아래와 같이 SELECT INTO OUTFILE과 LOAD DATA INFILE을 

혼용해서 사용하는 방법을 사용해보는 것이 좋을 듯 하다.

아래 예제에서는 article이라는 테이블을 내용을 group1_id, group2_id 컬럼을 이용하여 group by 한 결과를

다른 집계 테이블에 넣어 두고, 필요시 간단히 조회해서 사용할 수 있도록 하는 시나리오를 가정한 것이다.

아래의 쿼리는 article 테이블에 read lock을 사용하게 되므로, 실시간 변경 트랜잭션에 영향을 미치게 된다.

insert into temp$summary (summary_id, group1_id, group2_id, ...)

select null, group1_id, group2_id, ...

from article al

group by al.group1_id, al.group2_id

order by NULL;

그래서, 아래와 같이 SELECT -> Disk 파일 -> LOAD INTO -> RENAME TABLE과 같은 방법으로 해결할 수 있다.

-- // 임시 작업용 테이블 생성

create temp$summary(

  summary_id integer unsigned not null auto_increment,

  group1_id bigint not null,

  group2_id bigint not null,

  article_count integer default 0 not null,

  ...

  primary key(summary_id)

);

-- // Grouping 결과를 임시 파일로 저장

SELECT al.group1_id, al.group2_id, count(*) as article_count, ...

INTO OUTFILE '/tmp/temp_aritcle_summary.dat'

FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'

from article al

group by al.group1_id, al.group2_id

order by NULL;

-- // 저장된 임시 파일을 테이블로 적재

LOAD DATA INFILE '/tmp/temp_aritcle_summary.dat'

INTO TABLE temp$summary

FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'

  (group1_id, group2_id, article_count, ...)

SET summary_id=null;

-- // 데이터 적재후 추가적으로 필요한 인덱스 생성

alter table temp$summary add index ix_group1id_group2id (group1_id, group2_id);

-- // 준비된 임시 테이블을 서비스용 테이블로 이름 변경

-- // 아래와 같이 한 명령으로 필요한 테이블의 이름 변경을 한꺼번에 실행하게 되면, 

-- // 실시간 서비스라 하더라도 순단 현상(일시적으로 TABLE NOT FOUND)을 피할 수 있다.

rename table summary_yesterday to summary_old,

             summary to summary_yesterday,

             temp$summary to summary;

-- // 이틀 전 데이터를 가지고 있는 테이블은 삭제

drop table summary_old;