Oriand Oct 7 2013 at 22:25

Tips & tricks for MySQL Developers. Работа с SQL

10 min

51K

MySQL*

Tutorial

+63

Comments 28

dmitryklerik Oct 8 2013 at 00:01

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

Oriand Oct 8 2013 at 14:44

Да, MySQL оптимизатор переписывает запросы, но на это он тратит немного ресурсов и всегда есть шанс, что его запрос не будет на 100% оптимальным. Так что если есть возможность помочь оптимизатору, то почему бы этого не сделать?

Oriand Oct 8 2013 at 15:28

Несмотря на то, что MySQL пытается улучшить запрос, у него это не очень хорошо получается. Результаты своего эксперимента описал в этом комментарии

habrahabr.ru/post/196692/#comment_6825020

seriyPS Oct 8 2013 at 00:17

Немного релевантно первому пункту — очень полезная картинка:

+25

drdim Oct 8 2013 at 07:03

Вижу эту картинку на многих статьях к mysql. Ну и правильно!

nalobin Oct 8 2013 at 07:47

Вот только full outer join не поддерживается в mysql. Но его можно заменить на пару left join с union.

А еще в документации рекомендуется использовать left join нежели right join для большей портабельности кода.

psyX Oct 8 2013 at 09:25

а кто-то пользуется right join?

Vumik Oct 8 2013 at 20:22

Вообще RIGHT JOIN оптимизатор преобразует перед выполнением к LEFT JOIN, поэтому лучше и писать сразу LEFT JOIN

SamDark Oct 8 2013 at 12:32

А ещё вот такая есть:

ksusha Oct 8 2013 at 00:53

Вопрос по первому примеру. Вы сравнивали производительность запросов с EXISTS и INNER JOIN? Проверять лень, но тут, имхо, сравнение будет в пользу EXISTS, ибо в запросе с INNER JOIN есть DISTINCT, для которого может понадобиться временная таблица.

Oriand Oct 8 2013 at 15:06

Да, делал эксперимент из двух таблиц t11 и t22, имеющие общую структуру:

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 секунды

luckyredhot Oct 8 2013 at 12:06

«Tips & tricks for MySQL Developers», если уж быть совсем точным.

Oriand Oct 8 2013 at 14:46

Спасибо за идею! Изменил.

luckyredhot Oct 8 2013 at 14:49

Спасибо, что учли :)

luckyredhot Oct 8 2013 at 17:18

Кстати, было бы классно, если бы что-то подобное и для MySQL админов опубликовали. Достаточно мало cheetsheets и howto встречалось мне.

kinitko Oct 8 2013 at 14:14

Спасибо. А кто-нибудь знает о еще подобнымх познавательных статьях о SQL?

Oriand Oct 8 2013 at 14:48

Могу порекомендовать мою предыдущую статью по MySQL. В ней тоже много интересных нюансов habrahabr.ru/post/166411/

kinitko Oct 8 2013 at 16:58

Благодарю!

armid Oct 14 2013 at 21:13

Если вас когда-то посетит мысль писать ли еще на эту тему, сомнения прочь — пишите!

mc_dir Oct 8 2013 at 14:48

Для этих целей существует регламентированный SQL стандартом запрос, отрабатываемый всеми базами данных

…

А не подскажите синтаксис для SQLITE?

Или может не везде таки работает?

Oriand Oct 8 2013 at 14:56

Не могу точно ответить, т.к. под руками нет SQLite, но, в самом плохом случае, никто не мешает сделать так:

SELECT * FROM table1 WHERE (col1 || col2) = (SELECT col3 || col4 FROM table2)

-1

alexeyrom Oct 8 2013 at 19:21

Неправильность результата (например, col1=col4='a', col2=col3='') и невозможность использования индексов мешают.

ajvol Oct 8 2013 at 15:31

В MSSQL такая штука тоже работать не будет

Oriand Oct 8 2013 at 15:46

А вот тут ты не прав. Когда работал с Microsoft SQL Server, то пару раз использовал такой синтаксис и тогда узнал о нём впервые.

ajvol Oct 9 2013 at 22:14

Увы, не работает (MSSQL2012). В документации тоже ничего про это не упоминается msdn.microsoft.com/en-us/library/ms177682(v=sql.110).aspx

Oriand Oct 10 2013 at 00:31

Давно не работал с SQL Server, сейчас даже попробовать не на чем. Порывшись в интернете понял, что мои знания из Oracle, который изучал некоторое время назад.
Признаю свою ошибку и правлю фразу. Всем спасибо за бдительность :-)

svetasmirnova Oct 8 2013 at 18:06

Причём, мы знаем, что в MySQL join запросы отрабатывают быстрее за счёт эффективного построения плана выполнения запроса оптимизатором

Это чаще всего так, но не всегда. Особенно в последних версиях. Поэтому стоит добавить ещё один пункт: тестируйте!

petropavel Oct 11 2013 at 05:46

Переписывать подзапросы в join-ы это из серии вредных советов. Или надо явно говорить, для какой версии это применимо.

Начиная с 5.6 (а в MariaDB с 5.3) оптимизатор знает кучу способов выполнить IN подзапрос. И только один из них — переписать в форму join-а. А другие — материализация, semi-join и еще более хитрые трюки. Оптимизатор выбирает самый из них быстрый, оценивая их «стоимости». Так что перетисывать IN-подзапрос в join уже нельзя, так как это сильно ограничивает возможности оптимизатора по выбору оптимального плана.

Хотя… в последней версии MariaDB оптимизатор уже умеет переписывать некоторые EXISTS подзапросы в IN. Может когда-нибудь он и join-ы научится в подзапросы переписывать. Тогда будет опять можно.

Show the best of all time