사용자 정의 필터링 쿼리의 인덱스 미적용과 풀 스캔 유발

2월 1, 2026 • 2분 읽기 • 보안 API 흐름

데이터베이스 서버에 빨간색 경고 표시가 켜져 있으며, 포트에서 걸쭉한 꿀처럼 느리게 데이터가 유출되는 것을 시각적으로 표현한 디지털 개념 이미지입니다.

증상 진단: 데이터베이스 응답이 갑자기 느려졌나요?

애플리케이션의 특정 기능(예: 고급 검색, 사용자 정의 리포트 생성)을 실행할 때, 평소와 달리 데이터 조회 속도가 극도로 저하되는 현상을 경험하고 계신다면 이는 전형적인 쿼리 성능 문제의 신호입니다. 특히 WHERE 절에 사용자 정의 함수(UDF), 복잡한 CASE 문, 또는 여러 테이블의 컬럼을 조합한 계산식이 포함된 경우, 데이터베이스 엔진이 최적의 실행 계획을 수립하지 못하고 있습니다. 핵심 증상은 CPU 사용률이 해당 쿼리 실행 시 급격히 상승하며, 디스크 I/O(읽기)가 활발히 발생하는 것입니다. 쿼리 실행 계획을 확인하면 ‘Index Scan’이 아닌 ‘Table Scan’ 또는 ‘Clustered Index Scan’이 표시될 것입니다.

원인 분석: 왜 인덱스가 무시되는가?

데이터베이스 인덱스는 사전과 같아서 정렬된 순서로 데이터를 빠르게 찾도록 돕습니다. 그러나 사용자 정의 필터링 조건은 이 ‘사전 찾기’ 규칙을 무너뜨리는 주된 원인입니다. 데이터베이스 옵티마이저는 쿼리 실행 전, 사용 가능한 인덱스와 조건절을 평가해 비용이 가장 낮은 경로를 선택합니다. 문제는 다음과 같은 경우에 발생합니다.

비결정적 함수 사용: WHERE YEAR(created_date) = 2023 또는 WHERE UPPER(username) = 'ADMIN'과 같이 컬럼을 함수나 표현식으로 감싸면, 인덱스가 가진 원본 값과 비교가 불가능해집니다. 인덱스는 ‘created_date’ 자체로 정렬되어 있지 ‘YEAR(created_date)’로 정렬되어 있지 않기 때문입니다.
암시적 데이터 형 변환: 문자열 컬럼에 숫자 값을 비교하거나 그 반대의 경우. 데이터베이스가 내부적으로 형 변환을 수행하며 인덱스 사용을 포기하게 됩니다.
인덱스 선택도 부족: 필터링 조건이 테이블의 대부분 행(일반적으로 15~30% 이상)을 반환할 것으로 예상되면, 인덱스를 탐색하고 다시 데이터 페이지를 찾아가는 비용이 테이블을 처음부터 끝까지 읽는 풀 스캔 비용보다 더 클 수 있어 옵티마이저가 풀 스캔을 선택합니다.

결국, 풀 스캔은 필요한 데이터를 찾기 위해 테이블의 모든 데이터 페이지를 메모리로 읽어들이는 작업입니다. 데이터 양이 적을 때는 체감이 안 되지만, 로그 테이블이나 트랜잭션 테이블이 수백만 건 이상 축적된 환경에서는 쿼리 하나가 전체 시스템의 병목이 될 수 있습니다.

해결 방법 1: 쿼리 조건 재작성 – 가장 빠른 적용법

인덱스를 새로 만들거나 스키마를 변경하기 전에, 현재 쿼리 자체를 인덱스 친화적으로 수정할 수 있는지 검토하십시오. 이 방법은 애플리케이션 코드 또는 저장 프로시저의 수정이 가능할 때 즉시 효과를 발휘합니다.

