SQL反模式学习笔记17 全文搜索

目标：全文搜索

使用SQL搜索关键字，同时保证快速和精确，依旧是相当地困难。

SQL的一个基本原理（以及SQL所继承的关系原理）就是一列中的单个数据是原子性的。

反模式：模式匹配

使用Like 或者正则表达式。

  缺点：（1）无法使用索引，进行全表遍历，非常耗时，性能极低。

     （2）有时候会返回医疗之外的结果。select * from bugs where description like '%one%'，

返回结果可能是money、prone、lonely。

正则表达式可能会为单词边界提供一个模式来解决单词的匹配问题。

如何识别反模式：当出现以下情况时，可能是反模式

  1、如何在like表达式的2个通配符之间插入一个变量？

  2、如何写一个正则表达式来检查一个字符串是否包含多个单词、不包含一个特定的单词，或者包含给定单词的任意形式？

  3、网站的搜索功能在增加了很多文档进去之后慢的不可理喻。

合理使用反模式：

  1、性能总是最重要的，如果一些查询过程很少执行，就不必要花很多功夫去对它进行优化

  2、使用模式匹配操作进行很复杂的查询是很困难的，但是如果你为了一些简单的需求设计这样的模式匹配，它们能帮助你用

最少的工作量获得正确的结果。

解决方案：使用工具

  最好的方案就是使用特殊的搜索引擎技术，而不是SQL。

  另一个方案是将结果保存起来从而减少重复的搜索开销。

  1、MySQL中的全文索引：可以再一个类型为Char、varchar或者Text的列上定义一个全文索引。然后使用Match函数来搜索。

  2、Oracle中的文本索引：Context、Ctxcat、Ctxxpath、Ctxule。

  3、SQL Server中的全文搜索：使用Contains()操作符来使用全文索引。使用之前需要通过复杂的步骤来配置。

  4、PostgreSQL的文本搜索：提供一个复杂大可大量配置的方式来将文本转换为可搜索的词汇集合，并且让这些文档能够进行

模式匹配搜索。

  5、SQLite的全文搜索：使用SQLite的扩展组件来实现。

  6、第三方搜索引擎：

    （1）Sphinx Search：开源的搜索引擎，用于MySQL以及PostgreSQL来配套使用。

    （2）Apache Lucene:是一个针对Java程序的成熟搜索引擎。

  7、实现自己的搜索引擎：

使用反向索引方案：反向索引就是一个所有可能被搜索的单词列表。

    （1）定义一个KeyWords表来记录所有用户搜索的关键字，然后定义一个交叉表来建立多对多的关系。

    （2）将每个关键字和匹配的内容添加到交叉表中。

        当有新的搜索单词，就使用like查询结果，并将结果保存到交叉表里，这样下次就不必like了。

     当有新的文档入库，就需要用触发器（或者定时）去填充交叉表。

结论：不必使用SQL来解决所有问题。