Пользователь
0,0
рейтинг
15 июля 2009 в 14:07

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

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


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

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

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


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

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

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

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


Расчетные значения.
Зачастую медленно выполняются и потребляют много ресурсов запросы, в которых производятся какие-то сложные вычисления, особенно при использовании группировок и агрегатных функций (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'ы, и усложнением задачи тех, кто обеспечивает наполнение базы и обновление данных. Поэтому проводить денормализацию базы надо очень аккуратно, очень выборочно, только там, где без этого никак не обойтись.
Если заранее нельзя подсчитать плюсы и минусы денормализации, то изначально необходимо реализовать модель с нормализованными таблицами, и лишь затем, для оптимизации проблемных запросов проводить денормализацию.
Юлия Темушева @JuliaTem
карма
103,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

Самое читаемое Разработка

Комментарии (22)

  • +5
    При проектировании структуры БД я руководствуюсь принципом: «Если нет положительного опыта конкретной денормализации, то используй третью нормальную форму (как минимум)». В последующем легче произвести денормализацию чем наоборот.
    • 0
      А если есть ;)?
  • +1
    Опыт показывает, что нормализация, точнее — использование нормальных форм в чистом виде, даже если иметь ввиду третью нормальную форму (а тем паче уж более высшие формы..), является типичным жестоким сферическим конем в вакууме… Как и много чего из того, чему в универе учат эти замечательные преподаватели. И когда эти кони пытаются пойти по невспаханному полю реальной действительности, то не то что галопом скакать как ветер, обычным шагом идти им весьма и весьма сложно…

    P.S.: не знаю как у кого, но из тех преподавателей, что работали на нашей кафедре, практиков было 1-2 штуки… Остальные теоретики такие, начитанные, просто жуть…
    • +1
      вам сюда
    • +2
      Скажу тебе как преподаватель :) который одно время читал «Теорию БД». К тому же я ещё и практик.

      Так вот, тебе просто не везло с преподами :) не смогли объяснить нужность подобных вещей.
      Нормализация — это правильная и нужная вещь.
      Я встречал пару задач на практике, требовавшие вдумчивой нормализации.Нормальные формы я в них не считал, не задавался целью, но до 4-й дошел стопроцентно.
      Так что сферических коней тут нет. Люди, придумавшие НФ, сделали это не из любви к искусству, они ведь тоже решали какие-то задачи таким способом. Тебе не приходило в голову работы Д. Кнута называть подобныи «конями»? Думаю, нет — потому что описанные алгоритмы используются на практике. Так и здесь.

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

      Так что автору — респект и +1 интерес.
      • +1
        Ну я и не говорил, что любая нормализация — зло, я написал, что использование этих самых НФ в чистом виде, скорее всего, представляет скорее академический интерес, ибо на практике (у меня лично) я хоть и стремился к какой-то из форм прийти, но встречались места, где нужно было чуточку денормализовать, где-то сильнее, а где-то и очень нормализовать, в зависимости от задачи. Не было у меня такого, чтобы вся структура БД была полностью и стопроцентно приведена к какой-то из нормальных форм, ну не было. Потому что где-то требуется скорость работы с данными по добавлению/удалению, где-то важны быстрые выборки… В общем, реальная действительность гораздо многообразнее какой-либо из НФ ))

        А нормализация ради нормализации — да, зло.
    • 0
      > Опыт показывает, что нормализация, точнее — использование нормальных форм в чистом виде, даже если иметь ввиду третью нормальную форму (а тем паче уж более высшие формы..), является типичным жестоким сферическим конем в вакууме…

      Чей опыт? Ваш личный? Вы делаете необоснованное обобщение, основываясь на своем (зачастую весьма ограниченном) опыте.

      Соль реляционных БД как раз в том, что можно применять математический аппарат (логику первого порядка, теорию множеств) к решению задачи. Во многих случаях это упрощает жизнь.
  • 0
    Я уже писал, но повторюсь.

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

    • +1
      Вот это кеширование как раз и является денормализацией. А про «сливание справочных таблиц» в статье просто не хватает толкового примера.
      • 0
        Вот это кеширование как раз и является денормализацией.

        Может подскажете, какая из форм указывает на то, что это денормализация?

        А про «сливание справочных таблиц» в статье просто не хватает толкового примера.

        Сомнительно, что это дает серьезную потерю в производительности.
        • +1
          Может подскажете, какая из форм указывает на то, что это денормализация?
          Четвертый абзац, вообще это близко к третьей нормальной форме, хотя переубедить мне Вас сложно.
          • 0
            Формальное определение третьей нормальной формы:
            www.citforum.ru/database/osbd/glava_23.shtml#_2_3_1_2

            Как видите про расчетные стобцы тут ничего нет :) Хотя конечно если у вас в той же таблице где не расчетные данные присутствуют, это не есть здраво. Кеширование стоит выносить в отдельную сущность. При этом у вас не будет дубликатов и транзитивности.
    • 0
      к примеру создание таблиц заказа

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

          я собственно то же, что и Вы, хотел сказать — сохранение заказа это создание новой сущности, а не денормализация. т.е. выбран очень плохой пример.
      • +1
        Действительно, пример можно подобрать и более наглядный. Смысл такой: иногда, добавляя в таблицы атрибуты, напрямую или косвенно зависящие не от праймари кей, а от набора строк или значений неключевых столбцов в этой же строке, мы пораждаем дублирование информации, что можно счесть нарушение 3РФ. При этом необходимо дополнительно следить за целостностью данных, но зато можно выиграть на скорости выборки данных.

        P.S. нормализация, нормальные формы — это cool но… если нужно нарушить 3НФ, то важно обосновать, как минимум себе, почему это сделано, какой будет выигрыш, и подстраховаться от возможных негативных последствий.
  • 0
    Интересно узнать результаты исследований для различных БД на счет примера про хранение blob'ов. Т.к. разного типа БД и разных производителей по-разному ведут себя в подобных запросах. ИМХО это сильно зависит от способа хранения blob'ов.
  • 0
    Крупные системы принято разбивать на OLTP и OLAP части. Для ввода данных используется строго нормализованная форма. А вот для отчетности данные аггрегируются в нужных разрезах, либо в большие плоские таблицы — реляционный OLAP, либо вообще в новую структуру — многомерные кубы. Все это описано в википедии. Для небольших систем конечно можно пытаться разместить все в одной базе, но придется идти на компромиссы между скоростью выборок, записи и надежностью системы.
  • 0
    Вообще денормализацией страдают и большие компании. Для них скорость превыше всего. Денормализация — совсем не зло, а бы сказал необходимость. К тому же визуально логическая, правда немного избыточна, но если с умомо подойти, то избыток сразу превращается в плюс.
    И смотря что вводить в избыток, если добавлять к «собранной» таблице большой blob или текст — то это зло, а если поле byte или int — то этого субд даже не заметит
  • 0
    Нормализация это добро
    • 0
      Денормализация тоже добро
  • 0
    >Каждый раз, когда приходилось идти на сделку с совестью, нарушая принципы нормальных форм, оставалось ощущение неудовлетворённости, ложное осознание своей некомпетентности.

    Как точно описаны мои мучения.

    Хотел бы предложить ряд вопросов, на которые полезно для себя ответить перед проведением денормализации:
    Точно ли высокая производительность действительно необходима в ближайшее будущее?
    Много ли еще изменений будет вноситься до релиза, существенная ли часть требований удовлетворена?
    Уверенные ли вы в выборе платформы, обслуживающей логику, настолько, что готовы доверить ей ответственность за операции над связанными данными?
    Располагаете ли вы достаточными инструментами (тестами и диагностикой), чтобы вовремя обнаружить дефекты, внесенные вместе с денормализацией?
    Нет ли других способов путем изменения архитектуры, кэширования и т.д. решить задачи, которые преследует денормализация?
    Решают ли планируемые изменения проблему или есть намек на внесение изоляции между модулями приложения, например вынести данные о статике веб-сервиса в отдельный KV-store.

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.