SQL Server 使用全文索引進行頁面搜尋

標籤：

原文:SQL Server 使用全文索引進行頁面搜尋

標籤：SQL SERVER/MSSQL SERVER/資料庫/DBA/全文索引

概述  

全文引擎使用全文索引中的資訊來編譯可快速搜尋表中的特定詞或片語的全文檢索查詢。全文索引將有關重要的詞及其位置的資訊儲存在資料庫表的一列或多列中。全文索引是一種特殊類型的基於標記的功能性索引，它是由 SQL Server 全文引擎產生和維護的。產生全文索引的過程不同於產生其他類型的索引。全文引擎並非基於特定行中儲存的值來構造 B 樹結構，而是基於要編製索引的文本中的各個標記來產生倒排、堆積且壓縮的索引結構。在 SQL Server 2008 中，全文索引大小僅受運行 SQL Server 執行個體的電腦的可用記憶體資源限制。

最近遇到一個需求，需要在一個100萬的表中通過關鍵字對一個大型字元欄位進行檢索，類似於百度搜尋引擎的搜尋，查詢出所有包含關鍵字的資料並進行分頁處理，並且將匹配度最高的資料排在第一位，要求查詢回應時間控制在1秒左右。

 測試環境：SQL Server 2008 r2

 

目錄

•  概述
•  全文索引概念
•  建立全文索引
  •  啟動服務
  •  建立全文檢索目錄
  •  建立全文索引
  •  全文謂詞
•  需求
•  總結

全文索引概念

• 全文索引是針對資料表，只能對錶建立全文索引，不能對資料庫建立全文索引。
• 每個資料庫可以不包含全文檢索目錄或包含多個全文檢索目錄，一個全文檢索目錄可以包含多個全文索引，但一個全文索引只能用於構成一個全文檢索目錄。
• 一個資料表只能建立一個全文索引，一個全文索引可以包含多個欄位。
• 建立全文索引的表必須要有一個唯一的非空索引，並且這個唯一的非空的索引只能是一個欄位，不能是組合欄位。
• 每個表只允許有一個全文索引。若要對某個表建立全文索引，該表必須具有一個唯一且非 Null 的列。您可以對以下類型的列建立全文索引：char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary 和 varbinary(max)，從而可對這些列進行全文檢索搜尋。對資料類型為 varbinary、varbinary(max)、image 或 xml 的列建立全文索引需要您指定類型列。類型列是用來儲存每行中文檔的副檔名（.doc、.pdf、xls 等）的表列。

 

全文檢索搜尋由全文引擎提供支援。全文引擎有兩個角色：索引支援和查詢支援。

全文檢索搜尋體繫結構：

從 SQL Server 2008 開始，全文檢索搜尋體繫結構包括以下進程：

• SQL Server 進程 (sqlservr.exe)
• 篩選器背景程式宿主進程 (fdhost.exe)。

SQL Server 進程組件：

• 使用者表
  這些表包含要進行全文索引的資料。
  
  
• 全文收集器
  全文收集器使用全文耙梳線程。它負責計劃和驅動對全文索引的填充，並負責監視全文檢索目錄。
  
  
• 同義字檔案
  這些檔案包含搜尋項的同義字。
  
  
• 停用字詞表對象
  停用字詞表對象包含對搜尋無用的常見詞列表。
  
  
• SQL Server 查詢處理器
  查詢處理器編譯並執行 SQL 查詢。如果 SQL 查詢包含全文檢索搜尋查詢，則在編譯和執行期間該查詢都會發送到全文引擎。查詢結果將與全文索引相匹配。
  
  
• 全文引擎
  SQL Server 中的全文引擎現已與查詢處理器完全整合。全文引擎編譯和執行全文檢索查詢。作為查詢執行的一部分，全文引擎可能會接收來自同義字和停用字詞表的輸入。在 SQL Server 2008 和更高版本中，SQL Server 的全文引擎在 SQL Server 查詢處理器內部運行。
  
  
• 索引編寫器（索引器）
  索引編寫器產生用於儲存索引標記的結構。
  
  
