Левый Присоединиться к Inner Join?
Я занимаюсь профилированием некоторых запросов в приложении, над которым я работаю, и натолкнулся на запрос, который извлекал больше строк, чем необходимо, и набор результатов был обрезан в коде приложения.
Изменение LEFT JOIN в INNER JOIN подрезало результирующий набор только тем, что было необходимо, и, по-видимому, также было бы более результативным (поскольку выбрано меньшее количество строк). В действительности, запрос LEFT JOIN's превосходил INNER JOIN'ed, в течение половины времени для завершения.
LEFT JOIN: (127 полных строк, запрос занял 0,0011 сек)
INNER JOIN: (10 полных строк, запрос занял 0,0024 сек)
(Я запускал запросы несколько раз, и это средние значения).
Запуск EXPLAIN на обоих не показывает ничего, что объясняет различия в производительности:
Для INNER JOIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE contacts index NULL name 302 NULL 235 Using where
1 SIMPLE lists eq_ref PRIMARY PRIMARY 4 contacts.list_id 1
1 SIMPLE lists_to_users eq_ref PRIMARY PRIMARY 8 lists.id,const 1
1 SIMPLE tags eq_ref PRIMARY PRIMARY 4 lists_to_users.tag_id 1
1 SIMPLE users eq_ref email_2 email_2 302 contacts.email 1 Using where
Для LEFT JOIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE contacts index NULL name 302 NULL 235 Using where
1 SIMPLE lists eq_ref PRIMARY PRIMARY 4 contacts.list_id 1
1 SIMPLE lists_to_users eq_ref PRIMARY PRIMARY 8 lists.id,const 1
1 SIMPLE tags eq_ref PRIMARY PRIMARY 4 lists_to_users.tag_id 1
1 SIMPLE users eq_ref email_2 email_2 302 contacts.email 1
И сам запрос:
SELECT `contacts`.*, `lists`.`name` AS `group`, `lists`.`id` AS `group_id`, `lists`.`shared_yn`, `tags`.`name` AS `context`, `tags`.`id` AS `context_id`, `tags`.`color` AS `context_color`, `users`.`id` AS `user_id`, `users`.`avatar`
FROM `contacts`
LEFT JOIN `lists` ON lists.id=contacts.list_id
LEFT JOIN `lists_to_users` ON lists_to_users.list_id=lists.id AND lists_to_users.user_id='1' AND lists_to_users.creator='1'
LEFT JOIN `tags` ON tags.id=lists_to_users.tag_id
INNER JOIN `users` ON users.email=contacts.email
WHERE (contacts.user_id='1')
ORDER BY `contacts`.`name` ASC
(Предложение, о котором я говорю, это последний INNER JOIN в таблице "users" )
Запрос выполняется в базе данных MySQL 5.1, если это имеет значение.
Кто-нибудь знает, почему LEFT JOIN'ed запрос превосходит INNER JOIN'ed в этом случае?
ОБНОВЛЕНИЕ: Из-за предложения Томалака, что небольшие таблицы, которые я использую, делали INNER JOIN более сложными, я создал тестовую базу данных с некоторыми макетными данными. Таблица "пользователей" - 5000 строк, а таблица контактов - 500 000 строк. Результаты одинаковы (также тайминги не изменились, что удивительно, если учесть, что таблицы теперь намного больше).
Я также использовал ANALYZE и OPTIMIZE в таблице контактов. Не было различимых различий.
Ответы
Ответ 1
Если вы считаете, что реализация LEFT JOIN - INNER JOIN + больше работы, то этот результат запутан. Что делать, если реализация INNER JOIN (LEFT JOIN + filtering)? Ах, теперь ясно.
В планах запросов единственная разница заключается в следующем: users... extra: using where. Это означает фильтрацию. Там дополнительный шаг фильтрации в запросе с внутренним соединением.
Это другой вид фильтрации, который обычно используется в предложении where. Простое создание индекса на A для поддержки этого действия фильтрации.
SELECT *
FROM A
WHERE A.ID = 3
Рассмотрим этот запрос:
SELECT *
FROM A
LEFT JOIN B
ON A.ID = B.ID
WHERE B.ID is not null
Этот запрос эквивалентен внутреннему соединению. В B нет индекса, который поможет этому фильтрующему действию. Причина в том, что предложение where указывает условие на результат соединения, а не условие на B.
Ответ 2
Вероятно, из-за того, что INNER JOIN должен проверить каждую строку в обеих таблицах, чтобы увидеть, соответствуют ли значения столбца (адрес электронной почты в вашем случае). LEFT JOIN будет возвращать все из одной таблицы независимо. Если он проиндексирован, то он будет знать, что делать быстрее тоже.
Ответ 3
Эффективность таблицы влияет на оптимизатор запросов. Я предполагаю, что небольшие таблицы, поскольку вы сделали внутреннее соединение более сложной операцией. Как только у вас будет больше записей, чем сервер БД хочет сохранить в памяти, внутреннее соединение, вероятно, начнет превосходить левое соединение.
Ответ 4
imo вы попадаете в ловушку, известную как преждевременная оптимизация. Оптимизаторы запросов безумно непостоянны. Мое предложение состоит в том, чтобы двигаться дальше, пока вы не сможете точно определить, что конкретное соединение проблематично.
Ответ 5
Попробуйте следующее:
SELECT `contacts`.*, `lists`.`name` AS `group`, `lists`.`id` AS `group_id`, `lists`.`shared_yn`, `tags`.`name` AS `context`, `tags`.`id` AS `context_id`, `tags`.`color` AS `context_color`, `users`.`id` AS `user_id`, `users`.`avatar`
FROM `contacts`
INNER JOIN `users` ON contacts.user_id='1' AND users.email=contacts.email
LEFT JOIN `lists` ON lists.id=contacts.list_id
LEFT JOIN `lists_to_users` ON lists_to_users.user_id='1' AND lists_to_users.creator='1' AND lists_to_users.list_id=lists.id
LEFT JOIN `tags` ON tags.id=lists_to_users.tag_id
ORDER BY `contacts`.`name` ASC
Это должно дать вам дополнительную производительность, потому что:
- Вы помещаете все внутренние соединения перед появлением какого-либо "левого" или "правильного" соединения. Это отфильтровывает некоторые записи перед применением последующих внешних соединений
- Короткое замыкание операторов "И" (порядок "И" имеет значение). Если сравнение столбцов и литералов ложно, оно не будет выполнять требуемое сканирование таблицы для сравнения между таблицами PK и FKs
Если вы не найдете улучшения в производительности, замените весь набор колонок на "COUNT (*)" и выполните свои левые/внутренние тесты. Таким образом, независимо от запроса, вы получите только одну строку с 1 единственным столбцом (счетчиком), поэтому вы можете отказаться от того, что количество возвращенных байтов является причиной медленности вашего запроса:
SELECT COUNT(*)
FROM `contacts`
INNER JOIN `users` ON contacts.user_id='1' AND users.email=contacts.email
LEFT JOIN `lists` ON lists.id=contacts.list_id
LEFT JOIN `lists_to_users` ON lists_to_users.user_id='1' AND lists_to_users.creator='1' AND lists_to_users.list_id=lists.id
LEFT JOIN `tags` ON tags.id=lists_to_users.tag_id
Удачи.
Ответ 6
LEFT JOIN возвращает больше строк, чем INNER JOIN, потому что эти 2 разные.
Если LEFT JOIN не находит соответствующую запись в таблице, которую она ищет, она вернет NULL для таблицы.
Но если INNER JOIN не находит связанную запись, она не вернет строку целиком вообще.
Но на ваш вопрос, у вас включен query_cache?
Попробуйте запустить запрос с помощью
SELECT SQL_NO_CACHE `contacts`.*, ...
Кроме этого, я бы заполнил таблицы большим количеством данных, запустил
ANALYZE TABLE t1, t2;
OPTIMIZE TABLE t1, t2;
И посмотрим, что произойдет.