전문검색(Full Text Search) - 1. 전문검색 이해

이번에 소개해 드릴 내용은 전문검색 - Full Text Search 입니다.

SQL7 강좌의 전문 검색을 보신 분들과 다른 책이나 미디어를 통해 전문검색을 이미

접하신 분들도 계실 겁니다. 또한 SQLER 바로 이곳의 게시판에서 검색을

한번이라도 해 보신분은 이 전문검색을 이미 사용해 보신 것이기도 하지요. ^_^

개인적으로는 이렇게 전문 검색을 사용하고 있습니다.(제목검색, 내용검색)

대략적으로 전문 검색이 등장한 배경은 이렇습니다.

문자열 컬럼인 Varchar나 Char컬럼 또는 Text형 컬럼에서

SELECT 컬럼 FROM 테이블 WHERE TEXT컬럼 LIKE '%찾으려는문자열%'

이런 식으로 쿼리를 수행할 경우 전체 테이블을 Full Scan해서 하나하나 찾으려는 문자열을

비교해서 가져와야 하는 무척이나 부하가 많이 걸리고 속도도 느린 방식을

사용해야만 했지요. 정확히는 찾으려는 문자열의 앞쪽 % 때문에 이런 문제가

발생하게 된 것이기도 하구요.

　

간단히 구축이나 그 기반 기술을 보기 전에 전문검색과 %찾으려는 문자열% 검색을

비교해 얼마나 시간이 걸리는지 확인해 보도록 할까요?

　

SET STATISTICS IO ON SET STATISTICS TIME ON --백업 키워드로 내용 전문 검색 SELECT idx, title, writeday, content FROM tMSSQLQnA WHERE contains(content, '"백업"') SQL Server 구문 분석 및 컴파일 시간: CPU 시간 = 15ms, 경과 시간 = 36ms. 'tMSSQLQnA' 테이블. 스캔 수 934, 논리적 읽기 수 2967, 물리적 읽기 수 0, 미리 읽기 수 0. SQL Server 실행 시간: CPU 시간 = 78ms, 경과 시간 = 691ms. --백업 키워드로 내용 검색 SELECT idx, title, writeday, content FROM tMSSQLQnA WHERE content like '%백업%' SQL Server 구문 분석 및 컴파일 시간: CPU 시간 = 0ms, 경과 시간 = 3ms. 'tMSSQLQnA' 테이블. 스캔 수 1, 논리적 읽기 수 974, 물리적 읽기 수 133, 미리 읽기 수 501. SQL Server 실행 시간: CPU 시간 = 37141ms, 경과 시간 = 49853ms.

이렇게 시간 차이가 나게 됩니다. 현재는 저 혼자만의 테스트 시스템에서 사용하기

때문에 비교적 나은 시간을 보여주지만 만약 실제 운영될 프러덕션 시스템에서

운영되고 데이터가 많이 진다면 더 큰 시간차이가 벌어지겠지요.

　

전문검색에 대해서 조금은 흥미가 생기시는지요? 현재는 구별되는 가장 큰 특징만을

언급했으니 이제 상세히 전문검색에 대해서 공부해 보도록 하지요.

아직까지는 구현을 하는 것 보다는 이해를 먼저 하심이 좋습니다.

　

전문검색은 Windows 시스템의 Search 서비스를 사용한다.

정확히 전문검색은 SQL서버의 엔진이라기 보다는 NT서버의 엔진을 사용합니다.

SQL서버의 전문검색 서비스를 이용한 여러 T-SQL의 확장이나 구성등은 이 Windows NT에

포함된 전문검색 엔진을 확장한 것일 뿐이지요.

최초 전문 검색은 Windows NT4의 옵션팩에서 제공 되었습니다. 인덱스 서버라는 이름으로

제공 되었지요. 웹페이지들을 인덱싱 서버에 등록해 둔후 데이터를 조회하는 처리를

해본분들도 계실듯 하네요. SQL7에서 이 인덱싱 서비스가 이름이 Microsoft Search로

바뀌고 SQL2000에 전문검색 서비스가 포함됩니다.

　

화일시스템에 전문검색 인덱스 데이터 저장.

SQL서버의 인덱스는 SQL서버의 저장소에 페이지 형식으로 저장 됩니다.

전문검색은 그 태생부터가 틀리며 전문검색의 데이터인 카탈로그는 Windows시스템의

화일 시스템에 저장됩니다.

이 전문검색 서비스의 백업과 복구는 거북엄마님의 명쾌한 글이 있으니

참고해 보시길 바랍니다.

거북엄마님의 전문검색 백업과 복구 강좌

　

전문검색은 자동으로 인덱스를 구성하지 않는다.

SQL서버의 인덱스는 자동으로 DB를 구성하면 설정되는 옵션인

auto create statistics
auto update statistics

이 두 옵션으로 인해 인덱스가 자동으로 채워지고 데이터에 대한 변경이 있을 경우 자동으로

인덱스 데이터를 재구성 하지만 전문검색은 자동으로 인덱스를 재구성 하지 않으며

최초 인덱스를 채우기 위해서는 풀 파퓰레이션(Full Population)작업을 수행해야 하며

