В пятницу я выступил с докладом « Шаблоны запросов MySQL, оптимизированные » для веб-семинаров Percona MySQL . Если вы пропустили это, вы все равно можете зарегистрироваться, чтобы просмотреть запись и мои слайды.
Спасибо всем, кто присутствовал, и особенно людям, которые задавали замечательные вопросы. Я ответил столько, сколько у нас было времени во время сессии, но вот все вопросы с моими полными ответами:
Вопрос: Можете ли вы сравнить использование подзапросов / множественных объединений и нескольких запросов (например, временных таблиц)?
Для производительности сложно составить общее правило, потому что существуют разные типы подзапросов (коррелированные, некоррелированные, производные таблицы, скалярные подзапросы), и мы видели в моей презентации, что иногда они работают хуже, а иногда лучше, чем другое эквивалентное решение.
Если вы говорите об использовании временной таблицы для хранения промежуточного результирующего набора, а затем используете ее для конечного результата, который вы хотите, это еще одна техника, которую я вижу используемой, хотя она менее распространена. Может быть полезно упростить разработку отчета, но вопрос о том, является ли это преимуществом для производительности, зависит от конкретного случая.
Существуют и другие недостатки использования временных таблиц таким образом, например, при использовании репликации временная таблица может исчезнуть, если ведомый перезапустится, а затем последующие запросы к временной таблице завершатся неудачно.
В: Почему оптимизатор запросов не заботится о решениях по выполнению? Поскольку все они логически одинаковы, я считаю, что все это должно привести к одному и тому же плану выполнения. (Я знаю, что это не так.)
Почему бы вам не попробовать написать оптимизатор SQL-запросов и посмотреть, насколько это просто? 😉 Генеративные грамматики, такие как SQL, могут создавать бесконечное множество запросов. Оптимизатор запросов анализирует запросы для общих шаблонов, которые, как знают разработчики MySQL, могут быть улучшены. Но на разработку и тестирование оптимизатора запросов уходит определенное количество инженерных лет. Разработчики должны принять решение о том, какие шаблоны запросов распознает оптимизатор, а какие оставлены разработчику для рефакторинга вручную. Также могут быть случаи, когда автоматическая оптимизация запроса обходится дороже, чем просто выполнение запроса в неоптимальной форме.
Вопрос: Разве решение первичного ключа для случайного выбора не работает только тогда, когда идентификаторы для фильмов равномерно распределены по диапазону 1. МАКС (идентификатор)?
Да, существует риск нерегулярного распределения совпадающих строк, что приведет к искаженным результатам. Например, если промежуток между 47000 и 50000 идентификаторов отсутствует, а фильмы отсутствуют, но генератор случайных чисел выбирает значения в этом диапазоне с такой же частотой, как и в любом другом диапазоне, то фильм, следующий сразу за «разрывом», будет выбираться чаще.
Почти каждое решение для случайного выбора строк включает в себя некоторый компромисс либо по производительности, либо по точности случайности. Известно, что решение ORDER BY RAND () имеет низкую производительность, но возвращает лучший случайный выбор.
Другой обходной путь может заключаться в добавлении столбца в таблицу и заполнении строк, которые вы хотите выбрать (в данном случае, фильмов), значениями, которые, как известно, являются последовательными. Затем вы можете выбрать случайное значение, найти строку с «=» вместо «>» и гарантированно найти ровно одно совпадение. Но компромисс этого решения заключается в том, что оно требует сохранения другого столбца и повторной инициализации последовательности после выполнения определенных операций вставки / обновления / удаления. Если у вас есть набор данных, который редко меняется, то это может быть хорошим компромиссом.
Q: Сравнение кортежей не зависит от порядка сортировки? Не было бы проблемой, если бы ключевые слова были возвращены в порядке, отличном от того, который вы указали в запросе?
Сравнение кортежей, на которое вы ссылаетесь, это пример:
WHERE (k1.keyword, k2.keyword, k3.keyword) = ('espionage', 'nuclear-bomb', 'ejector-seat');
Это нормально, потому что каждое имя корреляции (k1, k2, k3) является поиском по индексу. Посмотрите на вывод EXPLAIN — он сначала обращается к ним, просматривая указанные вами ключевые слова. Неважно, в каком порядке вы указываете ключевые слова в этом кортеже, это эквивалентно этому выражению:
WHERE k1.keyword = 'espionage' AND k2.keyword = 'nuclear-bomb' AND k3.keyword = 'ejector-seat';
Оператор AND является коммутативным, поэтому порядок этих членов не имеет значения.
Однако если бы я использовал синтаксис кортежа для поиска значений по нескольким столбцам в составном индексе, то порядок имел бы значение, и он должен совпадать с порядком столбцов в индексе. Пример:
WHERE (last_name, first_name) = ('Karwin', 'Bill');
В: На Dynamic Pivot, прямом соединении, вы можете подробнее объяснить, почему вы думали сделать это и почему это помогло?
Когда я не использовал STRAIGHT_JOIN, оптимизатор запросов переупорядочил таблицы. Казалось, что сначала предпочтительнее сканирование индекса из 7 строк в таблице `kind_type`, а затем поиск соответствующих строк в` title` по вторичному индексу. Но в результате была создана временная таблица для подсчета фильмов за год производства для каждого вида kind_id.
В этом случае было бы более эффективно заставить MySQL сначала сканировать таблицу `title`, группируя по kind_id в порядке индекса. Это заставило первую таблицу в EXPLAIN выглядеть так, как будто она сканировала больше строк. Но, избегая временную таблицу и просматривая таблицу `kind_types` по первичному ключу, результатом был запрос, который занимал половину времени.
Важным выводом моей сегодняшней презентации является то, что иногда вы обнаруживаете неожиданные различия в производительности, как это, поэтому стоит протестировать все различные решения и измерить полученную производительность!
Вопрос: Билл, вернемся к запросу, по которому вы пытались определить последний эпизод каждого телешоу. Не могли бы вы использовать SUBQUERY для получения и сравнения MAX (episode_id) в качестве опции?
Да, я думаю, что вы имеете в виду то, что я описал как решение для производных таблиц на слайдах 55-59. Извиняюсь, если это не было ясно, потому что я не представил детали всех таблиц и столбцов. Оказалось, что это решение было в 150 раз быстрее для этого случая, поэтому ваше предложение является хорошим!
Еще раз спасибо за участие в моем вебинаре! Вот еще несколько советов:
- Проверьте предстоящие учебные классы Percona в Северной Америке и Европе.
- Присоединяйтесь к Percona и сообществу MySQL на наших мероприятиях. Следующий состоится в Лондоне 11-12 ноября 2013 года . Мы также с нетерпением ожидаем конференции и выставки Percona Live MySQL в Санта-Кларе, Калифорния, которая начнется 31 марта 2014 года .
- Смотрите больше вебинаров от Percona в будущем!