Руководящие принципы по проектированию и разработке баз данных

Предисловие

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

О требованиях безопасности и говорить не приходится - это безусловное требование. Но и это ещё не всё. База данных должна быть обязательно документирована: наличие логической и физической модели, описание таблиц, полей и других объектов; наличие регламентов обслуживания (SOP -Standard Operating Procedures), регламентов по обновлению и других документов. Таблицы без комплекта документации - набор табличек и не более того. Нет документации - нет системы.

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

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

Соглашения об именовании объектов баз данных

"Как корабль назовёшь, так на нём и поплывёшь" - немного перефразируем высказывание главного героя из книги "Приключения капитана Врунгеля", метко подмеченного писателем Андреем Некрасовым. Вдумчивое, разумное, логичное именование объектов баз данных - таблиц (Tables), представлений (Viewes), хранимых процедур (Stored Procedures), функций (Functions), триггеров (Triggers), индексов (Indexes) облегчает последующее понимание, использование базы данных, повышает читаемость исходного кода серверных процедур и функций. Существует несколько нотаций (соглашений об именовании) объектов баз данных, например, Паскаль нотация, Camel нотация, венгерская нотация. Ниже предлагаются применяемые на практике соглашения об именовании объектов баз данных в ещё одной нотации.

1. Общие положения об именованиях

Для всех пользовательских объектов баз данных и полей принимаются и устанавливаются наименования:
1.1 преимущественно используя under_score нотацию;
1.2 полными словами, сочетанием терминов, отражающими предметный смысл;
1.3 на английском языке;
1.4 в нижнем регистре;
1.5 общая длина наименований - до 30 символов;
1.6 слова - в единственном числе, разделяемые символом нижнего подчеркивания (но не пробелами), например:
         client
         client_address
         client_contact
         branch
         branch_address
         branch_history
         branch_by_manager
1.7 Не используйте зарезервированные слова в наименованиях объектов баз данных, так как это может привести к непредсказуемым ситуациям.

Почему предметный смысл? Для того, чтобы разговаривать с Бизнес-заказчиками, пользователями на одном языке. Можно использовать общеизвестные / устоявшиеся в конкретной сфере бизнеса сокращения, аббревиатуры, например,
pos (point of sell) - точки продаж, торговые точки
fpd (first payment default) - неоплаченный первый платеж по кредитному займу

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

Почему на английском языке? Для того, чтобы разговаривать с зарубежными Бизнес-заказчиками, пользователями на одном языке. Кроме того, иногда, редко в некоторых системах, приложениях могут возникать проблемы в случае использования национальных символов, пробелов. Системные наименования таблиц и полей на английском языке и отражающие бизнес смысл особенно удобно применять в хранилищах данных (DWH), поскольку далее их можно использовать как есть (As Is) в Ad-hoc отчетах (например, DWH --> Excel), в OLAP-проектах в качестве наименований измерений, атрибутов, мер. В наименованиях не допускаются транслитерации вида dogovor, oplata и прочий "колхоз-style" вида name_prnt, objstroy.

Почему в нижнем регистре? Для того, чтобы в программном коде дополнительно фокусировать взгляд на пользовательские объекты и отделять от системных команд, инструкций, которые в свою очередь должны быть написаны в верхнем регистре. Не всегда разработчики, пользователи баз данных имеют возможность просматривать исходный код в редакторах с поддержкой технологии Intelligence Sense и подсветкой синтаксиса. Иногда приходится работать в обычном редакторе текста, как то, Блокнот (Notepad).

Почему разделение слов символом нижнего подчеркивания, а не Camel нотация? Иногда в СУБД, например, Oracle, наименования объектов принудительно приводятся к верхнему регистру и наименования становятся трудно воспринимаемыми, например, CLIENTRETAILHISTORY, POSMONTHLYPLAN.

Почему общая длина наименований до 30 символов? Ну, во-первых, давно прошли те времена (эпоха MS DOS и настольных СУБД), когда системные наименования таблиц и полей ограничивались длиною 8-10 символов. Во-вторых, всё же разумный предел длины должен быть. Так, в СУБД Oracle системное ограничение - 30 знаков. В СASE-средстве SAP Sybase Power Designer архитектору баз данных предоставляется гибкая возможность устанавливать, контролировать правила именования объектов.

Далее сформулированы объект-специфичные рекомендации по именованию.

2. Именование таблиц

Шаблон: <существительное_в_единственном_числе>[_<существительное_в_единственном_числе>]

