인덱스를 추가했는데 쿼리가 더 느려지는 이유: 옵티마이저의 선택 이해하기

인덱스를 추가하면 당연히 쿼리가 빨라질 것이라고 기대합니다. 그런데 실제로 실행 계획을 분석해보면 방금 만든 인덱스는 사용되지 않고, 심지어 쿼리 응답 시간이 이전보다 길어지는 경우가 있습니다. 이런 상황은 데이터베이스를 다루다 보면 꽤 자주 마주치는 문제입니다. 이 글에서는 그 원인을 하나씩 짚어보고, 실제로 어떻게 대응할 수 있는지 정리합니다.

옵티마이저가 하는 일

데이터베이스는 쿼리를 실행하기 전에 **쿼리 옵티마이저(Query Optimizer)**라는 내부 컴포넌트가 실행 계획을 수립합니다. 옵티마이저는 사용 가능한 인덱스 목록, 테이블 통계 정보, 조인 순서, 예상 행 수 등을 종합적으로 고려해 가장 비용이 낮다고 판단되는 경로를 선택합니다.

중요한 점은 옵티마이저가 항상 ‘가장 빠른’ 실행 계획을 고르는 것이 아니라, 내부 비용 모델에 따라 ‘가장 저렴하다고 추정되는’ 계획을 선택한다는 것입니다. 이 추정이 실제 상황과 어긋날 때 인덱스가 있음에도 성능이 나빠지는 현상이 발생합니다.

인덱스가 있어도 사용되지 않는 주요 원인

통계 정보가 실제 데이터와 다를 때

옵티마이저는 테이블의 행 수, 칼럼의 값 분포, 선택도(Selectivity) 등을 담은 통계 정보를 기반으로 비용을 추정합니다. 이 통계는 실시간으로 갱신되지 않고 주기적으로 또는 특정 조건이 충족될 때 업데이트됩니다.

데이터가 대량으로 삽입되거나 삭제된 직후에는 통계 정보가 현실을 반영하지 못할 수 있습니다. 예를 들어 테이블에 1만 건밖에 없다고 통계가 기록되어 있는데 실제로는 100만 건이 들어있다면, 옵티마이저는 풀 테이블 스캔이 더 싸다고 잘못 판단할 수 있습니다.

MySQL이나 PostgreSQL 모두 통계를 수동으로 갱신하는 명령을 제공합니다.

-- MySQL
ANALYZE TABLE orders;

-- PostgreSQL
ANALYZE orders;

이 명령을 실행한 뒤 실행 계획이 바뀌었다면 통계 문제가 원인이었을 가능성이 높습니다.

선택도가 낮은 칼럼에 단독 인덱스를 생성했을 때

선택도란 해당 칼럼의 값이 얼마나 다양하게 분포되어 있는지를 나타내는 지표입니다. 예를 들어 gender 칼럼처럼 ‘M’과 ‘F’ 두 가지 값만 존재하는 경우, 인덱스를 타더라도 전체 행의 절반 가까이를 읽어야 합니다.

이런 경우 옵티마이저는 인덱스를 사용하면 인덱스 페이지를 읽고 다시 테이블 페이지를 읽는 이중 접근이 발생하므로, 처음부터 테이블을 순차 스캔하는 편이 더 저렴하다고 판단합니다. 즉, 인덱스 자체는 정상이지만 해당 쿼리 패턴에는 적합하지 않은 인덱스인 것입니다.

잘못된 인덱스가 선택될 때

테이블에 여러 개의 인덱스가 존재하는 경우, 옵티마이저가 그 중 최적이 아닌 인덱스를 선택하는 상황이 생길 수 있습니다. 특히 복합 인덱스(Composite Index)와 단일 인덱스가 혼재할 때, 비용 추정 오류로 인해 더 느린 인덱스가 선택되기도 합니다.

이 경우 실행 계획(EXPLAIN 또는 EXPLAIN ANALYZE)을 통해 어떤 인덱스가 선택되었는지 확인하고, 필요하다면 인덱스 힌트를 사용해 특정 인덱스를 명시적으로 지정할 수 있습니다.

-- MySQL 인덱스 힌트 예시
SELECT * FROM orders USE INDEX (idx_created_at) WHERE created_at > '2024-01-01';

다만 인덱스 힌트는 유지보수 부담을 높이므로 근본적인 원인을 먼저 파악하는 것이 우선입니다.

함수나 연산이 인덱스 칼럼에 적용될 때

쿼리의 WHERE 절에서 인덱스가 걸린 칼럼에 함수를 적용하거나 연산을 수행하면, 옵티마이저는 해당 인덱스를 활용할 수 없습니다.

-- 인덱스를 사용할 수 없는 경우
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- 인덱스를 사용할 수 있는 형태로 변환
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

이는 인덱스 자체의 문제가 아니라 쿼리 작성 방식의 문제입니다. 인덱스를 추가해도 쿼리 형태가 그대로라면 성능 향상을 기대하기 어렵습니다.

