Что такое план запроса

Открытый вебинар «Порядок выполнения запроса SELECT и план запроса в MS SQL Server»

Коллеги, в последний день января мы запускаем курс «MS SQL Server разработчик», в связи с чем у нас прошёл тематический открытый урок. На нём мы поговорили о том, как MS SQL Server выполняет запрос SELECT, обсудили, в каком порядке и что анализируется, а также немного погрузились в чтение плана запроса.

Преподаватель — Кристина Кучерова, архитектор модели данных в Сбербанке России.

Цели и маршрут вебинара

В начале вебинара были поставлены следующие цели:

Зачем нужен план запроса?

План запроса — очень полезный инструмент, который, к сожалению, многие разработчики не используют. На первый взгляд, может показаться, что совсем не обязательно знать механику запроса. Однако если вы будете понимать, что происходит внутри SQL Server, вы сможете написать более эффективный запрос. И это очень поможет, например, при оптимизации.

Как мы видим запрос SELECT?

Давайте посмотрим, как выглядит запрос SELECT:

Как понять, куда идти за данными?

Первое, что пытается понять сервер при поступлении запроса — куда идти за данными. На этот вопрос отвечает команда FROM, т. к. именно здесь у нас будет список таблиц (либо имя одной таблицы).

Для наглядности давайте представим, что наш сервер — это некий дворецкий, которому мы приказываем собрать нас в отпуск. Соответственно, дворецкий начинает думать, а в каком же шкафу лежат нужные вещи (в какой таблице нужно брать данные)? И чтобы наш дворецкий без затруднений выполнил свою задачу, мы используем FROM.

Как понять, какие данные брать?

Допустим, дворецкий нашёл нужный шкаф и открыл его. Но какие вещи брать? Может, мы едем на горнолыжный курорт? А может, на жаркий солнечный пляж? Чтобы наши вещи соответствовали погоде, нам пригодится команда WHERE, определяющая условия, то есть позволяющая отфильтровать данные. Если жарко, берём сланцы, майки и купальники, если холодно — варежки, вязаные носки, свитера)).

Следующий этап — вложить эти данные в группы, что происходит с помощью GROUP BY (майки отдельно, носки отдельно). По результатам группировки можно наложить ещё одно условие, используя HAVING (например, отсеиваем непарные вещи). В конечном итоге всё складываем с помощью ORDER BY, получая на выходе готовый чемодан вещей, а точнее — упорядоченный блок данных.

Кстати, тут есть нюанс, а заключается он в том, что есть разница, какие условия следует прописывать в WHERE, а какие в HAVING. Но об этом лучше посмотреть в видео.

Продолжаем. Путь выполнения запроса сохраняется в виде плана запроса в кэше, то бишь наш дворецкий всё записывает, ведь он хороший дворецкий — вдруг в следующем году вы захотите повторить свой приказ? И таких планов, в принципе, может быть много.

Виды соединений в плане запроса

Существуют три соединения, которые вы можете встретить в плане запроса:

Допустим, нам нужно соединить данные из разных таблиц. Давайте представим эти таблицы в виде… небольшого количества конфет Skittles и полной упаковки M&M’s.

При соединении типа Nested Loop мы берём конфету Skittles, а потом достаём вслепую конфету из пакета M&M’s. Если нам не попадается конфета такого же цвета (это наше условие), мы достаём следующую, то есть происходит обычный перебор. В результате можно сказать, что соединение Nested Loop больше подходит для небольших объёмов данных. Очевидно, что если данных много, перебор — не самый оптимальный вариант.

Посмотрим, как это выглядит в SQL-панели:

Соединение используется для больших объёмов данных. Когда у вас Merge join, у вас обе таблицы имеют индекс, по которому их можно соединить. В случае с конфетами – это как будто они у нас заранее разложены по цветам.

Выглядит всё следующим образом:

Merge join хорош в следующих случаях:

Hash join используется при неотсортированных больших объёмах данных. Для соединения таблиц в данном случае нужно построить что-то, имитирующее индекс.

Пример соединения Hash join:

Для наглядности вспомним наши конфеты:

Применение Hash join предполагает 2 фазы действий:

Когда хорош Hash join:

Друзья, кроме вышесказанного, в открытый урок вошли и другие интересные моменты, с которыми лучше всего ознакомиться, посмотрев видео. Мы же предлагаем посетить День открытых дверей курса «MS SQL Server разработчик», где можно будет задать преподавателю все интересующие вопросы.

