SQL Server FullText解决Like字句性能问题

场景引入

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

SQL Server利用HashKey计算列解决宽字段查询的性能问题

问题分析

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

解决方法

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

创建测试对象

USE master
GO
IF DB_ID('test') IS NULL
    CREATE DATABASE Test;
GO

use test
go

--in order to check fulltext enable or not.
SELECT is_fulltext_enabled,*
FROM sys.databases
WHERE name = DB_NAME()
;

IF OBJECT_ID('dbo.Item','u') IS NOT NULL
BEGIN
    TRUNCATE TABLE dbo.Item
    DROP TABLE dbo.Item
END
GO

CREATE TABLE dbo.Item
(
id int identity(1,1) primary key
,item_number int not null
,item_desc varchar(4000) null
);
GO

--fulltext unique index creating
CREATE UNIQUE INDEX uix_item_number ON dbo.Item(item_number);

--fulltext catelog creating
IF 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 ON
WHILE @do <= @loop
BEGIN
    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 inited
CREATE 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 1
Operation 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 & fulltext
declare
    @item_desc varchar(10)
;

SELECT TOP 1 @item_desc = LEFT(item_desc, 10)
FROM dbo.Item WITH(NOLOCK) 
WHERE id = 2012;

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON

SELECT *
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 OFF
SET STATISTICS IO OFF
SET STATISTICS PROFILE OFF
GO

执行计划对比

非常有意思了，执行计划对比来看，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语句的性能问题吧。