MySQL 存储引擎 (SphinxSE)

9.1. SphinxSE 概览

SphinxSE是一个可以编译进MySQL 5.x版本的MySQL存储引擎,它利用了该版本MySQL的插件式体系结构。SphinxSE不能用于MySQL 4.x系列,它需要MySQL 5.0.22或更高版本;或MySQL 5.1.12或更高版本;或MySQL 5.5.8或更高版本

尽管被称作“存储引擎”,SphinxSE自身其实并存储任何数据。它其实是一个允许MySQL服务器与searchd交互并获取搜索结果的嵌入式客户端。所有的索引和搜索都发生在MySQL之外。

显然,SphinxSE的适用于:

  • 使将MySQL FTS 应用程序移植到Sphinx;

  • 使没有Sphinx API的那些语言也可以使用Sphinx;

  • 当需要在MySQL端对Sphinx结果集做额外处理(例如对原始文档表做JOIN,MySQL端的额外过滤等等)时提供优化。

9.2. 安装 SphinxSE

你需要获得MySQL的源代码,然后重新编译MySQL。MySQL的源代码 (mysql-5.x.yy.tar.gz) 可在 dev.mysql.com 网站获得.

针对某些版本的MySQL,Sphinx 网站提供了包含支持SphinxSE的打过补丁tarball压缩包。将这些文件解压出来替换原始文件,就可以配置(configure)、构建(build)以生成带有内建Shpinx支持的MySQL了。

如果网站上没有对应版本的tarball,或者由于某种原因无法工作,那您可能需要手工准备这些文件。您需要一份安装好的GUN Autotools框架(autoconf,automake和libtool)来完成这项任务。

9.2.1. 编译支持SphinxSE的MySQL 5.0.x

