[MySQL 튜닝] EXISTS를 이용한 SQL 튜닝

 

ㅁ 개요

 쿼리를 튜닝할 때에 가장 큰 요소는 색인데이터를 최소화 하는 방법이 있습니다. 이번 글에서는 EXISTS 구문을 통해서 불필요한 IO를 줄여 쿼리를 튜닝하는 방법을 정리해 보았습니다.

 

 

ㅁ 일러두기

 ㅇ  쿼리 테스트를 위해 [MySQL] 실습환경 구성하기에서 생성한 MySQL이며, 버젼은 5.7.37입니다.

 ㅇ categories(8 row)에 속한 products(77 row) 테이블을 샘플로 하였습니다.

 

 

ㅁ EXISTS 연산자란?

  EXISTS 연산자는 서브쿼리에 데이터가 존재하는지 체크하고 존재할 경우 TRUE를 반환합니다. EXISTS 연산자는 IN 연산자와 비슷한 용도로 사용할 수 있으며, IN 연산자는 비교할 값을 직접 대입할 수 있지만 EXISTS 연산자는 서브쿼리만 사용할 수 있습니다. 서브쿼리를 사용할 경우 상황에 따라서 IN 연산자 보다 EXISTS 연산자의 성능이 좋을 수 있습니다. 이 부분에 대해서는 [MySQL 튜닝] IN vs INNER JOIN vs EXISTS 성능비교에서 다루었습니다. 쿼리의 순서는 메인쿼리 이후 EXISTS 쿼리가 수행되며, 조건에 해당하는 ROW가 탐색되면 더이상 수행하지 않는 특성이 있습니다.

 

 

ㅁ SQL 구문 및 실행 계획

SELECT c.CategoryID
FROM categories c INNER JOIN products p ON p.CategoryID = c.CategoryID
WHERE p.UnitPrice > 10
GROUP BY c.CategoryID
ORDER BY c.CategoryID

 categories 테에블에는 8건의 데이터, products 테이블에는 77건의 데이터가 있습니다. 위 SQL은 UnitPrice가 10 초과인 카테고리 코드를 출력하기 위해 작성되었습니다.

 

   조회를 하면 최종 건수는 8건이지만 77건 전체의 데이터를 Full Scan하고 있습니다. 전체 77건의 데이터를 읽고 이를 GROUP BY 하여 중복을 제거하는 로직은 불필요한 IO를 발생하기 때문에 쿼리 튜닝이 필요합니다. 

 

 

  플랜에서 보면 Nested Loop Join(줄여서 NL)이 수행되었는데, NL 조인 특성상 선행 데이터의 수만큼 후행 테이블에 조인을 하기 때문에 선행 데이터가 많으면 그만큼 IO가 발생하게 됩니다. 이를 개선하기 위해 EXISTS 구문을 이용하여 불필요한 I/O를 줄이도록 하겠습니다.

 

 

ㅁ 튜닝된 SQL 구문 및 실행 계획

SELECT c.CategoryID
FROM categories c
WHERE EXISTS (
        SELECT '1'
        FROM  products p
        WHERE p.CategoryID = c.CategoryID
          AND p.UnitPrice > 10
    )
GROUP BY c.CategoryID
ORDER BY c.CategoryID

 categories 테이블과 products 테이블을 서브 쿼리인 EXISTS절로 변경하였습니다.

튜닝된 SQL 구문은 메인 쿼리인 categories 조회 8건 중에서 products에서 조회된 15건 중 존재 유무만 확인하면 되기 때문에 I/O가 개선되었습니다.

 

 

# 63건
SELECT count(1)
FROM categories c INNER JOIN products p ON p.CategoryID = c.CategoryID
WHERE p.UnitPrice > 10
# GROUP BY c.CategoryID
# ORDER BY c.CategoryID
;

# 8건
SELECT count(1)
FROM categories c
WHERE EXISTS (
        SELECT '1'
        FROM  products p
        WHERE p.CategoryID = c.CategoryID
          AND p.UnitPrice > 10
    )
# GROUP BY c.CategoryID
# ORDER BY c.CategoryID
;

  위의 쿼리는 Group By를 통한 중복제거 전에 조회되는 I/O를 확인하기 위해서 작성되었습니다. INNER JOIN의 경우,  63건 조회 후 중복제거가 이루어졌습니다. EXISTS의 경우, 이미 색인과정에서 존재하는 1건만 조회되었기 때문에 이미 중복이 제거된 상태였습니다.그래서 튜닝을 통해 기존 63건에서 8건으로 IO를 감소하는 효과를 얻었습니다.

 

 

ㅁ 함께 보면 좋은 사이트

[MYSQL] 📚 서브쿼리 연산자 EXISTS 총정리

[MYSQL] 📚 서브쿼리 연산자 EXISTS 총정리 (성능 비교)