함수 사용 제거: 컬럼을 함수로 감싸지 말고, 상수 부분을 조정하십시오.
수정 전: WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 10
수정 후: WHERE order_date >= '2023-10-01' AND order_date < '2023-11-01'
이렇게 하면 `order_date` 컬럼에 생성된 인덱스를 정상적으로 범위 검색(Range Scan)에 사용할 수 있습니다.
LIKE 패턴 수정: 와일드카드(%)를 문자열 앞에 사용하면 인덱스 활용이 불가능합니다.
불가능: WHERE product_name LIKE '%apple%'
가능(접두사 검색): WHERE product_name LIKE 'apple%'
전문 검색이 필요하다면 데이터베이스의 전문 검색(Full-Text Search) 기능을 도입하는 것을 고려해야 합니다.
암시적 형 변환 제거: 애플리케이션 로직 또는 쿼리에서 비교 대상의 데이터 형식을 명시적으로 일치시키십시오.
문제: WHERE user_id = '1001' (user_id는 정수형 INT 컬럼)
해결: WHERE user_id = 1001 또는 WHERE user_id = CAST('1001' AS INT)

해결 방법 2: 계산된 컬럼(Computed Column)에 인덱스 생성 – 근본적인 최적화

함수나 표현식을 제거할 수 없는 비즈니스 로직이라면, 데이터베이스가 제공하는 ‘계산된 컬럼’과 이를 기반으로 한 인덱스를 활용하십시오. 이는 원본 데이터를 변환한 결과를 물리적으로 저장(또는 인덱스에만 저장)하여 인덱스 검색이 가능하게 합니다.

주의사항: 계산된 컬럼을 추가하면 테이블 스키마가 변경됩니다, 기존 애플리케이션 쿼리에 영향을 미치지 않도록 신중히 설계해야 하며, 특히 대용량 테이블에 컬럼을 추가하는 작업은 시스템 부하가 낮은 시간에 진행해야 합니다. 반드시 스테이징 환경에서 테스트 후 적용하십시오.

계산된 컬럼 추가 (SQL Server 예시):
ALTER TABLE Orders ADD OrderYear AS YEAR(order_date) PERSISTED;
여기서 PERSISTED 키워드는 계산 결과를 실제 디스크에 저장하도록 지시합니다. 이 옵션은 인덱스 생성에 필수적입니다.
계산된 컬럼에 인덱스 생성:
CREATE INDEX IX_Orders_OrderYear ON Orders(OrderYear);
쿼리 수정:
기존 쿼리: SELECT * FROM Orders WHERE YEAR(order_date) = 2023;
수정된 쿼리: SELECT * FROM Orders WHERE OrderYear = 2023;
이제 옵티마이저는 새로 생성된 IX_Orders_OrderYear 인덱스를 효율적으로 사용할 수 있습니다.

MySQL / PostgreSQL에서의 유사 접근법

MySQL에서는 생성된 컬럼(Generated Column), PostgreSQL에서는 저장된 생성 컬럼(Stored Generated Column)으로 동일한 개념을 구현할 수 있습니다. 실제 다수의 운영 리포트를 분석해 보면, 이러한 기능은 애플리케이션의 계산 부하를 데이터베이스 계층으로 전이시켜 데이터 무결성과 조회 성능을 동시에 확보하는 효율적인 패턴으로 관측됩니다. 각 데이터베이스 엔진에 따른 구문적 차이는 실제 인프라 구성 단계에서 기술적 환경에 맞춰 반영되는 요소입니다.

해결 방법 3: 인덱스 선택 전략 및 통계 업데이트 – 시스템 관점의 조정

쿼리와 스키마를 최적화했음에도 풀 스캔이 발생한다면, 인덱스 자체의 문제나 데이터베이스의 잘못된 비용 예측을 의심해야 합니다.

