인덱스를 타도 느린 쿼리는 왜 느린가

인덱스를 처음 배울 때 가장 쉽게 생기는 오해는 “인덱스가 있으면 쿼리가 빨라진다”는 생각입니다. 방향은 맞지만, 실제 성능을 설명하기에는 부족합니다.

MySQL에서 더 중요한 질문은 따로 있습니다. 그 인덱스를 타고 들어간 뒤에도 몇 건을 다시 읽어야 하는가입니다. 같은 B-Tree 인덱스라도 어떤 쿼리에서는 읽기 범위를 크게 줄이고, 어떤 쿼리에서는 인덱스 탐색 이후의 추가 읽기만 늘립니다.

이 글에서는 랜덤 I/O, InnoDB의 Double Lookup, 선택도, 커버링 인덱스를 하나의 읽기 경로로 묶어서 정리합니다. 핵심은 인덱스의 존재가 아니라, 인덱스가 실제로 남기는 후보 row 수입니다.

인덱스는 읽기를 없애는 장치가 아니라 줄이는 장치입니다

데이터베이스 조회 비용을 볼 때 먼저 나눠야 하는 것은 순차 읽기와 랜덤 읽기입니다.

풀 테이블 스캔은 많은 row를 읽더라도 비교적 연속된 방향으로 진행됩니다. 반면 인덱스 레인지 스캔은 조건에 맞는 키를 빠르게 찾을 수 있지만, 실제 레코드를 확인하는 과정에서 여러 위치를 오가게 됩니다. 이때 후보가 많이 남으면 인덱스를 탔는데도 체감 속도가 좋지 않을 수 있습니다.

인덱스가 빠른 이유는 “읽기를 하지 않기 때문”이 아닙니다. 덜 읽게 만들기 때문입니다. 조건이 충분히 좁혀지지 않으면 인덱스는 진입점만 제공하고, 이후에는 많은 후보를 다시 확인하게 만듭니다.

이 기준으로 보면 “WHERE 절에 쓰이는 컬럼이니 인덱스를 만든다”는 판단은 부족합니다. WHERE 절에 있더라도 후보를 거의 줄이지 못하면 좋은 인덱스가 아닙니다.

InnoDB 세컨더리 인덱스는 한 번 더 찾아갑니다

MyISAM과 InnoDB의 세컨더리 인덱스 차이는 인덱스 비용을 이해하는 데 좋은 출발점입니다.

MyISAM의 세컨더리 인덱스 리프는 데이터 파일의 물리적 위치를 가리킵니다. 인덱스에서 조건에 맞는 키를 찾으면 해당 위치로 바로 이동합니다. 반면 InnoDB의 세컨더리 인덱스 리프는 실제 row 위치가 아니라 프라이머리 키 값을 저장합니다. 그래서 세컨더리 인덱스를 찾은 뒤, 그 프라이머리 키로 클러스터드 인덱스를 다시 탐색합니다.

이 추가 탐색을 흔히 Double Lookup이라고 부릅니다. 세컨더리 인덱스를 사용했다는 사실만으로 조회가 끝났다고 보면 안 되는 이유입니다.

InnoDB 구조가 나쁘다는 뜻은 아닙니다. 클러스터드 인덱스를 기준으로 데이터가 정리되고, 세컨더리 인덱스가 물리 위치에 직접 묶이지 않는 장점도 있습니다. 다만 조회 성능을 볼 때는 세컨더리 인덱스 hit가 곧 실제 row hit가 아니라는 점을 계속 기억해야 합니다.

같은 인덱스인데 체감이 달라지는 이유는 선택도입니다

country 컬럼에 인덱스가 있다고 가정해 보겠습니다. 값의 종류가 적고 특정 국가에 데이터가 몰려 있다면, country = 'KR' 조건은 많은 row를 남깁니다. 인덱스는 빠르게 조건 범위로 진입하지만, 그 뒤에 실제 row를 다시 읽는 비용이 커집니다.

반대로 값의 분포가 고르게 퍼져 있고 특정 조건이 소수의 row만 남긴다면 같은 인덱스라도 효과가 커집니다. 결국 선택도는 “인덱스가 후보를 얼마나 잘 줄이는가”를 보여주는 값입니다.

선택도가 낮은 인덱스가 항상 쓸모없다는 뜻은 아닙니다. 예를 들어 상태값처럼 종류가 적은 컬럼도 다른 조건과 함께 복합 인덱스를 만들면 효과가 생길 수 있습니다. 문제는 단일 조건으로 많은 row를 남기는데도, 인덱스를 탔다는 이유만으로 성능이 좋을 것이라고 판단하는 것입니다.

EXPLAIN에서는 type보다 rows와 filtered를 같이 봐야 합니다

