MySQL Full-Text 검색 / match against

- 180123 내용 보완

* MySQL 에서 텍스트 검색시 like '%XXX'%' 나 REGEXP  이용 가능하지만

데이터가 늘어나면서 성능이 떨어짐

- 미리 Fulltext Index 를 만들어 놓아 빠른 검색 가능

- MyISAM엔진 에서는 기본 지원

- InnoDB 에서는 5.6 이상부터 지원 

https://dev.mysql.com/doc/refman/5.5/en/fulltext-restrictions.html

* like -> match against 로 쿼리 변경시 성능 개선

- 뉴스 제목 200만건 중 50건 정도되는 검색시

21.606sec => 1.154 sec

- 2000만건 대상, 비약적 차이 (몇 분이 걸려 검색 불가 => 5초 내에 가능) 

- 단, 검색 달라짐 ( fulltext index 는 어절 단위로 인덱싱 되어 쿼리에 따라 like '%..%' 와 다른 결과 나올 수 있음)

* Full text Index 만들기

CREATE TABLE ... (

FULLTEXT KEY 인덱스명 (컬럼명)

)

또는

ALTER TABLE 테이블명 ADD FULLTEXT INDEX 인덱스명 (컬럼명);

ex)

CREATE TABLE ... (

 ,  FULLTEXT KEY `idxTitleFulltext` (`title`)

)

https://kmongcom.wordpress.com/2014/03/28/mysql-%ED%92%80-%ED%85%8D%EC%8A%A4%ED%8A%B8fulltext-%EA%B2%80%EC%83%89%ED%95%98%EA%B8%B0/

- 참고 : 복수 컬럼 지정 가능

- 참고 2 : 인덱스 생성에 걸리는 시간

뉴스 제목 200만건 대상일때 1시간 이내

2000만건 이상 일때 2일 정도 걸림. (OPTIMIZE 도 동일)

* 검색시 최소 글자수 설정 (innoDB 의 경우 다음 항목을 참고) 

4글자 이상 검색 가능한데, 이것을 2글자 이상으로 고쳐야함.

my.cnf (윈도우즈는 my.ini (C:\ProgramData\MySQL\...)

ft_min_word_len = 4 (기본값) 을 2로 고침.

(추가/변경 후 mysql 종료, 재시작

net stop mysqlXX

net start mysqlXX)

- 수정후 확인  

show variables like '%ft_min%';

- 이미 인덱스 생성되어 있는 상태라면 갱신해야...

REPAIR TABLE 테이블명 QUICK;

- 이 쿼리는 MySQL 5.6 /  innoDB 테이블에서 실패함

The storage engine for the table doesn't support repair

- 참고 : check repair optimize

http://nota.tistory.com/74

- 설정 후 재시작(net start mysqlXX) 시 1067 오류 발생

시스템 오류 1067이(가) 생겼습니다

my.ini 에 잘못된 내용 추가했을 때 발생했음. (오타확인)

* innoDB 인 경우 최소 글자수 설정

innodb_ft_min_token_size=2

OPTIMIZE TABLE 테이블명;

결과

실행 후 짧은 단어로 검색 가능한것을 확인함.

- 참고 : 뉴스 제목 컬럼 대상, 2백만개 Row 있을 때

optimize 에 30분 정도 걸림

* 자연어 검색

SELECT * FROM 테이블명

WHERE MATCH(컬럼명) AGAINST('검색어1 검색어2')

- 자연어 검색은 검색어1과 검색어2을 OR 조건으로 검색

즉 '검색어1' 만 있어도 검색 결과에 포함됨

SELECT * FROM 테이블명

WHERE MATCH(컬럼명) AGAINST('"벵에돔 낚시"')

- 큰 따옴표로 묶어서 띄어쓰기 가능

=> "벵에돔 낚시" 를 검색하며 "벵에돔" 이나 "낚시" 를 따로 검색하지 않음

- 참고 : 앞에 있는 검색어가 최소글자수 보다 짧으면 무시되기도 . . .

ex : 최소글자수 3글자이고 "낚시 벵에돔" 검색시 검색결과는 없음.

* 불린 모드 검색 

SELECT * FROM 테이블명

WHERE MATCH(컬럼명) AGAINST(' "벵에돔 낚시" -거제도' in boolean mode)

- "벵에돔 낚시" 결과에서 "거제도" 들어간 것을 제외

SELECT * FROM 테이블명

WHERE MATCH(컬럼명) AGAINST('+"벵에돔" +"거제도"' in boolean mode)

- "벵에돔"과 "거제도" 모두 들어간 것..

- 참고 : MATCH(컬럼명) AGAINST('"벵에돔" +"거제도"' in boolean mode)

("벵에돔" 앞에는 + 가 없는 상태) 에는 

1. 벵에돔 & 거제도 동시 들어간게 우선

2. 그 이후 거제도 만 들어간 것이 이어짐

- 일치도를 확인하고 싶으면 다음과 같이 하면 됨...

SELECT title, 

MATCH(title) AGAINST('"벵에돔" +"거제도"' in boolean mode) AS SCORE

FROM 테이블명

WHERE MATCH(title) AGAINST('"벵에돔" +"거제도"' in boolean mode)

SELECT * FROM 테이블명

WHERE MATCH(컬럼명) AGAINST('벵에돔*' in boolean mode)

- '벵에돔' 만 검색시 '벵에돔을' '벵에돔이' 는 검색 불가 

- '벵에돔*' => 벵에돔, 벵에돔을, 벵에돔이, ... 모두 검색 됨.

- 주의 : '*' 에 큰따옴표 씌우면 안돼 . . .

'벵에돔*' (O)

'"벵에돔*"' (X)

- '벵에돔을' 이란 단어를 포함한 row 가 있더라도 '*에돔을' 으로 검색할 순 없음.

- '+벵에돔*' 은 가능하지만 '+"벵에돔"*' 은 안됨.

- 기타 사용 방법

https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

+ - () ~ * " < > @distance 등

InnoDb 안되는 것도 있음.

* 플러그인 파서 지원

- 5.7.3 부터..

- N-Gram 파서(5.7.6부터), MeCab 파서 등...

http://mysqldba.tistory.com/287

https://mysqlserverteam.com/innodb-%EC%A0%84%EB%AC%B8-%EA%B2%80%EC%83%89-n-gram-parser/