인덱스가 있다는 사실만으로는 충분하지 않다

느린 쿼리를 보다 보면 “인덱스가 있는데 왜 느리지?”라는 질문을 자주 하게 된다. 그런데 MySQL에서는 인덱스가 존재하는 것과, 그 인덱스를 원하는 방식으로 활용하는 것이 꽤 다르다. 실제로는 인덱스를 가지고도 많은 행을 읽거나, 정렬을 별도로 수행하거나, 심지어 옵티마이저가 인덱스를 포기하는 상황도 흔하다.

그래서 인덱스를 볼 때는 유무보다 어떤 조건으로 찾고 어떤 순서로 정렬하며 어디까지 읽고 멈출 수 있는지를 함께 봐야 한다. 이 기준이 없으면 인덱스를 더 추가해도 기대한 만큼 달라지지 않는 경우가 많다.


조회 패턴과 인덱스 순서를 같이 봐야 한다

복합 인덱스가 맞는 경우와 조회 패턴이 갈리는 경우를 같은 묶음으로 보면, 왜 “인덱스가 있다”는 사실만으로는 부족한지 더 잘 드러난다.

복합 인덱스는 앞에서부터 맞아야 한다

예를 들어 특정 언론사의 최신 기사 목록 API가 아래 쿼리를 자주 사용한다고 해 보자.

이 글은 MySQL 8.0 버전을 기준으로 한다.

SELECT id,
       office_id,
       title,
       published_at
FROM article
WHERE status = 'PUBLISHED'
  AND office_id = 10
ORDER BY published_at DESC LIMIT 20;

이때 아래처럼 복합 인덱스를 잡는 경우를 생각해 볼 수 있다.

CREATE INDEX idx_article_status_office_published_at
    ON article (status, office_id, published_at DESC);

이 인덱스가 잘 맞는 이유는 WHERE 절의 동등 조건이 앞에 오고, 그 뒤에 정렬 컬럼이 붙어 있기 때문이다. 반대로 published_at을 앞에 두고 status, office_id를 뒤로 밀어두면 원하는 필터와 정렬을 한 번에 처리하기 어려워진다.

흔히 “복합 인덱스는 컬럼이 많을수록 좋다”는 식으로 생각하기 쉬운데, 실제로 중요한 것은 개수보다 앞에서부터 어떤 탐색 경로를 만들 수 있느냐다. 뉴스 서비스처럼 목록 조회가 많은 시스템에서는 특히 이 차이가 크게 드러난다. 조건은 맞지만 정렬을 따로 해야 하는 인덱스와, 조건과 정렬을 함께 만족하는 인덱스는 같은 LIMIT 20 쿼리에서도 체감 차이가 크다.


같은 테이블이라도 조회 패턴이 다르면 인덱스도 달라진다

기사 테이블 하나만 놓고 봐도 조회 방식은 여러 종류다. 메인 화면은 최신순 목록을 원하고, 언론사 페이지는 office_id별 목록을 원하며, 관리자 화면은 상태값과 예약 발행 시간을 함께 볼 수 있다. 이때 모든 조회를 단일 인덱스 하나로 해결하려고 하면 어딘가에서 비용이 새기 시작한다.

예를 들어 아래 두 쿼리는 같은 article 테이블을 조회하지만 요구가 다르다.

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

SELECT id,
       title,
       reserved_at
FROM article
WHERE status = 'RESERVED'
  AND reserved_at <= NOW()
ORDER BY reserved_at ASC LIMIT 50;

첫 번째는 발행 기사 최신순 목록이고, 두 번째는 예약 발행 대상 조회다. 둘 다 기사 조회지만 필터 조건과 정렬 기준이 다르므로 적절한 인덱스도 달라질 수 있다. 그래서 인덱스 설계는 테이블 중심보다 조회 패턴 중심으로 보는 편이 낫다.


인덱스를 잘 활용하지 못하는 경우가 있다

