连接表

Manticore Search 中的表连接使您能够通过匹配相关列来组合两个表中的文档。此功能允许更复杂的查询和跨多个表的增强数据检索。

通用语法

SQL

SELECT
    select_expr [, select_expr] ...
    FROM tbl_name
    {INNER | LEFT} JOIN tbl2_name
    ON join_condition
    [...other select options]
join_condition: {
    left_table.attr = right_table.attr
    | left_table.json_attr.string_id = string(right_table.json_attr.string_id)
    | left_table.json_attr.int_id = int(right_table.json_attr.int_id)
    | [..右表属性上的过滤器]
}

有关选择选项的更多信息，请参阅SELECT部分。

当通过 JSON 属性中的值进行连接时，您需要使用 int() 或 string() 函数显式指定该值的类型。

SELECT ... ON left_table.json_attr.string_id = string(right_table.json_attr.string_id)
SELECT ... ON left_table.json_attr.int_id = int(right_table.json_attr.int_id)

JSON

POST /search
{
  "table": "table_name",
  "query": {
    <optional full-text query against the left table>
  },
  "join": [
    {
      "type": "inner" | "left",
      "table": "joined_table_name",
      "query": {
        <optional full-text query against the right table>
      },
      "on": [
        {
          "left": {
            "table": "left_table_name",
            "field": "field_name",
            "type": "<common field's type when joining using json attributes>"
          },
          "operator": "eq",
          "right": {
            "table": "right_table_name",
            "field": "field_name"
          }
        }
      ]
    }
  ],
  "options": {
    ...
  }
}
on.type: {
    int
    | string
}

注意，left 操作数部分中有一个 type 字段，当使用 json 属性连接两个表时应使用。允许的值为 string 和 int。

连接类型

Manticore Search 支持两种类型的连接：

  1. INNER JOIN：仅返回两个表中都有匹配的行。例如，该查询在 orders 和 customers 表之间执行 INNER JOIN，仅包含具有匹配客户的订单。
‹›
  • SQL
  • JSON
📋
SELECT product, customers.email, customers.name, customers.address
FROM orders
INNER JOIN customers
ON customers.id = orders.customer_id
WHERE MATCH('maple', customers)
ORDER BY customers.email ASC;
‹›
Response
+---------+-------------------+----------------+-------------------+
| product | customers.email   | customers.name | customers.address |
+---------+-------------------+----------------+-------------------+
| Laptop  | alice@example.com | Alice Johnson  | 123 Maple St      |
| Tablet  | alice@example.com | Alice Johnson  | 123 Maple St      |
+---------+-------------------+----------------+-------------------+
2 rows in set (0.00 sec)
  1. LEFT JOIN：返回左表中的所有行以及右表中匹配的行。如果没有匹配，则右表的列返回 NULL 值。例如，此查询使用 LEFT JOIN 检索所有客户及其对应的订单。如果不存在对应订单，则显示 NULL 值。结果按客户的电子邮件排序，仅选择客户的姓名和订单数量。
‹›
  • SQL
  • JSON
📋
SELECT
name, orders.quantity
FROM customers
LEFT JOIN orders
ON orders.customer_id = customers.id
ORDER BY email ASC;
‹›
Response
+---------------+-----------------+-------------------+
| name          | orders.quantity | @int_attr_email   |
+---------------+-----------------+-------------------+
| Alice Johnson |               1 | alice@example.com |
| Alice Johnson |               1 | alice@example.com |
| Bob Smith     |               2 | bob@example.com   |
| Carol White   |               1 | carol@example.com |
| John Smith    |            NULL | john@example.com  |
+---------------+-----------------+-------------------+
5 rows in set (0.00 sec)

跨连接表的全文匹配

Manticore Search 表连接的强大功能之一是能够同时对左表和右表执行全文搜索。这允许您创建基于多个表中文本内容过滤的复杂查询。

您可以在 JOIN 查询中为每个表使用单独的 MATCH() 函数。查询基于两个表中的文本内容过滤结果。

‹›
  • SQL
  • JSON
📋
SELECT t1.f, t2.f 
FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
WHERE MATCH('hello', t1) AND MATCH('goodbye', t2);
‹›
Response
+-------------+---------------+
| f           | t2.f          |
+-------------+---------------+
| hello world | goodbye world |
+-------------+---------------+
1 row in set (0.00 sec)

连接的 JSON 查询结构

在 JSON API 查询中，特定表的全文匹配结构与 SQL 不同：

主表查询：根级别的 "query" 字段应用于主表（在 "table" 中指定）。

连接表查询：每个连接定义可以包含其自己的 "query" 字段，专门应用于该连接表。

‹›
  • JSON
