이 포스팅에서는 MySQL의 FullText Search에 대해 주로 다뤄보고자 한다.
웹 어플리케이션을 설계할 때, 고려할 점은 참 많다. 그 중에서도 이번에는 Restful API에서 RDBMS 를 사용할 때 쿼리 조회 성능을 올리는 방법에 대해 소개하려고 한다. 검색에 대해 패턴 일치 검색 기능(LIKE 기능)을 사용한 조회 쿼리의 경우 조회 성능이 가장 낮다고 할 수 있다. 하지만 놀랍게도 많은 초심자들은 이 쿼리에는 문제가 없다고 생각한다(그리고 다른 곳에서 문제를 찾으려고 한다). 패턴 일치 검색 기능은 %를 사용함으로써 컬럼에 인덱스가 지정되어 있어도 인덱스를 사용하지 못할 수도 있다.
RDBMS는 정형화된 데이터를 저장하기에 매우 편리한 데이터베이스지만, 설계와 사용에서의 아주 세심한 주의를 요한다.
FullText Search(전문 검색)
게시물의 내용이나 제목 등과 같이 문장이나 문서의 내용에서 키워드를 검색하는 기능이다. 전문 검색은 이름이나 별명(닉네임)과 같은 단어에서 일부만 일치하는 사용자를 검색하는 기능으로도 사용할 수 있다. FullText Search는 아래와 같이 3가지 종류의 검색이 있다.
Natural Search(자연어 검색)
검색 문자열을 단어 단위로 분리한 후, 해당 단어 중 하나라도 포함되는 행을 찾는다.
Boolean Mode Search(불린 모드 검색)
검색 문자열을 단어 단위로 분리한 후, 해당 단어가 포함되는 행을 찾는 규칙을 추가적으로 적용하여 해당 규칙에 매칭되는 행을 찾는다.
Query Extenstion Search(쿼리 확장 검색)
2단계에 걸쳐서 검색을 수행한다. 첫 단계에서는 자연어 검색을 수행한 후, 첫번째 검색의 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두번째 검색을 수행한다.
FullText Search를 사용하기 위해 필요한 조건
1. FullText 인덱스 생성
FullText 인덱스는 MyISAM 엔진을 사용하는 테이블에 대해서만 생성할 수 있다.
컬럼 데이터 타입은 반드시 Text, Binary Char, Varchar 타입을 가져야 한다.
한글 데이터를 검색하려면 테이블 인코딩으로 utf8을 사용해야 한다.테이블을 생성할 때 생성할 수도 있고, 이미 만들어진 테이블에 대해서도 인덱스를 생성할 수 있다.
create table posts(
id bigint(100) NOT NULL AUTO_INCREMENT,
gtitle text NOT NULL,
gdesc text NOT NULL,
....
PRIMARY KEY (id),
FULLTEXT KEY gtitle (gtitle)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;위 쿼리는 posts라는 테이블을 만들고, gtitle(제목) 컬럼을 FullText 검색을 하도록 생성하는 쿼리이다.
이미 만들어진 테이블에 대해 FullText 인덱스를 생성해야 한다면, 아래와 같이 테이블을 변경할 수 있다.
alter table posts add FULLTEXT(gtitle);만일 본문에 대해서도 FullText 검색을 지원하려면 아래와 같은 쿼리를 수행해야 한다.
alter table posts add FULLTEXT(gtitle, gdesc);
2. FullText 검색 엔진 설정
검색어가 4글자 이하로 너무 짧은 경우, 아무런 검색 결과가 나오지 않는다. 만약 이 이하의 검색어를 지원하려면, 최소 검색어 길이 값을 수정해야 한다. 아래는 my.cnf 설정파일에서 최소 검색어 길이를 2로 수정한다는 내용이다.
# my.cnf 파일
ft_min_word_len=2만일 FullText 인덱스를 생성한 경우라면 인덱스를 삭제한 후 재생성해야 한다.
혹은 아래와 같이 인덱스를 갱신할 수 있다.
REPAIR TABLE posts QUICK;
References