[MSSQL] MSSQL LIKE 검색 쿼리 성능 개선기

개요

이번 글에서는 MSSQL에서 Like을 사용한 검색 쿼리를 개선했던 경험과 여러가지 개선 방법에 대해 이야기해 보려고 한다.

 

환경

• Microsoft SQL Server 2016 (SP1)

 

배경

현재 사내에서 Spring + MSSQL을 기반으로 운영 중인 게임 커뮤니티 사이트의 백엔드를 전담하고 있다.

금일 게임 운영실로부터 해당 사이트의 모든 검색 시스템이 작동하지 않는다는 연락을 받게 된다.

이전부터 게시글 조회 관련 API의 Latency가 길었어서 두고두고 쿼리 리팩토링을 진행하려고 했었는데 오늘 갑자기 서비스에 장애가 발생한 상태인 것이다.

 

문제 원인

장애가 생겼다는 소식을 전달받자마자 인프라팀에 모니터링 대시보드를 요청 해당 DB 서버의 CPU 및 메모리 사용률을 확인하였다.

 

게임 커뮤니티 이외에 다른 프로젝트에서도 사용하는 DB 서버였기에 처음에는 스펙업을 고려했었다.

대시보드를 확인해보니 메모리 사용률은 50%, CPU 사용률은 평소에는 10% ~  40% 이하를 유지하는 상황이 많아 오히려 스펙다운이 필요했었고 특이사항으로 특정 시점에 CPU 사용률이 100%까지 치솟는 현상을 확인할 수 있었다.

위 현상이 게시글의 조회 쿼리로 인해 발생하는 문제임을 확인할 수 있었다.

 

이후에는 해당 조회 쿼리를 확인하였다.

게시물의 메인이나 카테고리별 게시글 조회, 검색 등 모두 하나의 쿼리에서 파라미터 값에 따라(카테고리 ID의 여부, 검색 키워드의 여부 등) 동적으로 처리하는 구조였다. 

검색 시에는 Like을 사용하도록 쿼리가 구성되어 있었고 장애가 발생하는 시점 또한 사용자가 게시글의 내용 검색을 할 때였다.

그렇다면 쿼리에서 Like을 사용할 때 문제가 발생한다는 것을 도출해낼 수 있다.

 

게시글 테이블의 내용 컬럼은 NTEXT을 자료형을 사용하고 있었다.

해당 쿼리의 실행 계획을 분석해 본 결과 인덱스를 타지 않고 FULL SCAN이 발생하여 쿼리의 성능이 엄청나게 저하된 것이다.

5자의 검색 키워드를 넣고 쿼리를 실행하면 결과가 나오는 시간이 10초 ~ 20초 정도 걸렸었다.

장애가 발생한 것은 당연한 수순이였다.

 

Like 검색 쿼리

SELECT * FROM '테이블명' WHERE 제목 LIKE '%검색어%'

위 쿼리는 MSSQL에서 Like을 이용해 검색 쿼리를 작성한 예시이다.

하지만 위 쿼리는 와일드카드인 '%'의 위치에 따라 인덱스를 타지 않는 경우가 있다.

SELECT * FROM '테이블명' WHERE 제목 LIKE '검색어%'

위 쿼리는 인덱스를 타지만

SELECT * FROM '테이블명' WHERE 제목 LIKE '%검색어'

SELECT * FROM '테이블명' WHERE 제목 LIKE '%검색어%'

위 두 쿼리는 모두 인덱스를 타지 않는다.

이 때문에 검색어가 길어지고 쿼리가 복잡해질수록 엄청난 성능 저하를 가져올 수 있다.

 

하지만 와일드카드의 위치를 변경할 수는 없다.

와일드카드의 위치에 따라 쿼리의 결과값이 달라지기 때문이다.

장애를 해결하기 위해서는 기존에 있던 조회 API의 결과값은 동일하게 유지하되 오로지 쿼리의 속도를 개선하는 방법으로 방향을 좁혀가야만 했다.

 

해결하기 - FULL TEXT SEARCH 적용

API의 결과값은 동일하게 유지하되 오로지 쿼리의 속도를 개선하는 방법을 두고 빠르게 고민했어야 했다.

 

그러다 SQL Server의 TEXT vs VARCHAR에 관련된 비교 글을 접했다.

게시글 테이블이 있다고 가정하고 이 게시글에 대한 컬럼 'content'가 NTEXT형으로 되어 있을 때

NTEXT는 일반적인 문자열 인덱스를 지원하지 않기에 Like을 통한 검색 쿼리 사용 시 성능 저하를 야기할 수 있다.

 

초기에는 해당 테이블에 대해 검색할 때 인덱스를 타지 않아도 쿼리가 느리다고 잘 체감이 되지 않지만 데이터가 쌓이면 쌓일수록 쿼리가 복잡해지고 검색어가 길어질수록 점점 성능이 떨어지는 것을 체감할 수 있다. 

