mysql TEXT like 검색 개선 포인트 찾기 1

Mysql은 데이터형이 TEXT인 경우 인덱스를 지원하지 않는다.

고객의 요구사항은 테이블의 컬럼을 선언할 때 데이형을 TEXT 정도가 아니면 받기 어렵다. 결국 문자열 검색을 요구하고.. like 검색이 시작되고..

데이터가 늘어나면서 한글 문자열 검색은 like로 슬슬 성능이 떨어지니 다른 대안을 검토 중이다.

 

1.사전요구사항

1) like 검색보다 빨라야 한다.

2). like 검색만큼 정확해야 한다.

 

2. 구글링

1. 기존 mysql 에서 지원하는 FullText 인덱스 사용 (mysql 5.6 이상만 지원)

   FULLTEXT 인덱스, n-gram 파서 인덱스

 

2. 검색엔진도입

   스핑크스, 엘라스틱서치,루씬

 

 

3. 작업시작

1. Mysql 내 지원인덱스 적용

 

#인덱스 적용전

보통 와일드카드 검색을 많이 한다.

seelct * from TB_CM_ADI_xxx

where CONTENTS like '%서비스%';

--> 수행시간 0.5초

--> 결과값 3119건

#Plan

1 SIMPLE TB_CS_xxx TB_CM_ADI_xxx ALL 105344 11.11 Using where

 

# FULLTEXT 인덱스적용

#구분자(Stopword) 기법
전문의 내용을 공백이나 탭(띄어쓰기) 또는 마침표와 같은 문장 기호, 그리고 사용자가 정의한 문자열을 구분자로 등록합니다.
구분자 기법은 이처럼 등록된 구분자를 이용해 키워드를 분석해 내고, 결과 단어를 인덱스로 생성해 두고 검색에 이용하는 방법을 말합니다.
일반적으로 공백이나 쉼표 또는 한국어의 조사 등을 구분자로 많이 사용합니다. MySQL의 내장 전문 검색(FullText search) 엔진은 구분자 방식만으로 인덱싱할 수 있습니다.
구분자 기법은 문서의 본문으로부터 키워드를 추출해 내는 작업이 추가로 필요할 뿐, 내부적으로는 B-Tree 인덱스를 그대로 사용합니다.
전문 검색 인덱스의 많은 부분은 B-Tree의 특성을 따르지만 전문 검색 엔진을 통해 조회되는 레코드는 검색어나 본문 내용으로 정렬되어 조회되지는 않습니다.
전문 검색에서 결과의 정렬을 일치율(Match percent)이 높은 순으로 출력되는 것이 일반적입니다.
구분자 기법으로 전문 검색을 사용할 때는 문장 기호뿐 아니라 특정 단어를 일부러 구분자로 등록할 수도 있습니다.
예를 들어 MySQL 매뉴얼을 페이지 단위로 잘라서 테이블에 저장하고 전문 검색을 구현한다고 해봅시다.
이 경우 테이블의 모든 레코드에는 "MySQL"이라는 단어가 포함돼 있을 것입니다. 이 경우 "MySQL"이라는 단어로 검색하면 테이블의 모든 레코드가 일치하므로 검색의 효과가 없어집니다.
이럴 때는 "MySQL"이라는 단어를 구분자에 등록하고 전문 검색 인덱스에 포함하지 않게 해주는 것이 좋습니다.
많은 인터넷 사이트에서 "Stopword"를 "불용어"로 해석하고 있지만, 이보다는 "구분자"라는 표현이 더 적절한 해석이라고 볼 수 있습니다.
이 기법의 알고리즘에서 "Stopword"는 "검색에 사용할 수 없다"보다는 "검색어를 구분해주는 기준(문장 기호나 특정 문자열)이다"의 의미가 더 강하기 때문입니다.

// TEXT 컬럼 중에 잴 데이터 많은 테이블을 찾는다.
select * from information_schema.COLUMNS
where TABLE_SCHEMA = 'PRIME'
and COLUMN_TYPE = 'TEXT';

//인덱스 생성 전에 mysql parameter 설정값을 확인한다.
//문자열 검색 variable 확인
//MyIsam 은 아래값 수정
mysql> show global variables like 'ft_min%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 4     |
+-----------------+-------+
1 row in set (0.00 sec)

//Innodb는 이거 수정한다.
mysql> show global variables like 'innodb_ft_min%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_ft_min_token_size | 3     |
+--------------------------+-------+
1 row in set (0.00 sec)

//2글자 검색이다. 다 2로 수정하고 DB를 재시작한다.
vi /etc/my.cnf
---------------------------------------------
ft_min_word_len =2
innodb_ft_min_token_size = 2

//FullText 인덱스 넣어본다.
alter table TB_CM_ADI_SVC
add FULLTEXT index IDX_CM_ADI_SVC_02 (CONTENTS);

 

select * from TB_CM_ADI_xxx
where MATCH(CONTENTS) AGAINST ('서비스');
1 SIMPLE TB_CS_xxx fulltext IDX_CM_ADI_xxx_01 IDX_TB_CS_xxx_01 0 const 1 100 Using where; Ft_hints: sorted

