Full text search 전문검색 사용하기

다음과 같은 경우 인덱스를 사용하기 힘들기 때문에 스캔이 발생하게 됩니다.

1. 앞쪽 %를 붙여서 Like 검색을 사용할 경우
   SELECT * FROM myClient WHERE addr like '%을지로3가%')
2. "을지로 3가"와 "을지로3가"를 같은 데이터로 취급하고 싶어서 공백을 제거한 결과를 비교해야 하는 경우
   SELECT * FROM myClient WHERE replace(addr, ' ', '') = '을지로3가')

 

이런 경우 검색의 성능을 향상시키기 위해 SQL Server에서는 FTS(Full Text Search) 서비스를 제공하고 있습니다. (물론 좀 더 전문적으로 본문 검색을 위해서는 3^rd party 검색 엔진을 도입할 수도 있습니다.)

FTS는 검색어를 인덱싱하여 검색 성능을 향상시켜 주고, 유사어 처리와 같이 검색에 필요한 기능을 제공해 주게 됩니다.

 

 

== FTS서비스를 이용하기 위한 단계

1. SQL Server 설치시 다음과 같이 "전체 텍스트 검색" 항목을 포함하여 설치 합니다.
2. 서비스에서 FTS서비스가 시작되어 있는지 확인
   SQL Full-text Filter Daemon Launcher 서비스가 시작되어 있는지 확인 합니다.
3. SQL Server에서 sp_configure 명령을 수행하여 "default full-text language" 값이 "한국어"인 1042로 설정되어 있는지 확인 합니다.
   만일 이 값이 1042가 아닌 경우 검색 결과의 완성도가 떨어질 수 있기 때문에 다음과 같이 수행하여 1042로 설정 합니다.
        exec sp_configure  'show advanced option', '1'
        ( 'show advanced option' 값이 1이 되어야 'default full-text language'를 변경 할 수 있습니다. )
   
        exec sp_configure 'default full-text language', 1042
        reconfigure with override

1. 이제 FTS 서비스를 이용할 준비가 완료 되었습니다. 
   아래의 샘플 스크립트를 이용하여 FTS를 이용하여 검색을 구성 하시기 바랍니다.

 

== 샘플 스크립트

-- DB 생성 CREATE DATABASE FT_DATA GO USE FT_DATA GO   -- 테스트 테이블 생성 -- 키가 될 컬럼은 null이 들어갈 수 없도록 NOT NULL 제약조건이 필요합니다. CREATE TABLE myClient (num int not null, name varchar(500), addr varchar(max), regdate datetime) INSERT INTO myClient SELECT 1,'manha', '을지로3가', GETDATE() INSERT INTO myClient SELECT 2,'smgo', '을지로 3가', GETDATE() INSERT INTO myClient SELECT 3,'eulim', '종로 3가', GETDATE() INSERT INTO myClient SELECT 4,'manha2', '을지로33가', GETDATE()   -- 인덱스 생성 -- FTS 서비스에서 사용할 인덱스는 NOT NULL 조건과 UNIQUE 조건이 필요합니다. CREATE UNIQUE INDEX idx_u_myClient_1 ON myClient (num)   -- 생성된 DB에서 FTS를 이용할 수 있도록 설정 exec sp_fulltext_database @action='enable'   -- 기본 카탈로그 생성 CREATE FULLTEXT CATALOG FT AS DEFAULT   -- 생성된 카탈로그 조회 SELECT * FROM sys.fulltext_catalogs   -- FTS용 인덱스 생성. 이때 키로 이용할 인덱스를 지정합니다. (이전 단계에서 생성한 인덱스 이용) -- http://msdn.microsoft.com/ko-kr/library/ms187317.aspx CREATE FULLTEXT INDEX ON myClient(addr) KEY INDEX idx_u_myClient_1 ON FT WITH CHANGE_TRACKING AUTO   -- FTS 인덱스 속성 확인 SELECT OBJECT_NAME(object_id), * FROM sys.fulltext_index_columns;   -- 데이터 조회 테스트 SELECT * FROM myClient   -- 다음 3개 쿼리는 모두 동일한 결과를 반환 함 SELECT * FROM myClient WHERE contains(addr, '을지로3가') SELECT * FROM myClient WHERE contains(addr, '을지로 AND 3가') SELECT * FROM myClient WHERE contains(addr, '"을지로 3가"') SELECT * FROM myClient WHERE contains(addr, '을지로')   SELECT * FROM myClient WHERE contains(addr, '3가')   SELECT * FROM myClient WHERE contains(addr, '33가')

참조:http://fai.cafe24.com/xe/pepe/6219