[MySQL] 성능 개선: 서브쿼리를 JOIN으로 튜닝하기

문제 및 원인 분석

특정 MySQL 쿼리에서 최소 30초 이상, 최악의 경우 timeout까지 걸리는 경우가 발견됐다.
해당 쿼리는 서브쿼리 내에서 COUNT를 수행하고 있었는데, 실행계획을 확인해보니 서브쿼리가 테이블 FULL SCAN을 하는 것을 확인할 수 있었다.

• 기존 쿼리

  SELECT
      *, 
      (SELECT COUNT(column1) 
       FROM a a2
       WHERE a1.column1 = a2.column1
         AND a2.column3 = 'N') AS column3_cnt
  FROM (
      SELECT
          column1,
          column2,
      FROM a
      WHERE column3 = 1
        AND column4 IN (
            SELECT c.id FROM c WHERE c_column2 = 1
        )
        AND column1 IN (1, ...생략)
      GROUP BY column1
  ) AS a1;

• 기존 쿼리의 실행계획

  id	select_type	table	type	key	key_len	rows	filtered	Extra
  1	PRIMARY	<derived3>	ALL				7	100.0
  3	DERIVED	a	ref	PRIMARY	4	284	50.0	Using where; Using temporary
  3	DERIVED	c	eq_ref	PRIMARY	4	1	5.0	Using where
  2	DEPENDENT SUBQUERY	a2	ALL			1695808	1.0	Using where

• DEPENDENT SUBQUERY: 외부 쿼리의 결과에 의존하여 실행되는 서브 쿼리.
  해당 쿼리의 rows가 160만 건이 넘는 것을 봐서는 외부 쿼리가 한 행씩 실행될 때마다 160만 건의 모든 데이터를 읽는다는 뜻이므로 성능 부하가 예상된다. 또한, type이 ALL이고 Extra가 Using where이므로 full scan을 통해 where 절의 조건이 맞는 레코드를 찾는다.

  ❓ Extra가 Using where인 경우, 항상 Full scan을 하는 걸까?
  ❗ type이 const, eq_ref, ref, range인 경우는 인덱스를 사용하면서 where절을 필터링한다.
  - const: 단일 테이블에서 primary key, unique key로 레코드가 단 1건만 반한되는 경우
  - eq_ref: join 시에 다른 테이블의 primary key, unique key를 사용해서 현재 테이블에서 단 1건만 반환되는 경우.
  - ref: primary key, unique key가 아니라 인덱스에 해당하는 키를 사용해 값을 찾지만 레코드가 여러 개 반환되는 경우.
  - range: 인덱스를 사용하되 범위를 스캔하는 경우.

해결

이 DEPENDENT SUBQUERY를 제거하기 위해 외부 쿼리를 의존하는 서브쿼리의 WHERE 절을 삭제하고, 외부 쿼리에 LEFT JOIN을 하는 방식으로 변경했다.

❓ 서브쿼리는 항상 JOIN으로 변경하는 게 좋을까?
❗ 항상 그런 것은 아니다. 하지만 현재 다루고 있는 쿼리에서는 SELECT 절에 서브쿼리가 쓰여 FROM 절의 DERIVED 테이블이 실행될 때마다 매번 실행되는 것이 문제라서 JOIN으로 튜닝하는 게 좋다.

• 개선 후 쿼리

  SELECT
      a.column1,
      a.column3,
      COUNT(CASE WHEN a1.column3 = 'N' THEN a1.column1 ELSE NULL END) AS column3_cnt
  FROM (
      기존과 동일
  ) AS a1;
  LEFT JOIN a2
      ON a1.column1 = a2.column1
  GROUP BY a1.column1, a1.column3

• 개선 후 실행계획

  id	select_type	table	type	key	key_len	ref	rows	filtered	Extra
  1	PRIMARY	<derived2>	ALL				7	100.0	Using temporary
  1	PRIMARY	a2	ALL				1712986	100.0	Using where; Using join buffer (hash join)
  2	DERIVED	a	ref	PRIMARY	4	const	284	50.0	Using where; Using temporary
  2	DERIVED	c	eq_ref	PRIMARY,c.id	4	column4	1	5.0	Using where

개선 후 실행 시간은 1초 이내로 개선됐다. 여전히 a2 테이블 쿼리 실행 시 full scan이 실행되고 있어, 이 문제를 해결하려면 복합 인덱스 설정이 필요해보인다. 복합 인덱스의 대상이 되는 컬럼의 cardinality를 조사해봤더니 전체 컬럼에 대해 매우 낮은 수치였다. 일단 보류하고 추가 성능 개선이 필요한 경우 진행하려고 한다.