인덱스 타는 것은 확인했지만 와일드카드 검색이 안된다..

 

select * from TB_CS_xxx
where MATCH(CONTENTS) AGAINST ('*서비스*' in boolean mode)

#order by xxx_SEQ;

-->수행시간 30초

-->결과값 2775건

위처럼 와일드 카드 검색을 할 수 있지만 결과값은 like검색과 차이가 있다.

일단 순서정렬이 서비스와 가장가까운 값부터 정렬하도록 해서 만약 seq가 있다면 order by를 따로 써야할 거 같고..

결과값도 2775건이다. like검색과 비교해보니

예를 들어 _서비스 처럼 서비스 문자열 앞에 특수문자가 오면 검색이 안되었다.

그리고 가장 중요한건 성능이 별로다 like가 더 빠르다. 굳이 쓸 이유 없다.

 

# N-gram 파서

N-그램(N-Gram) 기법
하지만 각 국가의 언어는 띄어쓰기가 전혀 없다거나 문장 기호가 전혀 다른 경우가 허다합니다.
이런 다양한 언어에 대해 하나의 규칙을 적용해 키워드를 추출해내기란 쉽지 않습니다. 또한 구분자 방식은 추출된 키워드의 일부(키워드의 뒷부분)만 검색하는 것은 불가능하다는 단점도 있습니다.
이러한 부분을 보완하기 위해 지정된 규칙이 없는 전문도 분석 및 검색을 가능하게 하는 방법이 N-그램이라는 방식입니다.

N-그램이란 본문을 무조건적으로 몇 글자씩 잘라서 인덱싱하는 방법입니다. 구분자에 의한 방법보다는 인덱싱 알고리즘이 복잡하고, 만들어진 인덱스의 크기도 상당히 큰 편입니다.
트리톤(Tritonn)이나 스핑크스(Sphinx)에서는 다른 인덱싱 방법도 제공하지만, 이 알고리즘이 주로 사용됩니다.
N-그램에서 n은 인덱싱할 키워드의 최소 글자(또는 바이트) 수를 의미하는데, 일반적으로는 2글자 단위로 키워드를 쪼개서 인덱싱하는 2-Gram(또는 Bi-Gram이라고도 한다) 방식이 많이 사용됩니다.
2-Gram 인덱싱 기법은 2글자 단위의 최소 키워드에 대한 키를 관리하는 프론트엔드(Front-end) 인덱스와 2글자 이상의 키워드 묶음(n-SubSequence Window)를 관리하는 백엔드(Back-end) 인덱스 2개로 구성됩니다.
인덱스의 생성 과정은 다음과 같이 2가지 단계로 나눠서 처리됩니다.
첫 번째 단계로, 문서의 본문을 2글자보다 큰 크기로 블록을 구분해서 백엔드 인덱스(3)을 생성
두 번째 단계로, 백엔드 인덱스의 키워드들을 2글자씩 잘라서 프론트엔드 인덱스(6)을 생성

n=1: 'a', 'b', 'c', 'd'
n=2: 'ab', 'bc', 'cd'
n=3: 'abc', 'bcd'
n=4: 'abcd'
그리고, 기본적으로 InnoDB에서 제공해주는 ngram의 최소 토큰 사이즈(ngram_token_size)는 2이고, 위에서 “n=2” 부터 토큰을 만들게 됩니다.
당연한 이야기겠지만, n-수치가 낮을수록 토큰 수가 많아질 것이기에,
모든 검색어들이 3글자부터 시작된다면 이 수치를 3으로 상향 조정하는 것도 인덱싱 관리 및 사이즈 안정성에 도움이 되겠습니다.

 

//인덱스 적용
ALTER TABLE TB_CM_ADI_xxx ADD FULLTEXT INDEX IDX_CM_ADI_xxx_01(CONTENTS) WITH PARSER ngram;

//기본이 2다 굳이 수정할 필요없다.
mysql> show global variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+
1 row in set (0.01 sec)

#테스트

select * from TB_CM_ADI_xxx
where MATCH(CONTENTS) AGAINST ('+서비스+' in boolean mode)

-->수행시간 0.4초

-->결과값 3119건

like %서비스% 성능비슷하고 (와일드카드 검색) 결과값도 일치한다.

대용량테이블로 검색테스트는 해보지 않았지만 이정도면 like검색을 대체해도 될 거 같다.

 

select * from TB_CM_ADI_xxx
where MATCH(CONTENTS) AGAINST ('서비스' in natural language mode)

-->수행시간 0.4초

--> 결과값 8159건
--> 자연어로 와일드카드 검색시 ngram default 값이 2자리라서 서비스 를 검색했지만 '서비' ,'비스'로도 검색되서

3자리지만 각각파싱된 2자리 이상의 단어가 전부 조회되는 것을 확인했다.

정확한 값을 원한다면 사용하지 말아야할 거 같다.

 

to be continue..