SubQuery와 Join 절 성능 분석

728x90

SMALL

면접에 나왔던 질문 중 Join과 SubQuery 중 Subquery를 사용했을 때 성능이 저하하는 경우는 어떤 상황인지 적는 문제가 나왔을 때 적잖이 당황했어서 Join , Subquery에 대한 개념과 성능을 비교해서 적어보려고 한다.

Join

- 두 개 이상의 테이블을 연결해 하나의 결과 집합을 만드는 방식

- 일반적으로 행을 수평 결합(컬럼의 확장)

SELECT ...
FROM A
JOIN B ON A.ID = B.A_ID

Join의 종류

- Inner Join

양쪽 테이블 모두 조건을 만족하는 행만 선택

- Left Join

왼쪽 테이블은 모두, 오른쪽은 없으면 Null

- Right Join

오른쪽 테이블은 모두, 왼쪽은 없으면 Null

- Full Outer Join

양쪽 모두 보존, MySQL에서는 지원하지 않음(Union으로 구현은 가능하나 쓸 일이 거의 없다)

Join 문제 예시

실제로 내가 필기 시험에서 받았던 문제 유형이다.

고객명단 테이블과 구매 내역 테이블이 있다. 구매내역이 없어도 고객은 모두 조회해야 하며 구매 건수가 없을 경우 Null대신 0으로 출력한다.

고객 명단 테이블(Customers)

customer_id	name
1	Alice
2	Bob
3	Charile

구매 내역 테이블(Orders)

order_id	customer_id	amount
100	1	5000
101	1	3000
102	2	1000

정답 SQL 쿼리

보는 바와 같이 Left Join을 사용하면 구매하지 않은 customer인 3번은 내역이 없어서 null로 반환될 수 있다. 이를 ifnull을 사용해서 0으로 변환하고 값을 보여주며, 중복된 값이 반환되지 않도록 GroupBy로 중복값을 제거하는 과정을 확인할 수 있다.

-- 테이블 생성
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    amount INT,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

-- 더미데이터 삽입
insert into customers(customer_id,name) values
(1,'alice'),(2,'bob'),(3,'charile');

insert into orders values
(100,1,5000),(101,1,3000),(102,2,1000);

--정답 코드
select c.name, ifnull(sum(o.amount),0) as total_amount
from customers c
left join orders o on c.customer_id = o.customer_id
group by c.customer_id, c.name;

SubQuery

- 쿼리 내부에 또 다른 Select 문이 들어있는 일시적인 테이블

- 값 필터링, 컬럼 계산, 테이블 대체 등 다양한 용도로 사용된다.

장점

- SQL 구문 안에서 유연하게 또 다른 SQL 구문을 만들 수 있다. 코딩 시 편리함
단점

- 연산 비용이 추가된다

서브쿼리는 뷰와 마찬가지로 가상의 테이블을 구성한다. 그렇기 때문에 서브쿼리에 접근할 때마다 SELECT 구문에 접근하여 데이터를 만들게 되고, 이로 인해 연산비용이 추가된다.

서브쿼리가 간단한 경우에는 무리가 없지만 서브 쿼리 안의 내용이 복잡해 질 수록 비용이 추가로 발생한다.

- 최적화를 받을 수 없다

서브쿼리와 테이블의 큰 차이점은 서브쿼리에는 메타 정보가 담겨있지 않다는 것이다. 명시적인 제약이나 인덱스가 작성되어 있지 않다.

그렇기 때문에 옵티마이저가 쿼리에 접근하고 해석할때 필요한 정보를 얻을 수 없다.

이는 내부적으로 복잡한 연산 수행, 용량이 큰 서브쿼리를 사용할 때 성능적으로 부하가 발생할 수 있다.

- 쿼리가 복잡해진다

SubQuery 종류와 개선 방법

- 스칼라 서브쿼리

