什么时候应该使用全文索引？

我们有一大堆查询"搜索"客户,客户等.您可以按名字,电子邮件等进行搜索.我们以下列方式使用LIKE语句:

SELECT * 
FROM customer 
WHERE fname LIKE '%someName%'

全文索引是否有助于该场景？我们正在使用SQL Server 2005.

这取决于您的DBMS.我相信大多数系统都不会利用全文索引,除非你使用全文函数.(例如mySQL中的MATCH/AGAINST或MS SQL中的FREETEXT/CONTAINS)

这是一篇关于何时,为什么以及如何在SQL Server中使用全文索引的好文章:了解SQL Server全文索引

FTS 可以在这种情况下提供帮助,问题在于它是否值得.

首先,让我们看看为什么LIKE可能不是最有效的搜索.当您使用时LIKE,尤其是在%比较开始时使用a进行搜索时,SQL Server需要对每一行执行表扫描,并对要检查的列执行逐字节检查.

FTS有一些更好的匹配数据的算法,以及一些关于名称变化的更好的统计数据.因此,当你寻找史密斯时,FTS可以为匹配史密斯,史密斯,史密瑟斯等提供更好的性能.

然而,使用FTS会有点复杂,因为你需要掌握CONTAINSvs FREETEXT和搜索的神秘格式.但是,如果要在FName或LName匹配的情况下进行搜索,则可以使用一个语句而不是OR来执行此操作.

要确定FTS是否有效,请确定您拥有的数据量.我在数亿行的数据库上使用FTS,这比使用搜索LIKE更有利,但我并没有在每个表上使用它.

如果您的表大小更合理,少于几百万,您可以通过为要搜索的每个列创建索引来获得类似的速度,SQL Server应该执行索引扫描而不是表扫描.

根据我的测试场景:

SQL Server 2008

10.000.000行,每行包含一个字符串,如"wordA wordB wordC ..."(在1到30个字之间变化)

用CONTAINS(列,"wordB")选择计数(*)

结果大小数十万

目录大小约1.8GB

全文索引的范围为2s,而'%wordB%'的范围为1-2分钟.

但只有当您不使用任何其他选择标准时,这才算重要!例如,如果我在主键列上另外使用了一些"喜欢'前缀%'",那么性能会更差,因为进入全文索引的操作比在某些字段中进行字符串搜索要花费更多(只要不是太多了).

所以我建议只在你需要进行"免费字符串搜索"或使用它的一些特殊功能的情况下才能推荐全文索引...