Рекомендации по ведению SQL-кода

2023-12-11 в 9:49, admin, рубрики: MS Sql Server, sql, tsql

В этом материале разберем общие рекомендации по ведению SQL-кода на примере СУБД MS SQL (T-SQL). Однако, многие пункты можно также применить и к другим СУБД.

Рекомендации по ведению SQL-кода

Все фильтрации применять сразу в запросе, т е не рекомендуется сначала вызвать функцию, а потом применять фильтр (лучше передать как параметр фильтр):
пример (плохо):
```
SELECT t.ID FROM dbo.fn_func_table () AS t WHERE (t.IsActive = 1)
```
пример (хорошо):
```
SELECT t.ID FROM dbo.fn_func_table (1) AS t
```
Здесь важно обратить внимание, что в ф-ии нужно определить параметр IsActive со значением по умолчанию следующим образом:
```
CREATE OR ALTER FUNCTION dbo.fn_func_table (@IsActive INT = NULL)
```
и затем везде явно задавать этот параметр или передавать значение по умолчанию через ключевое слово DEFAULT:
```
SELECT t.ID FROM dbo.fn_func_table (DEFAULT) AS t 
```
Все пользовательские типы рекомендуется делать оптимизированными в памяти.
Для этого нужно создать файловую группу оптимизированную в памяти:
```
ALTER DATABASE [<DB_Name>]
ADD FILEGROUP [<FG_Name>] CONTAINS MEMORY_OPTIMIZED_DATA;
GO
```
через GUI:

Создание файловой группы

