1. 문제 상황
게시물 검색 시, 제목뿐만 아니라 게시물 내용과도 일치하는 결과를 조회할 수 있게 해달라는 고객사의 요청사항을 받았다.
이를 해결하기 위해 기존의 게시물 조회 쿼리에서 CONTENT LIKE '%{검색 키워드}%' 를 추가하여 게시물 내용과 검색 키워드가 일치하는 게시물을 조회할 수 있도록 쿼리를 수정했다.
그러나, 이 쿼리 수정으로 인해 조회 시간이 2분 이상 소요되는 문제가 발생하였고, 검색 성능이 크게 악화되었다.
2. 문제 원인
💾 실제 데이터베이스에 저장된 게시물 내용
운영 서버의 데이터베이스를 확인한 결과, 최대 170만자에 이르는 지나치게 큰 게시물 내용 데이터가 조회 성능 악화의 원인임을 알아냈다.
문서나 표를 포함한 게시물을 에디터에서 작성할 때, 텍스트와 함께 HTML 태그가 자동으로 삽입되어 실제 작성한 내용보다 훨씬 더 많은 데이터가 저장되고 있었다.
170만자 데이터를 %{검색 키워드}% 조건으로 조회할 경우, 상황에 따라 1~2초 정도의 시간이 소요되고 이러한 데이터가 100건일 경우 100~200초의 시간이 소요된다.
3. 문제 해결 실패 과정
(1) 인덱스 적용
첫 시도는 조회 속도를 O(n) 에서 O(logN)으로 줄일 수 있는 인덱스를 적용하려고 했다.
하지만, SQL Server에서는 인덱스 키의 최대 크기가 900바이트로 NVARCHAR(MAX) 필드에 대해 직접적으로 인덱스를 생성하는 것은 불가능하였다.
(2) 풀텍스트 인덱스
두 번째 시도는 긴 텍스트 필드에 대해 효율적인 검색을 제공하며, 단어 또는 구문 검색에 최적화된 풀텍스트 인덱스를 적용하려고 했다.
하지만, 풀텍스트 인덱스를 적용할 경우
인덱스 생성과 업데이트 과정에서 많은 시간과 시스템 리소스를 소모하며, 빈번한 데이터 변경 시 성능 저하가 발생할 수 있다는 점,
대규모 인덱스를 처리하는 과정에서 전체 데이터베이스 성능이 저하될 수 있으며, 다른 쿼리와 리소스 경쟁이 발생할 수 있다는 점을 고려하여 풀텍스트 인덱스를 적용하지 못했다.
4. 해결 방법
검색 성능을 개선하기 위해 검색에 불필요한 HTML 태그를 제거하고, 순수 텍스트만을 추출하여 새로운 데이터를 생성하고, 해당 데이터를 기반으로 데이터를 검색 기능을 수행할 수 있도록 수정하였다.
(1) 데이터 파싱 메서드
게시물을 등록할 때, 기존 데이터에서 검색에 불필요한 HTML 태그를 제거하고, 순수 텍스트만을 추출하는 메서드를 데이터베이스에 선언한다.
📄 removeHtmlTags 메서드
-- HTML 텍스트 추출 함수 생성
CREATE FUNCTION dbo.removeHtmlTags
(
@input NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @output NVARCHAR(MAX) = '';
DECLARE @length INT;
DECLARE @index INT = 1;
DECLARE @currentChar NCHAR(1);
DECLARE @insideTag BIT = 0; -- HTML 태그 안에 있는지 여부를 추적
-- 문자열의 길이를 계산합니다.
SET @length = LEN(@input);
-- 각 문자를 하나씩 확인하는 루프
WHILE @index <= @length
BEGIN
-- 현재 문자를 가져옵니다.
SET @currentChar = SUBSTRING(@input, @index, 1);
-- HTML 태그 시작 '<'를 만나면 태그 안에 있다고 표시
IF @currentChar = '<'
BEGIN
SET @insideTag = 1;
SET @index = @index + 1;
CONTINUE;
END
-- HTML 태그 끝 '>'를 만나면 태그 안에 있는 상태를 종료
IF @currentChar = '>'
BEGIN
SET @insideTag = 0;
SET @index = @index + 1;
CONTINUE;
END
IF @insideTag = 1 -- 태그 내부인 경우
OR @currentChar = CHAR(9) -- Tab
OR @currentChar = CHAR(10) -- Newline
OR @currentChar = CHAR(13) -- Carriage return
OR @currentChar = ' ' -- Space
OR @currentChar = ' ' -- Full-width space
BEGIN
SET @index = @index + 1;
CONTINUE;
END
-- 태그 안에 있지 않은 경우에만 출력을 추가
IF @insideTag = 0
BEGIN
SET @output = @output + @currentChar;
END
-- 인덱스를 증가시킵니다.
SET @index = @index + 1;
END
-- 등의 특수 문자를 제거
SET @output = REPLACE(@output, ' ', '');
RETURN @output;
END
GO(2) 테이블 필드 추가
📄 검색용 필드(SEARCH_CONTEN) 추가
ALTER TABLE POST
ADD SEARCH_CONTENT NVARCHAR(MAX);HTML 태그를 제거하고, 순수 텍스트만을 추출한 데이터를 저장할 검색용 필드(SEARCH_CONTENT)를 추가한다.
(3) 데이터 추출
📄 게시물 저장
INSERT INTO POST (CONTENT, SEARCH_CONTENT)
VALUES (CONTENT, dbo.removeHtmlTags(CONTENT));게시물을 등록할 때, removeHtmlTags() 메서드를 사용하여 검색용 데이터를 추출하고 게시물 테이블(POST)에 추가한다.
(4) 게시물 조회
📄 게시물 조회
SELECT *
FROM POST
WHERE SEARCH_CONTENT LIKE N'%{검색 키워드}%';게시물 내용을 조회할 때, 검색용 데이터(SEARCH_CONTENT)를 기반으로 게시물을 조회할 수 있도록 쿼리를 수정한다.
5. 결과
📄 HTML 태그 제거 후 데이터 크기 변화
removeHtmlTags() 메서드를 사용하여 검색용 데이터를 추출한 결과, 데이터의 크기를 평균 8% 로 압축할 수 있었고, 기존 2분 내외의 게시물 조회 속도를 2초 내외로 개선할 수 있었다.
6. 마무리
테이블에 새로운 필드를 추가하는 것은 관리해야 할 데이터가 늘어나기 때문에 관리 포인트가 증가하고, 이는 추가적인 부담을 가져올 수 있다고 생각합니다.
그러나, 검색 기능이 핵심적인 서비스에서는 검색 전용 데이터를 별도로 관리하는 것이 필요할 것이라고 생각합니다.
물론, 더 나은 개선 방법이 존재할 수 있겠지만, 여기 까지가 내가 스스로 생각해낼 수 있는 최선의 방법인 것 같습니다.
비슷한 사례로 고민하시는 분들께 도움이 되길 바라며, 앞으로도 다양한 문제를 해결하며 경험한 내용을 공유 할 날을 기대하며 글 마무리 하도록 하겠습니다.
