- --创建测试表
- --DROPTABLEFullTextIndexing
- CREATETABLEFullTextIndexing
- (
- IDINTIDENTITY(1,1)NOTNULL,
- SentenceVARCHAR(MAX)
- )
- --创建聚集索引
- ALTERTABLEFullTextIndexingADDCONSTRAINTPK_FullTextIndexingPRIMARYKEYCLUSTERED(IDASC)
- GO
- --将"全文搜索概述"页面的文字全部拷贝到txt,然后倒入到表FullTextIndexing中
- --https://msdn.microsoft.com/zh-cn/library/ms142547(v=sql.105).aspx
- --重复15次,从47行变为154万行
- INSERTINTOFullTextIndexing(Sentence)
- SELECTSentenceFROMFullTextIndexing
- GO15
- SELECTCOUNT(*)FROMFullTextIndexing
- --创建全文目录
- --https://msdn.microsoft.com/zh-cn/LIBRARY/ms189520(v=sql.105).aspx
- CREATEFULLTEXTCATALOG[Catalog_Test]
- WITH
- ACCENT_SENSITIVITY=ON--区分重音
- ASDEFAULT--默认目录
- AUTHORIZATION[dbo];--全文目录的所有者
- GO
- --更改全文目录的属性
- --https://msdn.microsoft.com/zh-cn/LIBRARY/ms176095(v=sql.105).aspx
- ALTERFULLTEXTCATALOG[Catalog_Test]
- REBUILDWITHACCENT_SENSITIVITY=ON;--重新生成整个目录并区分重音
- --REORGANIZE;--重新组织全文目录
- --ASDEFAULT;--指定此目录为默认目录
- GO
- --从数据库中删除全文目录(先删除全文索引)
- --https://msdn.microsoft.com/zh-cn/LIBRARY/ms188403(v=sql.105).aspx
- DROPFULLTEXTCATALOG[Catalog_Test];
- GO
- --创建干扰字表
- --https://msdn.microsoft.com/zh-cn/library/cc280405(v=sql.105).aspx
- CREATEFULLTEXTSTOPLIST[Stoplist_Test]
- FROMSYSTEMSTOPLIST
- AUTHORIZATION[dbo];
- GO
- --添加删除干扰字
- --https://msdn.microsoft.com/zh-cn/library/cc280871(v=sql.105).aspx
- ALTERFULLTEXTSTOPLIST[Stoplist_Test]
- ADDN'乎'LANGUAGE2052;
- GO
- ALTERFULLTEXTSTOPLIST[Stoplist_Test]
- DROPN'乎'language2052;
- --ALLLANGUAGE'English'
- --ALL
- GO
- --从数据库中删除全文本非索引字表
- --https://msdn.microsoft.com/zh-cn/library/cc280482(v=sql.105).aspx
- DROPFULLTEXTSTOPLIST[Stoplist_Test];
- GO
- --创建全文索引
- --https://msdn.microsoft.com/zh-cn/library/ms187317(v=sql.105).aspx
- CREATEFULLTEXTINDEXON[dbo].[FullTextIndexing]
- (SentenceLANGUAGE2052)--索引列,明确列中存储的语言,方便过滤
- KEYINDEXPK_FullTextIndexing--全文键:当前表中唯一索引名称
- ON[Catalog_Test]--指定全文目录
- WITH(
- STOPLIST[Stoplist_Test],--指定全文非索引字表
- CHANGE_TRACKINGAUTO--自动填充
- );
- GO
- --更改全文索引的属性
- --https://msdn.microsoft.com/zh-cn/library/ms188359(v=sql.105).aspx
- --激活全文索引
- ALTERFULLTEXTINDEXON[dbo].[FullTextIndexing]ENABLE;
- GO
- --删除全文索引
- --https://msdn.microsoft.com/zh-cn/library/ms184393(v=sql.105).aspx
- DROPFULLTEXTINDEXON[dbo].[FullTextIndexing];
- GO
- --测试常规查询方法(先查询全部数据,放到内存:154万行31秒)
- SELECT*FROMFullTextIndexing
- SETSTATISTICSIOON
- SETSTATISTICSTIMEON
- SELECT*FROMFullTextIndexingWHERESentenceLIKE'%全文索引%'
- /*执行了几遍,耗时13440毫秒
- SQLServer分析和编译时间:
- CPU时间=0毫秒,占用时间=0毫秒。
- (229376行受影响)
- 表'FullTextIndexing'。扫描计数1,逻辑读取15633次,物理读取0次,预读0次,lob逻辑读取0次,lob物理读取0次,lob预读0次。
- SQLServer执行时间:
- CPU时间=11591毫秒,占用时间=13440毫秒。
- */
- SELECT*FROMFullTextIndexingWHERECHARINDEX('全文索引',Sentence)<>0
- /*执行了几遍,耗时15338毫秒
- SQLServer分析和编译时间:
- CPU时间=0毫秒,占用时间=0毫秒。
- (229376行受影响)
- 表'FullTextIndexing'。扫描计数1,逻辑读取15633次,物理读取0次,预读0次,lob逻辑读取0次,lob物理读取0次,lob预读0次。
- SQLServer执行时间:
- CPU时间=12714毫秒,占用时间=15338毫秒。
- */
- --使用全文索引的方法:
- SELECT*FROM[dbo].[FullTextIndexing]WHEREFREETEXT(Sentence,'全文索引');
- /*执行了几遍,耗时17402毫秒
- SQLServer分析和编译时间:
- CPU时间=16毫秒,占用时间=21毫秒。
- (851968行受影响)
- 表'FullTextIndexing'。扫描计数1,逻辑读取15633次,物理读取0次,预读0次,lob逻辑读取0次,lob物理读取0次,lob预读0次。
- SQLServer执行时间:
- CPU时间=2230毫秒,占用时间=17402毫秒。
- */
- --重新生成全文目录!~再执行(更多方法有待参考:使用全文搜索查询SQLServer)
- --https://msdn.microsoft.com/zh-cn/library/ms142559(v=sql.105).aspx
- ALTERFULLTEXTCATALOG[Catalog_Test]REBUILD;
- GO
- SELECT*FROM[dbo].[FullTextIndexing]WHEREFREETEXT(Sentence,'全文索引');
- SELECT*FROM[dbo].[FullTextIndexing]WHERECONTAINS(Sentence,'全文索引');
- SELECT*FROM[dbo].[FullTextIndexing]WHERECONTAINS(Sentence,'全文AND索引');
- /*这时快多了!~不到1秒就查询完成!~但是返回的行数才1000多行
- SQLServer分析和编译时间:
- CPU时间=0毫秒,占用时间=0毫秒。
- (8853行受影响)
- 表'FullTextIndexing'。扫描计数0,逻辑读取27121次,物理读取0次,预读0次,lob逻辑读取0次,lob物理读取0次,lob预读0次。
- SQLServer执行时间:
- CPU时间=78毫秒,占用时间=944毫秒。
- */
- SETSTATISTICSIOOFF
- SETSTATISTICSTIMEOFF
- 不断地执行就会找出规律:
- 表扫描次数为0。
- 逻辑读也越来越多,耗时越来越多,查询返回的行数也越来越多,性能越来越差!~比"like"还差
- 最后还得重建重组(REBUILD/REORGANIZE)全文索引目录
- 逻辑读取27121次
- 逻辑读取945268次
- 逻辑读取1212885次
- 逻辑读取1407846次
- 逻辑读取1736686次
- 逻辑读取1953265次
- --查询句词拆分结果.可以看到按什么词语进行匹配查询
- select*fromsys.dm_fts_parser('全文索引',2052,5,0)
- --如果只需要全文键或排名的信息,可使用表值函数
- --使用表值函数的方法可以使用联接提示或查询提示(LOOP/MERGE/HASH)
- ALTERFULLTEXTCATALOG[Catalog_Test]REBUILD;
- GO
- ALTERFULLTEXTCATALOG[Catalog_Test]REORGANIZE;
- GO
- SELECT*FROM[dbo].[FullTextIndexing]t1
- INNERJOINCONTAINSTABLE([FullTextIndexing],Sentence,'概述')ASt2
- ONt1.ID=t2.[KEY]
- GO
- SELECT*FROM[dbo].[FullTextIndexing]t1
- INNERJOINFREETEXTTABLE([FullTextIndexing],Sentence,'概述',LANGUAGE2052,1000)ASt2
- ONt1.ID=t2.[KEY]
- ORDERBYt2.RANKDESC;
- GO
- --相关视图:
- select*fromsys.syslanguages
- select*fromsys.fulltext_indexes
- select*fromsys.fulltext_catalogswherename='Catalog_Test'
- select*fromsys.dm_fts_active_catalogswherename='Catalog_Test'
- select*fromsys.fulltext_stoplistswherename='Stoplist_Test'
- select*fromsys.fulltext_stopwordswherestoplist_id=5--andlanguage_id=2052
- select*fromsys.dm_fts_parser('全文索引',2052,5,0)
SQLSERVER 建立全文检索
免责声明:文章转载自《SQLSERVER 建立全文检索》仅用于学习参考。如对内容有疑问,请及时联系本站处理。
上篇MySQL主从复制什么原因会造成不一致,如何预防及解决?webpackJsonp is not defined?下篇
宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=