Microsoft SQL Server 2000 전체 텍스트 검색을 사용하여 웹을 검색하는 응용 프로그램 작성

Andrew B. Cencini
Microsoft Corporation

2002년 12월

적용 대상:
    Microsoft SQL Server 2000

요약: SQL Server 2000의 전체 텍스트 검색을 최대한 활용하는 방법에 대해 알아 봅니다. 이 기사에는 최대 처리량과 성능에 대한 여러 가지 팁이 제공됩니다(17 페이지/인쇄 페이지 기준).

목차

소개
전체 텍스트 검색에 대한 간단한 정보
전체 텍스트 검색 구성
전체 텍스트 쿼리
순위 지정 및 최적화
추가 성능 트릭
결론
부록 A: 전체 텍스트 검색 최적 구현
부록 B: 최적 구현, 결과 페이징 및 효율적인 전체 텍스트 쿼리 논리를 사용하는 샘플 응용 프로그램
부록 C: 참고 자료

소개

Microsoft SQL Server 2000의 전체 텍스트 검색 기능을 사용하면 비구조적 텍스트 데이터에 대해 작성된 인덱스를 빠르고 융통성 있게 쿼리할 수 있습니다. 전체 텍스트 검색은 일반적으로 웹 사이트의 검색 엔진을 통해 수행됩니다. 최대 처리량과 성능에 대한 쿼리와 전체 텍스트 인덱스를 최적화하는 여러 가지 팁을 비롯하여, 전체 텍스트 검색을 최대한 활용하는 방법을 이해하는 데 도움이 되는 일반적인 개념과 추상적인 개념이 많이 있습니다.

전체 텍스트 검색에 대한 간단한 정보

전체 텍스트 검색 기능은 SQL Server 7.0에서 소개되었습니다. 전체 텍스트 검색의 핵심 엔진은 MSSearch(Microsoft Search) 기술에 의해 제공되며, Microsoft Exchange, Microsoft SharePoint Portal Server 등과 같은 제품에서도 사용됩니다.

SQL Server 7.0 전체 텍스트 검색에 공개된 기능은 기본 텍스트 검색 기능을 제공하며 이전 버전의 MSSearch를 사용합니다. SQL Server 2000의 전체 텍스트 검색 구현에서는 SQL Server 7.0에 포함된 기능 외에도 강력한 인덱스 및 쿼리 집합과 여러 가지 향상된 기능을 제공합니다. 이러한 향상된 기능으로는 Microsoft 클러스터링 서비스를 통한 완벽한 클러스터링 지원, IMAGE 열에 저장된 문서 필터링 및 인덱싱 기능, 향상된 언어 지원, 성능 향상, 확장성 향상, 안정성 향상 등이 있습니다.

SQL Server에서와는 반대로 MSSearch는 파일 시스템에 저장된 전체 텍스트 인덱스를 빌드, 유지 관리 및 쿼리합니다. MSSearch에서 전체 텍스트 인덱스에 사용하는 논리적 및 물리적 저장 장치는 카탈로그입니다. 전체 텍스트 카탈로그에는 데이터베이스마다 하나 이상의 전체 텍스트 인덱스가 포함됩니다. 즉, SQL Server에서 테이블마다 전체 텍스트 인덱스 하나를 만들 수 있으며, 인덱스에 해당 테이블의 열을 하나 이상 포함시킬 수 있습니다. 각 테이블은 하나의 카탈로그에만 속할 수 있으며, 테이블마다 인덱스를 하나씩만 만들 수 있습니다. 전체 텍스트 카탈로그 및 인덱스 구성의 측면에서 최적의 방안을 살펴보겠지만, 먼저 전체 텍스트 검색 방법에 대해 좀 더 자세히 알아보겠습니다.

전체 텍스트 검색 구성

SQL Server에 저장된 텍스트 데이터에 대한 전체 텍스트 인덱스를 만들려면 몇 가지 준비 단계를 수행해야 합니다. 첫째 단계에서는, 아직 지정하지 않은 경우 인덱싱할 텍스트 데이터가 포함된 데이터베이스에서 전체 텍스트를 사용할 수 있도록 지정합니다.

주의   다음 문을 실행하면 전체 텍스트 검색을 활성화할 데이터베이스에 속하는 전체 텍스트 카탈로그가 모두 삭제된 다음 다시 만들어집니다. 따라서, 전체 텍스트 카탈로그를 다시 작성하려는 경우가 아니라면 활성화하려는 특정 데이터베이스에 전체 텍스트 카탈로그가 이미 만들어져 있지 않은지 확인하십시오.

해당 데이터베이스에 대한 sysadmin 역할 또는 db_owner의 구성원인 사용자는 계속해서 다음 문을 실행할 수 있습니다.

use Northwind exec sp_fulltext_database 'enable'

그런 다음 전체 텍스트 카탈로그를 만들어 전체 텍스트 인덱스를 저장할 수 있습니다. 앞에서 설명한 것처럼 이 카탈로그의 데이터는 SQL Server에서와 달리 파일 시스템에 존재하기 때문에 전체 텍스트 카탈로그를 저장할 위치를 고려할 경우 주의해서 선택해야 합니다. 저장 위치를 지정하지 않으면 전체 텍스트 카탈로그가 Microsoft SQL Server\MSSQL 저장소 위치에 있는 FTDATA 디렉터리의 하위 디렉터리에 저장됩니다. 기본 위치가 아닌 위치에 전체 텍스트 카탈로그를 만드는 방법은 다음과 같습니다.

