쿼리 속도 개선기

쿼리 속도 개선

업무중 엄청 느린 쿼리들이 여럿 있었다.

그냥 단순히 select * from 으로 모든 칼럼을 조회하는 것이 아니라

조건에 맞는 데이터를 뽑는 로직들이 있었는데

일단 복합키로 PK가 잡혀있는 상황이고, 세개의 복합키들 중에 두개는 중복되는게 많았다.

다시 말해 카디널리티가 낮은 것 들이었다.

JPA를 사용해서 더럽게 뽑아온 다음에 가공하려고 했었지만, 데이터가 많아서 그것조차 조금 어려운 상황이었다.

계속 똑같은 쿼리문을 반복적으로 실행시키니까 처음 사용자입장에선 빨리 받아오는 것처럼 느낄지 모르겠다 (근데 이것도 느리다.)

하지만 맨 뒤쪽에 와서 이 쿼리를 실행하게 된 사람은 앞 사람들이 요청한 그 트랜잭션들이

처리된 후에나 실행이 되기 때문에 쿼리가 1초이고 누른 사람이 10명이라면

마지막 사람은 10초보다 그 이상이 걸려서 데이터를 받아볼 수 있게 된다.

이게 얼마나 흉측한 상황인지 10명만 해도 이정도로 지연된다는 것 자체가 웃기다.

개선 방법

우선은 DB 실행계획을 보았다.

explain select ...

이 구문으로 실행계획을 찾아봤다.

나는 인덱스를 생성하는 방식으로 쿼리 개선을 진행하였다.

블로그 상에 있던 규칙을 가져왔다.

쿼리 최적화를 위한 7가지 체크리스트 에서 내용을 좀 추려봤다.

요약해보자면

SELECT시에는 꼭 필요한 칼럼만 불러와야 한다.

(결국 조건에 맞는 데이터를 줄 단위로 가져온 후 거기서 추출하고 싶은 각 칼럼을 도출해 내는것이니까 메모리적으로는 이득일 수 있다.)

1. 조건을 부여할 시에는 가급적이면 DB에 별도의 연산을 끼워넣지 않는다.
2. LIKE 사용 시, 와일드카드 문자열(%) 을 String 앞부분에 배치하지 않는 것이 좋다.
3. SELECT DISTINCT, UNION DISTINCT 같은 중복 값을 제거하는 연산은 최대한 사용하지 않아야 한다.
4. 쿼리 실행 순서는 WHERE절이 HAVING절보다 먼저 실행된다. WHERE절로 데이터를 작게 가공해둔다면 효율적인 연산이 가능하다.
5. 3개 이상의 테이블을 JOIN할 경우에 크기가 가장 큰 테이블을 FROM절에 배치하고 JOIN절에는 남은 테이블을 작은 순서대로 배치하는 것이 좋다.
6. 자주 사용하는 데이터의 형식에 대해서는 미리 전처리된 테이블을 따로 보관 or 관리하는 것도 좋다.

일단 이중에 해당하는 사항은 1, 2번을 사용한 쿼리인데
1번은 바람직하게 잘 실행했다.

하지만 2번에서 WHERE조건이 조금 복잡하게 구성이 되어있었다.

애시당초에 조회속도가 그냥 느려서 조건을 검색하여도 느렸다.

그러니까 복합키 중에 두 키가 같은 값으로 쭉 정제가 되어있었기 때문에

카디널리티가 높은 (그러니까 중복 값이 적은) 칼럼을 인덱스를 잡아주어 스캔을 하도록 유도하려고 했다.

실행 계획

실행 계획에는 위와 같이 구성이 되어있다.

실행 계획에 대한 설명이 필요하다면 여기에서 확인 가능하다.

해결

그래서 나는 카디널리티가 높은 칼럼을 찾아야 했는데 복합키중 1개면서 동시에 유니크한 그런 데이터 칼럼이 바로 날짜였기 때문에 이 칼럼에 인덱스를 부여해주었다.

CREATE INDEX 해당칼럼별명
on 테이블 (인덱스칼럼);

부여해준 후

그렇게 해서 쿼리 테스트를 진행했다.

결과는 아래와 같다.

개선 전

개선 후

시간을 정말 많이 줄이게 되었다.