SQLite FTS5 trigram으로 한글·부분 문자열 검색하기 — 3글자 미만은 LIKE로

Cliply의 핵심 경험은 "몇 글자 치면 바로 나온다"이다. 그래서 검색에는 세 가지 조건을 걸었다.

dock만 쳐도 docker가 나와야 한다 (부분 문자열)
컨테이너, 로그 같은 한글도 단어 중간부터 찾아야 한다
스니펫 5,000개에서 50ms 안에 답해야 한다

LIKE '%검색어%'로 시작하면 1·2번은 쉽지만 3번과 정렬(관련도)이 문제가 된다. 그래서 SQLite에 내장된 전문 검색 엔진 FTS5를 쓰기로 했다.

왜 trigram 토크나이저인가

FTS5의 기본 토크나이저 unicode61은 공백과 문장 부호로 단어를 자른다. docker logs -f는 docker, logs, f가 된다. 이 방식은 단어 단위 검색에는 좋지만 ocke처럼 단어 중간을 찾지 못한다. 접두어 검색(dock*)은 되지만 한글 조사가 붙은 경우(컨테이너의)나 단어 중간 검색에는 약하다.

trigram 토크나이저(SQLite 3.34+)는 문자열을 세 글자씩 겹쳐 자른다. docker는 doc, ock, cke, ker가 된다. 검색어도 같은 방식으로 잘라 모두 포함된 행을 찾으니, 어떤 위치의 부분 문자열이든 찾을 수 있다. 한글도 글자 단위로 똑같이 동작한다.

CREATE VIRTUAL TABLE IF NOT EXISTS SnippetsFts USING fts5(
    SnippetId UNINDEXED,
    Title,
    Body,
    Description,
    Tags,
    tokenize = 'trigram'
);

SnippetId는 원본 Snippets 테이블과 잇는 열쇠라 UNINDEXED로 뒀다. 태그는 여러 개를 공백으로 이어 붙여 Tags 열 하나에 넣는다.

EF Core가 모르는 테이블은 raw SQL로

EF Core는 FTS5 가상 테이블을 마이그레이션으로 만들 수 없다. 그래서 테이블 생성은 앱 시작 때 FtsInitializer가 맡는다. 순서는 Database.Migrate() → FTS 테이블 확인 → 빈 DB면 샘플 시드다.

private async Task CreateTableAsync(CliplyDbContext context, CancellationToken cancellationToken)
{
    try
    {
        await context.Database.ExecuteSqlRawAsync(string.Format(CreateTableSqlTemplate, "trigram"), cancellationToken);
    }
    catch (SqliteException ex)
    {
        // SQLite 3.34 미만은 trigram 미지원
        logger.LogWarning(ex, "trigram 토크나이저를 쓸 수 없어 unicode61로 FTS 테이블을 만듭니다.");
        await context.Database.ExecuteSqlRawAsync(string.Format(CreateTableSqlTemplate, "unicode61"), cancellationToken);
    }
}

Microsoft.Data.Sqlite가 들고 오는 SQLite는 trigram을 지원하지만, 혹시 모를 환경에 대비해 unicode61로 물러서는 길을 남겼다. 검색 쪽은 sqlite_master에 저장된 테이블 정의에 trigram이 들어 있는지 한 번 보고 동작을 바꾼다. unicode61이면 각 토큰 뒤에 *를 붙여 접두어 검색으로 바꾼다.

트리거 대신 저장할 때 직접 색인

FTS 색인을 원본과 맞추는 흔한 방법은 트리거다. 하지만 태그는 다대다 관계 테이블(SnippetTags)에 있어서, 트리거로 "스니펫의 태그 문자열"을 만들려면 여러 테이블에 트리거를 걸어야 한다. 그래서 트리거를 쓰지 않고, 저장소(SnippetRepository)가 스니펫을 저장하거나 지울 때 같은 트랜잭션 안에서 FTS 행을 DELETE 후 INSERT 한다.

public static async Task IndexAsync(CliplyDbContext context, Snippet snippet, CancellationToken cancellationToken = default)
{
    await RemoveAsync(context, snippet.Id, cancellationToken);
    await InsertAsync(context, snippet, cancellationToken);
}

대신 안전장치를 하나 뒀다. 앱을 시작할 때 Snippets 수와 SnippetsFts 수를 비교해서 다르면 전체를 다시 색인한다. 비정상 종료나 DB를 직접 고친 경우에도 검색이 어긋난 채로 남지 않는다.

여기서 주의할 점이 하나 있다. 원본 테이블은 GUID를 소문자 문자열(D 형식)로, 시각은 yyyy-MM-ddTHH:mm:ss.fffffffZ 고정 길이 문자열로 저장하도록 ConfigureConventions에서 일괄 변환해 두었다. raw SQL로 FTS 테이블과 조인할 때도 같은 형식(FtsIndexer.Key(id))을 써야 한다. 형식이 하나라도 다르면 조인이 조용히 0건이 된다.

trigram의 함정: 3글자 미만은 못 찾는다

trigram은 세 글자 조각으로 찾으니, 검색어가 두 글자 이하면 조각을 만들 수 없다. ps, ip, 로그, 삭제 같은 검색어가 아무것도 찾지 못한다. 명령어 노트에서 두 글자 검색은 아주 흔하다.

그래서 검색어를 토큰으로 나눈 뒤 길이로 갈랐다.

var textTokens = query.Terms.Concat(query.Phrases).ToList();
var ftsTokens = usesTrigram ? textTokens.Where(t => t.Length >= MinFtsTermLength).ToList() : textTokens;
var likeTokens = usesTrigram ? textTokens.Where(t => t.Length < MinFtsTermLength).ToList() : [];