• 篩選器背景程式管理器
  篩選器背景程式管理器負責監視全文引擎篩選器背景程式宿主的狀態。

篩選器背景程式宿主組件：

篩選器背景程式宿主是一個由全文引擎啟動的進程。它運行下列全文檢索搜尋組件，這些組件負責對錶中的資料進行訪問、篩選和斷字，同時還負責對查詢輸入進行斷字和提取詞幹：

篩選器背景程式宿主的組件如下：

• 通訊協定處理常式
  此組件從記憶體中取出資料，以進行進一步的處理，並訪問指定資料庫的使用者表中的資料。其職責之一是從全文索引列中收集資料，並將所收集的資料傳遞給篩選器背景程式宿主，從而由該宿主根據需要應用篩選和斷字元。
  
  
• 篩選器
  某些資料類型需要篩選，然後才能為文檔中的資料（包括 varbinary、varbinary(max)、image 或 xml 列中的資料）建立全文索引。給定文檔採用何種篩選器取決於文件類型。例如，Microsoft Word (.doc) 文檔、Microsoft Excel (.xls) 文檔和 XML (.xml) 文檔分別使用不同的篩選器。然後，篩選器從文檔中提取文字區塊區，刪除嵌入的格式並保留文本，如有可能的話也會保留有關文本位置的資訊。結果將以文本化資訊流的形式出現。
  
  
• 斷字元和詞幹分析器
  斷字元是特定於語言的組件，它根據給定語言的詞彙規則尋找詞邊界（“斷字”）。每個斷字元都與用於組合動詞及執行變形擴充的特定於語言的詞幹分析器組件相關聯。在建立索引時，篩選器背景程式宿主使用斷字元和詞幹分析器來對給定表列中的文本資料執行語言分析。與全文索引中的表列相關的語言將決定為列建立索引時要使用的斷字元和詞幹分析器。

 

建立全文索引啟動服務

 在SQL Server組態管理工具中，找到‘SQL Full-text Filter Daemon Launcher‘服務用本機使用者啟動。

建立全文檢索目錄

 開啟需要建立全文檢索目錄的資料庫-儲存-全文檢索目錄-右鍵建立全文檢索目錄

用語句建立全文檢索目錄 

CREATE FULLTEXT CATALOG [FD_HouseSearch]WITH ACCENT_SENSITIVITY = ONAS DEFAULTAUTHORIZATION [dbo]

建立全文索引

 右鍵需要建立全文索引的表-全文索引-定義全文索引

1.全文索引必須要有一個唯一非空索引，這裡選擇主鍵。

2.選擇需要全文檢索搜尋的列，並且選擇斷字元語言，因為該欄位主要用來儲存中文，所以這裡也選擇了簡體中文。

斷字元：斷字元用來對全文檢索搜尋資料進行語言分析，尋找單詞的邊界，也就是怎樣將一段很長的內容拆分成日常的詞語或字。例如“全文檢索搜尋”，可能會斷字成“全文”、‘搜尋’、‘全’、‘文’、‘搜’、‘索’等符合中國人正常的習慣的詞或字。

3.選擇跟蹤方式，這裡選擇自動跟蹤，就是表發生更改時自動填滿索引。

4.選擇全文檢索目錄、索引檔案、停用字詞表

停用字詞表：在剛才的斷字中講了怎樣斷字，這裡就是將斷的字儲存在一張表中，該處選擇系統預設的停用字詞表.

 

----查詢斷字表SELECT TOP 1000 * FROM sys.dm_fts_index_keywords(db_id(‘‘), object_id(‘‘))

 

5.填充計劃

可以建立填充計劃來填充全文索引，填充計劃可以是完全填充、增量填充、更新填充。

用語句建立全文索引

--語句少了很多預設參數，其它就按系統預設即可
CREATE FULLTEXT INDEX ON dbo.Housetest(Description)KEY INDEX PK_HousetestON FD_HouseSearch

 

全文謂詞

全文檢索查詢使用全文謂詞（CONTAINS 和 FREETEXT）以及全文函數（CONTAINSTABLE 和 FREETEXTTABLE）。它們支援複雜的 Transact-SQL 文法，這種文法支援各種形式的查詢詞。若要編寫全文檢索查詢，必須瞭解何時以及如何使用這些謂詞和函數。

