SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 26.12.2016 16:03:42

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Быстродейвие JOIN и LEFT JOIN

До недавнего времени был искренне убежден, что запросы вида

SELECT
  ...
FROM t1
JOIN t2 on t1.f1 = t2.f1

Выполняются если не быстрее, то хотя бы одинаково по времени с запросами
SELECT
  ...
FROM t1
LEFT JOIN t2 on t1.f1= t2.f1

Но опыт показал обратное. Первый запрос отрабатывается за 26 сек., второй за 2.
Этому есть объяснение? Меня даже больше не частный случай интересует, а общее понимание процесса, чтобы в дальнейшем на наступать на подобные грабли.

Отредактированно klow (26.12.2016 16:04:17)

Неактивен

 

#2 26.12.2016 16:30:05

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Быстродейвие JOIN и LEFT JOIN

у вас было правильное убеждение
опыт, указывает на то, что где-то грабли зарыты, например, в запросах отличается список выбираемых полей, в результате первый отдает сотни мегабайт, а второй десятки килобайт.

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

Неактивен

 

#3 26.12.2016 16:35:07

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Быстродейвие JOIN и LEFT JOIN

Меняю только LEFT и больше ничего не трогаю!
Запросы не привожу, так как они достаточно сложные и основаны на многих таблицах.

Отредактированно klow (26.12.2016 20:49:44)

Неактивен

 

#4 26.12.2016 17:42:52

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Быстродейвие JOIN и LEFT JOIN

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

Неактивен

 

#5 26.12.2016 18:37:08

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Быстродейвие JOIN и LEFT JOIN

Да, глянуть бы на explain обоих запросов. В частности, если подсунуть в join-запрос STRAIGHT_JOIN.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#6 26.12.2016 20:47:46

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Быстродейвие JOIN и LEFT JOIN

STRAIGHT_JOIN выполняется за 9 секунд, еще одна странность.
Планы запросов во вложении

Отредактированно klow (26.12.2016 20:59:31)


Прикрепленные файлы:
Attachment Icon Explain.xlsx, Размер: 20,169 байт, Скачано: 911

Неактивен

 

#7 26.12.2016 20:57:41

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Быстродейвие JOIN и LEFT JOIN

Есть еще одна странность. Когда запрос, с учетом параметров, обрабатывает меньше количество строк - выполняется медленнее чем на больших объемах данных.
Так для 15 тыс локаций он отрабатывает за 2 сек, для 3 локаций за 6. Результат для LEFT JOIN.

Неактивен

 

#8 27.12.2016 02:38:22

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Быстродейвие JOIN и LEFT JOIN

klow написал:

STRAIGHT_JOIN выполняется за 9 секунд, еще одна странность.

ничего странного:
left join -- 2 сек
join -- 26

STRAIGHT_JOIN похож на left, но все отличается, нужно больше подсказок оптимизатору (например, тех же STRAIGHT_JOIN)

я понимаю почему вы не стали выкладывать сами запросы smile
это вы такое руками пишите или они всё-таки генерятся?

ещё можно посмотреть на профайлинг, может там время расходуется на составление самого плана.

Неактивен

 

#9 27.12.2016 09:59:50

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Быстродейвие JOIN и LEFT JOIN

Пишу руками, но нужно учитывать, что 2 самых сложных запроса оформлены к качестве View и используются несколько раз в самом запросе. Иначе запрос перерос бы в невообразимого монстра.
Одна вьюха нужна для таблицы, которая ссылается сама на себя и там использую UNION до 8 раз. К сожалению MySql нет инструментов для работы с рекурсивными запросами. sad
Во второй вью  идет предварительная выборка нужных ID и дат, а после на ней-же использую конструкцию

SELECT ... FROM View v1 WHERE v1.id = (SELECT v2.ID FROM  view v2 WHERE v1.f1 = v2.f1 AND v2.Date BETWEEN D1 AND D2 ORDER v2.Date DESC LIMIT 1)
для выбора последней даты.
Поэтому такой сложный запрос.
Сейчас думаю, что неправильно сделал дав план всего запроса, нужно было бы только основные таблицы, вспомогательные не существенно влияют на запрос.
Поэтому наново привожу планы и профайлинг запроса удалив вспомогательные таблицы.
Странно, но разница времени выполнения между LEFT JOIN и JOIN уменьшилась, составила 5 и 15 сек. соответственно. Объяснить это я не могу.

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

Отредактированно klow (27.12.2016 10:29:53)


Прикрепленные файлы:
Attachment Icon Explain.xlsx, Размер: 17,976 байт, Скачано: 887

Неактивен

 

#10 27.12.2016 12:29:10

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Быстродейвие JOIN и LEFT JOIN

Отредактировал сообщение, так как написал изначально неправильно. Спасибо vasya за упоминание. Правильно так:
JOIN - поиск строк, соответствующих левой таблице по условию. LEFT JOIN - поиск строк, соответствующих левой таблице + строк левой таблицы, для которых в правой таблице соответствий нет. JOIN дает дополнительную гибкость оптимизатору, так как может выполняться и слева направо (как STRAIGHT_JOIN или LEFT JOIN) и справа налево. Если оптимизатор ошибается, то может выбирать неоптимальный вариант для JOIN.

Неактивен

 

#11 27.12.2016 12:39:15

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Быстродейвие JOIN и LEFT JOIN

