Real MariaDB (2017년)
전문 검색 엔진 0 0 41,701

by 구루비스터디 MariaDB 스토리지 엔진 전문 검색 엔진 [2019.08.11]


6.4 전문 검색 엔진

MySQL 5.6 부터 InnoDB 에서도 전문 검색 엔진 기능 추가.
MySQL 5.5 에서는 MyISAM 의 한계로 전문 검색 엔진 효용 가치 낮음.


6.4.1 전문 검색 인덱스 추가

  • Demo

 create table employee_name_innodb (
   emp_no int(11) not null,
   first_name varchar(14) not null,
   last_name varchar(16) not null,
   primary key (emp_no)
) engine=innodb default charset=utf8;

insert into employee_name_innodb
select emp_no, first_name, last_name from employees;

alter table employee_name_innodb
  add fulltext index fx_firstname_lastname (first_name, last_name);

 show warnings;


  • 전문 검색 엔진 인덱스 생성 시 내부 FTS_DOC_ID 컬럼 필요, 없을 경우 경고와 함께 추가(Table rebuilding) 됨
  • 컬럼 추가는 온라인 처리 불가


6.4.2 전문 검색 인덱스를 위한 테이블 스페이스

  • ibd 파일
ibd 파일역할비고
employee_name_innodb.ibd테이블 데이터 저장용 기본 테이블 스페이스
FTS_xxx_yyy_DOC_ID.ibd단위 검색어로 구성 된 인덱스 테이블 스페이스컬럼의 값을 구분자로 자른 결과, 인덱스 별 존재
FTS_xxx_CONFIG.ibd전문 검색 인덱스 설정 저장optimize_checkpoint_limit, synced_doc_id, deleted_doc_count, stopword_table_name, use_stopword 등
FTS_xxx_STOPWORDS.ibd전문 검색 인덱스가 사용하는 구분자 저장기본 내장, 사용자 지정
FTS_xxx_{ADDED,DELETED}.ibd추가/삭제 레코드 FTS_DOC_ID 저장, 검색시 DELETED 레코드 제거 후 사용자에게 반환레코드 추가 삭제 즉시 전문 검색 인덱스 반영 안됨
FTS_xxx_BEING_{ADDED,DELETED}.ibd전문 검색 인덱스에 반영(임시→메인)시 작업중 FTS_DOC_ID 값 저장이 파일은 대부분 비어 있음
FTS_xxx_{ADDED,DELETED}_CACHE.ibd레코드 추가 삭제 내용을 즉시 반영하는 임시 전문 인덱스, 메인 전문 인덱스 반영은 테이블 리빌드에 의해 처리


  • 테이블 리빌드

-- 전문 검색 인덱스 반영을 위한 테이블 리빌드
ALTER TABLE employee_name_innodb ENGINE = InnoDB;
OPTIMIZE TABLE employee_name_innodb;


  • 메인 전문 검색 인덱스 생성 이후 변경 데이터는 임시 전문 인덱스로 관리.
  • 테이블 리빌드 시 임시 전문 인덱스는 메인 전문 인덱스에 병합.
  • 임시 전문 인덱스가 지나치게 커지지 않도록 주기적 테이블 리빌드 필요.
  • 각 테이블 스페이스 파일은 필요시 생성 되고, 전문 인덱스 삭제 후 테이블 리빌드 하야 모든 테이블 스페이스 파일 삭제됨


6.4.3 전문 검색 인덱스 관련 INFORMATION_SCHEMA 정보

  • 테이블
테이블단위내용
INNODB_FT_CONFIG전체전문 검색 인덱스의 기본 설정
INNODB_FT_DEFAULT_STOPWORD전체InnoDB의 기본 구분자 목록, 영어에 적합한 구분자 존재
INNODB_FT_INDEX_TABLE테이블innodb_ft_aux_table 변수값 저장
INNODB_FT_INDEX_CACHE테이블임시 전문 인덱스 내용 저장
INNODB_FT_{INSERTED,DELETED}테이블추가/삭제된 레코드의 FTS_DOC_ID 저장
INNODB_FT_BEING{INSERTED,DELETED}테이블테이블 리빌드에 의해 메인 전문 인덱스에 반영 중인 레코드 정보 저장


  • 테이블 단위 조회는 innodb_ft_aux_table 변수에 대상 테이블 명시 필요.

 SET GLOBAL innodb_ft_aux_table = 'employees/employee_name_innodb';
 


6.4.4 전문 검색 인덱스 사용

  • 전문 검색 전용 쿼리 문법 사용
  • MATCH 절에 전문 검색 인덱스 구성 컬럼 모두 명시 필수

SELECT * FROM employee_name_innodb WHERE MATCH(first_name, last_name) AGAINST ('+Matt +Wallrath' IN BOOLEAN MODE);

EXPLAIN
SELECT * FROM employee_name_innodb WHERE MATCH(first_name) AGAINST ('+Matt +Wallrath' IN BOOLEAN MODE);
-- ALL

EXPLAIN
SELECT * FROM employee_name_innodb WHERE MATCH(first_name, last_name) AGAINST ('+Matt +Wallrath' IN BOOLEAN MODE);
-- FULLTEXT


6.4.5 주의 사항

  • MyISAM, InnoDB 전문 검색 인덱스의 기본 구분자 리스트는 서로 다르므로 검색 결과가 다를 수 있음.
    • 빌트인 구분자 리스트 : INFORMATION_SCHEMA.innodb_ft_default_stopword
    • 커스텀 구분자 리스트 : 기본 구분자 리스트 와 같은 구조 테이블 생성 후 구분자 입력 하고, innodb_ft_server_stopword_table 시스템 변수에 설정 (db_name/table_name)
    • 커스텀 구분자 리스트 설정 이후 테이블에 대해서만 적용 됨

CREATE TABLE stopword_for_employees (
  value varchar(18) NOT NULL DEFAULT ''
) ENGINE = InnoDB;

INSERT INTO stopword_for_employees SELECT * FROM INFORMATION_SCHEMA.innodb_ft_default_stopword;

SELECT * FROM stopword_for_employees limit 10;

SET GLOBAL innodb_ft_server_stopword_table = 'employees/stopword_for_employees';
-- ERROR 1231 / Latin1 문자 셋만 사용 가능

ALTER TABLE stopword_for_employees CONVERT TO CHARACTER SET Latin1;
SET GLOBAL innodb_ft_server_stopword_table = 'employees/stopword_for_employees';


구분자 리스트 테이블 이용 순서
  • 사용자 : innodb_ft_user_stopword_table
  • 시스템 : innodb_ft_server_stopword_table
  • 기본값 : INFORMATION_SCHEMA.innodb_ft_default_stopword


단위 검색어 길이 기본 값
구분최소최대
MyISAMft_min_word_len(4)ft_max_word_len(무제한)
InnoDBinnodb_ft_min_token_size(3)innodb_ft_max_token_len(64)
InnoDB 전문 검색 추가 시스템 변수
  • innodb_ft_result_cache_limit : 검색 쿼리 중간/최종 결과가 저장되는 메모리 공간 제한, 초과 시 쿼리 취소 (기본값:2GB)
  • innodb_ft_total_cache_size : 모든 임시 전문 검색 인덱스 사용 공간 제한, 초과 시 강제 동기화


전문 검색 알고리즘


"구루비 데이터베이스 스터디모임" 에서 2017년에 "Real MariaDB" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4203

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입