발단
APM을 보다가 고객 이름 검색 API 응답 시간이 심상치 않은 걸 발견했다.
평균적으로 1.6초, 결과가 많이 나오는 이름으로 검색하면 3초씩 걸리고 있었다.
API 서버 문제는 아니었다. 쿼리 실행 계획을 까보니 원인은 단순했다.
WHERE name LIKE '%길동'이거 하나였다.
왜 느린 건데?
MySQL에서 LIKE 검색은 패턴 위치에 따라 인덱스를 타냐 못 타냐가 완전히 갈린다.
| 패턴 | 예시 | 인덱스 활용 |
|---|---|---|
| 전방 일치 (Starts with) | name LIKE '홍길%' | O (Index Range Scan) |
| 후방 일치 (Ends with) | name LIKE '%길동' | X (Full Table Scan) |
홍길%처럼 앞이 고정된 패턴은 B-Tree 인덱스에서 시작점을 딱 찾아서 Range Scan을 탄다.
근데 %길동처럼 앞에 %가 붙으면 어디서부터 시작해야 할지 알 수가 없다.
결국 인덱스를 포기하고 테이블을 처음부터 끝까지 다 읽는다.
이름 검색 특성상 "길동"처럼 이름(뒤) 부분만 입력해서 검색하는 경우가 꽤 많다.
데이터가 쌓일수록 이 쿼리는 당연히 점점 느려질 수밖에 없는 구조였다.
아이디어: 이름을 뒤집어서 저장하면?
%길동을 못 찾는 이유는 인덱스가 앞에서부터 정렬되어 있기 때문이다.
그럼 이름을 뒤집어서 저장해두면?
%길동을 찾고 싶다 → 뒤집으면 동길% → 이건 전방 일치! → 인덱스를 탄다!
생각보다 간단한 아이디어인데 효과가 있다.
참고: Optimising "Ends With" searches with REVERSE – SQLServerCentral
구현
역순 컬럼 추가
MySQL 5.7+의 Generated Column을 쓰면 insert/update 시 자동으로 REVERSE() 값을 유지해 준다.
따로 트리거나 애플리케이션 로직을 건드릴 필요가 없다.
ALTER TABLE User
ADD COLUMN name_reversed VARCHAR(100) GENERATED ALWAYS AS (REVERSE(name)) STORED,
ADD COLUMN phone_reversed VARCHAR(20) GENERATED ALWAYS AS (REVERSE(phone_number)) STORED;STORED를 꼭 써야 한다.
VIRTUAL은 조회할 때 그때그때 계산만 하기 때문에 인덱스를 걸 수 없다.
인덱스 생성
CREATE INDEX ix_name_reversed ON User (name_reversed);
CREATE INDEX ix_phone_reversed ON User (phone_reversed);검색 쿼리
전방 일치(이름 앞부분 검색)와 역순 전방 일치(이름 뒷부분 검색)를 UNION ALL로 합친다.
SET @param_name = '홍길동';
SELECT u.*
FROM User u
WHERE u.UserId IN (
-- '홍길동OO' 형태: 기존 name 인덱스 활용
SELECT u.UserId
FROM User u
WHERE u.name LIKE CONCAT(@param_name, '%')
UNION ALL
-- 'O홍길동' 형태: name_reversed 인덱스 활용
SELECT u.UserId
FROM User u
WHERE u.name_reversed LIKE CONCAT(REVERSE(@param_name), '%')
);결과
적용하고 나서 응답 시간이 확 줄었다.
| 시나리오 | 개선 전 | 개선 후 |
|---|---|---|
| 일반 이름 검색 | ~1,600ms | ~60ms |
| 결과 많은 이름 | ~3,000ms | ~100ms |
1.6초 → 60ms, 약 96% 단축 😮
결과가 많은 케이스도 3초에서 100ms로 줄었다.
데이터가 많아서 어쩔 수 없을 거라고 생각했는데, 인덱스 하나의 차이가 이 정도였다.
정리
LIKE '%키워드'후방 일치 검색은 인덱스를 못 타서 Full Table Scan이 발생한다.- MySQL의 Generated Column(STORED) 으로 역순 컬럼을 만들고 인덱스를 걸면, 후방 일치도 전방 일치처럼 인덱스를 활용할 수 있다.
- 이름뿐 아니라 휴대폰 번호 같은 다른 컬럼에도 똑같이 적용할 수 있다.
- 다만 중간 포함 검색(
%키워드%)은 이 방법으로 해결이 안 된다.