P. S. Преподаватель Кристина Кучерова выражает признательность Jes Schultz Borland за её презентацию с PASS Summitt Execution Plans: The Secret to Query Tuning Success, которая была использована при подготовке открытого урока.

Источник

Как посмотреть план выполнения запроса в Microsoft SQL Server

Всем привет! Сегодня мы поговорим о том, как посмотреть план выполнения запроса в Microsoft SQL Server, при этом мы рассмотрим несколько способов.

Введение

План выполнения запроса – это набор конкретных действий, выполнение которых приведет SQL запрос к итоговому результату.

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

Более подробно план выполнения запроса мы рассматривали в материале

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

Типы планов выполнения запроса

Оптимизатор запросов Microsoft SQL Server формирует только один план выполнения для запроса, однако существует несколько типов планов выполнения запроса, которые можно отобразить с помощью SQL Server Management Studio (SSMS).

Предполагаемый план выполнения

Предполагаемый план выполнения (Estimated Execution Plan) – это план, созданный оптимизатором запросов на основе оценок.

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

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

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

Такой план удобно использовать в тех случаях, когда запрос выполняется долго, а нам необходимо посмотреть план, который собирается использовать SQL Server для данного запроса.

Действительный план выполнения

Действительный план выполнения (Actual Execution Plan) – это план, созданный оптимизатором запросов после фактического выполнения запроса. Иными словами, план становится доступным после выполнения SQL инструкции. Поэтому такой план отображает фактические метрики использования ресурсов.

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

Статистика активных запросов

Статистика активных запросов (Live Query Statistics) – это план, который создаётся в режиме реального времени. Такой план доступен во время выполнения SQL запроса и обновляется каждую секунду, что позволяет нам просматривать динамический план выполнения активного запроса.

Такая возможность позволяет нам анализировать процесс выполнения запроса в режиме реального времени по мере передачи управления от одного оператора плана запроса другому.

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

Примечание! Эта функция предназначена в основном для диагностики. Ее использование может значительно снизить общую производительность запроса.

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

Посмотреть план выполнения запроса можно, конечно же, с помощью SQL Server Management Studio. При этом для каждого типа используется свой способ просмотра.

Отображение предполагаемого плана выполнения запроса

Посмотреть предполагаемый план выполнения запроса можно несколькими способами, в частности:

С помощью интерфейса SSMS

В окне создания запроса на панели инструментов нажмите кнопку «Показать предполагаемый план выполнения» (Display Estimated Execution Plan).

В результате откроется вкладка «План выполнения». Сам запрос, как Вы помните, в данный момент выполняться не будет.

С помощью инструкции Transact-SQL

Тот же самый план выполнения можно получить с помощью следующей инструкции языка T-SQL

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

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

Отображение действительного плана выполнения запроса

Фактический план выполнения запроса можно также посмотреть нескольким способами:

С помощью интерфейса SSMS

В окне создания запроса на панели инструментов нажмите кнопку «Включить действительный план выполнения» (Include Actual Execution Plan).

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

С помощью инструкции Transact-SQL

Тот же самый план выполнения можно получить с помощью следующей инструкции языка T-SQL

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

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

Просмотр динамической статистики запросов

В окне создания запроса на панели инструментов нажмите кнопку «Включить статистику активных запросов» (Include Live Query Statistics).

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

Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.

На сегодня это все, надеюсь, материал был Вам полезен, пока!

Источник

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

Узнайте о антипаттернах, планах выполнения, time complexity, настройке запросов и оптимизации в SQL

Язык структурированных запросов (SQL) является незаменимым навыком в индустрии информатики, и вообще говоря, изучение этого навыка относительно просто. Однако большинство забывают, что SQL — это не только написание запросов, это всего лишь первый шаг дальше по дороге. Обеспечение производительности запросов или их соответствия контексту, в котором вы работаете, — это совсем другая вещь.

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

Почему следует изучать SQL для работы с данными?

SQL далеко не мертв: это один из самых востребованных навыков, который вы находите в описаниях должностей из индустрии обработки и анализа данных, независимо от того, претендуете ли вы на аналитику данных, инженера данных, специалиста по данным или на любые другие роли. Это подтверждают 70% респондентов опроса О ‘Рейли (O’ Reilly Data Science Salary Survey) за 2016 год, которые указывают, что используют SQL в своем профессиональном контексте. Более того, в этом опросе SQL выделяется выше языков программирования R (57%) и Python (54%).

