使用 Sphinx 更好地进行 MySQL 搜索

标签: 搜索 sphinx MySQL高级应用 | 发表时间:2012-02-06 23:52 | 作者:OurMySQL
出处:http://ourmysql.com

简介

MySQL 是一个出色的综合性数据库,但是,对于需要进行大量搜索的应用程序,考虑采用具体的搜索实用工具可能会更好一些。本文章将 (一个著名的全文本搜索包)视为 MySQL 的替代品,用它来进行搜索,提高非全文本搜索的速度。本文章研究此更改中涉及的权衡方法和说明,演示一些具体测试,并考虑以通用方式使用 所需的内容。

MySQL 或 Sphinx?

为什么会考虑使用 Sphinx 替换 MySQL?比如说,以一个书店的搜索应用程序为例。用户可能会通过指定书名、作者、状态(新或旧)、版本(第一版或影印版)、封面(精装或平装)、出版商、出版年份、是否有作者签名和价格等内容查找书籍。MySQL 通常不会使用多个索引(一个例外是索引合并优化;参阅  参考资料),因此优化所有可能搜索的唯一方法就是使用限制数量的多列索引,但这样做效果并不是很好。

但是,Sphinx 是一种搜索引擎,可以与 MySQL 很好地集成,也可以自己独立工作。它提供了高索引和搜索性能,支持通过 SphinxQL 进行查询。SphinxQL 是一种基于 Structured Query Language ( ) 的语言。最后,Sphinx 能很好地向上扩展,能够使用分布式搜索功能处理包含几 TB 数据的数百万个文档。

Sphinx 可处理文档(可在数据库表或视图中保存)、文本字段(它索引的内容,提供全文本搜索)和属性(非文本值,可用于对结果进行筛选、排序和分组)。属性保存在随机存取内存 (RAM) 中以提高效率;请参阅  参考资料 中的 Sphinx 文档,了解实际大小计算公式。

在处理查询时,Sphinx 依赖于特殊索引文件。您必须为索引过程定义数据源,然后运行  indexer 程序。另一种可能性是使用实时索引文件,您可以在牺牲一些效率的情况下立即更新该文件。我将在本文后面详细介绍这些方法。

示例问题

为了测试 Sphinx 和 MySQL 的搜索速度,我找到了一些有趣的(在大小方面)数据集,以及一个包含 300 万条薪水记录的测试数据库,这似乎与我的目的非常相符。(请参阅  参考资料 以获得此数据。)图表很简单:在部门中工作的具有工作职务的员工和年薪。此示例处理两个最大的表: employees 和  salaries

安装了数据后,我发现需要修正  salaries 表的一些细节。首先, to_date 字段有时包含作为特殊标记的  9999-01-01;我将其替换为 2038-01-01,因为 UNIX® 的时间戳不得超过 2038。此外,Sphinx 要求每个记录都有一个 ID 字段,因此我必须为  salaries 表添加一个  salaries_id auto-increment 字段。参见  清单 1
清单 1. 在  salaries 表中修正一些细节

ALTER TABLE salaries
        PRIMARY KEY,
  ADD UNIQUE emp_from (emp_no, from_date) ;
ALTER TABLE salaries
  ADD salaries_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;
UPDATE salaries
  SET to_date="2038-01-01" WHERE to_date="9999-01-01";

 

现在,考虑 清单 2 中所示的查询。
清单 2. 比较 MySQL 和 Sphinx 的一些简单测试

SELECT *
  FROM employees.employees
  WHERE birth_date BETWEEN '1960-09-01' AND '1960-09-30'
    AND hire_date >= '1998-01-01';
38 rows in set (0.19 sec)

SELECT *
  FROM employees.employees eee JOIN employees.salaries sss
  ON sss.emp_no=eee.emp_no
  WHERE eee.first_name='Yucel'
    AND sss.salary>120000
    AND sss.from_date >= '2000-01-01';
5 rows in set (0.15 sec)

 

第一个搜索仅包含  employees 并查找在 1960 年出生且在 1998 年及之后雇佣的人。MySQL 在大约 0.19 秒的时间内找到了 38 条记录。(当然,您的结果可能有所不同。)第二个搜索加入了  employees 和  salaries 以查找在 2000 年及之后年薪超过 120,000 且名为 Yucel 的男员工。MySQL 在大约 0.15 秒内找到了 5 条记录。

要使用 Sphinx 运行这些搜索,您必须设置它,索引数据并运行守护程序。下一节将介绍这些任务。

配置 Sphinx

