[MySQL] SQL문 최적화

작성일 : 2022-12-08 (목)

오늘의 주제는 “SQL 최적화” 입니다.

DBA 업무를 수행하면 데이터베이스와 관련된 모든 운영 업무를 맡게 됩니다.
많은 업무중에서도 오늘은 SQL 최적화에 대한 내용을 다뤄보려 합니다.

SQL 최적화, 쿼리 튜닝이라고도 불리는 해당 업무는
과도한 I/O가 발생하지 않는지(적절한 인덱스가 있음에도 불구하고 테이블 풀스캔을 하거나 다른 인덱스를 통한 스캔을 하지는 않는지) 확인하며, 옵티마이저가 최적의 효율로 데이터를 스캔할 수 있도록 SQL문을 최적화 하는 작업을 의미합니다.

아래의 쿼리는 제가 관리하던 데이터베이스에서 조회 권한을 할당받은 엔지니어가
1회성으로 다른 팀의 데이터 추출 작업을 위해 작성한 쿼리입니다.

해당 쿼리를 통해 여러 쿼리 튜닝 기법을 소개할 수 있을 것 같아 해당 쿼리를 소개하려 합니다.

# 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

select a.code
     , a.price
     , a.time as `datetime`
     , b.type
     , b.number
     , b.time as `request_time`
  from emp a
  left join dept b on a.code = b.code
  left join dept_detail c on b.dept_col = c.dept_col
 where a.code in (
                  select a.code
                       , max(a.emp_no)
                       , max(a.time) as `last_time`
                       , date(max(a.time)) as `last_date`
                       , count(*) as `total_count`
                    from emp a 
                    left join dept b on on a.code = b.code
                    left join dept_detail c on b.dept_col = c.dept_col
                   where a.status = 'wait'
                     and a.country = 'kr'
                     and b.type = 'extend'
                   group by a.code
                 ) extend on a.code = extend.code
   and total_count = 1
union
select a.code
     , 0 as price
     , b.create_date as `datetime`
     , 'unknown' as `type`
     , b.number
     , null as `request_time`
  from emp a
  left join dept b on a.code = b.code
 where a.country = 'kr'
   and b.version = 'v1'
   and b.col_num is null
   and date(b.create_date) between '2020-01-01' and '2023-01-01'
;

위에서 소개한 쿼리는 10분이 넘는 시간 동안 종료되지 않고 수행되던 슬로우 쿼리입니다.

엔지니어가 조회하고자 하는 emp, dept, dept_detail 테이블은 모두 데이블의 크기가 큰 무거운 테이블입니다.
이러한 조건을 고려했을 때 10~20분을 더 기다려도 위 쿼리에 대한 결과집합은 확인하기 어려울 것 같아 보이네요

1회성 쿼리이기 때문에 모든 항목에 대해 최적화를 진행하는 것보다는 작은 공수로 빠르게 스캔될 수 있도록 최적화 방안을 고민하게 되었는데요,

위 쿼리에는 여러 문제점이 존재합니다.
그중에서도 오늘은 크게 3가지 정도만 알아보고자 합니다.

1. 무분별한 OUTER JOIN

• 위 쿼리는 테이블 간의 관계, 테이블의 크기 등 다양한 상황을 고려하지 못한 채 테이블 간의 조인 방식을 Outer 방식을 사용했습니다.
• 쿼리를 작성할 때에는 각 테이블 간의 연관성, 테이블 크기, 적합한 인덱스 유무 등 다양한 항목에 대한 고려가 필요합니다.
  테이블에 대한 정보를 전혀 모르고 무작정 조회하면 서버에 부하를 줄 수 있습니다.
• 다른 테이블의 데이터를 참조해서 데이터를 불러오고자 할 때는 JOIN, IN, EXISTS 등 다양한 방법이 있지만 오늘은 JOIN 방식으로 빠르게 튜닝해보고자 합니다.

2. 비효율적인 방식으로 IN 연산자 사용

• IN 과 EXIST 은 WHERE절에서 사용되며, 조건에 따라 데이터를 걸러내고 결과를 조회할 때 사용한다는 공통점이 존재합니다.
  그러나, 두 개의 연산자는 명확한 차이점이 존재합니다.

• 연산자(exists, in)	특징
  EXISTS	조건에 해당하는 데이터의 유무를 확인하여 결과값 리턴
  	처리 순서: 메인 쿼리 → 서브 쿼리 순서로서 서브 쿼리에서 메인 쿼리의 정보를 토대로 모든 조건을 한 번에 설정
  IN	조건에 해당하는 데이터의 컬럼과 비교하며 체크
  	SELECT 절의 조회 컬럼 값으로 비교하기 때문에 비교할 데이터가 많을수록 EXISTS에 비해 성능이 떨어짐
  	처리 순서: 서브 쿼리 → 메인 쿼리 순서로서 서브 쿼리가 메인 쿼리의 정보를 가져올 수 없기 때문에 조건을 각각 설정

• 이러한 차이점이 존재하기 때문에 IN 의 경우, 비교하고자 하는 데이터가 적거나, 상수 혹은 문자열 등 사용할 때 높은 효과를 볼 수 있습니다.
  

        SELECT a.code
          FROM test.emp a
         WHERE a.code in ('a', 'b', 'c', 'd', 'e', 'f')
        ;

3. UNION

• union은 다수의 select 결과값을 하나로 병합할 때 사용하는 방식입니다.
• union은 기본적으로 중복되는 데이터를 제거하고 결과값을 보여줍니다.
  중복 데이터를 제거하는 과정에서 임시 테이블을 사용하기 때문에 성능이 매우 나쁩니다.

크게 3가지 + @ 문제점이 존재하여 쿼리 튜닝에 많은 시간을 소요해야 하는 상황이었으나,
빠르게 처리해야 하는 업무임을 엔지니어 분께서 전달 해주셨기에 (1), (2) 번의 문제점만 간단하게 처리하는 과정을 진행했습니다.

튜닝 쿼리

# 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

select a.code
     , a.price
     , a.time as `datetime`
     , b.type
     , b.number
     , b.time as `request_time`
  from emp a
  join dept b on a.code = b.code
  join dept_detail c on b.dept_col = c.dept_col
 where exists (
               select a.code
                    , max(a.emp_no)
                    , max(a.time) as `last_time`
                    , date(max(a.time)) as `last_date`
                    , count(*) as `total_count`
                 from emp a 
                 join dept b on on a.code = b.code
                 join dept_detail c on b.dept_col = c.dept_col
                where a.status = 'wait'
                  and a.country = 'kr'
                  and b.type = 'extend'
                group by a.code
                having count(*) = 1
               ) extend on a.code = extend.code
union
select a.code
     , 0 as price
     , b.create_date as `datetime`
     , 'unknown' as `type`
     , b.number
     , null as `request_time`
  from emp a
  join dept b on a.code = b.code
 where a.country = 'kr'
   and b.version = 'v1'
   and b.col_num is null
   and b.create_date >= '2020-01-01'
   and b.create_date <  '2023-01-01'
;

급하게 튜닝 작업을 진행하였지만 매우 만족스러운 결과가 나왔습니다.
쿼리에 사용된 3개의 테이블은 각각 40만 / 60만 / 70만 정도로 데이터의 크기가 크지 않았기에
이정도의 튜닝 작업으로도 빠르게 처리가 가능했던 것 같습니다.

오늘은 SQL 최적화 기법에 대한 소개를 진행했습니다.
평소와 달리 1회성이라는 부분 + 긴급성이 존재하여 블로그를 통해 소개하기에 다소 미흡한 부분이 다소 존재했던 것 같습니다.

이외에도 다양한 최적화 기법이 존재하기 때문에 다음 번에는 다른 사례를 통하여
더욱 다양한 최적화 기법을 소개할 예정입니다.