내부 쿼리가 단일 값을 반환하거나 1개의 열과 1개의 행을 반환하는 경우를 말한다.

만약 내부 쿼리가 단일한 값을 반환한다면 이는 조인으로도 충분히 구현할 수 있다는 의미가 된다.

SELECT
 name,
 cost
FROM product
WHERE id =
( SELECT product_id
   FROM sale
   WHERE price = 2000
        AND product_id = product.id );

위 쿼리는 상품의 이름과 가격을 조회한다.

우리는 모든 상품이 아닌 Where조건에 적합한 데이터만 조회하고 싶어 한다.

그럼 서브 쿼리를 살펴보자. sale 테이블은 각 상품의 판매가에 대한 정보를 담고 있다.

서브쿼리 처리 순서는 판매가가 2000달러인 상품중에서(1) // 외부 쿼리의 id값과 서브 쿼리의 product_id값이 동일한지 비교하여 일치하는 상품의 아이디만 반환해 준다. 여기서 주의할 점은 id를 비교하는 과정에서 각 아이디를 비교할 때마다 내부 쿼리가 수행된다는 점이다.

이러한 부분이 매번 단일한 값이 반환되므로 스칼라 서브 쿼리라고 명칭하는 점이다.

또 내부 쿼리가 실행되기 위해서는 외부 쿼리에 의존하는 서로 상관관계를 갖는 쿼리를 서브 쿼리라고 한다.

위 쿼리는 효율적이지 못하다. 서브 쿼리 내에서 외부 쿼리와 Id와 내부 쿼리 product_id를 매칭하는 과정에서 각 id값마다 내부 쿼리가 실행되어 전체 쿼리가 실행되는 시간이 길어질 수밖에 없기 때문이다.

이를 Join절로 수정하면 다음과 같이 작성할 수 있다.

SELECT
 p.name,
 p.cost
FROM product AS p
JOIN sale AS s
  ON p.id = s.product_id
WHERE s.price = 2000;

Join을 통해 product테이블과 sale테이블을 연결했다. 두 테이블을 연결하기 위한 product의 id열과 sale의 product_id열을 서로 매칭하였다. 마지막 조건절에 price가 2000달러인 상품만 조회될 수 있도록 필터링하였다.

Join절로 대체함으로써 테이블을 한 번만 읽고, 조건에 따라 매칭하는 방식으로 한 번에 처리할 수 있다. 또한 옵티마이저가 메타데이터를 읽어서 인덱스를 활용한 최적화를 수행할 수도 있다. 또한, 코드도 간결해지고 명확해져서 가독성도 증가했다.

- In 서브 쿼리

In연산자 안에 서브 쿼리가 있다면 해당 서브 쿼리를 조인으로 바꿔 쓸 수 있다. IN연산자의 서브 쿼리는 여러 개의 값을 반환하게 된다.

SELECT
 name,
 cost
FROM product
WHERE id IN ( SELECT product_id FROM sale );

위 쿼리의 외부 쿼리는 product테이블에서 이름과 가격을 조회한다. 그런 후 서브 쿼리에서 반환된 아이디들을 통해 결괏값을 추가로 필터링하는 과정을 수행한다. 위 코드를 JOIN절로 수정한다면 어떻게 나타낼 수 있을까?

SELECT DISTINCT
 p.name,
 p.cost
FROM product AS p
JOIN sale AS s
   ON p.id = s.product_id;

코드가 훨씬 간결해졌다. product_id로 2개의 테이블을 연결한 후 그 테이블에서 상품의 이름, 가격을 조회한다. 여기서 사용한 Join 은 Inner Join으로 판매된 상품 결과만 가져오게 된다. 또한 dintinct 키워드로 중복값을 제거한다. 장점은 위에서 설명한 부분과 거의 동일하다. 만약 Not in을 사용해야 한다면 Left Join과 Where 절에 is Null 키워드를 사용해서 가져올 수 있을 것이다.

