검색이 느려서 인덱스를 추가했는데 전혀 빨라지지 않은 이유

문제 발생

제목 검색이 느려서 인덱스를 만들었는데 실행 시간이 그대로였습니다.

CREATE INDEX idx_title ON dev_notes (title);
SELECT * FROM dev_notes WHERE title LIKE '%리액트%';

EXPLAIN을 보니 여전히 전체 스캔이었습니다.

원인 분석

인덱스는 정렬된 순서를 이용하는 자료구조입니다. 값의 앞부분을 알아야 범위를 좁힐 수 있는데, %로 시작하면 그 정보가 없습니다.

MySQL 문서가 두 경우를 나눠 적습니다. LIKE의 인자가 와일드카드로 시작하지 않는 상수 문자열이면 B-tree 인덱스를 쓸 수 있습니다. 문서의 예시대로 LIKE 'Patrick%'는 'Patrick' <= key_col < 'Patricl' 범위의 행만 검사합니다.

반대로 인덱스를 쓸 수 없는 경우도 명시돼 있습니다.

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';   -- 선행 와일드카드
SELECT * FROM tbl_name WHERE key_col LIKE other_col;      -- 상수가 아님

이유는 각각 LIKE 값이 와일드카드로 시작하기 때문, 그리고 LIKE 값이 상수가 아니라 컬럼을 참조하기 때문입니다.

즉 인덱스가 안 쓰인 게 아니라 쓸 수 없는 질의였습니다.

해결 방안

1. 접두어 검색으로 충분한지 먼저 봅니다. 자동완성처럼 앞부분 일치가 요구라면 그대로 인덱스를 탑니다.

WHERE title LIKE '리액트%'

2. 부분 문자열 검색이 요구라면 전문 검색을 씁니다. MySQL/MariaDB의 FULLTEXT 인덱스와 MATCH ... AGAINST가 이 목적의 도구입니다. 한국어는 형태소 분석기나 n-gram 파서 설정이 필요하므로, 도입 전에 실제 데이터로 결과 품질을 확인합니다.

3. 데이터 양을 먼저 확인합니다. 수천~수만 행 규모라면 LIKE '%...%'도 실질적으로 충분히 빠릅니다 — 이 저장소의 노트 검색이 그 경우입니다. 측정 없이 전문 검색을 도입하면 복잡도만 늘어납니다.

4. 검색 대상 컬럼을 좁힙니다. 본문 전체가 아니라 제목·요약만 검색해도 되는 요구인지 확인하면, 스캔 비용 자체가 줄어듭니다.

5. 컬럼끼리 비교하는 LIKE를 피합니다. 문서가 두 번째로 든 경우이고, 조인 조건에 이런 패턴이 있으면 인덱스를 전혀 못 씁니다.

6. 정말 커지면 전용 검색 엔진을 검토합니다. 다만 그것은 별도 인프라와 색인 동기화 비용을 들이는 결정이라, 그 비용을 낼 만큼 검색이 제품의 핵심일 때 합니다.

공식 문서

• MySQL: Comparison of B-Tree and Hash Indexes
• MySQL: Full-Text Search Functions