Sphinx 根据  sphinx.conf 文件中的定义索引数据源,如  清单 3 所示。注意日期字段的类型转换。本文省略了一些主要的必备参数,但是您应该了解,此处可使用更多的配置设置。
清单 3. Sphinx 的源定义

source employeesSource
{
  type = mysql
  sql_host = localhost
  sql_user = mysqluser
  sql_pass = mysqlpass
  sql_db = employees
  sql_query= SELECT \
    emp_no as id, \
    TO_DAYS(birth_date) AS birth_date_td, \
    first_name, \
    last_name, \
    gender, \
    TO_DAYS(hire_date) AS hire_date_td \
    FROM employees
  sql_attr_uint = birth_date_td
  sql_field_string = first_name
  sql_field_string = last_name
  sql_field_string = gender
  sql_attr_uint = hire_date_td
}

source employeesSalariesSource
{
  type = mysql
  sql_host = localhost
  sql_user = mysqluser
  sql_pass = mysqlpass
  sql_db = employees
  sql_query= SELECT \
    ss.salaries_id AS id, \
    ee.emp_no AS emp_no, \
    TO_DAYS(ee.birth_date) AS birth_date_td, \
    ee.first_name AS first_name, \
    ee.last_name AS last_name, \
    ee.gender AS gender, \
    TO_DAYS(ee.hire_date) AS hire_date_td, \
    ss.salary AS salary, \
    UNIX_TIMESTAMP(ss.from_date) AS from_date_ts, \
    UNIX_TIMESTAMP(ss.to_date) AS to_date_ts \
    FROM employees ee JOIN salaries ss \
    ON ss.emp_no=ee.emp_no
  sql_attr_uint = emp_no
  sql_attr_timestamp = birth_date_ts
  sql_field_string = first_name
  sql_field_string = last_name
  sql_field_string = gender
  sql_attr_uint = hire_date_td
  sql_attr_uint = salary
  sql_attr_timestamp = from_date_ts
  sql_attr_timestamp = to_date_ts
}

 

配置文件可划分为许多小节。source 部分定义数据源。Sphinx 可处理多种类型的文件,包括文本、Hypertext Markup Language (HTML) 和 Extensible Markup Language (XML)。但是,本示例仅使用 MySQL,因此  type=mysqlsql_hostsql_usersql_pass和  sql_db 参数定义了访问数据库的方式和使用的架构。 sql_query 参数提供 SQL 语句,检索将引用的数据。实际上,此处的唯一限制是第一个字段必须是唯一的、无符号的正整数 ID 数字。这就是我之前不得不在  salaries 表中添加  salaries_id 字段的原因。您最多可以指定 32 个文本字段和任意数量的属性。Sphinx 全文本索引除 ID(第一个字段)和属性之外的所有列。

如  表 1 所示,Sphinx 支持几种属性类型,但并不支持所有可能的 MySQL 数据类型。还有一些特定于 Sphinx 的属性类型,但是如果您只是寻找 MySQL 的替代品,这些属性类型就不适合,因此我没有在这里介绍它们。
表 1. Sphinx 仅支持几个属性类型

属性 描述
sql_attr_uint 和 sql_attr_bigint 32 位无符号整数值和 64 位有符号整数值。可对所有整数数据库字段和  DATE 使用这两种类型。
sql_attr_float 32 位浮点值。如果您想要存储地理坐标,可使用此属性类型。还要注意的是,如果您需要更高的精确度,则没有解决方法;字段四舍五入到七位小数。
sql_attr_bool 一个布尔型(单个位)值,类似于 MySQL 的  tinyint 值。
sql_attr_timestamp 一种 UNIX 时间戳,可表示从 1970-01-01 到 2038-01-19 的日期/时间值。您在 Sphinx 中无法直接使用  DATE 或  DATETIME 列类型。您必须使用  UNIX_TIMESTAMP() 函数将它们转换为时间戳。如果您仅需要日期,可使用  TO_DAYS() 函数将  DATE 字段转换为一个整数。
sql_attr_string 和 sql_field_string 字符串(很明显!),但前者仅用于检索,而后者可作为全文本被索引。

 

配置文件的 index 部分描述源中的属性( 清单 4)。
清单 4. Sphinx 的索引定义

index employeesIndex
{
  type = plain
  source = employeesSource
  path = /home/fkereki/bin/sphinx/var/data/sphinxFilesEI
  charset_type = utf-8
  preopen = 1
}

index employeesSalariesIndex
{
  type = plain
  source = employeesSalariesSource
  path = /home/fkereki/bin/sphinx/var/data/sphinxFilesESI
  charset_type = utf-8
  preopen = 1
}

 

