目标：全文搜索

任何存储文本的应用都有针对某个字段进行查找的需求，随着数据增长，性能成为必经的瓶颈。
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来解决所有问题