Full-Text Search 사용법

Full-Text Search는 첫 글자 뿐 아니라 중간의 단어나 문장으로도 인덱스를 생성해주기 때문에, Full-Text Index를 통해 순식간에 검색 결과를 얻을 수 있다.

 

전체 텍스트 검색은 긴 문자의 텍스트 데이터를 빠르게 검색하기 위한 MySQL의 부가적인 기능이다.

일반 인덱스와 차이로는, 풀텍스트 인덱스는 긴 문장 전체를 대상으로 인덱싱 하며, InnoDB와 MyISAM 테이블에만 지원하며, char, varchar, text 타입 문자만 인덱싱이 가능하다.

 

또한 여러 개의 열에 풀텍스트 인덱스 지정이 가능하다.

---

Full-Text Index 사용법

풀텍스트 인덱스 생성 3가지 방식

CREATE TABLE 테이블이름(
    ...
    열이름 데이터형식,
    ...
    FULLTEXT 인덱스이름 (열이름)
);

CREATE FULLTEXT INDEX 인덱스이름 ON 테이블이름 (열이름); -- built in parser 사용
CREATE FULLTEXT INDEX 인덱스이름 ON 테이블이름 (열이름) WITH PARSER ngram; -- ngram parser 사용

ALTER TABLE 테이블이름 ADD FULLTEXT (열이름);

 

Built in parser는 기본적으로 공백을 기준으로 단어를 구분해서 파싱하고, 
ngram parser는 하나의 문장을 최소 토큰 수만큼 모두 나눠서 기록을 한다.
예를 들어서, "안녕하세요 반갑습니다" 라는 문장이 있다면,
Built in parser는 안녕하세요, 반갑습니다라고 파싱해서 인덱스를 생성하지만,
ngram parser는 안녕, 녕하, 하세, 세요, 요반, 반갑, 갑습, 습니, 니다와 같이 파싱을 한다.
그래서 실무에서는 아마도 ngram parser를 사용해야하는 경우가 대부분일 것이고,
각자의 상황에 맞게 parser를 선택해서 사용하면 된다.

 

풀텍스트 인덱스 삭제

ALTER TABLE 테이블이름 DROP INDEX FULLTEXT (열이름);

drop index idx_description on FulltextTbl;

 

풀텍스트 인덱스 확인

SHOW INDEX FROM FulltextTbl; -- 전체 인덱스 확인 쿼리로 확인하면 된다.

FullText Index 생성

---

Full-Text Search

테이블 필드에 전체 텍스트 인덱스를 생성해 봤다.

이제 전체 텍스트 검색을 하기 위해 일반 인덱스와는 다른점이 있는데, 쿼리의 일반 SELECT 문의 WHERE 절에 MATCH(), AGAINST() 특수한 메소드를 사용해야 전체 텍스트 인덱스를 사용하여 검색이 된다.

 

테스트를 위한 테이블 생성

---

자연어 검색

특별히 옵션을 지정하지 않거나 뒤에 in natural language mode를 붙이면 자연어 검색을 한다.

자연어 검색은 단어가 정확한 것을 검색해준다.

 

예로, newspaper이라는 테이블의 article 열에 전체 텍스트 인덱스가 생성되어 있고, 영화라는 단어가 들어간 기사를 찾으려면 다음과 같다.

SELECT * FROM newspaper WHERE MATCH(article) AGAINST('영화');

SELECT * FROM newspaper WHERE MATCH(article) AGAINST('영화' in natural language mode);

SELECT * FROM newspaper WHERE MATCH(article) AGAINST('영화 배우'); 
-- ‘영화’ 또는 ‘배우’ 두 단어 중 하나가 포함된 기사 검색.

하지만 이럴 경우 '영화'라는 정확한 단어만 검색되며 '영화는','영화가' 등 능동적인 검색은 불가하다.

---

불린 모드 검색

이 문제를 해결하기 위해 LIKE 연산자에서 %를 쓰듯이 불린 모드 검색 기능을 사용한다.