Sphinx 使用的索引文件独立于 MySQL 使用的索引文件。 type=plain 行表示您正在使用标准的 Sphinx 索引文件。其他可能的索引是 distributed(当您具有在网络的几个节点分布的索引文件时)和  rt(表示  real time),您可以立刻更新这些索引。 source= 行将一个数据源与一个索引相关联。您可以在一个索引中合并几个数据源,但是在本示例中没有这样做。 path= 行定义索引文件名称及其存储位置。 charset_type= 行指定您应该使用 Single Byte Character Set ( sbcs) 还是 Universal Character Set (UCS) Transformation Format-8 bit ( utf-8)。最后, preopen=1 告诉搜索守护进程在加载时打开所有索引文件,而不是等待第一批查询到达。

配置文件中的最后一小节处理  indexer 和  searchd 应用程序( 清单 5)。
清单 5. Indexer 和搜索守护进程参数

indexer
{
  mem_limit = 1024M
}

searchd
{
  listen = 127.0.0.1:9306:mysql41
  log = /home/fkereki/bin/sphinx/var/log/searchd.log
  query_log = /home/fkereki/bin/sphinx/var/log/query.log
  pid_file = /home/fkereki/bin/sphinx/var/log/searchd.pid
}

 

您必须定义  mem_limit RAM 大小,以便有足够的内存可供  indexer 使用。 searchd 的定义不需要再加以说明,除了它使用了 listen=。您可以使用此参数指定 SphinxQL 二进制网络协议可用的 IP 地址和端口。如果您想要与协议直接进行交互,可使用标准 MySQL 客户端,方法是输入  mysql -P 9306 并运行查询,而不在机器上运行 MySQL  mysqld 程序。

现在设置了所有内容,您可以索引数据并开始运行搜索守护进程( 清单 6)。 --all 参数表示将生成所有索引文件。
清单 6. 索引数据和运行搜索守护进程 

~/bin/sphinx/etc> ../bin/indexer --all
Sphinx 2.0.1-beta (r2792)
Copyright (c) 2001-2011, Andrew Aksyonoff
Copyright (c) 2008-2011, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file './sphinx.conf'...
indexing index 'employeesSalariesIndex'...
collected 2844047 docs, 40.9 MB
sorted 8.5 Mhits, 100.0% done
total 2844047 docs, 40877736 bytes
total 24.842 sec, 1645464 bytes/sec, 114482.32 docs/sec
indexing index 'employeesIndex'...
collected 300024 docs, 4.3 MB
sorted 0.9 Mhits, 100.0% done
total 300024 docs, 4311224 bytes
total 0.914 sec, 4714945 bytes/sec, 328119.56 docs/sec
total 7 reads, 0.274 sec, 19695.2 kb/call avg, 39.2 msec/call avg
total 110 writes, 0.528 sec, 2351.0 kb/call avg, 4.8 msec/call avg

~/bin/sphinx/etc> ../bin/searchd
Sphinx 2.0.1-beta (r2792)
Copyright (c) 2001-2011, Andrew Aksyonoff
Copyright (c) 2008-2011, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file './sphinx.conf'...
listening on 127.0.0.1:9306
precaching index 'employeesSalariesIndex'
precaching index 'employeesIndex'
precached 2 indexes in 0.124 sec

 

既然已经索引了数据,并且所需的守护进程正在运行,那么您可以准备运行一些搜索。

运行搜索

查询 Sphinx 的首选方法是使用 SphinxQL,它适用于 MySQL 的语言,也适用于 SphinxQL。本示例使用的是 PHP,代码如  清单 7 所示。
清单 7. 一个查询 MySQL 和 Sphinx 的 PHP 程序,定时测试其性能

$bd0 = mysql_connect('localhost:3306', 'mysqluser','mysqlpass');
$bd1 = mysql_connect('localhost:9306');

echo "FIRST TEST ... SINGLE TABLE\n\n";

$td1 = to_days("1960-09-01");
$td2 = to_days("1960-09-30");
$td3 = to_days("1998-01-01");

do_time("test 1 - MySQL ", "SELECT * FROM employees.employees ".
  "WHERE birth_date BETWEEN '1960-09-01' AND '1960-09-30' ".
  "AND hire_date >= '1998-01-01'", $bd0);

do_time("test 1 - Sphinx", "SELECT * FROM employeesIndex ".
  "WHERE birth_date_td BETWEEN {$td1} AND {$td2} ".
  "AND hire_date_td >= {$td3} LIMIT 0,10000 ", $bd1);

echo "\nSECOND TEST ... JOIN\n\n";

$ts1 = mktime(0,0,0,1,1,2000);

