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

时间:2022-06-26 07:28:58

原文: 【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数据库的角度,有什么方法呢?

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


一、首先是普通的方法:


   
  
  1. set statistics io on
  2. set statistics time on
  3. create database wc
  4. go
  5. use wc
  6. go
  7. create table tbl_word
  8. (
  9. i int not null primary key identity( 1, 1),
  10. v nvarchar( max) --存储大量文字
  11. )
  12. go
  13. --delete from tbl_word
  14. insert into tbl_word
  15. values( ‘我的一个兴趣是看电影。‘),
  16. ( ‘我的一个爱好是看电影和电视剧‘)
  17. insert into tbl_word
  18. values( replicate( ‘我的一个爱好是看电影和电视剧‘, 1000) ‘兴趣‘
  19. replicate( ‘我的爱好是看电视剧和film和动漫‘, 1500))
  20. go 1000
  21. insert into tbl_word
  22. values( ‘我的一个兴趣是看电影。‘),
  23. ( ‘我的一个爱好是看电影和电视剧‘)
  24. go 100
  25. insert into tbl_word
  26. values( replicate( ‘我的一个爱好是看电影和电视剧‘, 1000) ‘haha‘
  27. replicate( ‘我的爱好是看电视剧和film和动漫‘, 1500))
  28. go
  29. /*
  30. SQL Server 分析和编译时间:
  31. CPU 时间 = 0 毫秒,占用时间 = 3 毫秒。
  32. (1 行受影响)
  33. 表 ‘tbl_word‘。扫描计数 1,逻辑读取 1509 次,物理读取 0 次,预读 0 次,lob 逻辑读取 5 次,lob 物理读取 0 次,lob 预读 0 次。
  34. SQL Server 执行时间:
  35. CPU 时间 = 484 毫秒,占用时间 = 490 毫秒。
  36. */
  37. select *
  38. from tbl_word
  39. where v like ‘%haha%‘


 

二、全文检索的方法:


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

 

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

 

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

 

3、全文检索的问题

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

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

 


   
  
  1. --1. 但是如果我们查询"是",会发现没有返回记录
  2. SELECT *
  3. from dbo.tbl_word
  4. WHERE FREETEXT (v, --带全文索引的列名
  5. ‘是‘); --要搜索的文本
  6. --2.查询地区标识
  7. select name,
  8. alias,
  9. lcid --地区标识符
  10. from sys.syslanguages
  11. where name = ‘简体中文‘
  12. /*
  13. name alias lcid
  14. 简体中文 Simplified Chinese 2052
  15. */
  16. --3.干扰词列表id
  17. select *
  18. from sys.fulltext_stoplists
  19. /*
  20. stoplist_id name
  21. 5 WCX
  22. */
  23. --4.是否可以被全文引擎识别,也就是对内容,断词后的结果
  24. --"是"是一个Noise Word ,也就是干扰词
  25. select special_term,
  26. display_term,
  27. source_term
  28. from sys.dm_fts_parser
  29. ( ‘我的一个兴趣是看电影。‘, --要搜索的字符串
  30. 2052, --地区标识符
  31. 5, --干扰词列表id
  32. 0) --是否区分重音
  33. /*
  34. special_term display_term source_term
  35. Noise Word 我 我的一个兴趣是看电影。
  36. Noise Word 的 我的一个兴趣是看电影。
  37. Noise Word 一 我的一个兴趣是看电影。
  38. Noise Word 个 我的一个兴趣是看电影。
  39. Exact Match 兴趣 我的一个兴趣是看电影。
  40. Noise Word 是 我的一个兴趣是看电影。
  41. Exact Match 看 我的一个兴趣是看电影。
  42. Exact Match 电影 我的一个兴趣是看电影。
  43. */
  44. --5.1 我们可以查询一下干扰词列表,发现 "是" 是一个干扰词
  45. SELECT stopword
  46. FROM sys.fulltext_stopwords
  47. WHERE language_id = 2052 and stopword = ‘是‘
  48. /*
  49. stopword
  50. */
  51. --5.2把干扰词"是"去掉
  52. ALTER FULLTEXT STOPLIST wcx
  53. DROP ‘是‘ language ‘Simplified Chinese‘;
  54. --5.3再次查询,没有结果返回,发现已去掉这个干扰词
  55. SELECT stopword
  56. FROM sys.fulltext_stopwords
  57. WHERE language_id = 2052 and stopword = ‘是‘
  58. --5.4 会返回3000多条记录
  59. SELECT *
  60. from dbo.tbl_word
  61. WHERE FREETEXT (v, --带全文索引的列名
  62. ‘是‘); --要搜索的文本
  63. --5.5 但考虑到这个"是"没什么意义,所以还是需要加到干扰词列表中
  64. ALTER FULLTEXT STOPLIST wcx
  65. add ‘是‘ language ‘Simplified Chinese‘;
  66. --5.6 再次查询,没有返回任何记录,说明干扰词起作用了
  67. SELECT *
  68. from dbo.tbl_word
  69. WHERE FREETEXT (v, --带全文索引的列名
  70. ‘是‘); --要搜索的文本
  71. --6.1 改成使用系统干扰词列表
  72. ALTER FULLTEXT INDEX on tbl_word --表名
  73. SET STOPLIST= system ; --指定使用的全文非索引字表为系统干扰词列表
  74. --6.2 启动填充,如果CHANGE_TRACKING != AUTO,则需要启动一次填充才使新设定的全文非索引字表生效;
  75. ALTER FULLTEXT INDEX on tbl_word --表名
  76. START FULL POPULATION



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

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

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

【SQL Server性能优化】运用SQL Server的全文检索来提高模糊匹配的效率【SQL Server性能优化】运用SQL Server的全文检索来提高模糊匹配的效率 不想长大啊 发布了416 篇原创文章 · 获赞 135 · 访问量 94万 他的留言板 关注