전체 글

[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 사용량 증가로 이어진다.대용량 테이블에서의 대체 원칙은 명확하다. 임의의 페이지..

[MySQL] OFFSET 100000 지연 원인과 복합 키셋 페이징 전환 방법

OFFSET 페이징: 실무적 정의와 운영상 중요성MySQL에서 OFFSET 페이징은 정렬된 결과의 앞부분을 건너뛴 뒤 지정한 행 수만 반환하는 방식이다. LIMIT 20 OFFSET 100000은 100001번째 행으로 즉시 이동하는 명령이 아니다. MySQL은 정렬 순서에 맞는 선행 100000행을 확인하고 버린 뒤 다음 20행을 반환해야 한다.인덱스를 사용하더라도 건너뛸 행을 탐색하는 비용은 사라지지 않는다. 페이지가 뒤로 갈수록 스캔하는 인덱스 레코드와 CPU 사용량이 증가하며, 필요한 컬럼을 인덱스가 포함하지 않으면 테이블 접근까지 반복될 수 있다. 동시 요청이 집중되는 운영 환경에서는 버퍼 풀 경쟁과 디스크 I/O 병목으로 이어진다.OFFSET 100000 이후의 목록 조회는 마지막 행의 정렬 ..

[MSSQL] Sort 메모리 부족과 TempDB Spill 진단: Memory Grant 문제 구분 방법

Sort 연산자: 실무적 정의와 운영상 중요성MSSQL에서 Sort 연산자는 입력 행을 ORDER BY, GROUP BY, DISTINCT, 윈도 함수 또는 조인 처리에 필요한 순서로 재배열한다. 정렬 대상이 메모리 안에 들어오면 작업은 메모리에서 끝나지만, 확보한 작업 공간보다 데이터가 커지면 일부 정렬 데이터를 TempDB에 기록한다. 이 현상이 TempDB Spill이다.Spill 여부와 Memory Grant 부족은 같은 의미가 아니다. Spill은 실제 실행 중 메모리가 부족했다는 결과이고, Memory Grant 문제는 왜 부족해졌는지를 설명하는 원인이다. DBA는 실제 실행 계획의 Sort 경고를 먼저 확인한 뒤 메모리 요청량, 실제 부여량, 최대 사용량, 카디널리티 오차를 순서대로 대조해야..