인덱스를 걸었는데 왜 안 탈까?

데이터베이스 인덱스는 어떻게 조회를 빠르게 만들까?에서 인덱스가 빠른 이유를, 복합 인덱스는 왜 컬럼 순서가 중요할까?에서 컬럼 순서 규칙을 확인했는데요. 그런데 실무에서 더 자주 마주치는 건 이런 상황입니다. 인덱스를 분명히 걸었고 컬럼 순서도 맞췄는데 쿼리가 여전히 느린 경우요.

인덱스는 만들어 두는 것만으로 일하지 않습니다. 쿼리를 어떻게 쓰느냐에 따라 멀쩡한 인덱스가 통째로 무시되기도 하거든요. 이번 글에서는 인덱스가 있는데도 안 타는 경우를 하나씩 재현해 보겠습니다.

실험 준비

실습 데이터는 지난 글에서 쓰던 50만 명짜리 회원 테이블 그대로입니다. 실행 계획과 소요 시간을 찍어주는 probe() 함수도 다시 쓰고요. 이번에는 인덱스 조합을 자주 바꿔야 해서 초기화 함수만 하나 더했습니다.

setup.ts

const KNOWN = [
  "idx_email",
  "idx_lower",
  "idx_nocase",
  "idx_city",
  "idx_age",
  "idx_city_age",
];

/** 알려진 인덱스를 모두 지우고 넘겨준 것만 새로 만듭니다 */
export function only(...defs: [string, string][]) {
  for (const n of KNOWN) db.run(`DROP INDEX IF EXISTS ${n}`);
  for (const [name, cols] of defs) db.run(`CREATE INDEX ${name} ON members ${cols}`);
  db.run("ANALYZE");
}

실험마다 인덱스를 깨끗이 지우고 시작하는 게 중요한데요. 인덱스가 남아 있으면 데이터베이스가 의도치 않은 걸 골라 써서 결과를 엉뚱하게 만들거든요. 마지막의 ANALYZE는 통계를 갱신해 쿼리 플래너(query planner)가 제대로 판단하게 해줍니다.

컬럼을 가공하면 인덱스가 죽습니다

가장 흔한 함정부터 보겠습니다. 대소문자를 무시하고 이메일을 찾으려고 lower()를 씌우는 경우인데요.

s1-function.ts

import { db, only, probe } from "./setup";

const target = "user499999@example.com";
only(["idx_email", "(email)"]);

probe("A. 컬럼 그대로", "SELECT * FROM members WHERE email = ?", target);
probe("B. lower() 씌우기", "SELECT * FROM members WHERE lower(email) = ?", target);
probe("C. 문자열 이어붙이기", "SELECT * FROM members WHERE email || '' = ?", target);

결과

A. 컬럼 그대로
  실행 계획: SEARCH members USING INDEX idx_email (email=?)
  결과 행수: 1
  소요 시간: 0.010ms

B. lower() 씌우기
  실행 계획: SCAN members
  결과 행수: 1
  소요 시간: 56.656ms

C. 문자열 이어붙이기
  실행 계획: SCAN members
  결과 행수: 1
  소요 시간: 65.732ms

0.010ms가 56ms로, 5,000배 넘게 느려졌습니다. 인덱스는 그대로 있는데 말이죠.

이유는 인덱스가 무엇을 정렬해뒀는지 생각하면 명확합니다. idx_email이 정렬해둔 건 email 값이지 lower(email) 값이 아닙니다. 데이터베이스 입장에서 lower(email)은 매 행마다 함수를 돌려봐야 알 수 있는 미지의 값이에요. 정렬된 목록이 없으니 50만 행을 전부 꺼내 함수를 적용하고 비교하는 수밖에 없습니다.

기억해 둘 원칙은 하나입니다. WHERE 절 왼쪽의 컬럼에 손을 대는 순간 인덱스는 쓸 수 없습니다. 함수든 산술 연산이든 문자열 결합이든 마찬가지예요. WHERE age + 1 = 31 대신 WHERE age = 30으로 쓰라는 조언이 여기서 나옵니다.

