【SQL Server性能优化】运用SQL Server的全文检索来提高模糊匹配的效率

原文: 【SQL Server性能优化】运用SQL Server的全文检索来提高模糊匹配的效率

今天去面试，这个公司的业务需要模糊查询数据，之前他们通过mongodb来存储数据，但他们说会有丢数据的问题，我从业务上了解到，显然对他们公司而言，丢数是绝对不能允许的。

另外，他们说之前也用过SQL Server的全文检索，但速度不够快，不如用mongodb快，当然我不太清楚他们所谓快的具体定义，比如查询只需要1秒，还是1分钟。他们的系统现在采用的是SQL Server，通过复制来实现高可用性，因为他们说备份数据库需要很长时间。我在想，这确实解决了可用性问题，但没解决性能问题，可以考虑分表，把大的表拆分到多个数据库，每个数据库可以通过复制来实现可用性。

我觉得他们可能更需要一个架构师，来决定采用何种技术解决他们现在的技术问题，因为这种技术问题，显然不是靠DBA的优化就能解决的；其次才是需要DBA，让DBA来管理、维护、优化系统。

当时在面试的时候，我表示虽然在博客里也写了一篇关于SQL Server全文索引的文章，但在实际工作中确实也没有用到。回去以后，我想了想，觉得这个问题还是可以通过SQL Server的全文索引来尝试一下。

引用自己之前写的一篇全文检索的文章: http://blog.csdn.net/yupeigu/article/details/7792955

上面的文章是去年写的，当时在看《SQL Server 2008 实战》这本书，看完后，觉得不能只是看书，不然很快就会忘记，于是在空闲的时候，把书上的东西实践了一下，算是装模做样的把书上的代码抄写了一遍，就算是实践过了，放心了。但其实很快就忘记了，就算抄写10遍，也会忘记，学了不用等于不学。不过这也没办法，因为学了这个全文检索，公司里也用不到。

现在回想一下，这种实践有点像以前小学和初中时抄写错别字一下，字写错了，老师会说：“你把这个句子抄写100遍”，一开始抄写的时候，还挺认真的，但写了一会，手就开始酸了。

于是手上握着5支笔，开始抄写，这样就能一次抄写5遍，效率提高了好多倍，现在想想这个是偷工减料，但也包含了优化的思想，那就是同时用更多的资源(这里是5支笔)来做事。

但再想想，其实这种学习效率其实是很差的，本质上就是做了不少的无用功，没必要抄写那么多遍，所以就有另一种优化，那就是少做无用之事，少做无用功。

言归正传，现在有这样一个问题，有个字段，文本型的，可能会有上万个文字，现在要从表中，通过这个字段的文本，找到复合要求的记录，那么从SQL Server数据库的角度，有什么方法呢？

我觉得通过使用全文检索，能少做不少的无用功。下面是例子。

一、首先是普通的方法：


   
  
      set statistics io on   
         set statistics time on   
           
         create database wc   
         go   
           
         use wc   
         go   
           
           
         create table tbl_word   
         (   
         i int not null primary key identity( 1, 1),   
         v nvarchar( max) --存储大量文字   
         )   
         go   
         --delete from tbl_word   
           
         insert into tbl_word   
         values( ‘我的一个兴趣是看电影。‘),   
         ( ‘我的一个爱好是看电影和电视剧‘)   
           
         insert into tbl_word   
         values( replicate( ‘我的一个爱好是看电影和电视剧‘, 1000)   ‘兴趣‘     
         replicate( ‘我的爱好是看电视剧和film和动漫‘, 1500))   
           
         go 1000   
           
         insert into tbl_word   
         values( ‘我的一个兴趣是看电影。‘),   
         ( ‘我的一个爱好是看电影和电视剧‘)   
           
         go 100   
           
         insert into tbl_word   
         values( replicate( ‘我的一个爱好是看电影和电视剧‘, 1000)   ‘haha‘     
         replicate( ‘我的爱好是看电视剧和film和动漫‘, 1500))   
           
         go   
           
           
         /*   
         SQL Server 分析和编译时间:    
          CPU 时间 = 0 毫秒，占用时间 = 3 毫秒。   
            
         (1 行受影响)   
         表 ‘tbl_word‘。扫描计数 1，逻辑读取 1509 次，物理读取 0 次，预读 0 次，lob 逻辑读取 5 次，lob 物理读取 0 次，lob 预读 0 次。   
            
          SQL Server 执行时间:   
          CPU 时间 = 484 毫秒，占用时间 = 490 毫秒。   
         */   
         select *   
         from tbl_word   
         where v like ‘%haha%‘

二、全文检索的方法：


   
  
      create fulltext catalog cat_production_document   
         go   
           
           
         --从系统干扰词表，来创建自定义的干扰词表，因为系统干扰词表是无法修改的   
         CREATE FULLTEXT STOPLIST WCX   
         from system stoplist;   
         go   
           
           
         create fulltext index on dbo.tbl_word --在这个表上建全文索引    
         (   
         v   
         )   
         key index PK__tbl_word__3BD019960BC6C43E --键索引,一般是表的主键,这里需要修改为具体的名称    
         on cat_production_document --全文目录    
         with (CHANGE_TRACKING AUTO, --全文索引会随着表数据的修改而自动更新    
         StopList=wcx); --是用自定义的干扰字表    
         go   
           
           
         ALTER FULLTEXT INDEX ON dbo.tbl_word   
         enable   
         go   
           
         set statistics io on   
         set statistics time on   
           
         --查询基于变形的,字面的,同义的匹配方式搜索全文列    
         --会返回要搜索文本中包含的单词以及单词的同义词,变形词(复数)的记录   
         /*   
         SQL Server 分析和编译时间:    
          CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。   
            
          SQL Server 执行时间:   
          CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。   
         SQL Server 分析和编译时间:    
          CPU 时间 = 0 毫秒，占用时间 = 5 毫秒。   
            
         (1 行受影响)   
         表 ‘tbl_word‘。扫描计数 0，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 3 次，lob 物理读取 0 次，lob 预读 0 次。   
            
         (1 行受影响)   
            
          SQL Server 执行时间:   
          CPU 时间 = 0 毫秒，占用时间 = 10 毫秒。   
         SQL Server 分析和编译时间:    
          CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。   
            
          SQL Server 执行时间:   
          CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。   
            
         */   
         SELECT *   
         from dbo.tbl_word   
         WHERE FREETEXT (v, --带全文索引的列名    
         ‘haha‘); --要搜索的文本