do_time("test 2 - MySQL ", "SELECT * ".
  "FROM employees.employees eee JOIN employees.salaries sss ".
  "ON sss.emp_no=eee.emp_no ".
  "WHERE eee.first_name='Yucel' ".
  "AND sss.salary>120000 ".
  "AND sss.from_date >= '2000-01-01'", $bd0);

do_time("test 2 - Sphinx", "SELECT * FROM employeesSalariesIndex ".
  "WHERE MATCH('[email protected]_name Yucel') ".
  "AND salary>120000 ".
  "AND from_date_ts>={$ts1} limit 0,10000 ", $bd1);

function to_days($date) {
  return 719528 + floor(strtotime($date)/(60*60*24));
}

function do_time($description, $sentence, $bd) {
  $m0 = microtime(true);
  $res= @mysql_query($sentence, $bd);
  $m1 = microtime(true);
  $nr = mysql_num_rows($res);
  echo $description." ".$nr." rows in ".
    sprintf("%6.4f", $m1-$m0)." secs\n";
}

 

根据  清单 5 中的规范,必须将 SphinxQL 查询发送给端口 9306。因为 MySQL  TO_DAYS() 函数没有等效的 PHP ,所以我自己编写了一个函数,请注意, mktime 适用于 UNIX_TIMESTAMP 转换。 do_time 函数执行并确定在指定服务器上指定查询的时间。

SphinxQL 查询与 MySQL 查询的不同之处在于:

  • 您使用  MATCH 来查询文本字段。
  • 您必须将所有日期转换为时间戳或整数。为了具有多样性,本示例使用了两种方法。
  • Sphinx 可返回完整记录或仅 ID 字段,后者更高效。当然,如果您使用后一个选项,您必须使用 MySQL 来检索其他信息。
  • AND 和  OR 运算符的优先级是相同的,因此使用它们时应小心,并在需要时使用括号。
  • 并不是所有 MySQL 数值、字符串和其他函数都是由 Sphinx 提供的。

仅运行一些测试并不是彻底的概念证明,但是  清单 8 中所示的结果确实说明 MySQL 到 Sphinx 的改变可能很有用。
清单 8. MySQL 和 Sphinx 之间的比较结果

~/bin/sphinx/etc> php test.php
FIRST TEST ... SINGLE TABLE

test 1 - MySQL  38 rows in 0.1912 secs
test 1 - Sphinx 38 rows in 0.0157 secs

SECOND TEST ... JOIN

test 2 - MySQL  5 rows in 0.1532 secs
test 2 - Sphinx 5 rows in 0.0020 secs

 

这些结果很好,但是到目前为止,本示例仅考虑了静态搜索,它假定使用了常数表。您仍然需要查看更新索引文件的问题。

更新您的索引文件

如果更新原始数据,会出现什么情况?您必须更新索引文件,否则搜索将生成错误结果。在每次更新之后,就可以重新索引所有内容,但是这样做的成本可能太高了!Sphinx 提供了两种解决方案:增量索引文件和实时索引更新。

通常,您拥有时常需要添加少数新记录的大数据集。对于本示例,我们假定旧数据在第一次写好之后没有进行任何修改。您可以通过实现 main+delta 方案来获得几乎实时的索引更新。这里采用的原理是:为旧数据和固定数据设置一个索引,为新数据设置另一个索引,这样就可以快速创建索引,因为数据比较小。然后,您需要做的就是查询两种索引文件并使用两种结果的组合。考虑使用索引合并来重新创建主索引,方法是合并一个之前的索引和一个增量索引(参阅  参考资料 中的 Sphinx 文档,了解有关的详细信息)。

如果可以修改旧数据或者您真的需要实时更新,会出现什么情况?Sphinx 的 RT 索引文件是一种解决方案,因为它们支持 INSERTREPLACE 和  DELETE 命令实时影响索引文件。无论何时更新主表,都必须注意执行相应的索引更新,以确保 MySQL 和 Sphinx 数据之间没有任何差别。为了安全起见,请参阅 Sphinx 文档第 4.2 节中的说明和限制(参阅  参考资料)。

结束语

您可以得出什么样结论?首先,使用 Sphinx 代替 MySQL 可以提供显著的性能优势。Sphinx 十分适用于搜索静态表。但是,对于经常更新的表,则无法使用  plain 索引文件。相反,您需要实施增量文件索引或更改为实时索引,这两种解决方案都会导致额外的性能成本。最后,高效使用 Sphinx 需要进行一些规划,因为您必须预定义所有必需的源和索引文件,当然,这不是一个缺点,只是普通常识。

