使用fulltext解决Like完全模糊匹配的性能问题

背景

当我们不得不对超宽字段(不适合建立索引的字段)Search时，而又特别是使用Like进行完全模糊查询的时候(无法使用到Index)，我们有另外一种加快检索效率的方法，那就是全文索引(Fulltext)。以下是来自于帮助文档：

全文引擎需要使用此唯一索引将表中的每一行映射到一个唯一的可压缩键。 全文索引可以包括 char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary 和 varbinary(max) 列。

场景

我们有一个ItemInfo表，存放产品信息，产品信息中有一个Item_desc(varchar(4000))字段是商品的描述信息，如果我们想去检索含有特定描述信息商品的时候，如何加快检索速度？

Demo

直接上测试的脚本

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.ItemInfo','u') is not null

    drop table dbo.ItemInfo

go

create table dbo.ItemInfo

(

    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.ItemInfo(item_number);

--fulltext catelog creating

CREATE FULLTEXT CATALOG ftxt_ItemInfo AS DEFAULT;

--create fulltext index

CREATE FULLTEXT INDEX ON dbo.ItemInfo(item_desc)

   KEY INDEX uix_item_number

   ON ftxt_ItemInfo;

GO

--testing table data init.

declare

    @do int

    ,@loop int

    ,@item_desc varchar(4000)

;

select

    @do = 1

    ,@loop = 10000

    ,@item_desc = ''

;

while @do <= @loop

begin

    set

       @item_desc = REPLICATE(newid(),110)

    ;

    insert into dbo.ItemInfo

    select CHECKSUM(@item_desc),@item_desc;

    set @do = @do + 1;

end;

go

--try to create index on item_desc:error occurs when data inited

create index ix_item_desc ON dbo.ItemInfo(item_desc);

/*error info when create index on big data column

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 3960 bytes for the index 'ix_item_desc' exceeds the maximum length of 900 bytes.

The statement has been terminated.

*/

GO

--===testing performance bewteen like & fulltext

declare

    @item_desc varchar(10)

;

select TOP 1 @item_desc = item_desc

FROM dbo.ItemInfo

WHERE id = 2012;

set statistics time on

set statistics io on

select *

from dbo.ItemInfo

where item_desc like '%'+@item_desc+'%'

/*performance statistics of like

 SQL Server Execution Times:

   CPU time = 1264 ms,  elapsed time = 1299 ms.

*/

SELECT *

FROM  dbo.ItemInfo

where contains(item_desc,@item_desc)

/*performance statistics of fulltext

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

*/

set statistics time off

set statistics io off

GO

if OBJECT_ID('dbo.ItemInfo','u') is not null

    drop table dbo.ItemInfo

go

说明

1.        从测试的结果来看，使用Like查询和使用Fulltext查询性能天壤之别，fulltext的性能比Like高太多了，可以参见上面的注释部分统计信息

2.        这里顺便提一下经常被问到的问题：like完全模糊匹配如何使用变量的问题：where item_desc like '%'+@item_desc+'%'

3.        当表中字段最大的长度超过900byte时，该字段上建立Index会报错；当表中长度没有超过该值时，创建Index仅有警告