CONTAINS 謂詞可以搜尋：

• 詞或短語。
  
• 詞或短語的首碼。
  
• 與另一個詞相鄰的詞。
  
• 由另一個詞的屈折而產生的詞（例如，drive 一詞是 drives、drove、driving 和 driven 屈折的詞幹）。
  
• 使用同義字確定的另一個詞的同義字（例如，metal 一詞可能有 aluminum 和 steel 等同義字）。

---下面的樣本將尋找包含 "Mountain"
USE AdventureWorks2008R2;GOSELECT Name, ListPriceFROM Production.ProductWHERE CONTAINS(Name, ‘Mountain‘);GO

--下面的樣本將尋找包含 "Mountain"或 "Road"

USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE CONTAINS(Name, ‘ "Mountain" OR "Road" ‘)GO

---下面的樣本返回的所有產品名稱中，其 Name 列中至少有一個詞以前輟 chain 開頭

USE AdventureWorks2008R2;GOSELECT NameFROM Production.ProductWHERE CONTAINS(Name, ‘ "Chain*" ‘);GO

FREETEXT謂詞的用法這裡就不做解釋了！

需求

 現在來說一下我最近的需求，表資料100萬條，資料這裡就不弄出來了，只把方案說一下，title類似於文章的標題，Description是內容也是全文索引欄位

方案1：like，測試後果斷排除

方案2：直接使用全文檢索搜尋進行，排序消耗大。

方案3：由於查詢需要對Title進行排序，建Title欄位的倒序索引包含其它欄位，最後選擇該方案(建立Title欄位的倒序索引很重要)。

--給出部分欄位
CREATE TABLE [dbo].[Housetest](    [ID] [int] IDENTITY(1,1) NOT NULL,    [Title] [varchar](200) NULL,    [Description] [nvarchar](max) NOT NULL,    [IsOnline] [tinyint] NOT NULL, CONSTRAINT [PK_Housetest] PRIMARY KEY CLUSTERED (    [ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

建立索引

CREATE INDEX IX_Housetest ON Housetest(Title DESC)INCLUDE(ID,Description,IsOnline)

查詢Description包含“美園”，並且如果Title是“美園”則排在第一位，並且以分頁的形式顯示，每頁20條記錄。

select  * from (SELECT  ROW_NUMBER() OVER(Order by (case when Title=‘美園‘ Then 1 Else 0 End) desc ) as RowsNumber,ID,Title,Description  From Housetest  Where  contains(Description,‘美園‘) and IsOnline=1) tab1  where RowsNumber between 1 and 20

 

總結

大家不要拿這個與搜尋引擎做對比，肯定是沒法比的，因為我這裡只需要解決需求就好，所以方案適合我目前的需求。 

全文索引功能類似於百度的搜尋引擎，但是百度這類搜尋引擎有自己的資料字典，在關鍵字表中對關鍵字進行排序，儲存關鍵字對應的 文檔id,一個文檔只會保留很少的關鍵字，就跟平時寫文章要添加標籤一樣，一般一篇文章就幾個標籤，當搜尋的時候匹配的速度就會非常快，這就需要一個很完善的資料字典表。

全文檢索搜尋還有另外的一個功能就是FileStream，需要添加檔案流，在服務中啟用該功能可以在欄位中將文檔以二進位的形式儲存在欄位當中，這樣大型文檔也可以隨資料庫一起備份，很多網站儲存圖片都是儲存圖片的路徑，這樣備份資料庫的時候圖片不會一起備份。

全文索引帶來好處的同時也會對效能有一定的影響，特別是在進行篩選操作的時候對伺服器效能會帶來影響，所以選擇一個功能的同時需要考慮對效能帶來的影響。

 

 如果文章對大家有協助，幫忙點推薦，謝謝！！！

 

備忘：     pursuer.chen     部落格：http://www.cnblogs.com/chenmh 本網站所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。 《歡迎交流討論》

 

SQL Server 使用全文索引進行頁面搜尋