함수 적용, 형변환, 문자열 검색처럼 겉으로는 단순해 보이는데도 탐색 경로를 흐리게 만드는 조건은 따로 묶어서 보는 편이 낫다.

함수와 형변환은 인덱스를 무디게 만든다

쿼리 문장이 보기에는 간단한데도 인덱스 효율이 갑자기 떨어지는 경우가 있다. 대표적인 원인은 컬럼에 함수를 적용하거나, 비교 과정에서 형변환이 일어나는 상황이다.

SELECT id,
       title
FROM article
WHERE
    DATE (published_at) = '2022-03-24';

이 쿼리는 읽기에는 편하지만, 일반적인 컬럼 인덱스에서는 published_at 자체를 범위 탐색하는 대신 매 행마다 DATE() 계산을 하게 만들 수 있다. 같은 조건이라면 아래처럼 범위 비교로 바꾸는 편이 보통 더 유리하다.

SELECT id,
       title
FROM article
WHERE published_at >= '2022-03-24 00:00:00'
  AND published_at < '2022-03-25 00:00:00';

형변환도 비슷하다. 비교 과정에서 컬럼 쪽에 변환이 걸리거나, 타입 정합성이 맞지 않아 옵티마이저가 예상하기 어려운 조건이 되면 기대한 접근 방식과 다른 계획이 잡힐 수 있다. 쿼리 성능 문제를 볼 때 타입 정합성을 같이 봐야 하는 이유가 여기에 있다.

예를 들어 문자열 컬럼에 숫자 비교를 걸거나, 조인하는 두 컬럼의 타입이 서로 다르면 눈으로 보기에는 단순한 조건인데도 인덱스 활용이 어긋날 수 있다. 반면 정수 컬럼에 문자열 "10" 같은 값을 넘기는 단순 비교는 MySQL이 상수를 숫자로 해석해 무리 없이 처리하는 경우도 많다. 그래서 “문자열로 넘기면 항상 느리다”처럼 일반화하기보다, 실제 실행 계획에서 컬럼 쪽 변환이 일어나는지와 비교 타입이 일관된지를 함께 확인하는 편이 안전하다.

아래처럼 문자열 컬럼을 숫자와 비교하거나, 서로 다른 타입의 컬럼을 조인하는 경우를 생각해 볼 수 있다.

SELECT id,
       office_code
FROM article
WHERE office_code = 10;

SELECT a.id,
       o.name
FROM article a
         INNER JOIN office o ON o.legacy_code = a.office_id;

이럴 때는 쿼리 문장만 보면 단순해 보여도, 실제로는 어느 쪽에서 타입 변환이 일어나는지에 따라 인덱스 활용 방식이 달라질 수 있다.

MySQL 8.0에서는 functional index를 사용할 수 있으므로, 함수 호출이 들어간 표현을 반드시 피해야 한다고 단정할 수는 없다. 다만 일반적인 컬럼 인덱스만 전제로 할 때는 범위 조건으로 풀어 쓰는 쪽이 여전히 이해하기 쉽고 예측 가능하다.


LIKE 검색은 패턴 위치가 더 중요하다

문자열 검색에서는 인덱스가 있어도 기대보다 느린 경우가 많다. 특히 아래처럼 앞에 와일드카드가 붙으면 상황이 달라진다.

SELECT id,
       title
FROM article
WHERE title LIKE '%속보%';

이 패턴은 문자열의 시작점을 알 수 없어서 일반적인 B-Tree 인덱스로는 효율적인 범위 탐색이 어렵다. 반대로 title LIKE '속보%'처럼 접두어 검색은 비교적 유리하다. 그래서 기사 제목 검색 요구를 단순 인덱스로 처리하려고 하기보다, 접두어 검색인지 전문 검색인지부터 먼저 구분하는 편이 낫다.


인덱스를 탔는데도 느릴 수 있는 경우가 있다

