Главная страница » Что такое нормализация и денормализация

Что такое нормализация и денормализация

  • автор:

Что такое нормализация и денормализация

Видео: Базы данных. 1,2,3 нормальные формы.

Нормализация против денормализации

Реляционные базы данных состоят из отношений (связанных таблиц). Таблицы состоят из столбцов. Если таблицы две большие (то есть слишком много столбцов в одной таблице), могут возникнуть аномалии базы данных. Если таблицы две маленькие (т.е.база данных состоит из множества небольших таблиц), это было бы неэффективно для запросов. Нормализация и денормализация — это два процесса, которые используются для оптимизации производительности базы данных. Нормализация сводит к минимуму избыточность, которая присутствует в таблицах данных. Денормализация (обратная нормализации) добавляет избыточные данные или групповые данные.

Что такое нормализация?

Нормализация — это процесс, который выполняется для минимизации избыточности, которая присутствует в данных в реляционных базах данных. Этот процесс в основном разделит большие таблицы на более мелкие с меньшим количеством избыточностей (так называемые «нормальные формы»). Эти меньшие таблицы будут связаны друг с другом четко определенными отношениями. В хорошо нормализованной базе данных любое изменение или модификация данных потребует изменения только одной таблицы. Первая нормальная форма (1NF), вторая нормальная форма (2NF) и третья нормальная форма (3NF) были введены Эдгаром Ф. Коддом. Нормальная форма Бойса-Кодда (BCNF) была введена в 1974 году Коддом и Раймондом Ф. Бойсом. Были определены высшие нормальные формы (4NF, 5NF и 6NF), но они используются редко.

Таблица, соответствующая 1NF, гарантирует, что она действительно представляет отношение (то есть не содержит никаких повторяющихся записей) и не содержит никаких атрибутов, которые имеют реляционное значение (т.е. все атрибуты должны иметь атомарные значения). Чтобы таблица соответствовала 2NF, она должна соответствовать 1NF, и любой атрибут, который не является частью какого-либо ключа-кандидата (то есть непервичные атрибуты), должен полностью зависеть от любого из ключей-кандидатов в таблице. Согласно определению Кодда, таблица считается принадлежащей 3NF, тогда и только тогда, когда эта таблица находится во второй нормальной форме (2NF), и каждый атрибут в таблице, который не принадлежит ключу-кандидату, должен напрямую зависеть от каждого ключ-кандидат этой таблицы. BCNF (также известный как 3.5NF) улавливает некоторые аномалии, которые не рассматриваются 3NF.

Что такое денормализация?

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

В чем разница между нормализацией и денормализацией?

— Нормализация и денормализация — два совершенно противоположных процесса.

— Нормализация — это процесс разделения больших таблиц на более мелкие, с уменьшением избыточных данных, а денормализация — это процесс добавления избыточных данных для оптимизации производительности.

— Нормализация проводится для предотвращения аномалий баз данных.

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

— Часто рекомендуется «нормализовать, пока не станет больно, денормализовать, пока не сработает».

Денормализация БД. Зачем? Когда? Как?

Рассмотрим некоторые распространенные ситуации, в которых денормализация может оказаться полезна.

Большое количество соединений таблиц.
  • денормализация путем сокращения количества таблиц. Лучше объединять в одну несколько таблиц, имеющих небольшой размер, содержащих редко изменяемую (как часто говорят, условно-постоянную, или нормативно-справочную) информацию, причем информацию, по смыслу тесно связанную между собой.
    В общем случае, если в большом количестве запросов требуется объединять более пяти или шести таблиц, следует рассмотреть вариант денормализации базы данных.
  • Денормализация путём ввода дополнительного поля в одну из таблиц. При этом появляется избыточность данных, требуются дополнительные действия для сохранения целостности БД.
Расчетные значения.