从上面的2段代码在执行时所消耗的时间，就可以清楚的看出2种方法的效率差异，全文索引的效率提高了50倍左右。

其实采用合适的技术（比如，这里的全文检索技术，就很适合模糊查询），就能提高不少性能。

3、全文检索的问题

有时候，我们发现有些词是找不到的，这个主要是因为干扰词的影响，比如我们查询"是"，那么就没有记录返回。

通过dm_fts_parser，我们可以知道sql server 的全文服务，是如何来断词分词的。


   
  
      --1. 但是如果我们查询"是",会发现没有返回记录   
         SELECT *   
         from dbo.tbl_word   
         WHERE FREETEXT (v, --带全文索引的列名    
         ‘是‘); --要搜索的文本    
           
           
           
         --2.查询地区标识   
         select name,   
         alias,   
         lcid --地区标识符    
         from sys.syslanguages   
         where name = ‘简体中文‘   
         /*   
         name alias lcid   
         简体中文 Simplified Chinese 2052   
         */   
           
           
         --3.干扰词列表id    
         select *   
         from sys.fulltext_stoplists   
         /*   
         stoplist_id name   
         5 WCX   
         */   
           
           
         --4.是否可以被全文引擎识别,也就是对内容,断词后的结果   
         --"是"是一个Noise Word ,也就是干扰词   
         select special_term,   
         display_term,   
         source_term   
         from sys.dm_fts_parser   
         ( ‘我的一个兴趣是看电影。‘, --要搜索的字符串    
         2052, --地区标识符    
         5, --干扰词列表id    
         0) --是否区分重音    
         /*   
         special_term display_term source_term   
         Noise Word 我 我的一个兴趣是看电影。   
         Noise Word 的 我的一个兴趣是看电影。   
         Noise Word 一 我的一个兴趣是看电影。   
         Noise Word 个 我的一个兴趣是看电影。   
         Exact Match 兴趣 我的一个兴趣是看电影。   
         Noise Word 是 我的一个兴趣是看电影。   
         Exact Match 看 我的一个兴趣是看电影。   
         Exact Match 电影 我的一个兴趣是看电影。   
         */   
           
           
           
         --5.1 我们可以查询一下干扰词列表,发现 "是" 是一个干扰词   
         SELECT stopword   
         FROM sys.fulltext_stopwords   
         WHERE language_id = 2052 and stopword = ‘是‘   
         /*   
         stopword   
         是   
         */   
           
           
         --5.2把干扰词"是"去掉   
         ALTER FULLTEXT STOPLIST wcx   
         DROP ‘是‘ language ‘Simplified Chinese‘;   
           
           
           
         --5.3再次查询,没有结果返回,发现已去掉这个干扰词   
         SELECT stopword   
         FROM sys.fulltext_stopwords   
         WHERE language_id = 2052 and stopword = ‘是‘   
           
           
           
         --5.4 会返回3000多条记录   
         SELECT *   
         from dbo.tbl_word   
         WHERE FREETEXT (v, --带全文索引的列名    
         ‘是‘); --要搜索的文本    
           
           
           
         --5.5 但考虑到这个"是"没什么意义,所以还是需要加到干扰词列表中    
         ALTER FULLTEXT STOPLIST wcx   
         add ‘是‘ language ‘Simplified Chinese‘;   
           
           
           
         --5.6 再次查询,没有返回任何记录,说明干扰词起作用了   
         SELECT *   
         from dbo.tbl_word   
         WHERE FREETEXT (v, --带全文索引的列名    
         ‘是‘); --要搜索的文本    
           
           
           
           
         --6.1 改成使用系统干扰词列表   
         ALTER FULLTEXT INDEX on tbl_word --表名   
         SET STOPLIST= system ; --指定使用的全文非索引字表为系统干扰词列表   
           
           
         --6.2 启动填充，如果CHANGE_TRACKING != AUTO，则需要启动一次填充才使新设定的全文非索引字表生效;   
         ALTER FULLTEXT INDEX on tbl_word --表名   
         START FULL POPULATION

有的时候，我们要查询的单词，虽然不是Noise Word，但还是会查询不到，这个就是全文检索的分词的问题了

对于每种断字符语言，断词结果是无法改变的。如果实在想要改变，只能通过微软公布的接口，自行编程修改相应的组件。

不过我们可以通过修改分词所使用的语言来尝试一下，比如一般是用2052，也就是简体中文，如果用1028，也就是繁体中文，那么就有可能达到合理分词的目的。

【SQL Server性能优化】运用SQL Server的全文检索来提高模糊匹配的效率

不想长大啊发布了416 篇原创文章 · 获赞 135 · 访问量 94万他的留言板关注

秒客网

【SQL Server性能优化】运用SQL Server的全文检索来提高模糊匹配的效率

相关文章