Некоторые из "хинтов" настройки SQL Server

По материалам статьи Raghu Donepudi: Some "Hints" for Mastering SQL Tuning

Перевод Алексея Сафонова

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

Оптимизация в SQL

Когда приложение направляет серверу SQL-запрос, сервер вначале анализирует (парсит) этот запрос. Он проверяет синтаксис запроса, его права, и затем готовит запрос к исполнению. Оптимизатор запросов пытается выбрать наилучший способ исполнения запроса. В каждой базе данных имеется встроенный набор интеллектуальных алгоритмов, которые выбирают наиболее оптимальный способ исполнения запроса. К примеру, для сложного запроса на основе объединения 8 разных таблиц оптимизатор может потратить целых 30 минут на выбор оптимального решения, прежде чем сервер начнет исполнение запроса. Для этих целей сервер применяет один из двух типов оптимизации: на основе стоимости затрат (cost-based) или на основе правил (rule-based). Эта статья целиком посвящена оптимизатору на основании издержек.
Оптимизатор на основе затрат пробует оценить стоимость каждого плана исполнения. Оценка основывается на рассчитанном значении необходимых чтений страниц базы данных, а также необходимости сортировать результаты. Низкая стоимость говорит о низких затратах системных ресурсов. Однако запросы меньшей стоимости еще не гарантируют более быстрое исполнение.
Однажды проанализировав запрос, сервер сохраняет его в пуле. Поэтому, когда в очередной раз серверу вновь поступит этот же самый запрос, ему не придется снова анализировать его. Наилучшие результаты показывает использование связанных переменных для наиболее часто исполняемых запросов.

Хинты

Хинт - это инструкция (указание) оптимизатору, которую Вы включаете в свой оператор SQL. С помощью хинта вы можете задавать порядок объединения таблиц, порядок сканирования таблиц, указать индексы, которые нужно применить и намеченные цели оптимизации. Для того, чтобы использовать хинт в своих SQL операторах, вы должны разместить его сразу за оператором SELECT таким образом: /*+ <hint> */

Замечание: Несоблюдение синтаксиса не вызовет синтаксической ошибки. Вместо этого оптимизатор будет трактовать это как отсутствие хинта.

Режимы оптимизации

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

SELECT /*+ FIRST_ROWS */ distinct customer_name FROM customer

Следующий запрос наоборот, не вернет записи клиенту до тех пор, пока не выполнит запрос до конца и не отсортирует записи.

SELECT /*+ ALL_ROWS */ distinct customer_name FROM customer ORDER BY customer_name

Вы можете устанавливать режим оптимизации как на уровне сессии, так и на уровне запроса. PL/SQL процедуры, в которых выполняется сразу по несколько запросов, нуждаются в установке режима оптимизации на уровне сессии.

Index Hint

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

SELECT /*+ index(cust_table_last_name_indx) */ distinct author_names FROM devx_author_names WHERE author_last_name ='DON%'

Если вы посмотрите на план исполнения данного запроса, то увидите, что оптимизатор задействовал индекс cust_table_last_name_indx. А еще Вы можете позволить оптимизатору самому выбрать любой индекс из предложенных вами: /*+ index( indx1, indx2) */.

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

Синтаксис анализа индекса таков:

analyze index <index_name> compute statistics;

Запросы с объединением

Если запрос включает в себя объединение двух или более таблиц, сервер базы данных предоставляет несколько хинтов для ускорения запросов. Обычно запрос объединения подразумевает, что будет выполнен поиск по внутренней таблице для каждой записи из внешней таблицы.
Например, предположим, что таблица A имеет 100 записей, и таблица B имеет 1 000 записей. Логично, что запрос выполнился бы быстрее, если бы для каждой записи из таблицы B искалась соответствующая запись в таблице A. Противоположное объединение таблиц может потребовать времени до 10 раз больше.

ORDERED Hint

Хинт ORDERED заставляет оптимизатор объединять таблицы в том порядке, в котором они указаны в разделе FROM. Даже если бы оптимизатор самостоятельно выбрал другой порядок объединения, основываясь на показаниях статистики, этот хинт принудил бы оптимизатор изменить свое решение о порядке сортировки. Синтаксис этого хинта выглядит так: /*+ ORDERED */.

USE_NL Hint

Используйте объединения вложенных циклов, когда объединяемых между двумя таблицами данных не много. Поскольку объединения вложенных циклов позволяет выбирать данные наиболее быстрым способом, это наиболее предпочтительное объединение, когда, либо данные не нуждаются в сортировке, либо вам необходимо, чтобы запрос как можно раньше начал передавать клиенту результаты.
Для таблиц A и B из предыдущего примера (отвлечённо от возможной селективности и наличия индексов) при объединении внутренней таблицей (второй таблицей) была бы таблица A:

SELECT /*+ ORDERED USE_NL(A) */ FROM B, A Where A.column1 = B.column2

USE_HASH Hint

Этот хинт наиболее подходит для объединения больших массивов данных. Объединение хешированных таблиц обеспечивает лучшую производительность и лучше всего подходит для запросов, требующих сортировки. Однако, когда размер объединяемых таблиц достаточно велик, размер этих таблиц после хеширования становится еще больше, что значительно увеличивает расход системных ресурсов: CPU и памяти. Синтаксис для данного хинта выглядит так: /*+ USE_HASH (table_name) */.

USE_MERGE Hint

Хинт слияния таблиц требует, чтобы обе объединяемые таблицы были отсортированы по столбцам, по которым происходит их объединение. Поскольку каждая таблица отсортирована, оператор объединения Merge берет строки из каждой таблицы и сравнивает их. Например, для объединений типа inner join запись попадет в итоговый набор, если значения полей, по которым происходит объединение таблиц, одинаковы. Если они различны, тогда та запись, ключевое поле которой имеет наименьшее значение, отбраковывается, а указатель поиска переходит к следующей записи. Этот цикл повторяется для всех строк. Синтаксис для данного хинта выглядит так: /*+ USE_MERGE(table_name) */.

Используйте только подходящие настройки

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

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

Raghu Donepudi ведущий специалист департамента разработок Global Computer Enterprises. Имеет ученую степень в области информатики университета Lamar в Техасе. Сертифицированный специалист Sun, Java-developer и автор многочисленных методов проектирования программного обеспечения.

Перевод: Алексея Сафонова  2005г.