Для чего нужна денормализация базы данных
Зачем нужна денормализация баз данных, и когда ее использовать
В нашем блоге на Хабре мы не только рассказываем о развитии своего продукта — биллинга для операторов связи «Гидра», но и публикуем материалы о работе с инфраструктурой и использовании технологий.
Недавно мы писали об использовании Clojure и MongoDB, а сегодня речь пойдет о плюсах и минусах денормализации баз данных. Разработчик баз данных и финансовый аналитик Эмил Дркушич (Emil Drkušić) написал в блоге компании Vertabelo материал о том, зачем, как и когда использовать этот подход. Мы представляем вашему вниманию главные тезисы этой заметки.
Что такое денормализация?
Обычно под этим термином понимают стратегию, применимую к уже нормализованной базе данных с целью повышения ее производительности. Смысл этого действия — поместить избыточные данные туда, где они смогут принести максимальную пользу. Для этого можно использовать дополнительные поля в уже существующих таблицах, добавлять новые таблицы или даже создавать новые экземпляры существующих таблиц. Логика в том, чтобы снизить время исполнения определенных запросов через упрощение доступа к данным или через создание таблиц с результатами отчетов, построенных на основании исходных данных.
Непременное условие процесса денормализации — наличие нормализованной базы. Важно понимать различие между ситуацией, когда база данных вообще не была нормализована, и нормализованной базой, прошедшей затем денормализацию. Во втором случае — все хорошо, а вот первый говорит об ошибках в проектировании или недостатке знаний у специалистов, которые этим занимались.
Рассмотрим нормализованную модель для простейшей CRM-системы:
Пробежимся по имеющимся здесь таблицам:
Когда полезно использовать денормализацию
Прежде чем браться разнормализовывать то, что уже однажды было нормализовано, естественно, нужно четко понимать, зачем это нужно? Следует убедиться, что выгода от применения метода перевешивает возможные негативные последствия. Вот несколько ситуаций, в которых определенно стоит задуматься о денормализации.
Не все так гладко
Очевидная цель денормализации — повышение производительности. Но всему есть своя цена. В данном случае она складывается из следующих пунктов:
Денормализация на примере
В представленной модели были применены некоторые из вышеупомянутых правил денормализации. Синим отмечены новые блоки, розовым — те, что были изменены.
Что изменилось и почему?
Изменения в таблице product_sold сделаны по тем же соображениям. С той лишь разницей, что в данном случае речь идет о проданных наименованиях товара.
Денормализация — мощный подход. Не то чтобы к ней следует прибегать каждый раз, когда стоит задача увеличения производительности. Но в отдельных случаях это может быть лучшим или даже единственным решением.
Однако, прежде, чем принять окончательное решение об использовании денормализации, следует убедиться в том, что это действительно необходимо. Нужно провести анализ текущей производительности системы — часто денормализацию применяют уже после запуска системы в работу. Не стоит этого бояться, однако следует внимательно отслеживать и документировать все вносимые изменения, тогда проблем и аномалий данных не должно возникнуть.
Наш опыт
Мы в Латере много занимаемся оптимизацией производительности нашей биллинговой системы «Гидра», что неудивительно, учитывая объемы наших клиентов и специфику телеком-отрасли.
Один из примеров в статье предполагает создание таблицы с промежуточными итогами для ускорения отчетов. Конечно, самое сложное в этом подходе — поддерживать актуальное состояние такой таблицы. Иногда можно переложить эту задачу на СУБД — например, использовать материализованные представления. Но, когда бизнес-логика для получения промежуточных результатов оказывается чуть более сложной, актуальность денормализованных данных приходится обеспечивать вручную.
«Гидра» имеет глубоко проработанную систему привилегий для пользователей, операторов биллинга. Права выдаются несколькими способами — можно разрешить определенные действия конкретному пользователю, можно заранее подготовить роли и выдать им разные наборы прав, можно наделить определенный отдел специальными привилегиями. Только представьте, насколько медленными стали бы обращения к любым сущностям системы, если бы каждый раз нужно было пройти всю эту цепочку, чтобы убедиться: «да, этому сотруднику разрешено заключать договоры с юридическими лицами» или «нет, у этого оператора недостаточно привилегий для работы с абонентами соседнего филиала». Вместо этого мы отдельно храним готовый агрегированный список действующих прав для пользователей и обновляем его, когда в систему вносятся изменения, способные на этот список повлиять. Сотрудники переходят из одного отдела в другой намного реже, чем открывают очередного абонента в интерфейсе биллинга, а значит вычислять полный набор их прав нам приходится настолько же реже.
Конечно, денормализация хранилища — это только одна из принимаемых мер. Часть данных стоит кэшировать и непосредственно в приложении, но если промежуточные результаты в среднем живут намного дольше, чем пользовательские сессии, есть смысл всерьез задуматься о денормализации для ускорения чтения.
Интернет и базы данных. Часть 03. Денормализация базы данных
Мы хорошо потрудились, нормализовав таблицы базы до четвертой нормальной формы. Зачем же сейчас возвращаться назад?
Для чего нужна денормализация
Рассмотрим некоторые распространенные ситуации, в которых денормализация может оказаться полезна.
Большое количество соединений таблиц
В такой ситуации поможет денормализация путем сокращения количества таблиц. Лучше объединять в одну несколько таблиц, имеющих небольшой размер, содержащих редко изменяемую (как часто говорят, условно-постоянную, или нормативно-справочную) информацию, причем информацию, по смыслу тесно связанную между собой.
Расчетные значения
Зачастую медленно выполняются и потребляют много ресурсов запросы, в которых производятся какие-то сложные вычисления, особенно при использовании группировок и агрегатных функций (Sum, Max и т.п.). Иногда имеет смысл добавить в таблицу 1-2 дополнительных столбца, содержащих часто используемые (и сложно вычисляемые) расчетные данные.
Длинные поля
Если у нас в базе данных есть большие таблицы, содержащие длинные поля (Blob, Long и т.п.), то серьезно ускорить выполнение запросов к такой таблице мы сможем, если вынесем длинные поля в отдельную таблицу. Хотим мы, скажем, создать в базе каталог фотографий, в том числе хранить в blob-полях и сами фотографии (профессионального качества, с высоким разрешением, и соответствующего размера). С точки зрения нормализации абсолютно правильной будет такая структура таблицы:
Как правильно проводить денормализацию
С MySQL сложнее. В MySQL версии 4.1 (последняя на момент написания статьи стабильная версия) триггеров и хранимых процедур нет вообще. Поэтому заботиться об обеспечении непротиворечивости данных в денормализованной базе должны разработчики приложений.
Денормализация базы данных
Всем привет! Сегодня мы с Вами подробно рассмотрим процесс денормализации базы данных, Вы узнаете, что такое денормализация, как и зачем вообще проводить эту денормализацию.
Нормализация базы данных
В предыдущих материалах мы подробно рассмотрели нормализацию базы данных и каждую нормальную форму базы данных, вот соответствующие статьи:
Сегодня мы с Вами поговорим об обратном процессе, т.е. о денормализации.
Денормализация
Ранее мы выяснили, что данные в базе данных хранятся в таблицах, иными словами, база данных – это набор таблиц.
Суть проектирования правильной базы данных заключается в том, что необходимо сформировать тот набор таблиц, который будет отражать предметную область и соответствовать требованиям этой предметной области и требованиям разрабатываемой системы.
Проще говоря, необходимо сделать так, чтобы сформированные таблицы можно было удобно и эффективно использовать в работе, они не должны содержать каких-то аномалий и противоречивых данных, именно это и является целью проектирования базы данных.
В процессе проектирования базы данных нам на помощь приходит нормализация, которая помогает привести базу данных к тому состоянию, которое будет удовлетворять в первую очередь нашим требованиям, т.е. требованиям предметной области и требованиям разрабатываемой системы.
Иными словами, нормализация — это не процесс целенаправленного приведения базы данных к какой-то определенной нормальной форме, нормализация – это набор принципов, зная и следуя которым, мы можем спроектировать базу данных, структура которой будет гарантировать нам отсутствие определенного рода аномалий.
Таким образом, нет требования, которое обязывало бы нас приводить базу данных к максимально возможной нормальной форме, например, к 5 или 6, и не нужно нормализовать базу данных только для того, чтобы она была нормализована.
Максимально нормализованная база данных – это плохая база данных.
Дело в том, что нормализация базы данных до 5 или 6 нормальной формы значительно снижает производительность, работа с такой базой будет неэффективна и неудобна, а управлять ей будет очень сложно.
Поэтому разработчики с целью нахождения компромисса между устранением аномалий и повышением производительности могут не выполнять требования той или иной нормальной формы, более того, они могут прибегать к так называемой денормализации, т.е. обратному процессу нормализации.
Денормализация — намеренное приведение структуры базы данных в состояние, не удовлетворяющее требованиям нормализации.
Денормализация обычно проводится путем добавления избыточных данных в таблицу, т.е. тех данных, которые по требованиям той или иной нормальной формы должны выноситься в отдельную таблицу.
Например, в процессе эксплуатации базы данных у Вас возникла необходимость добавить новую характеристику, однако по требованиям определенной нормальной формы Вы должны под эту характеристику создать новую таблицу, однако требования предметной области и требования разрабатываемой системы позволяют этого не делать, при этом Вы четко понимаете, что невынесение этих данных в отдельную таблицу положительно скажется на эксплуатации базы данных. В этом случае Вы выполняете как раз денормализацию.
Также денормализация проводится и в процессе проектирования базы данных, например, когда по требованиям той или иной нормальной формы Вы должны выполнить декомпозицию таблицы, а Вы ее намерено не выполняете, все по тем же причинам, т.е. это удовлетворяет Вашим требованиям.
Методика нормализации базы данных
Существует методика нормализации, которая поможет Вам спроектировать достаточно хорошую базу данных. Суть ее в следующем.
Вы проектируете базу данных в соответствии с требованиями предметной области и требованиями разрабатываемой системы, при этом Вы строго соблюдаете все принципы нормализации и приводите базу данных практически к максимально возможной в Вашем случае нормальной форме.
В итоге Вы получаете базу данных, которая по идее не будет содержать аномалий. Однако такая версия базы данных скорей всего будет иметь не очень хорошую производительность, и с ней не очень удобно будет работать.
Поэтому затем Вы анализируете полученную структуру и руководствуясь здравым смыслом выявляете таблицы, которые можно было бы не декомпозировать, и никаких требований предметной области, которые обязывали бы делать такую декомпозицию, нет.
И таким образом, Вы выполняете денормализацию.
В конечном итоге Вы получите базу данных, в которой будет минимум аномалий, и при этом она будет иметь хорошую производительность.
Поэтому процесс проектирования базы данных иногда называют процессом нахождения компромисса между нормализацией и денормализацией, т.е. между устранением аномалий и повышением производительности.
Дело в том, что компромисс между этими составляющими даст наилучший результат, т.е. наилучшую базу данных, в каждом конкретном случае, в каждой предметной области, т.е. нормализация, как это уже не раз было отмечено, зависит от требований предметной области и требований разрабатываемой системы.
Отсюда мы можем сформулировать определение хорошей базы данных.
Хорошая база данных – это база, которая достаточно нормализована, чтобы не создавать аномалии для пользователей этой базы данных, в то же время она достаточно денормализована, чтобы иметь хорошую производительность.
Единственное, следует отметить, что в общей практике 3 нормальная форма является как раз тем компромиссом между отсутствием аномалий и приемлемой производительностью, поэтому база данных, находящаяся в третьей нормальной форме, считается достаточно нормализованной, и ее принципы все-таки рекомендовано соблюдать.
Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.
На сегодня это все, надеюсь, материал был Вам полезен и интересен, пока!
Нормализация и денормализация базы данных, нормальные формы
Нормализация схемы реляционной БД оказывает существенное влияние буквально на все аспекты взаимодействия с БД: от затрат на модификацию структур и данных до производительности запросов приложений и хранимых объёмов информации. В ряде случаев структуры могут быть сознательно денормализованы, что созвучно с другим словом «деморализованы». Однако, следует хорошо понимать, с какой целью это было сделано и полностью отдавать себе отчёт о последствиях. В общем же случае безопаснее всего придерживаться простого правила:
Нормализация — не догма, но чтобы её нарушать, нужны основания
На практике проектирования схем баз данных достижение третьей нормальной формы (3НФ) считается достаточным условием для большинства случаев.
Первая нормальная форма (1НФ) выполняется, если все значения атрибутов (читай, колонок таблицы) атомарны, то есть неделимы.
Собственные типы данных СУБД считаются атомарными, исключение могут составлять массивы, в том числе символьные (текстовые) и байтовые. Следует также понимать, что атомарность может быть относительна выбранного взгляда со стороны предметной области и контекста. Например, телефонный номер в базе данных маркетинга содержится в одной колонке, тогда как у телефонных операторов он разделяется на номера АТС, шлейфов и т.п. Колонки для хранения комментариев, подлежащих последующей обработке приложением, также отчасти нарушают принцип атомарности.
По этой же причине не стоит рассматривать отдельно целую и дробные части действительного числа или даже пару «дата-время»: дальнейшая детализация не имеет смысла с точки зрения моделируемой области, где они атомарны.
Предположим, мы нарушили 1НФ и стали хранить фамилии, имена и отчества клиентов в одной колонке. Пока операторы вносили информацию, эта ошибка проектирования особенно не мешала, Однако, на следующем этапе понадобилась отчётность, в которой ФИО клиентов выводились бы в виде фамилии и инициалов. Оказалось, что некоторые записи вместо «Сидоров Петр Иванович» содержат «Петр Иванович Сидоров», в других отчества нет вовсе, в третьих фамилия двойная и не всегда записана через тире, в четвёртых после фамилий расставлены запятые. Эту проблему пришлось решать программированием совсем нетривиальной логики с элементами распознавания по словарю. Было потрачено много времени и средств, но в отчётности нет-нет да и проскакивали непонятные значения типа «Оглы П.Б.Б.».
Следует отметить, что при добавлении к этому учёту клиентов- иностранцев, проектировщиков логической схемы БД не спасла бы и более структурированная форма из трёх колонок для раздельного хранения фамилий, имён и отчеств. Потому что это проблема уровня концептуального проектирования и соответствующих моделей: необходим синтез не привязанной к модели данных структуры, способной вмещать в себя комбинации имён людей разных стран и культур.
Вторая нормальная форма (2НФ) означает, что выполнены требования 1НФ, при этом все атрибуты целиком зависят от составного ключа и не зависят ни от какой его части.
На первый взгляд кажется, что нарушения 2НФ практически невозможны, потому что чаще всего в качестве первичных ключей используются автоинкрементные целочисленные значения или иные суррогаты для реализации ссылок. Однако, в определении говорится о ключах вообще, а не только о первичных. В отношении может быть несколько ключей, и некоторые из них могут являться составными. Такие ключи следует подвергнуть проверке в первую очередь.
Ассоциативная таблица — таблица, имеющая ключевые связи с двумя и более таблицами
Например, если каждая операция сбыта мебельной продукции в таблице продаж однозначно характеризуется колонками идентификатора товарной позиции, даты продажи и идентификатором покупателя, то нахождение в той же таблице столбца «Тип материала», зависящего непосредственно от товарной позиции, должно немедленно привлечь ваше внимание.
Аномалия в данном случае приведёт только к избыточности хранения в виде размера идентификатора, помноженного на число строк таблицы (без учёта индексов). Но если в той же таблице обнаружится ещё и колонка «Контактный телефон», присущая атрибутике покупателя, то последствия окажутся более серьёзными. Кроме избыточности хранения при ошибке ввода придётся исправлять номер телефона во всех записях о продажах данному покупателю.
Кроме приведённых примеров, при наличии в таблицах нескольких ключей необходимо, с позиций логики предметной области, определить, являются ли эти ключи присущими данной сущности или же они суть внешние ключи другой сущности, пока ещё не выделенной в процессе проектирования.
Третья нормальная форма (3НФ) означает, что выполнены требования 2НФ, при этом в между атрибутами отношения нет транзитивных зависимостей.
Что такое транзитивная зависимость легко понять на примере уже упоминавшейся выше таблицы продаж — типичного примера ассоциативной таблицы.
Предположим, что продажа каждой товарной позиции имеет своим основанием документ (заказ, счёт и т.д.), а её стоимость характеризуется ценой, количеством и валютой. В этом случае имеем следующие зависимости между атрибутами (колонками):
Эти зависимости транзитивны: каждая продажа однозначно определяет свой документ-основание и расчётную валюту, однако, валюта определяется ещё и документом.
Результатом нарушения 3НФ является избыточность хранения и необходимость обновления данных в связанной таблице. Так, если вы оставите колонку «Код валюты» в таблице продаж, то при изменении валюты документа придётся также обновлять все связанные с ним строки продаж.
Демормализация в базе данных: «звезда» и «снежинка»
Как можно понять из вышеприведённых примеров, основными целями нормализации являются:
Но список заявленных целей касается приложений транзакционных.
В приложениях интерактивной аналитической обработки приоритет меняется: на первый план выходит время отклика системы, в ущерб которому данные могут быть избыточны.
Зачем нужна денормализация?
Наиболее дорогостоящая с точки зрения вычислительных ресурсов операция между большими таблицами — соединение. Соответственно, если в одном запросе необходимо «провентилировать» несколько таблиц, состоящих из многих миллионов строк, то СУБД потратит достаточно много времени на такую обработку. Пользователь в это время может отойти выпить кофе. Интерактивность обработки практически исчезает и приближается к таковой для обработки пакетной. Даже хуже, в пакетном режиме пользователь с утра получает все запрошенные накануне данные и спокойно работает с ними, подготавливая новые запросы к вечеру.
Чтобы избежать ситуации тяжёлых соединений таблицы денормализуют. Но не абы как. Существуют некоторые правила, позволяющие считать денормализованные с точки зрения транзакционной обработки таблицы «нормализованными» согласно правилам построения таблиц для хранилищ данных.
Основных схем, считающихся «нормальными» в аналитической обработке, две: «снежинка» и «звезда». Названия хорошо отражают суть и следуют непосредственно из картинки связанных таблиц.
В обоих случаях центральным элементом схемы являются так называемые таблицы фактов, содержащие интересующие аналитика события, транзакции, документы и другие занятные вещи. Но если в транзакционной БД один документ «размазан» по нескольким таблицам (как минимум по двум: заголовки и строки-содержание), то в таблице фактов одному документу, точнее, каждой его строке или набору сгруппированных строк, соответствует одна запись. Сделать это можно денормализацией двух вышеупомянутых таблиц.
Рис. 1. Денормализация документов в таблицу фактов
Теперь можно оценить, насколько облегчится для выполнения СУБД запрос, например, следующего вида: определить объёмы продаж муки клиентам «ООО Пирожки» и «ЗАО Ватрушки» за период.
В нормализованной транзакционной БД:
В аналитической БД:
Вместо тяжёлого соединения между двумя таблицами документов и их состава с миллионами строк, СУБД достаётся прямая работа с таблицей фактов и лёгкие соединения с небольшими вспомогательными таблицами, без которых также можно обойтись, зная идентификаторы.
Вернёмся к схемам «звезда» и «снежинка». За кадром первого рисунка остались таблицы клиентов, их групп, магазинов, продавцов и, собственно, товаров. При денормализации эти таблицы, называющиеся измерениями, также соединяются с таблицей фактов. Если таблица фактов ссылается на таблицы-измерения, имеющие ссылки на другие измерения (измерения второго уровня и выше), то такая схема называется «снежинка».
Рис. 2. Таблица фактов в схеме «снежинка»
Как можно заметить, для запросов, включающих фильтрацию по группам клиентов, приходится делать дополнительное соединение.
В таком случае денормализацию можно продолжить и опустить измерение второго уровня на первый, облегчив запросы к таблице фактов.
Схема, в которой таблица фактов ссылается только на измерения, не имеющие второго уровня, называется «звезда». Число таблиц измерений соответствует числу «лучей» в звезде.
Схема «Звезда» полностью исключает иерархию измерений и необходимость соединения соответствующих таблиц в одном запросе.
Рис. 3. Таблица фактов в схеме «звезда»
Обратной стороной денормализации всегда является избыточность, являющаяся причиной увеличения размера БД как в случае транзакционных, так и аналитических приложений. Давайте посчитаем примерную дельту на приведённом выше примере преобразования «снежинки» в «звезду».
В некоторых СУБД, например Oracle, специальные целочисленные типы на уровне определений схемы БД отсутствуют, необходимо использовать универсальный логический тип numeric(N), где N — число хранимых разрядов. Размер хранения такого числа рассчитывается по специальной формуле, приводимой в документации по физическому хранению данных, и, как правило, он превышает таковой для низкоуровневых типов вроде «16битное целое» на 1-3 байта.Положим, таблица продаж не использует компрессию данных и содержит около 500 миллионов строк, а количество групп покупателей порядка 1000. В этом случае мы можем использовать в качестве типа идентификатора id_customer_group короткое целое (shortint, smallint), занимающее 2 байта.
Будем считать, что наша СУБД поддерживает двухбайтовый целочисленный тип (например, PostgreSQL, SQL Server, Sybase и другие). Тогда добавление соответствующей колонки id_customer_group в таблицу продаж вызовет увеличение её размера как минимум на 500 000 000 * 2 = 1 000 000 000 байт