답변.

질문의 역사:
원래 SQL은 구조화된 데이터 작업에 주로 사용되었으며, 텍스트 필드에서의 검색은 LIKE와 같은 간단한 연산으로 제한되었습니다. 텍스트 정보의 양이 증가함에 따라 기사, 메시지, 블로그 등 큰 텍스트에서 빠르고 유연하게 검색할 필요성이 생겼습니다.

문제:
표준 SQL 도구(LIKE/ILIKE)는 대량의 텍스트에 대해 잘 작동하지 않으며, 관련성을 기준으로 단어를 효과적으로 찾지 못하고, 형태나 단어 간 거리도 고려하지 않기 때문에 성능 저하와 검색 응답 시간이 너무 길어질 수 있습니다.

해결책:
이러한 문제를 해결하기 위해 데이터베이스에 내장된 전체 텍스트 검색(Full-Text Search, FTS) 메커니즘이 사용됩니다. 예를 들어, 전체 텍스트 인덱스와 특별한 연산자(CONTAINS, MATCH AGAINST, tsvector, tsquery)를 사용합니다. 이러한 인덱스는 "단어 카드"("역방향 인덱스")를 구축하여 텍스트 검색을 수십 배 빠르게 만듭니다.

코드 예제 (SQL Server):

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Documents(Content) KEY INDEX PK_Documents;

SELECT * FROM Documents WHERE CONTAINS(Content, '"SQL 프로그래밍"');

주요 특징:

일반 인덱스와 분리된 특별한 전체 텍스트 인덱스를 기반으로 작동합니다.
관련성, 표제어 변환, 중지어 인정 및 복잡한 조건 (AND, OR, 근접성)을 지원하는 쿼리를 지원합니다.
대량 데이터 변경 시 인덱스를 유지해야 하며, 주기적인 재인덱싱이 필요합니다.

함정이 있는 질문.

LIKE를 사용한 검색과 전체 텍스트 검색의 차이는 무엇입니까?

LIKE는 일반적으로 텍스트 인덱스를 사용하지 않는 단순한 패턴 비교 연산이고, 대량의 데이터에 대해서는 느립니다. 전체 텍스트 검색은 특별한 인덱스를 사용하여 형태소 및 관련성을 고려할 수 있습니다.

예:

SELECT * FROM articles WHERE body LIKE '%database%';
-- 느림, 순위 없음

SELECT * FROM articles WHERE MATCH(body) AGAINST ('database');
-- 빠르며, 순위 있음

대량의 삽입 또는 삭제 시 전체 텍스트 인덱스에는 어떤 일이 발생합니까?

대량 변경 후 텍스트 필드의 인덱스는 구식이 되며(때때로 자동 업데이트가 이루어짐), 성능을 회복하기 위해 인덱스 재구성이 필요합니다.

-- MSSQL의 경우
ALTER FULLTEXT INDEX ON Documents START FULL POPULATION;

JSON 또는 XML 유형의 열에서 전체 텍스트 인덱스를 사용할 수 있습니까?

아니요, 대부분의 전체 텍스트 엔진은 JSON/XML 구조를 직접 지원하지 않습니다. 이러한 데이터는 문자열 필드로 추출하거나 특별한 파서/외부 도구(예: Elasticsearch)를 사용해야 합니다.

일반적인 오류 및 안티 패턴

큰 테이블에서 LIKE '%word%' 연산자 사용 — 성능 대재앙
재인덱싱이 수행되지 않으면 검색의 관련성이 떨어집니다.
언어 및 중지어의 특성을 고려하지 않음
추가 리소스 없이 수십 기가바이트의 데이터를 인덱싱

실생활의 예

부정적 사례

회사는 수천만 개의 기사 기록을 보관했습니다. 검색에는 LIKE '%단어%'가 사용되었습니다. IT 부서는 정기적으로 타임아웃을 겪었고, 사용자는 결과를 얻기 위해 10분 이상 기다려야 했습니다.

장점:

추가 라이센스나 설정이 필요하지 않음
간단한 구현

단점:

특히 대량에 대해 성능 저하
시스템 응답 시간 비현실적
검색 결과가 잘못되며(단어 형태를 고려하지 않음)

긍정적 사례

MySQL에서 전체 텍스트 검색(전체 텍스트 인덱스)을 도입했습니다. 검색 속도가 100배 빨라졌고, "유사한" 단어와 구문을 검색할 수 있는 가능성이 생겼으며 순위 기능을 추가했습니다.