데이터 타입 불일치로 인한 암묵적 형변환

칼럼의 데이터 타입과 비교 값의 타입이 다를 때 데이터베이스는 내부적으로 형변환을 수행합니다. 이 과정에서 인덱스가 무력화될 수 있습니다.

예를 들어 user_id 칼럼이 INT 타입인데 쿼리에서 문자열 '123'으로 비교한다면, 데이터베이스는 모든 행에 대해 형변환을 수행하며 인덱스를 사용하지 못할 수 있습니다. 칼럼 타입과 비교 값의 타입을 항상 일치시키는 것이 기본입니다.

인덱스 추가가 오히려 성능을 저하시키는 경우

인덱스가 쿼리를 느리게 만드는 것은 SELECT 성능 문제만이 아닙니다. 인덱스 자체가 쓰기 작업에 부담을 주기도 합니다.

쓰기 성능 저하

INSERT, UPDATE, DELETE 작업이 실행될 때마다 테이블의 모든 인덱스도 함께 갱신되어야 합니다. 인덱스가 많을수록 각 쓰기 작업의 비용이 늘어납니다. 읽기 최적화를 위해 인덱스를 무분별하게 추가하면 쓰기 성능이 눈에 띄게 떨어질 수 있습니다.

인덱스 선택의 혼선

비슷한 목적의 인덱스가 여러 개 존재하면 옵티마이저가 어떤 인덱스를 선택할지 판단하는 과정 자체가 복잡해집니다. 중복되거나 불필요한 인덱스는 옵티마이저의 비용 추정을 방해하고, 결과적으로 최적이 아닌 실행 계획이 선택될 확률을 높입니다.

인덱스 페이지 관리 비용

인덱스는 별도의 데이터 구조로 저장되며, 데이터가 변경될 때마다 인덱스 트리가 재정렬될 수 있습니다. 이 과정에서 페이지 분할(Page Split)이 발생하면 I/O 비용이 급격히 늘어납니다. 특히 순서 없이 삽입되는 UUID 기반의 기본 키를 사용하는 경우 이 문제가 두드러질 수 있습니다.

실행 계획으로 원인 진단하기

인덱스 관련 성능 문제를 진단할 때 가장 먼저 해야 할 일은 실행 계획을 확인하는 것입니다.

-- MySQL
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

실행 계획에서 확인해야 할 주요 항목은 다음과 같습니다.

• type (MySQL): ALL이면 풀 테이블 스캔, ref나 range면 인덱스를 활용하고 있다는 의미입니다.
• key: 실제로 선택된 인덱스 이름이 표시됩니다. 기대한 인덱스와 다르다면 원인을 추적해야 합니다.
• rows: 옵티마이저가 읽을 것으로 예측하는 행 수입니다. 실제 결과 행 수와 크게 다르다면 통계 문제일 수 있습니다.
• Extra: Using filesort나 Using temporary 같은 항목이 보이면 성능에 영향을 주는 추가 처리가 발생하고 있다는 신호입니다.

PostgreSQL에서는 EXPLAIN ANALYZE를 사용하면 예측값과 실제 실행값을 함께 볼 수 있어 더 정확한 진단이 가능합니다.

실질적인 대응 방법 정리

원인을 파악한 후에는 상황에 맞는 방법으로 대응합니다.

1. 통계 정보 갱신: 데이터 변화가 많은 테이블은 통계를 주기적으로 갱신하거나, 자동 갱신 설정을 점검합니다.
2. 인덱스 선택도 검토: 선택도가 낮은 칼럼의 단독 인덱스는 제거하거나 다른 칼럼과 조합한 복합 인덱스로 대체합니다.
3. 쿼리 형태 개선: WHERE 절에서 인덱스 칼럼에 함수나 연산을 적용하지 않도록 쿼리를 수정합니다.
4. 불필요한 인덱스 정리: 사용되지 않거나 중복된 인덱스를 제거해 옵티마이저의 선택지를 단순화합니다.
5. 복합 인덱스 설계 재검토: 자주 함께 사용되는 칼럼은 복합 인덱스로 묶고, 칼럼 순서도 선택도와 쿼리 패턴을 기준으로 결정합니다.
6. 인덱스 힌트는 최후의 수단으로: 근본 원인을 해결하지 않은 상태에서 힌트에 의존하면 나중에 더 큰 문제로 이어질 수 있습니다.

마치며

인덱스는 올바르게 설계되고 관리될 때만 기대한 성능을 발휘합니다. 단순히 인덱스를 추가하는 것만으로는 충분하지 않으며, 옵티마이저가 어떤 기준으로 실행 계획을 수립하는지를 이해해야 문제의 핵심에 접근할 수 있습니다. 성능 문제가 발생했을 때는 먼저 실행 계획을 확인하고, 통계 상태와 쿼리 형태, 인덱스 구성을 차례로 점검하는 것이 효과적인 순서입니다.