DBeaver)진짜 검색기능은 Full Text search

여러분이 게시판 서비스를 운영한다고 칩시다.

그래서 테이블 하나에 게시물의 글내용, 작성자, 발행일을 저장하기 시작했는데  

검색기능이 필요해진겁니다. 검색기능은 어떻게 구현하죠? 

 

 

 

LIKE 연산자

 

예전에 간단한 검색기능 만들고 싶으면 컬럼명 LIKE %단어% 하면 된다고 했습니다.

짧은 문장 안에서 검색하는건 이걸로 충분하지만 

1. % 기호를 맨 앞에 쓰면 인덱스활용을 못하고

2. 문장이 좀 길거나 행이 너무 많아지면 LIKE 만으로는 매우 느리게 동작합니다.

하지만 full text index를 만들어두면 걱정없습니다. 

 

 

 

 

 

 

Full text search를 위한 index 

 

긴 글도 데이터베이스의 컬럼 하나에 보관할 수 있습니다. 

text 데이터타입 쓰면 6만5천자를 보관할 수 있으니까요. 

이렇게 긴 글 안에서 원하는 단어를 검색하고 싶다면 당연히 index를 만들어두어야 검색이 빨라집니다.

근데 긴 글은 그냥 index 말고 full text search index를 만들어두면 됩니다. 

 

 

 

궁금할까봐 어떤 원리로 index를 만들어주는지 설명하자면 

 

id	글
1	I run regularly.
2	I eat breakfast.
3	I like running.
4	I like eating pizza.
5	I swim in the sea.

 

▲ 이런 테이블이 있다고 칩시다. 

이 테이블에 full text index를 만들라고 시키면 

 

 

단어	어떤 행에 나오냐면
eat	2, 4
run	1, 3
swim	5

 

▲ 긴 글에 있는 모든 단어를 뽑아서 정렬해주고 

그 단어가 어떤 행에 출몰중인지를 옆에 적어둡니다. 

이러면 eat 이런 단어를 검색했을 때 어떤 행에 들어있는지 쉽게 파악가능하겠죠?

이게 끝입니다.

 

근데 문장 안에서 stopwords라고 부르는

"is the a are and I" 등 내용과 상관없는 쓸데없는 단어들을 제거하고 index를 만드는 경우가 많습니다.

그래서 웹의 검색엔진들이 is the a are 이런 내용 붙여서 검색하면 대부분 무시하는 이유가 그겁니다. 

 

 

 

 

 

 

 

Full text index 만드는 법

 

 

 

▲ 어떤 컬럼에 full text index를 생성하고 싶으면

저번 시간에 했던 index 만드는거랑 똑같은데 Btree말고 Full text로 고르면 됩니다. 

 

 

CREATE FULLTEXT INDEX 인덱스이름작명 ON 테이블명(컬럼명); 

SQL 문법으로 생성하고 싶으면 이거 실행하면 됩니다. 

 

 

 

 

 

Full text index를 이용해 검색하려면

 

MATCH() AGAINST() 이런 특별한 문법이 필요합니다. 

WHERE 뒤에 조건식 형태로 넣을 수 있고 이거 쓰면 Full text index를 이용해서 행을 매우빠르게 필터링해줍니다. 

 

 

SELECT * FROM library WHERE MATCH(서명) AGAINST('부동산'); 

이렇게 작성해서 실행해봅시다.

그럼 '부동산' 이라는 정확한 단어를 가진 행을 매우 빠르게 필터링해줍니다.

진짜 빠른지 확인하려면 execution plan 눌러봅시다. 

 

왜 1,2글자는 검색이 안되나요?

원래그렇습니다. 따로 설정바꾸면 가능 

C드라이브의 ProgramData폴더/MySQL Server 8.0/my.ini 파일에 innodb_ft_min_token_size=2 맨밑에 집어넣고

컴퓨터 재시작하면 됩니다. 

맥도 유사할듯 

[collapse]

 

 

 

포인트1.

그냥 대충 MATCH() AGAINST() 사용하면 IN NATURAL LANGUAGE MODE 라는 모드로 검색해주는데 

영어의 경우 IN NATURAL LANGUAGE MODE 쓰면 검색어에 a, is, the, or 이런 stopwords가 포함되어있을 경우 무시하고 검색해줍니다.

한글은 stopwords 그런거 적용 안됩니다. 

 

포인트2. 

AGAINST('부동산') 이라고 검색하면 정확히 '부동산' 단어만 찾아줍니다. '부동산을' 이런건 못찾음 

그래서 보통은 그거말고 더 다양한 기능을 쓸 수 있는 IN BOOLEAN MODE 를 사용합니다. 

 

 

 

 

 

SELECT * FROM library WHERE MATCH(서명) AGAINST('부동산' IN BOOLEAN MODE); 

IN BOOLEAN MODE를 저렇게 켤 수 있습니다.

근데 그냥 켜기만 하면 아까랑 똑같아서 별 의미 없습니다.

 

 

 

