使用 microsoft sql server 2000 的全文搜索功能構(gòu)建 web 搜索應(yīng)用程序 andrew b. cencinimicrosoft corporation 2002年12月 適用于： microsoft® sql™ server 2000摘要：學(xué)習(xí)如何充分利用 sql server 2000 的全文搜索功能。本文包含有關(guān)實(shí)現(xiàn)最大吞吐率和最佳性能的幾點(diǎn)提示和技巧。 目錄簡(jiǎn)介 全文搜索功能簡(jiǎn)介 配置全文搜索功能 全文查詢(xún) 排位和優(yōu)化 其他性能技巧 小結(jié) 附錄 a：實(shí)現(xiàn)全文搜索功能的最佳選擇 附錄 b：使用最佳選擇、結(jié)果分頁(yè)和有效全文查詢(xún)邏輯的示例應(yīng)用程序 附錄 c：資源 簡(jiǎn)介使用 microsoft® sql™ server 2000 的全文搜索功能，可以對(duì)在非結(jié)構(gòu)化文本數(shù)據(jù)上生成的索引執(zhí)行快速、靈活的查詢(xún)。常用的全文搜索工具是網(wǎng)站的搜索引擎。為了幫助讀者理解全文搜索功能的最佳使用方法，本文介紹了大量抽象概念；并對(duì)優(yōu)化全文索引和查詢(xún)以實(shí)現(xiàn)最大吞吐率和最佳性能，提供了幾點(diǎn)提示和技巧。全文搜索功能簡(jiǎn)介全文搜索功能在 sql server 7.0 中引入。全文搜索的核心引擎建立在 microsoft search (mssearch) 技術(shù)上，microsoft exchange 和 microsoft sharepoint™ portal server 等產(chǎn)品中也采用了此項(xiàng)技術(shù)。sql server 7.0 全文搜索中公開(kāi)的功能可提供基本的文本搜索功能，并使用早期版本的 mssearch；而 sql server 2000 的全文搜索實(shí)現(xiàn)則包含一組可靠的索引和查詢(xún)功能，并在 sql server 7.0 的基礎(chǔ)之上添加了幾項(xiàng)增強(qiáng)功能。這些增強(qiáng)功能包括：通過(guò) microsoft 群集服務(wù)完全支持群集操作，能夠過(guò)濾和索引 image 列中存儲(chǔ)的文檔，提供改進(jìn)的語(yǔ)言支持，以及在性能、可縮放性和可靠性方面進(jìn)行了改進(jìn)。mssearch 生成、維護(hù)和查詢(xún)文件系統(tǒng)中（而不是 sql server 中）存儲(chǔ)的全文索引。mssearch 進(jìn)行全文索引時(shí)使用的邏輯和物理存儲(chǔ)單元是目錄。全文目錄在每個(gè)數(shù)據(jù)庫(kù)中包含一個(gè)或多個(gè)全文索引 - 可以為 sql server 中的每個(gè)表創(chuàng)建一個(gè)全文索引，且索引中可以包含該表中的一列或多列。每個(gè)表只能屬于一個(gè)目錄，且每個(gè)表只能創(chuàng)建一個(gè)索引。我們將簡(jiǎn)單介紹有關(guān)組織全文目錄和索引的最佳方案 - 但首先，讓我們來(lái)簡(jiǎn)單了解一下全文搜索的工作原理。配置全文搜索功能要為 sql server 中存儲(chǔ)的文本數(shù)據(jù)創(chuàng)建全文索引，應(yīng)該先完成以下幾步準(zhǔn)備工作。第一步是以全文方式啟用包含要生成索引的文本數(shù)據(jù)的數(shù)據(jù)庫(kù)（如果您尚未執(zhí)行此操作）。注意：執(zhí)行以下語(yǔ)句將丟棄并重新創(chuàng)建屬于要啟用全文搜索的數(shù)據(jù)庫(kù)的所有全文目錄。除非要重新創(chuàng)建全文目錄，否則請(qǐng)確保在要啟用的特定數(shù)據(jù)庫(kù)中未創(chuàng)建任何全文目錄。如果您是 sysadmin 角色的成員或此數(shù)據(jù)庫(kù)的 db_owner，可以繼續(xù)進(jìn)行并發(fā)出以下語(yǔ)句：use northwind exec sp_fulltext_database 'enable'
接下來(lái)，您需要?jiǎng)?chuàng)建全文目錄，以存儲(chǔ)全文索引。正如前面所提到的，此目錄中的數(shù)據(jù)存儲(chǔ)在文件系統(tǒng)中（而不是 sql server 中），因此，在考慮全文目錄的存儲(chǔ)位置時(shí)應(yīng)該仔細(xì)選擇。除非指定其他位置，否則全文目錄將存儲(chǔ)在 ftdata 目錄（位于 microsoft sql server/mssql 存儲(chǔ)位置中）的子目錄中。以下是在非默認(rèn)位置創(chuàng)建全文目錄的方法：exec sp_fulltext_catalog 'cat_desc', 'create', 'f:/ft'
對(duì)于包含大量數(shù)據(jù)，且這些數(shù)據(jù)可適應(yīng)此架構(gòu)（或許是主架構(gòu)）更改的系統(tǒng)，其性能會(huì)得到顯著的提高。但在何時(shí)應(yīng)用多個(gè)過(guò)濾器或不應(yīng)用過(guò)濾器方面卻有著明顯的限制。當(dāng)然，還有其他的方法可以解決這些問(wèn)題。通過(guò)以上示例，您會(huì)了解一種將某些搜索條件抽象到架構(gòu)的方法 - 實(shí)際上是“欺騙”優(yōu)化程序（更確切的說(shuō)是“成為”優(yōu)化程序），因?yàn)樵?sql server 本身的全文查詢(xún)中當(dāng)前不存在本地優(yōu)化。其他性能技巧人們?cè)诹奶鞎r(shí)常常問(wèn)我的另一個(gè)問(wèn)題是如何才能分頁(yè)顯示全文查詢(xún)結(jié)果。換句話(huà)說(shuō)，如果我要發(fā)出“root beer”查詢(xún)，一次在某一 web 頁(yè)上顯示 40 個(gè)結(jié)果，并且只希望返回該頁(yè)面上的 40 個(gè)結(jié)果（例如，如果我在第三頁(yè)，我希望僅返回第 81 至第 120 條結(jié)果）。對(duì)于分頁(yè)顯示結(jié)果，我曾見(jiàn)過(guò)多種方法，但沒(méi)有一種方法能夠做到百分之百有效。我所推薦的方法可以最大程度地減少全文查詢(xún)執(zhí)行的次數(shù)（實(shí)際上，對(duì)于要分頁(yè)顯示的每個(gè)結(jié)果集只需執(zhí)行一次），并將 web 服務(wù)器用作一個(gè)簡(jiǎn)單的緩存。從更高的層面來(lái)講，您只需在全文查詢(xún)中檢索一個(gè)完整的主鍵和排位值行集合（如果需要，可以在架構(gòu)中使用最佳選擇并提取常用過(guò)濾器），并將其存儲(chǔ)在 web 服務(wù)器的內(nèi)存中（這取決于您的應(yīng)用程序和負(fù)載，想象將 <32 字節(jié)的典型主鍵大小與 <4 字節(jié)的排位大小相加 [等于 <36 字節(jié)]，然后乘以通常返回的結(jié)果集 <1000 行，最后等于 <35k。假定一個(gè)在任何給定時(shí)間返回 <1000 個(gè)活動(dòng)查詢(xún)結(jié)果集中的一個(gè)活動(dòng)緩存集，您將發(fā)現(xiàn)此活動(dòng)緩存集在 web 服務(wù)器上占用的內(nèi)存少于 35mb - 這還可以接受）。為了分頁(yè)顯示結(jié)果，該進(jìn)程只遍歷 web 服務(wù)器的內(nèi)存中存儲(chǔ)的數(shù)組，并對(duì) sql server 發(fā)出 select 以便只顯示需要顯示的行和列。這又回到了全文查詢(xún)僅返回主鍵和排位的概念中 - select（甚至許多這樣的查詢(xún)語(yǔ)句）比全文查詢(xún)的速度快許多倍。使用 select 而不是與基表合并多個(gè)行，并結(jié)合多個(gè)其他策略，您可以保留 sql server 計(jì)算機(jī)上更多的 cpu 周期，并且更有效、更劃算地利用 web 領(lǐng)域。另一種可以替代 web 服務(wù)器端緩存的方法是在 sql server 自身中緩存結(jié)果集，并定義多種用于瀏覽這些結(jié)果的方法。雖然本文著重說(shuō)明 web 服務(wù)器 (asp) 級(jí)別的應(yīng)用程序設(shè)計(jì)，但 sql server 的可編程功能還為生成高性能的 web 搜索應(yīng)用程序提供了強(qiáng)大的框架。小結(jié)microsoft sql server 2000 的全文搜索功能為索引和查詢(xún)數(shù)據(jù)庫(kù)中存儲(chǔ)的非結(jié)構(gòu)化文本數(shù)據(jù)提供了可靠、快速而靈活的方法。如果要廣泛地將這種快速、準(zhǔn)確的搜索功能應(yīng)用于各種應(yīng)用程序，那么很有必要充分利用其速度和精確性，來(lái)實(shí)現(xiàn)全文搜索解決方案。通過(guò)分布計(jì)算負(fù)載并通過(guò)某些巧妙的方式對(duì)數(shù)據(jù)進(jìn)行組織，可以省下錢(qián)來(lái)購(gòu)買(mǎi)其他硬件和軟件，以擺脫因不必要的緩慢查詢(xún)帶來(lái)的困擾。在開(kāi)發(fā)優(yōu)秀的搜索應(yīng)用程序時(shí)，通常要考慮到許多因素和注意事項(xiàng)，希望本文提供的信息和示例對(duì)您學(xué)習(xí)使用 sql server 2000 生成出色的 web 搜索應(yīng)用程序會(huì)有所幫助。附錄 a：實(shí)現(xiàn)全文搜索功能的最佳選擇改進(jìn)全文查詢(xún)性能和有效性的一種可行方法是實(shí)現(xiàn)“最佳選擇”系統(tǒng)。此系統(tǒng)是一種很簡(jiǎn)單的方法，可確保某些與特定查詢(xún)表達(dá)式匹配的行先于其他行返回。最佳選擇沒(méi)有復(fù)雜的預(yù)編程邏輯（例如，sharepoint portal server 就包含這樣的邏輯），因此，通常是首選辦法。在本示例中挑選出最佳選擇，并將唯一的主鍵和一些關(guān)鍵字存儲(chǔ)在單獨(dú)的表中。freetexttable 查詢(xún)對(duì)（非常小的）最佳選擇表執(zhí)行，并且從該查詢(xún)中返回的任何結(jié)果都與對(duì)基表的 freetexttable 查詢(xún)結(jié)果一同返回。在給定這些搜索條件下，最先返回的將是所有“最佳選擇”行，隨后是被 mssearch 視為關(guān)聯(lián)程度最高的行（以遞減順序返回）。下面是一個(gè)非常簡(jiǎn)單的用于創(chuàng)建最佳選擇系統(tǒng)的示例腳本。use mydb
create table documenttable(ftkey int not null, document ntext)create unique index dtftkey_idx on documenttable(ftkey)
/* 現(xiàn)在創(chuàng)建最佳選擇表和索引 （添加應(yīng)該始終最先返回的文檔）*/create table bestbets(ftkey int not null, keywords ntext)create unique index bbftkey_idx on bestbets(ftkey)
'----------------------------------------------------------------'' 顯示與最佳選擇/搜索詞匹配的簡(jiǎn)單主鍵/排位 ''----------------------------------------------------------------'private sub searchnpage()
dim p ' 循環(huán)通過(guò)行時(shí)的計(jì)數(shù)器 dim numrows ' 緩沖/結(jié)果集中的總行數(shù)
if (usecache <> "1") then ' 獲取最佳選擇/結(jié)果并將其緩存
dim queryarg ' 傳入的查詢(xún)?cè)~ 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
' 如果未返回任何結(jié)果，則結(jié)束 if (rs.eof and isempty(bbdata)) then response.write("沒(méi)有匹配的行" & 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
' 對(duì)于本應(yīng)用程序，只是打印出所有最佳選擇 ' （可能比頁(yè)面大小大），然后分頁(yè)顯示普通結(jié)果 ' 此處假設(shè)：在使用緩存時(shí)，如果沒(méi)有新的最佳選擇，' 則使用以前顯示的最佳選擇 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
' 返回搜索結(jié)果（可能只有最佳選擇） if not(isempty(alldata)) then if ubound(alldata, 2) < lastrow then lastrow = ubound(alldata, 2) end if
response.write("搜索結(jié)果：" & vbcrlf)
for p = firstrow to lastrowresponse.write(alldata(0,p) & " " & alldata(1,p) & vbcrlf) next end if ' not(isempty(alldata))
end sub
'----------------------------------------------------------------'' 關(guān)閉并清除連接對(duì)象 ''----------------------------------------------------------------'private sub connclose rs.close set rs = nothing cn.close set cn = nothingend sub
<form action="<搜索 asp 頁(yè)面>" method="post">搜索詞：<input name="searchterm"><p><input type="submit" value="search"></form>
</body></html>
正如以上兩個(gè)代碼示例所示，創(chuàng)建可執(zhí)行有效全文查詢(xún)（用最佳選擇完成）并緩存和分頁(yè)顯示結(jié)果的 web 應(yīng)用程序，并不需要花費(fèi)太多的工夫。只需使用最低的系統(tǒng)開(kāi)銷(xiāo)，即可添加用于提供其他數(shù)據(jù)、增強(qiáng)最佳選擇的外觀以及在搜索結(jié)果中導(dǎo)航的邏輯（此外，強(qiáng)烈建議您實(shí)現(xiàn)其他用于錯(cuò)誤處理、安全設(shè)置和清理傳入數(shù)據(jù)的嚴(yán)密邏輯）。通過(guò)上面的高級(jí)建議和示例，使用 sql server 2000 全文搜索設(shè)計(jì)和實(shí)現(xiàn)快速可縮放的 web 搜索應(yīng)用程序就是輕而易舉的事情了。
附錄 c：資源full-text search deployment（英文）http://support.microsoft.com/default.aspx?scid=/support/sql/content/2000papers/fts_white%20paper.asp是那些初次接觸全文搜索的用戶(hù)的最佳參考。介紹了填充方法及硬件和軟件需求，并為使用 sql server 2000 全文搜索提供了提示、技巧和其他文檔。全文搜索公共新聞組 (microsoft.public.sqlserver.fulltext)查找有關(guān)全文搜索問(wèn)題的答案以及有用提示和技巧的理想場(chǎng)所。全文搜索新聞組是 sql server 開(kāi)發(fā)小組和博學(xué)的 microsoft mvp 成員經(jīng)常光顧的場(chǎng)所。