변경사항을 재 인덱싱을 하기 위해선 증분 파퓰레이션(Incremental Population)을 수행해야

합니다. - SQL2000부터 추적옵션으로 start_change_tracking 옵션으로 변경사항 추적 가능.

　

반드시 키값 컬럼이 있어야만 하며 Composite Index 사용 불가.

기본키나 Unique Index와 같은 컬럼이 하나 있어야만 하며 두개의 컬럼의 값을 하나의

인덱스 키로 잡는 Composite Index로는 사용이 불가 합니다.

　

증분 파퓰레이션(Incremental Population)을 수행하기 위해선 반드시 Timestamp형

컬럼이 필요.

풀 파퓰레이션(Full Population) 이후 증분 파퓰레이션(Incremental Population)을 수행하게

됩니다. 변경된 데이터에 대해서만 재 인덱싱을 구성하는 것이지요.

어떤 데이터가 변경 되었는지 어떻게 알 수 있을까요? Timestamp 컬럼은 일종의

SQL서버 내부 시계값입니다. 순차적인 시퀀스 정보를 가지고 있으며 순서 정보 역시

가지고 있지요. 어떤 로우의 데이터가 변경되면 자동으로 이 Timestamp 값이

변경되게 되며 사용자는 이 컬럼을 신경쓸 필요가 없습니다. 그냥 단순히 테이블에

Timestamp 컬럼만을 추가해 두시면 되지요. - SQL서버가 내부적으로 알아서 사용함.

　

파퓰레이션(Population)에 따른 부하가 있다.

대부분의 분들이 SQL7버젼부터 사용해 오셨을 겁니다. SQL6.5까지는 자동으로 인덱싱을

재구성을 해주지 않았습니다.

auto create statistics
auto update statistics

이런 옵션 자체가 없었지요.(sp_dboption 명령을 수행해 보시면 나옵니다.)

SQL서버의 인덱스 역시 새로운 데이터가 들어오거나 update가 일어나면 자동으로

인덱스를 재구성하며 인덱싱간 부하가 있습니다만.. 대부분의 분들은 인지하지 못하고

그냥 당연하다고 생각하고 계시지요. 전문검색 역시 마찬가지로 부하가 있습니다만..

SQL서버의 인덱싱보다 전문검색의 인덱싱이 그 부하가 훨씬 높습니다.

또한 인덱싱자체가 굉장히 많은 디스크의 IO를 사용하므로 체감 속도는 더 느리게 느껴지지요.

자동으로 추적 옵션을 주셔서 전문검색 인덱스를 재구성하는 것도 좋은 방법이고

스케쥴을 걸어서 전문검색 인덱스를 재구성 하는 것도 한 방법이며 전적으로 어떤 목적으로

사용될지에 따라 결정을 해야 겠지요.

　

하나의 테이블당 하나의 전문검색 인덱스 구성.

약간 특이적인 부분이지요? 화일 시스템상에 구현하기 때문에 그 구분을 이렇게

특이하지만 하나의 테이블로 둔듯 합니다. 구현부에서 한번더 소개 드리지요.

　

SQL 확장 구문을 이용한 검색(Contains(), FreeText() 와 같은 구문을 사용 가능.)

위의 샘플에서 Contains() 라는 구문을 이용하는것 보셨을듯. 바로 뒤의 구현 부분에서

여러 전문검색의 T-SQL의 확장 기능들을 소개해 드릴 것입니다.

　

MS Office 화일들(*.doc *.xsl *.ppt 등)을 image 컬럼에 저장후 인덱싱 가능

기본적으로 인식 필터가 포함되어 있으며 즉각적인 인덱싱 가능.

　

엔터프라이즈 관리자를 이용해 관리 / 설정 가능.

당연한가요.. EM을 이용해 관리 / 설정이 가능하며 역시나 코난이가 늘 그랬던 것처럼...

쿼리로 이 전문검색을 구현하는 부분에 대해서도 바로 다음 강좌인 구현에서

보여 드리도록 하지요.

　

이러한 여러 특징들을 가지고 있었으며 SQL2000으로 넘어오면서 다음과 같은 기능이

향상됩니다.

Failover Clustering 지원 Change Tracking 지원 Image type에 저장된 문서에 대한 Filtering가능 다국어 지원 Top-N-By-Rank English Query에 의한 조회 가능 성능 / 설치 향상

클러스터링 하에서 수행이 가능한 부분과 데이터의 변화를 자동 추적하는 기능이

추가 된것이 특이점이라고 보시면 되며 그 안정성과 한글처리 역시 SQL7 시절에 비해

큰 향상이 있었습니다.

　

간단하게 전문검색을 알아 보았습니다.

전문검색은 깊이 들어 갈수록 굉장히 어렵습니다.

SQLER에서는 역시나 구현부를 다루고.. 세번째 이야기인 전문검색의 문제점에서는

조금 깊은 이야기와 함께 문제점과 그 해결을 이야기해 드릴 예정입니다.

　

가장 큰 문제는 역시나 영어를 사용하는 사람들이 만들었다는 것이지요. - 삘이 오시나요?

　

자 수고 하셨습니다. 그럼 다음 이야기인 전문검색의 구현 이야기를 풀어 보도록 하지요.