exec sp_fulltext_catalog 'Cat_Desc', 'create', 'f:\ft'

이 경우 전체 텍스트 카탈로그가 'f:\ft'의 하위 디렉터리로 만들어지고, 파일 시스템의 해당 부분이 자체 디렉터리로 표시됩니다. MSSearch에서 전체 텍스트 카탈로그에 사용되는 명명 규칙은 다음과 같습니다.

SQL+dbid+catalogID

카탈로그 ID는 00005에서 시작되며 새 카탈로그가 만들어질 때마다 1씩 증가합니다.

가능하면 전체 텍스트 카탈로그를 자체 물리 드라이브에 만드는 것이 가장 좋습니다. 전체 텍스트 인덱스 작성 프로세스가 I/O 집약적(높은 수준에서 SQL Server로부터 데이터를 읽은 다음 파일 시스템에 인덱스 작성)일 경우 I/O 하위 시스템에서 병목 현상이 발생하지 않도록 하고 싶을 것입니다.

그렇다면, 전체 텍스트 카탈로그의 크기가 어떻게 됩니까? 일반적으로 전체 텍스트 카탈로그는 전체 텍스트 인덱싱을 수행할 SQL Server에 저장된 데이터 양에 대해 약 30%의 오버헤드를 추가합니다. 그러나, 이 규칙은 데이터에 있는 단어 또는 키를 얼마나 정확하게 입력하는가에 따라 달라집니다. 예를 들어, noise word, stop word 등은 자주 발생하는 '대상' 검색 용어가 아니고 인덱스의 용량만 커지게 하기 때문에 전체 텍스트 인덱스 및 쿼리에서 제외됩니다. 이러한 용어를 입력할 경우 추가되는 오버헤드가 커집니다. noise word 선택에 대해서는 나중에 알아 보겠습니다. noise word를 조정하면 쿼리 성능을 향상시킬 수 있습니다.

아직 인덱스를 만들지 않은 경우 전체 텍스트 인덱스를 작성하려는 각 테이블에 단일 열로 구성되고 null을 허용하지 않는 고유 인덱스를 만듭니다. 이 고유 인덱스를 사용하여 테이블의 각 행을 MSSearch에서 내부적으로 사용하는 압축 가능한 고유 키에 매핑합니다. 그런 다음, MSSearch를 통해 테이블에 전체 텍스트 인덱스를 만들려고 합니다. 테이블에 대해 다음 문을 실행하면 선택한 전체 텍스트 카탈로그(여기서는 위에서 만든 'Cat_Desc')에 인덱스가 추가됩니다.

exec sp_fulltext_table 'Categories', 'create', 'Cat_Desc', 'PK_Categories'

다음 단계에서는 해당 전체 텍스트 인덱스에 열을 추가합니다. 각 열에 대해 언어를 선택할 수 있습니다. 열의 유형이 IMAGE일 경우 IMAGE 열의 각 행에 저장되는 문서 형식을 나타내는 데 사용할 다른 열을 지정해야 합니다.

열 언어 선택에서 중요하지만 체계화되지 않은 몇 가지 고려 사항이 있습니다. 이러한 고려 사항은 텍스트를 토큰화한 다음 MSSearch에서 인덱싱하는 방법과 관련이 있습니다. 인덱싱할 텍스트는 단어 범위에서 토큰화하는 단어 분리기라는 구성 요소를 통해 제공됩니다. 영어에서 이 단어 범위는 일반적으로 공백이거나 구두점 형태로 표시됩니다. 독일어와 같은 다른 언어에서는 단어 또는 문자가 함께 결합될 수 있으므로, 열 수준 언어를 선택하면 해당 언어가 해당 열에 여러 행으로 저장됩니다. 확신할 수 없는 경우 공백 및 구두점에서 순수하게 토큰화를 수행하는 중립 단어 분리기를 사용하는 것이 좋습니다. 열 수준 언어를 선택하면 "스테밍" 작업도 더불어 할 수 있습니다. 전체 텍스트 쿼리의 스테밍은 특정 언어에서 모든 어미 형식 단어를 검색하는 프로세스입니다.

단어 선택 시의 다른 고려 사항은 데이터를 표시하는 방식과 관련이 있습니다. IMAGE 이외의 열 데이터에 대해서는 특수 필터링이 수행되지 않고, 일반적으로 텍스트가 단어 분리 구성 요소를 통해 있는 그대로 전달됩니다. 단어 분리기는 기록된 텍스트를 처리하도록 디자인되었기 때문에, 텍스트에 태그를 넣으면 인덱싱 및 검색을 수행하는 동안 해당 언어가 정확하게 표시되지 않을 수 있습니다. 그럴 경우 두 가지 방법을 선택할 수 있습니다. 기본 방법은 텍스트 데이터를 IMAGE 열에 저장하여 필터링될 수 있도록 문서 형식을 나타내는 것입니다. 이 방법을 선택할 수 없을 경우 중립 단어 분리기를 사용하여 noise word 목록에 태그 데이터(예: HTML의 'br')를 추가할 수 있습니다. 중립 언어를 지정하여 열에서 언어 기반 스테밍 작업을 할 수는 없지만, 특정 환경에서는 이 방법을 선택할 수 있습니다.

