Оптимизация нескольких JOIN в MySQL
Я динамически создаю оператор SELECT, который выбирает элемент и, возможно, несколько списков связанных элементов.
Конечная цель - объект в пространстве приложения с массивами идентификаторов для каждого из связанных типов.
Использовать список JOIN довольно просто:
SELECT items.*, item_has_related1.related1_id, item_has_related2.related2_id, ...
FROM (items)
LEFT JOIN item_has_related1 ON item_has_related1.item_id = items.id
LEFT JOIN item_has_related2 ON item_has_related2.item_id = items.id
... potentially many more
WHERE items.id = $itemId;
LEFT JOIN используется, потому что некоторые отношения могут быть пустыми.
Наиболее очевидная проблема заключается в том, что количество возвращаемых строк является произведением числа совпадений во всех соединениях. Всего с несколькими соединенными таблицами это число может стать очень большим. Если бы было пять таблиц с шестью совпадениями в каждой, было бы 6 ^ 5 строк! Вторичная проблема заключается в том, что обработка возвращаемых строк более сложна, так как мне нужно раскапывать уникальные значения в каждом столбце.
В качестве альтернативы я написал что-то вроде этого, что по сути делает отдельный запрос для каждого JOIN:
SELECT items.*, item_has_related_1.related1_id, NULL as related2_id, ...
FROM (items)
JOIN item_has_related_1 ON item_has_related_1.item_id = items.id
WHERE items.id = $itemId
UNION
SELECT items.*, NULL as related1_id, item_has_related_2.related2_id, ...
FROM (items)
JOIN item_has_related_2 ON item_has_related_2.item_id = items.id
WHERE items.id = $itemId
Количество строк, возвращаемых таким образом, является суммой количества совпадений во всех соединениях. Однако время подготовки запроса намного больше, поэтому для небольших наборов данных этот метод менее эффективен. Я попытался эмпирически определить определение «меньше», но с моими тестовыми данными я не уверен, значимы ли мои результаты.
Есть ли более эффективный способ выполнить несколько СОЕДИНЕНИЙ и объединить результаты или есть другой подход к этой проблеме?
ИЗМЕНЕНО ДОБАВИТЬ: У Бармара есть правильный ответ на мой вопрос, но следующим моим шагом было расширение предложения where для возврата нескольких строк. Что касается этот вопрос, мой код выглядел так:
SELECT items.*,
(SELECT GROUP_CONCAT(related1_id) FROM item_has_related_1 WHERE item_id = items.id) as related1Ids,
(SELECT GROUP_CONCAT(related2_id) FROM item_has_related_2 WHERE item_id = items.id) as related2Ids,
...
FROM items
WHERE <where criteria>
Вы правы, внутреннее соединение в этом случае работает, но набор результатов все равно растет экспоненциально. Отредактирую свой вопрос.
Я не совсем уверен, что в первую очередь имеет смысл объединять объединения в один запрос. Я думаю, что здесь имеет значение то, что вы делаете с результатами. Поскольку подходы «комбинированное объединение» и «отдельные запросы объединения» дают совершенно разные наборы результатов, вам придется либо сворачивать, либо расширять результаты на стороне клиента, чтобы использовать их таким же образом.
Спасибо за ответ. Я добавил немного пояснения к вопросу; в конечном итоге я сведу результаты каждого соединения к дискретному списку.
Ответы 2
Вы можете просто написать запрос с внутренними соединениями следующим образом:
SELECT items.*, item_has_related1.related1_id, item_has_related2.related2_id, ...
FROM (items)
INNER JOIN item_has_related1 ON item_has_related1.item_id = items.id
INNER JOIN item_has_related2 ON item_has_related2.item_id = items.id
... potentially many more
WHERE items.id = $itemId;
В этом запросе будет столько строк, сколько совпадений $itemId в других таблицах.
Дело в том, что если вам когда-либо понадобятся все эти данные, перечисленные в операторе select, вам придется выполнить работу по объединению всех запросов, даже если они разделены, что ничего не даст по сравнению с подходом к выполнению всех объединений вместе, как перечислены здесь.
Спасибо за ответ. Я только что изменил свой запрос, чтобы использовать INNER JOIN, но все равно получаю экспоненциально растущий набор результатов.
@Jerry Проверьте мой добавленный комментарий в ответе. Нет никакого способа обойти это. Уточню, что это не экспонента, а произведение декартов.
Я не уверен, что понимаю ваш комментарий о том, что ничего не получил - со всеми операторами JOIN, объединенными, как вы перечислили, я мог бы легко получить 10 000 строк для сортировки, где использование подхода UNION дало бы мне 40 строк, каждая с одной интересной частью информации. Возможно, я ошибаюсь в том, как MySQL оптимизирует, что делает одновременное выполнение всех объединений достаточно эффективным, чтобы компенсировать огромный набор результатов?
Вы можете использовать GROUP_CONCAT, чтобы в результате получить все связанные элементы из каждой таблицы в список, разделенный запятыми.
SELECT items.*, related1_ids, related2_ids, ...
FROM items
LEFT JOIN (
SELECT item_id, GROUP_CONCAT(related1_id) AS related1_ids
FROM item_has_related_1
WHERE item_id = $itemId
) AS r1 ON items.id = r1.item_id
LEFT JOIN (
SELECT item_id, GROUP_CONCAT(related2_id) AS related2_ids
FROM item_has_related_2
WHERE item_id = $itemId
) AS r2 ON items.id = r2.item_id
...
Позже вы можете разделить их на языке приложения.
Неудивительно, что у этого подхода примерно такие же показатели производительности, как у подхода UNION, но с результатом определенно легко работать, и этот запрос должно быть проще сгенерировать в коде. Я сделал две оптимизации - я использовал INNER JOIN и, поскольку items.id задан, я заменил значение в условиях ON. В php: ... ON r1.item_id = $itemId. Мне не приходило в голову сделать это раньше; Не уверен, что это ускорило что-нибудь, но точно не замедлит.
Если вы используете INNER JOIN, а элемент полностью отсутствует в одной из связанных таблиц, вы не получите никакого результата.
Код уже фильтрует его до r1.item_id = $itemId в подзапросе. На самом деле не имеет значения, что вы используете в предложении ON, они эквивалентны.
Понятно. Хорошая точка зрения. Примечание для себя: добавьте тесты с пустыми отношениями.
Мне кажется, что вы должны использовать не соединение
left, а внутреннее соединение. Не могли бы вы объяснить, почему вы его используете?