검색이 전부 훑고 있었다

상품 검색이 느렸다. 인덱스를 확인하니 걸려 있었다.

SHOW INDEX FROM products;
-- name 에 인덱스 있음

Open Table of contents

원인 — 앞에 와일드카드가 있었다

EXPLAIN 을 봤다.

type   ALL
key    NULL

인덱스를 안 쓰고 있었다. 쿼리를 보니 이랬다.

WHERE name LIKE '%검색어%'

인덱스는 앞에서부터 정렬돼 있어서 앞이 정해지면 그 구간만 보면 되는데 앞이 열려 있으면 어디에 있을지 모른다. 그래서 전부 봐야 한다.

앞의 % 를 떼면 LIKE '검색어%' 가 되어 인덱스를 탄다. 그런데 검색 요구가 중간 낱말도 찾는 것이었다.

"타이어" 로 검색
    ↓
"겨울용 타이어 세트" 도 나와야 함

빠르게 만드는 것과 원하는 결과를 주는 것이 부딪쳤다. 어느 한쪽을 포기할 수 없어서 다른 방법을 찾아야 했다.

MySQL 에 전문 검색 기능이 있었다.

ALTER TABLE products ADD FULLTEXT (name);
SELECT * FROM products WHERE MATCH(name) AGAINST ('검색어');

써 보니 FULLTEXT 파서가 공백을 구분자로 삼아 단어를 가른다. 단어가 일치하면 잘 찾는데 단어 중간은 못 찾았다.

"겨울용타이어"  →  한 단어
"타이어" 로 검색  →  안 나옴

상품명은 낱말이 붙어 있는 경우가 많아 중간 낱말이 색인에 안 잡혔다. 우리 자료의 모양과 그 기능이 전제하는 모양이 달랐던 것이다.

기능이 있다는 것과 우리 자료에 맞는다는 것은 다른 문제였다. 그대로 쓰면 검색이 되기는 하는데 안 나오는 것이 많아진다.

찾아보니 글자 단위로 잘라 넣는 방법이 있었다.

"타이어"  →  "타이", "이어"

두 글자씩 겹쳐 잘라 두면 중간도 찾힌다. 원본과 별개로 잘라 넣는 컬럼을 뒀다.

name        원본
name_index  잘라 넣은 것

name_index 에 FULLTEXT 를 걸고 검색은 그쪽을 본다. 저장할 때 잘라서 같이 넣었다.

$data['name_index'] = tokenize($data['name']);

검색어도 tokenize 로 같은 방식으로 잘라야 맞는다. 넣는 방식과 찾는 방식이 같아야 결과가 나오고 한쪽만 바꾸면 아무것도 안 나오는데 그 상태는 오류로 보이지 않는다.

대신 저장 크기가 두세 배로 늘었다. 다만 검색만 name_index 를 쓰고 보여줄 때는 name 을 쓰므로 사용자는 모른다.

한 번 실수가 있었다. 상품명을 고치는 화면에서 name 만 고치고 name_index 를 안 고쳤다.

옛 이름으로 검색해야 나오는 상태가 됐다. 그 상품만 검색에 안 나오는 것이라 눈에 띄지도 않는다.

public function save($data) {
    $data['name_index'] = tokenize($data['name']);
    // 저장
}

저장을 save 한 곳으로 모으고 직접 저장하지 않고 이걸 거치게 했다. 다만 SQL 을 직접 쓰는 곳이 남아 있어서 전부 찾아 고쳐야 했다.

어긋난 것이 있나 주기적으로 확인하는 것도 넣었다.

SELECT id FROM products
 WHERE name_index <> <원본을 잘라 넣은 값>;

0이 아니면 어긋난 것이다. 검색 전용 도구도 있었지만 규모가 크지 않아 MySQL 안에서 해결하고 언제 옮길지 조건을 적어 뒀다.

검색 요구가 복잡해지면
데이터가 크게 늘면