Вы получаете картину: SQL — это необходимый навык, когда вы работаете над получением работы в индустрии информатики.

Неплохо для языка, который был разработан в начале 1970-х, верно?

Но почему именно так часто используется? И почему он не умер, несмотря на то, что он существует так долго?

Есть несколько причин: одной из первых причин могло бы стать то, что компании в основном хранят данные в реляционных системах управления базами данных (RDBMS) или в реляционных системах управления потоками данных (RDSMS), и для доступа к этим данным нужен SQL. SQL — это lingua franca данных: он дает возможность взаимодействовать практически с любой базой данных или даже строить свою собственную локально!

Если этого еще недостаточно, имейте в виду, что существует довольно много реализаций SQL, которые несовместимы между вендорами и не обязательно соответствуют стандартам. Знание стандартного SQL, таким образом, является для вас требованием найти свой путь в индустрии (информатики).

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

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

Короче говоря, вот почему вы должны изучить этот язык запросов:

Обработка SQL и выполнение запросов

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

Сначала запрос разбирается в «дерево разбора» (parse tree); Запрос анализируется на предмет соответствия синтаксическим и семантическим требованиям. Синтаксический анализатор создает внутреннее представление входного запроса. Затем эти выходные данные передаются в механизм перезаписи.

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

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

Теперь вы, вероятно, задаетесь вопросом, что считается «хорошим планом запроса».

Как вы уже читали, качество стоимости плана играет немаловажную роль. Более конкретно, такие вещи, как количество дисковых операций ввода-вывода (disk I/Os), которые требуются для оценки плана, стоимость CPU плана и общее время отклика, которое может наблюдать клиент базы данных, и общее время выполнения, имеют важное значение. Вот тут-то и возникнет понятие сложности времени (time complexity). Подробнее об этом вы узнаете позже.

Затем выбранный план запроса выполняется, оценивается механизмом выполнения системы и возвращаются результаты запроса.

Написание SQL-запросов

Из предыдущего раздела, возможно, не стало ясно, что принцип Garbage In, Garbage Out (GIGO) естественным образом проявляется в процессе обработки и выполнения запроса: тот, кто формулирует запрос, также имеет ключи к производительности ваших запросов SQL. Если оптимизатор получит плохо сформулированный запрос, он сможет сделать только столько же…

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

Идеальная отправная точка — подумать о «местах» в ваших запросах, где могут возникнуть проблемы. И, в общем, есть четыре ключевых слова, в которых новички могут ожидать возникновения проблем с производительностью:

Тем не менее, вы также должны понимать, что производительность — это нечто, что должно стать значимым. Однако просто сказать, что эти предложения и ключевые слова плохи — это не то, что нужно, когда вы думаете о производительности SQL. Наличие предложения WHERE или HAVING в запросе не обязательно означает, что это плохой запрос…

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

1. Извлекайте только необходимые данные

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

Оператор SELECT

Помните, что коррелированный подзапрос является подзапросом, использующим значения из внешнего запроса. И обратите внимание, что, несмотря на то, что NULL может работать в этом контексте как «константа», это очень запутанно!

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

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

Операция DISTINCT

Оператор LIKE

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

2. Ограничьте свои результаты

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

Преобразования типов данных

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

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

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

3. Не делайте запросы более сложными, чем они должны быть

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

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

Оператор OR

Когда вы используете оператор OR в своем запросе, скорее всего, вы не используете индекс.

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

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

Рассмотрим следующий запрос:

Оператор можно заменить на:

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

Оператор NOT

Это уже выглядит лучше, не так ли?

Оператор AND

Оператор AND — это другой оператор, который не использует индекс и который может замедлить запрос, если он используется чрезмерно сложным и неэффективным образом, как в следующем примере:

Лучше переписать этот запрос, используя оператор BETWEEN :

Операторы ANY и ALL

Изолируйте столбцы в условиях

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

Это выглядит забавно, а? Вместо этого попробуйте пересмотреть расчет и переписать запрос примерно так:

4. Отсутствие грубой силы

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

Порядок таблиц в соединениях

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

Избыточные условия при соединениях

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

Условие HAVING

Рассмотрим следующие запросы:

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

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

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

Set-based против процедурных подходов к написанию запросов

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

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

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

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

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

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

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

В следующей части будут рассмотрены план и оптимизация запросов

Источник