그럼 대소문자를 무시한 검색은 포기해야 할까요? 그렇지는 않습니다. 가공한 결과 자체를 인덱스로 만들면 되거든요.

s1-function.ts (이어서)

db.run("CREATE INDEX idx_lower ON members (lower(email))");
db.run("ANALYZE");

probe("D. 표현식 인덱스를 만든 뒤", "SELECT * FROM members WHERE lower(email) = ?", target);

결과

D. 표현식 인덱스를 만든 뒤
  실행 계획: SEARCH members USING INDEX idx_lower (<expr>=?)
  결과 행수: 1
  소요 시간: 0.008ms

56ms가 0.008ms로 제자리를 찾았습니다. 이렇게 컬럼이 아니라 계산식을 정렬해두는 인덱스를 표현식 인덱스(expression index)라고 부릅니다. 괄호 안이 (email=?)가 아니라 (<expr>=?)로 표시되는 게 재미있는데요. 특정 컬럼이 아니라 식 하나를 통째로 정렬해뒀다는 뜻입니다.

타입을 바꾸는 것도 가공입니다

같은 원리가 타입에도 적용됩니다. 다만 SQLite는 여기서 꽤 관대한 편이에요.

s2-type.ts

only(["idx_age", "(age)"]);

probe("A. 숫자 그대로", "SELECT * FROM members WHERE age = 30");
probe("B. 문자열로 넘기면", "SELECT * FROM members WHERE age = '30'");
probe("C. 컬럼을 문자열로 바꾸면", "SELECT * FROM members WHERE CAST(age AS TEXT) = '30'");

결과

A. 숫자 그대로
  실행 계획: SEARCH members USING INDEX idx_age (age=?)
  결과 행수: 10,000
  소요 시간: 11.074ms

B. 문자열로 넘기면
  실행 계획: SEARCH members USING INDEX idx_age (age=?)
  결과 행수: 10,000
  소요 시간: 9.804ms

C. 컬럼을 문자열로 바꾸면
  실행 계획: SCAN members
  결과 행수: 10,000
  소요 시간: 60.651ms

B번이 눈에 띕니다. 숫자 컬럼에 문자열 '30'을 넘겼는데도 인덱스를 탔고 시간도 A번과 사실상 같아요. SQLite가 컬럼의 타입 선호도(type affinity)를 보고 '30'을 숫자 30으로 바꿔서 비교했기 때문입니다. 비교당하는 값 쪽을 고치는 건 인덱스의 정렬 순서와 무관하니까요.

반면 C번은 CAST로 컬럼 자체를 문자열로 바꿨습니다. 앞 절의 lower()와 똑같은 상황이라 풀 스캔(full scan)으로 떨어졌어요.

다만 B번이 통한다고 안심하기는 이릅니다. 다른 데이터베이스는 이만큼 관대하지 않아서, 타입이 어긋나면 컬럼 쪽을 변환해 버리고 인덱스를 포기하는 경우가 있거든요. 파라미터 타입은 컬럼 타입에 맞춰 넘기는 습관이 안전합니다.

LIKE는 앞부분 일치라도 조건이 붙습니다

인덱스와 LIKE에 대해서는 널리 알려진 규칙이 있습니다. LIKE 'user%'처럼 앞부분이 고정되면 인덱스를 타고, LIKE '%example.com'처럼 와일드카드가 앞에 오면 못 탄다는 거죠. 그런데 실제로 돌려보면 이야기가 조금 다릅니다.

s3-like.ts

only(["idx_email", "(email)"]);

probe("A. 일반 인덱스", "SELECT * FROM members WHERE email LIKE 'user49999%'");
probe("B. 와일드카드가 앞에", "SELECT * FROM members WHERE email LIKE '%99999@example.com'");
probe("C. 대소문자를 구분하는 GLOB", "SELECT * FROM members WHERE email GLOB 'user49999*'");

