LIKE '%검색어%' 는 인덱스를 못 쓴다

검색 기능을 LIKE '%키워드%' 로 만들었어요. 데이터가 적을 때는 잘 돌았고, 인덱스도 걸어 뒀어요.

데이터가 늘면서 검색이 몇 초씩 걸려요. 인덱스는 분명히 있는데 EXPLAIN 을 보면 순차 스캔이에요.

더 나쁜 건 이게 한 사람만 느린 게 아니라는 점이에요. 검색 요청이 몇 개만 동시에 들어와도 DB 전체가 느려져요.

검색창은 사용자가 아무 문자열이나 넣을 수 있는 입구예요. 사실상 누구나 풀스캔을 유발할 수 있는 상태고, 악의가 없어도 그냥 한 글자를 넣는 것만으로 그렇게 됩니다. 검색이 느린 게 아니라 검색이 서비스를 느리게 만드는 단계입니다.

왜 그런가

B-tree 인덱스는 정렬된 순서로 값을 저장해요. 그래서 "앞에서부터 이렇게 시작하는 값"은 빠르게 찾을 수 있어요. 사전에서 단어를 찾는 것과 같아요.

LIKE '키워드%' 는 앞이 고정이라 인덱스를 탈 수 있어요. 그런데 LIKE '%키워드%' 는 앞이 열려 있어요. 사전에서 "가운데에 이 글자가 들어간 단어"를 찾으라는 요구와 같아서, 정렬이 아무 도움이 안 돼요. 결국 전부 훑어야 합니다.

그래서 데이터 양에 비례해서 느려져요. 10만 행에 0.1초면 천만 행에는 10초예요. 그리고 이건 캐시로 덮이지도 않아요 — 검색어가 매번 다르니까요.

한국어는 여기에 문제가 하나 더 있어요. 영어는 공백으로 단어가 나뉘지만 한국어는 조사가 붙어요. "서울에서"와 "서울로"와 "서울"을 같은 단어로 보려면 형태소 분석이 필요해요.

그래서 전문 검색을 설정하는 게 번거로워 보이고, 결국 단순 LIKE 를 쓰게 돼요. 그게 이 문제의 진짜 출발점입니다. 기술적 무지가 아니라 합리적인 지름길이 나중에 비용이 되는 형태예요.

코드로 보면

앞이 열려 있으면 인덱스가 무력하다:

코드

✕ SELECT * FROM posts WHERE title LIKE '%검색어%';
✕ -- 앞이 열려 있어 B-tree 를 못 쓴다 → 순차 스캔

✓ SELECT * FROM posts WHERE title LIKE '검색어%';
· 앞이 고정이면 인덱스를 탄다

· PostgreSQL — 트라이그램 인덱스면 %…% 도 인덱싱된다
✓ CREATE EXTENSION pg_trgm;
✓ CREATE INDEX idx_title_trgm ON posts
✓ USING gin (title gin_trgm_ops);

· 전문 검색이 목적이라면 그쪽 도구를 쓴다
✓ CREATE INDEX idx_title_fts ON posts
✓ USING gin (to_tsvector('simple', title));

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

해결책이 세 갈래인데, 어느 쪽인지는 무엇을 만들려는지에 달려 있어요.

첫째, 접두사 검색으로 충분한 경우. 자동완성처럼 앞에서부터 매칭하면 되는 기능이면 LIKE '검색어%' 로 바꾸는 것만으로 끝나요. 뒤에서부터 찾아야 하면 문자열을 뒤집어 저장한 컬럼에 인덱스를 걸어 접두사 검색으로 바꾸는 기법도 있어요.

둘째, 부분 문자열 검색이 정말 필요한 경우. PostgreSQL 의 pg_trgm 확장이 이걸 풀어요. 문자열을 세 글자 조각으로 쪼개 인덱싱하니까 가운데 매칭도 인덱스로 처리돼요. 다만 인덱스가 커지고 쓰기가 느려지며, 짧은 검색어(세 글자 미만)에는 효과가 떨어져요.

셋째, 진짜 검색 기능인 경우. 관련도 순 정렬, 오타 교정, 동의어, 형태소 분석이 필요하다면 LIKE 로는 절대 안 돼요. PostgreSQL 의 전문 검색이나 Elasticsearch 같은 전용 엔진으로 가야 해요. 한국어는 형태소 분석기(은전한닢, 노리 등)가 붙어야 제대로 동작합니다.

가장 흔한 실수는 둘째와 셋째를 구분하지 않는 거예요. "검색이 느리다"를 인덱스 문제로 보고 트라이그램을 붙였는데, 사용자가 원한 건 관련도 순 정렬이었던 경우요. 인덱스는 빨라졌지만 결과가 여전히 쓸모없어요.

그리고 성능 이전에 정확성 문제가 있어요. LIKE 는 사용자가 입력한 % 나 _ 를 와일드카드로 해석해요. 이스케이프를 안 하면 사용자가 % 하나만 넣어 전체 행을 반환시킬 수 있어요. 대소문자와 유니코드 정규화도 콜레이션에 따라 다르게 동작합니다.

처방

먼저 무엇이 필요한지 정하세요. 접두사 매칭인지, 부분 문자열인지, 진짜 검색인지. 이 판단을 건너뛰면 어떤 처방도 헛돕니다.
접두사로 충분하면 LIKE '검색어%' 로 바꾸고 B-tree 인덱스를 쓰세요. 가장 싸고 확실해요.
부분 문자열이 필요하면 PostgreSQL 의 pg_trgm 을 검토하세요. GIN 인덱스로 %…% 매칭이 인덱싱돼요. 쓰기 비용과 인덱스 크기가 늘어나는 건 감수해야 합니다.
관련도·오타·동의어가 필요하면 전문 검색으로 가세요. 한국어면 형태소 분석기 설정이 필수예요. 이건 인덱스 튜닝이 아니라 별도 컴포넌트를 들이는 결정입니다.
어느 쪽이든 검색어 길이 하한을 두세요. 한 글자 검색은 어떤 방식으로도 대부분의 행을 반환해요. 두세 글자 미만은 거부하는 게 합리적입니다.
검색에 타임아웃과 동시 실행 제한을 거세요. 검색은 사용자가 임의 입력으로 부하를 만들 수 있는 입구예요. 다른 기능이 검색 때문에 죽지 않게 격리하세요.
사용자 입력의 % 와 _ 를 반드시 이스케이프하세요.

지금 확인해 보세요

정말 인덱스를 못 타고 있나:

코드

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM posts WHERE title LIKE '%검색어%';
✕ Seq Scan 이면 예상대로다
✕ shared read 가 테이블 전체 크기면 다 읽은 것이다

· 트라이그램을 붙인 뒤 다시 확인
✓ Bitmap Index Scan on idx_title_trgm 이어야 한다

· 검색이 전체 부하에서 차지하는 비중
SELECT round(total_exec_time) AS total_ms, calls,
       left(query, 60)
  FROM pg_stat_statements
 WHERE query ILIKE '%LIKE%'
 ORDER BY total_exec_time DESC LIMIT 10;

· 검색어 길이 분포 — 한 글자 검색이 얼마나 오나
✓ 접근 로그에서 q 파라미터 길이를 센다

핵심 한 줄앞이 열린 패턴은 인덱스가 도울 수 없다. 검색은 검색 도구로 하라.

왜 그런가

코드로 보면

실전에서는

처방

지금 확인해 보세요

이어서 읽기