검색 기능 구현 시 DB성능에 관한 고찰🤔

LIKE ‘%키워드%’의 문제점

LIKE 연산자는 첫 번째 와일드카드 % 가 등장하기 전까지의 문자열로만 스캔할 인덱스 범위를 결정한다.

예를 들어 SEO%L이라는 조건절을 만든다면 첫 번째 와일드카드가 등장하기 전까지인 SEO까지만 인덱스 범위를 좁히는데 사용된다. 따라서 와일드 카드가 제일 첫번째 조건절로 온다면(%키워드%) 아무 인덱스를 타지 않는 것이다.

인덱스를 사용하기 위해 prefix 단어를 설정한다면 성능이 향상 될진 몰라도 원하는 검색 기능을 구현하기에는 제한적일 것이다.

LIKE 구문의 대체 FullText(a.k.a 전문 검색)

WHERE절에 MATCH와 AGAINST의 조합으로 조건절을 만들 수 있고, 옵션을 함께 설정할 수 있다. 사용할 수 있는 옵션값들은 아래와 같다.

MATCH (col1,col2,...) AGAINST (expr [search_modifier]) search_modifier: { IN NATURAL LANGUAGE MODE | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION | IN BOOLEAN MODE | WITH QUERY EXPANSION }

FullText검색을 위해서는 몇가지 준비 사항이 있다.

FULLTEXT 검색에 사용될 INDEX 추가

CREATE FULLTEXT INDEX 인덱스 명 on 테이블명(컬럼 명);

FULLTEXT 인덱스는 컬럼 타입이 VARCHAR, TEXT 같은 문자 열에 만 적용이 가능하다.

실제 검색 시에는 설정했던 컬럼을 모두 적어주어야 올바르게 인덱스를 사용하여 검색할 수 있다.

예를 들어 아래와 같은 인덱스를 생성했다고 한다면 WHERE 절에서 title과 body를 함께 검색해야한다.

CREATE FULLTEXT INDEX idx_ft_title_and_body on articles(title, body); SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('테스트' IN NATURAL LANGUAGE MODE)

그리고 fulltext검색 시에는 대/소문자를 구분하지 않는다.

Fulltext관련 변수 세팅 후 테이블 최적화

// 테이블 단위 조회는 innodb_ft_aux_table 변수에 대상 테이블 명시 필요. SET GLOBAL innodb_ft_aux_table = 'DB명/테이블명'; // 테이블 최적화 시 테이블 크기에 따라 오래걸릴 수 있기 때문에 fulltext에 관련한 최적화만 실행될 수 있도록 설정 SET GLOBAL innodb_optimize_fulltext_only=ON; // 인덱스 재설정 OPTIMIZE TABLE articles;

optimize를 통해 저장된 index 목록은 INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE 에서 확인 할 수 있다.

주의할 점

fulltext검색 시 기본 키워드 검색 글자 수가 정해져 있다. 전역 변수 설정을 확인하고 필요 시 변경하여 사용한다.

mysql> show global variables like 'innodb_ft_min%'

InnoDB기준 default값 innodb_ft_min_token_size(3) / innodb_ft_max_token_len(64)

fulltext index를 설정하고 실제 쿼리를 날려보면 like '%키워드%'와는 다른 결과 값이 나올 수 있다. 이는 fulltext에서는 기본적으로 단어 단위로 인덱싱을 저장하기 때문에 띄어쓰기가 되지 않은 단어에 대해서는 정확한 결과를 얻을 수 없기 때문이다.

 

인덱싱으로 저장되어 있는 단어 jessie를 검색하면 동일한 결과값이지만 인덱싱에 저장되지 않는 단어는 검색되지 않는다.

N-gram Parser

위와 같은 문제는 n-gram parser를 적용하여 해결할 수 있다. n-gram 모델(혹은 알고리즘)으로 불리는 이 방식은 주어진 문자열에서 n개 연속된 요소를 추출하는 것이다.

N=1 : 'a', 'b', 'c', 'd'; N=2 : 'ab', 'bc', 'cd'; N=3 : 'abc', 'bcd'; N=4 : 'abcd';

n-gram parser는 내가 설정한 n의 값 단위로 단어를 쪼개어 인덱싱을 모두 저장한다.

MySQL 5.7 버전부터는 플러그인 형태의 fulltext parser를 지원하기 시작했고, MySQL 5.7.6 버전부터는 bulit-in으로 가지고 있기 때문에 추가적인 설정 없이 n-gram parser를 사용할 수 있다.

n-gram parser는 innodb_ft_min_token_size 와 별개로 ngram_token_size 라는 시스템 변수를 가진다. 기본 값은 2이다.

토큰을 나눌때 공백은 항상 hard-coded stopword로 무시되어 토큰을 나누는 기준으로 포함되지 않는다.

n-gram parser를 사용하고 싶다면 기존 FULLTEXT INDEX추가하는 구문에 WITH PARSER ngram만 추가해주면 된다.

CREATE FULLTEXT INDEX 인덱스 명 on 테이블명(컬럼 명) WITH PARSER ngram;