결과

A. 일반 인덱스
  실행 계획: SCAN members
  결과 행수: 11
  소요 시간: 38.667ms

B. 와일드카드가 앞에
  실행 계획: SCAN members
  결과 행수: 5
  소요 시간: 46.290ms

C. 대소문자를 구분하는 GLOB
  실행 계획: SEARCH members USING INDEX idx_email (email>? AND email<?)
  결과 행수: 11
  소요 시간: 0.051ms

A번이 예상을 벗어납니다. 분명히 앞부분 일치인데 풀 스캔이에요. 그런데 같은 패턴을 GLOB으로 바꾼 C번은 인덱스를 탑니다.

C번 실행 계획의 괄호에 답이 있습니다. (email>? AND email<?), 즉 패턴 검색이 범위 조건으로 바뀌었습니다. user49999로 시작하는 값을 찾는 건 user49999 이상이면서 그보다 한 글자 큰 값 미만인 구간을 찾는 것과 같으니까요. 앞부분 일치만 인덱스를 타는 이유가 바로 이 변환에 있습니다.

문제는 이 변환이 성립하려면 인덱스의 정렬 순서와 패턴 비교 방식이 일치해야 한다는 점입니다. SQLite의 기본 LIKE는 대소문자를 구분하지 않는데 일반 인덱스는 대소문자를 구분하는 순서로 정렬되어 있어요. 기준이 어긋나니 구간을 잡을 수가 없습니다. 반면 GLOB은 대소문자를 구분하니 인덱스의 정렬과 아귀가 맞고요.

그럼 LIKE를 쓰면서 인덱스를 타려면 어떻게 해야 할까요? 인덱스를 LIKE와 같은 기준으로 정렬해두면 됩니다.

s3-like.ts (이어서)

db.run("CREATE INDEX idx_nocase ON members (email COLLATE NOCASE)");
db.run("ANALYZE");

probe("D. NOCASE 인덱스를 만든 뒤", "SELECT * FROM members WHERE email LIKE 'user49999%'");

결과

D. NOCASE 인덱스를 만든 뒤
  실행 계획: SEARCH members USING INDEX idx_nocase (email>? AND email<?)
  결과 행수: 11
  소요 시간: 0.014ms

38.667ms가 0.014ms가 됐습니다. 2,700배 넘는 차이인데, 쿼리는 한 글자도 바꾸지 않았어요. 인덱스의 정렬 규칙만 LIKE에 맞춰준 겁니다.

물론 와일드카드가 앞에 오는 B번은 어떤 인덱스로도 구제할 수 없습니다. 흩어진 값을 한 구간으로 묶을 방법이 없으니까요. 이런 검색이 필요하다면 SQLite FTS5로 전문 검색 구현하기에서 다룬 역색인을 써야 합니다.

OR는 모든 가지에 인덱스가 있어야 합니다

조건을 AND로 묶을 때와 OR로 묶을 때는 인덱스 사용 방식이 완전히 다릅니다.

s4-or.ts

only(["idx_city", "(city)"], ["idx_age", "(age)"]);

probe("A. AND로 묶기", "SELECT * FROM members WHERE city = ? AND age = ?", "서울", 30);
probe("B. OR로 묶기", "SELECT * FROM members WHERE city = ? OR age = ?", "서울", 30);
probe("C. OR인데 한쪽만 인덱스", "SELECT * FROM members WHERE city = ? OR name = ?", "서울", "사용자1");

결과

A. AND로 묶기
  실행 계획: SEARCH members USING INDEX idx_age (age=?)
  결과 행수: 2,000
  소요 시간: 4.663ms

B. OR로 묶기
  실행 계획: MULTI-INDEX OR
             INDEX 1
             SEARCH members USING INDEX idx_city (city=?)
             INDEX 2
             SEARCH members USING INDEX idx_age (age=?)
  결과 행수: 108,000
  소요 시간: 108.684ms

