Попробовать
демоверсию
Бесплатная демоверсия позволяет ознакомиться со всеми возможностями нашего биллинга
+7 499 940-95-05

Зачем нужна денормализация баз данных, и когда ее использовать

Недавно мы писали об использовании Clojure и MongoDB, а сегодня речь пойдет о плюсах и минусах денормализации баз данных. Разработчик баз данных и финансовый аналитик Эмил Дркушич (Emil Drkušić) написал в блоге компании Vertabelo материал о том, зачем, как и когда использовать этот подход. Мы представляем вашему вниманию главные тезисы этой заметки.

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

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

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

Рассмотрим нормализованную модель для простейшей CRM-системы:

 

Пробежимся по имеющимся здесь таблицам:

  • Таблица user_account хранит данные о пользователях, зарегистрированных в приложении (для упрощения модели роли и права пользователей из нее исключены).
  • Таблица client содержит некие базовые сведения о клиентах.
  • Таблица product — это список предлагаемых товаров.
  • Таблица task содержит все созданные задачи. Каждую из них можно представить в виде набора согласованных действий по отношению к клиенту. Для каждой есть список звонков, встреч, предложенных и проданных товаров.
  • Таблицы call и meeting хранят данные о заказах и встречах с клиентами и связывают их с текущими задачами.
  • Словари task_outcome, meeting_outcome и call_outcome содержат все возможные варианты результата звонков, встреч и задания.
  • product_offered хранит список продуктов, которые были предложены клиентам; 
  • product_sold — продукты, которые удалось продать.
  • Таблица supply_order хранит информацию обо всех размещенных заказах.
  • Таблица writeoff содержит перечень списанных по каким-либо причинам товаров.


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

Когда полезно использовать денормализацию

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

  1. Сохранение исторических данных. Данные меняются с течением времени, но может быть нужно сохранять значения, которые были введены в момент создания записи. Например, могут измениться имя и фамилия клиента или другие данные о его месте жительства и роде занятий. Задача должна содержать значения полей, которые были актуальны на момент создания задачи. Если этого не обеспечить, то восстановить прошлые данные корректно не удастся. Решить проблему можно, добавив таблицу с историей изменений. В таком случае SELECT-запрос, который будет возвращать задачу и актуальное имя клиента будет более сложным. Возможно, дополнительная таблица — не лучший выход из положения.
  2. Повышение производительности запросов. Некоторые запросы могут использовать множество таблиц для доступа к часто запрашиваемым данным. Пример — ситуация, когда необходимо объединить до 10 таблиц для получения имени клиента и наименования товаров, которые были ему проданы. Некоторые из них, в свою очередь, могут содержать большие объемы данных. При таком раскладе разумным будет добавить напрямую поле client_idв таблицу products_sold
  3. Ускорение создания отчетов. Бизнесу часто требуется выгружать определенную статистику. Создание отчетов по «живым» данным может требовать большого количества времени, да и производительность всей системы может в таком случае упасть. Например, требуется отслеживать клиентские продажи за определенный промежуток по заданной группе или по всем пользователям разом. Решающий эту задачу запрос в «боевой» базе перелопатит ее полностью, прежде чем подобный отчет будет сформирован. Нетрудно представить, насколько медленнее все будет работать, если такие отчеты будут нужны ежедневно.
  4. Предварительные вычисления часто запрашиваемых значений. Всегда есть потребность держать наиболее часто запрашиваемые значения наготове для регулярных расчетов, а не создавать их заново, генерируя их каждый раз в реальном времени.


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

Не все так гладко

Очевидная цель денормализации — повышение производительности. Но всему есть своя цена. В данном случае она складывается из следующих пунктов:

  • Место на диске. Ожидаемо, поскольку данные дублируются.
  • Аномалии данных. Необходимо понимать, что с определенного момента данные могут быть изменены в нескольких местах одновременно. Соответственно, нужно корректно менять и их копии. Это же относится к отчетам и предварительно вычисляемым значениям. Решить проблему можно с помощью триггеров, транзакций и хранимых процедур для совмещения операций.
  • Документация. Каждое применение денормализации следует подробно документировать. Если в будущем структура базы поменяется, то в ходе этого процесса нужно будет учесть все прошлые изменения — возможно, от них вообще можно будет к тому моменту отказаться за ненадобностью. (Пример: в клиентскую таблицу добавлен новый атрибут, что приводит к необходимости сохранения прошлых значений. Чтобы решить эту задачу, придется поменять настройки денормализации).
  • Замедление других операций. Вполне возможно, что применение денормализации замедлит процессы вставки, модификации и удаления данных. Если подобные действия проводятся относительно редко, то это может быть оправдано. В этом случае мы разбиваем один медленный SELECT-запрос на серию более мелких запросов по вводу, обновлению и удалению данных. Если сложный запрос может серьезно замедлить всю систему, то замедление множества небольших операций не отразится на качестве работы приложения столь драматических образом.
  • Больше кода. Пункты 2 и 3 потребуют добавления кода. В то же время они могут существенно упростить некоторые запросы. Если денормализации подвергается существующая база данных, то потребуется модифицировать эти запросы, чтобы оптимизировать работу всей системы. Также понадобится обновить существующие записи, заполнив значения добавленных атрибутов — это тоже потребует написания некоторого количества кода.

 

Денормализация на примере

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

Что изменилось и почему?
Единственное нововведение в таблице product — строка units_in_stock. В нормализованной модели мы можем вычислить это значение следующим образом: заказанное наименование — проданное — (предложенное) — списанное (units ordered — units sold — (units offered) — units written off). Вычисление повторяется каждый раз, когда клиент запрашивает товар. Это довольно затратный по времени процесс. Вместо этого можно вычислять значение заранее так, чтобы к моменту поступления запроса от покупателя, все уже было наготове. С другой стороны, атрибут units_in_stock должен оновляться после каждой операции ввода, обновления или удаления в таблицах products_on_orderwriteoffproduct_offered и product_sold.
В таблицу task добавлено два новых атрибута: client_name и user_first_last_name. Оба они хранят значения на момент создания задачи — это нужно, потому что каждое из них может поменяться с течением времени. Также нужно сохранить внешний ключ, который связывает их с исходным пользовательским и клиентским ID. Есть и другие значения, которые нужно хранить — например, адрес клиента или информация о включенных в стоимость налогах вроде НДС.
Денормализованная таблица product_offered получила два новых атрибута: price_per_unit и price. Первый из них необходим для хранения актуальной цены на момент предложения товара. Нормализованная модель будет показывать лишь ее текущее состояние. Поэтому, как только цена изменится, изменится и «ценовая история». Нововведение не просто ускорит работу базы, оно улучшает функциональность. Строка price вычисляет значение units_sold * price_per_unit. Таким образом, не нужно делать расчет каждый раз, как понадобится взглянуть на список предложенных товаров. Это небольшая цена за увеличение производительности.

Изменения в таблице product_sold сделаны по тем же соображениям. С той лишь разницей, что в данном случае речь идет о проданных наименованиях товара.
Таблица statistics_per_year (статистика за год) в тестовой модели — абсолютно новый элемент. По сути, это денормализованная таблица, поскольку все ее данные могут быть рассчитаны из других таблиц. Здесь хранится информация о текущих задачах, успешно выполненных задачах, встречах, звонках по каждому заданному клиенту. В данном месте также хранится общая сумма проведенных начислений за каждый год. После ввода, обновления или удаления любых данных в таблицах taskmeetingcall и product_sold приходится пересчитывать эти данные для каждого клиента и соответствующего года. Так как изменения, скорее всего, касаются лишь текущего года, отчеты за предыдущие годы теперь могут оставаться без изменений. Значения в этой таблице вычисляются заранее, поэтому мы сэкономим время и ресурсы, когда нам понадобятся результаты расчетов.

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

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

Наш опыт

Мы в Латере много занимаемся оптимизацией производительности нашей биллинговой системы «Гидра», что неудивительно, учитывая объемы наших клиентов и специфику телеком-отрасли.

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

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

Конечно, денормализация хранилища — это только одна из принимаемых мер. Часть данных стоит кэшировать и непосредственно в приложении, но если промежуточные результаты в среднем живут намного дольше, чем пользовательские сессии, есть смысл всерьез задуматься о денормализации для ускорения чтения.

Подписывайтесь на наш блог, чтобы не пропустить ничего интересного!

09.04.2016

Другие публикации