Зачастую медленно выполняются и потребляют много ресурсов запросы, в которых производятся какие-то сложные вычисления, особенно при использовании группировок и агрегатных функций (Sum, Max и т.п.). Иногда имеет смысл добавить в таблицу 1-2 дополнительных столбца, содержащих часто используемые (и сложно вычисляемые) расчетные данные.
Предположим, что необходимо определить общую стоимость каждого заказа. Для этого сначала следует определить стоимость каждого продукта (по формуле «количество единиц продукта» * «цена единицы продукта» – скидка). После этого необходимо сгруппировать стоимости по заказам.
Выполнение этого запроса является достаточно сложным и, если в базе данных хранятся сведения о большом количестве заказов, может занять много времени. Вместо выполнения такого запроса можно на этапе размещения заказа определить его стоимость и сохранить ее в отдельном столбце таблицы заказов. В этом случае для получения требуемого результата достаточно извлечь из данного столбца предварительно рассчитанные значения.
Создание столбца, содержащего предварительно рассчитываемые значения, позволяет значительно сэкономить время при выполнении запроса, однако требует своевременного изменения данных в этом столбце.

Длинные поля.

Если у нас в базе данных есть большие таблицы, содержащие длинные поля (Blob, Long и т.п.), то серьезно ускорить выполнение запросов к такой таблице мы сможем, если вынесем длинные поля в отдельную таблицу. Хотим мы, скажем, создать в базе каталог фотографий, в том числе хранить в blob-полях и сами фотографии (профессионального качества, с высоким разрешением, и соответствующего размера). С точки зрения нормализации абсолютно правильной будет такая структура таблицы:
ID фотографии
ID автора
ID модели фотоаппарата
сама фотография (blob-поле).
А сейчас представим, сколько времени будет работать запрос, подсчитывающий количество фотографий, сделанных каким-либо автором…
Правильным решением (хотя и нарушающим принципы нормализации) в такой ситуации будет создать еще одну таблицу, состоящую всего из двух полей — ID фотографии и blob-поле с самой фотографией. Тогда выборки из основной таблицы (в которой огромного blob-поля сейчас уже нет) будут идти моментально, ну а когда захотим посмотреть саму фотографию — что ж, подождем…

Как определить, когда денормализация оправдана?
Затраты и выгоды.

Один из способов определить, насколько оправданны те или иные шаги, — провести анализ в терминах затрат и возможных выгод. Во сколько обойдется денормализованной моделью данных?
Определить требования (чего хотим достичь) -> определить требования к данным (что нужно соблюдать) -> найти минимальный шаг, удовлетворяющий эти требования -> подсчитать затраты на реализацию -> реализовать.
Затраты включают в себя физические аспекты, такие как дисковое пространство, ресурсы, необходимые для управления этой структурой, и утраченные возможности из-за временных задержек, связанных с обслуживанием этого процесса. За денормализацию нужно платить. В денормализованной базе данных повышается избыточность данных, что может повысить производительность, но потребует больше усилий для контроля за связанными данными. Усложнится процесс создания приложений, поскольку данные будут повторяться и их труднее будет отслеживать. Кроме того, осуществление ссылочной целостности оказывается не простым делом — связанные данные оказываются разделенными по разным таблицам.
К преимуществам относится более высокая производительность при выполнении запроса и возможность получить при этом более быстрый ответ. Кроме того, можно получить и другие преимущества, в том числе увеличение пропускной способности, уровня удовлетворенности клиентов и производительности, а также более эффективное использование инструментария внешних разработчиков.

Частота запросов и устойчивость производительности.

Например, 70% из 1000 запросов, ежедневно генерируемых предприятием, представляют собой запросы уровня сводных, а не детальных данных. При использовании таблицы сводных данных запросы выполняются примерно за 6 секунд вместо 4 минут, т.е. время обработки меньше на 2730 минут. Даже с поправкой на те 105 минут, которые необходимо еженедельно тратить на поддержку таблиц сводных данных, в итоге экономится 2625 минут в неделю, что полностью оправдывает создание таблицы сводных данных. Со временем может случиться так, что большая часть запросов будет обращена не к сводным данным, а к детальным данным. Чем меньше число запросов, использующих таблицу сводных данных, тем проще от нее отказаться, не затрагивая другие процессы.

Прочее

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

Как грамотно реализовать денормализацию.
Сохранить детальные таблицы