如果使用我们事先做好的打过补丁的tarball,那请跳过步骤1-3。

  1.  sphinx.5.0.yy.diff 补丁文件复制到MySQL源码目录并运行

    patch -p1 < sphinx.5.0.yy.diff

    如果没有与您的MySQL版本完全匹配的.diff文件,请尝试一个最接近版本的.diff文件。确保补丁顺利应用,没有rejects。

  2. 在MySQL源码目录中运行

    sh BUILD/autorun.sh
  3. 在MySQL源码目录中建立sql/sphinx目录,并把Sphinx源码目录中mysqlse目录下的全部文件拷贝到这个目录。示例:

    cp -R /root/builds/sphinx-0.9.7/mysqlse/* /root/builds/mysql-5.0.24/sql/sphinx
  4. 配置(configure)MySQL,启用Sphinx引擎:

    ./configure --with-sphinx-storage-engine
  5. 构建(build)并安装MySQL:

    make
    make install

9.2.2. 编译支持SphinxSE的MySQL 5.1.x

如果使用我们事先做好的打过补丁的tarball,那请跳过步骤1-2。

  1. 在MySQL源码目录中建立storage/sphinx目录,并将Sphinx源码目录中的mysqlse目录下的全部文件拷贝到这个目录。示例:

    cp -R /root/builds/sphinx-0.9.7/mysqlse/* /root/builds/mysql-5.1.14/storage/sphinx
  2. 在MySQL源码目录运行

    sh BUILD/autorun.sh
  3. 配置(configure)MySQL,启用Sphinx引擎

    ./configure --with-plugins=sphinx
  4. 构建(build)并安装MySQL

    make
    make install

9.2.3. 检查SphinxSE安装与否

为了检查SphinxSE是否成功地编入了MySQL,启动新编译出的MySQL服务器,运行mysql客户端,执行SHOW ENGINES查询,这会显示一个全部可用引擎的列表。Sphinx应该出现在这个列表中,而且在“Support”列上显示“YES”:
mysql> show engines;
+------------+----------+-------------------------------------------------------------+
| Engine     | Support  | Comment                                                     |
+------------+----------+-------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance      |
  ...
| SPHINX     | YES      | Sphinx storage engine                                       |
  ...
+------------+----------+-------------------------------------------------------------+
13 rows in set (0.00 sec)

9.3. 使用 SphinxSE

要通过SphinxSE搜索,您需要建立特殊的ENGINE=SPHINX的“搜索表”(中间表,没有实际数据,查询时从searchd获取数据返回给MySQL),然后使用SELECT语句从中检索,把全文查询放在WHERE子句中。

让我们从一个create语句和搜索查询的例子开始:

CREATE TABLE t1
(
    id          INTEGER UNSIGNED NOT NULL,
    weight      INTEGER NOT NULL,
    query       VARCHAR(3072) NOT NULL,
    group_id    INTEGER,
    INDEX(query)
) ENGINE=SPHINX CONNECTION="sphinx://localhost:9312/test";

SELECT * FROM t1 WHERE query='test it;mode=any';

搜索表前三列(字段)的类型必须INTEGER UNSINGED(或者 BIGINT),INTEGER(或者 BIGINT)和VARCHAR(或者 TEXT),这三列分别对应文档ID,匹配权值和搜索查询。这前三个列的映射关系是固定的,你不能忽略这三列中的任何一个,或者移动其位置,或者改变其类型。搜索查询列必须被索引,其他列必须无索引。列的名字会被忽略,所以可以任意命名。

除此之外,其他列(字段)的类型必须是INTEGERTIMESTAMPBIGINTVARCHAR或者FLOAT之一。它们必须与Sphinx结果集中提供的属性按名称绑定,即它们的名字必须与sphinx.conf中指定的属性名一一对应。如果Sphinx搜索结果中没有某个属性名,该列的值就为NULL.

特殊的内部“虚拟”属性名也可以与SphinxSE列绑定。但特殊符号@_sph_代替。例如,要取得@group@count或者@distinct内部属性,列名应分别使用_sph_group_sph_count或者_sph_distinct

可以使用字符串参数CONNECTION来指定用这个表搜索时的默认搜索主机、端口号和索引。如果CREATE TABLE中没有使用连接(connection)串,那么默认使用索引名“*”(搜索所有索引)和localhost:9312。连接串的语法如下:

CONNECTION="sphinx://HOST:PORT/INDEXNAME"

默认的连接串也可以需要时改变:

ALTER TABLE t1 CONNECTION="sphinx://NEWHOST:NEWPORT/NEWINDEXNAME";

也可以在查询中覆盖全部这些选项。

如例子所示,查询文本和搜索选项都应放在WHERE子句中对query列的限制中(即第三列),选项之间用分号分隔,选项名与选项值用等号隔开。可以指定任意数目的选项。可用的选项如下:

  • query - 查询文本;

  • mode - 匹配模式.必须是 "all", "any", "phrase", "boolean", 或者 "extended", 或者 "extended2" 之一。默认为 "all";

  • sort - 匹配项排序模式 必须是 "relevance", "attr_desc", "attr_asc", "time_segments", 或者 "extended" 之一。除了“relevance”模式,其他模式中还必须在一个冒号后附上属性名(或“extended”模式中的排序子句):

    ... WHERE query='test;sort=attr_asc:group_id';
    ... WHERE query='test;sort=extended:@weight desc, group_id asc';
  • offset - 结果集中的偏移量,默认是0;

  • limit - 从结果集中获取的匹配项数目,默认为20;

  • index - 待搜索的索引:

    ... WHERE query='test;index=test1;';
    ... WHERE query='test;index=test1,test2,test3;';
  • minid, maxid - 匹配文档ID的最小值和最大值;

  • weights - 逗号分隔的列表,指定Sphinx全文数据字段的权值:

    ... WHERE query='test;weights=1,2,3;';
  • filter, !filter - 逗号分隔的列表,指定一个属性名和一系列可匹配的属性值:

    # only include groups 1, 5 and 19
    ... WHERE query='test;filter=group_id,1,5,19;';
    
    # exclude groups 3 and 11
    ... WHERE query='test;!filter=group_id,3,11;';
  • range, !range - 逗号分隔的列表,指定一个属性名和该属性可匹配的最小值和最大值:

    # include groups from 3 to 7, inclusive
    ... WHERE query='test;range=group_id,3,7;';
    
    # exclude groups from 5 to 25
    ... WHERE query='test;!range=group_id,5,25;';
  • maxmatches - 此查询最大匹配的数量, 与SetLimits() API 调用的max_matches参数类似:

    ... WHERE query='test;maxmatches=2000;';
  • cutoff - 此查询最大允许的匹配数, 与 SetLimits() API 调用的参数cutoff类似:

    ... WHERE query='test;cutoff=10000;';
  • maxquerytme - 此查询最大允许的查询时间 (单位为毫秒), 如同 SetMaxQueryTime() API 调用:

    ... WHERE query='test;maxquerytime=1000;';
  • groupby - 分组(group-by)函数和属性, 对应 SetGroupBy() API 调用:

    ... WHERE query='test;groupby=day:published_ts;';
    ... WHERE query='test;groupby=attr:group_id;';
  • groupsort - 分组(group-by)排序子句:

    ... WHERE query='test;groupsort=@count desc;';
  • distinct - 分组(group-by)时用于计算COUNT(DISTINCT) 的属性名称,如同 SetGroupDistinct() API 调用:

    ... WHERE query='test;groupby=attr:country_id;distinct=site_id';
  • indexweights - 逗号分隔的列表,指定一系列索引名和搜索时这些索引对应的权值:

    ... WHERE query='test;indexweights=idx_exact,2,idx_stemmed,1;';
  • comment - 用于查询日志标记本次查询的字符串 (对应Query() API 调用的$comment参数):

    ... WHERE query='test;comment=marker001;';
  • select - 用于计算的表达式 (对应 SetSelect() API 调用):

    ... WHERE query='test;select=2*a+3*b as myexpr;';
  • host, port - 分别表示本地或者远端 searchd 主机名称和 TCP 端口:

    ... WHERE query='test;host=sphinx-test.loc;port=7312;';
  • ranker - 当匹配模式(查询语法)为extended时用于评分模式 , 对应 SetRankingMode() API 调用. 可用值包括 "proximity_bm25", "bm25", "none", "wordcount", "proximity", "matchany", 和 "fieldmask".

    ... WHERE query='testrmode=extended2;anker=bm25;';
  • geoanchor - 地理坐标锚点,对应 SetGeoAnchor() API 调用. 参数有四个分别是维度与经度属性的名称以及定位点的坐标:

    ... WHERE query='test;geoanchor=latattr,lonattr,0.123,0.456';

一个非常重要的注意事项:让Sphinx来对结果集执行排序、过滤和切片(slice)要比提高最大匹配项数量然后在MySQL端用WHERE、ORDER BY和LIMIT子句完成对应的功能来得高效得。这有两方面的原因。首先,Sphinx对这些操作做了一些优化,比MySQL效率更高一些。其次,searchd可以打包更少的数据,SphinxSE也可以传输和解包更少的数据。

从版本0.9.9-rc1开始,除了结果集,额外的查询信息可以用SHOW ENGINE SPHINX STATUS语句获得:

mysql> SHOW ENGINE SPHINX STATUS;
+--------+-------+-------------------------------------------------+
| Type   | Name  | Status                                          |
+--------+-------+-------------------------------------------------+
| SPHINX | stats | total: 25, total found: 25, time: 126, words: 2 | 
| SPHINX | words | sphinx:591:1256 soft:11076:15945                | 
+--------+-------+-------------------------------------------------+
2 rows in set (0.00 sec)

查询状态信息可以通过状态变量名来访问。值得提醒的是,访问这些信息不需要超级用户权限。

mysql> SHOW STATUS LIKE 'sphinx_%';
+--------------------+----------------------------------+
| Variable_name      | Value                            |
+--------------------+----------------------------------+
| sphinx_total       | 25                               | 
| sphinx_total_found | 25                               | 
| sphinx_time        | 126                              | 
| sphinx_word_count  | 2                                | 
| sphinx_words       | sphinx:591:1256 soft:11076:15945 | 
+--------------------+----------------------------------+
5 rows in set (0.00 sec)

可以对SphinxSE搜索表和其他引擎的表之间使用JOIN,以下是一个例子,例中“documents”来自example.sql

mysql> SELECT content, date_added FROM test.documents docs
-> JOIN t1 ON (docs.id=t1.id) 
-> WHERE query="one document;mode=any";
+-------------------------------------+---------------------+
| content                             | docdate             |
+-------------------------------------+---------------------+
| this is my test document number two | 2006-06-17 14:04:28 | 
| this is my test document number one | 2006-06-17 14:04:28 | 
+-------------------------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SHOW ENGINE SPHINX STATUS;
+--------+-------+---------------------------------------------+
| Type   | Name  | Status                                      |
+--------+-------+---------------------------------------------+
| SPHINX | stats | total: 2, total found: 2, time: 0, words: 2 | 
| SPHINX | words | one:1:2 document:2:2                        | 
+--------+-------+---------------------------------------------+
2 rows in set (0.00 sec)

9.4. 通过 MySQL 生成片段 (摘要)

从版本0.9.9-rc2开始,SphinxSE提供了一个UDF函数,允许用户通过MySQL创建摘要。这个功能的作用与API调用BuildExcerprts的功能非常相似,但可以通过MySQL+SphinxSE来访问。

提供这个UDF的二进制文件叫做sphinx.so,当安装SphinxSE本身的时候,这个文件会自动地被创建,并且安装到合适的位置。 但如果由于某种原因它没能自动安装,那就请在创建SphinxSE的目录中寻找sphinx.so文件,并把它拷贝到你的MySQL实例的plugins目录下。然后用下面语句来注册这个UDF:

CREATE FUNCTION sphinx_snippets RETURNS STRING SONAME 'sphinx.so';

函数的名字必须是sphinx_snippets,而不能随便取名。函数的参数表必须如下:

原型: function sphinx_snippets ( document, index, words, [options] );

Documents和words这两个参数可以是字符串或者数据库表的列。Options参数(额外选项)必须这样指定:‘值’ AS 选项名。关于支持的所有选项,可以参见API调用BuildExcerprts()。只有一个选项不被API支持而只能用于UDF,这个选项叫做'sphinx',用于指定searchd的位置(服务器和端口)。

使用示例:

SELECT sphinx_snippets('hello world doc', 'main', 'world',
    'sphinx://192.168.1.1/' AS sphinx, true AS exact_phrase,
    '[b]' AS before_match, '[/b]' AS after_match)
FROM documents;

SELECT title, sphinx_snippets(text, 'index', 'mysql php') AS text
    FROM sphinx, documents
    WHERE query='mysql php' AND sphinx.id=documents.id;