Developer Calssess

-- Compute fragmentation information for all full-text indexes on the database
SELECT c.fulltext_catalog_id, c.name AS fulltext_catalog_name, i.change_tracking_state,
       i.object_id, OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS object_name,
       f.num_fragments, f.fulltext_mb, f.largest_fragment_mb,
       100.0 * (f.fulltext_mb - f.largest_fragment_mb) / NULLIF(f.fulltext_mb, 0)  AS fulltext_fragmentation_in_percent
FROM sys.fulltext_catalogs c
         JOIN sys.fulltext_indexes i
              ON i.fulltext_catalog_id = c.fulltext_catalog_id
         JOIN (
    -- Compute fragment data for each table with a full-text index
    SELECT table_id,
           COUNT(*) AS num_fragments,
           CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_mb,
           CONVERT(DECIMAL(9,2), MAX(data_size/(1024.*1024.))) AS largest_fragment_mb
    FROM sys.fulltext_index_fragments
    GROUP BY table_id
) f
              ON f.table_id = i.object_id

출처: https://stackoverflow.com/questions/16061248/how-to-optimize-a-sql-server-full-text-search

전체 텍스트 검색 인덱스 관리

테이블에 전체 텍스트 인덱스를 추가하는 구문은 CREATE FULLTEXT INDEX 구문입니다.

테이블에 전체 텍스트 인덱스를 생성할 때 NO POPULATION옵션을 설정하지 않는 경우 인덱스가 생성되면 즉시 전체 인덱스 채우기가 수행됩니다.

NO POPULATION옵션은 CHANGE_TRACKING옵션이 OFF일 경우에만 사용할 수 있습니다.

CHANGE_TRACKING옵션은 MANUAL, AUTO, OFF 플래그를 갖습니다.

CHANGE_TRACKING 옵션이 MANUAL일 경우 1)인덱싱은 SQL Server 에이전트를 사용하여 일정에 따라 수행되거나 사용자에 의해 수동으로 1)인덱스 채우기를 수행할 수 있습니다.

수동으로 인덱스 채우기를 수행하는 구문은 ALTER FULLTEXT INDEX 구문입니다.

AUTO일 경우 테이블에서 데이터가 수정될 때 전체 텍스트 인덱스를 자동으로 업데이트합니다.

OFF일 경우 인덱싱된 데이터의 변경 내용 목록을 유지하지 않습니다. 이 모드에서만 NO POPULATION옵션을 설정할 수 있는데 SQL Server에서 인덱스를 만든 후 전체 인덱스 채우기를 수행하지 않습니다. MANUAL 모드와 OFF 모드가 다른 부분이 바로 이 부분입니다.

인덱스를 생성한 후 전체 인덱스를 채우려면 ALTER FULLTEXT INDEX구문을 START {FULL | INCREMENTAL} POPULATION절과 함께 실행해야 합니다.

위에서 언급한 내용을 토대로 전체 텍스트 인덱싱을 관리하는 유형을 다음과 같이 정리해 볼 수 있습니다.

1.     변경 내용 자동 추적에 의한 인덱스 자동 채우기

인덱스를 생성할 때 CHANGE_TRACKING옵션을 AUTO로 설정합니다.

관련 테이블의 데이터가 변경될 경우 자동으로 인덱스를 채우게 됩니다.

하지만 즉시 반영되지 않는 경우도 있는데, 이럴 경우 전체 텍스트 인덱싱 프로세스를 모니터링하여 문제를 진단할 수 있습니다. 관련 정보는 다음 링크를 참조하세요.(Transact-SQL 함수를 사용하여 전체 텍스트 속성 값 얻기)

이 모드는 관련 데이터의 갱신이 빈번하지 않고 해당 데이터의 크기가 비교적 작을 경우에 설정하면 SQL Server에 부하를 주지 않고 인덱스를 관리할 수 있습니다.

2.     SQL Server 에이전트의 일정에 의한 인덱스 채우기

인덱스의 CHANGE_TRACKING옵션이 MANUAL이거나 OFF일 경우 전체 텍스트 인덱스 채우기는 일정에 의해 관리할 수 있습니다.

SQL Server 에이전트의 일정은 전체 텍스트 카탈로그 단위로 수행됩니다.

따라서 전체 텍스트 카탈로그에 테이블이나 인덱싱된 뷰를 구성할 경우 데이터의 변경 빈도, 변경되는 데이터의 크기, 변경되는 데이터의 양이 많거나 적은 시간대(요일)등을 고려하여 구성해야 합니다.

이러한 정보가 유사한 테이블이나 뷰를 하나의 전체 텍스트 카탈로그에 구성하면 일정에 의해 수행되는 인덱스 채우기 작업으로 시스템에 부하를 주는 일을 최소화할 수 있습니다.

3.     수동으로 인덱스 채우기 수행(즉시 수행)

