（7.2.4）SQL_Server_全文索引查询

SQL Server 全文索引查询T-SQL学习笔记之一(Full-text index)

2009-12-11 11:29

引言

　　这段时间为了提高海量字符串数据的查询效率，我对字段添加了全文索引。首先全文索引相对于传统的索引是有区别的，这是因为传统的索引主要是以首字母开始建立的索引，处理like 'keword%'这样的查询会很高效，但是如果查询时不限定首字母，而只是包含某个词，比如like '%keyword%'这样的查询，实际操作中无法使用传统索引加速查询效率，而只能一项一项比较了。

　　而全文索引正是提供了“包含”式查询机制，查询一个长字符串中是否包含给定关键词的功能，这无论是在搜索引擎或是网站的搜索平台都是很有用处的。

　　首先，推荐一本学习SQL Server全文索引的书籍，这本书详细的讲解了全文索引的方方面面，甚至还阐述许多设计搜索引擎的思想和方法。书名是《Pro Full-Text Search in SQL Server 2008》，是Apress出版的。

 

　　这本书的内容是按章划分的，同时由浅入深，从一般的技巧到高级的技巧。我这里就简单分享一下基本的全文查询方法，更多高级的技巧应该在实际应用中按需进行学习。

　　要实现全文查询，首先安装的SQL Server实例要支持全文查询服务，可以查看windows服务是否有全文索引服务。如果没有，则要重新安装SQL Server并选择添加功能，将Full-Text功能选中，然后再安装或升级。

 

　　
　　有了全文查询服务，还不能直接进行查询，需要先在想要建立全文索引的字段上建立一个全文索引。方法是打开企业管理器，选择字段所在表格，然后点击右键，选择"Full-text inde”，然后选择"define Full-text index"就能进入设置面板。

 

　　需要注意的是，全文索引只能建立在Unique（唯一）字段上，并且每个表最多只能有一个全文索引字段，因此要慎重。然后按照提示建立全文索引即可（可以参见我推荐的那本书，会有一步一步操作的详细说明和注释）

　　建立好全文索引后，就能够运用T-SQL的全文查询语法进行全文查询了。

　　主要有两个语法，一个是contains，另一个是freetext

Contains

　　contains从字面上就能很好理解，即包含，比如我们在表Sample的一个字段Column里查询包含“世界末日”这个关键词的所有记录，该表一共有60万条记录，传统的查询语法是

select * from Sample where [Column] like '%世界末日%'

　　我们修改一下查询语言，并记录查询时间，如下

declare @d datetime

set @d=getdate()

select [Column] as [result] from Sample where [Column] like '%世界末日%'

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

　　执行结果如下：

 

下面，我们使用全文查询语法contains，如下

select [Column] as [result] from Sample where contains([Column],'世界末日')

　　执行结果如下

 

　　我们可以看出，全文索引极大的改变了查询效率，传统查询花费了503毫秒（半秒），而全文索引却不足一毫秒，效率提高了上千倍。由于表记录只有60万，如果记录数更多，几千万甚至上亿，那么全文索引必然会带来更大的效率优势。

　　当然，效率是需要空间来换取的，全文索引需要开销大量的磁盘空间，所以有时候索引比原数据还大也是正常现象

Freetext

　　freetext在搜索引擎方面具有大量应用，因为它提供了类似于“模糊查询”的机制，他的查询机制是只要含有查询关键词中的某个字符或词语，那么就选中该记录，因此它返回的结果会很多很多，往往我们需要用top语法来限定结果数量。还是查询“世界末日”，这个时候它会返回包含“世”或“界”或“末”或“日”的所有词语，因此结果集往往很大。它的查询语法如下：

select [Column] as [result] from Sample where freetext([Column],'世界末日') 

　　执行结果如下，一共返回了1820条记录

 

　　其实freetext语法和contains语法是一样的，只是执行的方式不一样罢了，contains是精确限定，而freetext是模糊限定，只要出现了关键词中的字，就被选中。freetext的好处就是提供模糊查询，往往用户给出的关键词我们库里没有，那么用contains将无法返回结果，而使用freetext就能返回相关结果，不过由于freetext是即挑即选，没有对结果关于关键词的匹配程度来排序，所以也是一个缺陷，但是我们可以通过SQL Server全文索引中的Top_k_by_rank方法来对结果进行排序，这个时候要用到更为高级的全文索引查询语法containstable和freetexttable，这将在学习笔记二中介绍。

 

 

