MySQL FULLTEXT 검색 > Database

본문 바로가기
사이트 내 전체검색


회원로그인

오늘 59
어제 313
최대 618
전체 383,949
마음을 나누는 인터넷 일기장 - 통플 다이어리
Database

MySQL FULLTEXT 검색

페이지 정보

작성자 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 작성일14-04-25 12:08 조회9,202회 댓글0건

본문

MySQL은 % 연산자를 사용하여 패턴연산을 하는 대신, 단어 또는 구문에 대한 검색을 지원하며 FULLTEXT 검색이라고 부른다. MySQL은 3가지 종류의 FULLTEXT 검색 방식을 지원한다.


FULLTEXT 검색 방식

  • 자연어 검색
    검색 문자열을 단어 단위로 분리한 후, 해당 단어 중 하나라도 포함되는 행을 찾는다.
  • 불린 모드 검색
    검색 문자열을 단어 단위로 분리한 후, 해당 단어가 포함되는 행을 찾는 규칙을 추가적으로 사용할 수 있다.
  • 쿼리 확장 검색
    2단계에 걸쳐서 검색을 수행한다. 첫 단계에서는 자연어 검색을 수행한 후, 첫 번쨰 검색의 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두 번째 검색을 수행한다. 이는 1단계 검색에서 사용한 단어와 연관성이 있는 단어가 1단계 검색에 매칭된 결과에 나타난다는 가정을 전제로 한다.
이 글에서는 자연어 검색과 불린 모드 검색만을 다루고자 한다. 그리고 영어 데이터가 아닌 한글 데이터를 기준으로 한다.


FULLTEXT 검색을 위한 주의사항
FULLTEXT 검색을 지원하기 위해서는 아래의 사항들을 주의해야 한다.

FULLTEXT 인덱스 생성
FULLTEXT 검색을 하려는 컬럼에 대해 FULLTEXT 인덱스를 생성해야 한다. FULLTEXT 인덱스는 MyISAM 엔진을 사용하는 테이블에 대해서만 생성할 수 있다. 또한 컬럼 데이터 타입은 반드시 Text, Binary Char, Varchar 타입을 가져야 한다. 또한 한글 데이터를 검색하려면 테이블 인코딩을 utf8을 사용해야 한다.

FULL TEXT 인덱스는 테이블을 생성할 때 생성하거나, 또는 이미 만들어진 테이블에 대해서도 테이블을 생성할 수 있다. 예를 들어 게시판 검색 기능을 만든다고 해보자. 게시글을 저장할 posts 테이블을 만들고, 여기에 게시글의 제목(gtitle), 본문(gdesc) 컬럼을 추가한다고 하자. 만약 제목만을 FULLTEXT 검색을 하고자 한다면,
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;

와 같이 테이블을 생성한다.

만약 이미 만들어진 테이블에 대해 FULLTEXT 인덱스를 생성해야 한다면,
alter table posts add FULLTEXT(gtitle);

와 같이 생성한다.

FULLTEXT 인덱스는 하나 이상의 컬럼에 대해서 생성할 수 있다. 만약 제목 검색뿐만 아니라, 본문 검색까지 지원하려면 아래와 같이 게시글 제목과 본문에 대해서도 FULLTEXT 인덱스를 생성해야 한다.
alter table posts add FULLTEXT(gtitle, gdesc);


FULLTEXT 검색 엔진 설정하기
검색어가 너무 짧은 경우 아무런 검색결과도 나오지 않는다. 이때 짧다는 기준은 4글자 이하다. 만약 2글자의 검색어를 지원하려면 최소 검색어 길이 값을 2로 수정해야 한다. my.cnf 설정파일을 열어서 ft_min_word_len 변수 값을 기본값인 4에서 2로 변경한다.
# my.cnf 파일
ft_min_word_len=2

만약 FULLTEXT 인덱스를 생성한 경우라면 인덱스를 삭제한 후 재생성해야 한다. 또는 아래와 같이 인덱스를 갱신할 수 있다.
REPAIR TABLE posts QUICK;

이제 FULLTEXT 검색 본론으로 넘어가 보자.


자연어 검색
게시물의 제목과 본문에 "로고"라는 단어가 포함된 게시글을 검색하는 경우를 생각해 보자. FULLTEXT 검색은 아래와 같이 수행한다.
select P.gtitle, P.gdesc from posts P where match(gtitle, gdesc) against('로고') limit 10;

검색 결과는 다음과 같다.


무언가를 해낸듯한 기분이 들겠지만 약간의 문제가 있다. 이 부분은 조금 후에 불린 검색에서 다루기로 하고, 검색의 정확도를 수치로 보여주는 방법을 살펴보자.
select P.gtitle, P.gdesc, match(gtitle, gdesc) against('로고') as score from posts P where match(gtitle, gdesc) against('로고') limit 10;


보는 바와 같이 검색의 정확도(score)에 따라 내림차순 정렬되어 결과가 표시됨을 알 수 있다. 물론 1단어뿐만 아니라 여러 단어로도 검색이 가능하다.


select P.gtitle, P.gdesc, match(gtitle, gdesc) against('귀여운 로고') as score from posts P where match(gtitle, gdesc) against('귀여운 로고') limit 10;





자연어 검색은 구문을 단어 단위로 분할한 후, 해당 단어 중 1개라도 포함된 행을 검색한다. 따라서 6번째 결과와 같이 "예쁜 팔찌 만들어"와 같이 관련성이 낮은 게시글도 검색되게 된다.



불린 모드 검색

이제 불린 모드 검색을 이용하여 자연어 검색에서 부딪혔던 문제를 완화해 보자. 불린 검색이 자연어 검색와 크게 차이나는 점은

  • 검색의 정확도에 따라 결과가 정렬되지 않는다.
  • 구문 검색이 가능하다
  • 필수(+), 예외(-), 부분(*) 연산자를 사용할 수 있다

등이다.


앞서 "귀여운 로고"를 단어 검색이 아닌 구문 검색, 즉 "귀여운 로고"가 그대로 포함된 게시글을 검색하고자 한다면,

select P.gtitle, P.gdesc from posts P where match(gtitle, gdesc) against('"귀여운 로고"in boolean mode);


구문을 쌍따옴펴(" ")로 묶은 후, 불린 모드(boolean mode)로 검색을 실행한다.



만약 맨 마지막의 여행영상 게시글은 검색에서 제외하려면 예외 연산자(-)를 사용할 수 있다.

select P.gtitle, P.gdesc from posts P where match(gtitle, gdesc) against('"귀여운 로고" -여행영상' in boolean mode);



이제 부분 연산자(*)를 살펴보자. 앞서 자연어 검색 첫 번째 예제에서 "로고"를 검색한 결과를 보면, 모두 "로고" 단어가 독립적으로 존재한다. 다시 말해 "로고를", "로고와" 등과 같은 단어는 검색되지 않는다. 이처럼 한글에서 형태소를 잘라야만 제대로 인덱스가 구성되지만 쉬운일이 아니다. 따라서 검색할 때 "로고로 시작하는 모든 단어"와 같은 패턴을 사용할 수 있다. 명확한 예를 위해 게시글 제목(gtitle)에 대해서만 FULLTEXT 검색을 실행해 보자. 먼저 자연어 검색은 다음과 같다.

select P.id, P.gtitle from posts P where match(gtitle) against('로고를') limit 10;


위와 같이 검색하면 


와 같이 "로고를"이 포함된 행이 매칭된다. 하지만 자연어 검색에서 아래와 같이 '로고'를 사용하면 해당 행이 검색되지 않는다.

select P.id, P.gtitle from posts P where match(gtitle) against('로고') and id = 911 limit 10;


그러나 불린 검색을 이용하여 부분 검색 연산자를 사용하면 검색이 가능하다.

select P.id as id, P.gtitle from posts P where match(gtitle) against('로고*in boolean mode) and id = 911 limit 10;



메타 검색 기능 UI 구성
이제 FULLTEXT 검색 기능을 사용하여 서비스에 메타 검색 기능을 추가한다면 아래와 같이 UI를 구성해볼 수 있다.
일단 기본적으로 모든 검색은 불린 검색을 사용한다. 그리고 사용자가 입력한 단어는 구문 검색(" ")이 아닌 경우 공백을 기준으로 분리한 후, * 연산자를 추가한다. 아래는 입력 예다.



1단어를 입력한 경우 --> [로고*] 로 검색을 수행한다.
2단어를 입력한 경우 --> [귀여운* 로고*] 로 검색을 수행한다.
구문 검색을 입력한 경우 --> ["귀여운 로고"*] 로 검색을 수행한다.
예외 연산자를 사용한 경우 --> ["귀여운 로고"* -영상] 으로 검색을 수행한다.



[참고자료]

추천 0

댓글목록

등록된 댓글이 없습니다.

Database 목록

Total 72건 1 페이지
Database 목록
번호 제목 글쓴이 날짜 조회 추천
72 mariadb 원격접속 문제 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 08-29 68 0
71 MySQL #1690 - BIGINT UNSIGNED value is out of rang… 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 09-08 1143 0
70 MySQL ON DUPLICATE KEY UPDATE 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 05-04 3506 0
69 InnoDB 형식의 테이블 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 07-24 5722 0
68 mysql 대용량처리 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 07-24 6827 0
열람중 MySQL FULLTEXT 검색 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 04-25 9203 0
66 MySQL 문자열 길이 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 04-25 8200 0
65 [MySQL] Full-Text Search 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 04-08 8926 0
64 mysql euc-kr -> utf-8 변환 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 04-04 8306 0
63 [mysql] SQL order string as number 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 10-28 7561 0
62 [MySQL] 서브쿼리 - 두 개 이상의 SQL쿼리 조합하기 no_profile 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 05-14 6840 0
61 MYISAM -> INNODB 변환 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 10-06 13162 0
60 mysql euckr 을 utf8 로 변경 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 09-29 12338 0
59 VB MySql 연결 및 한글문제 해결 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 09-06 14907 0
58 windows 2003 MySQL 서비스 프로그램 등록 1067 에러 댓글2 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 02-11 19575 0
57 MySQL에서 root 암호(password) 설정 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 03-03 14068 0
56 MySQL에서 오라클의 rownum 구현하기 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 02-21 13232 0
55 mysql의 union연산이 빠르다고 합니다. 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 02-20 12182 0
54 MySQL RPM 설치 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 02-13 13579 0
53 MySQL 설치 (바이너리 혹은 소스) 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 02-13 10475 0
게시물 검색

Copyright © nuno21.net All rights reserved. 상단으로
모바일 버전으로 보기