- Exists 서브쿼리

존재 여부만 검사

SELECT
 name,
 cost,
 city
FROM product
WHERE NOT EXISTS
 ( SELECT id 
   FROM sale 
   WHERE year = 2020 AND product_id = product.id );

위 쿼리는 외부 쿼리 테이블이 가지고 있는 상품들을 가지고 서브 쿼리의 조건과 일치하는지 확인한다. 서브쿼리가 정한 조건은 판매연도가 2020년이면서 sale 테이블과 product의 id값이 일치하는 데이터를 가져온다는 것이다. 이때 not exists 키워드를 사용했기 때문에 서브 쿼리가 정한 조건과 정확히 일치해야 한다. 이를 join으로 수정하면 다음과 같다.

SELECT 
 p.name,
 p.cost,
 p.city
FROM product AS p
LEFT JOIN sale AS s
  ON p.id = s.product_id
WHERE s.year != 2020 OR s.year IS NULL;

SubQuery를 Join으로 개선할 수 없는 경우

- Group By를 사용한 서브쿼리가 From절에 있을 때

SELECT
 city,
 sum_price
FROM
 ( SELECT
     city, 
     SUM(price) AS sum_price 
   FROM sale
   GROUP BY city ) AS s
WHERE sum_price < 2100;

위 쿼리에서 서브 쿼리는 도시의 이름과 각 도시별 총 판매액을 반환한다. 각 도시별 총 판매액은 SUM 집계 함수와 Group By를 통해 구매하였는데 서브 쿼리의 결과를 하나의 테이블로 간주한 외부 쿼리는 무조건 별칭이 있어야 하며 Join절로 대체할 수 없다. 그 이유는 Join은 단순히 두 테이블을 족너에 맞게 연결해주는 역할이다. 현재 예시에서는 도시별 판매 총합을 서브쿼리에서 먼저 구하고, 그 집계 결과를 기준으로 필터링하는 것이다.

집계된 값을 반환하는 서브 쿼리가 Where 절에 있을때

SELECT name
FROM product
WHERE cost < ( SELECT AVG(price) FROM sale );

서브 쿼리가 전체 테입르을 대상으로 집계(AVG)를 수행하고, 이 집계값 하나를 기준으로 외부 쿼리의 각 행을 필터링하기 때문이다.

위에서 봤던 서브쿼리는 집계 함수가 들어가지 않은 단순 쿼리여서 Join으로 바꿀 수 있었지만 이는 sale 테이블 전체의 평균값을 하나만 반환하는 스칼라 서브쿼리이므로 각 행마다 매칭 조건을 적용해야 한다.

서브 쿼리가 ALL 연산자에 있을 때

SELECT name
FROM product
WHERE cost > ALL( SELECT price FROM sale )

위 쿼리에서 서브 쿼리는 sale 테이블의 모든 price값을 나열한다. cost > ALL(...)은 product 테이블의 각 cost값이 서브쿼리의 모든 price값보다 클 경우만 결과에 포함시킨다. 즉, product 가격이 sale 테이블의 모든 판매가보다 높아야만 결과에 나오는 것이다. 이는 모든 조건을 만족하는 경우에만 참이되는 ALL연산자의 특성이기에 Join으로 대체할 수 없다.

다시 말하지만 Join은 기본적으로 두 테이블의 행을 연결하고, 조건에 따라 일치하는 행을 필터링하는 역할이다.

여기서는 모든 행과의 비교라는 조건이 필요하기에 서브쿼리가 더 적절하다고 볼 수 있다.

테이블, 뷰, 서브쿼리에 대한 차이점

구분	설명	특징
테이블	DB에 물리적인 저장	영속적
뷰	가상의 테이블, 접근할 때 마다 Select 구문이 실행된다	영속적, 물리적 저장 X
서브 쿼리	가상의 테이블, SQL 구문 실행 중에만 존재	일시적, 물리적 저장 X