study MySQL 인덱스 전문 검색 인덱스 full text search 어근 분석 알고리즘 n-gramm 알고리즘 bi-gram 알고리즘 불용어 함수 기반 인덱스 가상 칼럼

전문 검색 인덱스

알고리즘과 함수 기반 인덱스

책너두 6기 25일차

백은빈, 이성욱의 Real MySQL8.0 1권 p.258 ~ p.266

내용정리

08 인덱스

8.5 전문 검색 인덱스

현재까지 공부한 인덱스 알고리즘은 크지 않은 데이터나 이미 키워드화한 작은 값에 대한 인덱싱 알고리즘이다.

문서 전체에 대한 분석과 검색을 위한 이러한 인덱싱 알고리즘을 전문 검색(Full Text search) 인덱스 라고 한다. 이는 일반화된 기능의 명칭이다.

8.5.1 인덱스 알고리즘

전문 검색에서는 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석해 내고, 빠른 검색용으로 사용할 수 있게 이러한 키워드로 인덱스를 구축한다.

문서의 키워드를 인덱싱하는 기법에 따라 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분한다.

8.5.1.1 어근 분석 알고리즘

전문 검색 인덱스는 두 가지 중요한 과정을 거친다.

  • 불용어(Stop Word) 처리
    • 불용어 처리는 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업을 의미한다.
  • 어근 분석(Stemming)
    • 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다.
    • 각 국가의 언어가 서로 문법이 다르고 다른 방식으로 발전해왔기 떄문에 형태소 분석이나 어근 분석이 언어별로 달라서 준비가 많이 필요하다.
8.5.1.2 n-gram 알고리즘

본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다. 어근 분석보다는 단순하고 국가별 언어ㅔ 대한 이해와 준비 작업이 필요 없는 반면, 만들어진 인덱스의 크기는 상당히 큰 편이다. n은 인덱싱할 키워드의 최수 글자 수를 의미하는데, 일반적으로 2-gram(Bi-gram이라고도 함) 방식이 많이 사용된다.

“To be or not to be. That is the question”

이 문장의 토큰을 분리하면 다음과 같다.

To, be, or, no, ot, to, be, Th, ha, at, is, th, he, qu, ue, es, st, ti, io, on

여기서 불용어를 거르고 중복된 토큰은 하나로 병합된다. 불용어는 내장되어 있지만 사용자가 별도로 설정 가능하다.

8.5.1.3 불용어 변경 및 삭제

불용어는 의도치 않게 사용자를 더 혼란스럽게 할 수도 있는 기능이다. 따라서 불용어 자체를 아예 무시하거나, 내장된 것 대신 사용자가 직접 등록할 수 있다.

전문 검색 인덱스의 불용어 처리 무시

두 가지 방법이 있다.

첫 번쨰 방법은 스토리지 엔진에 관계없이 MySQL 서버의 모든 전문 검색 인덱스에 대해 불용어를 완전히 제거하는 것이다. my.ini파일의 ft_stopword_file시스템 변수에 빈 문자열을 설정하면 된다.

두 번째 방법은 InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 인덱스에 대해서만 불용어 처리를 무시하는 것이다. 시스템 변수innodb_ft_enalbe_stopword 시스템 변수를 OFF로 설정하면 된다. 이 경우에는 다른 스토리지 엔진을 사용하는 테이블은 여전히 내장 불용어 처리를 사용한다.

사용자 정의 불용어 사용

이 방법도 두가지가 있다.

첫 번째 방법은 불용어 목록을 파일로 저장하고, MySQL 서버 설정 파일에서 파일의 경로를 다음과 같이 ft_stopword_file설정에 등록하면 된다.

두 번째 방법은 InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 엔진에서만 사용할 수 있는데, 불용어의 목록을 테이블로 저장하는 방식이다. 다음과 같이 테이블을 생성하여 innodb_ft_server_stopword_table시스템 변수에 설정하면 된다.

create table my_stopword(value VARCHAR(30)) engine = innodb;
insert into my_stopword(value) values ('MySQL');

set global innodb_ft_server_stopword_table='mydb/my_stopword';
alter table to_bi_gram
	add fulltext index fx_title_body(title, body) with parser ngram;

또한 innodb_ft_user_stopword_table 시스템 변수를 이용해도 된다.

8.5.2 전문 검색 인덱스의 가용성

전문 검색 인덱스를 사용하려면 다음 두 조건은 반드시 갖춰야 한다.

  • 쿼리 문장이 전문 검색을 위한 문법(match … against …)을 사용
  • 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유

전문 검색을 위한 문법을 사용하지 않으면 결과는 얻을 수 있지만 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 쿼리르 ㄹ처리하게 된다.

8.6 함수 기반 인덱스

일반적인 인덱스는 칼럼의 값 일부나 앞부분 또는 전체에 대해서만 인덱스 생성이 허용된다. 하지만 칼럼으 ㅣ값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때는 함수 기반의 인덱스를 활용하면 된다. 구현 방법은 두가지가 있다.

  • 가상 칼럼을 이용한 인덱스
  • 함수를 이용한 인덱스

함수 기반 인덱스는 인덱싱할 값을 계싼하는 과정의 차이만 있을 뿐, 실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.

8.6.1 가상 칼럼을 이용한 인덱스

create table user (
	user_id BIGINT,
    first_name VARCHAR(10),
    last_name VARCHAR(10),
    PRIMARY KEY (user_id)
);

위와 같은 테이블이 있을 때 first_name과 last_name을 합쳐서 검색해야 한다면? 기존에는 ful_name 칼럼을 추가해서 모든 레코드에 대해 업데이트 했어야 했지만 이젠 가상 칼럼을 추가하고 거기에 인덱스를 생성할 수 있다.

alter table user
	add full_name varchar(30) as (concat(first_name,' ',last_name)) VIRTUAL,
	add index ix_fullname (full_name);

이렇게 하면 full_name 칼럼에 대한 검색도 새로 만들어진 ix_fullname 인덱스를 이용하게 된다.

하지만 가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

CATALOG
    MySQL