LIKE 검색은 왜 인덱스를 못 타는가

이름·키워드 부분 일치 검색을 손본 주가 있었다. LIKE '%검색어%'를 걸어두고 인덱스를 만들었는데도 느렸다. 인덱스가 있는데 왜 안 탈까. 답은 인덱스의 자료구조 자체에 있다.

B-Tree는 “정렬된 사전”이다

관계형 DB의 일반 인덱스는 B-Tree다. B-Tree는 값을 정렬된 순서로 보관한다. 사전과 같다. “kim”으로 시작하는 단어는 사전에서 곧장 펼쳐 찾을 수 있다. 앞에서부터 비교해 범위를 좁히기 때문이다.

LIKE 'kim%' (접두 검색)은 이 구조와 맞는다. “kim”으로 시작하는 구간은 인덱스에서 연속된 범위이므로, DB는 그 시작점으로 seek한 뒤 범위를 스캔한다. 레인지 스캔이 가능하다.

반면 LIKE '%kim%' (선행 와일드카드)은 “어딘가에 kim이 든 단어”를 묻는다. 사전에서 이건 처음부터 끝까지 다 넘겨봐야 한다. 앞에서부터 비교할 기준점이 없으니 B-Tree로 범위를 좁힐 수 없다. 결국 풀 인덱스 스캔 또는 풀 테이블 스캔으로 떨어진다.

-- 인덱스 레인지 스캔 가능 (접두)
SELECT * FROM users WHERE name LIKE 'kim%';

-- 인덱스 무력화 (선행 와일드카드)
SELECT * FROM users WHERE name LIKE '%kim%';

-- 좌변 가공도 인덱스 무력화 (함수 적용)
SELECT * FROM users WHERE LOWER(name) LIKE 'kim%';

마지막 예시도 함정이다. 인덱스는 원본 name 값으로 정렬돼 있는데 LOWER(name)은 가공된 값이라 인덱스 순서와 무관해진다. 대소문자 무시 검색이 필요하면 collation을 case-insensitive로 두거나, 소문자 변환 결과를 별도 컬럼/함수 인덱스로 둔다.

collation이 검색을 바꾼다

collation은 문자 비교·정렬 규칙이다. utf8mb4_general_ci 같은 case-insensitive collation에서는 LIKE 'KIM%'이 ‘kim’도 매칭한다. 비교 자체가 대소문자를 무시하기 때문이다. 반대로 _bin collation은 바이트 단위 비교라 정확히 일치해야 한다. “왜 대문자로 검색하면 안 잡히지?”의 절반은 collation 문제다.

분기점: 언제 풀텍스트로 가는가

부분 일치(%kw%)가 핵심 요구사항이라면 LIKE는 본질적으로 한계다. 이때 풀텍스트 인덱스(역색인)로 넘어간다. 역색인은 문서를 토큰(단어)으로 쪼개 “단어 → 문서 목록” 매핑을 미리 만들어 둔다. 그래서 단어 단위 검색은 위치와 무관하게 인덱스를 탄다.

ALTER TABLE articles ADD FULLTEXT INDEX ft_body (body);
SELECT * FROM articles
WHERE MATCH(body) AGAINST('keyset pagination' IN BOOLEAN MODE);

단, 풀텍스트는 토큰 단위라 한국어 형태소 분석이나 부분 어절(예: “검색어”의 “색”)까지는 못 잡는다. 그 수준이 필요하면 전용 검색엔진(역색인 + 형태소 분석기)으로 가는 게 분기점이다.

운영 함정

1) 접두 검색으로 충분한데 %kw%를 쓰는 경우가 많다. 자동완성·이름 검색은 대개 접두 검색이면 된다. 요구사항을 kw%로 좁힐 수 있으면 인덱스를 살린다.

2) 작은 테이블에서 안 느리다고 방심하면 데이터가 쌓일 때 터진다. 풀스캔은 행 수에 선형 비례한다. 1만 건에선 멀쩡하던 %kw%가 100만 건에서 응답 지연을 만든다.

핵심 요약

• B-Tree는 정렬된 사전. 접두(kw%)는 범위 탐색되지만 선행 와일드카드(%kw%)는 비교 기준점이 없어 풀스캔이다.
• 좌변 함수 가공도 인덱스를 깬다. collation이 대소문자 매칭을 좌우한다.
• 부분 일치가 본질이면 풀텍스트(역색인)로, 형태소 수준이면 검색엔진으로 분기한다.

Q. name LIKE '%kim%'이 인덱스를 못 타는 이유는? A. B-Tree는 앞에서부터 정렬된 구조라 접두는 범위 탐색되지만, 앞에 와일드카드가 오면 시작 위치를 특정할 수 없어 전체 스캔으로 떨어진다.