지금까지 열 수준 옵션에 대해 알아 보았으며, 이제는 다음을 실행하여 전체 텍스트 인덱스에 하나 또는 두 개의 열을 추가해 보겠습니다.

exec sp_fulltext_column 'Categories', 'Description', 'add'

여기서 우리는 아직 언어를 지정하지 않았음을 알 수 있으며 이런 경우 기본 전체 텍스트 언어가 사용됩니다. 시스템 저장 프로시저 "sp_configure"를 통해 서버에 대해 기본 전체 텍스트 언어를 설정할 수 있습니다.

전체 텍스트 인덱스에 추가된 모든 열을 사용하여 입력할 준비가 되었습니다. 다양한 옵션에 대해서는 입력 방법에서 자세하게 설명되기 때문에 여기서는 자세하게 설명하지 않겠습니다. 이 예제에서는 테이블을 완전하게 입력한 다음 완료되는 동안 기다리기만 하면 됩니다.

exec sp_fulltext_table 'Categories', 'start_full'

FULLTEXTCATALOGPROPERTY 또는 OBJECTPROPERTY 함수를 사용하여 입력 상태를 모니터할 수 있습니다. 카탈로그 입력 상태를 보려면 다음을 실행할 수 있습니다.

select FULLTEXTCATALOGPROPERTY('Cat_Desc', 'Populatestatus')

일반적으로 전체 입력이 진행 중이면 결과 값 '1'이 반환됩니다. FULLTEXTCATALOGPROPERTY 및 OBJECTPROPERTY를 사용하는 방법에 대한 자세한 내용은 SQL Server 온라인 설명서를 참조하십시오.

전체 텍스트 쿼리

전체 텍스트 인덱스 쿼리는 SQL Server에서의 표준 관계형 쿼리와 약간 다르게 실행됩니다. 인덱스는 SQL Server의 외부에 저장되어 관리되므로 전체 텍스트 쿼리 프로세스는 대부분 MSSearch에 의해 처리됩니다. 부분적으로 관계형인 쿼리와 부분적으로 전체 텍스트 기반인 쿼리가 별도로 처리되기 때문에 성능이 떨어지는 경우가 있습니다.

전체 텍스트 쿼리를 실행하면 쿼리 용어가 MSSearch에 전달됩니다. 이때 내부 데이터 구조(인덱스)를 이동하여 SQL Server에 키 및 순위 값을 반환합니다. 일반적으로 키 또는 순위 값은 CONTAINS 또는 FREETEXT 쿼리를 실행할 때는 표시되지 않지만, CONTAINSTABLE 또는 FREETEXTTABLE 쿼리를 실행하면 해당 값이 함께 제공되어 기본 테이블에 연결됩니다. 기본 테이블에 키를 연결하는 프로세스는 상당히 비용이 많이 듭니다. 여기서는 이러한 연결을 최소화하거나 연결하지 못하게 하는 몇 가지 방법에 대해 살펴보겠습니다.

좀 더 포괄적으로 생각하고 전체 텍스트 쿼리에서 데이터를 반환하는 방법을 조금만이라도 알고 있었다면, CONTAINS/FREETEXT 쿼리는 단순히 CONTAINSTABLE/FREETEXTTABLE 쿼리를 수행하여 기본 테이블에 연결한다는 사실을 눈치챘을 것입니다. 따라서 다른 유형의 쿼리를 실행하는 것이 더 저렴하다면 이러한 유형의 쿼리를 실행하지 말아야 합니다. 웹 검색 응용 프로그램의 경우에는 CONTAINSTABLE 및 FREETEXTTABLE을 사용하는 것이 TABLE을 사용하는 것보다 더 좋습니다.

지금까지, 전체 텍스트 쿼리가 SQL Server 외부에 저장된 MSSearch 인덱스에서 데이터를 액세스하는 특수한 방법이고, 기본 테이블에 무조건 연결할 경우 약간의 문제가 발생할 수 있음을 알았습니다. CONTAINS 스타일 쿼리와 FREETEXT 스타일 쿼리 간의 실제적인 차이점에 대해서도 알고 있어야 합니다.

CONTAINS 쿼리에서는 모든 단어가 정확히 일치하는 용어를 검색합니다. 단어 하나를 찾던지 'orange'로 시작하는 단어는 모두 찾던지 상관없이 모든 검색 조건이 포함된 결과만 반환합니다. CONTAINS 쿼리는 일반적으로 매우 빠르게 수행되고 적은 수의 결과를 반환하며 추가 작업을 많이 수행하지 않아도 됩니다. CONTAINS 쿼리의 단점으로는 noise word 필터링 문제가 있습니다. 이전에 전체 텍스트 검색 작업 경력이 있는 개발자와 DBA라면 noise word가 한 단어라도 포함된 단어 또는 구문을 검색하려고 할 때 "Your query contains only noise words"라는 오류 메시지를 본 적이 있을 것입니다. 이 오류를 방지하려면 전체 텍스트 쿼리를 실행하기 전에 noise word를 필터링하십시오. CONTAINS 쿼리에서는 전체 쿼리 문자열과 정확히 일치하는 내용을 반환하기 때문에 noise word가 포함된 CONTAINS 쿼리에는 결과가 반환되지 않습니다. noise word는 전체 텍스트가 인덱싱되지 않기 때문에 noise word가 포함된 CONTAINS 쿼리에는 아무 행도 반환되지 않습니다.