Чтобы не ограничивать возможности базы данных, важные для бизнеса, необходимо придерживаться стратегии сосуществования, а не замены, т.е. сохранить детальные таблицы для глубинного анализа, добавив к ним денормализованные структуры. Например, счётчик посещений. Для бизнеса необходимо знать количество посещений веб-станицы. Но для анализа (по периодам, по странам …) нам очень вероятно понадобятся детальные данные – таблица с информацией о каждом посещении.

Использование триггеров

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

Программная поддержка

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

Технарь

Блог о программировании и околопрограммерских штуках.

Основы реляционных БД

Не забываем основы!

Реляционная БД:

Реляционная БД (Relational database) — это база данных с табличной организацией данных.

Где:
Отношение (relation, base relvar) — таблица (table);
Имена атрибутов (attribute) — столбцы (column) этой таблицы;
Кортеж (tuple) — отдельная строка (row) или запись в таблице
Индекс (index) — это отсортированный список значений полей, предназначенный для ускорения поиска в базе данных.

Нормальные формы:

1-ая Нормальная форма

Переменная отношения находится в первой нормальной форме (1НФ) тогда и только тогда, когда в любом допустимом значении отношения каждый его кортеж содержит только одно значение для каждого из атрибутов.

Т.е. эта таблица ненормализована:

ФИО: Адрес:
Иванов И.И. Пушкинская ул.
Лермонтова ул.
Петров П.П. ул. Толстого
ФИО: Адрес:
Иванов И.И. Пушкинская ул.
Иванов И.И. Лермонтова ул.
Петров П.П. ул. Толстого

2-ая Нормальная форма

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

Пусть в следующем отношении первичный ключ образует пара атрибутов <Сотрудник, Должность>:

Сотрудник Должность Зарплата Наличие компьютера
Гришин Кладовщик 20000 Нет
Васильев Программист 40000 Есть
Иванов Кладовщик 25000 Нет

Зарплату сотруднику каждый начальник устанавливает сам (хотя её границы зависят от должности). Наличие же компьютера у сотрудника зависит только от должности, то есть зависимость от первичного ключа неполная.

В результате приведения к 2NF получаются два отношения:

Сотрудник Должность Зарплата
Гришин Кладовщик 20000
Васильев Программист 40000
Иванов Кладовщик 25000
Должность Наличие компьютера
Кладовщик Нет
Программист Есть

3-ая Нормальная форма

Переменная отношения находится в 3NF тогда и только тогда, когда она находится во второй нормальной форме и отсутствуют транзитивные зависимости неключевых атрибутов от ключевых.

Нормализация и денормализация

Что такое нормализация и денормализация:

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

Денормализация (англ. denormalization) — намеренное приведение структуры базы данных в состояние, не соответствующее критериям нормализации, обычно проводимое с целью ускорения операций чтения из базы за счет добавления избыточных данных

О нормализации и денормализации данных

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

И все вроде бы хорошо: приводи базу как минимум к третьей нормальной форме и будет всем счастье. ) Однако всегда есть НО.

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

Однако в последнее время положение дел стремительно меняется. Предметные области усложняются, количество сущностей, описывающих предметную область, увеличивается, а с применением нормализации, их число возрастает как минимум в 2-3 раза. Объемы хранимой информации постоянно растут, таблицы с десятками и сотнями тысяч записей никого не удивляют. В современных СУБД все больше появляются и активно используются специализированные типы данных (например, универсальные уникальные идентификаторы UUID, xml, json, геометрические типы), которые могут занимать большие объемы памяти. Сами таблицы уже не хранятся в одной папке одного диска, а могут быть распределены по нескольким дискам, находящимся на разных серверах. В результате, выборка и анализ данных из таких таблиц становится уже дорогостоящим занятием. Объединение распределенных таблиц с большим количеством записей и объемными полями может занимать большое количество времени и ресурсов.

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

Важным моментом денормализации является то, что она используется НЕ вместо нормализации, а ПОСЛЕ ее. Здесь нужно найти золотую середину: до каких пор мы можем разбивать данные, чтобы облегчить выполнение команд insert, update и delete, и когда можно допустить некоторую избыточность для ускорения выполнения команды select.

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

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *