쿼리 튜닝하기

 

요즘 일하면서 SQL을 작성할 일이 많았다. 덕분에 쿼리를 작성하는 것이 좀 더 능숙해졌고, 내가 작성한 쿼리가 실행될 때 테이블에 어떻게 접근하게 될지 등 성능에 대한 고민을 많이 해보게 되었다. 실제로 성능이 걱정되어서 조언을 받아서 튜닝을 진행한 건이 몇 개 있어서, 그 내용을 간략하게 정리해보고자 한다.

 

인덱스 타게 하기

사실 쿼리 튜닝의 대부분은 인덱스를 활용하도록 쿼리를 수정하거나, 필요한 인덱스를 추가하는 것이 아닐까 한다. 내가 원래 작성했던 쿼리는 다음과 같다. 

SELECT *
FROM (
    SELECT tmp.*, ROWNUM R_NUM
    FROM (
        SELECT *
        FROM A a
        WHERE a.number = ?
        ORDER BY a.name, a.age, a.height
    ) tmp
    WHERE ROWNUM <= ?
) 
WHERE R_NUM > ?

오라클 12 이전의 버전에서 paging을 처리하는 쿼리인데, 12에서 나온 키워드를 사용하면 다음과 같다.

SELECT *
FROM A a
WHERE a.number = ?
ORDER BY a.name, a.age, a.height
OFFSET ? ROWS FETCH NEXT ? ROWS ONLY

이 쿼리의 경우 ORDER BY 때문에 걱정되었다. {number, age, height}에 대한 인덱스는 존재하는 상태였고, {number, name, age, height} 조합에 대한 결합 인덱스가 존재하지 않는 상태였다. number 조건으로 먼저 걸러서 조회해온 row들을 name부터 다시 정렬해야 하는 부담이 있었다. 이 부담을 줄이려면 {number, name, age, height} 조합으로 인덱스를 만들면 되는데, 이렇게 될 경우 number로 거를 때부터 이미 ORDER BY의 조건과 동일한 기준으로 정렬이 되어잇는 row들을 가져오게 되기 때문에, 정렬의 필요성이 아예 사라진다.

 

그런데 나의 경우에는 number로 한 번 걸렀을 때 가져오게 되는 row의 양이 많지 않아서 정렬의 부담이 적을 것으로 판단되었고, 실제로는 존재하지 않을 극단적인 케이스(10000개)로 mock 데이터를 만들어서 테스트해보니 크게 문제가 없었다. 따라서 아직은 인덱스를 추가하는 것의 장점이 크지 않기 때문에 추가하지 않기로 하였고, 실제로 서비스를 운영하면서 문제가 발생하면 인덱스를 추가하기로 하였다. (정렬이 부담스러워지는 수치가 어느 정도부터인지 궁금하다.)

 

파티션 활용하기

내가 원래 작성했던 쿼리는 다음과 같다.

select * from (
	select 
    a.id as a_id,
    a.name as a_name,
    b.name as b_name,
    c.name as c_name
    from A a
    left outer join B b on a.id = b.a_key
    left outer join C c on a.id = c.a_key
    where a.type =  'REGULAR'
    and a.id BETWEEN 220401XXX AND 220417XXX
    order by a.id
)
where rownum <= 100;

참고로 A, B, C 테이블 모두 운영 환경에서 데이터가 매우 많으며, 매일매일 계속해서 쌓이는 데이터이다. 이런 특성 때문에 A, B, C에는 월별로 partition이 생성되어 있었다. A.id, B.id, C.id 모두 YYYYMMDD + random number의 조합으로 생성하기 때문에, partition은 각 테이블의 id 컬럼을 기준으로 생성되어 있었다.

 

이 before 쿼리에 대해서 실행 계획을 뽑아보니, 조건에 맞는 B, C를 찾는 부분에서 성능이 매우 안 좋게 나왔다. 보다시피 before 쿼리는 A 테이블에 대해서는 id 컬럼을 활용하고 있지만, B.id, C.id는 사용되지 않고 있다. B, C 테이블에서 a_key에 대한 인덱스가 이미 생성되어 있기 때문에 문제가 없을 것이라고 예상했는데, 실제로는 B, C의 모든 파티션에 대해서 a_key를 만족하는 row를 다 조회하다 보니 워낙 데이터가 많아서 시간이 오래 걸리는 것 같았다.

 

그래서 다음과 같이 B.id, C.id에 대한 조건을 추가하여 범위를 좁힐 수 있도록 하였다.

select * from (
	select 
    a.id as a_id,
    a.name as a_name,
    b.name as b_name,
    c.name as c_name
    from A a
    left outer join B b on a.id = b.a_key
    where b.id between 220401000 AND 220430999 -- 추가
    left outer join C c on a.id = c.a_key    
    where c.id between 220401000 AND 220430999 -- 추가
    where a.type =  'REGULAR'
    and a.id BETWEEN 220401XXX AND 220417XXX
    order by a.id
)
where rownum <= 100;

이렇게 했더니 실행 계획의 cost가 1/70로 줄어들었다. (잘은 모르지만 실행계획의 cost라는 수치가 100% 신뢰할 만한 것은 아니고 몇 가지 함정이 있는 것 같긴 한데, 어쨌든 이 정도면 유의미한 변화인 것 같다.)

  

 

조인 시 결과 집합을 줄이기

사실 이것은 너무 당연한 이야기인데, 테이블을 조인할 때 where을 통해서 걸러낸 집합이 최대한 적어야 성능이 좋아진다. 

SELECT *
FROM A a, B b
WHERE a.c_key = b.c_key
AND a.height >= 150
AND b.age >= 10

설명을 위해 엉터리 쿼리를 하나 만들었다. 가장 많이 사용하고 익숙한 개념인 NL(Nested Loop) 조인을 예로 들어서 설명하겠다. DB의 optimizer은 테이블을 조인할 때 where을 통해서 뽑아낸 결과 집합이 가장 적은 테이블을 driving table로 사용한다. 만약 A를 driving table로 판단했다면, A에 먼저 접근해서 a.height >= 150인 row에 대해서 B 테이블과 조인해서 b.age >= 10로 최종 결과를 걸러내는 것이다.

 

그런데 내 경우에는 A, B 중에 어떤 것이 집합이 더 적을지, 릴리즈 이전의 시점에는 판단하기 어려웠다. 그래서 실제로 최적화는 DB가 알아서 해줄 것이라, 우리는 A와 B에 대한 where을 수정해서 좀 더 집합을 최대한 줄여주기로 하였다. 결과 집합을 좁히기 위해서는 애플리케이션 로직 측면에서 검토가 필요했다. 내가 더 좁힐 수 있는데 너무 널널하게 where을 작성했다는 것을 알게 되었다. 

 

(또한, 어떤 것이든 driven table이 되었을 때 문제가 생기지 않도록 둘의 연결고리인 c_key에 대한 인덱스도 추가해주기로 하였다. 예를 들어 A가 driving table이 되었고 해당 row에 맞는 데이터를 B에서 조회하는데 b.c_key에 인덱스가 없다면 table full scan을 하게 되어 성능이 매우 나빠진다.)