SQL 쿼리 튜닝 - UNION

배경

운영 중인 시스템에서 Lock Time out 이 자주 발생하는 기능이 있어 확인해보았습니다.

특정 테이블에 DELETE 를 하면서 서브 쿼리를 사용을 하고 있고 한번 기능이 실행이 되면

매우 빈번하게 DELETE 가 발생하고 있기 때문에 DELETE가 순간적으로 몰리면서 X LOCK에

의한 Lock Time out이 발생할 가능성이 높아 보여 SQL에 대해 살펴보았습니다.

튜닝 과정

•

테스트 환경 

◦

MariaDB 10.1.x

해당 SQL에 Sample Code 다음과 같습니다.

EXPLAIN
DELETE C FROM TEST_C C
INNER JOIN (
SELECT NO
  FROM TEST_A
 WHERE NO IS NOT NULL
UNION 
SELECT NO
  FROM TEST_B
 WHERE NO IS NOT NULL
) T 
ON C.IDX = T.NO
SQL
복사

SQL 튜닝은 보통 SELECT 쿼리만 가능합니다.

얼핏 보면 DELETE 하는 쿼리 같지만 DELETE 하는 대상을 만들기 위해서 서브 쿼리로 임시 테이블을

생성하여 임시 테이블과 INNER JOIN 합니다.

따라서 서브 쿼리 부분은 얼마든지 튜닝이 가능합니다.

서브 쿼리만 따로 분리하여 수행을 해보니 시간이 N초 이상 소요가 됩니다.

MariaDB 같은 경우 쿼리 앞에 EXPLAIN 을 붙여서 실행을 하면 실행 계획 결과를 볼 수 있습니다.

실행 계획 결과는 다음과 같습니다.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
"1"	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	1	NULL
1	PRIMARY	<derived2>	ref	<auto_key0>	<auto_key0>	9	sample.C.IDX	2	Using index
2	DERIVED	TEST_A	ALL	NULL	NULL	NULL	NULL	1	Using where
3	UNION	TEST_B	ALL	NULL	NULL	NULL	NULL	1	Using where
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	Using temporary

위 실행 결과를 보면 type 이 하나 빼고는 모두 ALL로 인덱스를 전혀 사용하고 있지 않습니다.

TEST_A와 TEST_B는 실제 몇 백만 건이 쌓여 있는 데이터로 서브 쿼리에서 UNION을 했을 경우

UNION은 UNION ALL과 달리 각 테이블의 몇 백만 건을 전부 비교하면서 중복 제거를 합니다.

이 작업이 시간이 지날 수록 더 증가 할 것이므로 수정을 해야 하는 부분입니다.

TEST_A와 TEST_B 테이블이 UNION 된 SQL의 결과를 TEST_C 테이블과 INNER JOIN 하고 있으

므로 UNION 결과를 최대한 줄여주면 도움이 될 것으로 보입니다.

EXPLAIN
DELETE C FROM TEST_C C
INNER JOIN (
SELECT C.IDX
  FROM test_c C INNER JOIN
 (SELECT NO
  FROM test_a 
  WHERE NO IS NOT NULL
 ) T1 
 ON C.IDX = T1.NO
UNION
SELECT C.IDX
  FROM TEST_C C INNER JOIN
 (SELECT NO
  FROM TEST_B 
  WHERE NO IS NOT NULL
 ) T2 
 ON C.IDX = T2.NO
) T 
ON C.IDX = T.IDX
SQL
복사

TEST_A와 TEST_B UNION 하는 서브 쿼리를 수정하여 UNION 하기 전에 먼저 TEST_C 테이블과

조인하여 데이터 수를 줄입니다.

만약 TEST_A와 TEST_B에 100만 건이 있다면 총 100만 건 끼리 비교 결과를 가지고 다시 TEST_C

와 비교해야 하므로 최대한 결과를 줄이기 위함입니다.

수정한 실행 계획 결과는 다음과 같습니다.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
"1"	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	1	NULL
1	PRIMARY	<derived2>	ref	<auto_key0>	<auto_key0>	8	sample.C.IDX	2	Using index
2	DERIVED	C	ALL	NULL	NULL	NULL	NULL	1	NULL
2	DERIVED	<derived3>	ref	<auto_key0>	<auto_key0>	8	sample.C.IDX	2	Using index
3	DERIVED	TEST_A	ALL	NULL	NULL	NULL	NULL	1	Using where
4	UNION	C	ALL	NULL	NULL	NULL	NULL	1	NULL
4	UNION	<derived5>	ref	<auto_key0>	<auto_key0>	9	sample.C.IDX	2	Using index
5	DERIVED	TEST_B	ALL	NULL	NULL	NULL	NULL	1	Using where
NULL	UNION RESULT	<union2,4>	ALL	NULL	\NULL	NULL	NULL	NULL	Using temporary

type이 ref로 인덱스 레인지 스캔을 사용하는 것을 확인 할 수 가 있습니다.

이제 TEST_A와 TEST_B가 줄어든 결과로 UNION을 수행하고 TEST_C와 동등 조인하여 DELETE 작업을

수행 하게 됩니다.

실제 데이터가 담긴 테스트 DB에서 수정한 SQL 수행 했을 경우 0.1초 이하로 수행 시간이 줄어든 것

확인 할 수 있었습니다.

기존 SQL은 1초 이상 N초가 걸렸으므로 최소 10배 이상 빨라졌고 여기에 실제 인덱스 생성되어 있지

않는 컬럼의 경우 인덱스를 타게 하기 위해 인덱스를 생성 했을 경우 조금 더 수행 시간이 줄어

들었습니다.

실제 운영 환경에 적용한 이후로는 더 이상 Lock Timeout 이슈는 발생하지 않았습니다.

마무리

DBMS 마다 실행 계획 보는 방법은 조금씩 다르지만 조회 SQL이 FULL 스캔 없이

INDEX를 타게 하는 방법 만으로 성능 이슈를 쉽게 해결할 수 있었습니다.

참고

MariaDB 실행 계획 관련해서는 잘 정리되어있는 블로그가 있어 공유하겠습니다.

조금 더 깊게 MariaDB를 공부해보실 분이나 실무에서 참조 용으로 쓴다면 책은 Real MariaDB 라는

책을 추천합니다.