设为首页收藏本站
切换到窄版

 找回密码
 用户注册

QQ登录

只需一步，快速开始

ACE Developer»论坛 › 其它技术研讨 › 数据库 › 使用 Sphinx 更好地进行 MySQL 搜索-使用 Sphinx 进行非 ...
返回列表 发新帖
查看: 3834|回复: 0

使用 Sphinx 更好地进行 MySQL 搜索-使用 Sphinx 进行非全文本搜

[复制链接]
发表于 2012-2-14 23:35:54 | 显示全部楼层 |阅读模式
简介： 尽管 MySQL 是一个出色的通用数据库，但是如果您的应用程序需要进行大量搜索，那么使用 Sphinx 可获得更好的性能。尽管 Sphinx 是一种全文本搜索工具，但即使与非全文本查询一起使用，它仍然可以提高应用程序的速度。本文将介绍如何针对此任务配置 Sphinx，其中包括一些示例查询，度量其执行时间，本文还将演示在考虑以某种通用的、系统的方式使用 Sphinx 时，更改中涉及的一些权衡方法。
简介
MySQL 是一个出色的综合性数据库，但是，对于需要进行大量搜索的应用程序，考虑采用具体的搜索实用工具可能会更好一些。本文章将 Sphinx（一个著名的全文本搜索包）视为 MySQL 的替代品，用它来进行搜索，提高非全文本搜索的速度。本文章研究此更改中涉及的权衡方法和说明，演示一些具体测试，并考虑以通用方式使用 Sphinx 所需的内容。
MySQL 或 Sphinx？
为什么会考虑使用 Sphinx 替换 MySQL？比如说，以一个书店的搜索应用程序为例。用户可能会通过指定书名、作者、状态（新或旧）、版本（第一版或影印版）、封面（精装或平装）、出版商、出版年份、是否有作者签名和价格等内容查找书籍。MySQL 通常不会使用多个索引（一个例外是索引合并优化；参阅 参考资料），因此优化所有可能搜索的唯一方法就是使用限制数量的多列索引，但这样做效果并不是很好。
但是，Sphinx 是一种搜索引擎，可以与 MySQL 很好地集成，也可以自己独立工作。它提供了高索引和搜索性能，支持通过 SphinxQL 进行查询。SphinxQL 是一种基于 Structured Query Language (SQL) 的语言。最后，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 表中修正一些细节
  1.                                
  2. ALTER TABLE salaries
  3.   DROP PRIMARY KEY,
  4.   ADD UNIQUE emp_from (emp_no, from_date) ;
  5. ALTER TABLE salaries
  6.   ADD salaries_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;
  7. UPDATE salaries
  8.   SET to_date="2038-01-01" WHERE to_date="9999-01-01";
  9.             
复制代码

现在，考虑清单 2 中所示的查询。

清单 2. 比较 MySQL 和 Sphinx 的一些简单测试
  1.                                
  2. SELECT *
  3.   FROM employees.employees
  4.   WHERE birth_date BETWEEN '1960-09-01' AND '1960-09-30'
  5.     AND hire_date >= '1998-01-01';
  6. 38 rows in set (0.19 sec)
  8. SELECT *
  9.   FROM employees.employees eee JOIN employees.salaries sss
  10.   ON sss.emp_no=eee.emp_no
  11.   WHERE eee.first_name='Yucel'
  12.     AND sss.salary>120000
  13.     AND sss.from_date >= '2000-01-01';
  14. 5 rows in set (0.15 sec)
  15.             
复制代码

第一个搜索仅包含 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 的源定义
  1.                                
  2. source employeesSource
  3. {
  4.   type = mysql
  5.   sql_host = localhost
  6.   sql_user = mysqluser
  7.   sql_pass = mysqlpass
  8.   sql_db = employees
  9.   sql_query= SELECT \
  10.     emp_no as id, \
  11.     TO_DAYS(birth_date) AS birth_date_td, \
  12.     first_name, \
  13.     last_name, \
  14.     gender, \
  15.     TO_DAYS(hire_date) AS hire_date_td \
  16.     FROM employees
  17.   sql_attr_uint = birth_date_td
  18.   sql_field_string = first_name
  19.   sql_field_string = last_name
  20.   sql_field_string = gender
  21.   sql_attr_uint = hire_date_td
  22. }
  24. source employeesSalariesSource
  25. {
  26.   type = mysql
  27.   sql_host = localhost
  28.   sql_user = mysqluser
  29.   sql_pass = mysqlpass
  30.   sql_db = employees
  31.   sql_query= SELECT \
  32.     ss.salaries_id AS id, \
  33.     ee.emp_no AS emp_no, \
  34.     TO_DAYS(ee.birth_date) AS birth_date_td, \
  35.     ee.first_name AS first_name, \
  36.     ee.last_name AS last_name, \
  37.     ee.gender AS gender, \
  38.     TO_DAYS(ee.hire_date) AS hire_date_td, \
  39.     ss.salary AS salary, \
  40.     UNIX_TIMESTAMP(ss.from_date) AS from_date_ts, \
  41.     UNIX_TIMESTAMP(ss.to_date) AS to_date_ts \
  42.     FROM employees ee JOIN salaries ss \
  43.     ON ss.emp_no=ee.emp_no
  44.   sql_attr_uint = emp_no
  45.   sql_attr_timestamp = birth_date_ts
  46.   sql_field_string = first_name
  47.   sql_field_string = last_name
  48.   sql_field_string = gender
  49.   sql_attr_uint = hire_date_td
  50.   sql_attr_uint = salary
  51.   sql_attr_timestamp = from_date_ts
  52.   sql_attr_timestamp = to_date_ts
  53. }
复制代码
            

配置文件可划分为许多小节。source 部分定义数据源。Sphinx 可处理多种类型的文件，包括文本、Hypertext Markup Language (HTML) 和 Extensible Markup Language (XML)。但是，本示例仅使用 MySQL，因此 type=mysql、sql_host、sql_user、sql_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 的索引定义
  1.                                
  2. index employeesIndex
  3. {
  4.   type = plain
  5.   source = employeesSource
  6.   path = /home/fkereki/bin/sphinx/var/data/sphinxFilesEI
  7.   charset_type = utf-8
  8.   preopen = 1
  9. }
  11. index employeesSalariesIndex
  12. {
  13.   type = plain
  14.   source = employeesSalariesSource
  15.   path = /home/fkereki/bin/sphinx/var/data/sphinxFilesESI
  16.   charset_type = utf-8
  17.   preopen = 1
  18. }
  19.             
复制代码

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 和搜索守护进程参数
  1.                                
  2. indexer
  3. {
  4.   mem_limit = 1024M
  5. }
  7. searchd
  8. {
  9.   listen = 127.0.0.1:9306:mysql41
  10.   log = /home/fkereki/bin/sphinx/var/log/searchd.log
  11.   query_log = /home/fkereki/bin/sphinx/var/log/query.log
  12.   pid_file = /home/fkereki/bin/sphinx/var/log/searchd.pid
  13. }
  14.             
复制代码

您必须定义 mem_limit RAM 大小，以便有足够的内存可供 indexer 使用。searchd 的定义不需要再加以说明，除了它使用了listen=。您可以使用此参数指定 SphinxQL 二进制网络协议可用的 IP 地址和端口。如果您想要与协议直接进行交互，可使用标准 MySQL 客户端，方法是输入 mysql -P 9306 并运行查询，而不在机器上运行 MySQL mysqld 程序。
现在设置了所有内容，您可以索引数据并开始运行搜索守护进程（清单 6）。--all 参数表示将生成所有索引文件。