인덱스의 CHANGE_TRACKING옵션이 MANUAL이거나 OFF일 경우 관리자(사용자)가 직접 인덱스 채우기를 수행할 수 있습니다.

인덱스 채우기를 직접 실행할 경우 ALTER FULLTEXT INDEX 구문에 {START | STOP | PAUSE | RESUME } POPULATION 절을 사용하여 수동 인덱스 채우기를 수행합니다.

인덱스 채우기를 시작하려면 START {FULL | INCREMENTAL | UPDATE} POPULATION 절을 사용합니다.

FULL모드는 이미 인덱싱된 행을 포함해서 테이블의 모든 행을 검색합니다.  

INCREMENTAL모드는 마지막 채우기 이후 수정된 행만 검색합니다.

단, 해당 테이블에 timestamp 유형의 열이 있는 경우에만 적용할 수 있습니다. timestamp 유형의 열이 없는 경우 FULL채우기를 수행합니다.

UPDATE모드는 해당 인덱스의 변경 내용 추적이 수동으로 설정되어 있을 때 마지막으로 업데이트된 후 모든 삽입, 업데이트 또는 삭제를 처리합니다. 주의할 것은 백그라운드 인덱스 업데이트(일정에 의한) 또는 변경 내용 추적이 자동으로 설정되어 있으면 안 됩니다.

STOP POPULATION은 START POPULATION에 의한 인덱스 채우기를 중지합니다. 백그라운드 인덱스 업데이트 또는 자동 변경 내용 추적을 중지하지 않습니다. 자동 변경 내용 추적을 중지하려면 SET CHANGE_TRACKING OFF절을 사용해야 합니다.

{PAUSE | RESUME} POPULATION 은 START FULL POPULATION에 의한 전체 채우기에만 사용할 수 있습니다.

이상으로 전체 텍스트 인덱스를 관리하는 방법에 대해서 알아보았습니다.

위의 내용을 기초로 전체 텍스트 인덱스를 관리하는 전략을 수립하면 됩니다.

단, 실제 운영하는 데이터베이스에서 전체 텍스트 인덱스를 변경 내용 자동 추적에 의한 채우기를 설정할 것인지 SQL Server 에이전트에 의한 일정으로 채우기를 수행할 것인지의 판단은 데이터의 양의 변화와 변경 빈도 및 접근 빈도를 지속적으로 모니터링하여 결정지어야 합니다.

1) 인덱스 채우기는 인덱싱과 같은 의미로 사용됩니다.

출처: https://devman.tistory.com/entry/SQL-Server-전체-텍스트-검색-3-전체-텍스트-인덱스-관리 [코딩하는 녀석]

-- Full Text Search 를 enable로 설정
exec sp_fulltext_database @action='enable'

-- Full Text Search 한글로 설정되어있는지 확인
EXEC sp_help_fulltext_system_components @component_type = 'wordbreaker', @param = 1042;

-- Full Text Search 에 한글이 설치되어있는지 확인
SELECT * FROM sys.fulltext_languages WHERE lcid='1042';

-- 현재 설치되어있는 언어 목록들
SELECT * FROM sys.fulltext_languages;

-- Catalog 재구성
ALTER FULLTEXT CATALOG [YourCatalogName] REBUILD

-- catalog 진행 현황 SQL
DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'YourCatalogName'
SELECT
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
     ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
                  WHEN 0 THEN 'Idle'
                  WHEN 1 THEN 'Full Population In Progress'
                  WHEN 2 THEN 'Paused'
                  WHEN 3 THEN 'Throttled'
                  WHEN 4 THEN 'Recovering'
                  WHEN 5 THEN 'Shutdown'
                  WHEN 6 THEN 'Incremental Population In Progress'
                  WHEN 7 THEN 'Building Index'
                  WHEN 8 THEN 'Disk Full.  Paused'
                  WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
                  
 -- Full Text indexing
ALTER FULLTEXT INDEX ON [YourDBName] START FULL POPULATION                 


-- 하단은 FREETEXT, FREETEXTTABLE 사용방법

-- FREETEXT 를 이용한 Full Text Searching
SELECT * FROM YOUR_TABLE WHERE FREETEXT(YOUR_COLUMN, 'Searching keywords')

-- FREETEXTTALBE 을 이용한 Full Text Searching
SELECT FT_TBL.*,
       KEY_TBL.RANK
FROM YOUR_TABLE AS FT_TBL
         INNER JOIN FREETEXTTABLE(YOUR_TABLE,
                                  (YOUR_COLUMN_1, YOUR_COLUMN_2 ,YOUR_COLUMN_3),
                                  'Searching keywords',
                                  LANGUAGE N'Korean', 10) AS KEY_TBL
                    ON FT_TBL.Index = KEY_TBL.[KEY]
ORDER BY RANK DESC;
-- column1, column2, column3 순서대로 Rank점수가 산정
-- LANGUAGE N'Korean', 10 => 사용할 언어는 한국어, Rank 점수가 높은 10개를 순서대로 가져오기

Okapi BM25는 검색엔진, 추천 시스템 등에서 사용되는 스코어링 알고리즘이다.

tf(term frequency) 와 idf(inversed document frequency) 의 개념을 바탕으로,

문서의 길이까지 고려를 하여 스코어링을 한다.

Okapi BM25의 기본적인 식은 다음과 같다.

식이 꽤 복잡해 보이지만 알고 보면 tf, idf, 문서 길이만 factor로 사용하며

나머지는 이 세 가지 요소들을 어떻게 weight를 주어 스코어링을 할 것인지를 결정하는 부분이다.

식을 처음부터 살펴보자.

Document (D) 에 Query (Q) 를 날려 얼마나 일치하는지 score를 얻는 것이 목적이다.

만약 검색에서 이 알고리즘을 사용한다면, score가 높은 순서대로 Document가 정렬되어 검색 결과로 나타날 것이다.

Query를 쪼개면 여러 개의 term이 생길 것이다.

제일 쉽게는 띄어쓰기를 기준으로 쪼갤 수 있을 것이다.

각 term을 Document에 적용한 결과를 모두 더하여 score가 결정된다.

idf의 식이다.

해당 term을 가지고 있는 문서의 갯수 / 전체 문서의 갯수 (=df) 를 역수 취하고 로그를 씌운 값이다.

보통 N이 매우 크기 때문에 로그를 씌워 값의 range를 좁혀준다.

(Document set이 1000000개의 Document들로 이루어져 있고, 그 중 검색하는 term이 나온 문서가 10개라면

로그를 씌우지 않았을 때는 값이 100000 이지만, 로그를 씌우면 5가 된다.)

제일 복잡해 보이는 식이다.

일단 tf(td) 는 말 그대로 Document d에서 t라는 term이 얼마나 나타났는지 그 count를 나타내는 것이고,

L(d) 는 현재 Target Document d의 길이,

L(avg) 는 전체 Document set에 있는 모든 Document 길이의 평균을 나타낸다.

(여기서 Document의 길이라 함은 term이 몇개인가? 라고 이해하면 되겠다.)

나머지 k1과 b는, tf와 L값을 어떻게 weight를 주어 계산을 해 줄건지 결정하는 parameter라고 보면 된다.

k1이 0이라면? 모든 텀은 다 지워지고 저 식이 통째로 1이 되겠다.

결국 tf고 L이고 뭐고 아무것도 고려를 안 하겠다는 말이 된다.

k1이 높아지면 높아질수록 tf에 많은 가중치를 두어 계산한다는 뜻이 된다.

그럼 b가 0이라면? 문서의 길이를 고려하지 않겠다는 말이 되고

b가 1에 가까워질수록 문서의 길이에 많은 가중치를 두어 계산한다는 뜻이 되겠다.

(k1과는 달리 b는 1을 넘을 수 없다.)

실험적으로, k1은 1.2와 2.0 사이, b는 0.75로 설정하는 것이 제일 올바른 스코어링을 하게 한다고 하지만

어떤 도메인에서 어떤 검색을 실행할 것인가에 따라 두 값을 조정하여 알고리즘을 튜닝할 수 있겠다.

어쨌든 보자면, score가 높아지려면 tf(td) 의 값은 높아져야 하고,

L(d) / L(avg) 의 값은 낮아져야 한다는 것을 알 수 있다.

여기서 tf는 그렇다 쳐도 문서의 길이는 왜 따지냐고 할 수 있는데, 이것도 간단하다.

100단어로 이루어진 문서 중 내가 날린 term이 한번 나오는 것과,

1000단어로 이루어진 문서 중 내가 날린 term이 한번 나오는 것은 매우 다르기 때문이다.

짧은 문서에서 내가 찾는 핵심 term만 딱! 나오는 게,

긴 문서에서 주저리주저리 하다가 어쩌다 내가 찾는 term이 한번 나오는 것보다는 훨씬 매칭이 잘 되는 문서일 것이다.

이제 총정리를 하면, 어떤 문서와 쿼리가 매칭이 잘 되어 높은 score를 얻기 위해서는 :

1. 내가 날린 query 안의 term이 적은 문서에만 있는, 즉 매우 희소하고 자주 쓰이지 않는 단어여야 하며,

2. 문서에서 해당 term이 많이 나와야 하며,

3. 해당 문서의 길이는 짧아야 한다.

로 요약할 수 있겠다!

근데 저 공식은 매우 기본적인 형태이고, 실질적으로 검색 서비스에 사용되는 랭킹 알고리즘은 훠어어어어어얼씬 더 많은 피쳐들을 알고리즘에 적용한다.

출처: https://jitwo.tistory.com/8 [jitwo]