MySQL Delete 문 성능 - IN절에서 서브쿼리 사용시 - Multiple Delete / CTAS

Last Updated on 1월 14, 2021 by 태랑(정현호)

1. MySQL 에서의 서브쿼리



MySQL 은 전통적으로 서브쿼리 최적화에 약한 면모를 보이고 있습니다 5.6 버전에서 개선되어( subquery materialization) 서브쿼리가 내부적으로 조인 형태로 변경되어 성능적 개선이 되었으나 상황에 따라서 서브쿼리는 DEPENDENT SUBQUERY 타입으로 실행되는 경우가 많습니다

그렇기 때문에 가급적 서브쿼리나 인라인 뷰에서 조인으로 변경하는 것이 MySQL은 아직까지는 SQL Plan 상 더 좋은 결과를 가져올수 있는 경우가 많습니다.



2.  문제의 Delete 문



Delete 문을 하나 처리해야 하는 상황이 발생하였습니다 쿼리는 아래와 같은 쿼리 입니다.
* 쿼리는 일부 수정 되어 있는 상태 입니다


2-1 수행 해야하는 Delete 쿼리

DELETE
FROM tb_test3
WHERE col1_seq in
    (SELECT DISTINCT col1_seq
     FROM tb_test1
     WHERE col1_seq NOT IN
         (SELECT DISTINCT tb_test1.col1_seq
          FROM tb_test1
          JOIN tb_test2 ON tb_test1.col1_seq = tb_test2.col1_seq));



2-2 삭제 대상 카운트

실행 전 요청사항과 맞는 건수가 삭제가 되는지 등을 확인 하기 위해서 조회를 실시하였으며, 대략 20초 가량 수행 되었고 삭제 대상 건수는 127건 으로 확인 되었습니다.
* 건수 및 시간 등은 테스트 VM 과 테스트 데이터를 통해 수행된 테스트 환경의 결과 입니다(실제 와 다름)

SELECT count(*)
FROM tb_test3
WHERE col1_seq in
    (SELECT DISTINCT col1_seq
     FROM tb_test1
     WHERE col1_seq NOT IN
         (SELECT DISTINCT tb_test1.col1_seq
          FROM tb_test1
          JOIN tb_test2 ON tb_test1.col1_seq = tb_test2.col1_seq) );

+----------+
| count(*) |
+----------+
|      127 |
+----------+
1 row in set (19.34 sec)



2-3 Delete 문 실행

start transaction;
root@localhost 14:33:07> DELETE
FROM tb_test3
WHERE col1_seq in
    (SELECT DISTINCT col1_seq
     FROM tb_test1
     WHERE col1_seq NOT IN
         (SELECT DISTINCT tb_test1.col1_seq
          FROM tb_test1
          JOIN tb_test2 ON tb_test1.col1_seq = tb_test2.col1_seq));

^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
root@localhost 15:21:36>

약 50분 가량 기다렸으나 Delete 문이 끝나지 않아 결국 쿼리 수행을 취소 하게 됩니다.



3. 테이블 건수 와 Plan



3-1 건수 및 사이즈

3개의 테이블의 건수 와 사이즈는 아래와 같습니다.

+------------+------------+---------------+----------------+
| table_name | table_rows | DATA_SIZE(MB) | INDEX_SIZE(MB) |
+------------+------------+---------------+----------------+
| tb_test2   |    3400902 |        273.84 |           0.00 |
| tb_test1   |    3567339 |        186.73 |         444.83 |
| tb_test3   |       3600 |          0.23 |           0.56 |
+------------+------------+---------------+----------------+

삭제 대상 테이블의 총 건수는 3600건이며 삭제 처리 건수는 127건으로 건수가 많지 않은 편이며  Select 조회시 빠르지는 않지만 조회에 20여초가 소요되어 대략 20여초 전후 로 처리를 예상하였으나 Delete 문의 처리가 되지 않고 있는 현상 입니다.


3-2 Delete SQL Plan

+--+-------------+---------+---------------+--------+--------+-------------------------+
|d | select_type | table   | type          | key    | rows   | Extra                   |
+--+-------------+---------+---------------+--------+--------+-------------------------+
|1 | DELETE      |tb_test3 | ALL           | NULL   |    3600| Using where             |
|2 | DEP SUBQUERY|tb_test1 | uniq_subquery | PRIMARY|       1| Using where;Using index |
|3 | DEP SUBQUERY|tb_test1 | eq_ref        | PRIMARY|       1| Using index             |
|3 | DEP SUBQUERY|tb_test2 | ALL           | NULL   | 3400902| Using where;            |
|                                                            | Using join buffer       | 
|                                                            |  (Block Nested Loop)    |
+--+-------------+---------+---------------+--------+--------+-------------------------+

[참고] 가로가 길어서 Plan은 편집이 되어 있는 상태 이며, DEPENDENT SUBQUERY 는 DEP SUBQUERY 로 치환하였습니다.


SQL 플랜은 Delete 의 IN - 서브쿼리에 있는 쿼리블럭이 모두 DEPENDENT SUBQUERY 타입 으로 수행되고 있으며 그에 따라 당연히 처리 결과가 나오지 않게 되는것 입니다.

DEPENDENT SUBQUERY는 즉 종속적 또는 의존적 서브쿼리로 상위 테이블에 종속적/의존적으로 처리됨을 의미하며, nested loop join 처럼 매 레코드 마다 서브쿼리와 결과를 비교 하게 됩니다.

오라클 처럼 서브쿼리의 결과가 먼저 수행되고, 그 결과 값이 상수로 상위 테이블에 제공되는 형태와는 다르게 수행이 됩니다 아래는 오라클에서 동일한 테이블(구조도) 로 동일한 쿼리를 수행 하였을 때의 플랜과 결과로 약 1.8초만에 완료 되었습니다.

-------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT         |             |  3473 |   868K|       | 20946   (1)|
|   1 |  DELETE                  | TB_TEST3    |       |       |       |            |
|*  2 |   HASH JOIN ANTI         |             |  3473 |   868K|       | 20946   (1)|
|*  3 |    HASH JOIN             |             |  3473 |   824K|       |  2240   (1)|
|   4 |     TABLE ACCESS FULL    | TB_TEST3    |  3473 |   780K|       |     7   (0)|
|   5 |     INDEX FAST FULL SCAN | TB_TEST1_PK |  3795K|    47M|       |  2223   (1)|
|   6 |    VIEW                  | VW_NSO_1    |  3795K|    47M|       | 18697   (1)|
|*  7 |     HASH JOIN            |             |  3795K|    94M|    90M| 18697   (1)|
|   8 |      INDEX FAST FULL SCAN| TB_TEST1_PK |  3795K|    47M|       |  2223   (1)|
|   9 |      TABLE ACCESS FULL   | TB_TEST2    |  3838K|    47M|       |  7419   (1)|
-------------------------------------------------------------------------------------

127 rows deleted.

Elapsed: 00:00:01.80



5. Action Plan



이와 같은 경우 MySQL에서  할 수 있는 몇가지 중 2가지를 기재 하겠습니다.


5-1 별도의 테이블을 사용하여 Delete

CTAS 를 통해 Select 절의 결과 집합을 생성, 그리고 상위 테이블과 조인 되는 컬럼에 Index 를 생성 함으로써 조회 성능을 개선하여 처리 하는 방법입니다

CREATE TABLE tb_del_source 
AS
SELECT DISTINCT col1_seq
FROM tb_test1
WHERE col1_seq NOT IN
    (SELECT DISTINCT tb_test1.col1_seq
     FROM tb_test1
     JOIN tb_test2 ON tb_test1.col1_seq = tb_test2.col1_seq);
Query OK, 120001 rows affected (25.36 sec)
Records: 120001  Duplicates: 0  Warnings: 0

alter table tb_del_src add index tb_del_source_idx ( col1_seq);

테이블 생성에 약 25초 가량 소요 되었습니다.



5-1-1 Delete 문 수행

DELETE
FROM tb_test3
WHERE col1_seq in
    (SELECT *
     FROM tb_del_source);



5-1-2 인덱스가 없을 경우

인덱스를 생성하지 않고 CTAS 로 생성한 테이블을 이용하여 Delete 를 하게 되면 3분 8초 가량 수행됩니다 이전의 Delete 문에 비해서는 빠르지만 여전히 개선 요건이 있습니다.

임시 복제 테이블 - Index 없을 경우 Plan 과 수행속도

Query OK, 127 rows affected (3 min 8.73 sec)
+----+--------------+---------------+------+------+--------+----------+-------------+
| id | select_type  | table         | type | key  | rows   | filtered | Extra       |
+----+--------------+---------------+------+------+--------+----------+-------------+
|  1 | DELETE       | tb_test3      | ALL  | NULL |   3600 |   100.00 | Using where |
|  2 | DEP SUBQUERY | tb_del_source | ALL  | NULL | 120019 |    10.00 | Using where |
+----+--------------+---------------+------+------+--------+----------+-------------+



5-1-3 인덱스 생성 시 

인덱스 생성 시 수행 속도 및 Plan

Query OK, 127 rows affected, 1 warning (0.01 sec)

+---+----------- --+------------+----------------+--------+------+----------+-------------+
|id | select_type  | table      | type           | key    | rows | filtered | Extra       |
+---+--------------+------------+----------------+--------+------+----------+-------------+
| 1 | DELETE       | tb_test3   | ALL            | NULL   | 3600 |   100.00 | Using where |
| 2 | DEP SUBQUERY | tb_del_src | index_subquery | src_idx|    1 |   100.00 | Using index |
+---+--------------+------------+----------------+--------+------+----------+-------------+



5-2 Multiple Delete (Join Delete)

또 다른 방법은 Join 형태의 Multiple Delete 를 사용하는 것 이며, 아래와 같이 조인 형태로 변경하여 수행 할 수 있습니다.

DELETE tb_test3
FROM tb_test3, (SELECT DISTINCT col1_seq
   FROM tb_test1
   WHERE col1_seq NOT IN
       (SELECT DISTINCT tb_test1.col1_seq
        FROM tb_test1
        JOIN tb_test2 ON tb_test1.col1_seq = tb_test2.col1_seq) ) b
WHERE tb_test3.col1_seq =b.col1_seq;

Query OK, 127 rows affected (39.07 sec)

위에서 임시 테이블(CTAS) 로 생성하는데 25초 가량 소요 되었던 것을 감안하면 대략 10초 ~15초 정도 차이가 정도 나게 되는데 이는 시스템 상황에 따라 차이가 덜 날수 도 있으며 플랜은 아래와 같습니다.

+---+---------+-----------+-------+--------------+---------+---------+-----------------------+
|id | sel_type| table     | type  | key          | rows    |filtered | Extra                 |
+---+---------+-----------+-------+--------------+---------+---------+-----------------------+
| 1 | DELETE  | tb_test3  | ALL   | NULL         |    3346 |  100.00 | Using where           |
| 1 | PRIMARY | <derived2>| ref   | <auto_key0>  |     974 |  100.00 | Using index           |
| 2 | DERIVED | tb_test1  | index | tb_test1_idx1| 3259500 |  100.00 | Using where;          |
|                                                                    | Using index; Distinct |
| 3 | SUBQUERY| tb_test2  | ALL   | NULL         | 3400902 |  100.00 | Using where           |
| 3 | SUBQUERY| tb_test1  | eq_ref| PRIMARY      |       1 |  100.00 | Using index           |
+---+---------+-----------+--------+-------------+---------+---------+-----------------------+



6. conclusion



조회 대상이 크지 않고 삭제나 변경의 건수가 크지 않다면 보통의 경우 IN-서브쿼리 형태로도 큰 문제없이 진행될 것 입니다 다만 이번과 같이 조회 대상에 사용할 적절한 인덱스가 없다거나 조회 건수가 많거나 하는 등의 조회 대상의 범위가 크다면 Update/Delete 구문에서의 IN-서브쿼리를 가능한 범위내에서 조인 형태로의 변경하는 것을 고려 해볼수 있을 것 같습니다.



관련된 다른 글

 

 

 

 

 

 

 

답글 남기기