清单 6. 索引数据和运行搜索守护进程
  1.                                
  2. ~/bin/sphinx/etc> ../bin/indexer --all
  3. Sphinx 2.0.1-beta (r2792)
  4. Copyright (c) 2001-2011, Andrew Aksyonoff
  5. Copyright (c) 2008-2011, Sphinx Technologies Inc (http://sphinxsearch.com)
  7. using config file './sphinx.conf'...
  8. indexing index 'employeesSalariesIndex'...
  9. collected 2844047 docs, 40.9 MB
  10. sorted 8.5 Mhits, 100.0% done
  11. total 2844047 docs, 40877736 bytes
  12. total 24.842 sec, 1645464 bytes/sec, 114482.32 docs/sec
  13. indexing index 'employeesIndex'...
  14. collected 300024 docs, 4.3 MB
  15. sorted 0.9 Mhits, 100.0% done
  16. total 300024 docs, 4311224 bytes
  17. total 0.914 sec, 4714945 bytes/sec, 328119.56 docs/sec
  18. total 7 reads, 0.274 sec, 19695.2 kb/call avg, 39.2 msec/call avg
  19. total 110 writes, 0.528 sec, 2351.0 kb/call avg, 4.8 msec/call avg
  21. ~/bin/sphinx/etc> ../bin/searchd
  22. Sphinx 2.0.1-beta (r2792)
  23. Copyright (c) 2001-2011, Andrew Aksyonoff
  24. Copyright (c) 2008-2011, Sphinx Technologies Inc (http://sphinxsearch.com)
  26. using config file './sphinx.conf'...
  27. listening on 127.0.0.1:9306
  28. precaching index 'employeesSalariesIndex'
  29. precaching index 'employeesIndex'
  30. precached 2 indexes in 0.124 sec
  31.             
复制代码

既然已经索引了数据，并且所需的守护进程正在运行，那么您可以准备运行一些搜索。
运行搜索
查询 Sphinx 的首选方法是使用 SphinxQL，它适用于 MySQL 的语言，也适用于 SphinxQL。本示例使用的是 PHP，代码如 清单 7 所示。

清单 7. 一个查询 MySQL 和 Sphinx 的 PHP 程序，定时测试其性能
  1.                                
  2. $bd0 = mysql_connect('localhost:3306', 'mysqluser','mysqlpass');
  3. $bd1 = mysql_connect('localhost:9306');
  5. echo "FIRST TEST ... SINGLE TABLE\n\n";
  7. $td1 = to_days("1960-09-01");
  8. $td2 = to_days("1960-09-30");
  9. $td3 = to_days("1998-01-01");
  11. do_time("test 1 - MySQL ", "SELECT * FROM employees.employees ".
  12.   "WHERE birth_date BETWEEN '1960-09-01' AND '1960-09-30' ".
  13.   "AND hire_date >= '1998-01-01'", $bd0);
  15. do_time("test 1 - Sphinx", "SELECT * FROM employeesIndex ".
  16.   "WHERE birth_date_td BETWEEN {$td1} AND {$td2} ".
  17.   "AND hire_date_td >= {$td3} LIMIT 0,10000 ", $bd1);
  19. echo "\nSECOND TEST ... JOIN\n\n";
  21. $ts1 = mktime(0,0,0,1,1,2000);
  23. do_time("test 2 - MySQL ", "SELECT * ".
  24.   "FROM employees.employees eee JOIN employees.salaries sss ".
  25.   "ON sss.emp_no=eee.emp_no ".
  26.   "WHERE eee.first_name='Yucel' ".
  27.   "AND sss.salary>120000 ".
  28.   "AND sss.from_date >= '2000-01-01'", $bd0);
  30. do_time("test 2 - Sphinx", "SELECT * FROM employeesSalariesIndex ".
  31.   "WHERE MATCH('_cnnew1@first_name Yucel') ".
  32.   "AND salary>120000 ".
  33.   "AND from_date_ts>={$ts1} limit 0,10000 ", $bd1);
  36. function to_days($date) {
  37.   return 719528 + floor(strtotime($date)/(60*60*24));
  38. }
  41. function do_time($description, $sentence, $bd) {
  42.   $m0 = microtime(true);
  43.   $res= @mysql_query($sentence, $bd);
  44.   $m1 = microtime(true);
  45.   $nr = mysql_num_rows($res);
  46.   echo $description." ".$nr." rows in ".
  47.     sprintf("%6.4f", $m1-$m0)." secs\n";
  48. }
  49.             
复制代码

根据 清单 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 之间的比较结果
  1.                                
  2. ~/bin/sphinx/etc> php test.php
  3. FIRST TEST ... SINGLE TABLE
  5. test 1 - MySQL  38 rows in 0.1912 secs
  6. test 1 - Sphinx 38 rows in 0.0157 secs
  8. SECOND TEST ... JOIN
  10. test 2 - MySQL  5 rows in 0.1532 secs
  11. test 2 - Sphinx 5 rows in 0.0020 secs
复制代码
            

这些结果很好，但是到目前为止，本示例仅考虑了静态搜索，它假定使用了常数表。您仍然需要查看更新索引文件的问题。
更新您的索引文件
如果更新原始数据，会出现什么情况？您必须更新索引文件，否则搜索将生成错误结果。在每次更新之后，就可以重新索引所有内容，但是这样做的成本可能太高了！Sphinx 提供了两种解决方案：增量索引文件和实时索引更新。
通常，您拥有时常需要添加少数新记录的大数据集。对于本示例，我们假定旧数据在第一次写好之后没有进行任何修改。您可以通过实现 main+delta 方案来获得几乎实时的索引更新。这里采用的原理是：为旧数据和固定数据设置一个索引，为新数据设置另一个索引，这样就可以快速创建索引，因为数据比较小。然后，您需要做的就是查询两种索引文件并使用两种结果的组合。考虑使用索引合并来重新创建主索引，方法是合并一个之前的索引和一个增量索引（参阅 参考资料 中的 Sphinx 文档，了解有关的详细信息）。
如果可以修改旧数据或者您真的需要实时更新，会出现什么情况？Sphinx 的 RT 索引文件是一种解决方案，因为它们支持INSERT、REPLACE 和 DELETE 命令实时影响索引文件。无论何时更新主表，都必须注意执行相应的索引更新，以确保 MySQL 和 Sphinx 数据之间没有任何差别。为了安全起见，请参阅 Sphinx 文档第 4.2 节中的说明和限制（参阅 参考资料）。
结束语
您可以得出什么样结论？首先，使用 Sphinx 代替 MySQL 可以提供显著的性能优势。Sphinx 十分适用于搜索静态表。但是，对于经常更新的表，则无法使用 plain 索引文件。相反，您需要实施增量文件索引或更改为实时索引，这两种解决方案都会导致额外的性能成本。最后，高效使用 Sphinx 需要进行一些规划，因为您必须预定义所有必需的源和索引文件，当然，这不是一个缺点，只是普通常识。
使用 Sphinx 代替 MySQL 并不简单，但也没有复杂到需要排除这种方法。如果快速搜索速度是必需的，则值得考虑将所用的搜索引擎从 MySQL 更改为 Sphinx，即使您不是正在进行全文搜索。

参考资料
学习
获得产品和技术
讨论
关于作者
Federico Kereki 是一名来自乌拉圭的系统工程师，拥有超过 20 年的开发、咨询和大学教学的经验。目前，他正在与各种各样的缩略词打交道：SOA、GWT、Ajax、PHP，当然还有 FLOSS！



傲游高品质VPS主机，特价促销
回复

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 用户注册

本版积分规则

Archiver|手机版|小黑屋|ACE Developer ( 京ICP备06055248号 )

GMT+8, 2024-12-4 01:26 , Processed in 0.017286 second(s), 5 queries , Redis On.

Powered by Discuz! X3.5

© 2001-2023 Discuz! Team.

快速回复 返回顶部 返回列表