실행 계획을 볼 때 type만 보고 “인덱스를 잘 탔다”고 판단하면 위험합니다. ref, range처럼 그럴듯한 접근 방식이 나와도 실제로는 많은 후보 row를 읽고 있을 수 있습니다.

확인 순서는 보통 다음처럼 잡는 편이 안전합니다.

1. possible_keys와 key가 기대한 인덱스인지 확인합니다.
2. key_len으로 복합 인덱스 중 어디까지 사용됐는지 봅니다.
3. rows가 실제 데이터 분포와 크게 어긋나지 않는지 확인합니다.
4. filtered가 낮다면 뒤쪽 조건이 인덱스에서 충분히 처리되지 않는지 봅니다.
5. Extra의 Using index, Using where, Using filesort를 함께 확인합니다.

rows가 크고 filtered가 낮다면 인덱스로 진입은 했지만, 조건을 만족하지 않는 후보를 많이 읽는 상황일 수 있습니다. InnoDB 세컨더리 인덱스에서는 이 후보 수가 그대로 PK 재탐색 비용으로 이어질 수 있습니다.

가능하다면 EXPLAIN ANALYZE로 실제 실행 결과도 확인하는 편이 좋습니다. 일반 EXPLAIN은 옵티마이저의 추정치이고, 통계가 오래됐거나 데이터 분포가 치우쳐 있으면 실제 실행과 다르게 보일 수 있기 때문입니다.

복합 인덱스는 컬럼 목록이 아니라 접근 순서입니다

복합 인덱스를 설계할 때는 어떤 컬럼을 넣을지보다 어떤 순서로 탐색할지를 먼저 봐야 합니다. B-Tree는 왼쪽부터 정렬된 구조이기 때문에 선행 컬럼이 빠지면 뒤쪽 컬럼만으로는 기대한 방식으로 탐색하기 어렵습니다.

예를 들어 다음 쿼리 패턴이 반복된다고 가정해 보겠습니다.

SELECT id, title, created_at
FROM posts
WHERE status = 'PUBLISHED'
  AND category_id = ?
ORDER BY created_at DESC
LIMIT 20;

이 경우 인덱스는 단순히 status, category_id, created_at이 모두 들어 있다는 사실보다, 필터링과 정렬이 어떤 순서로 이어지는지가 중요합니다. 선행 컬럼으로 후보를 줄이고, 그 다음 정렬 순서를 따라갈 수 있어야 filesort와 추가 row 접근을 줄일 수 있습니다.

다만 범위 조건이 들어오면 그 뒤 컬럼의 활용 방식이 제한될 수 있습니다. 그래서 복합 인덱스를 볼 때는 “조건에 포함됐는가”보다 인덱스의 정렬 순서를 따라 실제로 어디까지 좁혀지는가를 확인해야 합니다.

커버링 인덱스는 Double Lookup을 줄이는 방법입니다

InnoDB에서 세컨더리 인덱스를 사용할 때 큰 비용 중 하나는 PK를 다시 따라가 실제 row를 읽는 과정입니다. 그런데 쿼리에 필요한 컬럼이 모두 세컨더리 인덱스 안에 있다면 테이블 row까지 다시 가지 않아도 됩니다. 이것이 커버링 인덱스입니다.

SELECT id, title, created_at
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC
LIMIT 20;

목록 화면처럼 필요한 컬럼이 제한적이고, 필터·정렬 패턴이 반복된다면 커버링 인덱스는 효과가 큽니다. EXPLAIN의 Extra에서 Using index가 보이면 인덱스만으로 필요한 값을 읽었다는 신호로 볼 수 있습니다.

하지만 커버링 인덱스를 만들기 위해 SELECT 컬럼을 무리하게 모두 넣는 것은 조심해야 합니다. 인덱스가 넓어질수록 저장 공간이 늘고, INSERT/UPDATE/DELETE 때 유지해야 할 페이지도 많아집니다. 커버링 인덱스는 읽기 빈도가 높은 좁은 조회 패턴에 맞출 때 가장 설득력이 있습니다.

인덱스는 쓰기 비용도 같이 결정합니다

인덱스는 조회만 빠르게 만드는 구조가 아닙니다. 새로운 row가 들어오면 인덱스에도 키를 추가해야 하고, 값이 바뀌면 기존 키를 제거한 뒤 새 키를 넣어야 합니다. 페이지가 가득 차면 split이 발생할 수 있고, 인덱스가 많을수록 변경 작업은 더 많은 구조를 갱신해야 합니다.

그래서 인덱스 설계는 읽기 성능만 놓고 결정하면 안 됩니다.

좋은 인덱스는 많거나 화려한 인덱스가 아닙니다. 반복되는 쿼리에서 읽기 비용을 실제로 줄이고, 그 대가로 늘어나는 쓰기 비용을 설명할 수 있는 인덱스입니다.

