场景引入
这天老鸟火急火燎的冲到菜鸟座位：“还记得你在云栖社区发表的一篇名为‘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
AI 代码解读
初始化数据
--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
AI 代码解读
创建索引失败
想要在超过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);
AI 代码解读
错误信息
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.
AI 代码解读
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
AI 代码解读
执行计划对比
非常有意思了，执行计划对比来看，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.
AI 代码解读
写在最后
完成上面的测试，菜鸟有了对老鸟和“踢馆者”合理的交代，那就是使用FullText来解决Like语句的性能问题吧。