FREETEXT 쿼리를 사용하면 CONTAINS 쿼리에서 발생할 수 있는 모든 경고 문제가 해결됩니다. FREETEXT 쿼리를 실행하면 스테밍된 모든 단어에 대한 쿼리가 함께 실행됩니다. 따라서, "root beer"를 검색하면 'root'와 'beer'가 모든 형태로 스테밍(스테밍은 언어별로 고유하고, 사용되는 언어는 인덱싱 시 지정한 전체 텍스트 열 언어에 의해 결정되고, 쿼리된 모든 열에서 동일해야 함)되고, 해당 단어 중 하나라도 일치하는 내용이 있는 행이 모두 반환됩니다.

FREETEXT 쿼리는 CONTAINS 쿼리보다 CPU를 더 많이 사용하는 단점이 있는데, 이는 매우 복잡한 순위 계산이 수반되는 스테밍과 반환되는 결과가 더 많아지기 때문입니다. 그렇다고 해도 FREETEXT 기반 쿼리는 매우 융통적이고 빠르며 웹 기반 검색 응용 프로그램에 가장 알맞은 방법입니다.

순위 지정 및 최적화

전체 텍스트 검색 기능을 사용하는 사용자들을 만나보면, 그들은 순위 지정 번호의 의미가 무엇인지, 또 이 번호를 우리가 알아 볼 수 있는 값으로 변환하는 방법이 무엇인지에 대해 자주 물어 봅니다. 이 질문에 대해 짧게 대답할 수도 있고 길게 대답할 수도 있지만 간략하게 설명하기 위해 짧은 답변을 살펴보겠습니다. 기본적으로, 순위 번호는 결과가 반환되는 순서 이상의 의미를 갖지 않습니다. 이것은 결과의 순위를 지정할 때 연관성이 가장 큰 결과가 항상 제일 먼저 반환된다는 것을 의미합니다. 순위 값은 쉽게 변경됩니다. 전체 텍스트 검색에서는 가능성에 근거한 순위 알고리즘을 사용하므로, 반환할 각 문서의 관계가 전체 텍스트 인덱스의 다른 문서에 직접적인 영향을 미칩니다.

한 가지 트릭으로 특정 행에서 전체 텍스트로 인덱싱된 열에 공통으로 사용되는 검색 키워드를 반복하여 해당 행의 순위를 높일 수 있다고 믿는 사람들이 있습니다. 이렇게 하면 특정 키워드에 대해 제일 먼저 반환되는 행을 변경하는 데 어느 정도는 도움이 될 수도 있지만, 쿼리 성능을 떨어뜨려 역효과가 날 수도 있습니다. 이 문제를 해결하려면 특정 문서가 가장 먼저 반환되도록 검색 응용 프로그램에 "가장 적합한" 시스템을 구현하는 것이 좋습니다(위 예제 참조). 키워드의 포괄적인 중복 문제는 해당 키워드에 대한 전체 텍스트 인덱스를 확장시켜 MSSearch에서 올바른 행을 찾아서 순위 계산을 하는 데 필요 이상으로 많은 시간이 걸릴 수 있습니다. 많은 양의 전체 텍스트 인덱싱 데이터가 있을 경우 이 방법을 시도하면 전체 텍스트 쿼리를 수행하는 데 많은 시간이 걸릴 수 있습니다. 보다 안정적이고 정확하고 "가장 적합한" 시스템을 구현할 경우 쿼리 성능에서 많은 차이가 나타날 것이라고 기대합니다.

포괄적인 데이터 중복과 관련한 다른 문제는 관계형 및 전체 텍스트 쿼리를 결합하는 데 공통으로 사용되는 트릭과 관련이 있습니다. 이 문제는 전체 텍스트 검색을 사용하는 많은 사람들에게는 비효율적이며, 일부 필터를 전체 텍스트 쿼리에서 반환된 결과에 적용하려고 할 때 발생합니다. 앞에서 설명한 것처럼 전체 텍스트 쿼리는 일치하는 각 행에 대한 키와 순위를 반환합니다. 해당 행에 대한 자세한 정보를 수집하려면 기본 테이블로에 대한 연결을 수행해야 합니다. 제한되지 않은 전체 텍스트 쿼리에서는 결과 수를 무제한으로 반환할 수 있으므로 연결에 비용이 많이 듭니다. 그런 연결을 방지하려면 전체 텍스트 인덱스에 데이터를 추가하여 필터링(가능한 경우)하는 것이 좋습니다. 다시 말해서, 신문에 있는 모든 기사의 본문에서 "Ichiro"라는 키워드를 검색하고자 하지만 신문의 스포츠 섹션에 있는 기사만 반환될 경우, 쿼리는 다음과 같이 설명됩니다.

-- [방법 1:] -- 비용이 가장 많이 드는 경우: 모두 선택한 다음 연결하고 필터링합니다. SELECT ARTICLES_TBL.Author, ARTICLES_TBL.Body, ARTICLES_TBL.Dateline, FT_TBL.[rank] FROM FREETEXTTABLE(Articles, Body, 'Ichiro') AS FT_TBL INNER JOIN Articles AS ARTICLES_TBL ON FT_TBL.[key] = ARTICLES_TBL.ArticleID WHERE ARTICLES_TBL.Category = 'Sports' -- [방법 2:] -- 작동하지만 역효과가 나서 잘못된 결과를 반환하거나 속도가 느려지는 경우: -- 전체 텍스트를 필터링하거나 키와 순위만을 추출합니다. -- (웹 서버 수준에서 작업이 완료됨) SELECT [key], [rank] FROM CONTAINSTABLE(Articles, *, 'FORMSOF(INFLECTIONAL('Ichiro') AND "sports"')

