MySQL index 적용해보기 + Full Text Index 적용

다음 글을 읽기 위해 필요한 지식은 다음과 같습니다.

- 기초 MySQL 문법

- 인덱스

- 실행계획

 

테이블 생성

우선 Index를 적용하기에 앞서 간단한 테이블을 생성해 보겠습니다.

 

Metting(회의) 테이블은 회의 id와 회의 이름을 가집니다.

CREATE TABLE Meeting(
	metting_id int primary key AUTO_INCREMENT,
	meeting_name varchar(100) NOT NULL
);

 

Dummy 데이터 넣기

INSERT INTO Meeting Values(1,'abc');
INSERT INTO Meeting Values(2,'ab');
INSERT INTO Meeting Values(3,'f');
INSERT INTO Meeting Values(4,'abcd');
INSERT INTO Meeting Values(5,'e');
INSERT INTO Meeting Values(6,'g');
INSERT INTO Meeting Values(7,'h');
INSERT INTO Meeting Values(8,'i');
INSERT INTO Meeting Values(9,'j');
INSERT INTO Meeting Values(10,'hhhhabcd');

10개의 데이터를 넣어보겠습니다.

 

테이블 조회해보기

select * from Meeting;

테이블 조회

쿼리 실행계획 확인

EXPLAIN select * from Meeting;

 

타입은 ALL이고 모든 데이터를 조회해야하기 때문에 FULL SCAN이 발생합니다.

 

인덱스 걸기전 쿼리 실행계획 확인

 

EXPLAIN select * from Meeting where meeting_name like '%ab%';

ab를 포함하고 있는 회의 이름을 검색하고 싶을 때 실행계획을 보면 FULL SCAN이 나오게 됩니다.

여기서 meeting_name으로 인덱스를 걸어서 정렬시키면 탐색을 할 때 효율적으로 할 수 있을 것이라고 예상했습니다.

 

 

인덱스 생성하기

CREATE INDEX IDX_MEETING_NAME ON Meeting(meeting_name);

IDX_MEETING_NAME이라는 이름으로 meeting_name으로 인덱스를 생성합니다.

 

인덱스 생성확인하기

SHOW INDEX FROM Meeting;

IDX_MEETING_NAME이라는 이름으로 index가 생성되었습니다.

 

인덱스 생성 후 실행계획 확인

EXPLAIN select * from Meeting where meeting_name like '%ab%';

이번에는 type이 index로 인덱스를 사용하고 있지만 여전히 10개의 쿼리를 탐색합니다.

즉, 인덱스를 통해 정렬되어있어도 "ab"가 포함된 문자열은 모두 검사해야 하기 때문에 정렬됨은 의미가 없이 FULL SCAN이 발생하게 됩니다.

 

 

실행계획을 통해 분석 후 효율적인 쿼리 작성

EXPLAIN select * from Meeting where meeting_name like 'ab%';

위의 쿼리와 차이점은 앞에 %가 붙었느냐 붙지 않았느냐입니다.

이제는 앞에 ab가 나온 것만 검사하기 때문에 실행계획을 검사해보면 인덱스를 활용한 효율적인 실행계획이 예상됩니다.

 

실행계획

type은 range로 드디어 범위 검색이 반영되었으며 rows는 3개만 검사하였습니다.

 

 

문제점

하지만 우리는 특정 키워드로 검색할 때 이 키워드가 항상 앞에만 등장하는 게 아니라 가운데나 끝에 등장할 수 있습니다.

 

예시) 원하는 키워드 : 뉴스 , 저장된 제목들

1. 뉴스가 있다.

2. 어떤 뉴스가 있다.

3. 어떤 뉴스

 

이런 식으로 뉴스라는 키워드가 등장하지만 위의 쿼리로 검색하게 되면 1번 데이터만 보이게 됩니다.

 

이를 해결하기 위한 방법으로는 FULLTEXT INDEX가 있습니다.

 

FULL TEXT INDEX란?

InnoDB에서 5.6부터 지원하며 해당 문자열을 단어 단위로 분리하여 인덱스를 생성하는 방식입니다.

검색을 위한 index로 불필요한 단어를 (영어로 치면 a, the...)를 뺀 나머지 단어들의 index로 관리함으로써 성능을 향상해줍니다.

 

즉, % ab% 같은 구문을 효율적으로 사용할 수 있도록 합니다.

 

FULL TEXT INDEX 설정

ALTER TABLE Meeting ADD FULLTEXT INDEX IDX_MEETING_NAME_FULLTEXT (meeting_name);

meeting_nmae이라는 칼럼에 IDX_MEETING_NAME_FULLTEXT라는 이름으로 FULLTEXT INDEX를 설정합니다.

 

FULL TEXT INDEX를 통해 조회

select * from Meeting where MATCH(meeting_name) AGAINST('ab');

이때 FULL TEXT INDEX를 사용하지고 해당 쿼리를 날리면 다음과 같은 에러 메시지가 출력됩니다.

 

Error Code : 1191. Can't find FULLTEXT index matching the column list

 

하지만 결과로 아무것도 나오지 않습니다.

 

 

 

문제가 발생한 이유

이때 검색어의 길이가 4보다 작은 경우에는 나오지 않습니다. (기본값 = 4)

만약 2글자도 검색하고 싶다면 ft_min_word_len 변수 값을 2로 변경해야 합니다.

 

 

4글자 이상으로 조회

select * from Meeting where MATCH(meeting_name) AGAINST('abcd');

4글자 이상으로 만들어져 있으니 abcd를 통해 조회해 보겠습니다.

 

기대했던 결과로는 aabbccabcd도 나오는 것이지만 abcd만 나왔습니다.

FULL TEXT INDEX는 단어와 단어 사이를 구분하기 때문에 공백이 없어서 구분하지 못하는 것으로 보입니다.

 

단어를 구분하여 문자열 데이터를 추가로 넣어보겠습니다.

 

데이터 추가하고 공백으로 구분

INSERT INTO Meeting Values(11,'aabbcc abcd');
INSERT INTO Meeting Values(22,'aabbcc abcd f');

 

다시 조회 시 결과

이렇게 되면 FULL TEXT INDEX를 적용이 완료됩니다.

 

200만 건을 대상으로 검색하는 경우 like는 20초 match against는 1초 정도 시간이 소요됩니다.

하지만 어절 단위로 인덱싱 하기 때문에 쿼리에 따라 like % word% 구문과는 다른 결과가 나올 수 있습니다.

 

 

MySQL FULLTEXT INDEX vs Elastic serach full text

Elastic search와 mysql 둘 중 어떤 것을 사용해야 할지 질문하는 글들이 보였습니다.

Elasticsearch는  오픈소스 실시간 분산 검색 엔진으로 검색을 빠른 속도로 지원해줍니다.

 

ElasticSearch가 일반적으로 mysql에보다 더 높은 성능과 결과를 제공하지만 관리해야 하는 포인트가 하나 더 생기며 콘텐츠의 인덱싱 및 업데이트를 관리해주어야 합니다.

 

따라서 시작은 MySQL 텍스트 검색으로 시작하고 상황에 따라 Elastic Search를 도입하는 것이 적절해 보입니다.

 

 

출처

https://reasontaek.tistory.com/12

[MySQL] 게시글 검색(제목->내용->태그 순)

https://dogcowking.tistory.com/78

MySQL Full-Text 검색 / match against

https://stackoverflow.com/questions/41892179/elastic-search-full-text-vs-mysql-full-text

Elastic search full text vs mysql full text?