По определению консалтинговой компании Deloitte, текущий период в мировой экономике характеризуется неопределенностью. Поэтому бизнес во всем мире стремится к оптимизации и снижению издержек. Сегодня мы рассмотрим несколько способов, с помощью которых компании из разных стран экономят уже сейчас.
В новой версии Гидры мы по традиции продолжили работу над коммерческими возможностями биллинга. Встречайте контрактные тарифы! Другие мелкие улучшения упрощают интеграцию Гидры с внешними системами.
Компании из самых разных отраслей бизнеса сталкиваются с часто повторяющимися бизнес-процессами, связанными с обработкой заявок. В большинстве отраслей выполнение типовых работ и услуг — это сложный и разветвленный бизнес-процесс. Мы много размышляли об этих проблемах и создали инструмент, который каждая компания могла бы легко адаптировать под себя — новый продукт Гидра OMS
В одном из наших постов мы уже описывали ситуацию, в которой бесконтрольный рост таблиц в базе данных одной компании-пользователя нашей системы привел к настоящему DoS. Сегодня речь пойдет о еще одном интересном случае внезапного сбоя, который сделал «день смеха» 1 апреля этого года совсем не смешным для службы поддержки «Латеры».
В Instagram развертывание backend-кода (основная программно-аппаратная часть, с которой работают клиенты) происходит от 30 до 50 раз в день, каждый раз, когда инженеры подтверждают изменение оригинала. И, по большей части, без участия человека — сложно в это поверить, особенно учитывая масштабы соцсети, но факт остается фактом.
В этой заметке речь пойдет о масштабировании. Разработчики open-source почтового приложения Nylas опубликовали в своем блоге материал о том, как им удалось масштабировать систему в 20 раз за три недели с помощью инструмента ProxySQL. Для этого им пришлось переехать с Amazon RDS на MySQL на EC2.
Интересный материал о работе с JSON, и в частности, о применении ограничений опубликовал в своем блоге разработчик Магнус Хагандер (Magnus Hagander) — в нашем блоге мы решили представить его основные идеи.
Успешно завершили интеграцию биллинга Гидра с порталом NEXT TV.
Разработчики из американской компании Gaslight написали интересный материал о том, почему организация, известная своей любовью к Ruby и Ruby on Rails, решила инвестировать в освоение новых технологий — например, Clojure. Мы тоже работаем с этим языком программирования, поэтому решили выделить главные тезисы команды Gaslight в отдельный материал.
Разработчик и сотрудник проекта CouldBoost.io Наваз Дандала (Nawaz Dhandala) написал материал о том, почему в некоторых случаях не стоит использовать MongoDB. Мы в «Латере» уже много лет работаем с этой СУБД, поэтому решили представить и свое мнение по данному вопросу.
Немецкий журналист и хакер Ляйф Риге (Leif Ryge) написал для издания Ars Technica интересный материал о том, что современный подход к организации обновлений программного обеспечениях несет в себе серьезные риски информационной безопасности. Мы представляем вашему вниманию главные мысли этой заметки.
Инженер проекта Haleby.se написал материал, в котором рассказал о причинах выбора в качестве инструмента оркестрации Docker-контейнеров технологии Kubernetes. Мы представляем основные мысли этой заметки.
Адаптация заметки бывшего сотрудника Amazon про то, почему плохие продукты пользуются большим успехом, опубликованная в авторской колонке Дмитрия Копловича на Rusbase.
Поучительная история из жизни нашей техподдержки про то почему операторам нужно мониторить размеры таблиц в своих базах.
Инженер компании Akalak & Neo Technology Горка Садаковски (Gorka Sadakowski) написал интересный материал о том, как использование графовых баз данных может в режиме реального времени предотвращать мошенничество в сфере электронной коммерции. Мы представляем вашему вниманию основные мысли этой заметки.
Платежные протоколы уязвимы — об этом рассказали немецкие исследователи информационной безопасности на конференции Chaos Computing Club. Предлагаем вам адаптированный перевод их выступления.
Наша адаптация заметки разработчика и системного архитектора Михаэля Виттига о наиболее распространенных ошибках в использовании Amazon Web Services.
Наш адаптированный перевод заметки главного разработчика Azure Джеффа Уилкокса, о том, как более двух тысяч членов команды проекта переезжали на GitHub.
Представляем вашему вниманию адаптированный перевод одной из глав книги «Архитектура open-source-приложений», в которой описываются предпосылки появления, архитектура и организация работы популярного веб-сервера nginx.
Наш сегодняшний пост посвящен тому как мы писали софт для контроля работы удаленных сотрудников.
Сегодня мы расскажем про устройство системы подпольного банкинга Хавала, которая возникла еще в VIII веке и до сих пор пользуется большой популярности в странах Среднего Востока, Азии и Африки.
Мы уже рассказывали о том какие проблемы могут возникнуть у компании при саомостоятельной разработке сложных систем. Сегодня мы поговорим о том, как мы работали над повышением отказоустойчивости «Гидры».
Адаптированный перевод заметки главного инженера LinkedIn Джоша Клемма о процессе масштабирования инфраструктуры социальной сети.
Адаптированный перевод заметки инженера финансового стартапа Stripe о том, как его команда мигрировала огромное количество записей в базе данных.
По нашим оценкам, около половины российских операторов связи используют самописный (или переписанный до неузнаваемости простенький «покупной») софт. Сегодня мы поговорим о возможных минусах такого подхода.
Появились маркетинговые инструменты для сегментации абонентской базы, управления скидками и пакетными предложениями.
Подробнее читайте в блоге.
В новой версии мы полностью переработали механизм работы с услугами. Это сразу дало несколько серьезных улучшений, но полностью все заложенные в новой версии возможности будут раскрыты в следующих версиях.
В новой версии была полностью переработана система прав доступа. Новая система уникальна и позволяет реализовать самые смелые замыслы по разграничению доступа сотрудников к биллингу.

Начните знакомство с Гидрой прямо сейчас

Попробовать демоверсию Купить Гидру

Оформление демоверсии

Пожалуйста, укажите реальный email, на него придут данные для доступа в демо.
Все поля являются обязательными для заполнения.
.hydra-billing.com

Для вас будет развернута персональная облачная версия Гидры.
Нажимая кнопку "создать демоверсию" вы соглашаетесь с
Политикой обработки персональных данных.

Мы можем вам перезвонить

Нажимая кнопку "отправить" вы соглашаетесь с
Политикой обработки персональных данных.

Напишите нам!

Ваша компания

Услуги, которые вы предоставляете:

Нажимая кнопку "отправить" вы соглашаетесь с
Политикой обработки персональных данных.

Скачать буклет

Ваш адрес:

Хотите узнать больше?

Рассылка делается примерно раз в месяц. Мы обещаем бережно хранить ваш электронный адрес и никому его не выдавать даже под пытками.