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

최근 커뮤니티 기능을 가진 서버를 개발하다보니, 게시글 검색 기능을 구현하게 되었다.

 

요구조건은 아래와 같다.

- 검색결과는 제목->내용->키워드 순으로 배열

- 제목, 내용, 키워드로 검색된 내용을 각각 최신순으로 나열

 

요구조건을 딱 보자마자 든 생각은 쿼리 수행시간이 얼마나 될까 하는 걱정이였다.

그래서 나중에 쿼리 수행시간을 테스트하기로 하고, 요구사항 대로 쿼리문을 짜보았다.

 

SELECT * FROM board
	WHERE title rlike '테스트' AND title rlike '키워드'
 	ORDER BY t2.board_id DESC
UNION
SELECT * FROM board
	WHERE content rlike '테스트' AND content rlike '키워드'
	ORDER BY c2.board_id DESC
UNION
SELECT * FROM board
	WHERE tag rlike '테스트' AND tag rlike '키워드'
	ORDER BY k2.board_id DESC 
LIMIT 1000;

각 서브 쿼리문을 최신순으로 정렬하고 UNION으로 결합했다.

하지만 원하는대로 결과가 나오지 않았다. 왜냐하면 MySQL은 서브쿼리 내의 정렬은 적용이 되지 않는다.

 

그래서 다음과 같이 변경하였다.

(SELECT * FROM board
	WHERE
		title rlike '테스트' AND title rlike '키워드'
	ORDER BY board_id DESC LIMIT 1000)
UNION
(SELECT * FROM board
	WHERE
		content rlike '테스트' AND content rlike '키워드'
	ORDER BY board_id DESC LIMIT 1000)
UNION
(SELECT * FROM board
	WHERE
		tag rlike '테스트' AND tag rlike '키워드'
	ORDER BY board_id DESC LIMIT 1000)
LIMIT 1000;

이게 왜 먹히는지는 아래 사이트를 참고하자.

왜 서브쿼리에서는 정렬이 되지 않나요?

 

이제 원하는 대로 결과가 나왔다. 하지만 한 가지 문제가 남아있었다. 성능...

REGEXP를 사용하거나 LIKE에 검색어 앞에 '%'를 넣어 검색할 경우, 인덱스를 타지 않고 풀 스캔(Full Scan)이 일어나게 된다.

 

그럼 이제 인덱스를 사용할 수 있도록 쿼리문을 바꿔보자.

 

하지만 그 전에 먼저 인덱스를 설정해야 한다.

alter table board add FULLTEXT(title);
alter table board add FULLTEXT(content);
alter table board add FULLTEXT(tag);

그리고 테스트로 다음의 쿼리문을 실행해보았다.

SELECT * FROM db_test.board WHERE match(title) against('제목');

하지만 아무 결과도 나오지 않았다.

그래서 구글링을 해보니, FULLTEXT 검색 엔진의 최소 검색어 길이 값이 innoDB의 경우 3인 것을 알게 되었다.

최소 검색어 길이를 2로 설정해주자.

 

내 경우엔, AWS의 RDB를 이용하기 때문에 다음과 같이 RDB 파라미터 그룹에서 해당 값을 설정해주었다.

설정을 완료한 뒤 MySQL을 재부팅하고, 인덱스도 다시 설정해주어야 적용이 된다.

위의 테스트 쿼리를 날려보니, 이제 검색이 된다.

 

모든 준비는 끝났다. 이제 쿼리문을 바꿔보자.

(SELECT * FROM board
	WHERE
		match(title) AGAINST('+"테스트" +"키워드"' in boolean mode)
	ORDER BY board_id DESC LIMIT 1000)
UNION
(SELECT * FROM board
	WHERE
		match(content) AGAINST('+"테스트" +"키워드"' in boolean mode)
	ORDER BY board_id DESC LIMIT 1000)
UNION
(SELECT * FROM board
	WHERE
		match(tag) AGAINST('+"테스트" +"키워드"' in boolean mode)
	ORDER BY board_id DESC LIMIT 1000)
LIMIT 1000;

REGEXP를 모두 FULLTEXT 검색으로 바꿔주었다.

 

이제 대략 60000개의 행을 추가한 뒤, 두 쿼리문의 실행시간을 비교해보았다.

	REGEXP	FULLTEXT
Duration	0.21340025	0.68861400

이럴수가...

반전의 결과가 나왔다. 인덱스까지 사용했는데 왜 FULLTEXT 검색이 더 느릴까?

 

알고보니, 테스트용으로 같은 텍스트 값을 몇 만 개를 넣었기 때문에 Index가 소용이 없었던 것이였다.

MySQL 샘플 데이터베이스로 비교해보니 FULLTEXT 검색이 훨씬 빠른 것을 확인했다.

SELECT * FROM world.city 
WHERE match(Name) AGAINST('seoul' in boolean mode);

SELECT * FROM world.city 
WHERE Name rlike 'seoul';

	REGEXP	FULLTEXT
Duration	0.0055	0.0019

 

 

참고 사이트

MySQL 풀 텍스트(FULLTEXT) 검색하기

Mysql Mariadb full-text 풀텍스트 검색 방법 자세히 알아보기

MySQL / MariaDB 서브 쿼리 내의 order by 안 되는 현상 + 해결법