[쿼리튜닝 - 2] 사내 조회쿼리 성능의 개선 과정 두번째 이야기

해당 글을 보고 오시면 좋아요

 

[쿼리튜닝] 신입 개발자의 간단한 사내 조회 쿼리의 쿼리튜닝 여정.

 
 
 
 
 

서론

팀장님께서 나를 불렀다.
"집계 함수를 사용해서 요청사항 집계를 하는 SQL문이 있는데 ~~~ 성능 최적화를 좀 할 수 있을까요?"
 
기존 쿼리를 받아들고 돌려보았다.
 

 

 
 

기존 상태

SELECT
    YEAR(a.registe_time) AS year,
    MONTH(a.registe_time) AS month,
    a.code,
    count(a.code) AS total_count,
	(SELECT 
		count(DISTINCT a1.id)
	FROM
		dbname.tbname AS a1
	WHERE
		a1.code = a.code
		AND YEAR(a1.registe_time) = YEAR(a.registe_time)
		AND MONTH(a1.registe_time) =  MONTH(a.registe_time)
		AND a1.code != 0
		AND a1.id != ''
	) AS dup_count
FROM
    dbname.tbname as a
WHERE
    YEAR(a.registe_time) = 2023
    AND a.code!=0
    AND a.id!=''
GROUP BY
    YEAR(a.registe_time),
    MONTH(a.registe_time),
    a.code
order by year asc, month asc, total_count desc;

 
위는 요청받은 쿼리이다.
 
위의 쿼리에서, WHERE, GROUP BY, 서브쿼리에 공통으로 들어가는 code, registe_time, id에 대해 단일 인덱스만 생성되어있고, 복합 인덱스는 생성되어 있지 않았다.
 
 
 
 

복합 인덱스 생성

실제로 code에 따른 조회, 그룹핑에 가장 많은 리소스를 차지했고, id와 registe_time은 크게 순서가 상관이 없다고 판단하여
code, registe_time, id의 순서로 복합 인덱스를 생성했다.
 

 
 
성능이 일부 개선되었지만, 여전히 select절에 사용되는 서브쿼리가 마음에 들지 않았다.
 
 
 
 
 

윈도우함수 사용 시도

SQLD, SQLP 공부를 할 때의 기억을 끄집어내서, 아래처럼 변경해주려고 했다.

# 기존
(SELECT 
    count(DISTINCT a1.id)
FROM
    dbname.tbname AS a1
WHERE
    a1.code = a.code
    AND YEAR(a1.registe_time) = YEAR(a.registe_time)
    AND MONTH(a1.registe_time) =  MONTH(a.registe_time)
    AND a1.code != 0
    AND a1.id != ''
) AS dup_count



# window function으로 변경
COUNT(DISTINCT a.id)
OVER (
  PARTITION BY a.code,
  YEAR(a.registe_time),
  MONTH(a.registe_time)
) AS dup_count

 
 

 
 

Error 1235: This version of MariaDB doesn't yet support

 
 
현재 사용하고있는 DB버전에서 지원하지 않는 형식이라고 했다 ;;
 
 

SELECT절의 서브쿼리를 대신할 방법이 없을까?

결론적으로 개선되어야 할 부분은 ID의 DISTINCT 연산을 한 번만 계산하게끔 유도하는 것이다.
 
기존 쿼리는, SELECT절에서, RAW마다 DISTINCT연산을 수행하기 때문이다.
다시 정리해봤다.
 

결국 필요한 데이터는 registe_time, id, code, COUNT(DISTINCT(id)) 이다.

 

 
정리를 얼추 하고나니, 방향이 어느정도 잡혔다.
결국 위의 데이터를 중복연산 없이 단 1번만 조회해서 활용하게 하면 된다고 생각했다.

SELECT
    YEAR(registe_time) AS year,
    MONTH(registe_time) AS month,
    code,
    COUNT(code) AS total_count
    COUNT(DISTINCT id) AS dup_count
FROM
    dbname.tbname
WHERE
    YEAR(registe_time) = 2023
    AND code != 0
    AND id != ''
GROUP BY
    YEAR(registe_time),
    MONTH(registe_time),
    code
ORDER BY
    year ASC,
    month ASC,
    total_count DESC;

 
 
 

WITH절(CTE)을 사용해보자

WITH tempTable AS (
    SELECT
        YEAR(registe_time) AS year,
        MONTH(registe_time) AS month,
        code,
        COUNT(DISTINCT id) AS dup_count
    FROM
        dbname.tbname
    WHERE
        YEAR(registe_time) = 2023
        AND code != 0
        AND id != ''
    GROUP BY
        YEAR(registe_time),
        MONTH(registe_time),
        code
)
SELECT
    YEAR(a.registe_time) AS year,
    MONTH(a.registe_time) AS month,
    a.code,
    COUNT(a.code) AS total_count,
    b.dup_count
FROM
    dbname.tbname AS a
JOIN
    tempTable AS b ON a.code = b.code
    AND YEAR(a.registe_time) = b.year
    AND MONTH(a.registe_time) = b.month
WHERE
    YEAR(a.registe_time) = 2023
    AND a.code != 0
    AND a.id != ''
GROUP BY
    YEAR(a.registe_time),
    MONTH(a.registe_time),
    a.code,
    b.dup_count
ORDER BY
    year ASC, month ASC, total_count DESC;

 
 
위의 쿼리는, WITH를 이용해 DISTINCT(id)연산을 아래 메인쿼리의 raw에 맞게, 같은 조건으로 단 한번만 수행할 수 있게 작성한 쿼리이다.
 
이를 통해 중복 연산을 없앴고, 메인 쿼리에서 임시 데이터를 조인하여 select해주면 간편히 처리가 가능하다!!
 

 
 
실제로 연산 속도도 기존보다 10배나 증가한 모습이다.
 
 
 
 

끝!!! 이 아니라

잘 처리해놓고 기분좋게 성과를 말씀드려야겠다!!!
하고 전달드리고 난 뒤 몇시간 뒤에 업무일지를 기록하는 도중에 의문이 생겼다.
 
중복 아이디를 카운트하는데에 SELECT SUBQUERY, WITH 등등의 추가 리소스가 굳이 필요한가..? 에 대한 의문이었다.
 

SELECT
	YEAR(registe_time) AS year,
	MONTH(registe_time) AS month,
	code,
	COUNT(code) AS total_count,
	COUNT(DISTINCT id) AS dup_count
FROM
	dbname.tbname
WHERE
	YEAR(registe_time) = 2023
	AND code != 0
	AND id != ''
GROUP BY
	YEAR(registe_time),
	MONTH(registe_time),
	code
ORDER BY
	year ASC, month ASC, total_count DESC;

 
 
실제로 위 쿼리는, 같은 결과를 반환하는 동시에, 조회 성능이 조금이라도 더 좋은 코드가 되었다.
 

 
 
 
살포시 팀장님께 메세지를 추가해서 보내드렸다..
 
 
 
 
 

진짜 끝

데이터의 형태, 쿼리의 목적에 따라 조금 성능이 쳐질 수 있어도 WITH절을 활용한다던지, 프로시저 등의 활용으로 가독성을 높일 수도 있고, 이번 포스팅의 쿼리처럼 단순 성능에 치우쳐 코드를 작성해야할 수도 있는 것 같다.
 
API, 서버개발만 하는 현 상황에서, 가끔가다 들어오는 데이터 처리 관련 임무는 귀중한 경험이 되는 것 같다.