Левый Присоединиться к 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;

И посмотрим, что произойдет.