전체 텍스트 검색 사용하기

일반적으로 데이터베이스에서 데이터를 조회할때는 테이블에 인덱스를 걸어서 속도를 향상시키는데 like '%외국%' 처럼 인덱스를 쓸 수 없는 상황에서 ms-sql 외부에서 도움을 받아 일반적으로 하기 어려운 검색도 지원하기 위해서임.

인덱스 사용이 어렵고 검색 패턴이 복잡할때는 보통 상용 검색 엔진을 이용하는데 전체 텍스트 검색도 일종의 미니 검색엔진이다.

그렇기 때문에 별도의 검색엔진이 mssql 과 별개의 프로세스로 존재
msftesql.exe, msftefd.exe

작동방식은 미리 테이블의 내용을 파일로 색인을 해놓고 해당 컬럼을 검색할때는 테이블을 스캔하는게 아니고 msftesql한테 검색조건을 보내고 테이블의 pk를 mssql한테 리턴하면 그 pk를 이용해 해당 row를 뽑아내는 식이다.

1. 전체 텍스트 카탈로그 만들기

CREATE FULLTEXT CATALOG fxLGTSALES_TB_ECOM_MAIL_INFO

2. 전체 텍스트 인덱스 만들기

CREATE FULLTEXT INDEX ON LGTSALES.TB_ECOM_MAIL_INFO (        MAIL_BODY              LANGUAGE [Korean] ) KEY INDEX TB_ECOM_MAIL_INFO_1_PK ON fxLGTSALES_TB_ECOM_MAIL_INFO WITH CHANGE_TRACKING AUTO GO N

3. 전체 텍스트 인덱스 카탈로그 채우기

ALTER FULLTEXT INDEX ON LGTSALES.TB_ECOM_MAIL_INFO        START FULL POPULATION

4. 성능

테이블 크기	3.9GB
레코드 수	2,114,673
전체 채우기 성능	40분
MAIL_BODY Type	text

5. 검색

SELECT MAIL_SEQ FROM ECOM_MAIL_INFO WHERE CONTAINS(MAIL_BODY, '외국인')

6. 사용할때와 말아야 할때
  a. 사용해야 할때
    - 전체 텍스트 검색이 필요한 쿼리가 자주 실행될 때
    - text, varchar(max) 처럼 인덱스가 전혀 통하지 않는 컬럼타입을 검색시
    - char, varchar 타입에 like검색시 앞%가 붙을때
       예) like mail_title '%외국인%'

  b. 사용하지 말아야 할때
    - 쿼리가 자주 사용되지 않을때
      하루에 몇번 실행 안되는데 오버헤드가 큰 전체 텍스트 검색 사용하지 말고
      그냥 테이블 풀스캔해버리셈

    - char, varchar에 like 검색시 뒷%만 붙을때는 그냥 인덱스로 해결
      예) like mail_title '외국인%'

    - text, varchar(max)에서 검색후 select절에 또 그 열을 지정할때
      예) mail_body가 text타입일때

      SELECT MAIL_SEQ, MAIL_BODY

       FROM ECOM_MAIL_INFO

       WHERE CONTAINS(MAIL_BODY, '외국인')