SQL?Server如何實(shí)現(xiàn)全文搜索查詢

本篇內(nèi)容介紹了“SQL Server如何實(shí)現(xiàn)全文搜索查詢”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

一、概述

全文索引在表中包括一個(gè)或多個(gè)基于字符的列。這些列可以具有以下任何數(shù)據(jù)類型：char、varchar、nchar、nvarchar、text、ntext、image、xml 或 varbinary（max） 和 FILESTREAM。每個(gè)全文索引為表中的一列或多列編制索引，并且每列可以使用特定的語言。

全文查詢通過基于特定語言（如英語或中文）的規(guī)則對(duì)單詞和短語進(jìn)行操作，對(duì)全文索引中的文本數(shù)據(jù)執(zhí)行語言搜索。全文查詢可以包括簡(jiǎn)單的字詞和短語，也可以包括字詞或短語的多種形式。全文查詢返回至少包含一個(gè)匹配項(xiàng)（也稱為匹配項(xiàng)）的任何文檔。當(dāng)目標(biāo)文檔包含全文查詢中指定的所有術(shù)語，并滿足任何其他搜索條件（如匹配術(shù)語之間的距離）時(shí)，將發(fā)生匹配。

二、全文搜索查詢

將列添加到全文索引后，用戶和應(yīng)用程序可以對(duì)列中的文本運(yùn)行全文查詢。這些查詢可以搜索以下任何內(nèi)容：

• 一個(gè)或多個(gè)特定單詞或短語（簡(jiǎn)單術(shù)語)。

• 單詞或短語，其中單詞以指定文本（前綴術(shù)語）開頭)。

• 特定單詞的屈折形

  式（世代術(shù)語）)。

• 接近另一個(gè)單詞或短語的單詞或短語（鄰近術(shù)語）)。

• 特定單詞的同義詞形式（同義詞庫)。

• 使用加權(quán)值的字詞或短語（加權(quán)術(shù)語)。

全文查詢不區(qū)分大小寫。例如，搜索"Aluminum" 或 "aluminum"將返回相同的結(jié)果。

全文查詢使用一小組 Transact-SQL 謂詞 ( and ) 和函數(shù) ( and ) 。但是，給定業(yè)務(wù)方案的搜索目標(biāo)會(huì)影響全文查詢的結(jié)構(gòu)。例如：CONTAINS FREETEXT CONTAINSTABLE FREETEXTTABLE

（1）電子商務(wù)-在網(wǎng)站上搜索產(chǎn)品：

SELECT product_id FROM products   
WHERE CONTAINS(product_description, '"Snap Happy 100EZ"' OR FORMSOF(THESAURUS,'"Snap Happy"') OR '100EZ')   
AND product_cost < 200 ;

（2）招聘方案 - 搜索具有使用 SQL Server 經(jīng)驗(yàn)的求職者：

SELECT candidate_name,SSN FROM candidates   
WHERE CONTAINS(candidate_resume, '"SQL Server"') AND candidate_division = 'DBA';

三、將全文搜索查詢與 LIKE 謂詞進(jìn)行比較

與全文搜索相比，LIKE Transact-SQL 謂詞僅適用于字符模式。此外，不能使用 LIKE 謂詞查詢格式化的二進(jìn)制數(shù)據(jù)。此外，針對(duì)大量非結(jié)構(gòu)化文本數(shù)據(jù)的 LIKE 查詢比針對(duì)相同數(shù)據(jù)的等效全文查詢慢得多。針對(duì)數(shù)百萬行文本數(shù)據(jù)的 LIKE 查詢可能需要幾分鐘才能返回；而全文查詢對(duì)相同數(shù)據(jù)可能只需要幾秒鐘或更短的時(shí)間，具體取決于返回的行數(shù)。

四、全文搜索體系結(jié)構(gòu)

全文搜索體系結(jié)構(gòu)由以下過程組成：

• SQL Server 進(jìn)程 （sqlservr.exe）。

• 篩選器守護(hù)程序主機(jī)進(jìn)程 （fdhost.exe）。

出于安全原因，過濾器由稱為過濾器守護(hù)程序主機(jī)的單獨(dú)進(jìn)程加載。fdhost.exe進(jìn)程由 FDHOST 啟動(dòng)器服務(wù) （MSSQLFDLauncher） 創(chuàng)建，它們?cè)?FDHOST 啟動(dòng)器服務(wù)帳戶的安全憑據(jù)下運(yùn)行。因此，F(xiàn)DHOST 啟動(dòng)器服務(wù)必須運(yùn)行才能使全文索引和全文查詢正常工作。

這兩個(gè)過程包含全文搜索體系結(jié)構(gòu)的組件。下圖總結(jié)了這些組件及其關(guān)系。這些組件在圖示后進(jìn)行了描述。

4.1、SQL Server 進(jìn)程

SQL Server 進(jìn)程使用以下組件進(jìn)行全文搜索：

• 用戶表。這些表包含要進(jìn)行全文索引的數(shù)據(jù)。

• 全文收集器。全文收集器使用全文爬網(wǎng)線程。它負(fù)責(zé)計(jì)劃和驅(qū)動(dòng)全文索引的填充，還負(fù)責(zé)監(jiān)視全文目錄。

• 同義詞庫文件。這些文件包含搜索詞的同義詞。有關(guān)詳細(xì)信息，請(qǐng)參閱配置和管理全文搜索的同義詞庫文件。

• 非索引字表對(duì)象。非索引字表對(duì)象包含對(duì)搜索無用的常用詞的列表。有關(guān)詳細(xì)信息，請(qǐng)參閱配置和管理全文搜索的非索引字和非索引字表。

• SQL Server 查詢處理器。查詢處理器編譯并執(zhí)行 SQL 查詢。如果 SQL 查詢包含全文搜索查詢，則在編譯和執(zhí)行期間，該查詢將發(fā)送到全文引擎。查詢結(jié)果與全文索引匹配。

• 全文引擎。SQL Server 中的全文引擎與查詢處理器完全集成。全文引擎編譯并執(zhí)行全文查詢。作為查詢執(zhí)行的一部分，全文引擎可能會(huì)從同義詞庫和非索引字表接收輸入。

• 索引編寫器（索引器）。索引編寫器生成用于存儲(chǔ)索引令牌的結(jié)構(gòu)。

• 篩選器守護(hù)程序管理器。篩選器守護(hù)程序管理器負(fù)責(zé)監(jiān)視全文引擎篩選器守護(hù)程序主機(jī)的狀態(tài)。

4.2、過濾器守護(hù)程序主機(jī)進(jìn)程

篩選器守護(hù)程序主機(jī)是由全文引擎啟動(dòng)的進(jìn)程。它運(yùn)行以下全文搜索組件，這些組件負(fù)責(zé)訪問、篩選和分詞表數(shù)據(jù)，以及分詞和對(duì)查詢輸入進(jìn)行詞干提取。

篩選器守護(hù)程序主機(jī)的組件如下所示：

• 協(xié)議處理程序。此組件從內(nèi)存中提取數(shù)據(jù)以進(jìn)行進(jìn)一步處理，并從指定數(shù)據(jù)庫中的用戶表中訪問數(shù)據(jù)。它的職責(zé)之一是從全文索引的列中收集數(shù)據(jù)，并將其傳遞給篩選器守護(hù)程序主機(jī)，該主機(jī)將根據(jù)需要應(yīng)用篩選和分詞系統(tǒng)。

• 過濾器。某些數(shù)據(jù)類型需要篩選，然后才能對(duì)文檔中的數(shù)據(jù)進(jìn)行全文索引，包括變量、變量二元（最大值）、圖像或 xml 列中的數(shù)據(jù)。用于給定文檔的篩選器取決于其文檔類型。例如，不同的篩選器用于 Microsoft Word （.doc） 文檔、Microsoft Excel （.xls） 文檔和 XML （.xml） 文檔。然后，篩選器從文檔中提取文本塊，刪除嵌入的格式并保留文本，并可能保留有關(guān)文本位置的信息。結(jié)果是文本信息流。有關(guān)詳細(xì)信息，請(qǐng)參閱配置和管理搜索篩選器。

• 分詞系統(tǒng)和詞干分析器。分詞系統(tǒng)是特定于語言的組件，它根據(jù)給定語言的詞法規(guī)則（斷詞）查找單詞邊界。每個(gè)分詞系統(tǒng)都與特定于語言的詞干分析器組件相關(guān)聯(lián)，該組件共軛動(dòng)詞并執(zhí)行屈折擴(kuò)展。在編制索引時(shí)，篩選器守護(hù)程序宿主使用分詞系統(tǒng)和詞干分析器對(duì)給定表列中的文本數(shù)據(jù)執(zhí)行語言分析。與全文索引中的表列關(guān)聯(lián)的語言確定用于為列編制索引的分詞系統(tǒng)和詞干分析器。

五、全文搜索處理

全文搜索由全文引擎提供支持。全文引擎有兩個(gè)角色：索引支持和查詢支持。

5.1、全文索引過程

啟動(dòng)全文填充（也稱為爬網(wǎng)）時(shí)，全文引擎會(huì)將大量數(shù)據(jù)推送到內(nèi)存中，并通知篩選器守護(hù)程序主機(jī)。主機(jī)過濾和單詞分解數(shù)據(jù)，并將轉(zhuǎn)換后的數(shù)據(jù)轉(zhuǎn)換為倒置單詞列表。然后，全文搜索從單詞列表中提取轉(zhuǎn)換后的數(shù)據(jù)，處理數(shù)據(jù)以刪除非索引字，并將批處理的單詞列表保存到一個(gè)或多個(gè)倒排索引中。

對(duì)存儲(chǔ)在 varbinary（max） 或圖像列中的數(shù)據(jù)編制索引時(shí)，實(shí)現(xiàn) IFilter 接口的篩選器會(huì)根據(jù)該數(shù)據(jù)的指定文件格式（例如 Microsoft Word）提取文本。在某些情況下，過濾器組件需要將變量（max）或圖像數(shù)據(jù)寫出到filterdata文件夾，而不是推送到內(nèi)存中。

作為處理的一部分，收集的文本數(shù)據(jù)通過分詞系統(tǒng)傳遞，以將文本分隔為單獨(dú)的標(biāo)記或關(guān)鍵字。用于標(biāo)記化的語言在列級(jí)別指定，也可以通過過濾器組件在 varbinary（max）、圖像或 xml 數(shù)據(jù)中標(biāo)識(shí)。

可以執(zhí)行其他處理以刪除非索引字，并在標(biāo)記存儲(chǔ)在全文索引或索引片段中之前對(duì)其進(jìn)行規(guī)范化。

填充完成后，將觸發(fā)最終合并過程，將索引片段合并到一個(gè)主全文索引中。這提高了查詢性能，因?yàn)橹恍枰樵冎魉饕皇嵌鄠€(gè)索引片段，并且可以使用更好的評(píng)分統(tǒng)計(jì)信息進(jìn)行相關(guān)性排名。

5.2、全文查詢流程

查詢處理器將查詢的全文部分傳遞給全文引擎進(jìn)行處理。全文引擎執(zhí)行斷詞和（可選）同義詞庫擴(kuò)展、詞干提取和非索引字（干擾詞）處理。然后，查詢的全文部分以 SQL 運(yùn)算符的形式表示，主要表示為流式表值函數(shù) （STVF）。在查詢執(zhí)行期間，這些 STVF 訪問倒排索引以檢索正確的結(jié)果。此時(shí)，結(jié)果要么返回到客戶端，要么在返回到客戶端之前進(jìn)一步處理。

六、全文索引體系結(jié)構(gòu)

全文引擎使用全文索引中的信息來編譯全文查詢，這些查詢可以快速在表中搜索特定單詞或單詞組合。全文索引存儲(chǔ)有關(guān)重要單詞及其在數(shù)據(jù)庫表的一列或多列中的位置的信息。全文索引是一種特殊類型的基于令牌的功能索引，由 SQL Server 全文引擎生成和維護(hù)。構(gòu)建全文索引的過程不同于構(gòu)建其他類型的索引。全文引擎不是基于存儲(chǔ)在特定行中的值構(gòu)造 B 樹結(jié)構(gòu)，而是基于要編制索引的文本中的單個(gè)標(biāo)記構(gòu)建倒置、堆疊、壓縮的索引結(jié)構(gòu)。全文索引的大小僅受運(yùn)行 SQL Server 實(shí)例的計(jì)算機(jī)的可用內(nèi)存資源的限制。

從 SQL Server 2008 （10.0.x） 開始，全文索引與數(shù)據(jù)庫引擎集成，而不是像以前版本的 SQL Server 那樣駐留在文件系統(tǒng)中。對(duì)于新數(shù)據(jù)庫，全文目錄現(xiàn)在是不屬于任何文件組的虛擬對(duì)象;它只是一個(gè)邏輯概念，指的是一組全文索引。

每個(gè)表只允許有一個(gè)全文索引。若要在表上創(chuàng)建全文索引，該表必須具有單個(gè)唯一的非空列?？梢栽?char、varchar、nchar、nvarchar、nvarchar、text、ntext、image、xml、varbinary 和 varbinary（max） 類型的列上構(gòu)建全文索引，以便為全文搜索編制索引。 在數(shù)據(jù)類型為變量、變量二進(jìn)制（max）、圖像或 xml 的列上創(chuàng)建全文索引需要指定類型列。類型列是表格列，您可以在其中將文檔的文件擴(kuò)展名（.doc、.pdf、.xls等）存儲(chǔ)在每行中。

6.1、全文索引結(jié)構(gòu)

充分了解全文索引的結(jié)構(gòu)將有助于您了解全文引擎的工作原理。例如：

下表顯示了片段 1，描述了在“文檔”表的“標(biāo)題”列上創(chuàng)建的全文索引的內(nèi)容。全文索引包含的信息比此表中顯示的信息要多。該表是全文索引的邏輯表示形式，僅用于演示目的。這些行以壓縮格式存儲(chǔ)，以優(yōu)化磁盤使用情況。

請(qǐng)注意，數(shù)據(jù)已從原始文檔反轉(zhuǎn)。發(fā)生反轉(zhuǎn)是因?yàn)殛P(guān)鍵字映射到文檔 ID。因此，全文索引通常稱為倒排索引。

另請(qǐng)注意，關(guān)鍵字“and”已從全文索引中刪除。這樣做是因?yàn)椤癮nd”是非索引字，從全文索引中刪除非索引字可以節(jié)省大量磁盤空間，從而提高查詢性能。

片段一：

“關(guān)鍵字”列包含在編制索引時(shí)提取的單個(gè)標(biāo)記的表示形式。分詞系統(tǒng)確定令牌的組成。

6.2、全文索引片段

邏輯全文索引通常拆分為多個(gè)內(nèi)部表。每個(gè)內(nèi)部表稱為全文索引片段。其中一些片段可能包含比其他片段更新的數(shù)據(jù)。例如，如果用戶更新 標(biāo)識(shí)為 3 的以下行，并且表是自動(dòng)更改跟蹤的，則會(huì)創(chuàng)建一個(gè)新片段。

一下的片段 2，與片段 3 相比，片段包含有關(guān) 標(biāo)識(shí) 1 的更新數(shù)據(jù)。因此，當(dāng)用戶查詢“后反射器”時(shí)，片段 2 中的數(shù)據(jù)將用于 標(biāo)識(shí)3。每個(gè)片段都標(biāo)有創(chuàng)建時(shí)間戳，可以使用sys.fulltext_index_fragments目錄視圖查詢?cè)摃r(shí)間戳。

片段 2：

從片段 2 可以看出，全文查詢需要在內(nèi)部查詢每個(gè)片段并丟棄較舊的條目。因此，全文索引中的全文索引片段過多可能會(huì)導(dǎo)致查詢性能大幅下降。若要減少片段數(shù)，請(qǐng)使用“更改全文目錄 Transact-SQL”語句的“重新組織”選項(xiàng)重新組織全文目錄。此語句執(zhí)行主合并，這會(huì)將片段合并為一個(gè)較大的片段，并從全文索引中刪除所有過時(shí)的條目。

重新組織后，示例索引將包含以下行：

6.3、全文索引和常規(guī) SQL Server 索引之間的差異

“SQL Server如何實(shí)現(xiàn)全文搜索查詢”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！