Эффективность SQL: ГДЕ В подзапросе или JOIN, затем GROUP
В качестве примера, я хочу получить список всех элементов с определенными тегами, применяемыми к ним. Я мог бы выполнить одно из следующих действий:
SELECT Item.ID, Item.Name
FROM Item
WHERE Item.ID IN (
SELECT ItemTag.ItemID
FROM ItemTag
WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55)
или
SELECT Item.ID, Item.Name
FROM Item
LEFT JOIN ItemTag ON ItemTag.ItemID = Item.ID
WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55
GROUP BY Item.ID, Item.Name
Или что-то совсем другое.
В целом (при условии, что существует общее правило), какой более эффективный подход?
Ответы
Ответ 1
SELECT Item.ID, Item.Name
FROM Item
WHERE Item.ID IN (
SELECT ItemTag.ItemID
FROM ItemTag
WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55)
или
SELECT Item.ID, Item.Name
FROM Item
LEFT JOIN ItemTag ON ItemTag.ItemID = Item.ID
WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55
GROUP BY Item.ID
Второй запрос не будет компилироваться, поскольку он ссылается на Item.Name
без группировки или агрегации на нем.
Если мы удалим GROUP BY
из запроса:
SELECT Item.ID, Item.Name
FROM Item
JOIN ItemTag
ON ItemTag.ItemID = Item.ID
WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55
это все еще разные запросы, если только ItemTag.ItemId
не является UNIQUE
и помечен как таковой.
SQL Server
способен обнаруживать условие IN
в столбце UNIQUE
и просто преобразует условие IN
в JOIN
.
Если ItemTag.ItemId
не UNIQUE
, первый запрос будет использовать своего рода алгоритм SEMI JOIN
, которые достаточно эффективны в SQL Server
.
Вы можете преобразовать второй запрос в JOIN
:
SELECT Item.ID, Item.Name
FROM Item
JOIN (
SELECT DISTINCT ItemID
FROMT ItemTag
WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55
) tags
ON tags.ItemID = Item.ID
но эта мелочь менее эффективна, чем IN
или EXISTS
.
См. эту статью в своем блоге для более детального сравнения производительности:
Ответ 2
Я думаю, что это будет зависеть от того, как оптимизатор справится с ними, возможно, даже в случае, если вы закончите с той же производительностью. Отобразить план выполнения - ваш друг здесь.
Ответ 3
SELECT Item.ID, Item.Name
...
GROUP BY Item.ID
Это недопустимый T-SQL. Item.Name должно появляться в предложении group by или внутри агрегатной функции, такой как SUM или MAX.
Ответ 4
Это практически невозможно (если вы не из тех сумасшедших администраторов-гуру), чтобы сказать, что будет быстро и что не будет, не глядя на план выполнения и/или выполняя стресс-тесты.
Ответ 5
запустите это:
SET SHOWPLAN_ALL ON
затем запустите каждую версию запроса
вы можете увидеть, вернут ли они тот же план, и если не посмотреть TotalSubtreeCost в первой строке каждого и посмотреть, насколько они отличаются.
Ответ 6
Производительность всегда, кажется, получает голос, но вы также слышите "дешевле покупать оборудование, чем программисты"
Вторая победа в производительности.
Иногда приятно смотреть на SQL и знать цель, но для чего нужны комментарии. Первый запрос использует другую таблицу для фильтра - довольно прямолинейно.
Второй из них будет иметь больше смысла (с точки зрения понимания, а не производительности), используя различные, а не групповые. Я бы ожидал, что некоторые агрегаты будут в списке, но их нет. Скорость убивает.
Ответ 7
Второй вариант более эффективен в MySQL. MySQL будет повторно выполнять запрос в инструкции IN для каждого теста условия WHERE.