3글자 이상 → SnippetsFts MATCH
3글자 미만 → FTS 테이블의 각 열에 LIKE '%토큰%'

두 조건은 AND로 함께 걸린다. docker 로그라고 치면 docker는 FTS로, 로그는 LIKE로 찾는다. LIKE 대상은 원본 테이블이 아니라 FTS 테이블의 열이다. 태그가 이미 문자열로 합쳐져 있어서 조인을 늘리지 않아도 된다. LIKE에 들어가는 %, _, \는 이스케이프한다.

정렬: 제목이 본문보다 먼저

FTS5의 bm25()는 열마다 가중치를 줄 수 있다. 제목에 걸린 결과가 본문 어딘가에 걸린 결과보다 위에 와야 하므로 이렇게 정했다.

/// <summary>bm25 가중치: SnippetId(미색인), Title, Body, Description, Tags</summary>
private const string Bm25 = "bm25(SnippetsFts, 0.0, 10.0, 1.0, 1.0, 5.0)";

열	가중치
Title	10
Tags	5
Body	1
Description	1

최종 정렬은 다음 순서다.

검색어 있음: 고정(핀) → bm25 → 사용 횟수 → 최근 사용
검색어 없음: 고정 → 최근 사용 → 사용 횟수 → 제목

LIKE 폴백만으로 찾는 경우에는 bm25 점수가 없다. 이때도 제목 우선 원칙을 지키려고 "제목에 걸린 토큰 수"를 정렬 키로 넣었다.

var titleHits = likeParams.Select(p => $"(SnippetsFts.Title LIKE {p} ESCAPE '\\')");
sql.Append(", (").Append(string.Join(" + ", titleHits)).Append(") DESC");

SQLite에서 비교식은 0 또는 1이라 더하면 개수가 된다.

검색 문법은 파서 하나로

빠른 검색창에서는 #docker @bash "tail -f" 로그처럼 섞어서 친다. 이 문자열은 SearchQueryParser가 네 가지로 나눈다.

입력	의미	SQL
`log tail`	모든 단어 포함	FTS MATCH 또는 LIKE
`#docker`	태그 필터	`EXISTS (SnippetTags JOIN Tags ...)`
`@sql`	언어 필터	`Language IN (...)`
`"exact phrase"`	구문 일치	FTS 구문 문자열

파서는 Core 프로젝트에 있고 DB를 모른다. 닫는 따옴표가 없으면 끝까지를 구문으로 보고, 같은 토큰이 두 번 나오면 하나로 합친다. 태그는 저장할 때와 같은 정규화(# 제거, 소문자)를 거친다. 검색어를 FTS 문자열로 넘길 때는 각 토큰을 큰따옴표로 감싸고 안쪽 따옴표를 두 번 써서, 사용자가 친 -나 *가 FTS 연산자로 해석되지 않게 했다.

테스트는 실제 마이그레이션 그대로

검색 테스트는 SQLite in-memory DB를 쓴다. 중요한 점은 실제 마이그레이션과 FtsInitializer를 그대로 돌린다는 것이다. EF Core의 InMemory 공급자로는 FTS5도 raw SQL도 확인할 수 없다.

var connection = new SqliteConnection("Data Source=:memory:");
await connection.OpenAsync();
// ... MigrateAsync() → FtsInitializer.EnsureCreatedAsync()

연결을 열어 둔 동안만 DB가 살아 있으니, 테스트마다 새 연결을 만들고 끝나면 닫는다. 시간은 TimeProvider를 주입해 ManualTimeProvider로 고정했다. "최근 사용 순" 같은 정렬을 시간에 흔들리지 않고 확인할 수 있다.

테스트 이름은 한글로 썼다. 실패하면 이름만 보고도 무엇이 깨졌는지 알 수 있다.

[Theory]
[InlineData("ps")]
[InlineData("로그")]
public async Task 세_글자_미만은_LIKE로_폴백해서_찾는다(string query)

이 밖에 제목_일치가_본문_일치보다_먼저, 검색어가_있어도_핀이_먼저, LIKE_특수문자는_그대로_비교한다, 삭제된_스니펫은_검색되지_않는다, 색인_개수가_어긋나면_시작_시_전체_재색인 같은 항목을 테스트로 고정했다. 성능은 스니펫 5,000개를 넣고 검색해 6ms 이하를 확인했다. 목표 50ms에 비하면 넉넉하다.

정리

부분 문자열·한글 검색은 FTS5 trigram 토크나이저로 해결된다.
EF Core가 모르는 가상 테이블은 시작 시 raw SQL로 만들고, 개수 비교로 색인을 맞춘다.
태그처럼 여러 테이블에 걸친 값은 트리거보다 저장 코드에서 직접 색인하는 편이 단순하다.
trigram은 3글자 미만을 못 찾는다. 짧은 토큰은 LIKE로 폴백하고, 정렬에서도 제목 우선을 지킨다.
검색 테스트는 실제 마이그레이션을 거친 in-memory SQLite로 한다.

다음 글에서는 이 검색을 어느 창에서든 단축키 한 번으로 띄우는 팝업 이야기를 한다.

Cliply 개발기

자주 쓰는 명령어를 단축키 한 번으로 — 「Cliply」 개발기
SQLite FTS5 trigram으로 한글·부분 문자열 검색하기 (이 글)
어느 창에서든 단축키로 뜨는 WPF 검색 팝업 (10월 7일 공개)
{{이름:기본값}} 변수 치환 파서와 입력 창 (10월 7일 공개)
동기화를 대비한 스키마 — GUID, UpdatedAt, 소프트 삭제와 JSON 가져오기 (10월 7일 공개)
트레이 상주 앱 마무리 — 단일 인스턴스, 한/영 전환, 70MB 단일 exe (10월 7일 공개)