이 쿼리에는 불필요하게 많은 비용이 들거나 잘못된 결과가 반환될 수 있다는 문제가 있습니다. 두 번째 쿼리에서는 'sports'가 모든 범주의 기사에 나타날 수 있습니다. 이 기술을 다른 기술로 대체할 수 있지만, 그런 기술로는 매우 간단한 두 가지 실험용 기술이 전부입니다. 여유가 된다면 대개 데이터의 파티션을 수평으로 지정하는 것이 좋습니다. 다시 말해서, '범주' 열의 가능한 값이 자체 열 또는 테이블로 지정되었거나 해당 기사와 관련하여 검색할 수 있는 키워드가 해당 열에만 저장되었을 수 있습니다. 단일의 'Body' 열 및 'Category' 열을 사용하는 대신 이 방법을 사용하면 'Category' 열을 제거하고 'Body_<category>' 열에 검색 가능한 키워드를 저장할 수 있습니다. 예를 들면 다음과 같습니다.

-- 사용자 스키마가 조정 가능한 경우 가장 잘 작동함 – -- 각 범주가 자체 열이나 테이블이 되고 -- 해당 전체 텍스트 인덱스를 더 적게 실행합니다. 반드시 경고가 발생습니다. SELECT [key], [rank] FROM FREETEXTTABLE(Articles, Body_Sports, 'Ichiro')

이 주요 스키마 변경을 수용할 수 있는 데이터가 많은 시스템의 경우 매우 많은 성능 향상이 즉각적으로 이루어집니다. 필터를 적용하지 않거나 여러 필터를 적용할 때 분명한 제한이 있으며, 이 문제를 해결할 수 있는 다른 방법도 분명히 존재합니다. 위 예제에서 스키마에 검색 조건의 일부를 요약하는 방법을 다음과 같이 설명할 수 있습니다. SQL Server 자체 내에서 전체 텍스트 쿼리를 최적화할 수 없으므로 최적화 프로그램을 '모방'(보다 적절하게는 최적화 프로그램 '되기')해야 합니다.

추가 성능 트릭

저와 대화해 본 사람들의 경우 공통적으로 전체 텍스트 쿼리 결과에 대한 페이지 지정 기능을 요청했습니다. 다시 말해서, "root beer"에 대한 쿼리를 실행할 경우 한 번에 40페이지의 웹 페이지에 결과를 표시하여 각 페이지마다 40개의 결과만 반환되게 하고자 합니다. 예를 들어, 3페이지에는 81번에서 120번까지의 결과만 표시되게 합니다.

결과에 대한 페이지를 지정하는 몇 가지 방법에 대해 설명한 적이 있지만 어떠한 방법도 100% 효과적이지는 않습니다. 실행하는 전체 텍스트 쿼리 수를 최소화(실제로, 결과 집합마다 하나의 페이지를 지정)하고 웹 서버를 단순 캐시로 사용하는 방법을 제안합니다. 고급 방법으로는 전체 텍스트 쿼리에 대한 전체 키 및 순위 값 행 집합을 검색하여(원하는 경우 최적 시스템을 실행하여 스키마에 공통 필터를 요약) 웹 서버의 메모리에 결과를 저장합니다. 응용 프로그램 및 부하에 따라, <32바이트의 일반 키 크기+<4바이트의 순위 크기 = <36바이트*일반적으로 반환되는 결과 세트 <1000행=<35K이고, <1000 활성 쿼리 결과 집합이 지정된 시간에 캐싱된다고 가정합니다. 이렇게 하면 웹 서버에서 35MB 미만의 RAM이 사용되어 메모리가 지저분해지지 않습니다.

결과 내용의 페이지를 지정하려면 웹 서버에서 메모리에 저장되는 배열을 변환하고 SQL Server에서 표시할 행과 열에 대해서만 SELECT를 실행합니다. 이것은 전체 텍스트 쿼리에 대해 반환되는 유일한 키 및 순위라는 개념에도 적용됩니다. SELECT는 전체 텍스트 쿼리보다 속도가 몇 배 이상 빠릅니다. 기본 테이블에 많은 행을 연결하는 대신 여러 가지의 서로 다른 전략과 함께 SELECT를 사용하면 SQL Server 시스템에서 보다 많은 CPU 주기를 예약하여 보다 적은 비용으로 보다 많은 웹 그룹을 사용할 수 있습니다.

웹 서버쪽 캐싱의 대체 방법으로 SQL Server 자체에서 결과 집합을 캐싱하고 해당 결과를 통해 탐색할 수 있는 여러 가지 방법을 정의할 수 있습니다. 이 기사에서는 웹 서버(ASP) 수준의 응용 프로그램 디자인을 주로 다루지만 SQL Server의 프로그램 가능성 기능은 웹에 대한 고성능 검색 응용 프로그램을 작성하는 풍부한 프레임워크를 제공합니다.

결론