인덱스 추가 후에는 읽기와 쓰기를 같이 봐야 합니다

인덱스를 추가하면 특정 조회 하나는 빨라질 수 있습니다. 하지만 테이블 전체로 보면 쓰기 경로, 버퍼 풀 점유, 저장 공간, 다른 쿼리의 실행 계획까지 함께 바뀔 수 있습니다. 그래서 인덱스는 추가한 순간보다 추가한 뒤에도 의도한 쿼리가 계속 그 인덱스를 쓰는지 확인하는 일이 더 중요합니다.

인덱스 튜닝에서 위험한 결론은 “추가했으니 끝났다”입니다. 데이터 분포가 바뀌거나 쿼리 조건이 달라지면 옵티마이저의 선택도 달라질 수 있습니다. 특히 카디널리티가 낮은 컬럼이나 범위 조건이 섞인 복합 인덱스는 실제 트래픽에서 다시 확인해야 합니다.

실무에서는 쿼리 패턴 단위로 검증해야 합니다

인덱스 설계는 컬럼 하나를 고르는 일이 아니라, 자주 반복되는 쿼리 패턴을 읽는 일에 가깝습니다. 어떤 조건이 항상 들어오는지, 범위 조건은 어디에 놓이는지, 정렬 기준은 무엇인지, 결과를 만들기 위해 실제 row까지 다시 읽어야 하는지를 함께 봐야 합니다.

실무에서의 점검 순서는 다음처럼 가져갈 수 있습니다.

1. WHERE 조건 중 후보 row를 가장 크게 줄이는 조건을 찾습니다.
2. 복합 인덱스의 선행 컬럼이 실제 쿼리에서 빠지지 않는지 확인합니다.
3. 범위 조건 이후 컬럼이 탐색과 정렬에 얼마나 쓰일 수 있는지 봅니다.
4. SELECT 절의 컬럼이 인덱스 안에서 끝나는지, table row 접근이 필요한지 확인합니다.
5. EXPLAIN의 추정 rows와 실제 실행 결과가 크게 어긋나지 않는지 비교합니다.
6. 쓰기 빈도와 저장 공간 증가를 감당할 수 있는지 다시 계산합니다.

이 과정을 거치면 “인덱스를 탔다”에서 멈추지 않고, 그 인덱스가 실제로 읽기 비용을 얼마나 줄였는지까지 볼 수 있습니다.

인덱스를 추가한 뒤 확인할 지표

같은 인덱스인데 속도가 다르게 느껴지는 이유는 인덱스 구조 하나로 설명되지 않습니다. InnoDB에서는 세컨더리 인덱스 이후 PK를 다시 탐색해야 하고, 선택도가 낮은 조건은 그 추가 읽기를 너무 많이 만들 수 있습니다. 복합 인덱스는 컬럼 목록이 아니라 접근 순서이고, 커버링 인덱스는 Double Lookup을 줄이는 방식입니다.

운영에서 남는 질문은 “인덱스가 있느냐”보다 더 구체적이어야 합니다. 그 인덱스가 실제로 읽어야 할 row 수를 줄였는지, secondary index에서 clustered index로 다시 들어가는 탐색을 줄였는지, 정렬과 페이징까지 같은 접근 경로 안에서 처리했는지를 같이 봐야 합니다. EXPLAIN에서 key가 잡혔더라도 rows, filtered, Extra가 기대와 다르면 인덱스를 탄다는 사실만으로는 충분하지 않습니다.

• 인덱스는 읽기를 없애는 장치가 아니라 후보 row를 줄이는 장치입니다.
• InnoDB 세컨더리 인덱스는 필요한 경우 PK를 따라 clustered index를 다시 탐색합니다.
• 선택도가 낮으면 인덱스를 타도 후보 row와 Double Lookup 비용이 크게 남을 수 있습니다.
• 복합 인덱스는 컬럼 목록보다 접근 순서와 범위 조건 위치가 중요합니다.
• 커버링 인덱스는 효과적이지만, 인덱스 폭과 쓰기 비용을 함께 늘립니다.
• 인덱스 추가 후에는 목표 쿼리의 지연뿐 아니라 rows examined, 쓰기 지연, 중복 인덱스 여부까지 확인해야 합니다.

이 기준으로 보면 같은 B-Tree를 타는 쿼리라도 결과가 달라지는 이유를 설명할 수 있습니다. 다음에 비슷한 쿼리를 보면 먼저 rows examined, filtered, Extra, key_len을 보고, 그다음 컬럼 순서와 범위 조건 위치, 커버링 인덱스가 만든 쓰기 비용까지 같이 확인합니다. 인덱스는 “추가했는가”보다 “읽어야 할 후보와 되돌아가는 횟수를 줄였는가”로 평가해야 합니다.