Оптимизация SQL запросов¶
В данном разделе рассматриваются возможности ускорения выполнения SQL-запросов. Часто даже минимальная оптимизация запроса во много раз ускоряет выполнение какой-то операции в системе, и это очень заметно ощущается пользователями. Долгое выполнение какой-то одной, но часто повторяющейся операции может привести к тому, что пользователи будут недовольны системой и станут саботировать ее использование, и в конечном счете мы потеряем клиента.
Оптимизация SQL-запросов строится на использовании индексов. Индекс — объект БД, создаваемый с целью повышения производительности поиска данных. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет быстрее искать строки, удовлетворяющие критерию поиска.
План выполнения запроса¶
Для работ по оптимизации SQL-запросов можно использовать План выполнения запроса. В системе план доступен при тестировании скриптов на странице Выполнить SQL запрос и в редакторе TSQL-выражений.
В SSMS включить действительный план выполнения можно по кнопке или с помощью горячих клавиш (Ctrl+M).
Создадим таблицу с простым целочисленным первичным ключом.
create table dbo.m\ (\ i int not null primary key\ vc varchar(100)\ );
Посмотрим на отбор по первичному ключу, если сами данные из таблицы не возвращаются, а обращение идет только к ключу:
select i \ from [dbo.]m\ where i = 1
Проблем не наблюдается:
select i \ from [dbo.]m\ where i = 1
Проблем также нет: Проблема не возникает потому, что используется кластерный индекс. В отличие от обычного индекса, в кластерном индексе помимо ключа остальные данные также находятся в листьях индекса. Дополнительные чтения нужны, но они прямые и детерминированные.
Создадим индекс на некластерном столбце (это не обязательно varchar):
create index ix_vc on dbo.m (vc);
и выполним отбор по нему:
select i, vc\ from [dbo.]m\ where vc = \'a\';
Если таблица организована как кластерный индекс, то в обычном индексе значение первичного ключа (в данном примере это vc) хранится также в листьях, и чтобы получить значение дополнительного поля (в данном примере это i) дополнительных усилий не требуется. Поэтому запрос также выполняется за один шаг.
Если отбирать еще один столбец, которого нет ни в первичном ключе, ни в индексе:
create table dbo.m\ (\ i int not null primary key\ vc varchar(100),\ dt date \ );\ create index ix_vc on dbo.m (vc);\ select i, vc, dt\ from [dbo.]m\ where vc = \'a\';
Оптимизатор вообще не использует индекс, потому что в этом индексе нет столбца dt. Значит, пришлось бы сначала получить первичный ключ из индекса, а потом по первичному ключу брать кластерный индекс и по нему извлекать данные.
select i, vc, dt\ from [dbo.]m with (index(ix_vc))\ where vc = \'a\'; В этом можно убедиться, посмотрев список возвращаемых полей: Nested Loops означает, что каждой строке первой таблицы подыскивается подходящая строка второй таблицы. Такой подбор — это случайное, непоследовательное чтение. На таблицах порядка нескольких тысяч записей это не сильно замедляет выполнение запроса, на больших объемах это уже чувствуется.
Анализ плана запроса¶
При анализе плана выполнения запроса важно уделять особое внимание последним операциям, которые занимают наибольшее время выполнения. Эти операции могут оказывать значительное влияние на общую производительность запроса.
Рассмотрим пример: вложенные циклы заняли 37 секунд, тогда как предыдущий оператор (параллелизм) был выполнен всего за 0,016 секунды. Это указывает на необходимость тщательного анализа того, что именно замедляет выполнение оператора с наибольшим временем выполнения.
Для выявления проблемной области рекомендуется двигаться по цепочке операторов слева направо, начиная с самого длительного. Важно определить первый оператор в этой цепочке, который начал выполняться дольше остальных. Одним из индикаторов проблемы может служить широкая стрелка в плане выполнения между операторами.
Широкая стрелка свидетельствует о большом объеме данных, проходящих через операцию. Фактическое количество прочитанных строк можно узнать, наведя мышь на соответствующий элемент плана.
Если в плане запроса наблюдаются широкие стрелки, это является сигналом для пересмотра структуры запроса и возможных улучшений.
Виды объединений (join)¶
Если предполагаются большие объемы данных, лучше использовать merge join — объединение таблиц, отсортированных по ключу соединения.
create table dbo.m\ (\ i int not null primary key\ vc varchar(100) \ );
create table dbo.d\ (\ i int not null primary key\ dt date \ );\ select * \ from dbo.m\ inner merge join dbo.d on m.i = d.i
Здесь нет случайного чтения (Index Seek), а только последовательное чтение (Index Scan): Часто в больших наборах данных используют большую таблицу, уже отсортированную по индексу, соответствующему ключу соединения, и объединяют ее с небольшой таблицей, которую можно быстро по этому ключу отсортировать. В этом случае также хорошо использовать merge join.
Если обе таблицы не отсортированы и не могут быть нормально отсортированы, используется hash join: по ключу соединения (который может состоять более чем из одного столбца) создается хэш, и затем таблицы соединяются по этим хешам. Часто оптимизатор выбирает этот способ даже без прямого указания hash join.
select * \ from dbo.m\ inner hash join dbo.d on m.dt = d.dt
select * \ from dbo.m\ inner merge join dbo.d on m.dt = d.dt Если в плане запроса есть Nested Loop, и мы предполагаем, что на вход в него будут подаваться большие объемы данных, надо оптимизировать план запроса — например, задать способ объединения merge join для таблиц, отсортированных по ключу соединения, или hash join для несортированных.
Если запросы выполняются так долго, что дождаться выполнения невозможно и не удается посмотреть действительный (фактический) план выполнения, нужно смотреть предполагаемые значения — они берутся из статистики оптимизатора. Однако для нескольких сложных соединений или для быстро меняющихся таблиц предполагаемые данные уже не адекватны. Статистику можно обновить.
loop order?
Порядок таблиц в запросе¶
При объединении обычно ведущая таблица — первая, к ней ищутся подходящие записи второй таблицы, и т.д. Но оптимизатор может объединить этот порядок. Чтобы не допустить изменения порядка обработки таблиц, указывается option (force order):
select * \ from dbo.m\ join dbo.d on m.dt = d.dt\ option (force order)
Если есть таблица, join с которой существенно фильтрует результирующий набор данных, эту таблицу лучше ставить первой.
В общем случае сначала лучше ставить таблицы с меньшим числом записей, и к ним с помощью join присоединять более крупные таблицы.
Риски оптимизации¶
Если вы оптимизируете план запроса, нужно учитывать риски:
-
если явно указывается индекс, а этого индекса нет, то возникнет ошибка;
-
если явно указан план запроса (например, вид объединения), но объемы таблиц объединились, и этот план уже не выгоден, оптимизатор не сможет его отменить и будет всегда использовать явно указанный, уже невыгодный план.
Вместо явного указания индекса можно указывать forseek. Тогда оптимизатор будет искать любой индекс, по которому можно отобрать данные по нужному условию.
select i, vc, dt\ from [dbo.]m with (forseek)\ where vc = \'a\';
Для сканирования по кластерному индексу указывается
select i, vc, dt\ from [dbo.]m with (index(1))\ where vc = \'a\';
Отделение ключевых полей от остальных¶
ℹ️ Когда мы говорим о больших данных, имеет значение не только количество записей, но и количество полей в строке, а также сами данные (особенно большие текстовые поля)
При работе с таблицами денормализованных категорий с большими текстовыми ДП имеет смысл сначала указывать в select только ключи и поля, нужные для объединения и проверки условий, а уже потом, после построения основного запроса, добавлять к нему обычным join остальные поля. Особенно хорошо иметь в индексе все поля, необходимые для объединения.
Временные таблицы¶
Еще один вариант ускорения выполнения запроса — использование временной таблицы в запросе:
select m.i, m.vc, d.dt\ into #tmp\ from dbo.m \ join dbo.d on m.i = d.i
Если у нескольких запросов есть какая-то общая выборка, лучше сначала выгрузить эту выборку во временную таблицу, и затем строить запросы, используя эту таблицу. Тогда выборку не придется выполнять несколько раз. При необходимости временную таблицу можно проиндексировать, если нужно гарантировать уникальность — можно задать в ней первичный ключ.
Если выборка содержит небольшое количество строк, можно использовать для нее переменную типа "Таблица" .
ℹ️ Вариант с таблицей-переменной (@) следует использовать только для небольших выборок, т.к. при планировании запроса оптимизатор всегда предполагает, что в такой таблице содержится только одна строка и строит план выполнения запроса соответствующим образом. Для временной таблицы (#) таких ограничений нет — оптимизатор использует реальную статистику и строит оптимальные планы выполнения. С другой стороны, преимущество таблиц-переменных в том, что для них используется упрощенное логирование (для возможного отката транзакций), и за счет этого повышается скорость выполнения запросов.
Курсоры лучше не использовать. Для получения списков вместо курсоров лучше использовать FOR XML PATH.
WITH (NOLOCK)¶
Использование конструкции
...\ from a with (nolock)\ join b with (nolock) on ...
означает, что если таблица заблокирована какой-то другой транзакцией, то данный запрос не будет ждать завершения транзакции, а будет использовать "грязные" данные. Таким образом, запрос не будет останавливаться и будет выполняться быстрее, но при этом консистентность данных может быть нарушена.
В процедурах с бизнес-логикой лучше НЕ использовать with (nolock), а в отчетах — предпочтительнее использовать.
В процедурах чтобы не писать with (nolock) по несколько раз, в начале процедуры можно указать
set transaction isolation level read uncommitted;
( в функциях это не применяется, только в процедурах).
View и хранимые процедуры¶
Представление (view) полностью раскрывается, т.е. при использовании view в другом запросе его текст подставляется целиком в исходный запрос.
Табличные функции — это фактически параметризированные view, т.е. view, в которые передаются параметры.
Простые табличные функции (inline) содержат один оператор return select. Они раскрываются полностью, как обычные view.
Многооператорные табличные функции выполняются дольше, если сначала создаются другие наборы данных, и из них добавляются данные в результирующую таблицу. Формирование каждого набора данных приостанавливает выполнение основного запроса, поэтому лучше преобразовывать такие сложные функции в простые табличные функции с одним оператором return select.
Скалярные функции лучше не плодить без необходимости и оформлять как один запрос, в виде обычной inline-функции. Даже если она возвращает одно значение, его лучше оформлять как запрос, возвращающий одну строку с одним столбцом. Так процедуры проще вставляются в исходный запрос и не тормозят его выполнение.
UNION и UNION ALL¶
UNION ALL просто объединяет все строки в таблицах, а UNION сначала проверяет их на уникальность. Если по логике объединяются таблицы, точно содержащие уникальные данные, лучше использовать UNION ALL для ускорения выполнения запроса.