복합 인덱스 컬럼 순서 검토: 복합 인덱스는 첫 번째 컬럼을 기준으로 정렬된 후, 두 번째 컬럼을 정렬하는 방식입니다, 이로 인해 쿼리 조건에 가장 자주 사용되며, 고유한 값이 많은 컬럼을 선두에 배치해야 합니다.
예: CREATE INDEX IX_User_Search ON Users(last_name, first_name, status);
이 인덱스는 WHERE last_name='Kim' 또는 WHERE last_name='Kim' AND first_name='Gildong' 조건에는 효과적이지만, WHERE first_name='Gildong' 단독 조건에는 사용되지 않습니다.
데이터베이스 통계 업데이트 실행: 옵티마이저는 테이블과 인덱스에 대한 통계 정보(행 수, 값의 분포도 등)를 기반으로 실행 계획을 세웁니다. 이 통계가 오래되었거나 부정확하면 잘못된 판단을 내릴 수 있습니다.
SQL Server: UPDATE STATISTICS 테이블명 [인덱스명];
MySQL (InnoDB): ANALYZE TABLE 테이블명;
이 작업은 시스템 리소스를 일시적으로 사용하므로 운영 시간 외에 정기적으로 스케줄링하는 것이 좋습니다.
쿼리 힌트의 신중한 사용: 최후의 수단으로 데이터베이스에 특정 인덱스를 사용하도록 강제하는 힌트를 줄 수 있습니다. 그러나 이는 데이터 분포가 변경되면 오히려 성능을 해칠 수 있는 위험한 방법입니다. 옵티마이저보다 개발자가 더 낫다고 확신할 때만 제한적으로 적용하십시오.
SQL Server 예시: SELECT * FROM Orders WITH (INDEX(IX_OrderDate)) WHERE ...;

검색 엔진 최적화 과정에서 검색 인덱스 경로를 배제한 채 다른 요소를 분석하는 SEO 전략을 복잡한 플로우차트를 통해 탐정이 집중적으로 살펴보고 있는 개념도입니다.

주의사항 및 예방 조치

성능 최적화는 한 번의 작업이 아닌 지속적인 프로세스입니다. 특히 데이터베이스 부하가 애플리케이션 서버로 전이되어 발생하는 시스템 리소스(CPU/Memory) 누수로 점진적 성능 저하 현상을 방지하기 위해 다음 사항을 체크리스트로 관리하십시오.

모니터링 필수: 느린 쿼리 로그(Slow Query Log)를 활성화하고 주기적으로 분석하여 새로운 성능 병목 지점을 사전에 발견하십시오.
인덱스 관리: 인덱스는 생성만 하는 것이 아닙니다. 사용 빈도가 낮은 인덱스는 오히려 INSERT/UPDATE/DELETE 성능을 저하시키는 부채입니다. 정기적으로 사용되지 않는 인덱스를 조사하여 제거하십시오.
테스트 절대 무시: 프로덕션 환경에 인덱스를 추가/삭제하거나 쿼리를 변경하기 전, 반드시 실제 데이터가 있는 스테이징 환경에서 성능 테스트와 부하 테스트를 수행하십시오. 예상치 못한 교착 상태(Deadlock)나 리소스 경합이 발생할 수 있습니다.
애플리케이션 캐싱 고려: 자주 실행되고 결과가 자주 변경되지 않는 사용자 정의 필터링 쿼리라면, **애플리케이션 레벨(Redis, Memcached 등)**에서 결과를 캐싱하여 데이터베이스 부하를 근본적으로 줄이는 아키텍처 검토가 필요합니다.

전문가 팁: 인덱스는 데이터의 사본입니다. 계산된 컬럼 인덱스나 복합 인덱스를 생성한다는 것은 데이터의 일부를 미리 계산하거나 재정렬하여 별도로 저장하는 것을 의미합니다. 이는 읽기 성능을 대가로 쓰기 성능과 저장 공간을 지불하는 트레이드오프(Trade-off) 관계입니다.

따라서 ‘모든 쿼리를 빠르게’ 하려는 욕심보다는, 실제 모니터링을 통해 가장 빈번하고 중요한 핵심 경로(Critical Path)의 쿼리에 집중하여 인덱스를 설계하십시오. 인덱스 하나를 추가하기 전에 “이 인덱스가 정말로 가장 많이 쓰이는 쿼리를 구해줄까?”*라는 질문을 반드시 던져보아야 합니다.