目标:全文搜索

任何存储文本的应用都有针对某个字段进行查找的需求,随着数据增长,性能成为必经的瓶颈。
SQL的一个基本原理就是一列中的单个数据是原子性的,也就是说,你能对两个值进行比较,但通常是把两个值当成两个整体来比较。而你要在SQL中比较子字符串意味着低效和不准确。那么我们该如何高效的查找一个字符的子字符串呢?

反模式:模式匹配断言

SQL提供了模式匹配断言来比较字符串,通常是程序员用来搜索关键字的第一选择,最广泛的就是LIKE断言。

1
select * from bug where description like '%crash%';

还有正则表达式也被多数数据库支持,如MYSQL

1
select * from bug where description REGEXP 'crash';

模式匹配虽然能实现我们大部分需求,但是最大缺点就是性能问题,它无法从索引上受益,因此必须全表扫描,这是非常恐怖的。

模式匹配还有一个缺点就是,它会返回莫名奇妙的结果集

1
select * from bug where description like '%one%';

上面模式匹配则会返回money、prone、lonely等单词,即使在两端加上空格也不能避免。虽然mysql对此提出单词匹配的解决方案,但是它仍然是不高效的,如下:

1
select * from bug where description REGEXP '[[:<:]]one[[:>:]]';

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

  1. 如何在like表达式的2个通配符之间插入一个变量?
  2. 如何写一个正则表达式来检查一个字符串是否包含多个单词、不包含一个特定的单词,或者包含给定单词的任意形式?
  3. 网站的搜索功能在增加了很多文档进去之后慢的不可理喻。

合理使用反模式

  1. 性能是非常重要的,如果一些查询过程很少执行,就不必要花很多功夫去对它进行优化
  2. 使用模式匹配操作进行很复杂的查询是很困难的,但是如果你为了一些简单的需求设计这样的模式匹配,它们能帮助你用最少的工作量获得正确的结果。

解决方案:使用正确的工具

最好的解决方案不是优化你的SQL,而是使用搜索引擎。或者另一个可选的方案是将结果保存起来从而减少重复的搜索开销。

数据库扩展

MySQL的全文索引

MySQL在MyISAM存储引擎上提供了一个简单的全文索引类型,你可以在char、varchar、text类型的列上定义一个全文索引。下面的例子在summary和description列上定义全文索引。

1
alter table bug add fulltext index bugfts(summary, description);

可以使用MATCH()函数对索引内容进行搜索。必须在匹配时指定需要全文索引的列(因而你可以在同一张表中对其他列使用不同类型的索引)

1
select * from bug where match(summary, description) against('crash');
Oracle的文本索引

从Oracle8开始,就支持文本索引特性,它的索引非常强大且复杂,这里只是简单介绍

  • CONTEXT
    为单个文本列建立这样的索引类型,使用CONTAINS()操作符进行搜索,索引和数据不同步,所以你每次都得手动重建索引或定期重建

    1
    2
    CREATE INDEX BugText on bug(summary) INDEXTYPE IS CTSSYS.CONTEXT;
    select * from bug where CONTAINS(summary, 'crash') > 0;
  • CTXCAT
    这样类型的索引是针对短文本设计的,比如用在程序的分类目录上,和同一张表的其他结构化列在一起。这样的索引会保持数据的同步。

    1
    2
    3
    4
    CTX_DDL.CREATE_INDEX_SET('BugsCatalogSet');
    CTX_DDL.ADD_INDEX('BugsCatalogSet', 'status');
    CTX_DDL.ADD_INDEX('BugsCatalogSet', 'priority');
    CREATE INDEX BugsCatalog ON Bug(summary) INDEXTYPE IS CTSSYS.CTXCAT PARAMETERS('BugsCatalogSet');

    CATSEARCH()操作符能分别接受两个参数来搜索索引列和结构化的集合。

    1
    select * from bug where CATSEARCH(summary, '(crash save)', 'status = "NEW"') > 0;
  • CTXXPATH
    这中索引是针对XML搜索而设计的。使用existsNode()操作符。

    1
    2
    CREATE INDEX BugTestXml ON bug(testoutput) INDEXTYPE IS CTSSYS.CTXXPATH;
    select * from bug where testoutput.existsNode('/testsuite/test[@status="fail"]') > 0;
  • CTXRULE
    假设在数据库中存在大量的文档,然后需要根据文档内容进行分类。使用CTXRULE索引,你可以设计分析文档的规则并返回文档的分类信息。同时,你可以提供一些示例文档以及对应的分类概念,然后让Oracle去分析这个规则并应用到其它文档上。

其他数据库全文索引

书中还提到SQLServer、PostgreSQL、SQLite。个人没有用到,不是很熟悉,请自行查看。

第三方搜索引擎

