В нашем блоге на Хабре мы не только рассказываем о развитии своего продукта — биллинга для операторов связи «Гидра», но и публикуем материалы о работе с инфраструктурой и использовании технологий.
Недавно мы писали об использовании Clojure и MongoDB, а сегодня речь пойдет о плюсах и минусах денормализации баз данных. Разработчик баз данных и финансовый аналитик Эмил Дркушич (Emil Drkusic) написал в блоге компании Vertabelo материал о том, зачем, как и когда использовать этот подход. Мы представляем вашему вниманию главные тезисы этой заметки.
Что такое денормализация?
Обычно под этим термином понимают стратегию, применимую к уже нормализованной базе данных с целью повышения ее производительности. Смысл этого действия — поместить избыточные данные туда, где они смогут принести максимальную пользу. Для этого можно использовать дополнительные поля в уже существующих таблицах, добавлять новые таблицы или даже создавать новые экземпляры существующих таблиц. Логика в том, чтобы снизить время исполнения определенных запросов через упрощение доступа к данным или через создание таблиц с результатами отчетов, построенных на основании исходных данных.
Непременное условие процесса денормализации — наличие нормализованной базы. Важно понимать различие между ситуацией, когда база данных вообще не была нормализована, и нормализованной базой, прошедшей затем денормализацию. Во втором случае — все хорошо, а вот первый говорит об ошибках в проектировании или недостатке знаний у специалистов, которые этим занимались.
Рассмотрим нормализованную модель для простейшей CRM-системы:
Пробежимся по имеющимся здесь таблицам:
- Таблица
user_accountхранит данные о пользователях, зарегистрированных в приложении (для упрощения модели роли и права пользователей из нее исключены). - Таблица
clientсодержит некие базовые сведения о клиентах. - Таблица
product— это список предлагаемых товаров. - Таблица
taskсодержит все созданные задачи. Каждую из них можно представить в виде набора согласованных действий по отношению к клиенту. Для каждой есть список звонков, встреч, предложенных и проданных товаров. - Таблицы
callиmeetingхранят данные о заказах и встречах с клиентами и связывают их с текущими задачами. - Словари
task_outcome,meeting_outcomeиcall_outcomeсодержат все возможные варианты результата звонков, встреч и задания. product_offeredхранит список продуктов, которые были предложены клиентам;product_sold— продукты, которые удалось продать.- Таблица
supply_orderхранит информацию обо всех размещенных заказах. - Таблица
writeoffсодержит перечень списанных по каким-либо причинам товаров.
В этом примере база данных сильно упрощена для наглядности. Но нетрудно увидеть, что она отлично нормализована — в ней нет никакой избыточности, и все должно работать, как часы. Никаких проблем с производительностью не возникает до того момента, пока база не столкнется с большим объёмом данных.
Когда полезно использовать денормализацию
Прежде чем браться разнормализовывать то, что уже однажды было нормализовано, естественно, нужно четко понимать, зачем это нужно? Следует убедиться, что выгода от применения метода перевешивает возможные негативные последствия. Вот несколько ситуаций, в которых определенно стоит задуматься о денормализации.
- Сохранение исторических данных. Данные меняются с течением времени, но может быть нужно сохранять значения, которые были введены в момент создания записи. Например, могут измениться имя и фамилия клиента или другие данные о его месте жительства и роде занятий. Задача должна содержать значения полей, которые были актуальны на момент создания задачи. Если этого не обеспечить, то восстановить прошлые данные корректно не удастся. Решить проблему можно, добавив таблицу с историей изменений. В таком случае SELECT-запрос, который будет возвращать задачу и актуальное имя клиента будет более сложным. Возможно, дополнительная таблица — не лучший выход из положения.
- Повышение производительности запросов. Некоторые запросы могут использовать множество таблиц для доступа к часто запрашиваемым данным. Пример — ситуация, когда необходимо объединить до 10 таблиц для получения имени клиента и наименования товаров, которые были ему проданы. Некоторые из них, в свою очередь, могут содержать большие объемы данных. При таком раскладе разумным будет добавить напрямую поле
client_idв таблицуproducts_sold. - Ускорение создания отчетов. Бизнесу часто требуется выгружать определенную статистику. Создание отчетов по «живым» данным может требовать большого количества времени, да и производительность всей системы может в таком случае упасть. Например, требуется отслеживать клиентские продажи за определенный промежуток по заданной группе или по всем пользователям разом. Решающий эту задачу запрос в «боевой» базе перелопатит ее полностью, прежде чем подобный отчет будет сформирован. Нетрудно представить, насколько медленнее все будет работать, если такие отчеты будут нужны ежедневно.
- Предварительные вычисления часто запрашиваемых значений. Всегда есть потребность держать наиболее часто запрашиваемые значения наготове для регулярных расчетов, а не создавать их заново, генерируя их каждый раз в реальном времени.
Вывод напрашивается сам собой: не следует обращаться к денормализации, если не стоит задач, связанных с производительностью приложения. Но если чувствуется, что система замедлилась или скоро замедлится, впору задуматься о применении данной техники. Однако, прежде чем обращаться к ней, стоит применить и другие возможности улучшения производительности: оптимизацию запросов и правильную индексацию.
Не все так гладко
Очевидная цель денормализации — повышение производительности. Но всему есть своя цена. В данном случае она складывается из следующих пунктов:
- Место на диске. Ожидаемо, поскольку данные дублируются.
- Аномалии данных. Необходимо понимать, что с определенного момента данные могут быть изменены в нескольких местах одновременно. Соответственно, нужно корректно менять и их копии. Это же относится к отчетам и предварительно вычисляемым значениям. Решить проблему можно с помощью триггеров, транзакций и хранимых процедур для совмещения операций.
- Документация. Каждое применение денормализации следует подробно документировать. Если в будущем структура базы поменяется, то в ходе этого процесса нужно будет учесть все прошлые изменения — возможно, от них вообще можно будет к тому моменту отказаться за ненадобностью. (Пример: в клиентскую таблицу добавлен новый атрибут, что приводит к необходимости сохранения прошлых значений. Чтобы решить эту задачу, придется поменять настройки денормализации).
- Замедление других операций. Вполне возможно, что применение денормализации замедлит процессы вставки, модификации и удаления данных. Если подобные действия проводятся относительно редко, то это может быть оправдано. В этом случае мы разбиваем один медленный SELECT-запрос на серию более мелких запросов по вводу, обновлению и удалению данных. Если сложный запрос может серьезно замедлить всю систему, то замедление множества небольших операций не отразится на качестве работы приложения столь драматических образом.
- Больше кода. Пункты 2 и 3 потребуют добавления кода. В то же время они могут существенно упростить некоторые запросы. Если денормализации подвергается существующая база данных, то потребуется модифицировать эти запросы, чтобы оптимизировать работу всей системы. Также понадобится обновить существующие записи, заполнив значения добавленных атрибутов — это тоже потребует написания некоторого количества кода.
Денормализация на примере
В представленной модели были применены некоторые из вышеупомянутых правил денормализации. Синим отмечены новые блоки, розовым — те, что были изменены.
Что изменилось и почему?
Единственное нововведение в таблице
product — строка units_in_stock. В нормализованной модели мы можем вычислить это значение следующим образом: заказанное наименование — проданное — (предложенное) — списанное (units ordered — units sold — (units offered) — units written off). Вычисление повторяется каждый раз, когда клиент запрашивает товар. Это довольно затратный по времени процесс. Вместо этого можно вычислять значение заранее так, чтобы к моменту поступления запроса от покупателя, все уже было наготове. С другой стороны, атрибут units_in_stock должен оновляться после каждой операции ввода, обновления или удаления в таблицах products_on_order, writeoff, product_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 (статистика за год) в тестовой модели — абсолютно новый элемент. По сути, это денормализованная таблица, поскольку все ее данные могут быть рассчитаны из других таблиц. Здесь хранится информация о текущих задачах, успешно выполненных задачах, встречах, звонках по каждому заданному клиенту. В данном месте также хранится общая сумма проведенных начислений за каждый год. После ввода, обновления или удаления любых данных в таблицах task, meeting, call и product_sold приходится пересчитывать эти данные для каждого клиента и соответствующего года. Так как изменения, скорее всего, касаются лишь текущего года, отчеты за предыдущие годы теперь могут оставаться без изменений. Значения в этой таблице вычисляются заранее, поэтому мы сэкономим время и ресурсы, когда нам понадобятся результаты расчетов.Денормализация — мощный подход. Не то чтобы к ней следует прибегать каждый раз, когда стоит задача увеличения производительности. Но в отдельных случаях это может быть лучшим или даже единственным решением.
Однако, прежде, чем принять окончательное решение об использовании денормализации, следует убедиться в том, что это действительно необходимо. Нужно провести анализ текущей производительности системы — часто денормализацию применяют уже после запуска системы в работу. Не стоит этого бояться, однако следует внимательно отслеживать и документировать все вносимые изменения, тогда проблем и аномалий данных не должно возникнуть.
Наш опыт
Мы в Латере много занимаемся оптимизацией производительности нашей биллинговой системы «Гидра», что неудивительно, учитывая объемы наших клиентов и специфику телеком-отрасли.
Один из примеров в статье предполагает создание таблицы с промежуточными итогами для ускорения отчетов. Конечно, самое сложное в этом подходе — поддерживать актуальное состояние такой таблицы. Иногда можно переложить эту задачу на СУБД — например, использовать материализованные представления. Но, когда бизнес-логика для получения промежуточных результатов оказывается чуть более сложной, актуальность денормализованных данных приходится обеспечивать вручную.
«Гидра» имеет глубоко проработанную систему привилегий для пользователей, операторов биллинга. Права выдаются несколькими способами — можно разрешить определенные действия конкретному пользователю, можно заранее подготовить роли и выдать им разные наборы прав, можно наделить определенный отдел специальными привилегиями. Только представьте, насколько медленными стали бы обращения к любым сущностям системы, если бы каждый раз нужно было пройти всю эту цепочку, чтобы убедиться: «да, этому сотруднику разрешено заключать договоры с юридическими лицами» или «нет, у этого оператора недостаточно привилегий для работы с абонентами соседнего филиала». Вместо этого мы отдельно храним готовый агрегированный список действующих прав для пользователей и обновляем его, когда в систему вносятся изменения, способные на этот список повлиять. Сотрудники переходят из одного отдела в другой намного реже, чем открывают очередного абонента в интерфейсе биллинга, а значит вычислять полный набор их прав нам приходится настолько же реже.
Конечно, денормализация хранилища — это только одна из принимаемых мер. Часть данных стоит кэшировать и непосредственно в приложении, но если промежуточные результаты в среднем живут намного дольше, чем пользовательские сессии, есть смысл всерьез задуматься о денормализации для ускорения чтения.
Другие технические статьи в нашем блоге:
Комментарии (27)
gandjustas
09.04.2016 20:12Ручная денормализация — почти всегда крайне плохая идея.
В РСУБД есть механизмы материализованных представлений, вычисляемых столбцов и индексов, по ним и другие способы выполнить расчеты в момент записи, а не в момент запроса.
Что касается прав доступа, то обычно права ("утверждения", claims) пользователя кешируется в приложении и проверяются простыми предикатами. Денормализовывать их — плохая идея, особенно есть рекурсия групп.
excoder
09.04.2016 22:21+2Согласен, materialised view в теории должен решать означенные в статье проблемы. Если это read-only views — всё тип-топ на самом деле. Если же надо писать туда, на практике выходит, что реализации updatable views имеют много тонкостей, с которыми надо жить.
gandjustas
09.04.2016 22:30А зачем апдейтить материализованные представления? Они сами обновляются (или командой в Postgres) при обновлении данных.
Dreyk
09.04.2016 22:35имеется в виду, что insert/update идут в тот же вид, из которого было чтение, а не в нормализованные таблицы.
gandjustas
09.04.2016 22:38+1А зачем такое?
excoder
10.04.2016 13:20Если не надо инкапсулировать нормализованное представление и все части приложения знают всё о базе данных (обернули в один большой Repository и подобные паттерны) — то вроде и не требуется.
gandjustas
10.04.2016 15:07А зачем может понадобится инкапсулировать материализованное представление?
excoder
11.04.2016 13:50Например, несколько систем, разработанных различными группами, будучи установленными, используют одну базу. Внутри этой базы сидит нормализованное представление, разработанное главным БД архитектором. Однако же системы обращаются с данными исключительно через VIEWs. Тем самым достигается decoupling между приложениями и архитектурой базы. Можно не менять приложения и VIEWs, но постоянно работать над внутренним нормализованным представлением и мигрировать его через версии схемы. Separation of concerns на уровне БД.
gandjustas
11.04.2016 14:17Еще раз — зачем писать в ДЕНОРМАЛИЗОВАННОЕ представление?
Зачем писать в нормализованное и так понятно.excoder
11.04.2016 15:01В случае если денормализованное представление — это единственное представление, в которое можно писать. Ну не знают модули о нормализованном представлении, оно инкапсулировано как раз-таки через VIEWs. Оно не обязательно, на самом деле, просто денормализованное — оно просто другое, «фасет» если хотите.
getElementById
10.04.2016 00:03+2В постгресе сейчас нет инкрементального обновления мат. представлений, недавно только завезли конкурентное.
В статье прямо говорится, что инструмент требует взвешенного подхода. Например, у нас денормализация работает вопреки вашим предостережениям.Ivan22
11.04.2016 12:53у материализованных представлений слишком много проблем с перформансом. Как только объемы превышают определенныйц порог и не прокатывает полная перестройка матвью — сразу же на практике от матвью преходится отказываться.
getElementById
11.04.2016 15:36О том и речь. В оракле есть инкрементальный рефреш по коммиту, но то в оракле. И там есть серьезные ограничения на возможность именно инкрементального обновления.
PavelBelyaev
10.04.2016 01:00Еще в интернет-магазине, где по сути товар создается один раз, но для вывода страниц требуется получать главные картинки товаров в категории, проще имена главных картинок еще одним полем хранить, чем джоином еще одну таблицу цеплять ради такого, а еще в заказах хранить нужно цену на момент покупки и товары (как минимум названия), даже если товар удаляется — древние заказы можно поднять и понять что там было заказано.
foxmuldercp
10.04.2016 21:52Тут можно очень много данных выбросить в кеши, вроде Redis, и бегать в основную базу уже по минимальному количеству поводов, вроде резервирования товара, например
babylon
10.04.2016 03:37Отношения (структуру) и контент лучше разделять. И собирать на время запроса. Оставлять ли ответ в кэше, зависит от частоты (статистики)запросов. Её тоже планируют (настраивают)и прогнозируют для конкретной задачи. Эвристики тоже используют, но они не всегда работают. При записи такая же фигня. Любая оптимизация требует жертв. Память против скорости или наоборот.
ComodoHacker
10.04.2016 14:01В оригинальной статье принципы денормализации изложены неплохо. Но вот пример с остатками товара совершенно оторван от жизни и неуместен. В реальных системах никто не станет хранить остатки в таблице Product. (Ну разве что студенты, писавшие на коленке за еду).
Мы в Латере много занимаемся оптимизацией производительности нашей биллинговой системы
Вот и написали бы статью на своих примерах, всесто того, чтобы переписывать с чужого блога.
P.S. Самое интересное, о чем я узнал из статьи — об онлайн редакторе моделей от Vertabelo. :)getElementById
10.04.2016 19:51Боюсь, мы не сможем тут вас удивить. В статье действительно хорошо изложена суть подхода, поэтому мы ее и выбрали. Ее удобно, например, кидать новичкам. В остальном, как мне кажется, тонкостям можно научиться только на своем опыте.
Mixim333
10.04.2016 18:04+1Позволю не согласиться, что денормализация — это допустимо\хорошо. Работал с двумя биллинговыми системами, одна из которых BgBilling (https://bgbilling.ru/). Да, денормализация позволяет увеличить скорость работы клиентских приложений, но когда по какой-то причине в базе нужно что-то поправить ручками — это превращается в какой-то ад, когда для изменения атомарной колонки «A» тебе нужно сперва обновить «Table1», затем «Table2», которая хранит копию колонки «A».
Самим на работе в Oracle 11g год назад была создана таблица, в которой на текущий момент более 30млн строк и каждый день туда добавляется около 100тыс строк. Работа с этой таблицей исполняется очень быстро, есть функциональный индекс по полю: trunc(MyTable.Date_Of_Event) и время запроса к этой таблице не превышает 1 минуты (группировки, простые выборки, выборки с парой тройкой JOIN'ов). На эту таблицу завязаны еще 5 других, в каждой из которых около 50тыс строк, связь сделана по Id и все мои запросы с JOIN'ами c другими таблицами этой БД и с таблицами, находящимися в БД, физически расположенными на другом конце страны, отрабатывают минуты за 2 (проводил анализ объемов данных — десятки-сотни ГБ).
Теперь представим, что я создал кучу материализованных представлений, все хранилось бы на одном сервере, обновлялось бы раз в сутки, формально отрабатывало бы не за 2 минуты, а секунд за 5-10, но данные в dblink'е меняются очень часто=>достоверность MatView была бы очень спорна.
Еще как-то мне доказывали, что old-style join (связь между таблицами осуществляется на основе условия внутри блока WHERE) работает на порядок быстрее, требует меньше системных ресурсов — да, согласен, системных ресурсов требует меньше, но насчет скорости утверждение еще ни разу на практике не подтвердилось!getElementById
10.04.2016 19:45+1Поэтому у нас API предоставляется в виде хранимых процедур. А тому, кто ручками что-то апдейт в базе, мы ручки укорачиваем.
Ivan22
11.04.2016 12:58про олд-стайл джоин на порядок быстрейший — это какая байка. Т.к. либо у них одинаковый план — и они тогда работают одинаково, либо у них разных план- и значит это разные запросы вообще. (Ну или просто оптимизатор туповат — и тогда это не фича, а баг, и тогда уже хинтами можно получить одинаковый план)
misato
11.04.2016 13:53Хорошо бы иметь специальное соглашение по именованию дополнительных денормализованных полей или таблиц. В примерах в статье такие поля ничем не отличимы от остальных, и это впоследствии может привести к ошибке, потому что отношение к нормальным данным и избыточным должно быть разное, как раз чтобы поддерживать целостность. Можно добавлять префикс какой-нибудь, или суффикс.
Что касается большинства веб-проектов, то денормализация там отлично применяется и используется по назначению. Собственно, расцвет nosql-хранилищ, это не что иное как осознанный отказ от преимуществ нормализованных строгих данных в сторону быстрых, но ненормализованных.
Не раз приходилось сталкиваться с сайтиками, в которых «правильная» структура данных создавала массу неудобств и трудностей, при отсутствии видимых преимуществ (мало по-настоящему сложных зависимостей, статичность данных и проч.) Я бы даже сказал, что стремление нормализовать данные — это вторая наиболее распространённая причина оверинжиниринга веб-проектов.
excoder
Кстати, с in-memory database вопрос денормализации обычно не стоит. Исполнение JOIN суть разыменование указателей в памяти, эта операция тоже чего-то стоит, но безобразно дёшева в сравнении с disk-based JOIN-ами. Я конечно понимаю, big data и все дела, данные не влезают в память. Но во многих бизнес-приложениях «большая база» — это 50-80 Гб. Это уже всё влезает спокойно. 128 Gb сервер совершенно нормально. Ну и опять же, окей, положите своп на быстрый SSD и дайте ей туда немного залезть, тоже ничего страшного. В таком случае, вместо того чтобы кушать кактус и денормализовывать / избыточно материализовать таблицы, проще перейти на true in-memory и получить возможность быстренько все эти данные запрашивать на чтение вдоль и поперёк, и одновременно работая с ними на запись, всё в одном флаконе.
ZOXEXIVO
Так почти все базы пытаются держать все в памяти, если возможно
excoder
Это верно, но это не одно и то же. Алгоритмика очень разная. Можно запустить тест, скажем, на агрегацию и простеньким джойном, с классическим MS Sql Server и рядом с чем-нибудь типа MemSQL или VoltDB, и посмотреть. Памяти там много не потребуется, даже на 100 Мб данных разница будет разы или десятки раз. При разрастании числа джойнов это дело уйдёт в сотни раз разницы. Дело не в объёме памяти, а в том, как база работает с хранилищем. После можно попробовать другое упражнение, написать это в хранимке на Ms Sql Server 2014-2016 Hekaton (in-memory table). Hekaton закомпилит это дело и исполнит как нативную DLL внутри процесса in-memory таблицы. В сравнении с обычной Ms Sql Server таблицей разница будет очень заметна. Если бы всё было так просто с памятью и классической БД, то и никакого Hekaton Microsoft не стал бы добавлять в продукт.