불린 모드 검색은 단어나 문장이 정확히 일치하지 않는 것도 검색하는 것을 의미한다.

뒤에 in boolean mode 옵션을 붙여주면 적용된다.

불린 모드 검색은 필수인 +, 제외하기 위한 -, 부분 검색을 위한 * 연산자 등의 다양한 연산자를 지원한다.

-- '영화' 가 앞에 들어간 모든 결과 검색 

-- ex) 영화가 영화는 영화를
select * from newspaper
where match(article) against ('영화*' in boolean mode);


-- 정확히 '영화 배우' 단어가 들어있는 기사 내용 검색
select * from newspaper
where match(article) against('영화 배우' in boolean mode);


-- '영화 배우' 단어가 들어 있는 기사 중에서 '공포' 내용이 들어간 결과
select * from newspaper
where match(article) against('영화 배우 +공포' in boolean mode);


-- '영화 배우' 단어가 들어 있는 기사 중에서 '남자' 내용은 검색에서 제외
select * from newspaper
where match(article) against('영화 배우 -남자' in boolean mode);

 

한 번 테스트를 해보겠다.

-- '여자' 또는 '남자' 가 있는 경우 검색
SELECT *, MATCH(description) AGAINST('남자* 여자*' IN BOOLEAN MODE) AS 점수 
FROM FulltextTbl WHERE MATCH(description) AGAINST('남자* 여자*' IN BOOLEAN MODE);

점수' 컬럼은 MATCH(description) AGAINST(...) 함수가 반환하는 검색 관련도 점수(Relevance Score) 를 의미한다.

MATCH() 함수는 지정한 컬럼(여기서는 description)에서 특정 검색어가 얼마나 잘 일치하는지를 계산한다.
AGAINST() 함수는 실제 검색어를 입력받아 일치도를 평가한다.
이 함수는 검색어와 각 행의 텍스트가 얼마나 관련성이 높은지 수치(점수)로 반환하는 것이다.
점수가 높을수록 해당 행이 검색어와 더 밀접하게 관련 있다는 뜻이 된다.

MATCH(description) AGAINST('남자* 여자*' IN BOOLEAN MODE) AS 점수에서
점수가 높으면 description 컬럼에 '남자' 또는 '여자'와 관련된 내용이 더 많이 혹은 더 정확히 포함되어 있다는 의미이다.
이를 이용해 ORDER BY 점수 DESC 하면 검색 결과를 관련성 높은 순서대로 정렬할 수 있다.
요약하자면, 점수는 검색어와 해당 행의 텍스트 간 관련성을 나타내는 점수이다.

 

-- 님자가 포함된 행을 찾고 여자가 포함되어 있다면 상위행으로 찾기
SELECT * FROM FulltextTbl 
WHERE MATCH(description) AGAINST('+남자* 여자*' IN BOOLEAN MODE);


-- 여자가 포함된 행을 찾고 남자가 있으면 상위 행으로 찾기
SELECT * FROM FulltextTbl 
WHERE MATCH(description) AGAINST('남자* +여자*' IN BOOLEAN MODE);


-- 딱 '남자' 가 포함된 행중에서 여자가 포함된 행만 찾기
SELECT * FROM FulltextTbl 
WHERE MATCH(description) AGAINST('남자 +여자' IN BOOLEAN MODE);    


-- 딱 '남자' 가 포함된 행중에서 여자가 있으면 상위행으로, 남자만 있는것도 찾기
SELECT * FROM FulltextTbl 
WHERE MATCH(description) AGAINST('+남자 여자' IN BOOLEAN MODE);


-- 남자로 검색된 영화 중에서 여자가 들어간 영화는 제거하는 검색
SELECT * FROM FulltextTbl 
WHERE MATCH(description) AGAINST('남자* -여자*' IN BOOLEAN MODE);

---

쿼리 확장 검색

2단계에 걸쳐서 검색을 수행한다.