Microsoft SQL Server 2000의 전체 텍스트 검색 기능은 데이터베이스에 저장된 비구조적 텍스트 데이터를 쿼리 및 인덱싱하는 강력하고, 빠르고, 융통성 있는 방법을 제공합니다. 다양한 응용 프로그램에서 빠르고 정확한 검색 기능에 대한 점증하는 사용 인구와 중요성을 고려할 때 그 속도와 정밀도를 활용하는 방식으로 전체 텍스트 검색 솔루션을 구현해야 합니다. 컴퓨터 부하를 분산시키고 데이터를 여러 가지 방식으로 구성하면 하드웨어와 소프트웨어를 추가하는 비용이 절감되며, 불필요하게 쿼리가 느려지는 문제를 방지할 수 있습니다. 대용량 검색 응용 프로그램 개발에 적용되는 많은 요소와 고려 사항이 있지만, 이 기사에서 제공하는 정보와 예제가 SQL Server 2000을 사용하여 최상의 웹 검색 응용 프로그램 작성을 시작하는 데 도움이 되기를 바랍니다.

부록 A: 전체 텍스트 검색 최적 구현

전체 텍스트 쿼리의 성능과 효율을 향상시키려면 "가장 적합한" 시스템을 구현해야 합니다. 이 시스템은 특정 쿼리 문과 일치하는 행이 다른 행보다 먼저 반환되게 하는 매우 간단한 방법입니다. 최적 시스템은 사전에 프로그래밍된 포괄적인 논리(예: SharePoint Portal Server에서 발견되는 논리)가 없을 경우에 일반적으로 선택됩니다.

이 예제의 목적은 최적 시스템을 선택하여 고유 키와 많은 키워드를 개별 테이블에 저장하는 것입니다. FREETEXTTABLE 쿼리는 매우 작은 Best Bets 테이블에 대해 실행되며, 해당 쿼리에서 반환되는 결과에는 기본 테이블에 대한 FREETEXTTABLE 쿼리 결과가 함께 반환됩니다. 이 방법에서는 "가장 적합한" 모든 행이 먼저 반환되고, 그 다음에 MSSearch에서 관련이 있다고 간주되는 행들이 내림차순으로 반환됩니다.

다음은 최적 시스템을 만드는 매우 간단한 샘플 스크립트입니다.

use myDb create table documentTable(ftkey int not null, document ntext) create unique index DTftkey_idx on documentTable(ftKey) /* 여기에 문서를 삽입합니다. (전체 텍스트를 인덱싱할 모든 문서) */ -- 모든 문서 테이블에 대한 전체 텍스트 카탈로그와 인덱스를 만듭니다. exec sp_fulltext_catalog 'documents_cat', 'create', 'f:\ftCats' exec sp_fulltext_table 'documentTable', 'create', 'documents_cat', 'DTftkey_idx' exec sp_fulltext_column 'documentTable', 'document', 'add' exec sp_fulltext_table 'documentTable', 'start_change_tracking' exec sp_fulltext_table 'documentTable', 'start_background_updateindex' /* 최적의 시스템 테이블과 인덱스를 만듭니다. (항상 제일 먼저 반환되는 문서를 추가합니다.) */ create table bestBets(ftKey int not null, keywords ntext) create unique index BBftkey_idx on bestBets(ftKey) /* 여기에 최적의 시스템을 삽입합니다. */ -- 최적의 시스템 테이블에 대한 전체 텍스트 카탈로그와 인텍스를 만듭니다. exec sp_fulltext_catalog 'bestBets_cat', 'create', 'f:\ftCats' exec sp_fulltext_table 'bestBets', 'create', 'bestBets_cat', 'BBftkey_idx' exec sp_fulltext_column 'bestBets', 'keywords', 'add' exec sp_fulltext_table 'bestBets', 'start_change_tracking' exec sp_fulltext_table 'bestBets', 'start_background_updateindex'

먼저, 일반적인 '모든 문서' 테이블을 만들어 전체 텍스트를 인덱싱할 모든 문서를 저장합니다. 일반적으로, 문서 테이블에는 서로 다른 여러 열이 있지만, 이 기사에서는 기사 목적 상 문서 자체와 키 인덱스의 두 열만 있는 것으로 가정합니다. 문서 테이블에 대한 전체 텍스트 카탈로그와 인덱스를 만듭니다.

그런 다음, '최적 시스템' 테이블을 만들어 모든 전체 텍스트 쿼리에서 가장 먼저 반환될 특별 문서를 저장합니다. 이 테이블에는 전체 텍스트 키 열과 문서 자체 열만 있어야 합니다. 이 전략을 계획적으로 활용하면 특정 쿼리에 대해 특정 문서를 대상으로 지정하는 데 도움이 되며, 문서 자체에 포함되어 있지 않은 문서에 대한 키워드를 추가할 수 있습니다. 최적 시스템 테이블에 대한 전체 텍스트 카탈로그와 인덱스를 만듭니다.

최적 시스템 테이블과 문서 테이블은 문서를 공유(가장 적합한 문서는 일반 문서 테이블에도 저장되고, 동일한 키 값을 공유함)하거나 단독으로 사용할 수 있습니다(최적 시스템 문서는 최적 시스템 테이블에만 저장됨). 간편하게 검색할 수 있도록 최적 시스템 테이블을 문서 테이블과 독립적으로 유지하는 것이 더 쉬울 수 있습니다. 그렇게 하면 반환되는 최적 시스템 및 일반 검색 결과 행 집합에서 공유 문서를 제거할 필요가 없습니다. 다시 말해서, 이 방법으로 문서를 유지 관리할 수 없는 경우 쿼리에 논리를 추가하여 반환된 행 집합 간의 공유 문서를 제거해야 합니다.