2.1 Таблицы именуются существительными в единственном числе, поскольку таблица подразумевает хранение множественных экземпляров сущности - записей. Кроме того, так сокращается длина наименования таблицы.
2.2 Наименование таблицы должно раскрывать её смысловое предназначение, характеризовать содержащиеся в ней записи без необходимости лишний раз выполнять инструкцию SELECT * FROM table.
2.3 Наименования таблиц не должны иметь префиксов, т.к. во-первых, незачем смущать Бизнес-пользователей различными примесями, а во-вторых, есть понятие схема базы данных.
2.4 Если наименование состоит из более чем одного слова, то ключевое слово располагается первым (см. пример выше), чтобы в списке (в браузере) объектов таблицы визуально компоновались вместе в алфавитном порядке. Последнее условие особенно важно для баз данных с большим количеством таблиц.

3. Именование представлений

Шаблон: <префикс><имя_таблицы_или_смысловое_наименование>

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

4. Именование хранимых процедур

Шаблон: <префикс><имя_таблицы_или_логической_сущности><действие>

4.1 Наименования серверных хранимых процедур должны начинаться с короткого префикса, например, p_ . Использование префикса sp_ - не желательно, так как в MS SQL Server такие префиксы имеют системные хранимые процедуры и движок СУБД будет осуществлять поиск такой процедуры в первую очередь в базе данных master.
4.2 Наименования серверных хранимых процедур должны оканчиваться такими глаголами, как get, insert, update, delete, calculate, archive, ... или одноименными суффиксами _get, _add, _upd, _del, _calc, _arch, ..., отражающими их основные действия, например,
         p_client_address_add
         p_client_address_upd
         p_client_address_del
         p_client_cashback_calculate

5. Именование функций

Шаблон: <префикс><имя_таблицы_или_логической_сущности><действие>()

5.1 Наименования пользовательских серверных функций должны начинаться с коротких префиксов, например, f_ . В MS SQL Server различают скалярные и табличные пользовательские функции, поэтому для их отличия можно зарезервировать префиксы f_ и ft_ соответственно.
5.2 Наименования пользовательских функций должны говорить об их функциональности, например,
ft_user_details_get()
f_address_parse()

6. Именование триггеров

Шаблон: <префикс><имя_таблицы><действие>

6.1 Наименования табличных триггеров должны начинаться с короткого префикса, например, tr_, а затем в единственном числе имя таблицы, на которую навешивается триггер.
6.2 Наименования табличных триггеров должны оканчиваться суффиксами _add, _upd, _del, отражающими их основные действия, например,
tr_client_address_upd
tr_branch_history_add_upd

7. Именование индексов

Шаблон: <префикс><имя_таблицы>_<имя_поля>[_<имя_поля>]

7.1 Наименования индексов должны начинаться с префиксов:
         pck_ - PRIMARY    CLUSTERED      KEY   - первичный (единственный уникальный) кластерный ключ
         pnk_ - PRIMARY    NONCLUSTERED KEY   - первичный (единственный уникальный) некластерный ключ
         ack_ - ALTERNATE CLUSTERED      KEY   - альтернативный (уникальный) кластерный ключ
         ank_ - ALTERNATE NONCLUSTERED KEY   - альтернативный (уникальный) некластерный ключ
         uci_ - UNIQUE     CLUSTERED      INDEX - уникальный кластерный индекс
         uni_ - UNIQUE     NONCLUSTERED INDEX - уникальный некластерный индекс
         _ci_ -            CLUSTERED      INDEX
         _ni_ -            NONCLUSTERED INDEX - обычный индекс
7.2 далее следуют наименование таблицы и, если необходимо, наименования полей индекса, например,
         pck_client
         uni_branch_code

8. Именование связей ссылочной целостности

Шаблон: <префикс><имя_подчиненной_таблицы>__<имя_таблицы>

8.1 Наименования связей ссылочной целостности (Referential Integrity) должны начинаться с короткого префикса, например, ref_ (с возможным, если необходимо, указанием порядкового номера связи). Далее следует имя подчиненной (зависимой) таблицы, а затем - имя старшей таблицы, например,
         ref_metro__locality
         ref_transaction__account
         ref2_transaction__account

9. Именование полей

9.1 Одинаковые по бизнес-назначению поля в разных таблицах должны иметь по возможности одинаковые системные наименования. В этом случае облегчается понимание базы данных, некоторые ETL-инструменты автоматически строят мэппинги между полями.
9.2 Системные наименования полей-идентификаторов, ключевых полей должны начинаться с префикса id_