인덱스를 쓴다는 사실만으로 읽는 범위가 충분히 줄었다고 보기는 어렵다. 선택도가 낮거나 커버링이 되지 않으면, 인덱스를 통해 시작해도 결국 많은 행을 따라가야 할 수 있다.

인덱스를 탔는데도 느릴 수 있는 이유

인덱스를 탔다는 표현도 조금 조심해서 봐야 한다. 옵티마이저가 인덱스를 사용했다는 사실과, 적은 행만 읽었다는 사실은 같지 않다. 조건의 선택도가 낮으면 인덱스를 통해 시작하더라도 결국 많은 행을 따라가야 할 수 있다.

예를 들어 status = 'PUBLISHED'처럼 전체 데이터의 대부분이 같은 값을 갖는 조건은 인덱스가 있어도 걸러지는 양이 크지 않을 수 있다. 이럴 때는 단일 인덱스 하나보다 status, office_id, published_at처럼 실제 조회 패턴을 함께 반영한 복합 인덱스가 더 낫다.

또 한 가지는 커버링 여부다. 목록 조회에 필요한 컬럼이 인덱스에 충분히 들어 있지 않으면, 인덱스로 후보를 찾은 뒤 다시 테이블 본문으로 돌아가 값을 읽어야 한다. 이 비용은 데이터 양이 많을수록 무시하기 어려워진다. 그래서 인덱스를 만들 때는 “찾을 수 있는가”만이 아니라 “추가 접근이 얼마나 필요한가”도 같이 봐야 한다.


통계 정보와 옵티마이저 판단도 같이 봐야 한다

인덱스가 있는데도 기대와 다른 실행 계획이 나오는 경우에는, 인덱스 구조만이 아니라 통계 정보와 옵티마이저의 판단도 함께 볼 필요가 있다. MySQL 공식 문서도 인덱스가 기대처럼 쓰이지 않을 때는 EXPLAIN으로 실제 경로를 확인하고, 필요하면 ANALYZE TABLE로 통계 정보를 갱신해 보라고 안내한다.

예를 들어 office_id별 데이터 분포가 한쪽으로 크게 치우쳐 있다면, 같은 인덱스를 두고도 어떤 값에서는 꽤 많은 행을 읽게 될 수 있다.

EXPLAIN
SELECT
    id,
    title,
    published_at
FROM
    article
WHERE
    office_id = 10
ORDER BY
    published_at DESC
LIMIT 20;

ANALYZE TABLE article;

이럴 때는 “인덱스가 있는데 왜 안 타지?”라고 바로 단정하기보다, 어떤 인덱스를 골랐는지와 rows 추정치가 얼마나 크게 잡히는지를 먼저 보는 편이 낫다. 인덱스를 타더라도 시작 범위가 넓으면 느릴 수 있고, 통계 정보가 오래됐다면 옵티마이저가 기대와 다른 선택을 할 수도 있다.


정리하며

결국 인덱스는 “만들면 빨라지는 장치”라기보다, 자주 쓰는 탐색 경로를 미리 정리해 두는 구조에 가깝다. 어떤 조건으로 찾고 어떤 순서로 보여줄지를 먼저 정리하지 않으면 인덱스 추가는 종종 안심용 작업에 그친다.

인덱스가 있어도 느린 이유는 대개 인덱스가 없어서가 아니라, 조회 패턴과 인덱스의 탐색 경로가 맞지 않아서 생긴다. 복합 인덱스 순서, 함수 적용, 형변환, 커버링 여부를 함께 봐야 하는 이유도 결국 같은 맥락이다.

인덱스를 추가할 때는 무엇을 찾는지만이 아니라, 어떤 순서로 읽고 어디에서 멈출 수 있는지도 함께 보는 편이 낫다. 이 기준이 잡혀 있으면 인덱스가 늘어나더라도 왜 그 인덱스가 필요한지 설명하기가 훨씬 쉬워진다.


참조