JSON
📋
⚙
POST /search
{
  "table": "t1",
  "query": {
    "query_string": "hello"
  },
  "join": [
    {
      "type": "left",
      "table": "t2",
      "query": {
        "match": {
          "*": "goodbye"
        }
      },
      "on": [
        {
          "left": {
            "table": "t1",
            "field": "id"
          },
          "operator": "eq",
          "right": {
            "table": "t2",
            "field": "id"
          }
        }
      ]
    }
  ]
}
‹›
Response
{
  "took": 1,
  "timed_out": false,
  "hits": {
    "total": 1,
    "total_relation": "eq",
    "hits": [
      {
        "_id": 1,
        "_score": 1680,
        "t2._score": 1680,
        "_source": {
          "f": "hello world",
          "t2.id": 1,
          "t2.f": "goodbye world"
        }
      }
    ]
  }
}

理解 JOIN 操作中的查询行为

1. 仅主表查询：返回主表中所有匹配的行。对于未匹配的连接记录（LEFT JOIN），SQL 返回 NULL 值，而 JSON API 返回默认值（数字为 0，文本为空字符串）。

‹›
  • SQL
  • JSON
📋
SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
WHERE MATCH('database', t1);
‹›
Response
+------+-----------------+-------+------+
| id   | f               | t2.id | t2.f |
+------+-----------------+-------+------+
|    3 | database search |  NULL | NULL |
+------+-----------------+-------+------+
1 row in set (0.00 sec)

2. 连接表上的查询作为过滤器：当连接表有查询时，仅返回同时满足连接条件和查询条件的记录。

‹›
  • JSON
JSON
📋
⚙
POST /search
{
  "table": "t1",
  "query": {
    "query_string": "database"
  },
  "join": [
    {
      "type": "left",
      "table": "t2",
      "query": {
        "query_string": "nonexistent"
      },
      "on": [
        {
          "left": {
            "table": "t1",
            "field": "id"
          },
          "operator": "eq",
          "right": {
            "table": "t2",
            "field": "id"
          }
        }
      ]
    }
  ]
}
‹›
Response
{
  "took": 0,
  "timed_out": false,
  "hits": {
    "total": 0,
    "total_relation": "eq",
    "hits": []
  }
}

3. JOIN 类型影响过滤：INNER JOIN 需要同时满足连接和查询条件，而 LEFT JOIN 即使右表条件不满足，也返回匹配的左表行。

JOIN 中全文匹配的重要注意事项

使用连接进行全文匹配时，请注意以下几点：

  1. 特定表匹配：

    • SQL：每个 MATCH() 函数应指定搜索的表：MATCH('term', table_name)
    • JSON：主表使用根级 "query"，连接表使用各自连接定义内的 "query"

  2. 查询语法灵活性：JSON API 支持全文查询的 "query_string" 和 "match" 语法

  3. 性能影响：对两个表进行全文匹配可能影响查询性能，尤其是大数据集。请考虑使用适当的索引和批处理大小。

  4. NULL/默认值处理：使用 LEFT JOIN 时，如果右表无匹配记录，查询优化器会根据性能决定先评估全文条件还是过滤条件。SQL 返回 NULL 值，JSON API 返回默认值（数字为 0，文本为空字符串）。

  5. 过滤行为：连接表上的查询作为过滤器——限制结果为同时满足连接和查询条件的记录。

  6. 全文操作符支持：JOIN 查询支持所有全文操作符，包括短语、邻近、字段搜索、NEAR、法定人数匹配和高级操作符。

  7. 评分计算：每个表维护自己的相关性评分，可通过 SQL 中的 table_name.weight() 或 JSON 响应中的 table_name._score 访问。

示例：带分面功能的复杂 JOIN

基于前面的示例，让我们探索一个更高级的场景，将表连接与分面和跨多个表的全文匹配结合起来。这展示了 Manticore JOIN 功能在复杂过滤和聚合中的全部威力。

MORE

该查询演示了跨 customers 和 orders 两个表的全文匹配，结合范围过滤和分面搜索。它搜索名为 "Alice" 或 "Bob" 的客户及其包含 "laptop"、"phone" 或 "tablet" 且价格高于 500 美元的订单。结果按订单 ID 排序，并按保修条款进行分面。

‹›
  • SQL
  • JSON
📋
SELECT orders.product, name, orders.details.price, orders.tags
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.details.price > 500
AND MATCH('laptop | phone | tablet', orders)
AND MATCH('alice | bob', customers)
ORDER BY orders.id ASC
FACET orders.details.warranty;
‹›
Response
+-----------------+---------------+----------------------+-------------+
| orders.product  | name          | orders.details.price | orders.tags |
+-----------------+---------------+----------------------+-------------+
| Laptop Computer | Alice Johnson |                 1200 | 101,102     |
| Smart Phone     | Bob Smith     |                  800 | 103         |
+-----------------+---------------+----------------------+-------------+
2 rows in set (0.00 sec)
+-------------------------+----------+
| orders.details.warranty | count(*) |
+-------------------------+----------+
| 2 years                 |        1 |
| 1 year                  |        1 |
+-------------------------+----------+
2 rows in set (0.00 sec)

搜索选项和匹配权重

