[MSSQL]FULL TEXT INDEX 생성 방법 LIKE 조건 성능 튜닝 방법

MSSQL 에서 성능 쿼리 성능 개선을 하다보면 KEYWORD 검색중 LIKE 절 특히 앞에 %가 붙는 인덱스를 제대로 활용하지 못하는 쿼리는 성능에 극악한 영향을 줍니다. 이럴때 FULL TEXT SEARCH를 활용하면 성능을 향상 시킬 수 있습니다..

 

FULL TEXT SEARCH 하는 방법, FULL TEXT INDEX 생성에 대한 기본적인 개념은 MSDN을 참고하시길 바랍니다.

(https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver15)

 

여기서는 성능 차이와 사용하는 방법 주의 사항에 대해서만 알리고자 합니다.

1. PK 설정

2. 카탈로그 생성

3. 카탈로그에 데이터 생성

4. CONTAINS로 데이터 검색

 

결과 - 쿼리 성능 비교

많지 않은 데이터라 3초에서 0초로밖에 표현되지 않았지만, 쿼리 플랜상으로는 엄청난 차이입니다.

 

아래 예시를 보고 참고하세요~

DROP TABLE FT_SEARCH_TEST
CREATE TABLE FT_SEARCH_TEST (ID INT NOT NULL IDENTITY(1,1) , NAME VARCHAR(1000)  CONSTRAINT [PK_FT_SEARCH_TEST] PRIMARY KEY CLUSTERED (ID))

/*
주소, 간판 명칭 데이터 채우기 300만건
*/
--인덱스
CREATE INDEX IDX_FT_SEARCH_TEST_1 ON FT_SEARCH_TEST (NAME)

SELECT * FROM FT_SEARCH_TEST WHERE NAME LIKE '%광나루%' --3''
SELECT * FROM FT_SEARCH_TEST WHERE CONTAINS(NAME,'광나루') --0''

--fulltext 인덱스를 위한 카탈로그
CREATE FULLTEXT CATALOG fx_FT_SEARCH_TEST
GO

--fulltext 인덱스 생성 및 등록
CREATE FULLTEXT INDEX ON TEST.DBO.FT_SEARCH_TEST( NAME LANGUAGE [Korean] )
KEY INDEX PK_FT_SEARCH_TEST ON fx_FT_SEARCH_TEST
WITH CHANGE_TRACKING AUTO
GO
ALTER FULLTEXT INDEX ON TEST.DBO.FT_SEARCH_TEST

--fulltext 인덱스 시작
START FULL POPULATION
GO

--fulltext 인덱스 지우기
--DROP FULLTEXT INDEX ON TEST.DBO.FT_SEARCH_TEST;

--fulltext 인덱스 정보보기
SELECT TOP 10 * FROM sys.fulltext_indexes;
SELECT TOP 10 * FROM sys.dm_fts_index_keywords(DB_ID(), OBJECT_ID('dbo.FT_SEARCH_TEST'));
SELECT TOP 10 * FROM sys.dm_fts_parser (N'" 광나루 "', 1042, 0, 0);

 

--fulltext 인덱스 정보보기 : 결과

SELECT TOP 10 * FROM sys.fulltext_indexes;

SELECT TOP 10 * FROM sys.dm_fts_index_keywords(DB_ID(), OBJECT_ID('dbo.FT_SEARCH_TEST'));

SELECT TOP 10 * FROM sys.dm_fts_parser (N'" 광나루 "', 1042, 0, 0);

 

full text index 와 일반 인덱스 비교

full text index	regular index
테이블당 하나만 생성 가능	테이블당 여러개 생성 가능
데이터 추가시 population 호출 필요, 자동갱신아님	자동갱신임
하나의 데이터베이스에 대해 카탈로그로 그룹화 관리됨	그룹안됨