MYSQL DELETE 시 속도 개선

일반적으로 DELETE 를 하는 경우에 속도문제가 발생하진 않습니다

하지만 문제가 생기는 경우가 있는데 DELETE 를 할 때 특정 테이블에 데이터를 SELECT 해와서 그걸 IN 구절안에 쓰게 될 경우에 발생합니다.

대부분 아래와 같은 쿼리를 한번 보시죠

우선 TB_MED_ALARM 과 TB_MED_ALARM_RST 테이블이 있습니다.

관계는 1 : N 의 관계를 형성하고 있죠.

TB_MED_ALARM 테이블은 데이터가 대략 4만

TB_MED_ALARM_RST 테이블은 데이터가 대략 100만건 정도가 있습니다.

해당 쿼리를 수행하게 되면 대략적으로 수행시간은 26초 정도 걸리게 됩니다.

DELETE
       FROM TB_MED_ALARM_RST
      WHERE ALARM_IDX IN (
       SELECT ALARM_IDX
         FROM TB_MED_ALARM
      WHERE USER_IDX = '190592'
        AND FAM_IDX = 34514
        AND MY_MED_YN = 'N'
      );

해당 쿼리를 EXPLAIN 모드로 해서 실행 계획을 보겠습니다.

id2번의 select_type을 보면 DEPENDENT SUBQUERY(의존적 서브쿼리)라고 되어 있는걸 볼 수 있습니다. 즉 상위 테이블 결과에 의존적인 상태이며 해당 쿼리는 100만건의 대한 레코드를 모두 가져와서 하나하나 비교해가는 방식이라 굉장히 느려진걸 볼 수있죠

단순하게만 생각하면 subquery가 먼저 수행되고 그 결과가 RST 테이블과 조인 될지 알았지만 그 반대로 작동되었습니다. 사실 mysql 에서 in 구절안에서 서브쿼리 사용을 권장하지 않습니다 성능 및 최적화 문제가 있기 때문입니다.

회피하는 방법으로는 IN 을 쓰지 않고 INNER JOIN , EXISTS 로 변환하는 방법이 있으나 오늘은 JOIN 을 통한

방법으로 쿼리를 개선해 보겠습니다

DELETE TB_MED_ALARM_RST FROM  TB_MED_ALARM_RST , 
(SELECT  DISTINCT(T1.ALARM_IDX)
        FROM TB_MED_ALARM T1
        JOIN TB_MED_ALARM_RST T2 ON T1.ALARM_IDX = T2.ALARM_IDX AND T1.USER_IDX = 190592) TMA
 WHERE TB_MED_ALARM_RST.ALARM_IDX = TMA.ALARM_IDX
 AND TB_MED_ALARM_RST.USER_IDX = 190592  

수행시간이 0.016초로 단축 되었습니다

그리고 이 쿼리에 대해 EXPLAIN 을 가지고 확인해 보겠습니다

보시면 DEPENT SUBQUERY는 없어지고 DERIVED라고 나오는걸 볼 수 있으며 해당 타입은 서브 쿼리로 인해 발생한

임시 테이블이라 그렇습니다. 보통 메모리에 저장될 경우 성능에 지장을 주진 않지만 디스크에 저장할 경우에는 성능

저하가 발생합니다. 

위 쿼리는 보시면 지워야 할 RST 테이블과  임시 테이블을 만들어서 INNER JOIN 을 수행하고 있습니다.

단 임시테이블의 데이터 수를 최대한 줄이기위해 ALARM_RST 와 ALARM의 INNER JOIN 한 결과값을 가지고 임시테이블 을 생성합니다

이렇게 지워야 할 RST테이블과의 INNER JOIN 을 통해 최대한 필터링된 결과값의 결과를 시행하여 데이터를 지우는 쿼리로 변경해 보았습니다.

도움이 되셨길 바랍니다.