수천만 건 공공데이터 부분 검색 속도를 10배 향상시키는 MySQL 8.0 N-gram 전문 검색(Full-Text Index) 아키텍처

전국 사업자 상호명, 요양기관 시설명, 도로명 주소 등 수천만 건의 공공데이터를 서비스하는 백엔드 시스템에서 가장 높은 빈도로 호출되는 기능은 사용자가 입력한 검색어가 상호명이나 주소의 중간에 포함되어 있는지 확인하는 부분 일치(Sub-string) 검색입니다.

하지만 RDBMS의 기본 B-Tree 인덱스는 전방 일치(Prefix Match, LIKE '강남%')에만 인덱스 레인지 스캔이 동작하며, 전방 와일드카드가 포함된 LIKE '%강남%' 쿼리는 인덱스를 완전히 무시하고 수천만 건의 레코드를 처음부터 끝까지 읽는 풀 테이블 스캔(Full Table Scan)을 발생시킵니다. 데이터가 늘어날수록 CPU 사용률이 100%에 도달하고 쿼리 지연이 수 초대로 급증합니다. 별도의 무거운 검색 엔진(Elasticsearch)을 도입하기 부담스러운 서비스 환경에서, 본 글은 MySQL 8.0 InnoDB 내장 N-gram 전문 검색(Full-Text Search) 파서의 동작 메커니즘과 불리언 모드(Boolean Mode)를 활용한 고성능 부분 일치 인덱스 아키텍처를 분석합니다.

1. B-Tree 와일드카드 검색의 한계와 N-gram 역색인 원리

B-Tree 인덱스는 사전순(Lexicographical order)으로 정렬된 리프 노드를 이진 탐색하므로, 문자열의 시작 지점을 모르는 %keyword% 패턴은 탐색 범위를 좁힐 수 없습니다. 반면 MySQL 8.0의 InnoDB 전문 검색 엔진은 문서를 토큰 단위로 분해하여 단어가 위치한 문서 ID를 매핑하는 역색인(Inverted Index) 구조를 생성합니다.

• 공백 기반 파서의 한계: MySQL 기본 전문 검색 파서는 띄어쓰기(공백) 단위로 단어를 분리하므로, 조사가 붙거나 복합명사가 연속되는 한국어·중국어·일본어(CJK) 환경에서는 단어 중간 검색이 불가능합니다.
• N-gram 슬라이딩 윈도우: ngram 파서는 문자열을 N개(기본값 2)의 연속된 글자 단위로 분할하는 슬라이딩 윈도우 방식을 취합니다. 예를 들어 ‘연세정형외과’는 [‘연세’, ‘세정’, ‘정형’, ‘형외’, ‘외과’]의 2글자 바이그램(Bigram) 토큰들로 쪼개져 역색인 테이블에 보관됩니다.
• 부분 일치 매핑: 사용자가 ‘정형’을 검색하면 역색인에서 ‘정형’ 토큰을 포함하는 문서 리스트를 $O(1)$에 직접 찾아내므로 풀 테이블 스캔 없이 수천만 건 데이터를 밀리초 단위로 필터링합니다.

2. N-gram 전문 검색 테이블 DDL 및 불리언 모드 쿼리 설계

N-gram 인덱스를 구성할 때는 반드시 WITH PARSER ngram 절을 명시해야 하며, 정확한 검색 적중률을 위해 IN BOOLEAN MODE 연산자를 적용합니다.

-- 1. 공공데이터 시설물 테이블 생성 및 N-gram 전문 검색 인덱스 추가
CREATE TABLE public_organizations (
    org_id BIGINT NOT NULL AUTO_INCREMENT,
    biz_no VARCHAR(20) NOT NULL,
    org_name VARCHAR(150) NOT NULL,
    address VARCHAR(255) NOT NULL,
    category VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (org_id),
    -- N-gram 파서를 지정한 Full-Text Index 생성
    FULLTEXT INDEX idx_ft_org_name (org_name) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. 고속 부분 일치 검색 쿼리 실행
-- '+단어': 반드시 포함되어야 함 (AND 조건)
SELECT org_id, biz_no, org_name, address
FROM public_organizations
WHERE MATCH(org_name) AGAINST('+정형* +외과*' IN BOOLEAN MODE)
LIMIT 30;

3. Python 검색 API 연동 및 불리언 연산자 전처리 파이프라인

사용자가 입력한 임의의 검색어를 안전하게 MATCH ... AGAINST 구문으로 변환하기 위해서는 특수문자 이스케이프와 2글자 단위 바이그램 필수 연산자(+) 조합 전처리가 필수적입니다.

# N-gram 불리언 모드 검색어 자동 정제 및 쿼리 빌더
import re
from typing import List

class NgramQuerySanitizer:
    @staticmethod
    def build_boolean_query(user_keyword: str) -> str:
        """
        사용자 검색어를 MySQL ngram IN BOOLEAN MODE용 질의어로 변환
        예: '연세 정형' -> '+연세* +정형*'
        """
        # 1. 전문 검색 특수 연산자 문자 제거 (+, -, *, ~, @, <, >, (, ), ")
        cleaned = re.sub(r'[\+\-\*\~\@\<\>\(\)"\']', ' ', user_keyword)
        tokens = [token.strip() for token in cleaned.split() if len(token.strip()) >= 2]
        
        if not tokens:
            return ""

        # 2. 각 토큰에 대해 필수 포함(+) 및 와일드카드(*) 적용
        boolean_terms = [f"+{token}*" for token in tokens]
        return " ".join(boolean_terms)

# 사용 예시:
# sanitizer = NgramQuerySanitizer()
# query_term = sanitizer.build_boolean_query("서울아산병원 종합")
# 결과: '+서울아산병원* +종합*' -> MySQL MATCH AGAINST 질의어 투입

4. 결론 및 도입 성과

수천만 건의 공공데이터 테이블에서 무분별한 LIKE '%...%' 쿼리는 데이터베이스 엔진의 전역 I/O 병목을 유발하는 주범입니다. MySQL 8.0 내장 ngram 전문 검색 인덱스를 적용하면 별도의 검색 인프라(ES, Solr)를 증설하지 않고도 쿼리 레이턴시를 3초 이상에서 10ms 이내로 단축하는 고효율 검색 아키텍처를 완성할 수 있습니다.

출처 및 1차 공식 기술 문서

• MySQL 8.0 Reference Manual — ngram Full-Text Parser and Token Size
• MySQL 8.0 Reference Manual — Boolean Full-Text Searches
• MySQL 8.0 Reference Manual — InnoDB Full-Text Index Architecture