Sphinx 搜索的数据库定义

Question

背景

我正在创建一个 MySQL 数据库来存储课程等项目，其中单个课程可能有许多属性。例如：

单个课程可能具有以下任何或全部属性：

• 标题 (varchar)
• 次要标题 (varchar)
• 说明（文本）
• 日期
• 时间
• 特定位置 (varchar;例如，White Hall Room 7）
• 一般位置（varchar；例如，拉斯维加斯，内华达州）
• 位置坐标（浮点数；例如纬度、经度）
• 等。

数据库设置如下：

存储特定课程信息的表：

课程表：

• Course_ID（每个课程的主键唯一 ID）
• Creator_ID（创建者的唯一 ID）
• Creation_Date（课程创建的日期时间）
• Modified_Date（这是修改课程的最新时间戳）

存储的表每个课程的多个属性设置如下：

course_attributes 表：

• Attribute_ID（每个属性的唯一ID）
• Course_ID（具体课程属性的引用）
• Attribute（varchar 定义属性；例如“标题”）
• 值（包含指定属性值的文本；例如“我的课程标题”）

我想

使用 sphinx 搜索来搜索该数据库。通过此搜索，我有不同的字段权重不同，例如：“标题”比“描述”更重要。

我希望拥有的具体搜索字段是：

• 标题
• 日期
• 位置（字符串）
• 位置（地理 - 纬度/经度）

问题

我应该在 Mysql 中定义一个视图来根据 ' 组织属性标题'、'描述'等，或者有没有办法定义我的 sphinx.conf 文件以了解特定属性？

我愿意接受解决此问题的所有建议问题，是否是重新排列数据库/表或我的搜索方式。

如果您需要任何其他详细信息来帮助我找到解决方案，请告诉我。

提前感谢您的帮助

!--更新--!

好的，在阅读了一些答案后，我觉得我应该提供一些额外的信息。

纬度/经度

纬度/经度属性是我在收到大致位置字符串后在内部创建的。我可以按照我希望的任何方式生成值，这意味着我可以将它们一起存储在单个纬度/经度属性中，作为“浮点纬度、浮点长”值或任何其他所需的格式。仅当从初始位置字符串生成并验证它们后，才会执行此操作。这是为了防止 @X-Zero 和 @Cody 所建议的格式错误的数据。

请记住，纬度和经度只是说明需要使该字段可搜索，而不是其他任何内容。它只是另一个属性；许多之一。

对搜索结果进行加权

我知道如何为 Sphinx 搜索查询中的结果添加权重：

$cl->setFieldWeights( array('title'=>1000, 'description'=>500) );

这会导致 title 列的权重高于 description 列（如果结构如 @X-Zero 建议的那样）。我的问题更直接地涉及如何将上述逻辑应用于当前的表定义。

数据库结构、视图和效率

利用我对视图的介绍性知识，我想我可以创建一些东西，为每个课程显示一行，其中每个属性都是自己的柱子。我不知道如何实现这一点，或者是否可能。

我对数据库结构不是最有信心，但我按照描述设置表格的原因是因为在很多情况下，并非每门课程的所有字段都会填写，而我尝试要有效率[是的，看来我失败了]。

我认为使用当前的结构，每个属性都将包含一个值，因此不会导致表中浪费空间。或者，如果我有一个包含大量潜在属性的表，我会认为会浪费空间。如果我错了，我很高兴知道为什么我的理解是错误的。

Answer 1

首先我要说的是，我从未听说过 Sphinx，也（显然）使用过它。然而，从数据库的角度来看......

像这样做多域列是一个可怕（我会追捕并杀死你）的想法。一方面，不可能进行有意义的索引或排序。您还必须祈祷您不会获得带有文本数据的 latitude 属性（并且因为这只能通过编程方式得到加强，我将保证这种情况会发生） - 这样做会导致所有基于距离的公式来崩溃。说到位置，如果有人存储没有经度的纬度（请注意，无论您是否存储单个GeoLocation，这都是可能的） > 属性，或成对）？

最好的办法是执行以下操作：

1. 找出始终需要哪些属性。这些属于course 表（...大部分）。
2. 对于每组相关可选属性，创建一个表。例如，位置（尽管这可能是必需的...），其中包含纬度/经度、城市、州、地址、房间等。允许列为 null< /code>able（在集合中 - 添加约束，以便用户不能仅添加经度而不添加纬度）。
3. 为每组常见查询添加一个视图。即使（也许特别是）如果您坚持使用当前的设计，也可以使用视图。这促进了数据库的逻辑和物理实现之间的分离。 （这假设通过 SQL 进行搜索）然后您将能够通过指定 view_column 为 null 或 view_column = input_parameter 或任何一个来进行搜索。
4. 对于加权搜索（假设动态加权），您的查询将需要使用左连接（也在视图内 - 请记录这一点），并使用准备好的语句主机参数（只是省去麻烦）试图自己逃避事情）。检查每组参数（例如，纬度和经度），并将输入权重分配给新列（每个属性），该新列可以汇总为“总计”列（必须超过某个阈值）。

---

编辑：

使用视图：
对于您的结构，您通常要做的就是多次左连接到attributes表（每个需要的属性一个），关闭attribute code> （实际上应该是表的 int FK；您不希望其中同时包含“title”和“Title”）并加入 course_id - 值将是包含在选择中。使用这种技术，可以很简单地获取列列表，然后您可以在 Sphinx 中对其进行明显的权重。
这样做的问题是，如果您需要进行任何数据转换 - 您打赌如果类型发生变化，您将能够找到所有转换。当使用强类型列时，这介于琐碎（很可能最终得到一个唯一命名的列）和不必要（视图通常从查询中的字段中获取其数据类型定义）之间；根据您的架构，您可能最终会发现太多误报。

数据库效率：
你是对的，未填充的列是浪费空间。通常，当某些东西是可选的（ish）时，这意味着您可能需要一个额外的表。这就是为什么我建议将位置拆分到它自己的表中：这可以防止不需要位置的事件（...什么？）“浪费”空间，但会强制任何需要位置的事件定义一个位置来指定所有必需的信息。以这种方式拆分还有一个额外的好处：如果多个事件都使用同一位置（...我们希望不是同时发生），交叉引用表将为您节省很多的空间。远远超出您的 attributes 表的能力（毕竟您仍然需要存储每个事件的完整位置）。如果您仍然有很多“可选”属性，我听说 NoSQL 就是为这类事情而设计的（但我还没有真正研究过）。然而，除此之外，额外一张桌子的成本是微不足道的；内部数据的成本可能不会，但所需的空间会根据所存储数据的感知价值进行权衡。请记住，磁盘空间相对便宜 - 开发人员/维护人员时间才是昂贵的。

地址旁注：
您可能想要创建一个address 表。这将完全脱离事件信息，并且将包括（除其他外）预先计算的纬度/经度（在推荐的数据类型中 - 我不知道 >它是什么，但它肯定不是逗号分隔的字符串）。然后，您将拥有一个 event_address 表，该表将是事件及其发生地点之间的交叉引用 - 如果有其他信息（例如 room），那么应该保存在引用的location表中（而不是直接引用address）。一旦计算出纬度/经度值，您就永远不需要更改它。

关于纬度/经度后续更新的想法：
虽然自己指定纬度/经度值更好，但您需要将它们作为地址表的必需部分（或者纯粹的纬度/经度表的一部分/补充） 。坦率地说，任何类型的多值列（分隔列表）都只会带来麻烦 - 每次搜索它们时都必须解析它们（以及其他相关问题）。一旦你把它们分开排，其中的一个最终会被丢弃——如果有必要，墨菲本人将亲自干预。此外，在不同时间从地址更新它们将导致地址具有不匹配的纬度/经度对；您最好的选择是在插入时计算此信息（有许多网络服务可以为您查找此信息）。

多域表：
对于多域表，您基本上可以赌域键（属性）永远不会与值（错误，值）不同步。我不在乎你有多好，在某个地方，不知何故，它会发生：在我的公司，我们的遗留应用程序中有其中一个（它存储了 FK 链接以及 FK 引用的文件）到，以及一个属性）。在某一时刻，应用程序被安装在生产环境中，该应用程序立即开始存储正确的文件链接，但对于给定的属性类别，FK 链接到不同的文件。值得庆幸的是，另一个文件中有审计记录，可以逆转这一情况（……据他们所知）。

总结：
重新访问您的必需/可选数据。不要害怕创建额外的表，每个表对应一个实体，每列对应一个域；您还需要关系表。您可能还希望将审核数据 (last_updated_time) 放在一组单独的表中（单域表在这方面将有很大帮助）。

Answer 2

在 sphinx 配置中，您定义索引和填充索引的 SQL 查询。您可以定义基本属性，请参阅 Sphinx 属性

Sphinx 还支持纬度/经度的地理搜索，但它们需要以弧度表示，绝对不是像您这样的文本列。我同意 X-Zero 的观点，即存储 lat/lng 值是字符串是一个坏主意。