[MySQL] 파생 테이블 조건 푸시다운 확인하기: 외부 WHERE가 내려가지 않는 경우

728x90

데이터베이스를 다루다 보면 파생 테이블(Derived Table)에서 많은 행을 만든 뒤, 외부 쿼리의 WHERE 절로 극히 일부만 남기는 SQL을 만나게 됩니다. 이때 가장 흔하게 기대하는 최적화가 바로 파생 조건 푸시다운(Derived Condition Pushdown)입니다.

파생 조건 푸시다운은 외부 조건을 파생 테이블 내부로 옮겨 불필요한 중간 결과를 줄이는 최적화입니다. 하지만 모든 조건이 내려가는 것은 아닙니다. LIMIT처럼 결과의 의미가 달라질 수 있는 연산이 있거나, 조건 자체에 서브쿼리가 포함되거나, 윈도 함수 결과를 필터링하는 경우에는 적용되지 않거나 일부 조건만 내려갑니다. 이번 글에서는 외부 WHERE 조건이 내려가지 않는 경우를 중심으로 기본 동작, 실행 계획 확인법, 버전별 검증 기준까지 깊이 있게 다루어 보겠습니다.

1. 파생 조건 푸시다운의 기본 동작

다음 SQL은 파생 테이블에서 주문 데이터를 읽은 뒤 외부에서 특정 고객만 선택합니다.

SELECT COUNT(*)
FROM (
    SELECT order_id, customer_id, amount
    FROM dcp_orders
) AS dt
WHERE customer_id = 42;

파생 테이블 병합(Derived Table Merge)이 가능하면 옵티마이저가 파생 테이블 자체를 없앨 수 있으므로, 이 SQL만 보고 조건 푸시다운의 효과를 판단하면 안 됩니다. 두 최적화는 결과적으로 비슷한 실행 계획을 만들 수 있지만 동작 원리가 다르기 때문입니다.

푸시다운만 검증하려면 derived_merge=off 또는 적절한 NO_MERGE 힌트로 파생 테이블을 물질화(Materialization)한 뒤, derived_condition_pushdown을 켠 경우와 끈 경우를 비교해야 합니다.

SET SESSION optimizer_switch =
'derived_merge=off,derived_condition_pushdown=on';

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM (
    SELECT order_id, customer_id, amount
    FROM dcp_orders
) AS dt
WHERE customer_id = 42;

푸시다운이 적용되면 논리적으로 다음과 비슷한 형태로 처리됩니다.

SELECT COUNT(*)
FROM (
    SELECT order_id, customer_id, amount
    FROM dcp_orders
    WHERE customer_id = 42
) AS dt;

가장 중요한 점은 SQL 문자열이 실제로 다시 작성되어 보이는 것이 아니라, 옵티마이저가 조건을 더 안쪽 쿼리 블록에 배치한다는 점입니다. 따라서 단순 실행 시간보다 실행 계획의 조건 위치와 실제 처리 행 수를 함께 확인해야 합니다.

2. 외부 WHERE 조건이 내려가지 않는 대표적인 경우

구조	푸시다운 판단	이유 또는 확인 지점	실행 시간
일반 조건 DCP ON	적용 가능	외부 조건이 내부 스캔 조건으로 이동하는지 확인	0.8 ms
일반 조건 DCP OFF	적용 안 함	동일 SQL의 기준선이며 물질화 후 외부에서 필터링	113 ms
LIMIT 포함	적용 불가	필터 위치가 바뀌면 상위 N개라는 결과 의미가 달라짐	55.6 ms
외부 조건에 서브쿼리 포함	적용 불가	서브쿼리를 포함한 조건은 푸시다운 대상에서 제외되는지 확인	37.3 ms
윈도 파티션 조건	제한적 적용 가능	윈도 함수의 PARTITION BY 열에 대한 조건 위치 확인	0.5 ms
윈도 결과 조건	적용 불가	ROW_NUMBER 계산 후에만 평가할 수 있는 조건	69.2 ms

2.1 LIMIT이 있는 파생 테이블

SELECT COUNT(*)
FROM (
    SELECT order_id, customer_id, amount
    FROM dcp_orders
    ORDER BY order_id
    LIMIT 100000
) AS dt
WHERE customer_id = 42;