如果需要搜索功能的代码对不同数据库都通用,那么就需要一个独立于数据库的搜索引擎,这一节介绍两个产品Sphinx Search和Apache Lucene。

  • Sphinx Search
    Sphinx Search是一个开源引擎,用于和mysql、postgreSQL配套使用,它构建索引和搜索都很快,而且它还支持分布式查询。对数据不常更新且要求高可扩展性的程序来说,是一个很好的选择。
    你可以使用Sphinx来索引存在于mysql中的数据,通过修改sphinx.conf中的几个字段,你需要写一个sql查询脚本为构建索引的操作获取数据。这个查询第一列要求为一个整形主键。最后通过sql查询脚本通过给定的主键代码$id,从数据库中获取完整的记录。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    -- sphinx.conf
    source bugsrc{
    type = mysql
    sql_user = buguser
    sql_pass = bugpass
    sql_db = bug
    sql_query = select bug_id, status, date_reported, summary, description from bug
    sql_attr_timestamp = date_reported
    sql_attr_str2ordinal = status
    sql_query_info = select * from bug where bug_id = $id
    }
    index bug{
    source = bugsrc
    path = /opt/local/var/db/sphinx/bug
    }

    完成配置后,你可以在shell中使用indexer命令创建索引:

    1
    indexer -c sphinx.conf bug

    你可以使用search进行搜索

    1
    search -b "crash -save"

    sphinx 也有对应的API供常用的语言(php、perl、Ruby)调用。sphinx当前的问题是,目前索引算法不支持高效的增量更新。在一个经常更新的数据源上使用sphinx需要一些折中的处理方式。比如,可将搜索的数据拆成两张表 ,第一张存储不变的主体历史数据,第二张存储较小大的当前数据集合,当数据慢慢增长时,就需要重建索引。然后你的程序必须对两个sphinx索引进行搜索。

  • Apache Lucene
    lucene是一个针对java的成熟搜索引擎,它使用其独特的格式为文本文档创建索引,lucene索引不和元数据保持同步。如果你插入、删除、更新,必须也对lucene索引进行对应的操作。
    apache提供一个项目叫做solr,solr是lucene索引的网关服务,你可以向solr添加文档或者使用rest风格的接口提交查询请求,这样你可以使用任意语言调用lucene了。
    你也可以将solr配置成直接连接到数据库,执行一个查询操作,然后使用data-importHandler工具对结果进行索引。

  • 实现自己的搜索引擎
    本节使用一个称为反向索引的方案来实现独立搜索引擎。反向索引就是一个所有可能被搜索的单词列表。在多对多的关系中,索引将这些单词和包含这个单词的文本关联起来。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    create table keyword(
    keyword_id serial primary key,
    keyword varchar(40) not null,
    unique key(keyword)
    );
    create table bug_keyword(
    keyword_id bigint unsigned not null,
    bug_id bigint unsigned not null,
    primary key(keyword_id, bug_id),
    foreign key(keyword_id) reference keyword(keyword_id),
    foreign key(bug_id) reference bug(bug_id)
    );

    接下来,我们将每个关键字和所匹配到bug添加到bug_keyword中,我们可以使用like查询,获取我们所需要的匹配记录。对于单个单词来说,使用这个查询只有一次,接下来每一次都从索引中获取对应的记录。我们可以使用存储过成完成

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    create procedure bug_search(keyword varchar(40))
    begin
    set @keyword = keyword;
    prepare s1 from 'select max(keyword_id) into @k from keyword where keyword = ?';
    execute s1 using @keyword;
    deallocate prepare s1;
    if (@k is null) then
    prepare s2 from 'insert into keyword(keyword) value (?)';
    execute s2 using @keyword;
    deallocate prepare s2;
    select last_insert_id() into @k;

    prepare s3 from 'insert bug_keyword(bug_id, keyword_id)
    select bug_id, ? from bug
    where summary regexp concat(''[[:<:]]'', ?, ''[[:>:]]'')
    or description regexp concat(''[[:<:]]'', ?, ''[[:>:]]'')';
    execute s3 using @k, @keyword, @keyword;
    deallocate prepare s3;
    end if;

    prepare s4 from 'select b.* from bug b
    join bug_keyword k using(bug_id)
    where k.keyword_id = ?';
    execute s4 using @k;
    deallocate prepare s4;
    end

    使用这个存储过程,你需要维护一个触发器,当你插入或删除时,你需要维护bug_keyword的记录

    1
    2
    3
    4
    5
    6
    7
    create trigger bug_insert after insert on bug for each row
    begin
    insert into bug_keyword(bug_id, keyword_id)
    select new.bug_id, k.keyword_id from keyword k
    where new.description regexp concat('[[:<:]]', k.keyword, '[[:>:]]')
    or new.summary regexp concat('[[:<:]]', k.keyword, '[[:>:]]');
    end

    这个关键词列表会随着用户不断搜索而自动增长,因此我们不必将知识库中找到的单词都追加到列表中。

总结

你不必使用sql来解决所有问题