#052. 쿼리 개선: subquery가 원하는 인덱스를 타지 않는 경우 🚗

문제 상황

레거시 코드를 새로운 코드로 옮기는 작업을 하면서, 기존 쿼리의 효율성을 파악하기 위해 실행계획을 찍어보는데 독특한 상황이 있었다.
쿼리1 은 IN 절로 subquery를 사용하고 있고

-- 쿼리1:
SELECT "id"
FROM "courses"
WHERE (("status" = 'publish' AND "published_at" < now() AND "deleted_at" IS NULL) AND
       "exposure" = true AND
       "id" IN (SELECT "course_id"
                FROM "instructors"
                WHERE "user_id" = 743661234
                  AND "type" = 'main'));

쿼리 2는 subquery를 실행시킨 결과를 사용해 쿼리를 실행한다.

-- 쿼리2:
SELECT "id"
FROM "courses"
WHERE (("status" = 'publish' AND "published_at" < now() AND "deleted_at" IS NULL) AND
       "exposure" = true AND
       "id" IN (12325355,
                12325384,
                12325382,
                12325383));

subquery의 결과는 4개였고, 실행한 결과는 같지만 실행 계획을 살펴보면 course 테이블에서 사용하는 인덱스가 서로 다른 것을 확인할 수 있다.
쿼리 1(서브쿼리 O)에서는 pk를 인덱스로 사용하고 있는 반면 쿼리2(상수)에서는 의도한대로 course_id_published_at ... index를 사용하고 있다.

쿼리1: https://explain.dalibo.com/plan/986acb291bdc4bf6

 
쿼리2: https://explain.dalibo.com/plan/1ae8d6401b404af7

 

해결

subquery의 결과 갯수에 따라, 또 결과를 직접 적어주는 지에 따라 옵티마이저의 선택이 달라졌다.

(1) subquery의 결과가 큰 경우: 약 700개 이상이었던 케이스
옵티마이저가 조인문을 사용하는 경우 "Hash Cond", "Merge Cond" 또는 "Join Filter"와 같은 조인 조건이 포함된 줄이 생긴다. subquery의 결과 나오는 데이터가 큰 경우(여기서는 700개 이상) 아래와 같이 Hash Cond를 사용한 JOIN문이 된다. 다시 말해, 옵티마이저에 의해 조인문을 사용하며 course 테이블을 드라이빙 테이블로 선택해 우리가 원하는 인덱스(courses_id_published_at_status index)를 사용하는 것을 확인할 수 있다.

 
(2) subquery의 결과가 5~700
subquery의 결과가 작아지면서 조인을 사용하지 않고 직접 Nested Loop을 돌면서 데이터를 비교하는 것을 확인할 수 있다. 조인문이 아니므로 드라이빙 드리븐 테이블의 개념이 없어진다. course테이블 조회에 의도한 인덱스를 사용한다.

(3) subquery의 결과가 0~4
이또한 조인을 사용하지 않고 Nested Loop을 사용한다. 다만 갯수가 너무 적어서인지 pk를 이용해 테이블을 조회했다.

 

결론

IN절에 subquery를 사용하면 subquery 결과에 따라 최적화가 달라진다. 하지만 subquery의 데이터가 커지면서 subquery문은 JOIN문으로 바뀌며 의도한 인덱스를 사용하게 되므로 결과적으로는 성능상의 큰 차이는 없을 것이라고 생각한다. 반면 IN절에 상수를 넣는 경우 이러한 옵티마이저의 최적화 작업이 사라진다. 이로인해 IN절에 엄청나게 큰 데이터가 들어온다면 슬로우쿼리가 될 가능성이 있다.
따라서 이 쿼리는 JOIN문으로 수정하는 것이 가장 적합하다. 

참고

http://jason-heo.github.io/mysql/2014/05/22/avoid-mysql-in.html