C. OR인데 한쪽만 인덱스
  실행 계획: SCAN members
  결과 행수: 100,001
  소요 시간: 104.519ms

A번은 지난 글에서 본 대로입니다. AND는 조건을 좁혀가는 것이라 인덱스 하나로 후보를 줄인 뒤 나머지를 확인하면 되죠.

B번의 MULTI-INDEX OR가 흥미롭습니다. OR는 어느 한쪽만 만족해도 결과에 포함되니 후보를 좁힐 수가 없어요. 그래서 각 조건을 인덱스로 따로 조회한 다음 결과를 합칩니다. 인덱스를 두 번 뒤지는 셈이죠.

주목할 건 C번입니다. name에 인덱스가 없다는 이유만으로 city 쪽 인덱스까지 통째로 버려지고 풀 스캔이 됐습니다. OR로 이어진 조건 중 하나라도 인덱스가 없으면 어차피 전체를 훑어야 하니, 나머지를 인덱스로 조회할 이유가 사라지거든요.

AND는 조건 하나만 인덱스를 타도 이득을 보지만, OR는 모든 조건이 인덱스를 타야 이득을 봅니다. 조건 목록에 인덱스 없는 컬럼이 슬쩍 끼어들어 전체를 무너뜨리는 일이 실무에서 생각보다 자주 일어나요.

정렬 방향이 섞이면 반만 일합니다

인덱스는 검색뿐 아니라 정렬에도 쓰입니다. 이미 정렬된 순서로 저장되어 있으니 읽어오기만 하면 되니까요. 그런데 여기에도 함정이 있습니다.

s5-order.ts

only(["idx_city_age", "(city, age)"]);

probe("A. 인덱스와 같은 방향", "SELECT * FROM members ORDER BY city ASC, age ASC LIMIT 20");
probe("B. 뒤 컬럼만 반대 방향", "SELECT * FROM members ORDER BY city ASC, age DESC LIMIT 20");
probe("C. 둘 다 반대 방향", "SELECT * FROM members ORDER BY city DESC, age DESC LIMIT 20");

결과

A. 인덱스와 같은 방향
  실행 계획: SCAN members USING INDEX idx_city_age
  결과 행수: 20
  소요 시간: 0.024ms

B. 뒤 컬럼만 반대 방향
  실행 계획: SCAN members USING INDEX idx_city_age
             USE TEMP B-TREE FOR LAST TERM OF ORDER BY
  결과 행수: 20
  소요 시간: 118.809ms

C. 둘 다 반대 방향
  실행 계획: SCAN members USING INDEX idx_city_age
  결과 행수: 20
  소요 시간: 0.033ms

C번이 의외입니다. 인덱스는 오름차순으로 만들었는데 내림차순 정렬도 빠르네요. 정렬된 목록을 뒤에서부터 읽으면 그만이니 당연한 일이긴 합니다.

문제는 B번입니다. 앞 컬럼은 오름차순, 뒤 컬럼은 내림차순으로 방향이 섞이자 118.809ms로 뛰었어요. A번의 4,900배가 넘습니다. USE TEMP B-TREE FOR LAST TERM OF ORDER BY가 그 대가인데요. 마지막 정렬 기준만큼은 인덱스로 해결하지 못해 임시 B-Tree를 만들어 정렬했다는 뜻입니다.

인덱스를 앞에서 읽든 뒤에서 읽든, 어느 쪽이나 city와 age가 같은 방향으로 묶여 나옵니다. 도시는 오름차순인데 그 안에서 나이만 내림차순인 순서는 저장해둔 적이 없으니 그때그때 만들어야 하는 거죠.

이런 정렬이 자주 필요하다면 인덱스를 만들 때 방향을 지정하면 됩니다. CREATE INDEX idx ON members (city ASC, age DESC)처럼요.

SEARCH가 떴다고 안심할 수는 없습니다

지금까지는 SCAN으로 떨어지는 경우를 봤는데, 마지막으로 반대 상황도 짚고 가겠습니다. 실행 계획에 SEARCH가 떴는데도 느린 경우입니다.

s6-selectivity.ts

only(["idx_city", "(city)"]);

probe("A. 흔한 값 조회", "SELECT * FROM members WHERE city = ?", "서울");
probe("B. 같은 조건으로 개수만", "SELECT count(*) FROM members WHERE city = ?", "서울");

결과

A. 흔한 값 조회
  실행 계획: SEARCH members USING INDEX idx_city (city=?)
  결과 행수: 100,000
  소요 시간: 93.713ms

B. 같은 조건으로 개수만
  실행 계획: SEARCH members USING COVERING INDEX idx_city (city=?)
  결과 행수: 1
  소요 시간: 1.872ms

A번은 인덱스를 제대로 탔습니다. 그런데도 93ms가 걸렸어요. 서울 사람이 전체의 20%인 10만 명이라, 찾는 건 빨랐지만 그 10만 행을 원본 테이블에서 하나씩 꺼내오는 데 시간이 다 갔거든요.

B번이 같은 조건인데도 50배 빠른 게 그 증거입니다. 개수만 세면 되니 원본 테이블에 갈 일이 없어서, 순수하게 찾는 비용만 남은 겁니다.

정리하면 인덱스는 찾는 비용을 줄여주지 가져오는 비용을 줄여주지는 않습니다. 결과가 전체의 상당 부분을 차지하는 쿼리라면 인덱스를 아무리 잘 걸어도 극적인 개선은 어려워요. 이럴 때는 인덱스를 손보는 대신 조건을 더 좁히거나, 페이지네이션으로 가져오는 양 자체를 줄이는 쪽이 답입니다.

마치며

인덱스가 있는데도 안 타는 경우들을 정리해 볼게요.

WHERE 절에서 컬럼에 함수나 연산, 타입 변환을 적용하면 인덱스는 무력해집니다. 정렬해둔 건 원래 컬럼 값이지 가공된 값이 아니니까요. 꼭 필요하다면 표현식 인덱스로 그 계산식 자체를 정렬해두면 됩니다. 패턴 검색은 앞부분 일치일 때만 범위 조건으로 바뀌어 인덱스를 타는데, 인덱스의 정렬 규칙이 비교 방식과 맞아야 한다는 조건까지 붙습니다. OR로 묶인 조건은 하나라도 인덱스가 없으면 전부 무용지물이 되고, 정렬 방향이 섞이면 임시 정렬이 끼어듭니다.

무엇보다 실행 계획에 SEARCH가 떴다고 끝이 아니라는 점을 기억하시면 좋겠습니다. 인덱스는 찾는 비용만 줄여주니까요. 느린 쿼리를 만나면 EXPLAIN QUERY PLAN으로 인덱스를 탔는지 먼저 확인하고, 탔는데도 느리다면 이번엔 가져오는 행 수를 의심해 보세요.

SQLite가 어떤 경우에 인덱스를 포기하는지 더 알고 싶으시다면 SQLite 쿼리 플래너 문서에 LIKE 최적화 조건과 OR 처리 방식이 조건별로 정리되어 있으니 참고해 보세요.

This work is licensed under CC BY 4.0

실험 준비

컬럼을 가공하면 인덱스가 죽습니다

타입을 바꾸는 것도 가공입니다

LIKE는 앞부분 일치라도 조건이 붙습니다

OR는 모든 가지에 인덱스가 있어야 합니다

정렬 방향이 섞이면 반만 일합니다

SEARCH가 떴다고 안심할 수는 없습니다

마치며

개발자를 위한 뉴스레터

관련 포스트

데이터베이스 인덱스는 어떻게 조회를 빠르게 만들까?

복합 인덱스는 왜 컬럼 순서가 중요할까?

자바스크립트에서 SQLite 데이터베이스 사용하기

WorkOS AuthKit으로 최신 MCP 인증 붙이기

SQLite FTS5로 전문 검색 구현하기

실시간 단방향 통신을 위한 Server-Sent Events(SSE)