SELECT * FROM library WHERE MATCH(서명) AGAINST('부동산*' IN BOOLEAN MODE); 

IN BOOLEAN MODE에선 * 기호를 이용가능한데

* 기호를 넣어주면 %기호와 유사하게 동작합니다.

위 코드는 부동산이 앞에 포함된 모든 단어를 검색가능합니다. (부동산을, 부동산이 이런거)

 

 

 

 

SELECT * FROM library WHERE MATCH(서명) AGAINST('부동산 종이접기' IN BOOLEAN MODE); 

여러 단어를 넣으면 OR 연산을 해줍니다. 부동산 또는 종이접기가 포함된 결과를 출력해줍니다. 

(참고) 2자 이하는 검색안됩니다. 

 

 

 

SELECT * FROM library WHERE MATCH(서명) AGAINST('+부동산 +빅데이터' IN BOOLEAN MODE); 

+ 기호를 앞에 붙이면 해당단어가 꼭 들어가있는 것만 찾아줍니다. 

위처럼 코드를 입력하면 '부동산'이 꼭 들어있고 '빅데이터'도 꼭 들어있는걸 찾아줍니다. 

대충 AND 연산이랑 비슷함 

 

 

 

SELECT * FROM library WHERE MATCH(서명) AGAINST('-부동산 +빅데이터' IN BOOLEAN MODE); 

- 기호를 앞에 붙이면 해당단어를 걸러줍니다. 

위처럼 코드를 입력하면 '부동산'이 안들어있고 '빅데이터'는 들어있는걸 찾아줍니다. 

대충 NOT 연산자랑 비슷함 

 

이거 말고도 여러 연산자가 있는데 조금 더 검색엔진스러운 전문적인 검색기능이 필요하면 찾아봅시다. 

이 정도만 알아도 웹서비스의 게시판 검색기능 정도로는 훌륭합니다. 

 

 

 

 

 

 

쥐잡듯이 찾고 싶으면 n-gram parser로 index 만들 수 있음

 

영어는 띄어쓰기 단위로 단어를 분리해둘 수 있는데 

한글, 일본어, 중국어는 띄어쓰기가 중요하지않습니다. 

그래서 글자들을 다 붙여쓴 다음에 

몇 글자단위로 잘라서 index를 생성하는 방식이 있는데 이걸 n-gram parser라고 합니다. 

 

예를 들어 '주식 투자' 라는 문자를 n-gram으로 index를 생성하면 

 

단어	어떤 행에 나오냐면
주식	(생략)
식투	(생략)
투자	(생략)

 

띄어쓰기 무시하고 2자 단위로 잘라서 index를 만들어줍니다.

(최소 글자 수는 항상 2자일 필요 없고 맘대로 설정가능합니다.)

그럼 이전과 다르게 다양한 단어도 싸그리 검색이 가능하다는 장점이 있습니다. 

 

 

 

 

CREATE FULLTEXT INDEX 인덱스이름작명 ON 테이블명(컬럼명) WITH PARSER ngram; 

n-gram parser로 index를 생성하려면 SQL 문법으로 생성해야합니다. 

저는 아까의 '서명' 컬럼에 있던 index 삭제하고 이걸로 다시 만들어봤습니다. 

 

 

 

SELECT * FROM library
WHERE MATCH(서명) AGAINST('철학을'); 

그래서 n-gram을 이용해서 index 만들어둔 후

위 처럼 검색하면 '철학' 그리고 '학을' 이 포함된 모든 책제목을 찾아줍니다. 

그래서 '종이학을 접어보자' 이런 책 이름도 검색되는 것임 

 

왜냐면 IN NATURAL LANGUAGE MODE + ngram parser index를 적용하면

검색어를 2개 단어 단위로 쪼개서 하나라도 일치하는 모든 결과를 검색해줘서 그렇습니다. 

 

 

그래서 지엽적인 책 제목까지 싸그리 가져올 수 있다는 장점도 있는데 

1. 일단 n-gram parser로 index 만들면 하드용량을 많이 차지할 수 있고

2. 쓸데없는 결과도 많이 출력된다는 단점도 있습니다. 

근데 잘 보시면 기본적으로 관련도가 높은 결과를 맨 위에 올려주기 때문에

나름 네이버같은 검색엔진을 조금이라도 흉내낼 수 있는 기능이라 할 수 있겠습니다. 

참고로 MATCH(서명) AGAINST('철학을') 이걸 컬럼명 쓰는 자리에 넣어버리면 관련도 점수도 출력가능 

 

 

 

- 이 정도의 성능 말고 네이버, 구글, 대형쇼핑몰처럼 검색성능이 아주 중요한 사이트를 만들고 있다면 

elastic search라든지 검색만을 위한 DB 또는 서비스를 따로 사용할 수도 있기 때문에 그걸 쓰는게 나을 수 있습니다. 

- 다른 DBMS는 다른 문법이나 방법을 사용하는 경우가 많아서 필요시 따로 검색해봅시