SQL Server 全文索引查询T-SQL学习笔记之二(Full-text index)

2009-12-12 15:10

　　在学习笔记一里已经掌握了基本的contains语法和freetext语法的用法，但是面对一些复杂的操作，基本的包含语法是不够用的，如果我们想要查询含有“世界”或“末日”的所有字符串集合，那么无论是contains([column],'世界末日')或者freetext([column],'世界末日')都不能很好的工作，当然contains本身是可以含有条件的，因此有两个解决方案。

多条件查询

　　第一个就是传统的where多条件查询，加上两个contains语句，然后用or连接

select [Column] as [result] from Sample where contains([Column],'世界') or contains([Column],'末日')

　　执行结果如下：

 

　　现在，介绍如何直接使用一个contains语句实现多条件查询，语法的结构如下

contains([Column],'"keyword1" and "keyword2" and ......')

contains([Column],'"keyword1" or "keyword2" or ......')

contains([Column],'("keyword1" or "keyword2") and ......')

　　其实也就是在两个单引号内实现多条件，and表示交集，or表示并集，我们运行如下T-SQL

select [Column] as [result] from Sample where contains([Column],'"世界" or "末日"')

　　执行结果如下：

 

　　两次查询均返回了1820条记录，可以看到，排在前面的记录两者是不一样的，因此这两种方式的查询在底层的执行方式是不一样的，往往把条件都放在一个contains里会有更高的效率。

　　要注意的是，freetext本身就是模糊查询了，它不能再带有条件，如果想尝试在freetext里加入条件语句是没有意义的，不会返回任何结果。

　　现在总结一下contains和freetext，可以看到，使用这两个查询方法很简单，效率较高，但是它们一个很大的不足：

contains和freetext不会限定返回结果的数量，而是将满足条件的全部返回

　　这会带来两个比较重要的问题，

１、我们会得到很多无用的结果，同时如果返回结果过多，也会极大影响查询效率

２、返回的结果是无序的，并没有按照预想的如“相似程度”进行排序，导致最好的查询结果往往不再最前面

　　为了克服这样的问题，就可以使用containstable和freetexttable语法，这两个查询方法可以限定返回结果的数量，同时能赋予一个rank函数（相似度函数）返回rank最大的n个结果，这就是著名的top_n_by_rank argument

containstable、freetexttable

　　使用containstable和top_n_by_rank需要使用表的内连接操作，内连接也称为等同连接，返回的结果集是两个表中所有相匹配的数据，用on进行连接。我们让containstable返回的结果集作为一个表k，该表拥有两个字段，一个是key字段，一个是rank字段，在查询时需要将key字段与查询字段中的一个主键（唯一字段）相进行连接，如下

SELECT [Column] as [result] From wiki --查询Column字段的记录，该字段是唯一字段，并且已建立全文索引

inner join --内连接

containstable(Sample,[Column],'"世界" or "末日"',500) as k --含有“世界”和“末日”的前500条记录作为表k

on wiki.fs_wiki_title = k.[key] --连接条件

ORDER BY k.RANK DESC --按照k的rank降序排列，即相似度越高的越靠前

　　执行结果如下：

 

　　可以看到，现在的查询时间已经大大减少，因为我们只返回500条记录，并且这些记录都是与给定的“世界”或“末日”非常接近的。

　　下面，我们可以进行一些更加高级的查询，比如在containstable语法里限定条件，这个时候我们要在库里查含有“世界”并且含有“末日”的所有字符串，执行如下T-SQL语句

SELECT [Column] as [result] From Sample

inner join containstable(Sample,[Column],'"世界" and "末日"',500) as k 

on Sample.Column = k.[key]

ORDER BY k.RANK DESC

　　执行结果如下：

 

继续，我们再限定查找字符串长度小于等于6，执行如下T-SQL语句

SELECT [Column] as [result] From Sample

inner join containstable(Sample,[Column],'"世界" and "末日"',500) as k 

on Sample.Column = k.[key]

where len([Column])<=6

ORDER BY k.RANK DESC

　　执行结果如下：

 

　　可以看到，这个时候返回结果就很精确了，因此我们通过containstable能够非常灵活的进行查询设计并且对返回结果按相似度排序。freetexttable的用法和containstable用法类似，因此这里不再讲解。