Nested subquery가 Correlated subquery처럼 동작하는 경우에 대하여

개발팀의 쿼리 검수요청을 처리하던 중 Nested subquery가 Correlated subquery처럼 동작하는 케이스를 발견하여 알아보도록 하자.
해당 DB는 MySQL 5.6에서 실험하였다

우선 간단하게 서브쿼리에 대하여 짚고 넘어가자.

Nested subquery(중첩 서브쿼리)

# 이너 쿼리를 먼저 실행
# 이너 쿼리의 결과를 통해 아우터 쿼리 조건 검색

SELECT SUM (Sales) FROM Store_Information
WHERE Store_Name IN
  ( SELECT Store_Name FROM Geography
    WHERE Region_Name = 'West');

Correlated subquery(상관 서브쿼리)

# 아우터 쿼리를 먼저 실행
# 접근한 로우의 컬럼 값을 이용해 이너 쿼리 실행 
# 아우터 쿼리의 로우가 끝날 때 까지 반복 

SELECT SUM (a1.Sales) FROM Store_Information a1
WHERE a1.Store_Name IN
  ( SELECT Store_Name FROM Geography a2
    WHERE a2.Store_Name = a1.Store_Name);

이제부터 검수 요청 받은 쿼리와 테이블에 대하여 알아보고 분석을 해보자

검수 쿼리

# Table
CREATE TABLE `table_aaa` (
  `transactionSeq` bigint(20) NOT NULL,
  `userId` varchar(100) NOT NULL,
  .
  .
  .
  `regDt` datetime NOT NULL,
  PRIMARY KEY (`transactionSeq`,`regDt`),
  KEY `TABLE_AAA_IDX1` (`regDt`,`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

CREATE TABLE `table_bbb` (
  `userId` varchar(16) NOT NULL,
  .
  .
  .
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


# Query
SELECT
    ...
FROM
    table_aaa
WHERE
    regDt >= '2020-01-01'
AND
    regDt < '2020-02-01'
AND
    userId IN (
        SELECT
            userId
        FROM
            table_bbb
        WHERE
            regDt >= '2020-01-01'
        AND
            regDt < '2020-02-01'
    )
GROUP BY
    ...;

쿼리를 보면 중첩 서브쿼리라고 생각이 들겠지만, 실제로 실행계획을 살펴보니 그게 아니었다.

# EXPLAIN
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_aaa
         type: range
possible_keys: TABLE_AAA_IDX1
          key: TABLE_AAA_IDX1
      key_len: 5
          ref: NULL
         rows: 39124
        Extra: Using index condition; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_bbb
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 50
          ref: test_db.test_aaa.userId
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

ERROR:
No query specified

분석

1. TABLE_AAA_IDX1을 이용하여 table_aaa.regDt 검색
2. TABLE_AAA_IDX1 인덱스가 (regDt, userId) 복합키로 설정되어 있으므로, 인덱스 페이지에서 조건에 맞는 로우의 userId를 이용하여 table_bbb의 PK인 userId를 검색
3. PK를 통해 table_bbb의 로우에 접근 후 추가적으로 regDt 조건 필터

이너쿼리 조건절에 table_bbb.userId = table_aaa.userId가 기재되어 있는 것 같은 동작을 하고 있으므로,
옵티마이저가 중첩 서브쿼리를 채용한 것으로 보인다