场景引入

这天老鸟火急火燎的冲到菜鸟座位：“还记得你在云栖社区发表的一篇名为‘SQL Server利用HashKey计算列解决宽字段查询的性能问题’的文章吗？被人踢馆啦，人家觉得你这个限制条件太苛刻，只能解决完全等于的问题条件下的性能问题，没有太大的现实意义。”

问题分析

菜鸟反思着，的确，需要完全匹配这个条件限制太严格了，SQL Server有没有一种方法来代替LIKE字句的功能而又可以大大提高查询效率的呢？因为，我们知道，LIKE左模糊匹配是可以使用到索引，而右模糊和完全模糊匹配是完全无法使用到索引的。G哥告诉菜鸟有解决方法，用FullText搜索啊。据说阿里云RDS SQL Server 2008和ECS 版RDS SQL 2012都支持SQL Server的FullText哦，对于使用阿里云RDS SQL Server的用户真是个好消息。

解决方法

来看我们的一个简单测试。

创建测试对象

USE masterGOIF DB_ID('test') IS NULL    CREATE DATABASE Test;GOuse testgo--in order to check fulltext enable or not.SELECT is_fulltext_enabled,*FROM sys.databasesWHERE name = DB_NAME();IF OBJECT_ID('dbo.Item','u') IS NOT NULLBEGIN    TRUNCATE TABLE dbo.Item    DROP TABLE dbo.ItemENDGOCREATE TABLE dbo.Item(id int identity(1,1) primary key,item_number int not null,item_desc varchar(4000) null);GO--fulltext unique index creatingCREATE UNIQUE INDEX uix_item_number ON dbo.Item(item_number);--fulltext catelog creatingIF NOT EXISTS(        SELECT * FROM sys.fulltext_catalogs WITH(NOLOCK)        WHERE name = N'ftxt_Item')BEGIN    CREATE FULLTEXT CATALOG ftxt_Item AS DEFAULT;END;--create fulltext index CREATE FULLTEXT INDEX ON dbo.Item(item_desc) KEY INDEX uix_item_number ON ftxt_Item;GO

初始化数据

--testing table data init.DECLARE    @do int    ,@loop int    ,@item_desc varchar(4000);SELECT    @do = 1    ,@loop = 100000    ,@item_desc = '';SET NOCOUNT ONWHILE @do <= @loopBEGIN    set         @item_desc = REPLICATE(newid(),112)    ;    INSERT INTO dbo.Item    SELECT CHECKSUM(@item_desc),@item_desc;    SET @do = @do + 1;END;GO

创建索引失败

想要在超过900byte宽度的字段上创建索引，门都没有，SQL Server直接报错。

--try to create index on item_desc:error occurs when data initedCREATE INDEX ix_item_desc ON dbo.Item(item_desc);

错误信息

Warning! The maximum key length is 900 bytes. The index 'ix_item_desc' has maximum length of 4000 bytes. For some combination of large values, the insert/update operation will fail.Msg 1946, Level 16, State 3, Line 1Operation failed. The index entry of length 4000 bytes for the index 'ix_item_desc' exceeds the maximum length of 900 bytes.The statement has been terminated.

LIKE查询和Fulltext查询

--===testing performance bewteen like & fulltextdeclare    @item_desc varchar(10);SELECT TOP 1 @item_desc = LEFT(item_desc, 10)FROM dbo.Item WITH(NOLOCK) WHERE id = 2012;SET STATISTICS PROFILE ONSET STATISTICS TIME ONSET STATISTICS IO ONSELECT *FROM dbo.Item WITH(NOLOCK)WHERE item_desc like @item_desc+'%'SELECT *FROM dbo.Item WITH(NOLOCK)WHERE item_desc like '%'+@item_desc+'%'SELECT * FROM dbo.Item WITH(NOLOCK)WHERE CONTAINS(item_desc,@item_desc)SET STATISTICS TIME OFFSET STATISTICS IO OFFSET STATISTICS PROFILE OFFGO

执行计划对比

非常有意思了，执行计划对比来看，Like左匹配和完全模糊匹配均走是Clustered Index Scan，相当于Table Scan；而EstimateRows显示满足条件约为12440行，也暴露出SQL Server执行计划对Like字句的评估并不准确，实际满足条件只有1行而已，所以导致最后的查询效率不高。但是，FullText的执行计划中EstimateRows显示只有1行，与实际情况相符合。

执行效率对比

对比完执行计划，让我们来看看最后执行效率对比：

• Like左模糊匹配执行效率: Logical Reads 50185，说明IO读非常高，这是Clustered Index Scan导致的结果；CPU消耗47 ms，总的执行时间51ms。
• Like完全模糊匹配执行效率：和Like左模糊匹配一样，Logical Reads 50185，也是IO读非常高; CPU 更加离谱达到了4789ms，执行时间4919ms。
• FullText查询执行效率：Logical Reads仅仅只有5，所以最后CPU消耗为0ms，执行时间50ms。

从这个对比结果来看，FullText完胜LIKE字句，IO读取大大降低，CPU消耗减少，效率得到了很大的提升。

#### Like左模糊匹配执行效率Table 'Item'. Scan count 1, logical reads 50185, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 47 ms,  elapsed time = 51 ms.#### Like完全模糊匹配执行效率Table 'Item'. Scan count 1, logical reads 50185, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 4789 ms,  elapsed time = 4919 ms.#### FullText查询执行效率Informational: The full-text search condition contained noise word(s).Table 'Item'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 50 ms.

根据上面的数字，做出一个炫酷无比的图表来，直观的对比

写在最后

完成上面的测试，菜鸟有了对老鸟和“踢馆者”合理的交代，那就是使用FullText来解决Like语句的性能问题吧。