-
- CREATE 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
-
-
-
- INSERT INTO FullTextIndexing(Sentence)
- SELECT Sentence FROM FullTextIndexing
- GO 15
-
-
- SELECT COUNT(*) FROM FullTextIndexing
- CREATE FULLTEXT CATALOG [Catalog_Test]
- WITH
- ACCENT_SENSITIVITY = ON
- AS DEFAULT
- AUTHORIZATION [dbo];
- GO
-
-
- ALTER FULLTEXT CATALOG [Catalog_Test]
- REBUILD WITH ACCENT_SENSITIVITY = ON;
- GO
-
- DROP FULLTEXT CATALOG [Catalog_Test];
- GO
-
-
- CREATE FULLTEXT STOPLIST [Stoplist_Test]
- FROM SYSTEM STOPLIST
- AUTHORIZATION [dbo];
- GO
-
- ALTER FULLTEXT STOPLIST [Stoplist_Test]
- ADD N'乎' LANGUAGE 2052;
- GO
-
- ALTER FULLTEXT STOPLIST [Stoplist_Test]
- DROP N'乎' language 2052;
- GO
-
- DROP FULLTEXT STOPLIST [Stoplist_Test];
- GO
-
-
- CREATE FULLTEXT INDEX ON [dbo].[FullTextIndexing]
- (Sentence LANGUAGE 2052)
- KEY INDEX PK_FullTextIndexing
- ON [Catalog_Test]
- WITH (
- STOPLIST [Stoplist_Test],
- CHANGE_TRACKING AUTO
- );
- GO
-
- ALTER FULLTEXT INDEX ON [dbo].[FullTextIndexing] ENABLE;
- GO
-
- DROP FULLTEXT INDEX ON [dbo].[FullTextIndexing];
- GO
- SELECT * FROM FullTextIndexing
-
- SET STATISTICS IO ON
- SET STATISTICS TIME ON
-
- SELECT * 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 毫秒。
- */
![](//img-blog.csdn.net/20150413092035178?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQva2sxODU4MDA5NjE=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)
- 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 毫秒。
- */
![](//img-blog.csdn.net/20150413092101620?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQva2sxODU4MDA5NjE=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)
- ALTER FULLTEXT CATALOG [Catalog_Test] REBUILD;
- GO
- SELECT * 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 OFF
- SET STATISTICS TIME OFF
- 不断地执行就会找出规律:
- 表扫描次数为0。
- 逻辑读也越来越多,耗时越来越多,查询返回的行数也越来越多,性能越来越差!~比"like"还差
- 最后还得重建重组(REBUILD/REORGANIZE)全文索引目录
-
- 逻辑读取 27121 次
- 逻辑读取 945268 次
- 逻辑读取 1212885 次
- 逻辑读取 1407846 次
- 逻辑读取 1736686 次
- 逻辑读取 1953265 次
- select * from sys.dm_fts_parser('全文索引',2052,5,0)
-
-
- ALTER FULLTEXT CATALOG [Catalog_Test] REBUILD;
- GO
- ALTER FULLTEXT CATALOG [Catalog_Test] REORGANIZE;
- GO
-
- SELECT * FROM [dbo].[FullTextIndexing] t1
- INNER JOIN CONTAINSTABLE([FullTextIndexing],Sentence,'概述') AS t2
- ON t1.ID = t2.[KEY]
- GO
-
- SELECT * FROM [dbo].[FullTextIndexing] t1
- INNER JOIN FREETEXTTABLE([FullTextIndexing],Sentence,'概述',LANGUAGE 2052,1000) AS t2
- ON t1.ID = t2.[KEY]
- ORDER BY t2.RANK DESC;
- GO
- select * from sys.syslanguages
- select * from sys.fulltext_indexes
- select * 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
- select * from sys.dm_fts_parser('全文索引',2052,5,0)
posted @
2017-04-17 09:47
雨姗
阅读(
742)
评论()
编辑
收藏
举报