Привет, Хабр! Представляю Вашему вниманию небольшой список рекомендаций по разработке и сопровождению баз данных. Надеюсь будет полезным!
Я постарался составить достаточно универсальный список, но все же, некоторые советы могут быть не применимы к определенным базам данных (версиям) и типам приложений. Большинство рекомендаций было мной использовано в реальных проектах, например, в одном из проектов простое увеличение FetchSize позволили ускорить загрузку приложения в три раза.
Проектирование
Хорошая схема базы данных решает многие вопросы в приложении, постарайтесь изучить и применять лучшие практики. Плохо спроектированная схема доставит вам и вашим коллегам много хлопот.
Наиболее частый анти-паттерн в схемах это широкая таблица.
Приведу парочку таблиц, с которыми мне приходилось сталкиваться в проектах.
Table: Usl, Cols: Usl1..Usl88, далее в приложениях ну очень интересный код типа Usl1+..+Usl88
Table: DDUContracts, Cols: FirstPay,FirsPayDate, SecondPay, SecondPayDate - ну вы поняли, первой задачей в данном проекте мне было добавить третий платеж :)
Table: Contragents, Cols: Addr1, Addr2, Phone, MobilePhone - тоже реальный пример, попробуйте добавить третий адрес или новый тип телефона.
Table: GoodTable, Cols: Fld1,Fld2....Fld300
При проектировании схемы хранения, если сложно определиться с уровнем нормализации, лучше использовать третью нормальную форму.
В рамках проектируемой схемы должны быть приняты соглашения по именованию объектов, все создаваемые таблицы, представления, функции и прочие объекты базы данных должны создаваться по принятым соглашениям. Для упрощения поддержки соглашений рекомендуется использовать шаблоны и(или) вспомогательные утилиты по генерации объектов.
Все объекты (таблицы, колонки, представления и т.д.) необходимо тщательно комментировать. Если в используемой базе данных предусмотрены комментарии, желательно использовать данные механизмы; если база данных не поддерживает комментарий, можно разработать собственный механизм хранения комментариев.
Старайтесь минимизировать использование функций в условиях WHERE. Вызовы функций могут мешать использованию индексов и сильно замедлять получение данных.
При написании SQL запросов особое внимание стоит обращать на количество логических чтений. Большое количество логических чтений обычно указывает на недостаточное покрытие запроса индексами (Sargable).
В процессе проектирования и разработки базы данных старайтесь полностью изучить механизмы предоставляемые поставщиком базы данных, например, в Microsoft SQL Server последних версии существуют таблицы хранимые в памяти и хранимые вычисляемые колонки.
Если проект предусматривает поддержку нескольких баз данных, старайтесь применять ANSI SQL и не используйте слишком специфичные инструменты без сильной необходимости.
В современной практике разработки не рекомендуется использовать такой механизм баз данных, как хранимые процедуры, это сильно ограничивает возможность изменения поставщика базы данных.
Старайтесь изучать и избегать узкие места, заложенные проектировщиками используемой базы данных, например, функция NVL(a,b) в Oracle интерпретирует оба аргумента. Если аргумент b является подзапросом или вызовом функции, лучше использовать функцию COALESCE.
Примите во внимание, что сложные базы данных содержат достаточно много ошибок. Постарайтесь ознакомиться со списком известных ошибок и принять во внимание данный фактор.
Весь код исполняемых объектов (функции, процедуры, триггеры и т.д.) должен содержать подробное описание, желательно с работающими примерами вызовов и результатами.
Если в БД существует достаточно большое количество вспомогательных самописных функций и процедур, должен существовать подробный список с описанием. Вместо списка можно использовать отдельную схему или соглашения по именованию, например, вспомогательные функции по работе с датами можно создать в отдельной схеме UtilsDateTime.
Названия таблиц в множественном числе помогут избежать использования зарезервированных слов. Их, кстати, удивительно много в базах данных.
Рефакторинг и сопровождение баз данных
Настройте и протестируйте механизмы бекапов.
Настройте механизмы обслуживания, рекомендуемые для используемой базы данных. Обычно это дефрагментация индексов и перестроение статистики.
Для удобства разработки и сопровождения в достаточно больших командах принято использовать следующий набор баз данных (продуктовая, тестовая, стенд, общая или индивидуальная для разработки). На текущий момент очень удобно разворачивать индивидуальные базы данных для разработчиков с использованием Docker.
Запланируйте регулярный аудит схем БД, например, удаляйте неиспользуемые колонки.
Поскольку SQL код бывает достаточно плотным и не всегда хорошо читабельным, старайтесь оставлять подробные комментарии в данных случаях.
Старайтесь комментировать или давать описательные имена всем магическим константам, также иногда можно выносить константы в простые SQL функции.
Для улучшения читабельности рассмотрите возможность использования CTE.
В определенных условиях не всегда удается стандартными SQL средствами получить требуемую производительность. В данном случае можно использовать кэширующие таблицы, вычисляемые хранимые колонки, материализованные представления и прочие механизмы и методы для достижения требуемых параметров производительности.
Все колонки в БД по размерности должны быть максимально приближены к хранимым значениям, слишком большие колонки могут негативно повлиять на производительность и место занимаемое таблицами.
Если при проектировании таблиц заранее известно про очень большую размерность таблиц, необходимо предусмотреть механизм секционирования таблиц.
Все изменения в объектах БД также необходимо хранить в GIT или любой другой используемой на проекте системе контроля версий.
Настройте логирование изменений объектов БД, обычно это триггер уровня БД и таблица, в которую триггер записывает все изменения в объектах БД.
Разработка клиентских приложений и сервисов с использованием баз данных
Следите за размером строк в байтах, генерируемые запросами на чтение данных. Неоправданно большие размеры строк могут негативно сказаться на производительности, как БД, так и клиентских приложений. В исключительных случаях можно увеличить параметр FetchSize в провайдере данных для ускорения получения данных клиентским приложением.
Некоторые базы данных и провайдеры обеспечивают возможности для эффективной отмены SQL запросов, в данном случае в интерфейс можно добавить кнопку для отмены запросов.
В запросах старайтесь запрашивать только данные, которые нужны в вашем приложении. В некоторых случаях логирование неиспользуемых данных можно достаточно просто добавить в код приложения.
В проектах с большой нагрузкой постарайтесь изучить и правильно использовать ConnectionPool.
Для повышения производительности приложений рассмотрите возможность использования асинхронных запросов.
Для повышения производительности приложений старайтесь по возможности кешировать данные полученные из базы данных на клиенте.
С некоторыми операциями базы данных справляются достаточно плохо, в данном случае рассмотрите вопрос переноса операции на сторону клиента, например, склеивание blob файлов в MS-SQL может быть достаточно медленным.
