Comments 28
Mysql оптимизатор запросов почти всегда переписывал подзапросы внутри IN на JOIN-ы,
так-что не обязательно делать это за него.
Если интересно, переписанный запрос даже можно увидеть:
EXPLAIN EXTENDED ваш_запрос;
SHOW WARNINGS;
dev.mysql.com/doc/refman/5.5/en/subquery-optimization-with-exists.html
Что важно, такое поведение оптимизатора частенько приводило к тормозам, поэтому в версии Mysql 5.6 специально появились принудительная материализация подзапроса для борьбы с такой вредной оптимизацией:
dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
так-что не обязательно делать это за него.
Если интересно, переписанный запрос даже можно увидеть:
EXPLAIN EXTENDED ваш_запрос;
SHOW WARNINGS;
dev.mysql.com/doc/refman/5.5/en/subquery-optimization-with-exists.html
Что важно, такое поведение оптимизатора частенько приводило к тормозам, поэтому в версии Mysql 5.6 специально появились принудительная материализация подзапроса для борьбы с такой вредной оптимизацией:
dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
+4
Да, MySQL оптимизатор переписывает запросы, но на это он тратит немного ресурсов и всегда есть шанс, что его запрос не будет на 100% оптимальным. Так что если есть возможность помочь оптимизатору, то почему бы этого не сделать?
0
Несмотря на то, что MySQL пытается улучшить запрос, у него это не очень хорошо получается. Результаты своего эксперимента описал в этом комментарии
habrahabr.ru/post/196692/#comment_6825020
habrahabr.ru/post/196692/#comment_6825020
0
Немного релевантно первому пункту — очень полезная картинка:
+25
Вижу эту картинку на многих статьях к mysql. Ну и правильно!
0
Вот только full outer join не поддерживается в mysql. Но его можно заменить на пару left join с union.
А еще в документации рекомендуется использовать left join нежели right join для большей портабельности кода.
А еще в документации рекомендуется использовать left join нежели right join для большей портабельности кода.
+5
А ещё вот такая есть:
+1
Вопрос по первому примеру. Вы сравнивали производительность запросов с EXISTS и INNER JOIN? Проверять лень, но тут, имхо, сравнение будет в пользу EXISTS, ибо в запросе с INNER JOIN есть DISTINCT, для которого может понадобиться временная таблица.
+3
Да, делал эксперимент из двух таблиц t11 и t22, имеющие общую структуру:
Обе таблицы имеют примерно 200000 непоследовательных (хаотичных) записей.
В итоге:
Подзапрос и EXISTS отрабатывал за 13.7 секунды
JOIN за 3.2 секунды
CREATE TABLE `t11` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`t11` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=202981 DEFAULT CHARSET=latin1
Обе таблицы имеют примерно 200000 непоследовательных (хаотичных) записей.
В итоге:
Подзапрос и EXISTS отрабатывал за 13.7 секунды
JOIN за 3.2 секунды
0
«Tips & tricks for MySQL Developers», если уж быть совсем точным.
+1
Спасибо. А кто-нибудь знает о еще подобнымх познавательных статьях о SQL?
0
Могу порекомендовать мою предыдущую статью по MySQL. В ней тоже много интересных нюансов habrahabr.ru/post/166411/
+1
Для этих целей существует регламентированный SQL стандартом запрос, отрабатываемый всеми базами данных…
А не подскажите синтаксис для SQLITE?
Или может не везде таки работает?
0
Не могу точно ответить, т.к. под руками нет SQLite, но, в самом плохом случае, никто не мешает сделать так:
SELECT * FROM table1 WHERE (col1 || col2) = (SELECT col3 || col4 FROM table2)
-1
В MSSQL такая штука тоже работать не будет
0
А вот тут ты не прав. Когда работал с Microsoft SQL Server, то пару раз использовал такой синтаксис и тогда узнал о нём впервые.
0
Увы, не работает (MSSQL2012). В документации тоже ничего про это не упоминается msdn.microsoft.com/en-us/library/ms177682(v=sql.110).aspx
0
Причём, мы знаем, что в MySQL join запросы отрабатывают быстрее за счёт эффективного построения плана выполнения запроса оптимизатором
Это чаще всего так, но не всегда. Особенно в последних версиях. Поэтому стоит добавить ещё один пункт: тестируйте!
0
Переписывать подзапросы в join-ы это из серии вредных советов. Или надо явно говорить, для какой версии это применимо.
Начиная с 5.6 (а в MariaDB с 5.3) оптимизатор знает кучу способов выполнить IN подзапрос. И только один из них — переписать в форму join-а. А другие — материализация, semi-join и еще более хитрые трюки. Оптимизатор выбирает самый из них быстрый, оценивая их «стоимости». Так что перетисывать IN-подзапрос в join уже нельзя, так как это сильно ограничивает возможности оптимизатора по выбору оптимального плана.
Хотя… в последней версии MariaDB оптимизатор уже умеет переписывать некоторые EXISTS подзапросы в IN. Может когда-нибудь он и join-ы научится в подзапросы переписывать. Тогда будет опять можно.
Начиная с 5.6 (а в MariaDB с 5.3) оптимизатор знает кучу способов выполнить IN подзапрос. И только один из них — переписать в форму join-а. А другие — материализация, semi-join и еще более хитрые трюки. Оптимизатор выбирает самый из них быстрый, оценивая их «стоимости». Так что перетисывать IN-подзапрос в join уже нельзя, так как это сильно ограничивает возможности оптимизатора по выбору оптимального плана.
Хотя… в последней версии MariaDB оптимизатор уже умеет переписывать некоторые EXISTS подзапросы в IN. Может когда-нибудь он и join-ы научится в подзапросы переписывать. Тогда будет опять можно.
0
Sign up to leave a comment.
Tips & tricks for MySQL Developers. Работа с SQL