[MySQL] 느린 like 검색 대체 하기 full text index 로 검색 엔진 처럼 검색 / MariaDB

[MySQL] 느린 like 검색 대체 하기 full text index

 

LIKE 검색 이놈은 짜증나게 인덱스를 타지 않는다 속도를 그저 서버 성능에 기대야 하는 기능

하지만 중간 단어 검색 등 장점도 있지만 단어별로 검색을 하고 속도를 원한다면 마치 검색엔진 처럼 하고싶다면

full text index 를 적용하고 사용해보자

 

준비 - 색인을 위한 INDEX KEY 지정

 

테이블 생성할때

CREATE TABLE `test_table` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `full_text_test` varchar(200) DEFAULT NULL COMMENT 'FULL TEXT INDEX 칼럼',
    PRIMARY KEY (`id`),
    FULLTEXT KEY `FULL_TEXT_TEST` (`full_text_test`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='테이블';

 

ALTER 할때

ALTER TABLE test_table ADD FULLTEXT KEY `FULL_TEXT_TEST` (`full_text_test`);

 

문제 발생 - 한글 두글자 검색 안될때

MySql 설정 my.cnf 의 mysqld 부분에 아래 두가지를 추가

기본 검색 설정이 4로 되어있어 2로 변경하여 한글 두글자부터 검색 가능하도록 변경

[mysqld]
ft_min_word_len=2 
innodb_ft_min_token_size=2

* 설정 후 db 재시작

* 색인을 다시(데이터 다시 쌓기) 해야 한다는 말이 있다 정확한건 아니지만 확인해볼 필요는 있음

 

사용 예제

-- "미디어" 포함
SELECT pl.app_name FROM package_list AS pl
WHERE MATCH(pl.app_name) AGAINST ('미디어');

-- "미디어" 또는 "플레이어" 포함
SELECT pl.app_name FROM package_list AS pl
WHERE MATCH(pl.app_name) AGAINST ('미디어 플레이어');

-- "미디어" 필수 "플레이어" 선택 ("미디어"와 "플레이어"가 같이 있거나 "미디어"만 있거나)
SELECT pl.app_name FROM package_list AS pl
WHERE MATCH(pl.app_name) AGAINST ('+미디어 플레이어' in boolean mode);

-- "미디어"와 "플레이어" 둘다 있어야함
SELECT pl.app_name FROM package_list AS pl
WHERE MATCH(pl.app_name) AGAINST ('+미디어 +플레이어' in boolean mode);

-- "미디어" 중에 "플레이어"란 글자가 없는 것
SELECT pl.app_name FROM package_list AS pl
WHERE MATCH(pl.app_name) AGAINST ('+미디어 -플레이어' in boolean mode);

-- "미디"로 시작하는 단어가 있는 것
SELECT pl.app_name FROM package_list AS pl
WHERE MATCH(pl.app_name) AGAINST ('미디*' in boolean mode);

-- "미디"로 시작하는 단어 중에 미디어가 없는것
SELECT pl.app_name FROM package_list AS pl
WHERE MATCH(pl.app_name) AGAINST ('미디* -미디어' in boolean mode);

-- 아이언 블레이드 미디벌 레전드 찾기
SELECT pl.app_name FROM package_list AS pl
WHERE MATCH(pl.app_name) AGAINST ('+아이언 +블레이드 +미디벌 +레전드' in boolean mode);

 

알아둘것

- 공백 기준으로 나눠져서 색인이 된다.

: "테스트 글자 입니다" 에서 테스트, 글자, 입니다 이렇게 색인이 된다

- 테스% 된다, %스트% 안된다, %스트 안된다