위 테이블에서 두 저장 프로시저를 만들어 최적 시스템 테이블과 문서 테이블을 검색할 수 있습니다. 웹 서버 수준의 논리 또는 추가 저장 프로시저를 사용하여 원하는 결과를 캐싱하여 표시할 수 있습니다. 최적 시스템을 사용할 경우 캐싱, 표시 및 페이지 지정에 모두 유용한 예제는 다음 절을 참조하십시오.

첫째, 가장 적합한 행을 검색하는 저장 프로시저(있는 경우)

create procedure BBSearch @searchTerm varchar(1024) as select [key], [rank] from freetexttable(bestBets, keywords, @searchTerm) order by [rank] desc

들어오는 검색 문자열을 삭제하여 서버에서 T-SQL의 중복 실행을 방지하고 문자열을 작은 따옴표(' ') 안에 입력해야 합니다. FREETEXTTABLE은 어미 변화를 활용하여 모든 검색 조건에 일치하는 가장 적합한 항목을 찾기 때문에, 이 경우 FREETEXTTABLE을 사용하는 것이 CONTAINSTABLE을 사용하는 것보다 좋습니다.

둘째, 일반 검색 기준과 일치하는 문서를 검색하는 저장 프로시저(있는 경우)

create procedure FTSearch @searchTerm varchar(1024) as select [key], [rank] from freetexttable(documentTable, keywords, @searchTerm) order by [rank] desc

그런 다음, 들어오는 검색 문자열이 삭제되고 문자열이 작은 따옴표(' ') 안에 입력되는지 확인합니다.

이 저장 프로시저를 실행하면 두 저장 프로시저에 동일한 검색 조건이 전달되고, 가장 적합한 검색이 먼저 실행된 다음 일반 전체 텍스트 검색이 수행됩니다. 다음 절에서는 웹 검색 응용 프로그램을 작성할 때 최적 시스템을 다른 전체 텍스트 검색 기술과 함께 사용하는 방법에 대한 포괄적인 개요를 제공합니다.

부록 B: 최적 구현, 결과 페이징 및 효율적인 전체 텍스트 쿼리 논리를 사용하는 샘플 응용 프로그램

이 예제에서는 이 기사에서 설명한 거의 모든 최적화 기능을 이용하는 웹 검색 응용 프로그램을 구현합니다. 온라인 소매업체 카탈로그 검색 엔진에 대한 간단한 시나리오를 사용하며, 고객이 매우 짧은 응답 시간에 그들이 기대하는 모든 결과를 볼 수 있는 높은 볼륨의 트래픽을 가정합니다. 이 예제에서는 이전 절의 최적 시스템 테이블과 저장 프로시저를 사용합니다.

이 응용 프로그램은 가능한 최대 전체 텍스트 검색 성능을 얻기 위해 사용할 수 있는 여러 가지 고급 전략에 대한 간단한 예제에 불과합니다. 이 예제에서는 ASP를 사용하지만 ISAPI, ASP.NET 등과 같은 플랫폼을 사용하여 각각의 장단점을 갖는 유사한 솔루션을 구현할 수도 있습니다. 모든 응용 프로그램에서 세션 개체 사용이 권장되지는 않습니다. 세션 개체를 잘못 사용하면 위험한 상황이 발생할 수 있습니다. 여기서는 세션 개체를 사용하여 빠르고 난해한 캐싱 메커니즘을 구현합니다. 해당 기능을 다양한 수준으로 구현할 수 있는 많은 방법이 있습니다.

다음은 ASP 페이지에 대한 일반 코드입니다.

