Руководство по SQL: Как лучше писать запросы (часть 2)

Перевод статьи Karlijn Willems: SQL Tutorial: How To Write Better Queries

4. Скажите нет грубой силе

Этот последний совет на самом деле означает, что вы не должны слишком сильно ограничивать запрос, потому что это может повлиять на его производительность. Это особенно верно для объединений и для предложения HAVING.

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

• Избыточные условия для объединений

Когда вы добавляете слишком много условий для объединений, вы, по сути, предписываете SQL выбрать определенный путь. Может быть, однако, что этот путь не всегда более эффективен.

Предложение HAVING было первоначально добавлено в SQL, потому что ключевое слово WHERE не могло использоваться с агрегатными функциями.  HAVING обычно используется с предложением GROUP BY , чтобы ограничить группы возвращаемых строк только теми, которые соответствуют определенным условиям. Однако, если вы используете это предложение в своем запросе, индекс не используется, который, как вы уже знаете, что может привести к запросу, который будет не реально выполнить.

Если вы ищете альтернативу, подумайте об использовании предложения  WHERE. Рассмотрим следующие запросы:

SELECT state, COUNT(*) FROM Drivers WHERE state IN ('GA', 'TX') GROUP BY state ORDER BY state
SELECT state, COUNT(*) FROM Drivers GROUP BY state HAVING state IN ('GA', 'TX') ORDER BY state

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

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

Обратите внимание, что разница между этими двумя предложениями заключается в том, что оператор WHERE вводит условие для отдельных строк, тогда как оператор HAVING вводит условие агрегирования или повторных выборов, в которых один результат, такой как MIN, MAX, SUM, ... был создан из нескольких строк.

Как видите, оценка качества, запись и переписывание запросов –непростая задача, если учесть, что они должны быть максимально эффективными. Избегание анти-шаблонов и использование альтернативных вариантов в написании запросов также являются частью вашей заботы при написании очередей, которые можно запускать в базах данных в профессиональной среде.

Этот список был всего лишь небольшим обзором некоторых анти-шаблонов и советов, которые, надеюсь, помогут новичкам. Если вы хотите получить представление о том, что более старшие разработчики считают наиболее частыми антишаблонами, ознакомьтесь с этим обсуждением.

Методы, основанные на процедурах и процедурных подходах к запросам

Что мы хотели сказать вышеупомянутыми анти-шаблонами, так это указать тот факт, что они сводятся к различию в методах подхода, основанного на наборе данных и процедурномподходе к созданию запросов.

Процедурный подход к созданию запросов – это подход, который очень похож на обычное процедурное программирование: вы сообщаете системе, что делать и как это делать.

Примером такого подхода является избыточные условия в соединениях или случаях, когда вы злоупотребляете предложением  HAVING, как в приведенных выше примерах, когда вы запрашиваете базу данных, выполняя функцию, а затем вызываете другую функцию или используете логику, содержащую цикл, условия, пользовательские функции (UDF), курсоры, ... для получения окончательного результата. При таком подходе вы часто будете спрашивать подмножество данных, затем запрашивать другое подмножество из данных и так далее.

Неудивительно, что этот подход часто называют «поэтапным» или запросом «по очереди».

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

Поскольку SQL настроен на подход, основанный на наборе данных, вряд ли вас удивит, что этот подход будет более эффективным, чем процедурный, и это также объясняет, почему в некоторых случаях SQL может работать быстрее, чем код.

Совет. Подход, основанный на наборе данных, требуется большинству ведущих работодателей! Поэтому вам потребуется часто переключаться между этими двумя подходами.

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

От запроса к планам выполнения

Тот факт, что анти-шаблоны не являются неизменными, а постоянно развиваются по мере роста профессионализма разработчика на SQL, и что для написания альтернативных запросов необходимо рассматривать множество различных вариантов, также означает, что избежать создания анти-шаблоных запросов крайне трудно, последующая их корректировка может быть довольно трудной задаче. И здесь может пригодиться любая помощь, поэтому мы рассмотрим оптимизацию запросов для подхода, основанного на наборе данных с помощью некоторых специальных инструментов.

Обратите также внимание на то, что некоторые из анти-шаблонов, упомянутых в последнем разделе, связаны с проблемами производительности выполнения запроса, например, наличие оператора  AND, OR и NOT  приводит к невозможности воспользоваться индексом. Понимание производительности требует не только более структурированного, но и более глубокого подхода.

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

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

Чтобы получить этот план, вам нужно будет использовать инструменты, которые вам предоставляет система управления базами данных. Вот некоторые инструменты, которые уже могут иметься в вашем распоряжении:

• В некоторых пакетах есть инструменты, которые генерируют графическое представление плана запроса. Взгляните на этот пример:

• Другие могут предоставить вам текстовое описание плана запроса. Одним из примеров является оператор  EXPLAIN PLAN в Oracle, но конкретный синтаксис команды зависит от той СУБД, с которой вы работаете. В других СУБД это может быть  EXPLAIN (MySQL, PostgreSQL) или EXPLAIN QUERY PLAN (SQLite).

Заметьте: если вы работаете с PostgreSQL, вы различаете EXPLAIN, по которой вы просто получаете описание того как планировщик намеревается выполнить запрос без его запуска, тогда как  EXPLAIN ANALYZE фактически выполняет запрос и возвращает вам сопоставительный анализ ожидаемых и фактических планов запросов. Вообще говоря, фактический план выполнения – это тот, который получается при фактическом выполнении запрос, тогда как оценочный план выполнения оценивает, как может выполняться запрос, при этом не выполняя его фактически. Конечно реальный план выполнения намного полезнее, поскольку он содержит дополнительные сведения и статистику того, что на самом деле произошло при выполнении запроса.

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

Совет. Если вы хотите больше узнать об  EXPLAIN или более подробно ознакомиться с примерами, советуем вам прочитать книгу «Разумные объяснения», написанную Гийомом Леларжем.

Временная сложность и O-большое

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

Однако запросы, как правило, классифицироваться не по сложности, а по времени, которое требуется для их выполнения и получения некоторых результатов. Это называется временной сложностью, и для измерения такой тип сложности вы можете использовать формальную нотацию O-большое.

Записью O-большое вы выражаете время выполнения – насколько быстро он будет расти, в зависимости от подаваемой на вход величины при условии, что входная величина становится сколь угодно большой. Нотация O-большого исключает коэффициенты и термины более низкого порядка, поэтому вы можете сосредоточиться на важной части времени выполнения вашего запроса: скорости роста. Таким образом можно отбросить постоянные коэффициенты члены младшего порядка, а временная сложность будет описываться асимптотически. Это означает, что размер входных данных стремится в бесконечность.

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

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

Как вы уже видели, план выполнения определяет, среди прочего, какой алгоритм используется для каждой операции, что позволяет время выполнения запроса выражать как функцию размера таблицы, участвующей в плане запроса и называемой функция сложности. Другими словами, вы можете использовать нотацию O-большое и свой план выполнения для оценки сложности запросов и производительности.

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

Подсказка: индексы являются частью излагаемого здесь материала!

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

Подробнее см. здесь.

Резюмируя заметим, что вы также можете посмотреть следующий список подсказок для оценки производительности запросов в соответствии с их сложностью по времени выполнения и того, насколько хорошо они будут выполнять:

Тонкая настройка SQL

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

• Заменить ненужное полное сканирование больших таблиц на сканирование индексовэ
• Убедиться, что применяется оптимальный порядок соединения таблиц.
• Убедиться, что индексы используются оптимально, а также в том, что
• Небольшие таблицы кэшированы и используются полностью индексированные таблицы.

Поздравляем! Вы дошли до конца этого сообщения в нашем блоге, получив небольшой обзор по теме производительности SQL-запросов. Надеюсь, вы получили больше информации о об анти-шаблонах, оптимизаторе запросов и инструментах, которые можно использовать для просмотра, оценки и интерпретации сложности плана ваших запросов. Однако это только малая часть знаний! Если вы хотите узнать больше, рекомендуем вам прочитать книгу «Системы управления базами данных», написанную Р. Рамакришнаном и Дж. Герке.

Наконец, приведу цитату от пользователя StackOver-flow:

«Мой любимый анти-паттерн служит не для проверки запросов. он применяется тогда, когда:

- Ваш запрос включает несколько таблиц.

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

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

Начать работу с SQL вам может помочь вводный курс Data-Camp по SQL для науки о данных!

Статью перевел Владислав Семёнов.