SqlServer 全文索引创建及测试
来源:互联网 发布:模拟开关矩阵 编辑:程序博客网 时间:2024/04/20 00:22
--创建测试表--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)
0 0
- SqlServer 全文索引创建及测试
- SqlServer 创建全文索引
- SqlServer全文索引:sp_fulltext_table
- sqlServer全文索引
- SQLServer全文索引
- sqlserver 全文索引
- SQLSERVER全文索引
- 手动创建索引及全文检索
- 索引的创建及使用(sqlserver 2000)
- 索引的创建及使用(sqlserver 2000)
- 索引的创建及使用(sqlserver 2005)
- 索引的创建及使用(sqlserver 2000)
- 索引的创建及使用(sqlserver 2000)
- 索引的创建及使用(sqlserver 2000)
- SQLSERVER数据库全文索引示例
- 使用sqlserver中的全文索引
- SqlServer 2005 全文索引 实例
- sqlserver打开全文索引方法
- 使用GRUB2制作U盘PE启动盘
- thinkphp之视图总结
- C# 中的委托和事件
- 认知
- 如何讲解自己开发的程序
- SqlServer 全文索引创建及测试
- RWTHLM 源码分析(一)之词库组织
- iOS开发之Objective-c的MD5/SHA1加密算法的实现
- JS中如何输出空格
- IOS学习之蓝牙4.0
- Go 中的一些问题
- MINA 实现聊天功能
- 手机测试点--总结
- fontcreator制作iconfont