LIKE '%검색어%' - 인덱스가 있어도 다 훑는다

게시판에 검색창을 하나 붙였다. 처음엔 잘 돌았는데 글이 십만 건이 되자 검색만 누르면 화면이 멈춘다.

검색창을 붙였는데 느려진다

코드는 대개 이렇게 생겼다.

SELECT * FROM posts
 WHERE content LIKE '%장바구니%';

느리길래 content에 인덱스를 만들었다. 그래도 똑같다. 실행 계획을 보면 인덱스를 아예 쳐다보지도 않는다.

여기서 흔히 나오는 결론이 “DB가 검색에 약하다”인데, 그 말은 반쯤만 맞다. DB 인덱스는 이 질문에 답하도록 만들어진 물건이 아니다. 왜 아닌지를 알아야 다음 이야기가 전부 풀린다.

인덱스가 빠른 이유는 정렬이다

인덱스 글에서 본 것을 한 줄로 줄이면 이렇다. 인덱스가 빠른 건 값이 정렬돼 있기 때문이다.

정렬돼 있으면 찾는 값이 있을 자리를 곧장 짚을 수 있다. 사전에서 “장바구니”를 찾을 때 앞에서부터 넘기지 않고 ㅈ 근처를 펼치는 것과 같다. 이게 되는 이유는 사전이 첫 글자부터 정렬돼 있기 때문이다.

그러니 반대도 성립한다. 정렬을 못 쓰게 되는 순간 인덱스는 아무것도 아니다.

앞이 열리면 출발점이 없다

WHERE content LIKE '장바구니%'   -- 인덱스를 탄다
WHERE content LIKE '%장바구니%'  -- 못 탄다

위쪽은 “장바구니로 시작하는 값”을 찾는다. 정렬 순서대로 그 구간이 한 덩어리로 뭉쳐 있으니 거기로 점프하면 된다.

아래쪽은 다르다. 앞에 무엇이 오는지 모른다. 가나다장바구니일 수도 있고 주문 후 장바구니를일 수도 있다. 사전이 첫 글자로 정렬돼 있는데 첫 글자를 모르니 펼칠 자리가 없다. 남은 방법은 하나뿐이다. 처음부터 끝까지 전부 열어보는 것, 무식하게 다 해보는 방법이다.

데이터가 10배 늘면 시간도 10배 는다. 지금 느린 게 아니라 처음부터 안 되는 구조였고 데이터가 적어서 안 보였을 뿐이다.

책을 처음부터 넘기고 있다

이 시리즈는 처음부터 끝까지 책 한 권을 따라간다.

두꺼운 기술서에서 “격리 수준”이라는 말이 나오는 쪽을 찾고 싶다. 방법이 둘이다. 1쪽부터 마지막 쪽까지 눈으로 훑거나, 책 뒤에 붙은 찾아보기를 펴서 “격리 수준 → 214, 301”을 읽거나.

LIKE '%장바구니%'는 앞엣것이다. 700쪽짜리 책을 매번 처음부터 넘긴다. 뒤로 갈수록 이야기 전부가 “그러면 찾아보기는 어떻게 생겼고 누가 만드나”로 이어진다.

본문은 한 행에 낱말이 수백 개다

그런데 왜 애초에 DB 인덱스로 이걸 못 하나. 인덱스를 잘 만들면 되는 것 아닌가.

문제는 더 아래에 있다. DB 인덱스는 “행 하나에 키 하나”를 전제한다. email 컬럼에 인덱스를 만들면 행마다 이메일 하나가 키가 되고, 그 키들을 정렬해 늘어놓는다. 깔끔하다.

본문은 그렇게 안 생겼다.

content에 인덱스를 만들어도 키가 되는 건 본문 전체 문자열 하나다. "주문 후 장바구니를 비웠습니다"라는 긴 문자열 하나가 통째로 하나의 키다. 그 안의 장바구니는 키가 아니라 문자열 중간의 글자 몇 개일 뿐이다.

우리가 원하는 건 장바구니가 그 자체로 키인 인덱스다. 본문 한 건이 낱말 수백 개를 키로 만들어 내야 한다. 이건 인덱스를 더 잘 만드는 문제가 아니라 다른 물건을 만드는 문제다.

다 훑어도 결과가 이상하다

느린 것만 문제가 아니다. LIKE는 낱말을 모른다. 연속한 글자만 본다.

WHERE content LIKE '%사과%'

여기에 걸리는 것들이다.

