ВНУТРЕННЕЕ СОЕДИНЕНИЕ И ПРОИЗВОДИТЕЛЬНОСТЬ ЛЕВОГО СОЕДИНЕНИЯ в SQL Server
Я создал команду SQL, которая использует INNER JOIN для 9 таблиц, в любом случае эта команда занимает очень много времени (более пяти минут). Поэтому мой народ предлагает мне сменить INNER JOIN на LEFT JOIN, потому что выступление в LEFT JOIN лучше, вначале несмотря на то, что я знаю. После того, как я изменился, скорость запроса значительно улучшилась.
Я хотел бы знать, почему LEFT JOIN быстрее, чем INNER JOIN?
Моя команда SQL выглядит следующим образом:SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D и так далее
Обновление: это краткое из моей схемы.
FROM sidisaleshdrmly a -- NOT HAVE PK AND FK
INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK
ON a.CompanyCd = b.CompanyCd
AND a.SPRNo = b.SPRNo
AND a.SuffixNo = b.SuffixNo
AND a.dnno = b.dnno
INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine
ON a.CompanyCd = h.CompanyCd
AND a.sprno = h.AcctSPRNo
INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix
ON c.CompanyCd = h.CompanyCd
AND c.FSlipNo = h.FSlipNo
AND c.FSlipSuffix = h.FSlipSuffix
INNER JOIN coMappingExpParty d -- NO PK AND FK
ON c.CompanyCd = d.CompanyCd
AND c.CountryCd = d.CountryCd
INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd
ON b.CompanyCd = e.CompanyCd
AND b.ProductSalesCd = e.ProductSalesCd
LEFT JOIN coUOM i -- PK = UOMId
ON h.UOMId = i.UOMId
INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd
ON a.CompanyCd = j.CompanyCd
AND b.BFStatus = j.BFStatus
AND b.ProductSalesCd = j.ProductSalesCd
INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd
ON e.ProductGroup1Cd = g1.ProductGroup1Cd
INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd
ON e.ProductGroup1Cd = g2.ProductGroup1Cd
8 ответов
LEFT JOIN абсолютно не быстрее, чем INNER JOIN, На самом деле, это медленнее; по определению внешнее соединение (LEFT JOIN или же RIGHT JOIN) должен сделать всю работу INNER JOIN плюс дополнительная работа по расширению нуля результатов. Также ожидается, что будет возвращено больше строк, что приведет к дальнейшему увеличению общего времени выполнения просто из-за большего размера набора результатов.
(И даже если LEFT JOIN были более быстрыми в определенных ситуациях из-за некоторого сложного в представлении слияния факторов, это функционально не эквивалентно INNER JOINтак что вы не можете просто заменить все экземпляры одного на другой!)
Скорее всего, ваши проблемы с производительностью кроются в другом месте, например, при неправильной индексации ключа-кандидата или внешнего ключа. 9 таблиц - это довольно много, чтобы присоединиться, поэтому замедление может быть практически где угодно. Если вы опубликуете свою схему, мы сможем предоставить более подробную информацию.
Редактировать:
Размышляя далее об этом, я мог бы вспомнить одно обстоятельство, при котором LEFT JOIN может быть быстрее, чем INNER JOINи вот когда:
- Некоторые из таблиц очень маленькие (скажем, менее 10 строк);
- Таблицы не имеют достаточных индексов для покрытия запроса.
Рассмотрим этот пример:
CREATE TABLE #Test1
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Test1 (ID, Name) VALUES (1, 'One')
INSERT #Test1 (ID, Name) VALUES (2, 'Two')
INSERT #Test1 (ID, Name) VALUES (3, 'Three')
INSERT #Test1 (ID, Name) VALUES (4, 'Four')
INSERT #Test1 (ID, Name) VALUES (5, 'Five')
CREATE TABLE #Test2
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Test2 (ID, Name) VALUES (1, 'One')
INSERT #Test2 (ID, Name) VALUES (2, 'Two')
INSERT #Test2 (ID, Name) VALUES (3, 'Three')
INSERT #Test2 (ID, Name) VALUES (4, 'Four')
INSERT #Test2 (ID, Name) VALUES (5, 'Five')
SELECT *
FROM #Test1 t1
INNER JOIN #Test2 t2
ON t2.Name = t1.Name
SELECT *
FROM #Test1 t1
LEFT JOIN #Test2 t2
ON t2.Name = t1.Name
DROP TABLE #Test1
DROP TABLE #Test2
Если вы запустите это и просмотрите план выполнения, вы увидите, что INNER JOIN запрос действительно стоит больше, чем LEFT JOIN, потому что он удовлетворяет двум критериям выше. Это потому, что SQL Server хочет сделать хэш-совпадение для INNER JOIN, но делает вложенные циклы для LEFT JOIN; первый обычно намного быстрее, но так как количество строк очень мало и индекс не используется, операция хеширования оказывается самой дорогой частью запроса.
Вы можете увидеть тот же эффект, написав программу на вашем любимом языке программирования для выполнения большого количества поисков в списке из 5 элементов по сравнению с хэш-таблицей из 5 элементов. Из-за размера версия хеш-таблицы на самом деле медленнее. Но увеличьте его до 50 элементов или до 5000 элементов, и версия списка замедлится, потому что для хеш-таблицы это O(N) против O(1).
Но измените этот запрос на ID столбец вместо Name и вы увидите совсем другую историю. В этом случае он выполняет вложенные циклы для обоих запросов, но INNER JOIN version может заменить одно из сканирований кластеризованного индекса поиском - это означает, что это будет буквально на порядок быстрее при большом количестве строк.
Таким образом, заключение более или менее то, что я упомянул несколькими параграфами выше; почти наверняка это проблема индексации или охвата индекса, возможно, в сочетании с одной или несколькими очень маленькими таблицами. Это единственные обстоятельства, при которых SQL Server может иногда выбирать худший план выполнения для INNER JOIN чем LEFT JOIN,
Существует один важный сценарий, который может привести к тому, что внешнее соединение будет быстрее внутреннего, которое еще не обсуждалось.
При использовании внешнего объединения оптимизатор всегда может удалить внешнюю объединенную таблицу из плана выполнения, если столбцы объединения являются PK внешней таблицы, и ни один из столбцов не выбран из внешней таблицы. Например SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY B.KEY - это PK для B. Как Oracle (я полагаю, что я использовал выпуск 10), так и Sql Server (я использовал 2008 R2) удаляют таблицу B из плана выполнения.
То же самое не обязательно верно для внутреннего соединения: SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY может или не может требовать B в плане выполнения в зависимости от того, какие ограничения существуют.
Если A.KEY является обнуляемым внешним ключом, ссылающимся на B.KEY, то оптимизатор не может удалить B из плана, поскольку он должен подтвердить, что строка B существует для каждой строки A.
Если A.KEY является обязательным внешним ключом, ссылающимся на B.KEY, тогда оптимизатор может удалить B из плана, поскольку ограничения гарантируют существование строки. Но то, что оптимизатор может исключить таблицу из плана, вовсе не означает, что это произойдет. SQL Server 2008 R2 НЕ отбрасывает B из плана. Oracle 10 ДЕЛАЕТ B из плана. В этом случае легко увидеть, как внешнее объединение превзойдет внутреннее объединение на SQL Server.
Это тривиальный пример, а не практичный для отдельного запроса. Зачем присоединяться к столу, если вам это не нужно?
Но это может быть очень важным фактором при проектировании представлений. Часто создается представление "все", которое объединяет все, что может понадобиться пользователю, в отношении центральной таблицы. (Особенно, если наивные пользователи делают специальные запросы, которые не понимают реляционную модель). Представление может включать все соответствующие столбцы из многих таблиц. Но конечные пользователи могут получить доступ к столбцам только из подмножества таблиц в представлении. Если таблицы объединены с внешними объединениями, то оптимизатор может (и делает) отбрасывать ненужные таблицы из плана.
Очень важно убедиться, что представление с использованием внешних объединений дает правильные результаты. Как сказал Аарона, вы не можете слепо заменить ВНЕШНЕЕ СОЕДИНЕНИЕ на ВНУТРЕННЕЕ СОЕДИНЕНИЕ и ожидать того же результата. Но бывают случаи, когда это может быть полезно по соображениям производительности при использовании представлений.
И последнее замечание - я не проверял влияние на производительность в свете вышесказанного, но теоретически кажется, что вы должны быть в состоянии безопасно заменить INNER JOIN на OUTER JOIN, если вы также добавите условие
Если все работает так, как должно, не должно, но мы все знаем, что все работает не так, как должно, особенно когда речь идет об оптимизаторе запросов, кэшировании плана запросов и статистике.
Сначала я бы предложил перестроить индекс и статистику, а затем очистить кэш плана запросов, чтобы убедиться, что это не мешает. Однако у меня возникли проблемы, даже когда это было сделано.
Я сталкивался с некоторыми случаями, когда левое соединение было быстрее, чем внутреннее соединение.
Основная причина заключается в следующем: если у вас есть две таблицы и вы объединяетесь в столбце с индексом (в обеих таблицах). Внутреннее соединение будет давать тот же результат, независимо от того, циклически ли вы перебираете записи в индексе в таблице один и сопоставляете себя с индексом в таблице два, как если бы вы делали наоборот: зацикливание записей в индексе таблицы два и сопоставление с индексом в таблице один Проблема в том, что если у вас вводит в заблуждение статистику, оптимизатор запросов будет использовать статистику индекса, чтобы найти таблицу с наименее подходящими записями (на основе других ваших критериев). Если у вас две таблицы по 1 миллиону в каждой, в первой таблице у вас будет 10 совпадающих строк, а во второй - 100 000. Лучшим способом было бы выполнить сканирование индекса по первой таблице и сопоставить 10 раз по второй таблице. Обратным было бы сканирование индекса, которое повторяет более 100000 строк и пытается найти соответствие 100000 раз, и только 10 успешно выполнено. Поэтому, если статистика не верна, оптимизатор может выбрать неправильную таблицу и индекс для циклического перебора.
Если оптимизатор решит оптимизировать левое соединение в том порядке, в котором оно написано, оно будет работать лучше, чем внутреннее соединение.
НО оптимизатор также может оптимизировать левое соединение субоптимально как левое полусоединение. Чтобы сделать это, выберите тот, который вы хотите, вы можете использовать подсказку Force Order.
Попробуйте оба запроса (один с внутренним и левым соединением) с OPTION (FORCE ORDER) в конце и опубликуйте результаты. OPTION (FORCE ORDER) подсказка запроса, которая заставляет оптимизатор построить план выполнения с порядком соединения, указанным в запросе.
Если INNER JOIN начинает работать так быстро, как LEFT JOIN, это потому что:
- В запросе, составленном полностью
INNER JOINs, порядок соединения не имеет значения. Это дает оптимизатору запросов возможность упорядочивать объединения по своему усмотрению, поэтому проблема может зависеть от оптимизатора. - С
LEFT JOINЭто не так, потому что изменение порядка соединения изменит результаты запроса. Это означает, что механизм должен следовать порядку соединения, который вы указали в запросе, который может быть лучше, чем оптимизированный.
Не знаю, отвечает ли это на ваш вопрос, но я когда-то был в проекте, который представлял очень сложные запросы, делающие вычисления, которые полностью испортили оптимизатор. У нас были случаи, когда FORCE ORDER сократит время выполнения запроса с 5 минут до 10 секунд.
Внешние объединения могут предложить превосходную производительность при использовании в представлениях.
Допустим, у вас есть запрос, который включает представление, и это представление состоит из 10 таблиц, объединенных вместе. Скажем, ваш запрос использует только столбцы из 3 из этих 10 таблиц.
Если бы эти 10 таблиц были внутренне объединены, то оптимизатору запросов пришлось бы объединить их все, даже если самому запросу не требуется 7 из 10 таблиц. Это связано с тем, что сами внутренние объединения могут фильтровать данные, делая их необходимыми для вычислений.
Если бы вместо этого эти 10 таблиц были соединены внешне, то оптимизатор запросов фактически соединял бы только те, которые были необходимы: в данном случае 3 из 10. Это связано с тем, что сами объединения больше не фильтруют данные, и поэтому неиспользуемые объединения могут быть пропущены.
Источник: http://www.sqlservercentral.com/blogs/sql_coach/2010/07/29/poor-little-misunderstood-views/
Провел ряд сравнений между левым внешним и внутренним соединениями и не смог найти согласованной разницы. Есть много переменных. Я работаю над базой данных отчетов с тысячами таблиц, многие из которых содержат большое количество полей, много изменений со временем (версии поставщиков и локальный рабочий процесс). Невозможно создать все комбинации покрывающих индексов для удовлетворения потребностей такого широкого спектра запросов и обработки исторических данных. Мы видели, как внутренние запросы снижают производительность сервера, потому что две большие (от миллионов до десятков миллионов строк) таблицы объединяются внутри и тянут большое количество полей, а индекс покрытия отсутствует.
Самая большая проблема, кажется, не появляется в обсуждениях выше. Возможно, ваша база данных хорошо спроектирована с триггерами и хорошо спроектированной обработкой транзакций, чтобы обеспечить хорошие данные. Мой часто имеет значения NULL там, где они не ожидаются. Да, определения таблиц могут приводить в действие no-Null, но это не вариант в моей среде.
Таким образом, вопрос заключается в следующем: вы разрабатываете свой запрос только для скорости, более высокого приоритета для обработки транзакций, которая выполняет один и тот же код тысячи раз в минуту. Или вы стремитесь к точности, которую обеспечит левое внешнее соединение. Помните, что внутренние объединения должны находить совпадения с обеих сторон, поэтому неожиданный NULL не только удалит данные из двух таблиц, но, возможно, и целые строки информации. И это происходит так хорошо, без сообщений об ошибках.
Вы можете быть очень быстрыми, так как получаете 90% необходимых данных и не обнаруживает, что внутренние объединения молча удаляют информацию. Иногда внутренние объединения могут быть быстрее, но я не верю, что кто-то делает такое предположение, если они не рассмотрели план выполнения. Скорость важна, но точность важнее.
Скорее всего, ваши проблемы с производительностью связаны с количеством выполняемых вами объединений и наличием индексов в столбцах, к которым вы присоединяетесь.
В худшем случае вы могли бы легко сделать 9 сканирований всей таблицы для каждого соединения.
Я обнаружил что-то интересное в SQL-сервере, когда проверял, быстрее ли внутренние объединения, чем левые.
Если вы не включили элементы левой объединенной таблицы, в операторе выбора левое соединение будет быстрее, чем тот же запрос с внутренним соединением.
Если вы включите левую объединенную таблицу в оператор выбора, внутреннее соединение с тем же запросом будет равно или быстрее, чем левое соединение.
Из своих сравнений я обнаружил, что у них точно такой же план выполнения. Есть три сценария:
Если и когда они возвращают одинаковые результаты, у них одинаковая скорость. Однако мы должны иметь в виду, что это разные запросы, и что LEFT JOIN, возможно, вернет больше результатов (когда некоторые условия ON не выполняются) - поэтому обычно он медленнее.
Когда основная таблица (первая неконстантная таблица в плане выполнения) имеет ограничивающее условие (WHERE id =?) И соответствующее условие ON имеет значение NULL, "правая" таблица не присоединяется - это когда LEFT JOIN быстрее.
Как обсуждалось в пункте 1, обычно INNER JOIN более ограничен и возвращает меньше результатов, а значит, быстрее.
Оба используют (одинаковые) индексы.