이 경우 외부 조건을 LIMIT 아래로 내리면 결과가 달라질 수 있습니다. 원래 SQL은 먼저 order_id 순서의 100,000건을 선택한 다음 고객 42의 행을 찾습니다. 반면 조건을 내부로 내리면 고객 42의 주문만 모은 뒤 그중 100,000건을 선택하게 됩니다. 두 연산은 동등하지 않으므로 옵티마이저가 임의로 조건을 이동할 수 없습니다.

흔히 하는 실수는 “조건이 선택적이면 항상 먼저 실행하는 것이 빠르다”라고 판단하는 것입니다. 옵티마이저 최적화는 성능뿐 아니라 결과의 의미를 보존해야 하므로, LIMIT이 보이면 먼저 연산 순서가 바뀌어도 같은 결과인지 확인해야 합니다.

2.2 외부 조건 자체에 서브쿼리가 포함된 경우

SELECT COUNT(*)
FROM (
    SELECT order_id, customer_id, amount
    FROM dcp_orders
) AS dt
WHERE amount > (
    SELECT AVG(amount)
    FROM dcp_orders
    WHERE customer_id = 7
);

외부 조건에 서브쿼리가 들어 있으면 해당 조건은 파생 테이블 안으로 푸시다운되지 않는 제한을 확인해야 합니다. 이 경우 평균을 먼저 별도 쿼리로 구하거나, 조인 가능한 집계 결과로 재구성하는 방법을 검토할 수 있습니다. 하지만 쿼리를 변경할 때는 서브쿼리가 한 번 평가되는지, 상관 서브쿼리처럼 외부 행마다 달라지는지도 구분해야 합니다.

2.3 윈도 함수가 있는 경우

SELECT COUNT(*)
FROM (
    SELECT order_id,
           customer_id,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_id DESC
           ) AS rn
    FROM dcp_orders
) AS dt
WHERE customer_id = 42
  AND rn <= 3;

여기에는 성격이 다른 조건이 두 개 있습니다. customer_id = 42는 윈도 함수의 PARTITION BY 열만 제한하므로 해당 파티션을 선택하는 조건으로 내려갈 여지가 있습니다. 반면 rn <= 3은 ROW_NUMBER() 계산이 끝나야 평가할 수 있으므로 윈도 연산 아래로 내릴 수 없습니다.

따라서 윈도 함수가 있다는 이유만으로 모든 조건이 차단된다고 판단해서도 안 되고, 외부 조건 전체가 내려간다고 판단해서도 안 됩니다. 복합 조건을 원자적인 조건식으로 나누고 각 조건이 어느 연산 단계에서 계산 가능한지 살펴보는 것이 더 안전합니다.

2.4 외부 조인과 의미 보존 문제

파생 테이블이 외부 조인(Outer Join)의 NULL 보존 측에 참여하면 조건 이동으로 행의 보존 여부가 달라질 수 있습니다. 예를 들어 LEFT JOIN의 오른쪽 파생 테이블에 관한 조건이 ON 절에 있는지 외부 WHERE 절에 있는지에 따라 결과가 달라집니다. 옵티마이저는 이런 조건을 단순히 안쪽으로 이동할 수 없습니다.

이 경우 먼저 조건이 조인 일치 여부만 제한하는지, 조인 후 생성된 NULL 확장 행까지 제거하는지 확인해야 합니다. WHERE dt.column = 값과 ON dt.column = 값은 문법적으로 비슷해 보여도 외부 조인에서는 같은 의미가 아닙니다.

3. GROUP BY 조건은 WHERE와 HAVING으로 나뉩니다

집계 파생 테이블에서는 외부 조건의 종류에 따라 이동 위치가 달라질 수 있습니다.

SELECT customer_id, total_amount
FROM (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM dcp_orders
    GROUP BY customer_id
) AS dt
WHERE customer_id = 42
  AND total_amount > 100000;

customer_id = 42는 그룹화 열에 대한 조건이므로 내부 WHERE 절에 배치할 수 있습니다. 반면 집계 결과 별칭인 total_amount는 집계가 끝나야 계산되므로 내부 HAVING에 해당합니다. 즉, 푸시다운은 무조건 내부 WHERE로 옮기는 기능이 아니라 의미를 유지할 수 있는 내부 위치로 조건을 배치하는 최적화입니다.

집계 쿼리에서 Using temporary가 보인다면 조건 이동과 임시 결과 규모를 함께 확인해야 합니다. 관련 진단 절차는 Using temporary 진단 체크리스트에서 이어서 살펴볼 수 있습니다.