만약 위와 같은 문제로 조회 쿼리가 10 ~ 20초를 넘어가여 서비스에 지연이 생기거나 혹은 DB에 Lock이 걸려 최종적으로 서비스의 장애가 초래될 수도 있다.

 

기존 NTEXT 컬럼을 NVARCHAR로 변경해서 인덱스를 구성하는 것은 데이터 유실 가능성이 있기에 FULL TEXT SEARCH를 적용하는 방법을 선택했다.

 

FULL TEXT SEARCH란 전문 검색에 특화된 기능으로, 인덱스를 이용하여 텍스트 데이터를 검색한다.

이를 사용하면 정확한 검색어나 유사한 단어를 포함한 문서를 빠르게 검색할 수 있다.

MSSQL에서는 아래와 같은 방법으로 FULL TEXT SEARCH를 사용할 수 있다.

1.1. Full TEXT SEARCH 환경 구성

• 데이터베이스에 FULL TEXT SEARCH를 설치
• FULL TEXT SEARCH 카탈로그 생성

1.2. Full TEXT SEARCH 인덱스 생성

CREATE FULLTEXT INDEX ON '테이블명'
('컬럼명' LANGUAGE '언어 ID')
KEY INDEX 'PK 인덱스'
ON '카탈로그명'

위와 같은 형태를 통해 FULL TEXT SEARCH 인덱스를 생성할 수 있다.

• '컬럼명'에는 FULL TEXT SEARCH 인덱스로 만들 열의 이름이 들어간다.
• '언어 ID'에는 인덱스를 만들어야 하는 언어는 언어 ID를 통해 지정할 수 있다. 한국어는 1042, 영어는 1033을 사용한다.
• 'PK 인덱스'에는 PK 열에 대한 인덱스가 들어간다.

'카탈로그명'에는 1.1. 단계에서 생성했던 카탈로그의 이름이 들어간다.

1.3. 검색 쿼리 작성

SELECT * FROM '테이블명' WHERE CONTAINS('컬럼명', '검색어')

위 쿼리는 검색어가 문서에 모두 포함된 레코드를 반환한다.

SELECT * FROM '테이블명' WHERE FREETEXT('컬럼명', '검색어')

위 쿼리는 검색어와 유사한 모든 레코드를 반환한다.

 

=> 즉, CONTAINS은 검색어가 포함된 레코드만 반환하므로 FREETEXT 보다 검색 결과 정확도가 높다고 할 수 있다.

검색 대상에 따라 CONTAINS와 FREETEXT 중 적합한 것을 사용하면 된다.

검색 결과의 정확도가 중요시된다면 CONTAINS를 유사성을 고려한다면 FREETEXT를 사용하면 된다.

 

이렇게 FULL TEXT SEARCH를 구성하고 다시 검색 쿼리를 실행해 보니 데이터 10만 건 기준 기존 10 ~ 20초가 넘어가던 쿼리가 무려 0초대 중후반까지 개선된 것을 확인할 수 있었다.

여기에 추가로 서브쿼리를 조인으로 변경하고 애플리케이션에 캐시를 적용하면 조회 API 성능은 더더욱 개선될 것 같다.

 

검색 쿼리 개선 방법(2)  - 데이터 분할

FULL TEXT SEARCH에 이어서 또 다른 성능 개선 방법으로 데이터를 분할하여 저장하는 방법이 있다.

 

예를 들어 ntext형 데이터를 검색하는 쿼리가 성능 저하를 발생시킨다고 가정했을 때

이 ntext형 데이터를 여러 개의 nvarchar로 분할하여 저장할 수 있다.

이를 통해 대용량 문자열 데이터를 저장하며 검색 성능을 향상시킬 수 있다.

 

위 방법을 사용하기 위해 먼저 NTEXT와 NVARCHAR의 차이를 알고 가야 한다.

구분	ntext	nvarchar
문자열 인덱스 생성 지원	X	O
저장 공간의 가변적 할당	X	O

NTEXT는 문자열 인덱스 생성을 지원하지 않는 반면에 NVARCHAR는 인덱스 생성을 지원하고 데이터의 길이에 따라 동적으로 저장 공간을 할당하기에 공간 효율성이 높아 NTEXT에 비해 검색 성능이 좋다고 할 수 있다.

 

기존 NTEXT 데이터를 작은 조각으로 나누어 각각의 NVARCHAR 컬럼에 저장하는데 이 컬럼들이 순서대로 이어져 전체 텍스트를 나타낼 수 있다. 

각 컬럼에 대해 검색을 수행하고 결과를 병합하여 반환하는 형식으로 사용될 수 있다.

 

이 방법은 대용량 텍스트 데이터를 검색할 때 높은 성능을 보장할 수는 있지만 데이터를 분할하여 저장하기 때문에 관리에 복잡함을 불러올 수 있다.

또한 데이터 업데이트시에는 모든 열에 대해 일괄적 업데이트가 필요하므로 트랜잭션 처리 시간이 늘어날 수 있다는 단점이 존재한다.