可以为连接中的查询分别指定选项：左表和右表。语法为 SQL 查询中的 OPTION(<table_name>)，以及 JSON 查询中 "options" 下的一个或多个子对象。

下面是如何为右表的全文查询指定不同字段权重的示例。要通过 SQL 获取匹配权重，请使用 <table_name>.weight() 表达式。 在 JSON 查询中，该权重表示为 <table_name>._score。

‹›
  • SQL
  • JSON
📋
SELECT product, customers.email, customers.name, customers.address, customers.weight()
FROM orders
INNER JOIN customers
ON customers.id = orders.customer_id
WHERE MATCH('maple', customers)
OPTION(customers) field_weights=(address=1500);
‹›
Response
+---------+-------------------+----------------+-------------------+--------------------+
| product | customers.email   | customers.name | customers.address | customers.weight() |
+---------+-------------------+----------------+-------------------+--------------------+
| Laptop  | alice@example.com | Alice Johnson  | 123 Maple St      |            1500680 |
| Tablet  | alice@example.com | Alice Johnson  | 123 Maple St      |            1500680 |
+---------+-------------------+----------------+-------------------+--------------------+
2 rows in set (0.00 sec)

连接批处理

执行表连接时，Manticore Search 会批量处理结果以优化性能和资源使用。工作原理如下：

  • 批处理工作原理：

    • 首先执行左表的查询，并将结果累积到一个批次中。
    • 然后将该批次用作右表查询的输入，右表查询作为单次操作执行。
    • 这种方法减少了发送到右表的查询次数，提高了效率。

  • 配置批次大小：

    • 可以使用 join_batch_size 搜索选项调整批次大小。
    • 也可以在配置文件的 searchd 部分通过 join_batch_size 进行配置。
    • 默认批次大小为 1000，您可以根据使用场景增大或减小。
    • 设置 join_batch_size=0 可完全禁用批处理，这在调试或特定场景下可能有用。

  • 性能考虑：

    • 较大的批次大小可以通过减少右表查询次数来提升性能。
    • 但较大的批次可能会消耗更多内存，尤其是对于复杂查询或大数据集。
    • 通过尝试不同批次大小，找到性能和资源使用的最佳平衡点。

连接缓存

为了进一步优化连接操作，Manticore Search 对右表执行的查询采用缓存机制。您需要了解以下内容：

  • 缓存工作原理：

    • 右表的每个查询由 JOIN ON 条件定义。
    • 如果多个查询中重复相同的 JOIN ON 条件，结果会被缓存并重用。
    • 这避免了冗余查询，加快了后续连接操作。

  • 配置缓存大小：

    • 可以通过配置文件 searchd 部分的 join_cache_size 选项配置连接缓存大小。
    • 默认缓存大小为 20MB，您可以根据工作负载和可用内存调整。
    • 设置 join_cache_size=0 可完全禁用缓存。

  • 内存考虑：

    • 每个线程维护自己的缓存，因此总内存使用取决于线程数和缓存大小。
    • 确保服务器有足够内存以容纳缓存，尤其是在高并发环境下。

连接分布式表

仅包含本地表的分布式表支持作为连接查询的左侧和右侧表。然而，包含远程表的分布式表不被支持。

注意事项和最佳实践

在 Manticore Search 中使用 JOIN 时，请注意以下几点：

  1. 字段选择：在 JOIN 中选择两个表的字段时，不要为左表字段加前缀，但要为右表字段加前缀。例如：

    SELECT field_name, right_table.field_name FROM ...

  2. JOIN 条件：始终在 JOIN 条件中显式指定表名：

    JOIN ON table_name.some_field = another_table_name.some_field

  3. 带 JOIN 的表达式：当使用结合两个连接表字段的表达式时，为表达式结果设置别名：

    SELECT *, (nums2.n + 3) AS x, x * n FROM nums LEFT JOIN nums2 ON nums2.id = nums.num2_id

  4. 基于别名表达式的过滤：不能在 WHERE 子句中使用涉及两个表字段的表达式别名。

  5. JSON 属性：连接 JSON 属性时，必须显式将值转换为适当类型：

    -- 正确：
SELECT * FROM t1 LEFT JOIN t2 ON int(t1.json_attr.id) = t2.json_attr.id
-- 错误：
SELECT * FROM t1 LEFT JOIN t2 ON t1.json_attr.id = t2.json_attr.id

  6. NULL 处理：可以对连接字段使用 IS NULL 和 IS NOT NULL 条件：

    SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.name IS NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.name IS NOT NULL

  7. 使用带 MVA 的 ANY：在 JOIN 中使用带多值属性的 ANY() 函数时，为连接表的多值属性设置别名：

    SELECT *, t2.m AS alias
FROM t
LEFT JOIN t2 ON t.id = t2.t_id
WHERE ANY(alias) IN (3, 5)

遵循这些指南，您可以有效地使用 Manticore Search 中的 JOIN 来组合多个索引的数据并执行复杂查询。

过滤器 表达式
Last modified: November 10, 2025