使用 Sphinx 代替 MySQL 并不简单,但也没有复杂到需要排除这种方法。如果快速搜索速度是必需的,则值得考虑将所用的搜索引擎从 MySQL 更改为 Sphinx,即使您不是正在进行全文搜索。

 

参考资料

学习

相关文章

标签: , ,

相关 [sphinx mysql 搜索] 推荐:

使用 Sphinx 更好地进行 MySQL 搜索

- - OurMySQL
MySQL 是一个出色的综合性数据库,但是,对于需要进行大量搜索的应用程序,考虑采用具体的搜索实用工具可能会更好一些. 本文章将 Sphinx(一个著名的全文本搜索包)视为 MySQL 的替代品,用它来进行搜索,提高非全文本搜索的速度. 本文章研究此更改中涉及的权衡方法和说明,演示一些具体测试,并考虑以通用方式使用 Sphinx 所需的内容.

开源搜索引擎评估:lucene sphinx elasticsearch

- - 鲁塔弗的博客
lucene系,java开发,包括 solr和 elasticsearch. sphinx,c++开发,简单高性能. 搜索引擎程序这个名称不妥当,严格说来应该叫做 索引程序(indexing program),早期主要用来做中文全文搜索,但是随着互联网的深入普及,各家网站规模越来越大,索引程序在 优化网站架构上发挥了更大的作用: 替代mysql数据库 内置的索引.

用Sphinx写书

- Mao.. - HYRY Blog
写技术书是一件十分费时费力的事情,作者不但需要编写有趣的内容,还需要用标准且美观大方的格式呈现内容. 在编写《Python科学计算》一书的过程中,我尝试使用Sphinx、Leo、MiKTeX等软件,拼凑出了一套适合编写技术书籍和文档的编写环境. 这本书是关于这个编写环境的一些介绍. 在线阅读版: http://hyry.dip.jp/sphinxbook.

淺談 reStructuredText 與 Sphinx

- khsing - Blog.XDite.net
但因為最近非常忙碌,所以在這裡只能淺談,請見諒. 「 第一次學 Rails 就上手 」2011/8 月的版本包含了 PDF 版本以及 EPUB 版. 這次的版本不是改寫,而是「重新排版」. 這份書的初稿是用 Markdown 謄寫手排. 而完稿則是用 Mac 上的軟體「Pages」手排. (你看,出書門檻不高吧 XD).

文档与笔记利器reStructuredText和Sphinx

- Wick - 七星庐
关于制作文档和笔记这种事,我已经纠结了很久,网上解决方案也一大推,我试过几样,ScrapBook和Zotero,编辑不太方便,同步麻烦. Google Note过于格式简单,现在也不更新了,Google Docs又有点杀鸡用牛刀. 还有传得很神奇的Evernote跟Onenote,我压根没兴趣去用.

文档与笔记利器 reStructuredText 和 Sphinx

- timebug - Wow! Ubuntu
本文转载自七星庐 [ 原文:文档与笔记利器reStructuredText和Sphinx / 作者 muzuiget ]. 关于制作文档和笔记这种事,我已经纠结了很久,网上解决方案也一大推,我试过几样,ScrapBook 和 Zotero,编辑不太方便,同步麻烦. Google Note 过于格式简单,现在也不更新了,Google Docs又有点杀鸡用牛刀.

sphinx分布式索引简介

- - haohtml's blog
sphinx分布式索引原理:. 当searchd收到一个对分布式索引的查询时,它做如下操作:. (在远程代理执行搜索的同时)对本地索引进行查询. 在应用程序看来,普通索引和分布式索引完全没有区别. 任一个searchd实例可以同时做为主控端(master,对搜索结果做聚合)和从属端(只做本地. 集群中的每台机器都可以做为主控端来搜索整个集群,搜索请求可以在主控端之间获.

[转] Sphinx SetGeoAnchor 经纬度查找附近地点

- - 互联网 - ITeye博客
原文地址 http://www.douban.com/group/topic/30286342. Sphinx 的 SetGeoAnchor方法,(LinkWith:http://sphinxsearch.com/docs/manual-0.9.9.html#api-func-setgeoanchor).

主流全文索引工具的比较( Lucene, Sphinx, solr, elastic search)

- - 企业架构 - ITeye博客
前几天的调研(  Rails3下的 full text search (全文本搜索, 全文匹配. ) ), 我发现了两个不错的候选: . lucene  (solr, elasticsearch 都是基于它) . 把看到的有价值的文章记录在这里: . 回答1.  Result relevance ranking is the default.

Linux Ksplice,MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统,为 Kernel 打补丁无需重启动,做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户,超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.