SqlServer 全文索引建立及測試，sqlserver全文索引

SqlServer 全文索引建立及測試，sqlserver全文索引

--建立測試表--DROP TABLE FullTextIndexingCREATE TABLE FullTextIndexing(ID INT IDENTITY(1,1) NOT NULL,Sentence VARCHAR(MAX))--建立叢集索引ALTER TABLE FullTextIndexing ADD CONSTRAINT PK_FullTextIndexing PRIMARY KEY CLUSTERED(ID ASC)GO--將"全文檢索搜尋概述"頁面的文字全部拷貝到txt,然後倒入到表FullTextIndexing中--https://msdn.microsoft.com/zh-cn/library/ms142547(v=sql.105).aspx--重複15次,從47行變為154萬行INSERT INTO FullTextIndexing(Sentence)SELECT Sentence FROM FullTextIndexingGO 15SELECT COUNT(*) FROM FullTextIndexing

--【注】刪除的語句不要執行！

--建立全文檢索目錄--https://msdn.microsoft.com/zh-cn/LIBRARY/ms189520(v=sql.105).aspxCREATE FULLTEXT CATALOG [Catalog_Test]WITHACCENT_SENSITIVITY = ON--區分重音AS DEFAULT--預設目錄AUTHORIZATION [dbo];--全文檢索目錄的所有者GO--更改全文檢索目錄的屬性--https://msdn.microsoft.com/zh-cn/LIBRARY/ms176095(v=sql.105).aspxALTER FULLTEXT CATALOG [Catalog_Test] REBUILD WITH ACCENT_SENSITIVITY = ON;--重建整個目錄並區分重音--REORGANIZE;--重新組織全文檢索目錄--AS DEFAULT;--指定此目錄為預設目錄GO--從資料庫中刪除全文檢索目錄(先刪除全文索引)--https://msdn.microsoft.com/zh-cn/LIBRARY/ms188403(v=sql.105).aspxDROP FULLTEXT CATALOG [Catalog_Test];GO--建立幹擾字表--https://msdn.microsoft.com/zh-cn/library/cc280405(v=sql.105).aspxCREATE FULLTEXT STOPLIST [Stoplist_Test] FROM SYSTEM STOPLIST AUTHORIZATION [dbo];GO  --添加刪除幹擾字--https://msdn.microsoft.com/zh-cn/library/cc280871(v=sql.105).aspxALTER FULLTEXT STOPLIST [Stoplist_Test]ADD N'乎' LANGUAGE 2052;GO ALTER FULLTEXT STOPLIST [Stoplist_Test]DROP N'乎' language 2052;   --ALL LANGUAGE 'English' --ALLGO --從資料庫中刪除全文本停用字詞表--https://msdn.microsoft.com/zh-cn/library/cc280482(v=sql.105).aspxDROP FULLTEXT STOPLIST [Stoplist_Test];GO --建立全文索引--https://msdn.microsoft.com/zh-cn/library/ms187317(v=sql.105).aspxCREATE FULLTEXT INDEX ON [dbo].[FullTextIndexing](Sentence LANGUAGE 2052)--索引列,明確列中儲存的語言,方便過濾KEY INDEX PK_FullTextIndexing--全文鍵：當前表中唯一索引名稱ON [Catalog_Test]--指定全文檢索目錄WITH (STOPLIST [Stoplist_Test],--指定全文停用字詞表CHANGE_TRACKING AUTO--自動填滿);GO--更改全文索引的屬性--https://msdn.microsoft.com/zh-cn/library/ms188359(v=sql.105).aspx--啟用全文索引ALTER FULLTEXT INDEX ON [dbo].[FullTextIndexing] ENABLE;GO--刪除全文索引--https://msdn.microsoft.com/zh-cn/library/ms184393(v=sql.105).aspxDROP FULLTEXT INDEX ON [dbo].[FullTextIndexing];GO

--測試常規查詢方法(先查詢全部資料,放到記憶體:154萬行31秒)SELECT * FROM FullTextIndexingSET STATISTICS IO ONSET STATISTICS TIME ONSELECT * FROM FullTextIndexing WHERE Sentence LIKE '%全文索引%'/*執行了幾遍,耗時13440 毫秒SQL Server 分析和編譯時間:    CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。(229376 行受影響)表 'FullTextIndexing'。掃描計數 1，邏輯讀取 15633 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 SQL Server 執行時間:   CPU 時間 = 11591 毫秒，佔用時間 = 13440 毫秒。*/SELECT * FROM FullTextIndexing WHERE CHARINDEX('全文索引',Sentence)<>0/*執行了幾遍,耗時15338 毫秒SQL Server 分析和編譯時間:    CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。(229376 行受影響)表 'FullTextIndexing'。掃描計數 1，邏輯讀取 15633 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 SQL Server 執行時間:   CPU 時間 = 12714 毫秒，佔用時間 = 15338 毫秒。*/

--使用全文索引的方法：SELECT * FROM [dbo].[FullTextIndexing] WHERE FREETEXT(Sentence,'全文索引');/*執行了幾遍,耗時17402 毫秒SQL Server 分析和編譯時間:    CPU 時間 = 16 毫秒，佔用時間 = 21 毫秒。(851968 行受影響)表 'FullTextIndexing'。掃描計數 1，邏輯讀取 15633 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 SQL Server 執行時間:   CPU 時間 = 2230 毫秒，佔用時間 = 17402 毫秒。*/

--執行了17秒,不降反而上升了!!~

--重建全文檢索目錄!~再執行(更多方法有待參考：使用全文檢索搜尋查詢 SQL Server)--https://msdn.microsoft.com/zh-cn/library/ms142559(v=sql.105).aspxALTER FULLTEXT CATALOG [Catalog_Test] REBUILD;GOSELECT * FROM [dbo].[FullTextIndexing] WHERE FREETEXT(Sentence,'全文索引');SELECT * FROM [dbo].[FullTextIndexing] WHERE CONTAINS(Sentence,'全文索引');SELECT * FROM [dbo].[FullTextIndexing] WHERE CONTAINS(Sentence,'全文 AND 索引');/*這時快多了!~不到1秒就查詢完成!~但是返回的行數才1000多行SQL Server 分析和編譯時間:    CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。(8853 行受影響)表 'FullTextIndexing'。掃描計數 0，邏輯讀取 27121 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 SQL Server 執行時間:   CPU 時間 = 78 毫秒，佔用時間 = 944 毫秒。*/SET STATISTICS IO OFFSET STATISTICS TIME OFF

不斷地執行就會找出規律:表掃描次數為0。邏輯讀也越來越多,耗時越來越多,查詢返回的行數也越來越多,效能越來越差!~比"like"還差最後還得重建重組(REBUILD/REORGANIZE)全文索引目錄邏輯讀取 27121 次邏輯讀取 945268 次邏輯讀取 1212885 次邏輯讀取 1407846 次邏輯讀取 1736686 次邏輯讀取 1953265 次

--查詢句詞拆分結果.可以看到按什麼詞語進行匹配查詢select * from sys.dm_fts_parser('全文索引',2052,5,0)--如果只需要全文鍵或排名的資訊，可使用資料表值函式--使用資料表值函式的方法可以使用聯結提示或查詢提示（LOOP/MERGE/HASH）ALTER FULLTEXT CATALOG [Catalog_Test] REBUILD;GOALTER FULLTEXT CATALOG [Catalog_Test] REORGANIZE;GOSELECT * FROM [dbo].[FullTextIndexing] t1 INNER JOIN CONTAINSTABLE([FullTextIndexing],Sentence,'概述') AS t2ON t1.ID = t2.[KEY]GOSELECT * FROM [dbo].[FullTextIndexing] t1 INNER JOIN FREETEXTTABLE([FullTextIndexing],Sentence,'概述',LANGUAGE 2052,1000) AS t2ON t1.ID = t2.[KEY]ORDER BY t2.RANK DESC;GO

--相關視圖:select * from sys.syslanguagesselect * from sys.fulltext_indexesselect * from sys.fulltext_catalogs where name = 'Catalog_Test'select * from sys.dm_fts_active_catalogs where name = 'Catalog_Test'select * from sys.fulltext_stoplists where name = 'Stoplist_Test'select * from sys.fulltext_stopwords where stoplist_id = 5 --and language_id = 2052select * from sys.dm_fts_parser('全文索引',2052,5,0)