Производительность внутреннего соединения по сравнению с перекрестным соединением

Эффект выдачи внутреннего соединения такой же, как и указание перекрестного соединения с условием объединения в предложении WHERE. Я заметил, что многие люди в моей компании используют кросс-соединения, где я буду использовать внутренние соединения. Я не заметил значительного выигрыша в производительности после изменения некоторых из этих запросов и задавался вопросом, было ли это просто совпадением или СУБД прозрачно анализирует такие проблемы (MySql в нашем случае). И вот конкретный пример для обсуждения:

SELECT User.*
FROM User, Address
WHERE User.addressId = Address.id;

SELECT User.*
FROM User
INNER JOIN Address ON (User.addressId = Address.id);

Ответ 1

Кросс-соединения создают результаты, которые состоят из каждой комбинации строк из двух или более таблиц. Это означает, что если таблица A имеет 6 строк, а таблица B имеет 3 строки, перекрестное соединение приведет к 18 строкам. Между двумя таблицами нет никакой связи - вы буквально просто производите все возможные комбинации.

С внутренним соединением значения столбцов из одной строки таблицы объединяются со значениями столбцов из другой строки другой (или той же) таблицы, чтобы сформировать одну строку данных.

Если предложение WHERE добавляется к перекрестному соединению, оно ведет себя как внутреннее соединение, поскольку WHERE накладывает ограничивающий фактор.

Пока ваши запросы соответствуют стандартным рекомендациям , которые мне нравятся, мне нравится думать о решении о том, какой тип соединения использовать, чтобы быть простым делом вкуса.

Ответ 2

Нет никакой разницы, кроме внутреннего соединения, намного яснее, потому что он определяет объединение, оставляя условие where фактическим предельным условием.

Ответ 3

Используйте EXPLAIN, чтобы просмотреть план запроса для обоих запросов и посмотреть, есть ли разница. Вполне возможно, что MySQL будет использовать один и тот же план выполнения в обоих случаях. Я использую синтаксис INNER JOIN, потому что он намного яснее.

Ответ 4

Я нахожу, что рабочие места, которые допускают первый синтаксис (таблицы с разделителями-запятыми), имеют тенденцию иметь значительное время, чтобы отлаживать случаи, когда больше строк возвращено, чем предполагалось. Непреднамеренные кросс-соединения - это проклятие системы и могут донести до него даже самую хорошо настроенную базу данных. Это привело к тому, что наша система pre-prod дошла до визга по крайней мере в два раза за последний год.

Второй синтаксис (синтаксис соединения) заставляет писателя думать о том, как сначала объединяются таблицы, а затем возвращают только интересные строки. Невозможно случайно выполнить кросс-соединение, используя этот синтаксис, и, следовательно, опасность случайных неудачных запросов уменьшается.

Однако в этом вопросе я никогда не замечал разницы в скорости между двумя синтаксисами в любых системах, которые у меня были.

Ответ 5

Первый пример функционально совпадает со вторым примером. Однако этого синтаксиса следует избегать по нескольким причинам. Во-первых, намного проще случайно получить кросс-соединение при использовании этого синтаксиса, особенно когда в таблице есть объединение mulitple. Если вы видите много такого типа запроса с ключевым словом, у вас, вероятно, есть кто-то, кто пытается исправить кросс-соединения.

Далее синтаксис левого и правого соединений с использованием старого стиля устарел и больше не будет поддерживаться. Кроме того, он все равно работает неправильно. Иногда он неправильно истолковывает внешнее соединение и отправляет обратно неправильный набор результатов. Поэтому любые запросы, которые вы используете = или = в предложении where, должны быть немедленно заменены.

В-третьих, стандартные соединения ANSI легче понять и поддерживать. Понимание объединений является одним из самых важных базовых навыков, которые должны иметь лица, которые обращаются к любой реляционной базе данных. По моему опыту, некоторые люди, которые используют старый стиль, не понимают друг друга и как они работают, и поэтому пишут запросы, которые фактически не делают то, что они намеревались.

Ответ 6

Порядок, в котором вы присоединяетесь к таблицам, или вы устанавливаете условия ON/WHERE, не имеет значения.

Оптимизатор запросов должен оптимизировать и использовать наилучший порядок в любом случае (и выбирать, как наилучшим образом фильтровать данные, с чего начать и т.д.)

Как и многие другие, я предлагаю использовать синтаксис INNER JOIN, поскольку он делает вещи более читабельными, он более прозрачен с синтаксисом объединений LEFT или FULL.

Здесь есть несколько более обильный текст: http://linus.brimstedt.se/?/article/articleview/SQL Синтаксис

/Б

Ответ 7

Еще одно преимущество первого синтаксиса - вы можете быть более общим в своем предельном состоянии. Не только равенство.

Но если вы используете равенство, зачем доверять оптимизатору? Убедитесь, что он не будет сначала генерировать перекрестное соединение, а затем устранить строки. Используйте второй.

Ответ 8

SQL Server сказал: "Когда WHERE превращает Cross Join в Inner Join", поэтому нет разницы. http://msdn.microsoft.com/en-us/library/ms190690.aspx

Я сделал SQL-сервер "План выполнения". Производительность такая же.

Ответ 9

Объяснение обоих запросов дает одинаковый вывод

mysql> explain select * from t T1, t T2 where T1.ID=T2.ID;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | T1    | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | T2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set (0.00 sec)

mysql> explain select * from t T1  join t T2 on T1.ID=T2.ID;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | T1    | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | T2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set (0.00 sec)

Но использование внутреннего синтаксиса соединения предпочтительнее, чем его более четкое и точное. Mysql может самостоятельно настраивать запросы левого и правого соединения для выбора меньшего количества данных по сравнению с Cross Join.

Ответ 10

С начала времени оптимизаторы строятся вокруг классического синтаксиса синтаксиса с ограничением-проектом-картезианством. Практически все продавцы скопировали дизайн, впервые внедренный в System R. Тогда, неохотно, вендоры приняли "последний и самый большой" синтаксис ANSI и доработали свои механизмы выполнения SQL. Вопреки тому, что маркетинговая брошюра может вам рассказать ( "используйте последний синтаксис" ), незначительно изменился уровень физической реализации: все еще [проиндексированы] вложенные петли, хеширование или объединение слияния. Поэтому нет оснований полагаться на превосходство одного синтаксиса над другим.

По моему личному вкусу новый синтаксис избыточный, шумный и несовместимым. Что касается санкции комитета, "зайдите в любой парк в каждом городе, и вы не найдете статуи комитета".