То есть утверждение, что JOIN быстрее LEFT JOIN в общем случае не корректно. Все зависит от оптимизатора. Ок.
Но почему если "детей" меньше или нет вовсе время выполнения тоже увеличивается? Или оптимизатор и тут тоже строит разные планы запроса и, возможно, ошибается?

Отредактированно klow (27.12.2016 12:39:46)

Неактивен

 

#12 27.12.2016 13:10:33

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Быстродейвие JOIN и LEFT JOIN

klow написал:

Но почему если "детей" меньше или нет вовсе время выполнения тоже увеличивается? Или оптимизатор и тут тоже строит разные планы запроса и, возможно, ошибается?

Это в общем случае звучит странно, а в частном случае может произойти по ряду причин. Может быть оптимизатор строит план исполнения в другом порядке, предпочитает полный скан или еще что-то.

Неактивен

 

#13 27.12.2016 13:17:04

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Быстродейвие JOIN и LEFT JOIN

klow написал:

К сожалению MySql нет инструментов для работы с рекурсивными запросами. sad

Будут в MySQL 8 и MariaDB 10.2, а пока есть возможность имитировать обобщенные табличные выражения с помощью хранимой процедуры.
Посмотрите, может окажется полезным:
WITH RECURSIVE и MySQL

Неактивен

 

#14 27.12.2016 13:49:29

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Быстродейвие JOIN и LEFT JOIN

Как мне не нравится Oracle, но его операторы CONNECT BY и PRIOR мне бы очень упростили жизнь.
Статью читал, но не смог ее применить для своего случая.  Например как возвратить в SELECT результаты процедуры, когда возвращается много строк? Как в View сделать ссылку на саму себя?
Когда еще будут MySQL 8 и MariaDB 10.2, а работать нужно уже сегодня (вчера). Тем более, насколько я понял,  PRIOR даже не планируется в MySql (MariaDB).

Неактивен

 

#15 27.12.2016 21:45:28

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Быстродейвие JOIN и LEFT JOIN

klow написал:

Поэтому наново привожу планы и профайлинг запроса удалив вспомогательные таблицы.

1. Вы проверяли, что временные таблицы создаются в памяти, а не на диске?
2. На таких сложных запросах оптимизатор может легко ошибиться. Экспериментально, вы знаете более выгодный план и в случае JOIN нужно с помощью подсказок добиться такого же плана.

Неактивен

 

#16 28.12.2016 01:37:44

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Быстродейвие JOIN и LEFT JOIN

Исправил сообщение #10, а то в нем была путаница. Все дело только в плане запроса, иначе JOIN должен быть быстрее, чем LEFT JOIN.

Неактивен

 

#17 28.12.2016 09:22:51

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Быстродейвие JOIN и LEFT JOIN

vasya написал:

1. Вы проверяли, что временные таблицы создаются в памяти, а не на диске?

Не проверял. Подскажите, пожалуйста, как это сделать?

Неактивен

 

#18 28.12.2016 10:42:56

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Быстродейвие JOIN и LEFT JOIN

show global status like '%tmp%';

значение Created_tmp_disk_tables указывает на число временных таблиц, созданных на диске. Это происходит, когда размер таблицы превышает минимальную из переменных (tmp_table_size, max_heap_table_size). Емнип, text/blob тоже приводят к такому результату.

Чтобы проверить для конкретного запроса:
show status like '%tmp%';
select ... ;
show status like '%tmp%';

Неактивен

 

#19 28.12.2016 16:23:13

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Быстродейвие JOIN и LEFT JOIN

Спасибо!
До
Created_tmp_disk_tables    0
Created_tmp_files    676
Created_tmp_tables    126
После
Created_tmp_disk_tables    0
Created_tmp_files    676
Created_tmp_tables    137

Я так понял, что на диск временные таблицы не скидываются.

Неактивен

 

#20 28.12.2016 17:21:46

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Быстродейвие JOIN и LEFT JOIN

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

Неактивен

 

#21 28.12.2016 18:00:06

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Быстродейвие JOIN и LEFT JOIN

Спасибо! Еще раз проведу анализ с учетом предложений.

Неактивен

 

#22 28.12.2016 18:21:29

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Быстродейвие JOIN и LEFT JOIN

Вставлю и я свои 5 копеек smile.
Для хранения деревьев существует ведь несколько способов.
Подробнее рекомендую почитать в это книге (да, я нагло пиарю Билла Карвина smile ), там посвящена деревьям целая глава
http://www.ozon.ru/context/detail/id/7598812/
Раз Вам 8 уровней в глубину нужно - посмотрите различные методы хранения
    Adjacency List («список смежности»)
    Materialized Path («материализованный путь»)
    Nested Sets («вложенные множества»)
    Closure Table («таблица связей»)
Тут презентация от того же Карвина
http://www.slideshare.net/billkarwin/mo … hical-data


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#23 28.12.2016 20:51:25

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Быстродейвие JOIN и LEFT JOIN

Спасибо! Почитаю. Хотя уже много на эту тему читал статей.
Но рекурсия это смежная тема. В основном меня удивило, что left join выполняется быстрее обычного join.
8 уровней это сейчас, а вообще это переменная величина и зависит только от пользователей. 
Для меня сейчас более важно получить смежные строки. Вот проблема. На это в основном (90-97%) тратятся ресурсы, но, к сожалению, более приемлемого метода, чем описал выше, не нашел.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson