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

[dmitryklerik]

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]

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

[Oriand]

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

habrahabr.ru/post/196692/#comment_6825020

[seriyPS]

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

[drdim]

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

[nalobin]

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

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

[Vumik]

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

[SamDark]

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

[ksusha]

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

[Oriand]

Да, делал эксперимент из двух таблиц 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]

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

[Oriand]

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

[luckyredhot]

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

[luckyredhot]

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

[kinitko]

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

[Oriand]

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

[kinitko]

Благодарю!

[armid]

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

[mc_dir]

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

…

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

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

[Oriand]

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

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

[alexeyrom]

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

[ajvol]

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

[Oriand]

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

[ajvol]

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

[Oriand]

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

[svetasmirnova]

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

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

[petropavel]

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

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

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