Last Updated on 5월 13, 2022 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 에서 Delete 시(Update도 유사) IN 절 안의 서브쿼리의 결과를 통해서 DELETE 를 하는 형태에 대해서 확인 해보려고 합니다.
1. MySQL 에서의 서브쿼리
MySQL 은 전통적으로 서브쿼리 최적화에 약한 면모를 보이고 있습니다 5.6 버전에서 개선되어( subquery materialization) 서브쿼리가 내부적으로 조인 형태로 변경되어 사용되거나 또는 SEMI JOIN 으로 Query Transformation 을 하여 성능적 개선이 될 수 있는 기능이 추가 및 개선 되었으나 상황에 따라서 서브쿼리는 DEPENDENT SUBQUERY 타입으로 실행되는 경우가 많습니다
먼저 SQL Plan 을 확인 해봐야 하겠지만 Plan이 좋지 못하다면 가급적 서브쿼리나 인라인 뷰는 직접 풀어서 조인으로 변경하는 것이 MySQL은 아직까지는 SQL Plan 상 더 좋은 결과를 가져올수 있는 경우가 많습니다.
* 포스팅은 MySQL 5.7 버전에서 진행된 사례(상황) 입니다.
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
[참고] MySQL 서버 8.0.24 추가된 Transform correlated scalar subqueries 기능에 의해서 8.0.24 버전 부터는 서브쿼리에 대해서 이전 보다 많은 Query Transformation(Like Oracle's Subquery unnest) 기능을 사용할 수 있게 되었습니다.
자세한 내용은 아래 포스팅을 참고해보시면 됩니다.
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 | +---+---------+-----------+--------+-------------+---------+---------+-----------------------+
위와 같은 Join Delete 의 경우 주의 할점은 쿼리 작성 형태에 따라서 삭제 대상 테이블이 달라 질 수 있습니다.
위의 경우 DELETE 키워드와 FROM 절 사이에 테이블을 명시하였고 이럴 경우 DELETE 키워드와 FROM 절에 명시된 테이블이 삭제 대상이 되게 됩니다.
관련된 추가적인 사항은 다음 블로그 포스팅을 참조하시면 될것 같습니다.
• JOIN DELETE (Multiple-table Delete)
6. Conclusion
조회 대상이 크지 않고 삭제나 변경의 건수가 크지 않다면 보통의 경우 IN-서브쿼리 형태로도 큰 문제없이 진행될 것 입니다.
다만 이번과 같이 조회 대상에 사용할 적절한 인덱스가 없다거나 조회 건수가 많거나 하는 등의 조회 대상의 범위가 크다면 Update/Delete 구문에서의 IN-서브쿼리를 가능한 범위내에서 조인 형태로 변경하는 것을 고려 해볼수 있을 것 같습니다.
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
글 잘보았습니다. 도움이 많이 됐네요.
안녕하세요
댓글 감사합니다!
좋은 하루 되세요