• 사과를 샀다 - 맞다
• 사과문을 발표했다 - 원하지 않았다
• 풋사과 재배법 - 애매하다
• 대법원 판결에 사과했다 - 다른 낱말이다

반대로 못 찾는 것도 있다. 사용자가 사과 주스라고 치면 LIKE '%사과 주스%'는 사과가 든 주스를 못 찾는다. 사이에 다른 글자가 끼면 연속이 아니기 때문이다. 두 낱말을 각각 찾아 둘 다 든 문서를 고르는 일을 LIKE로 하려면 조건을 손으로 AND로 이어 붙여야 하고, 그러면 훑는 일이 낱말 수만큼 늘어난다.

그리고 결과에 순서가 없다. 500건이 걸렸을 때 무엇이 가장 관련 있는지를 LIKE는 말해주지 못한다. 조건에 맞거나 안 맞거나 둘 중 하나다.

두 인덱스는 서로 다른 것을 찾는다

여기서 선을 그어 두자. 둘 다 “인덱스”라고 불리는 탓에 계속 헷갈리는 자리다.

DB 인덱스는 주문번호 = 'A1024'처럼 정확히 아는 값으로 행을 집어내는 데 최적이다. 그건 잘한다.

역색인은 장바구니라는 낱말이 어느 문서들 안에 들어 있나를 답한다. 답이 하나가 아니라 목록이고, 그래서 순서를 매기는 일까지 따라붙는다.

같은 도구로 둘 다 하려니 안 되는 것이고, 도구를 잘못 고른 것이지 도구가 나쁜 게 아니다.

미봉책이 어디까지 버티나

엔진을 들이기 전에 LIKE를 살려보려는 시도가 몇 가지 있다. 어디까지 가는지 알아둘 값은 있다.

앞을 고정한다. LIKE '장바구니%'로 바꿀 수 있으면 인덱스를 탄다. 자동완성이나 아이디 검색처럼 앞에서부터 치는 게 자연스러운 자리에서는 이걸로 충분하다. 본문 검색에는 안 맞는다.

뒤집어서 저장한다. 문자열을 거꾸로 뒤집은 컬럼을 하나 더 두면 %장바구니가 구니바장%가 되어 인덱스를 탄다. 접미사 검색은 풀린다. 하지만 가운데가 열린 검색은 여전히 못 푼다.

글자 조각으로 쪼개 인덱스를 만든다. 본문을 두세 글자씩 잘라 그 조각들을 인덱스로 만드는 방법이 있다. 사실 이건 미봉책이라기보다 역색인의 아주 단순한 형태다. 다음 이야기가 여기서 이어진다.

실무에서: LIKE로 버티는 선

LIKE를 당장 걷어내야 한다는 말은 아니다. 버티는 조건이 꽤 뚜렷하다.

버틸 만한 자리는 이렇다. 데이터가 수만 건 아래고, 검색이 자주 안 불리고, 관리자 화면처럼 몇 초를 기다려 줄 사람이 쓰고, 검색 대상이 본문이 아니라 제목이나 이름처럼 짧은 값인 경우다. 짧으면 훑어도 싸다.

무너지는 신호도 뚜렷하다. 검색이 서비스의 주 기능이 되거나, 여러 낱말을 한꺼번에 넣기 시작하거나, “왜 이게 위에 안 뜨냐”는 말이 나오거나, 검색 하나가 DB의 CPU를 통째로 먹기 시작할 때다. 마지막 것이 특히 위험하다. 검색은 훑는 동안 DB의 자원을 쓰기 때문에, 느린 검색 하나가 검색과 상관없는 주문 처리까지 같이 끌어내린다.

정리

• LIKE '%...%'가 인덱스를 못 타는 건 앞을 모르면 정렬된 목록에서 출발점을 못 잡기 때문이다.
• 더 근본적인 이유는 따로 있다. DB 인덱스는 행 하나에 키 하나를 전제하는데, 본문은 한 행이 낱말 수백 개를 품는다.
• 그래서 이건 인덱스를 잘 만드는 문제가 아니라 낱말이 키인 다른 물건을 만드는 문제다.
• 속도만 문제가 아니다. LIKE는 낱말 경계를 모르고, 여러 낱말을 다루기 어렵고, 결과에 순서를 매기지 못한다.
• 짧은 값·적은 데이터·낮은 빈도라면 LIKE로 버틸 수 있다. 그 셋 중 하나라도 깨지면 구조를 바꿔야 한다.