작성일: 2024-10-05 (토)
오늘의 주제는 “EXISTS 활용하기” 입니다.
EXISTS 연산자란??
- WHERE 절에 작성되는 서브쿼리를 통해 데이터의 유무를 판단할 수 있음
- 조건에 해당되는 데이터가 있다면 TRUE, 없다면 FALSE 리턴
- 조건에 해당되는 데이터를 찾으면 더이상 스캔하지 않고 결과를 리턴
- IN 연산자와 달리 데이터의 유무만을 판단하여 결과값을 리턴하기 때문에
데이터가 많을수록 성능 향상 효과를 기대할 수 있음
- IN 연산자와 달리 데이터의 유무만을 판단하여 결과값을 리턴하기 때문에
- 쿼리 순서: [메인 쿼리] → [서브 쿼리]
작성 방법
테이블명: emp
| empno | ename | hiredate | deptno |
|---|---|---|---|
| 100 | KING | 1990-01-01 | NULL |
| 101 | SANGWON | 1996-05-23 | 10 |
| 102 | HYOJU | 1996-09-11 | 10 |
| 103 | HYUNSU | 1992-09-03 | 20 |
| 104 | MINJU | 2000-05-05 | 20 |
| 105 | SANGWOO | 1997-02-01 | 30 |
테이블명: dept
| deptno | dname |
|---|---|
| 10 | Engineer |
| 20 | Common Team |
| 30 | CS Team |
| 40 | Marketing |
select a.*
from test.emp a
where exists (
select 1
from test.dept b
where a.deptno = b.deptno
and b.deptno = 10
)
; -- 2
-- 101 SANGWON 1996-05-23 10
-- 102 HYOJU 1996-09-11 10현재 근무하고 있는 직장에서는 “쿼리문 검수 및 튜닝 업무”를 수행하고 있습니다.
서비스의 규모가 커지면 기존의 애플리케이션단에서 사용하던 쿼리는 점점 느려지고
이는 서버 부하 및 고객에게 부정적인 경험으로 연결됩니다.
그렇기 때문에 DBA에게 쿼리 튜닝 업무는 빠질 수 없는 업무 중 하나입니다.
쿼리 튜닝은
기존에 사용되던 쿼리를 튜닝하는 것만 아니라 새롭게 사용될 쿼리 또한 검수하고
서버에 부하를 줄 수 있다고 판단될 경우 튜닝 과정까지 거치게 됩니다.
엔지니어가 작성한 쿼리문을 확인하면
대부분의 경우 JOIN 또는 WHERE 절에 IN 연산자를 사용하여 서브 쿼리를 작성하는 것을 볼 수 있었습니다.
다른 테이블의 데이터를 참조하여 조회할 때는 다양한 방법을 통해서 조회할 수 있지만
조회 및 참조하고자 하는 테이블의 데이터가 얼마나 존재하는지에 따라서
이를 최적화할 수 있습니다.
테이블명: 주문
데이터: 100,000건
create table 주문 (
order_id int not null auto_increment,
order_code varchar(20) not null,
item_code varchar(20) not null,
...
create_date datetime not null default now(),
primary key (order_id),
unique (order_code),
key (create_date)
);테이블명: 상품
데이터: 1,000,000건
create table 상품 (
item_id int not null auto_increment,
item_code varchar(20) not null,
create_date datetime not null default now(),
...
primary key (item_id),
unique (item_code),
key (create_date)
);데이터가 10만 건 존재하는 주문 테이블과 데이터가 100만 건 존재하는 상품 테이블이 있다고 가정해보겠습니다.
2023년 1년 동안 등록되었던 상품을 주문한 주문 건수를 보고 싶다면
아래와 같은 쿼리들이 나올 수 있을 것 같습니다.
-- 1번)
select count(distinct a.order_code)
from 주문 a
join 상품 b on a.item_code = b.item_code
where b.create_date >= '2023-01-01'
and b.create_date < '2024-01-01'
;
-- 2번
select count(*)
from 주문 a
where exists (
select 1
from 상품 b
where a.item_code = b.item_code
and b.create_date >= '2023-01-01'
and b.create_date < '2024-01-01'
)
;
-- 3번
select count(*)
from 주문 a
join 상품 b on a.item_code = b.item_code
where a.item_code in (
select b.item_code
from 상품
where create_date >= '2023-01-01'
and create_date < '2024-01-01'
)
;[쿼리1]의 경우 INNER JOIN 방식을 통해서 데이터를 조회하는 방식을 사용하였으며,
[쿼리2]의 경우 EXISTS 연산자를 통해서 데이터를 조회하는 방식을,
[쿼리3]의 경우 IN 연산자를 통해서 데이터를 조회하는 방식을 사용했습니다.
가상의 테이블이기 때문에 각각의 조회되는 시간은 알 수 없겠으나,
여기서 가장 효율적인 방식의 쿼리는 [쿼리2] 입니다.
먼저 [쿼리1]에 대해서 간단하게 보자면
INNER JOIN을 통해서 1년 동안의 등록된 상품만을 조인하여 가져올 수 있지만
MySQL은 기본적으로 NL 조인을 사용하고 있기 때문에 조인되는 데이터의 개수가 많아질수록 성능 부하가 발생하게 되는 문제점이 있습니다.
[쿼리1]과 [쿼리3]의 경우 데이터베이스를 처음 접할 때 다양한 예제에서 다루기 때문에
많이 익숙하지만 [쿼리2]의 경우 흔히 접하기는 힘든 예제인데요,
EXISTS 연산자는 맨 처음 언급했듯이
“조건에 해당되는 데이터를 찾으면 더이상 스캔하지 않고 결과를 리턴” 한다는 특징을 가지고 있습니다.
그렇기 때문에 조건에 맞는 데이터를 찾게 되면 뒤의 데이터는 더 이상 확인하지 않기 때문에
그만큼의 성능 이점을 가져올 수 있게 됩니다.
[쿼리3]은 IN 연산자를 사용하는 예제입니다.
보통 우리가 흔히 아는 IN 연산자의 경우 서브 쿼리에서 조회된 값을 비교하며 데이터를 조회하기 때문에
성능 부하가 발생할 수 있다고 알고 있습니다.
MySQL 8.x 버전부터는 “First Match” 기법을 통해서
실제 조인을 하지 않고 레코드의 유무 여부만을 판단하는 세미 조인을 통해 데이터를 조회할 수 있게 되었습니다.
하지만, “Fisrt Match”만 믿고 IN 연산자를 사용하는 것은 옵티마이저가 언제 어떻게 순간 바뀔지 모르기 때문에 세미 조인을 사용한다면 EXISTS 연산자를 사용하는 것이 바람직합니다.
결과적으로 상품 테이블의 데이터가 100만 건인 것을 고려했을 때
JOIN, IN 연산자보다는 EXISTS 연산자가 더욱 효율적임을 알 수 있습니다
하지만!
쿼리를 작성할 때 무조건 EXISTS 연산자를 사용한 세미 조인을 사용하는 것만이 정답은 아닙니다.
쿼리의 호출 빈도, 데이터의 양 등등 다양한 부분을 고려해 최적의 판단을 내리는 것이
DBA가 수행하는 쿼리 튜닝의 업무라고 생각됩니다.
끊임없는 공부를 통해서 어떠한 상황에서도 최선의 효율을 낼 수 있는
쿼리를 작성할 수 있도록 노력해야 합니다.
