6.4 전문 검색 엔진

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 파일

• 테이블 리빌드

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

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

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

• 테이블

• 테이블 단위 조회는 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

단위 검색어 길이 기본 값

InnoDB 전문 검색 추가 시스템 변수

• innodb_ft_result_cache_limit : 검색 쿼리 중간/최종 결과가 저장되는 메모리 공간 제한, 초과 시 쿼리 취소 (기본값:2GB)
• innodb_ft_total_cache_size : 모든 임시 전문 검색 인덱스 사용 공간 제한, 초과 시 강제 동기화

전문 검색 알고리즘

• 구분자 방식 : MySQL 5.5 MyISAM, MySQL 5.6 InnoDB (키워드 일치 검색 적합)
• n-Gram 방식 : MySQL 5.7.6 (http://mysqlserverteam.com/innodb-%EC%A0%84%EB%AC%B8-%EA%B2%80%EC%83%89-n-gram-parser/)