4. 실행 계획에서 확인해야 할 항목

파생 테이블 병합을 통제합니다. derived_merge=off로 고정하지 않으면 병합 효과를 조건 푸시다운 효과로 잘못 해석할 수 있습니다.
DCP ON과 OFF를 같은 세션 조건에서 비교합니다. 데이터, 통계, 버퍼 상태와 SQL을 동일하게 유지하고 derived_condition_pushdown만 변경합니다.
EXPLAIN ANALYZE의 실제 행 수를 확인합니다. 파생 테이블을 구성하는 내부 스캔의 실제 출력 행과 외부 필터로 전달된 행을 비교합니다.
EXPLAIN FORMAT=JSON으로 조건 위치를 확인합니다. attached_condition이 어느 쿼리 블록과 테이블에 연결됐는지 살펴봅니다.
결과 집합이 같은지 검증합니다. 최적화 스위치를 바꾼 두 실행의 건수와 결과가 같아야 올바른 비교입니다.

JSON 실행 계획의 attached_condition과 비용 정보를 읽는 방법은 EXPLAIN FORMAT=JSON 해석법을 함께 참고하면 좋습니다. 다만 추정 비용만으로 결론을 내리지 말고 실제 처리 행을 우선해야 합니다. 실행 계획의 type 역시 단독 성능 지표가 아니므로 range, ref, eq_ref 비교 글의 판단 기준처럼 접근 행 수와 필터 비율을 함께 확인해야 합니다.

5. 버전별 동작 차이를 검증하는 방법

파생 조건 푸시다운은 MySQL 8.0 계열 안에서도 적용 범위가 확장되어 왔으므로 “MySQL 8.0에서 된다”라는 문장만으로는 충분하지 않습니다. 운영 버전과 검증 버전의 정확한 패치 번호를 기록하고 같은 데이터와 SQL을 각각 실행해야 합니다.

SELECT VERSION();으로 정확한 서버 버전을 기록합니다.
각 서버에서 테이블 정의, 데이터 분포, 통계 정보를 동일하게 맞춥니다.
derived_merge=off로 병합을 차단한 뒤 DCP ON과 OFF를 각각 실행합니다.
EXPLAIN FORMAT=JSON과 EXPLAIN ANALYZE 결과를 모두 보관합니다.
내부 스캔 행, 물질화 결과 행, 최종 결과 행과 실행 시간을 표에 입력합니다.

특히 UNION, 공통 테이블 표현식(CTE), 윈도 함수가 포함된 쿼리는 패치 버전에 따른 적용 범위를 공식 매뉴얼과 해당 서버의 실행 계획으로 다시 확인해야 합니다. 한 버전의 결과를 다른 8.0 패치 버전이나 8.4의 동작으로 일반화하면 안 됩니다.

6. 실무 판단 기준 정리

단순 열 조건이라도 파생 테이블이 병합되었다면 DCP 자체를 측정한 것이 아닙니다.
LIMIT처럼 조건 이동이 결과 행의 의미를 바꾸는 연산이 있으면 푸시다운되지 않습니다.
서브쿼리를 포함한 외부 조건은 적용 제한을 실행 계획에서 확인해야 합니다.
윈도 함수 조건은 파티션 열 조건과 윈도 결과 조건을 분리해서 판단해야 합니다.
집계 쿼리에서는 일반 열 조건과 집계 결과 조건이 각각 내부 WHERE와 HAVING으로 이동할 수 있습니다.
외부 조인에서는 조건 위치가 NULL 보존 의미를 바꾸므로 주의가 필요합니다.

결론적으로 외부 WHERE 조건이 내려가지 않는 핵심 이유는 조건을 이동했을 때 결과 의미를 보존할 수 없거나, 해당 쿼리 구조가 옵티마이저의 적용 범위를 벗어나기 때문입니다. 먼저 병합과 푸시다운을 분리해 검증하고, 조건별 계산 가능 시점과 실제 중간 처리 행을 확인하면 불필요한 파생 결과를 더 안전하게 줄일 수 있습니다.

측정 환경

이 글의 측정값은 로컬 개발 환경에서 직접 실행한 결과입니다. MySQL 8.4.3, InnoDB 버퍼 풀 128MB, 정렬 버퍼 256KB, 각 쿼리 3회 실행 후 중앙값입니다. 운영 서버의 사양과 데이터 분포에 따라 값은 달라집니다.