첫 단계에서는 자연어 검색을 수행한 후, 첫 번째 검색의 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두 번째 검색을 수행한다.

 

이는 1단계 검색에서 사용한 단어와 연관성이 있는 단어가 1단계 검색에 매칭된 결과에 나타난다는 가정을 전제로 한다.

---

검색 단어 제한 수 풀기

mysql은 기본값으로 검색 가능 단어의 숫자는 3이다.

즉, 3글자 이상만 전체 텍스트 검색이 되고 두 글자 단어는 안된다는 말이다.

따라서 검색 가능 단어의 숫자를 확인하고 mysql 설정에서 수정해줘야 한다.

-- 몇 글자 이상부터 검색 가능한지 확인
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

---

my.ini 수정

windows powershell(관리자)를 통해 다음 작업을 수행하고, notepad로 my.ini를 연다.

텍스트 파일이 열리게 되면 맨 아래에  innodb_ft_min_token_size=2 값을 넣고 저장한 뒤에

다시 powershell에

> net stop mysql

> net start mysql

로 mysql을 재시작해주면 적용이 완료된다.

---

중지 단어(stopwords)

전체 텍스트 인덱스는 긴 문장에 대해서 인덱스를 생성하기 때문에 그 양이 커질 수 밖에 없다.

그러므로, 실제로 검색에서 무시할 만한 단어들은 전체 텍스트 인덱스로 생성하지 않는 편이 좋다.

 

예를 들어 다음과 같은 문장이 있다고 하자.

이번 회의는 아주 중요한 안건이기 때문에 모두 꼭 참여 부탁드립니다.

 

이를 전체 텍스트 인덱스로 단어를 끊어 만든다면 다음과 같이 된다.

이번

회의는

아주

중요한

안건이기

때문에

모두

꼭

참여

부탁드립니다.

여기서 '이번', '아주', '모두', '꼭' 등과 같은 부사 단어는 이용자가 굳이 검색할 이유가 없으므로 인덱싱에서 제외하는게 효율적이다.

이러한 검색에 필요없는 단어를 중지 단어라고 한다.

 

mysql은 information_schema.innodb_ft_default_stopword 테이블에 36개의 중지 단어를 미리 가지고 있다.

필요하다면 사용자가 별도의 테이블에 중지 단어를 추가한 뒤에 적용시킬 수도 있다.

---

중지 단어 만들기

전체 텍스트 인덱스 단어 확인

--db와 테이블 이름은 소문자 써야된다.
set global innodb_ft_aux_table = '디비명/테이블명';


--테이블에서 만들어진 전체텍스트 인덱스 보기 (필요없는 검색 단어가 있음)
select word, doc_count, doc_id, position from information_schema.innodb_ft_index_table ;

 

중지 단어 생성

-- 중지단어 만들기전에 만들어놓은 풀텍스트 인덱스는 삭제
drop index idx_all on FulltextTbl ;


-- 중지단어를 위한 테이블 만들기
-- 테이블의 데이터는 반드시 value , 타입은 varchar -> 약속
CREATE TABLE user_stopword (value VARCHAR(30));


--중지 단어 만들기
insert into user_stopword values ('그는'), ('그리고'), ('극에') ; 


-- 중지 단어 테이블에 지금 만들 테이블을 추가하는 작업
-- 그러면 이제 user_stopword 테이블에 들어있는 단어로는 풀텍스트 인덱스를 만들지 않게 된다
set global innodb_ft_server_stopword_table = '디비명/user_stopword'; -- 모두 소문자


-- 만든 테이블이 들어갔는지 확인
show global variables like 'innodb_ft_server_stopword_table' ;


-- 중지단어 테이블 만들었으니, 이제 다시 풀텍스트 인덱스 생성.
create Fulltext index idx_description on FulltextTbl ( description );


-- 그러면 중지단어에 등록한 단어를 제외한 인덱스 단어들이 생성됨 (갯수가 줄어들음)
select word, doc_count, doc_id, position 
from information_schema.innodb_ft_index_table ;