Эта статья была впервые написана в 2009 году, когда я работал консультантом в специализированной компании по обработке данных и аналитике, расположенной в районе DFW Metroplex. Первоначально озаглавленный «Дизайн OLTP, дружественный к OLAP: предварительное планирование для неизбежного хранилища данных», суть заключалась в том, что данные имеют внутреннюю ценность, вы собираете их в базе данных по какой-то причине, и в конечном итоге кто-то будут либо любить вас, либо ненавидеть, когда им будет поручено извлечь это из этой базы данных, которую вы разрабатываете, так что делайте это хорошо.

Когда я недавно откопал эту статью, я подумал, что было интересно посмотреть, как мало что изменилось в этой индустрии. Реляционные базы данных по-прежнему играют ключевую роль в области аналитики, базовые методы проектирования реляционных баз данных по-прежнему действительны на 100%, а ценность данных выше, чем когда-либо.

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

Независимо от того, какие данные вы собираете и храните в своих OLTP-приложениях, кто-то в конечном итоге захочет сообщить об этом. Сегодня хранилище данных, столь же широко распространенное в корпорациях, как электронная почта, стало одним из столпов ИТ-инфраструктуры. Если у вашей компании нет хранилища данных, скорее всего, они действительно есть, но вы просто не знаете об этом, или есть планы по созданию хранилища данных, о которых вы еще не слышали. Это означает, что в конечном итоге вы обнаружите, что запросы с интенсивным вводом-выводом бьют ваши базы данных OLTP пугающе периодическим образом в нечетные часы ночи.

Если ваш дизайн заслуживает внимания, и если разработчики этих интенсивных запросов так же хороши в дизайне, как и вы, вы также, надеюсь, обнаружите, что продолжительность и влияние этих запросов минимальны. Первое описанное мною открытие неизбежно. Второе открытие будет определяться вами, разработчиком OLTP.

В таком случае мы должны начать проектировать наши приложения и базы данных в расчете на то, что хранилище данных будет использовать их для получения корпоративной информации. Я твердо верю, что мы должны сделать это привычкой и основной практикой развития. Независимо от того, являетесь ли вы дизайнером баз данных, программистом чистого промежуточного программного обеспечения (например, Java / NET) в серверной части или дизайнером «взаимодействия с пользователем» во внешнем интерфейсе, вы должны сыграть свою роль в хорошем проектировании OLTP.

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

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

Используйте метки даты аудита для сбора измененных данных (CDC)

Сбор данных об изменениях. Возможность определить, какие данные изменились с момента последнего запроса. Это критическая функция большинства архитектур извлечения, преобразования и загрузки (ETL) хранилищ данных, поскольку она определяет, можно ли постепенно обновлять таблицы хранилища данных, используя только действия исходной системы, которые произошли за последние 24 часа, а не перестраивать эти таблицы. с нуля каждую ночь со всей исторической активностью. Разницу во времени пакетной обработки и циклах ЦП между этими двумя подходами часто можно измерить на несколько порядков.

В последних выпусках продуктов крупных поставщиков ETL и СУБД рекламируются новые функции, поддерживающие эту функциональность. Некоторые из этих функций довольно сложны по своей реализации и требуют включения определенных опций продукта, которые имеют свои собственные последствия и должны быть тщательно рассмотрены перед переключением переключателя CDC. Одним из основных соображений дизайна, который решит большую часть проблемы CDC для ваших проектов, является последовательное использование штампов даты аудита на ваших таблицах.

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

В последнее время я также начал включать столбец «версия аудита» в свои конструкции таблиц - чему я научился у одного из наших клиентов. Этот атрибут также поддерживается триггерами предварительной вставки / предварительного обновления таблицы. Версия аудита (или AUD_VER) изначально заполняется значением 1 при первой вставке строки и увеличивается на 1 для каждой последующей операции обновления. Он не только сообщает мне, когда строка была вставлена ​​и когда она последний раз обновлялась, но теперь я знаю, сколько раз строка была изменена с момента ее создания.