Соглашения о проектировании баз данных

001. Проектируйте базы данных с учётом возможного темпа роста Бизнеса. Необходимо обеспечивать производительность баз данных не только в текущем моменте, но и при n-кратном увеличении объёмов данных, количества пользовательских запросов. Задайте и постарайтесь ответить на вопросы: Что будет с базой данных через три, пять лет? Как выдержит нагрузку база данных при объединении компаний? и т.д.;

002. Выбирайте оптимально архитектуру базы данных, таблиц. Рассматривайте плюсы и минусы сопровождения 3NF, EAV, Star-scheme, Data Vault, Parent-Child, Flat Table;

100. Проектную работу по моделированию базы данных выполняйте в CASE-среде, такой как SAP Sybase Power Designer, AllFusion ERwin Data Modeler, Oracle Designer, IBM Rational Software, Toad Data Modeler и др. Данные продукты имеют массу преимуществ: логическая и физическая декомпозиции, визуализация моделей БД, конвертирование / поддержку разных платформ СУБД, автоматическое генерирование кода DDL и т.д.;

101. В целях декомпозиции и повышения читабельности базы данных для каждой предметной области должна быть создана отдельная ER-диаграмма для наглядного отображения логических и/или физических взаимосвязей таблиц. Диаграммы должны иметь бизнес-наименования, описания. Расположение таблиц в диаграмме должно быть наглядным, линии связей между таблицами должны иметь минимальное количество пересечений, а по возможности не иметь пересечений;

102. Каждая таблица, за исключением временных и псевдовременных таблиц, [а также представления] должны быть включены в ER-диаграмму;

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

200. Таблицы обязательно должны иметь бизнес-наименование, описание. Комментарии не должны быть формальными, а быть актуальными, обстоятельными, раскрывать бизнес-содержание, процессы.

201. Поля таблиц обязательно должны иметь бизнес-наименование, описание. Комментарии не должны быть формальными, а быть актуальными, обстоятельными, раскрывать бизнес-содержание, артефакты.

300. Соблюдайте физический порядок следования полей в рамках таблицы:
       1) сначала первичный ключ;
       2) затем внешние, альтернативные ключи;
       3) далее - содержательные поля таблиц, сгруппированные по смысловому назначению;
       4) в конце - служебные, вспомогательные поля;
Тяжело работать, разбираться с широкой таблицей с десятками полей, в которой поля (например, поля адресов регистрации, фактического проживания клиентов) хаотично разбросаны;

301. Таблицы должны иметь первичный ключ (Primary Key), желательно простой (не композитный), суррогатный (искусственный, не содержащий какой-либо бизнес-логики) целочисленный ключ;

302. Между соответствующими таблицами должна быть установлена ссылочная целостность (Referential Integrity). Связи могут быть как декларативными, так и действующими;

303. Выбирайте оптимальные типы данных для полей. Так, VARCHAR(n) предпочтительнее CHAR(n), так как последний тип устанавливает строку фиксированной ширины, и даже незаполненное поле будет занимать n байтов. Для символьных полей необходимо подбирать с запасом размер (n), достаточный для самых длинных, но допустимых строк. Тип DECIMAL - самый тяжелый, затратный из числовых типов данных. В некоторых случаях тип MONEY может оказаться недостаточным по количеству знаков после запятой для хранения денежных значений. Избегайте использования устаревших NTEXT, TEXT, IMAGE типов данных, а пользуйте NVARCHAR(max), VARCHAR(max), VARBINARY(max). Применяйте юникод типы данных NCHAR, NVARCHAR, NTEXT в крайней необходимости, так как они требуют в 2 раза больше места, чем не юникод типы данных;

304. Не планируйте брать на борт в таблицу базы данных бинарные файлы или картинки (Binary Large Objects - BLOB), лучше храните в базе данных путь к таким файлам;

400. Наличие в таблице большого количества полей с NULL-значениями - вероятный признак плохого проектирования. Разбейте такую широкую таблицу на несколько таблиц, связав их отношением 1:1 к базовой таблице;

401. При наличии Enterprise редакции базы данных следует секционировать потенциально много миллионные таблицы; как правило, партиционирование таблицы осуществляется по полю типа DATE, DATETIME. Горизонтальное разбиение таблиц способствует повышению производительности запросов, позволяет выполнять обновление записей методом подмены секций;