<% @Language = "VBScript" %> <% Response.buffer = true %> <html> <head> <title>FT 테스트</title></head> <body> <pre> ----------------- Begin Test ------------------ <% Dim firstRow ' 행을 따라 페이지를 지정하는 경우 시작 요소 Dim lastRow ' 페이지를 지정하는 경우 마지막 행 Dim pageSize ' 페이지 크기(한 번에 포함할 행 수) Dim cn ' 연결 개체 Dim rs ' 다시 사용된 FT 키/순위에 대한 결과 Dim useCache ' 캐시를 사용하거나 FT 실행(0일 경우 사용 안 함, 1일 경우 사용함) Dim alldata ' 캐시할 결과 행 집합 Dim bbdata ' 캐시할 최적의 시스템 행 집합 Dim connectionString ' sql 연결 문자열 ' 캐시로부터 가져올지 여부를 결정합니다. ' 기본적으로 false이며 그렇지 않으면 들어오는 결과를 사용합니다. if (request.Form("useCache") <> "") then useCache = request.Form("useCache") elseif (request.QueryString("useCache") <> "") then useCache = request.QueryString("useCache") else useCache = 0 end if ' 상수 설정 pageSize = 24 firstRow = 0 lastRow = 23 connectionString = <연결 문자열을 여기에 입력합니다.> '----------------------------------------------------------------' ' 최적/검색 용어와 일치하는 간단한 키/순위 쌍을 표시합니다. ' '----------------------------------------------------------------' Private Sub SearchNPage() Dim p ' 열을 따라 루프를 수행하는 카운터 Dim numRows ' 캐시/결과 집합의 전체 열 수 if (useCache <> "1") then ' 최적/결과를 가져와서 캐시합니다. Dim queryArg ' 들어오는 쿼리 용어 if (request.Form("searchTerm") <> "") then queryArg = request.Form("searchTerm") elseif (request.QueryString("searchTerm") <> "") then queryArg = request.QueryString("searchTerm") else response.Write("입력된 검색어가 없음" & VbCrLF) exit sub end if ' 이상적으로는 쿼리 용어를 여기에서 지웁니다.... ' sql을 중복 실행하지 않으려면 사용자 지정 삭제 논리를 ' 추가합니다. ' CleanString(queryArg)을 호출합니다. ' SQL에 대한 연결을 설정합니다. Set cn = Server.CreateObject("ADODB.Connection") cn.Open connectionString ' 정리된 문자열에서 전달된 최적의 일치 결과를 가져옵니다. set rs = cn.Execute("exec BBSearch '" & queryArg & "'") ' 있는 경우 최적의 결과를 가져옵니다. if not(rs.EOF) then bbData = rs.GetRows end if ' 정리된 문자열에서 전달된 일반적인 일치 결과를 가져옵니다. set rs = cn.Execute("exec FTSearch '" & queryArg & "'") ' 아무것도 반환하지 않는 경우 중지합니다. if (rs.EOF and IsEmpty(bbdata)) then response.Write("일치하는 행이 없음" & VbCrLF) call ConnClose exit sub end if ' 그렇지 않으면, 해당하는 이 열을 끕니다. if not(rs.EOF) then alldata = rs.GetRows Session("results") = alldata end if call ConnClose else ' 캐시에서 로드합니다(usecache=1). alldata = Session("results") ' 또한 여기에 사용할 열 범위를 가져옵니다. if (request.Form("firstRow") <> "") then firstRow = request.Form("firstRow") lastRow = firstRow+pageSize elseif (request.QueryString("firstRow") <> "") then firstRow = request.QueryString("firstRow") lastRow = firstRow+pageSize end if end if ' useCache<>TRUE ' 이 응용 프로그램에서 페이지 크기보다 클 수 있는 ' 최적의 결과를 모두 인쇄합니다. 그런 다음 일반적인 결과를 페이징합니다. ' 이전에 최적의 결과를 표시한 캐시를 사용하고 있다고 가정합니다. ' 최적의 결과가 없으면 계속해서 합니다. if not(IsEmpty(bbdata)) then response.Write("최적의 결과:" & VbCrLf) for p = 0 to ubound(bbdata, 2) response.Write(bbData(0,p) & " " & bbData(1,p) & VbCrLf) next response.Write(VbCrLf) end if ' 있는 경우 검색 결과를 반환합니다(최적의 결과만 있을 수 있음) if not(IsEmpty(alldata)) then if uBound(alldata, 2) < lastRow then lastRow = uBound(allData, 2) end if response.Write("검색 결과:" & VbCrLf) for p = firstRow to lastRow response.Write(allData(0,p) & " " & allData(1,p) & VbCrLf) next end if ' not(IsEmpty(alldata)) End Sub '----------------------------------------------------------------' ' 연결 개체를 닫고 삭제합니다. ' '----------------------------------------------------------------' Private Sub ConnClose rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub call SearchNPage %> ---------------- Test Complete ---------------- <form action="<이 페이지>" method="post"> <input type=submit value="next <%=pageSize%> rows" NAME="Submit1"> <input type=hidden name="useCache" value="1"> <input type=hidden name="firstRow" value=<%=lastrow+1%>> </form> </pre> </body> </html> 단순 HTML 폼 페이지는 위와 같은 별도의 스크립트를 반환합니다. <html> <head><title>Enter search terms</title> </head> <body> <form action="<asp 페이지 검색>" method="post"> 검색 용어: <input name="searchTerm"> <p> <input type="submit" value="Search"> </form> </body> </html>

위의 두 코드 샘플에 표시된 것처럼 효과적인 전체 텍스트 쿼리를 실행(최적 시스템 완료)하고 그 결과를 통해 캐싱 및 페이징할 수 있는 웹 응용 프로그램을 만드는 것은 어렵지 않습니다. 최소의 오버헤드로 논리를 추가하여 추가 데이터를 제공하고, 최적 시스템의 모양을 개선하고, 검색 결과를 탐색할 수 있습니다. 오류 처리, 보안 및 들어오는 데이터 스크러빙을 위해 추가 논리를 심사숙고하여 구현하는 것이 좋습니다.

위의 고급 권장 사항 및 예제에서는 SQL Server 2000 전체 텍스트 검색을 사용하여 빠르고 확장 가능한 웹 검색 응용 프로그램이 올바르게 디자인 및 구현되어야 합니다.

부록 C: 참고 자료

Full-Text Search Deployment  

전체 텍스트 검색을 시작하는 사용자에게 좋은 참고 자료이며, 입력 방법, 하드웨어 및 소프트웨어 요구 사항을 다루고, SQL Server 2000 전체 텍스트 검색 작업에 대한 팁, 트릭, 추가 설명서 등을 제공합니다.

전체 텍스트 검색 공용 뉴스 그룹(microsoft.public.sqlserver.fulltext)

전체 텍스트 검색 관련 질문에 대한 대답을 찾을 수 있는 곳으로 유용한 힌트와 트릭을 제공합니다. 전체 텍스트 검색 뉴스 그룹에는 SQL Server 개발 팀의 구성원들과 학식이 매우 높은 Microsoft MVP들이 자주 방문합니다.