전체 글

[MySQL] CTE 병합(Derived Merge)과 구체화(Materialization) 조건 및 성능 영향

CTE(Common Table Expression): 실무적 정의와 운영상 중요성MySQL에서 CTE는 WITH 절로 정의해 이후 쿼리 본문에서 이름으로 참조하는 임시 결과 집합이다. 표면적으로는 서브쿼리를 밖으로 빼내 가독성을 높이는 문법적 장치처럼 보이지만, 옵티마이저 내부 처리 방식은 서브쿼리와 동일하지 않다. MySUBQL 옵티마이저는 CTE를 두 가지 방식 중 하나로 처리한다. 하나는 CTE 정의를 참조 지점에 그대로 풀어 넣는 파생 테이블 병합(Derived Table Merge)이고, 다른 하나는 CTE 결과를 먼저 임시 테이블에 저장한 뒤 그 임시 테이블을 조회하는 구체화(Materialization)이다.이 차이는 단순한 내부 구현 디테일이 아니라 실행 계획 전체를 바꾼다. 병합이 일어나..

[MSSQL] Scalar Subquery 반복 실행을 APPLY 또는 사전 집계로 줄이는 방법

Scalar Subquery 반복 실행: 실무적 정의와 운영상 중요성MSSQL에서 Scalar Subquery는 SELECT 절 안에서 정확히 하나의 값을 반환하도록 작성된 서브쿼리이다. 대부분 외부 쿼리의 컬럼을 조건절에서 참조하는 상관 서브쿼리(Correlated Subquery) 형태로 작성되며, 이 경우 옵티마이저는 실행 계획을 Nested Loop Join 형태로 구성한다. 외부 쿼리가 한 행을 반환할 때마다 서브쿼리가 한 번씩 실행되는 구조이기 때문에, 외부 쿼리의 결과 건수가 곧 서브쿼리의 실행 횟수가 된다. 결과 건수가 수십 건 수준이면 체감되지 않지만, 외부 쿼리가 수만~수백만 행을 반환하는 리포트나 배치 쿼리에서는 서브쿼리 실행 횟수가 그대로 곱해져 CPU 사용량과 논리 읽기(Logic..

[MySQL] 상관 서브쿼리의 반복 실행을 제거하는 윈도 함수 재작성

상관 서브쿼리(Correlated Subquery)와 윈도 함수(Window Function): 실무적 정의와 운영상 중요성MySQL에서 상관 서브쿼리는 외부 쿼리의 각 행(row)마다 내부 쿼리가 다시 평가되는 서브쿼리이다. 내부 쿼리가 외부 쿼리의 컬럼을 참조하기 때문에 옵티마이저는 이를 독립적으로 최적화할 수 없고, 외부 결과 집합의 행 수만큼 그대로 반복 수행한다.운영 환경에서는 이 반복 실행이 곧 반복적인 인덱스 탐색(Index Lookup) 또는 반복적인 정렬로 이어진다. 외부 행이 수만 건을 넘어가면 내부 쿼리도 수만 번 실행되며, 이는 CPU 사용률 급증과 응답 시간 저하를 유발한다. 반면 윈도 함수는 PARTITION BY로 정의된 그룹 단위로 단 한 번의 스캔과 정렬만으로 동일한 결과를..

[MSSQL] NOT IN과 NOT EXISTS의 NULL 함정: Anti Semi Join 실행 계획 완전 분석

NOT IN과 NOT EXISTS: 실무적 정의와 운영상 중요성MSSQL에서 NOT IN과 NOT EXISTS는 모두 "특정 조건을 만족하지 않는 행을 찾는다"는 목적으로 사용되는 안티 조인(Anti Join) 계열 연산이다. 옵티마이저는 두 구문을 내부적으로 Anti Semi Join 계열 실행 계획(Nested Loops (Left Anti Semi Join), Hash Match (Right Anti Semi Join))으로 변환하지만, 두 구문의 논리적 동치성은 서브쿼리 컬럼에 NULL이 존재하지 않을 때만 성립한다. 서브쿼리 컬럼이 NULL을 허용하는 순간 NOT IN은 3값 논리(Three-Valued Logic)에 의해 예상과 전혀 다른 결과, 극단적으로는 빈 결과 집합을 반환한다. 이 함정..

[MySQL] EXISTS와 IN 서브쿼리의 세미조인 변환 전략과 NULL 처리 차이

EXISTS와 IN: 실무적 정의와 운영상 중요성MySQL에서 IN 서브쿼리와 EXISTS 서브쿼리는 모두 특정 값이 다른 테이블에 존재하는지를 확인할 때 사용하는 구문이다. IN은 서브쿼리가 반환하는 값 목록과 외부 컬럼 값을 비교하고, EXISTS는 서브쿼리가 한 건이라도 행을 반환하는지 여부만 확인한다. 두 구문은 표면적으로 다른 문법이지만, MySQL 옵티마이저는 내부적으로 이를 동일한 세미조인(Semi-Join) 실행 계획으로 변환하는 경우가 많다.운영 환경에서는 두 구문이 항상 같은 결과를 낸다고 가정하는 실수가 자주 발생한다. 특히 서브쿼리 결과 컬럼에 NULL이 하나라도 포함되면 IN과 EXISTS, 그리고 이들의 부정형인 NOT IN과 NOT EXISTS의 결과가 서로 달라진다. 이 차이..

[MSSQL] DISTINCT가 감추는 Sort 비용과 근본 원인 제거 절차

DISTINCT: 실무적 정의와 운영상 중요성MSSQL에서 DISTINCT는 SELECT 결과 집합에서 중복된 행을 제거하는 키워드이다. 옵티마이저는 DISTINCT를 처리할 때 결과 행 전체를 정렬해 인접한 중복 행을 비교하는 Sort 연산자를 사용하거나, 행을 해시 테이블에 적재해 중복 여부를 판단하는 Hash Match (Aggregate) 연산자를 사용한다. 두 방식 모두 결과 집합 전체를 메모리 또는 TempDB에 올려야 하므로 원본 쿼리에는 없던 연산 비용이 추가로 발생한다.실무에서는 DISTINCT가 정당한 비즈니스 요구가 아니라 JOIN 조건 오류나 데이터 모델의 정합성 결함을 감추는 용도로 남용되는 경우가 많다. 원인을 찾지 않고 DISTINCT로 증상만 없애면 쿼리는 정상 동작하는 것처..

[MySQL] COUNT(DISTINCT) 임시 테이블 비용과 사전 집계 최적화 기준

COUNT(DISTINCT): 실무적 정의와 운영상 중요성MySQL에서 COUNT(DISTINCT expression)은 조건을 만족하는 행에서 중복되지 않은 값의 개수를 반환하는 집계 함수이다. 일반 COUNT(*)와 달리 입력값의 중복 여부를 판별해야 하므로 정렬, 임시 자료구조 또는 인덱스 기반 접근이 추가될 수 있다.고유값의 수와 집계 대상 행이 증가하면 중복 제거 상태도 커진다. 이 상태가 메모리 안에서 처리되지 못하면 내부 임시 테이블의 디스크 전환과 추가 I/O가 발생할 수 있다. 운영 환경에서는 긴 응답 시간에 그치지 않고 동시 쿼리의 버퍼 풀 경쟁과 디스크 I/O 병목으로 이어진다.고유값 집계에서 임시 테이블 사용을 줄이는 순서는 명확하다. 먼저 WHERE 절로 읽는 행을 줄이고, 필터와..

[MSSQL] Hash Aggregate와 Stream Aggregate 선택 조건: 정렬 입력·그룹 수 기준

Hash Aggregate와 Stream Aggregate: 실무적 정의와 운영상 중요성MSSQL에서 집계 연산자는 GROUP BY, DISTINCT, COUNT, SUM 같은 연산을 실제로 수행한다. 실행 계획에서는 주로 Hash Match (Aggregate) 또는 Stream Aggregate로 나타난다. 두 연산자는 같은 결과를 반환하지만 입력 순서, 그룹 수, 메모리 사용 방식이 다르다.Stream Aggregate는 그룹 키 순서로 정렬된 입력을 순차적으로 읽으며 집계한다. Hash Aggregate는 입력 행의 그룹 키로 해시 테이블을 만들고 같은 키를 하나의 버킷에 모아 집계한다. 따라서 정렬된 입력이 이미 존재하면 Stream Aggregate가 유리하고, 정렬되지 않은 대량 입력에서는 ..

[MySQL] GROUP BY Using temporary와 Using filesort를 줄이는 인덱스 설계 조건

GROUP BY: 실무적 정의와 운영상 중요성MySQL에서 GROUP BY는 동일한 그룹 키를 가진 행을 묶고 COUNT, SUM, MIN, MAX 같은 집계 함수를 계산하는 구문이다. 입력 행이 그룹 키 순서로 공급되지 않으면 MySQL은 그룹을 구성하기 위한 중간 작업을 수행해야 한다. 이때 실행 계획의 Extra에 Using temporary 또는 Using filesort가 표시될 수 있다.Using temporary는 중간 결과를 임시 테이블에 기록한다는 의미이다. Using filesort는 인덱스 순서만으로 요구된 순서를 만들지 못해 별도의 정렬 단계가 필요하다는 의미이다. filesort라는 이름이 항상 디스크 정렬을 뜻하지는 않지만, 대상 행이 증가하면 정렬 버퍼 사용과 디스크 I/O 병..

[MSSQL] OFFSET FETCH를 키셋 페이징으로 대체하는 정렬 키와 인덱스 설계

OFFSET FETCH와 키셋 페이징: 실무적 정의와 운영상 중요성SQL Server에서 OFFSET FETCH는 정렬된 결과의 앞부분을 건너뛴 뒤 지정한 행 수를 반환하는 페이징 방식이다. 페이지 번호를 직접 이동할 수 있다는 장점이 있지만, 깊은 페이지에서는 반환하지 않을 행까지 정렬 순서대로 확인하고 버려야 한다.예를 들어 페이지 크기가 50행일 때 10,000번째 페이지는 논리적으로 앞선 499,950행을 건너뛴 후 50행을 반환한다. 적절한 인덱스가 있어 정렬 연산을 피하더라도 OFFSET 자체가 사라지는 것은 아니다. 이 과정에서 인덱스 페이지 접근과 논리 읽기가 누적되며 동시 요청이 많으면 버퍼 풀 압박과 CPU 사용량 증가로 이어진다.대용량 테이블에서의 대체 원칙은 명확하다. 임의의 페이지..