402. В базе данных создайте несколько файловых групп / табличных пространств (Table Space), раскладывайте таблицы и индексы по разным группам, а сами файловые группы - по разным физическим дискам;

403. Для таблиц базы данных, ориентированных больше на чтение (например, целевые таблицы хранилища / витрины данных), можно включить опции сжатия таблиц, сжатия индексов, что при достаточном количестве процессоров будет содействовать повышению производительности запросов;

500. С целью контроля данных и содействия производительности запросов в таблицах по отдельным полям / совокупности полей по возможности должны быть созданы альтернативные ключи (Alternative Key), уникальные индексы (Unique Index);

501. Желательно, чтобы каждая таблица имела кластерный индекс;

502. С целью ускорения операций соединения (JOIN) таблиц для полей - внешних ключей таблиц с высокой избирательностью значений и значительным количеством записей должны быть созданы индексы;

503. В целях повышения производительности запросов таблицы должны иметь достаточное количество некластерных неуникальных индексов, утилизируемых в предложениях WHERE, JOIN, ORDER BY, SELECT (перечислены слева направо в порядке приоритетности);

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

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

Соглашения об оформлении исходных программных кодов

Хорошо оформленный структурированный программный код:
- способствует пониманию логики обработки данных;
- сокращает время поиска и отладки ошибок, минимизирует количество новых ошибок;
- обеспечивает отчуждаемость кода, передачу кода (а также его приёмку) другим разработчикам;
- существенно облегчает сопровождение, доработку, а, следовательно, снижает операционные затраты;
- качественно характеризует автора-разработчика (порядок в коде = порядок в голове).
Обсудим правила оформления программных процедур, триггеров, функций, sql-пакетных инструкций (далее - просто процедуры).

1. Наименования пользовательских процедур и функций должны говорить об их функциональном предназначении (см. Соглашения об именовании объектов).
2. Наименования переменных и параметров должны говорить об их функциональном предназначении и быть полными словами на английском языке в нижнем регистре в under_score нотации (см. Соглашения об именовании объектов).
3. Переменные должны объявляться DECLARE в одном месте, в начале процедуры, не будет проблем с их областью видимости.
4. Начальные значения переменным должны присваиваться SET в следующих строках сразу после инструкций DECLARE.
5. Должна быть обязательной практика документирования процедур: в начале тела процедуры, перед блоком объявления переменных должно быть содержательное предметное описание процедуры; в комментариях должны быть прописаны ограничения и допущения использования процедур, если таковые имеются; при наличии нескольких необязательных входных параметров желательны примеры вызова; желательны указание авторства, даты / номера версии процедуры.
6. Входные и выходные параметры процедур должны сопровождаться комментариями.
7. Комментарии не влияют на производительность процедур. Простые однострочные комментарии следуют после двойного дефиса --, а блоки текста обрамляются /* ... */ Комментарии важны для бизнес-критичных расчетов, в точках ветвления логики процедур, нетривиальных преобразованиях данных. Комментарии должны раскрывать бизнес цель, ожидаемый результат.

CREATE PROCEDURE dbo.p_credit_applications_get
 @date_from           VARCHAR(10)       -- дата начала отчетного периода    - строка вида 'dd.mm.yyyy'
,@date_to             VARCHAR(10)       -- дата окончания отчетного периода - строка вида 'dd.mm.yyyy'
,@user_name           VARCHAR(50)       -- доменный логин сотрудника, который вызвал данную процедуру
,@inn_filter          VARCHAR(20) = ''  -- дополнительный фильтр - поиск по ИНН юридического лица
,@office_filter       VARCHAR(50) = ''  -- дополнительный фильтр - поиск по наименованию офиса
AS
SET NOCOUNT ON
-- Данная процедура возвращает детализированные данные по розничным кредитным заявкам, зарегистрированные в указанном отчетном периоде
/******************************************************************************************************************************************
-- Примеры вызова:
EXEC dbo.p_credit_applications_get  @date_from='01.10.2014', @date_to='31.10.2014', @user_name='MOSCOW\U_M0LQ6'
EXEC dbo.p_credit_applications_get  @date_from='01.10.2014', @date_to='01.10.2014', @user_name='MOSCOW\U_M0LQ6',  @office_filter='Омск'
EXEC dbo.p_credit_applications_get  @date_from='01.10.2016', @date_to='31.12.2016', @user_name='MOSCOW\U_M0LQ6',  @inn_filter='5511111111'
******************************************************************************************************************************************/
DECLARE @date_start DATETIME, @date_end DATETIME

SET @date_start = CONVERT(DATETIME, @date_from, 104)
SET @date_end   = CONVERT(DATETIME, @date_to,   104)

8. Для большей читабельности кода следует отделять между собой логические фрагменты процедуры 2-3 пустыми строками, а отдельные блоки - разделительными комментарными строками из специальных символов.
9. Особое внимание следует уделять форматированию sql-запросов. Современные редакторы среды разработки (Microsoft SQL Server Management Studio, PLSQL Developer и другие) позволяют один раз настроить шаблон правила форматирования, а затем многократно применять их "в одно касание".

-- Пример трудно воспринимаемого sql-запроса,
-- при выполнении которого возможна потенциальная ошибка после добавления одноименного поля в другую таблицу:
select birth_date, user_name, gender, password,
employee, CATEGORY from user_details u 
join employee e on e.id_employee = u.id_employee where birth_date > '31-12-2010' and left(category,2)='гл' order by 2


-- После корректного оформления и форматирования sql-запрос стал интуитивно понятным:
SELECT u.user_name
      ,u.category          AS user_category
      ,u.[password]
      ,e.employee
      ,e.birth_date
      ,e.gender
  FROM dbo.user_details    u 
  INNER JOIN dbo.employee  e  ON e.id_employee = u.id_employee
    WHERE e.birth_date > '20101231'
          AND LEFT(u.category, 2) = 'гл' -- выбираем только главных, основных пользователей
    ORDER BY u.user_name

9.1 Так, уже стало общепринятой практикой выстраивать запрашиваемые поля вертикально, каждое поле в отдельной строке.
9.2 Запрашиваемые поля следует компоновать в смысловые группы, наиболее значимые поля - первыми (чтобы в выборке были слева), второстепенные поля - последними (чтобы в выборке были справа). Не следует разбрасывать поля как попало, будто на минном поле.
9.3 Зарезервированные слова и системные функции должны быть написаны в верхнем регистре, а наименования пользовательских объектов и полей - в нижнем регистре, чтобы дополнительно фокусировать взгляд читателей.
9.4 Таблицам, представлениям, табличным функциям обязательно назначать короткие алиасы (например, первая буква имени таблицы). Использование псевдонимов позволяет легко подменить в sql-запросе один табличный объект на другой, предотвращает коллизии неодназначного упоминания полей, сокращает объем кода.
9.5 Все поля в sql-запросе должны иметь квалификационную ссылку на табличный объект ( <алиас>. ).
9.6 Необходимо полностью квалифицировать табличный объект, указывая Database Owner, а отдельных случаях - Server Name, Database Name.
9.7 Задействованные в соединениях (JOIN) табличные объекты очень хорошо выстраивать вертикально, каждый объект в отдельной строке.
9.8 Вертикальные отступы в 2 пробела между разделами SELECT, FROM & JOIN, WHERE & GROUP BY & HAVING & ORDER BY также способствуют фокусировке взгляда. Вертикальный отступ в 1 пробел не так заметен, а большее количество отступов сильно смещает весь sql-запрос вправо при наличии вложенных запросов.
9.9 Контекстные комментарии облегчают дальнейшее сопровождение sql-запросов, а иногда спасают чьи-то погоны.
10. Потенциально проблемные, недоделанные участки кода можно помечать сигнальными комментариями, например,
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!
11. Большие процедуры следует стараться разбивать на логически обособленные процедуры, которые можно было бы вызывать из других процедур. Не следует перегружать процедуры, пытаясь запрограммировать всё в одной. Декомпозиция кода облегчает его отладку.

Рекомендации по разработке серверных скриптов

Обсудим и перечислим наиболее часто встречающиеся ошибки при разработке серверных скриптов для универсальных реляционных СУБД, отметим предложения по улучшению качества кода. Рекомендации могут быть справедливы как для Microsoft SQL Server, так и для Oracle Database. Для специфики платформ будут использоваться метки (T-SQL), (ORA).

001. (T-SQL) Размещайте инструкцию SET NOCOUNT ON в начале пакетов SQL команд (SQL Batch), хранимых процедур, триггеров. Это несколько повысит производительность процедур, поскольку не будет тратиться время на подсчёт количества обработанных записей.