Далее нужно создать файл в этой файловой группе:
```
ALTER DATABASE [<DB_Name>]
ADD FILE ( NAME = N'<File_Name>', FILENAME = N'<patch><File_Name>' ) TO FILEGROUP [<FG_Name>];
GO
```
И затем создается пользовательский тип оптимизированный в памяти (пример):
```
IF TYPE_ID('dbo.IdIntList') IS NOT NULL
BEGIN
   DROP TYPE dbo.IdIntList;
END
GO

CREATE TYPE dbo.IdIntList AS TABLE (ID INT NOT NULL PRIMARY KEY NONCLUSTERED, INDEX hash_index_sample_memoryoptimizedtabletype_c2 HASH (ID) WITH (BUCKET_COUNT = 20000) ) WITH (MEMORY_OPTIMIZED = ON);
GO
```
Здесь в BUCKET_COUNT нужно указать в 2 раза больше значение, чем предполагаемое кол-во обрабатываемых (передаваемых) строк в этом типе.
Не рекомендуется на постоянной основе в запросах обращаться к нематериализованным представлениям.
Не рекомендуется делать вычисления слева в условиях фильтрации и соединении (константы также справа, а слева-только одно индексируемое поле из таблицы).
Рекомендуется в условиях фильтрации и соединении слева использовать только индексированные поля.
Стараться в условиях не использовать оператор OR, а заменить его на IN или разбить на разные команды с помощью ветвления кода.
Если в IN значений больше, чем несколько, то лучше создать временную таблицу или оптимизированную в памяти табличную переменную T, в нее сложить нужные значения для сравнения и затем ее использовать в условии через EXISTS или ANY:
```
DECLARE @AccountIDs dbo.IdIntList;
INSERT INTO  (ID)
SELECT ...
SELECT ...
FROM tbl AS t
WHERE EXISTS (SELECT 1 FROM @AccountIDs AS a0 WHERE (t.KeyID = a0.ID));
```
Если из таблицы в SELECT не вытаскиваются поля, то вместо INNER/LEFT JOIN лучше использовать EXISTS/NOT EXISTS или ANY, что позволит использовать SEMI JOIN вместо INNER JOIN.
Пример:
```
SELECT t1.ID, t1.[Name] FROM tbl AS t1
WHERE (t1.FK_ID = ANY (SELECT t2.ID FROM tbl AS t2 WHERE t2.IsAction = 1))
```
Замечание. ANY — это сокращенная форма EXISTS (эквивалентные конструкции).
Стараться всегда максимально фильтровать сначала по первичным ключам, затем по кластерным индексам (первичный ключ и кластерный индекс в общем случае не обязаны совпадать), затем по некластерным индексам и соединения таблиц проводить сначала
по INNER JOIN. Если OUTER JOIN достаточно много (даже порой одного достаточно), то сначала выгрузить промежуточный результат во временную таблицу, а уже ее использовать с OUTER JOIN, если такое возможно. Не забыть во временной таблице проиндексировать
нужные поля для последующего запроса. Таких интераций может быть несколько.
Для юникода всегда явно ставить N перед значениями (N'<строка>').
Вынести из запроса все, что можно посчитать и определить заранее, в переменные или во временные таблицы (например, один и тот же подзапрос в разных местах запроса), а также если можно сделать ветвление кода вместо унифицированного запроса.
Вместо DISTINCT лучше использовать GROUP BY и его модификации (GROUPING SETS и т д) в том случае, если уникальность или группировка нужна не более чем по нескольким полям. Если уникальность нужна почти по всей или по всей строке и в ней значительно
больше, чем 5 полей, то лучше использовать DISTINCT.
При проектировании стараться делать так, чтобы первичные ключи и кластерные индексы (да и некластерные тоже) наполнялись значениями не в одном направлении (т е чтобы значения монотонно не возрастали и не убывали). Случайное значение — плохо для перфоманса (хотя в случае обновления 100% этого результата не достичь, но обычно PK не обновляют). Также плохо монотонное возрастание/убывание значений ключей и индексов.
Не индексировать немаленькие поля (например, строки, длина которых превышает 8 символов). В таком случае лучше определить вычисляемое сохраняемое поле, которое будет рассчитывать хэш этого поля и по нему создать индекс. В запросе сначала делать условие по этому индексу, а затем уточняющее условие по самому полю. Аналогично и для комбинации полей, когда хэш вычисляется по кортежу, а не по конкретному полю. Подобное решение реализовано во всех языках программирования, где есть сборщик мусора (сначала ищется по хэш-коду в виде целочисленного числа все элементы и потом уже по каждому полю уточняется нужный ли это элемент). Для перфоманса это быстрее, чем сразу искать точное совпадение особенно для больших объектов в ООП (строк таблицы — в БД).
Замечание. Аналогично и для сортировки
Всегда указывать название схемы перед объектом БД.
В создании синонимов (или при задании названий столбцов) в запросе не использовать =, а использовать ключевое слово AS, а вместо двойных кавычек использовать квадратные скобки (если это необходимо — например, название совпадает с ключевым словом или содержит спецсимвол (например, пробел)).
Обращаться к индексированным представлениям с хинтом NOEXPAND.
Всегда при вызове хранимых процедур явно указывать параметры.
Всегда явно перечислять столбцы как в запросах, так и при вставках (не использовать * нигде в том числе в EXISTS, исключение только одно – COUNT(*)).
В хранимых процедурах и везде, где можно вначале устанавливать флаги следующим образом:
```
SET NOCOUNT, XACT_ABORT ON
```
А в скриптах устанавливать флаги следующим образом:
```
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
SET NOCOUNT, XACT_ABORT ON;
GO
```
В скриптах, где применяются DDL-инструкции, устанавливать флаги следующим образом:
```
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
SET NOCOUNT, XACT_ABORT ON;
GO

-- Устанавливаем приоритет при возникновении взаимоблокировки
SET DEADLOCK_PRIORITY HIGH;
GO
```
Всегда использовать блог BEGIN — END в IF – ELSE.
Где можно всегда использовать EXISTS вместо COUNT.
Вместо ISNULL лучше использовать COALESCE, т к COALESCE определено стандартом и поддерживается в других СУБД. Тип приводится к максимальному с более предсказуемым выполнением: Deciding between COALESCE and ISNULL in SQL Server.
При использовании ключевого слова TOP не всегда нужно явно указывать ORDER BY, если нужен псевдослучайный порядок в результате. В остальных случаях — обязательно нужно указывать ORDER BY.
У таблицы всегда должны быть определены кластерный индекс и первичный ключ (кроме очень редких и специфичных случаев). При этом кластерный индекс обычно уникальный, хотя и могут быть исключения.
При указании строкового типа (в том числе и при конвертации) всегда нужно указывать длину этого типа в круглых скобках.
Всегда явно указывать при создании таблицы (или при добавлении/изменении столбца) NULL или NOT NULL.
Рекомендуется завершать выражение и строку точкой с запятой (“;”).
Не рекомендуется использовать динамический SQL. Однако, если приходится, то вызывать его через системную процедуру sys.sp_executesql.
Не используйте UNION, т к данный оператор должен выполнить сортировку или хэширование результирующего набора перед его возвращением, что значительно снижает производительность запроса.
Из транзакции вынести все проверки и всё, что можно делать вне ее тела.
Транзакции более высокого уровня, чем фиксированное чтение, лучше делать через специальные объекты, а не в буквальном смысле по таблицам-участницам (аналог введения синхронизирующих объектов в си-подобных языках).
Рекомендуется использование функций в виде Table Inline Function вместо multistatement функций. Однако, возможны отдельные исключения.
Запрещено объявлять переменные и временные таблицы внутри цикла.
Рекомендуется определять все переменные и временные таблицы вначале скрипта/тела объекта БД.
Временную таблицу необходимо удалять через конструкцию DROP TABLE IF EXISTS перед её созданием.
Очистку всей таблицы делать через DDL-команду TRUNCATE вместо DELETE, если это возможно.
При использовании временной таблицы кластерный индекс определять всегда до наполнения, а некластерные индексы всегда после (анализ).
Изменение/удаление/вставку огромного числа строк необходимо разбивать на порции.
При удалении/изменении данных всегда стараться делать это по кластерному индексу. Для этого можно создать временную таблицу и туда складывать значения кластерного индекса по нужному условию. Затем производят непосредственно удаление/изменение.
Пример:
```
DROP TABLE IF EXISTS #tbl;

CREATE TABLE #tbl (ID INT NOT NULL PRIMARY KEY);

INSERT INTO #tbl (ID)
SELECT < >

DELETE FROM t
FROM <>.<> AS T
WHERE t=ANY(SELECT t0.ID FROM #tbl AS t0);
```
или лучше через пользовательский табличный тип, оптимизированный в памяти, который в качестве примера был указан в п.2:
```
DECLARE @IDs dbo.IdIntList;

INSERT INTO @IDs (ID)
SELECT < >;

DELETE FROM t
FROM <>.<> AS T
WHERE t=ANY(SELECT t0.ID FROM @IDs AS t0);
```
При работе с последовательностями (SEQUENCE) при массовых вставках не рекомендуется использовать NEXT VALUE FOR.
Рекомендуется использовать sp_sequence_get_range.
При отсутствии ограничений, контролирующих отсутствие фантомных записей (UNIQUE или PRIMARY KEY), рекомендуется в инструкции MERGE указывать хинт SERIALIZABLE для целевой таблицы.
Не рекомендуется использовать INSERT — EXEC.
При работе с типом HIERARCHYID рекомендуется по возможности использование IsDescendantOf() в случае, если глубина поиска неизвестна. В этих ситуациях движение от родителя к потомкам эффективнее. В прочих ситуациях рекомендуется использовать комбинации GetAncestor() + GetLevel().
Рекомендуется использование инструкции TRY_CAST вместо CAST с последующей проверкой результата на NULL.
Не использовать SELECT INTO.
Запрещено использование параметра SET ROWCOUNT при модификации данных. Необходимо заменять его на ключевое слово TOP.
При присвоении значения переменной или полю всегда стоит учитывать факт возникновения более одного значения (если только это не явно по уникальному индексу/ключу) и разрешать его (TOP, MAX, MIN и т д) для предотвращения ошибок выполнения.
Не забывать тот факт, что при SELECT если строк нет, то в переменную явно положится значение NULL (кроме COUNT(*) и COUNT(1)).
Поэтому лучше поступать следующим образом в случае, если NULL не нужен:
```
SET @parameter = COALESCE(SELECT <запрос>, <значение_по_умолчанию>)
```
А ещё лучше <запрос> выполнить до COALESCE, чтобы избежать выполнения этого запроса более одного раза.
Рекомендуется НЕ использовать курсоры, в том числе неявные курсоры в виде циклов.
Если курсор все же необходим, то он должен соответствовать следующим требованиям:
- Курсор должен быть явно объявлен как LOCAL READ_ONLY FORWARD_ONLY и открыт, а после использования — обязательно закрыт и уничтожен (DEALLOCATE)
- При объявлении курсора рекомендуется использовать инструкцию STATIC, кроме курсора по временным объектам
- Не рекомендуется использовать синтаксис в виде бесконечного цикла и одного FETCH’а
- Запрещены курсоры FOR UPDATE
- Не рекомендуется использовать курсоры-переменные
Примечание:
STATIC и FAST_FORWARD взаимоисключающие опции. К STATIC можнонужно дописать только FORWARD_ONLY. Особенность STATIC курсора в том, что он делает копию данных в tempdb и всю работу ведет с этими данными, не отслеживая изменения данных в базовых таблицах запроса.
По поводу переменных. Отслеживание курсорных переменных — это дополнительная нагрузка на сервер, основная цель которой – это передача курсоров через параметры хранимой процедуры. Таким функционалом не рекомендуется пользоваться.
Запрещено делать DROP/TRUNCATE/DELETE по временным таблицам в конце процедуры (это произойдет само в фоне).
Большие поля (более 2 КБ) лучше выносить в отдельные таблицы для минимизации нагрузки на чтение (когда нужно прочитать не все колонки). Например, сам документ держать в отдельной таблице, а его метаданные, по которым в том числе производится поиск, в основной. Также может потребоваться более 1-ой дополнительной таблицы, если нужно хранить более 1-й большой колонки (например, очень большое описание документа — в одну таблицу, сам документ — в другую, а метаданные документа — в основную таблицу).
Желательно проверять оптимизацию до и после в том числе с помощью специальной тулы: plan-explorer.
При наименовании объектов рекомендуется придерживаться стандарта де-факто.
Не рекомендуется использовать (если есть, то убирать это) Нерекомендуемые функции ядра СУБД в SQL Server.