Денормализация — это зло, или просто надо уметь её готовить?

9 комментариев
Денормализация — это зло, или просто надо уметь её готовить?
Денормализация- это не результат кривых рук. Это не недоделанная нормализация, это намеренное нарушение нормальных форм, для увеличения производительности. Вопрос о денормализации у меня возникал не раз. Каждый раз, когда приходилось идти на сделку с совестью, нарушая принципы нормальных форм, оставалось ощущение неудовлетворённости, ложное осознание своей некомпетентности. Со временем, при работе в команде, обнаружилось, что это не только моя проблема. Настало время разобраться: денормализация - это зло, или просто надо уметь её готовить?

Что хотелось понять

  • Когда нужна денормализация? Признаки и запахи.
  • Как определить, когда денормализация оправдана?
  • Как грамотно реализовать денормализацию

Когда нужна денормализация? Признаки и запахи.

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

Большое количество соединений таблиц.

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

Расчетные значения

Зачастую медленно выполняются и потребляют много ресурсов запросы, в которых производятся какие-то сложные вычисления, особенно при использовании группировок и агрегатных функций (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 триггеров и хранимых процедур нет вообще. Поэтому заботиться об обеспечении непротиворечивости данных в денормализованной базе должны разработчики приложений. По аналогии с триггерами, должна быть одна функция, обновляющая все поля, зависящие от изменяемого поля.

Резюме

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

Хотите сообщить важную новость?

Пишите в наш Телеграм

Читайте также

Самые популярные фреймворки и языки программирования среди стартапов-единорогов
Самые популярные фреймворки и языки программирования среди стартапов-единорогов

Самые популярные фреймворки и языки программирования среди стартапов-единорогов

2 комментария
Huawei запустила ИИ-базу данных для корпоративных клиентов
Huawei запустила ИИ-базу данных для корпоративных клиентов

Huawei запустила ИИ-базу данных для корпоративных клиентов

Разработчик открытых баз данных Redis ограничил использование части своих продуктов
Разработчик открытых баз данных Redis ограничил использование части своих продуктов

Разработчик открытых баз данных Redis ограничил использование части своих продуктов

2 комментария
СМИ: доступ к базе данных миллиарда индийцев стоит дешевле $10
СМИ: доступ к базе данных миллиарда индийцев стоит дешевле $10

СМИ: доступ к базе данных миллиарда индийцев стоит дешевле $10

1 комментарий

Обсуждение

Anonymous
Anonymous Программист в Adwize
0

Ну спасибо, будем собирать check list.

1

"А сейчас представим, сколько времени будет работать запрос, подсчитывающий количество фотографий, сделанных каким-либо автором..."

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

Anonymous
Anonymous Acting PM, Expert Software Engineer в Epol Soft
1

“С точки зрения нормализации абсолютно правильной будет такая структура таблицы..”
Разве в соответствии с пятой нормальной формой структура будет не такая:
Таблица№1 { ID фотографии, ID автора, ID модели фотоаппарата }
Таблица№2 { ID фотографии, сама фотография (blob-поле)}
Таблица№3 { ID автора,информация автора}
Таблица№4 {ID модели фотоаппарат, информация модели}
И тогда как вы и сказали “выборки из основной таблицы (в которой огромного blob-поля сейчас уже нет) будут идти моментально”. Все это вписывается в понятие нормализации.

Anonymous
Anonymous работаю в Digiteum
0

Все верно, создание связей 1-к-1 (как в примере у автора) никак не противоречит идеям нормализации.
Вот только в вашем примере описана 3-я нормальная форма.
Правда я ниразу не встречал реального примера, где требовалась бы 5 нормальная форма. 4 бывало, а вот 5 никогда.

2

Вас не затруднит привести пример из Вашей практики касательно использования 4й нормальной формы? Спасибо.

Anonymous
Anonymous Software Engineering Manager в EPAM
0

Нормализация и "правильность" - это разные несколько разные понятия. Для нормализации ближе понятие "упорядоченность". Это как для шкалы кислотности рН сказать что "правильное значение" - это, например pH1 (кислота).
ЗЫ. Пример насчет порядок =(?) добро специиально не привожу, чтобы любители толкиена/ фентези не заминусовали

Anonymous
Anonymous
1

Хорошая статья, спасибо.
Ключевая весч - проектировать надо изначально нормализованную БД, так как легче денормализовать, чем нормализовать :-)

Anonymous
Anonymous работаю в Digiteum
1

Очень хорошая статья, спасибо.

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

Спасибо автору еще раз, +1.

Anonymous
Anonymous Team Lead / Project Manager в Qulix Systems
0

>>Если у нас в базе данных есть большие таблицы, содержащие длинные поля (Blob, Long и т.п.)
Такие фразы сразу дают понять что человек не понимает что сравнить блоб и лонг поля ни в коем случае нельзя, хотя бы по способу их хранения.

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

>>Сохранить детальные таблицы
Иногда стоит их удалять. Я думаю вас не будут интересовать полная информация о всех залогинившихся пользователях за январь 2005 года. Думаю остаток статистической информации будет достаточно. А похудение БД на пару гигов имеет кучу плюсов
>>Использование триггеров
очень плохое решение. Триггеры сами по себе очень медленные и на больших объемах данных просто убьют вашу БД
В итоге вы на них потеряете весь тот прирост который вы хотели получить от денормализации
>>Программная поддержка
Version 4.1: beta from June 2004, production release October 2004
А 2000 MSSQL сервер не мог резолвить circular reference for foreign key. Но поймите, 2000 серверу уже 9 лет, заказчик не будет платить деньги за саппорт устаревших технологий.

И вообще многие советы очень зависят от СУБД и даже от ее версии.

Спасибо! 

Получать рассылки dev.by про белорусское ИТ

Что-то пошло не так. Попробуйте позже