Этот изящный маленький атрибут очень пригодился на этапах тестирования при анализе того, какие данные были изменены. В приложениях, где задействован рабочий процесс, AUD_VER также может быть весьма полезным для определения того, где находится конкретная строка в процессе перехода между состояниями. Я даже начал использовать этот атрибут в своих проектах хранилища данных. Простые, элегантные и ненавязчивые колонки аудита, подобные тем, что я описал, - небольшая плата за эффективную стратегию CDC.

Воспользуйтесь преимуществом разделения таблицы

Одним из наиболее незаметных и в то же время положительных улучшений многих платформ РСУБД в недавнем прошлом является возможность разделения таблиц. Разделение таблиц не является доступной функцией во всех СУБД, но имеет преимущества в плане производительности и администрирования, которые мы, как дизайнеры OLTP, должны учитывать для долгосрочного успеха наших базовых баз данных.

Соответствующие преимущества, которые дает нам этот вариант, - это повышение производительности и упрощение административных операций. Вы можете подумать: «Я не администратор баз данных, так зачем мне заботиться об административных задачах?» Что ж, каким бы преимуществом ни было разделение таблиц, даже хорошие администраторы баз данных не всегда задумываются об этом. Чем больше вы знаете о доступных вам возможностях, тем лучше будут ваши приложения.

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

Этот процесс изоляции запроса называется сокращением раздела. База данных определяет, какие сегменты (разделы) базовой таблицы необходимы для обслуживания запроса. Когда запросы хранилища данных начинают получать доступ к таблице заказов, влияние на базу данных OLTP теперь гораздо более вероятно будет ограничено по объему. В сочетании с возможностью секционирования индексов и использования параллельного выполнения на некоторых платформах баз данных секционирование таблицы дает много преимуществ.

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

И самое лучшее я оставил напоследок. Доступ к многораздельной таблице из вашего приложения идентичен доступу к несекционированной таблице для обычных операций DML (например, CRUD). Это означает, что вы можете разделить существующую таблицу, лежащую в основе вашего OLTP-приложения, без необходимости изменять ни одной строчки кода. Однако будет справедливо сказать, что степень повышения производительности вашего приложения в результате секционирования определяется рядом факторов. Если ваши таблицы небольшие и / или если вы разбили ключевой столбец, который не используется ни в одном запросе, созданном вашим приложением, скорее всего, вы не заметите никаких улучшений в производительности. Это еще одна причина изучить возможности вашей базовой базы данных на раннем этапе. Если вы проектируете с учетом разделения, вы можете быть уверены, что другие узнают в вас мудрого архитектора, задумавшего о долгосрочной перспективе.

Используйте суррогатные первичные ключи

Без вопросов - во всех случаях и всегда - используйте в таблицах первичные ключи, генерируемые последовательностью (т.е. суррогатные), с одним столбцом. Основная причина для этого заключается в том, что устаревшие схемы нумерации или естественные ключи (например, идентификатор клиента, номер заказа и т. Д.), Которые исходят из операционных систем, находятся вне вашего контроля и, следовательно, не должны доверять или полагаться на них. Как только вы сделаете свой дизайн зависимым от чего-то, что вы не контролируете, поверьте мне, он изменится, и ваш дизайн сломается. Спасите себя и тех, кого вы оставите на своем пути, от головной боли такого сценария и с самого начала используйте суррогатные ключи. Если ваша СУБД поддерживает генераторы последовательностей или столбцы IDENTITY, используйте их. Вот для чего они нужны. Обычно я позволяю своим триггерам предварительной вставки / обновления таблицы управлять этой работой за меня, поскольку они уже используются как часть моей стратегии CDC.

Теперь, несмотря на все это, по-прежнему важно заботиться об этих естественных ключах, сохраняя их целостность и соответствующим образом индексируя их. Хранилище данных будет во многом полагаться на них при создании медленно изменяющихся измерений (SCD). Как только ваши данные попадают в хранилище данных, естественные ключи обычно являются последним сохранившимся звеном обратной связи с исходной операционной системой.

Рационализируйте по мере нормализации

Один мудрый администратор базы данных однажды сказал мне:

Нормализируйте «пока не болит, не нормализируйте», пока не сработает.

Я обнаружил, что эта стратегия очень практична в моих собственных проектах. Ключ - найти правильный баланс. Уменьшите или полностью устраните избыточность, насколько это возможно, и оставьте столько, сколько вам нужно, чтобы сделать приложение интуитивно понятным и хорошо работающим. Здоровый уровень денормализации также принесет пользу составителям отчетов и, в конечном итоге, разработчикам ETL хранилищ данных. Не существует жесткого правила определения «здорового» для любого конкретного дизайна, но хороший дизайнер распознает чрезмерную нормализацию и будет достаточно дальновидным, чтобы сократить его. Как отмечалось в предыдущем разделе, при нормализации следует помнить об одной конкретной стратегии - это использование первичных ключей с одним столбцом, а не с первичными ключами с несколькими столбцами (составными).

Вот общее практическое правило: если при написании SQL для проекта вашей базы данных вы обнаружите, что количество критериев соединения превышает количество таблиц в предложении FROM, вы, вероятно, чрезмерно нормализовали. Очень легко позволить вашему инструменту моделирования данных распространять внешние ключи в таблицы разрешения (т. Е. Сущности, которые разрешают отношения «многие ко многим») в качестве первичного ключа таблицы.

Однако у этого подхода есть несколько проблем. Во-первых, это нарушает вашу практику разработки суррогатных ключей, которая требует, чтобы вы всегда использовали первичные ключи, сгенерированные последовательностью, в своих таблицах. Вы можете подумать: «Что может повредить одна или две таблицы разрешения с многоколоночными ключами? Если я действительно ссылаюсь на них в операторе SQL, это все равно лишь один дополнительный критерий соединения, верно? "

Ну не всегда.

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

Поместите ссылочную целостность там, где она нужна - в базу данных

Ссылочная целостность или RI - это набор правил, предотвращающих несогласованность данных в базе данных. Этот набор правил принадлежит базе данных, а не вашему приложению. В начале моей ИТ-карьеры я работал с очень большим ERP-приложением. Это была фантастическая возможность, и я благодарен за опыт, который я получил от нее, поскольку он открыл двери для многих других прекрасных возможностей. Я помню, как думал про себя, когда копался во внутренностях этого конкретного пакета, насколько хорошо он был спроектирован. В то время я не понимал, но вскоре узнал, что вся ссылочная целостность в приложении встроена в код приложения, а не в надежную базу данных, в которой оно было развернуто.

В результате я начал замечать, что на досках вакансий появляются различные названия должностей типа «Application DBA», поскольку клиенты, внедрившие это приложение, столкнулись с необходимостью решения основных проблем производительности и обслуживания, к которым привела эта конструкция. Теперь я понимаю преимущества ограничений базы данных (или декларативного RI). Мы не можем полагаться только на приложение для сохранения целостности данных. Сейчас это может быть единственная точка входа, но что происходит, когда вводится стороннее приложение или интерфейс, которые каким-то образом влияют на данные? Внезапно этот грамотно спроектированный уровень RI на уровне приложения перестает действовать. Независимо от того, что разработчик делает с кодом приложения, я знаю, что могу положиться на ограничения базы данных для защиты целостности моих данных.

Правильно, вы меня правильно расслышали. Я сказал «мои данные».

Как разработчики OLTP, мы должны взять на себя ответственность за данные и стать распорядителями их, чтобы любой ценой сохранить их целостность. Неважно, какую роль вы играете в дизайне OLTP. Если дизайн приложения, в создание которого вы вносите свой вклад (и с которым вы теперь навсегда связаны), приводит к неточным отчетам или неадекватному доверию пользователей из-за низкого качества данных, считайте последующий «черный глаз» своим собственным. Ваша репутация разработчика приложений пострадает не меньше, чем конечный пользователь приложения, в разработке которого вы участвовали.

Обеспечьте соблюдение ограничений качества данных

Продолжая управление данными, мы также должны обеспечить соблюдение ограничений качества данных в наших проектах OLTP. Уникальность этой практики заключается в том, что ее можно применять на всех уровнях дизайна. Например, пользовательский интерфейс может защищать качество данных в форме «правок», которые вызываются на уровне ввода данных, и предотвращать попадание данных в базу данных. Точно так же база данных может (и должна) реализовывать NOT NULL и другие различные ограничения CHECK, чтобы предотвратить попадание недопустимых данных в базу данных.

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

Еще одна практическая причина для предотвращения недействительных данных - это огромные затраты на циклы ЦП и часы пакетной обработки ETL, необходимые для синтаксического анализа и очистки данных. Это ценное время лучше послужит делу CDC и других необходимых действий по извлечению, преобразованию и загрузке.

Не забывайте об индексах

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

В частности, процессы ETL хранилища данных по своей сути будут использовать правильно проиндексированный дизайн OLTP. Например, помните те столбцы CDC, которые мы обсуждали ранее? Они являются прекрасным примером столбцов, которые следует индексировать для поддержки обработки ETL. И не забывайте обо всех этих столбцах внешнего ключа, которые теперь являются частью вашего режима RI. Многие платформы РСУБД автоматически индексируют столбцы первичного ключа, но внешние ключи обычно игнорируются. Такие индексы не только ускорят обработку ETL, но и значительно снизят влияние ввода-вывода на вашу OLTP-систему, поскольку быстрое сканирование индекса, вероятно, заменит полное сканирование таблицы, которое в противном случае потребовалось бы.

Используйте согласованное соглашение об именах объектов базы данных

У каждого дизайнера баз данных есть свои предпочтения в отношении имен объектов. Я считаю, что эффективное соглашение об именах таблиц имеет особое значение, когда вы создаете свой дальновидный дизайн. Как разработчик ETL, я много раз пытался вывести смысл из базы данных OLTP, просматривая словарь данных. Одним из важнейших факторов, способствовавших моему пониманию этих моделей, было соглашение об именах таблиц.

Если я вижу таблицу с именем ORDER_DTL, мне сразу становится ясно несколько вещей. Во-первых, я, по всей вероятности, только что нашел таблицу сведений о заказе, а во-вторых, я легко смогу найти другую таблицу, связанную с ORDER, которая служит родительской сущностью для ORDER_DTL. Может, ORDER или ORDER_HDR? В любом случае, я просто собрал важную часть головоломки. Быстрый поиск в Интернете приведет к множеству мыслей о том, как назвать ваши объекты. Что касается личных предпочтений, мне нравится видеть объекты, названные таким образом, чтобы префикс соответствовал, а суффикс пояснял.

Взяв пример или ORDER_DTL, я сразу заметил, что таблица содержит данные о заказах и, кроме того, эта конкретная таблица содержит подробную информацию о заказах. Другие распространенные суффиксы, которые я использую, - это _LOG для таблиц журнала, _STG для промежуточных таблиц, _LKP для таблиц поиска кода и описания, и этот список можно продолжить. Еще одно преимущество использования префикса объекта в качестве квалификатора заключается в том, что при просмотре списка отсортированных имен объектов имена одного и того же типа субъекта сортируются вместе, и можно быстро получить представление о степени связанных с субъектом данных в базе данных.

Эта концепция распространяется и на другие типы объектов. В таблице ORDER_DTL я ожидал увидеть столбец с именем ORDER_ID, который составляет внешний ключ, ссылающийся на первичный ключ родительского объекта ORDER, и ORDER_DTL_ID, который является первичным ключом таблицы ORDER_DTL. Это рисует картину взаимоотношений, заложенных в дизайне. Не сбрасывайте со счетов важность решения о том, как назвать объекты базы данных.

Стоит потратить время на то, чтобы установить информативную конвенцию. Для начала не обязательно быть полностью законченным или обширным, но небольшая предусмотрительность на этапе моделирования данных - это небольшая плата за экономию времени, которую вы и другие получите в будущем.

Метаданные - не только для хранилищ данных

Метаданные предназначены не только для хранилищ данных - они начинаются с исходной системы. По крайней мере, до некоторой степени каждый разработчик OLTP должен приложить усилия, чтобы предоставить словарь данных, содержащий бизнес-определения всех сущностей и атрибутов вместе с базой данных. Словарь данных - это простейшая форма репозитория метаданных, который естественным образом переходит в хранилище данных.

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

Один из простых способов включить бизнес-определения - это пометить объекты базы данных комментариями. Платформы СУБД, такие как Oracle и SQL Server, хорошо поддерживают такие комментарии и делают их доступными для пользователей с доступом SELECT к словарю данных.

Воспользуйтесь этими функциями.

Я довольно религиозно отношусь к созданию комментариев к таблицам и столбцам просто потому, что это более или менее «халява метаданных», комплимент поставщика СУБД. Ценным побочным преимуществом этого является то, что я заставляю себя понимать данные, которые я храню, потому что для успешного завершения моего сценария сборки необходимо собрать бизнес-определение для каждого элемента моей базы данных.

Ведение таблиц поиска

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

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

Кроме того, каждый код в схеме кодирования (или поисковой таблице) составляет естественный ключ. Когда таблица измерений хранилища данных строится для хранения таких кодов, описания обычно извлекаются вместе с кодами, чтобы обеспечить средства CDC в наборе кодов. В случае таблицы медленно изменяющихся измерений изменение описания для конкретного кода вызовет деактивацию одной записи измерения для предыдущей «версии» этого кода и создание / активацию новой версии.

Также часть дебатов о дизайне таблиц поиска заключается в том, следует ли иметь одну таблицу для каждого набора кодов или вместо нее одну общую таблицу поиска (также известную как «Одна истинная таблица поиска» или OTLT) для хранения всех наборов кодов. Я призываю вас самостоятельно исследовать эту дискуссию, чтобы узнать о плюсах и минусах подхода OTLT, но я предложу одну из причин, по которой я предпочитаю не возвращаться к этому типу дизайна.

Наличие отдельных таблиц поиска для каждого набора кодов дает мне возможность специализировать каждую из них для соответствующего набора кодов. Например, для моей справочной таблицы «код штата / территории» требуется атрибут часового пояса, чтобы удовлетворить определенное бизнес-требование. В то же время моей поисковой таблице «код состояния» нужен атрибут, который указывает, активен ли конкретный код состояния (назовем его «активным индикатором»). Если бы я использовал дизайн OTLT, каждый код штата / территории также должен был бы поддерживать «активный индикатор». Точно так же, а может быть, даже хуже, каждый код состояния должен поддерживать атрибут часового пояса. Это не только сбивает с толку и бессмысленно, но еще и очень неэффективно.

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

Заключение

Вы слышите повторяющуюся тему? Это просто - продуманный дизайн. Суть в том, что в конечном итоге вам придется есть собственный корм для собак. Нравится вам это или нет, но ваш опыт и помощь будут востребованы в какой-то момент, чтобы помочь тем, кому поручено извлекать и / или составлять отчеты из базы данных вашего приложения. Возможно, вас даже попросят разработать отчеты. Облегчите себе, своим коллегам и тем, кого вы оставите, поддержку ваших приложений, проектируя с предусмотрительностью.

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

Какие ваши любимые условности и практики? Буду признателен за ваш отзыв!