002. (T-SQL) Используйте имя схемы с именем объекта, так как схема полноценно квалифицирует объект в рамках базы данных. Имя схемы должно предшествовать имени хранимой процедуры и всем объектам, упоминаемым в процедуре. Это впрямую помогает обнаружению в кэше скомпилированного плана запроса вместо поиска объектов в других схемах. Процесс поиска и идентификации объектов накладывает COMPILED блокировку на хранимую процедуру, что снижает производительность хранимой процедуры.

003. В запросах присваивайте алиасы полям согласно Соглашению об именовании, которое представлено выше.

004. (T-SQL) Избегайте без особой надобности использования серверных курсоров (Server-Side Cursors) в пользу sql-запросов (SET-based Operations) поскольку первые дополнительно отъедают ресурсы сервера. Серверный курсор утилизирует много накладных расходов для поддержания текущей позиции в наборе записей (Recordset).

005. (T-SQL) Наиболее быстрые однопроходные "шланговые" курсоры только на чтение:

DECLARE cursor_contracts CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
  FOR SELECT ...
...
...
CLOSE cursor_contracts
DEALLOCATE cursor_contracts  -- обязательно освобождайте ресурсы сервера

010. Откажитесь от Adhoc запросов в пользу серверных хранимых процедур. Инструкции по манипулированию, извлечению данных не должны растекаться по клиентским приложениям и Data Layer 3-х звенных архитектур, а должны централизованно сосредотачиваться в базе данных. Логика обработки, структура таблиц могут претерпевать изменения, а хранимые процедуры - это постоянный программный интерфейс (API). Кроме того, использование хранимых процедур экономит сетевой трафик, существенно повышает безопасность баз данных.

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

012. (T-SQL) Вместо инструкции EXECUTE утилизируйте системную хранимую процедуру sp_executesql, поддерживающую параметры. План исполнения динамического запроса может быть повторно использован, только если полностью совпадают тексты запросов, включая каждый символ, пробелы, комментарии. Например, если выполнялись инструкции:

DECLARE @query VARCHAR(300)
DECLARE @age INT
SET @age = 30
SET @query = 'SELECT * FROM dbo.person WHERE age = ' + CONVERT(VARCHAR(3), @age)
EXECUTE (@query)

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

DECLARE @query NVARCHAR(300)
SET @query = N'SELECT * FROM dbo.person WHERE age = @age'
EXECUTE sp_executesql @query, N'@age INT', @age = 30

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

013. Всегда создавайте хранимые процедуры и функции в той же самой базе, данными которой они призваны оперировать, в противном случае будет масса проблем с обслуживанием. Промышленные СУБД - это не настольные базы MS Access, где могла быть практика разделения на управляющую интерфейсную базу и базу хранения данных.

014. (T-SQL) Чрезмерное применение команды GOTO усложняет понимание логики процедуры, а иногда может стать причиной ошибки. Другие команды IF ... THEN ... ELSE , CASE ..., RETURN могут помочь избавиться от GOTO.

015. (T-SQL) Команда RETURN предназначена для завершения исполнения процедуры, но не для возврата значения в точку вызова. Если требуется вернуть данные из процедуры, практикуйте выходные параметры OUTPUT.

020. (T-SQL) Временные таблицы, располагающиеся в системной базе TempDB, в можно индексировать, для них также можно определять первичные ключи.

021. (T-SQL) Локальные временные таблицы уникальны, существуют и видны в рамках сессии, по завершению которой автоматически уничтожаются. Тем не менее, считается хорошим тоном удалять временные таблицы сразу, как только в них отпадает надобность (системная база TempDB - не резиновая):

IF OBJECT_ID('tempdb..#table_1') IS NOT NULL
  DROP TABLE #table_1

030. Во избежание конфузов при выполнении инструкций UPDATE, DELETE используйте первичный или альтернативный ключ в условии WHERE.

031. Вместо использования отдельных DML-инструкций INSERT, UPDATE, DELETE по изменению данных одной и той же таблицы возьмите на вооружение инструкцию MERGE, которая может порадовать приростом производительности.

032. UPDATE большого (от миллиона и выше) количества записей - это тормоза, остальные запросы к таблице будут поставлены в ожидание. Отмена, прерывание операции обновления записей займёт примерно столько же времени, сколько длилась операция UPDATE. В случае Enterprise редакции СУБД для массивного обновления таблицы можно использовать подход подмены секций.

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

034. Не вставляйте, не обновляйте, не удаляйте большой объём записей одной транзакцией. Разбивайте операции на мелкие либо делайте операции в цикле.

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

SELECT * FROM ...

SELECT * 
 INTO #table_1  -- метаданные полей создаваемой таблицы могут быть не такими, как ожидалось
   FROM ...

(T-SQL) Правильнее так:

IF OBJECT_ID('tempdb..#table_1') IS NOT NULL
  DROP TABLE #table_1
;
-- обязательно явное определение метаданных полей таблицы:
CREATE TABLE #table_1 (
  field_1  INT         NOT NULL
 ,field_2  VARCHAR(30) NOT NULL
 ,field_3  DATETIME    NOT NULL
                      )
;
INSERT INTO #table_1 (
  field_1
 ,field_2
 ,field_3
                     )
  SELECT  some_field1  AS field_1
         ,some_field2  AS field_2
         ,some_field3  AS field_3
    FROM ...

Всегда перечисляйте поля в инструкции INSERT, чтобы избежать проблем при изменении структуры таблиц. Явно определяйте структуру на лету создаваемой постоянной или временной таблицы, в противном случае типы данных полей могут быть неожиданными. Так, значения выбираемого исходного NOT NULL поля могут иметь тип данных VARCHAR, а в созданной таблице соответствующее поле - NVARCHAR и NULL. Сервер строит предположения о метаданных полей на основе некоторой выборке значений и не всегда может сделать это верно. Даже если будут созданы индексы по полям, то они не будут использоваться в запросах, так как типы данных не совместимы.

041. Не используйте порядковый номер поля в разделе ORDER BY запроса:

SELECT id_user
      ,user_name
      ,[password]
  FROM dbo.user_details
    ORDER BY 2

вместо снижения читабельности sql-запроса указывайте явно поле(я) сортировки:

SELECT u.id_user
      ,u.user_name
      ,u.[password]
  FROM dbo.user_details  u
    ORDER BY u.user_name

042. Использование скалярных пользовательских функций в выборках типа
SELECT stage.f_bad_symbols_remove(field_1) FROM stage.big_table
на больших объемах записей пагубно сказывается на скорости выполнения запроса. Лучше использовать:

SELECT RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(field_1, CHAR(10), ''), CHAR(9), ''), CHAR(13), '')))  AS field_1
  FROM stage.big_table

043. (T-SQL) Не используйте табличные переменные, особенно для больших объёмов строк.

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

045. Оберните часто используемые, сложные соединения (Join) таблиц в представление (View), а затем просто
SELECT field_1, field_2, field_n FROM dbo.v_yours_view
Не включайте без острой надобности в представление ORDER BY. Сортировка - забота потребителя данных, клиентского приложения.

046. Инструкции DISTINCT, UNION, ORDER BY - одни из самых тяжёлых, особенно на больших объёмах записей, поэтому применяйте их крайне обоснованно. Сортировку записей лучше возложить на клиентские приложения. В одном простом запросе DISTINCT и GROUP BY одновременно - это перебор. Перебор также - GROUP BY по 10-ти и более полям.

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

048. Соединение (Join) большого количества таблиц (~ 15 и более) негативно сказывается на производительности sql-запроса, анализатору запросов труднее построить оптимальный план исполнения запроса, особенно если в соединениях задействованы большие таблицы. Кроме того, если в запросе участвуют представления, то анализатор вскрывает их, и не факт, что план запроса порадует вас. Во время исполнения запроса при нехватке оперативной памяти сервер будет сбрасывать свопы на жесткий диск, а чтение / запись на диск - это дорогие операции. Если в одном запросе много соединений и преобразований одновременно, то движок начинает неправильно предполагать о количестве строк, что приводит к неоптимальному плану выполнения. Лучше разбить многоэтажный sql-запрос на отдельные запросы, к тому же логика простых запросов легче поддается пониманию.

049. (T-SQL) (ORA) Как в MS SQL, так и в Oracle используйте оконные аналитические функции, которые, как правило, обеспечивают большую производительность и, несомненно, компактные запросы. Оконная функция SELECT OVER() FROM ... определяет окно или определяемый пользователем набор строк внутри результирующего набора запроса, а затем вычисляет значение для каждой строки в окне.

060. (T-SQL) Если сервер имеет много ядер, то, скорее всего, параметр параметризации настроен либо по умолчанию, либо позволяет захватить отдельному sql-запросу много ядер. Чтобы не мешать другим sql-запросам и не забирать все ядра сервера, укажите хинт MAXDOP() в запросе. Значение 8 будет достаточным в большинстве случаев. Если на сервере меньше ядер или постоянно наблюдается высокая нагрузка на CPU, то укажите меньшее значение.

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

062. Анализируйте предварительные планы запросов, в частности:
- посмотрите сколько записей предполагается к участию и соответствует ли этот объём реальному объёму;
- заведомо большое количество логических чтений - характерный индикатор того, что с запросом что-то не так;
- необходимо стремиться к "Index Seek" вместо "Index Scan" и тем более вместо "Table Scan"

063. Использование в запросе условия ... WHERE field_1 LIKE '%some_text%' отменяет использование индекса по полю field_1, а условие ... WHERE field_1 LIKE 'some_text%' может задействовать соответствующий индекс.

070. Когда используете триггеры, то перед откатом триггера поднимайте наружу дружественные (User Friendly) сообщения, в котором сообщайте клиентским приложениям и пользователям - какой триггер, к какой таблице и почему сработал, а также техническую информацию. Это существенно облегчает идентификацию места и причины ошибки.

080. Транзакция должна быть как можно короче. Эксклюзивная блокировка не снимается до окончания транзакции. Продолжительность транзакции влияет на блокировки и её длительность повышает вероятность возникновения взаимных блокировок (Deadlock) (T-SQL). Проще говоря, расстояние между BEGIN TRANSACTION и COMMIT / ROLLBACK должно быть как можно меньше, то есть количество операций между этими границами должно быть как можно меньше, а сами операции - как можно проще.

081. (T-SQL) При применении BEGIN TRANSACTION сразу после инструкции INSERT / UPDATE / DELETE проверяйте значение глобальной переменной @@ERROR; если обнаружена ошибка, то немедленно ROLLBACK.

090. (T-SQL) В потенциально опасных участках кода используйте конструкцию TRY ... CATCH (появилась в версии MS SQL Server 2005) для перехвата и управляемой обработки ошибок.

BEGIN TRY
  -- Ваш t-sql код
END TRY
BEGIN CATCH
  -- Здесь Ваша обработка ошибок
END CATCH

091. Осуществляйте отладку запросов, хранимых процедур поэтапно, по частям. В хранимой процедуре можно предусмотреть дополнительный параметр IF @is_debug_mode=1 в зависимости от значения которого можно выводить дополнительную отладочную информацию после отдельных операций.

100. (T-SQL) Linked Server и распределенные кроссплатформенные запросы для больших объёмов данных - зло. Не применяйте JOIN к таблицам связанных серверов, особенно если это не MS SQL серверы. Сначала получите данные с удалённых серверов, разместите их в stage (временные или постоянные таблицы) и после этого выполняйте JOIN. Возможно, наоборот, стоит передать идентификаторы (ID) записей на удалённый сервер, там осуществить JOIN, а затем вернуть выборку на ваш сервер. Не используйте запросы типа:

SELECT t2.* 
  FROM OPENQUERY(ora_server, SELECT * FROM t ) AS t2 
    WHERE t2.column_1 > some_val это условие нужно выполнять на линкованном сервере, запрос должен быть вида:

а правильнее так:

SELECT  t2.column_1
       ,t2.column_2
       ,t2.column_3
  FROM OPENQUERY(ora_server, SELECT t.column_1, t.column_2, t.column_3 FROM t WHERE t.column_1 > some_val) AS t2

101. (T-SQL) При утилизации распределённых запросов на стороне удалённого сервера преобразовывайте поля к общим типа данных:

SELECT  t2.column_1
       ,t2.column_2
  FROM OPENQUERY(ora_server, SELECT t.column_1, TOCHAR(t.column_2) AS column_2 FROM t ) AS t2

102. (T-SQL) Не отменяйте (KILL) запрос к связанному серверу. Лучше уж дождаться окончания выполнения запроса, либо сначала отмените сессию на удалённом сервере.

103. Для кроссплатформенной работы с данными наиболее оптимальный подход - использование PipeLine дата интеграционных систем, систем класса ETL, таких как Informatica PowerCenter, Oracle Data Integrator, MS SQL Server Integration Services и других.

200. Используйте новые возможности последних версий СУБД, как то:
- операции с секциями (партициями);
- фильтрованные и другие проприетарные индексы;
- сжатие данных в таблицах и индексов;
- In-Memory операции.

Prev Next

Рекомендации по разработке баз данных