Объединение таблиц

Объединения таблиц в 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 обратитесь к разделу 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: Возвращает только строки, где есть совпадение в обеих таблицах. Например, запрос выполняет INNER JOIN между таблицами orders и customers, включая только заказы, у которых есть соответствующие клиенты.
‹›
  • 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 — это возможность выполнять полнотекстовый поиск одновременно по левой и правой таблицам. Это позволяет создавать сложные запросы, фильтрующие данные на основе текстового содержимого в нескольких таблицах.

Вы можете использовать отдельные функции MATCH() для каждой таблицы в вашем JOIN-запросе. Запрос фильтрует результаты на основе текстового содержимого в обеих таблицах.

‹›
  • 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. Вычисление оценки релевантности: Каждая таблица поддерживает собственный балл релевантности, доступный через table_name.weight() в SQL или table_name._score в JSON-ответах.

Пример: Сложный JOIN с фасетированием

Опираясь на предыдущие примеры, рассмотрим более продвинутый сценарий, где мы комбинируем объединения таблиц с фасетированием и полнотекстовым поиском по нескольким таблицам. Это демонстрирует всю мощь возможностей JOIN в Manticore с комплексной фильтрацией и агрегацией.

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)

Параметры поиска и веса совпадений

Для запросов в объединении можно задать отдельные параметры: для левой и правой таблиц. Синтаксис — OPTION(<table_name>) для SQL-запросов и один или несколько подобъектов в "options" для JSON-запросов.

Вот пример, как задать разные веса полей для полнотекстового запроса по правой таблице. Чтобы получить веса совпадений через 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 конфигурационного файла.
    • Размер пакета по умолчанию — 1000, но вы можете увеличить или уменьшить его в зависимости от задачи.
    • Установка join_batch_size=0 полностью отключает пакетную обработку, что может быть полезно для отладки или специфических сценариев.

  • Особенности производительности:

    • Больший размер пакета может улучшить производительность за счёт уменьшения количества запросов к правой таблице.
    • Однако большие пакеты могут потреблять больше памяти, особенно при сложных запросах или больших данных.
    • Экспериментируйте с разными размерами пакетов, чтобы найти оптимальный баланс между производительностью и использованием ресурсов.

Кэширование объединений

Для дальнейшей оптимизации операций объединения Manticore Search использует механизм кэширования запросов, выполняемых по правой таблице. Вот что важно знать:

  • Как работает кэширование:

    • Каждый запрос к правой таблице определяется условиями JOIN ON.
    • Если одни и те же условия JOIN ON повторяются в нескольких запросах, результаты кэшируются и переиспользуются.
    • Это позволяет избежать избыточных запросов и ускорить последующие операции объединения.

  • Настройка размера кэша:

    • Размер кэша объединений можно настроить с помощью опции join_cache_size в разделе searchd конфигурационного файла.
    • Размер кэша по умолчанию — 20MB, но вы можете изменить его в зависимости от нагрузки и доступной памяти.
    • Установка join_cache_size=0 полностью отключает кэширование.

  • Особенности использования памяти:

    • Каждый поток поддерживает собственный кэш, поэтому общий объём памяти зависит от количества потоков и размера кэша.
    • Убедитесь, что на сервере достаточно памяти для кэша, особенно при высокой конкуренции запросов.

Объединение распределённых таблиц

Распределённые таблицы, состоящие только из локальных таблиц, поддерживаются как слева, так и справа в запросах с объединением. Однако распределённые таблицы, включающие удалённые таблицы, не поддерживаются.

Особенности и рекомендации

При использовании JOIN в Manticore Search учитывайте следующие моменты:

  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. Использование ANY с MVA: При использовании функции ANY() с мультизначными атрибутами в JOIN задавайте псевдоним для мультизначного атрибута из присоединённой таблицы:

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

Следуя этим рекомендациям, вы сможете эффективно использовать JOIN в Manticore Search для объединения данных из нескольких индексов и выполнения сложных запросов.

Фильтры Выражения
Last modified: November 10, 2025