что такое денормализация таблиц и в каких типах систем она применяется чаще

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

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

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

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

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

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

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

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

Источник

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

В нашем блоге на Хабре мы не только рассказываем о развитии своего продукта — биллинга для операторов связи «Гидра», но и публикуем материалы о работе с инфраструктурой и использовании технологий.

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

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

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

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

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

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

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

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

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

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

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

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

Что изменилось и почему?

Изменения в таблице product_sold сделаны по тем же соображениям. С той лишь разницей, что в данном случае речь идет о проданных наименованиях товара.

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

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

Наш опыт

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

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

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

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

Источник

De-normalization in Database

Introduction

We have seen how to design a database and how to have normalized tables in database. The main purpose of having clear design and normalization of tables is to reduce redundancy and to have consistent data in the database. Hence we will have multiple tables in the database and each will be mapped with one another by referential integrity. If we need any related data we would join the related tables and get the records. This will work fine and quick if the database is small and have relatively few records.

But in real world, the database is very huge and it will have lots of records. When we normalize the table, we might not realize about the record amount. We would be concentrating only on having perfect database with less redundancy. But what happens when data grows in the database? How will a smallest request which has to retrieve the data from multiple tables because of normalization perform? The cost of the query will increase drastically. This is because, it will have to join multiple tables to get the data, but these tables are not smaller. They will huge data, and any smallest query on the table will have to traverse the table till it gets the record (although it depends on file organization method).

For example, in our STUDENT database, we have divided, Address into separate table as Door#, Street, City, State, ZIP. Imagine, if we have to show the address of a student in some report, we always have to have join with this address table. Our query will fetch student’s details from STUDENT table. Once it got the records, it will fetch his address from ADDRESS table. Here the two transaction on fetching and hence disk I/O happens. Hence the cost of performance is more.

Instead, imagine what will happen if we have address in the STUDENT table itself? Second transaction above is not all required. The first fetch itself will give student detail as well as his address. So it saves the second fetch time, making the fetch faster. But what happens to redundancy and 3NF here?

As a thumb rule of database design, we should have normalized data so that there should not be any redundancy. But this normalization increases the cost of performance. Comparing the good design and performance of database, performance takes the priority. Any user accessing the database is more interested in quick and correct result than how a database is designed. Hence if we introduce redundancy in the table and if the performance of the query increases, then we can ignore 3NF.

Please click Like if you loved this article?

Denormalization is not only recombining the columns to have redundant data. Denormalization can be any technique with which performance of the normalized table can be increased.

Methods of De-normalization

Adding Redundant columns

In this method, only the redundant column which is frequently used in the joins is added to the main table. The other table is retained as it is.

For example, consider EMPLOYEE and DEPT tables. Suppose we have to generate a report where we have to show employee details and his department name. Here we need to have join EMPLOYEE with DEPT to get department name.

But joining the huge EMPLOYEE and DEPT table will affect the performance of the query. But we cannot merge DEPT with EMPLOYEE. At the same time, we need to have a separate DEPT table with many other details, apart from its ID and Name. In this case, what we can do is add the redundant column DEPT_NAME to EMPLOYEE, so that it avoids join with DEPT and thus increasing the performance.

Now no need to join with DEPT to get the department name to get details. But it creates a redundancy of data on DEPT_NAME.

Adding derived columns

Suppose we have STUDENT table with student details like his ID, name, address and course. Another table MARKS with his internal marks in different subjects. There is a need to generate a report for individual student in which we need to have his details, total marks and grade. In this case, we have to query STUDENT table, then join the MARKS table to calculate the total of marks in different subjects. Based on the total, we have to decide the grade too in the select query. Then it has to be printed on the report.

Above query will run for each of the student records to calculate total and grade. Imagine how many students will exist and how many times this query will retrieve the data and do calculation? Instead what if we have total and grade stored in the STUDENT table itself? It will reduce join time and the calculation time. Once all the marks are inserted into the MARKS table, we can calculate the total and GRADE for each student and get STUDENT table updated for these columns (we can have trigger on MARKS to update STUDENT table, once marks are inserted). Now if we have to generate the report, simply fire a SELECT query on STUDENT table and print it on report.

Источник

Денормализация базы данных

Всем привет! Сегодня мы с Вами подробно рассмотрим процесс денормализации базы данных, Вы узнаете, что такое денормализация, как и зачем вообще проводить эту денормализацию.

Нормализация базы данных

В предыдущих материалах мы подробно рассмотрели нормализацию базы данных и каждую нормальную форму базы данных, вот соответствующие статьи:

Сегодня мы с Вами поговорим об обратном процессе, т.е. о денормализации.

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

Ранее мы выяснили, что данные в базе данных хранятся в таблицах, иными словами, база данных – это набор таблиц.

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

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

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

Иными словами, нормализация — это не процесс целенаправленного приведения базы данных к какой-то определенной нормальной форме, нормализация – это набор принципов, зная и следуя которым, мы можем спроектировать базу данных, структура которой будет гарантировать нам отсутствие определенного рода аномалий.

Таким образом, нет требования, которое обязывало бы нас приводить базу данных к максимально возможной нормальной форме, например, к 5 или 6, и не нужно нормализовать базу данных только для того, чтобы она была нормализована.

Максимально нормализованная база данных – это плохая база данных.

Дело в том, что нормализация базы данных до 5 или 6 нормальной формы значительно снижает производительность, работа с такой базой будет неэффективна и неудобна, а управлять ей будет очень сложно.

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

Денормализация — намеренное приведение структуры базы данных в состояние, не удовлетворяющее требованиям нормализации.

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

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

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

Методика нормализации базы данных

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

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

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

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

И таким образом, Вы выполняете денормализацию.

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

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

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

Отсюда мы можем сформулировать определение хорошей базы данных.

Хорошая база данных – это база, которая достаточно нормализована, чтобы не создавать аномалии для пользователей этой базы данных, в то же время она достаточно денормализована, чтобы иметь хорошую производительность.

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

Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.

На сегодня это все, надеюсь, материал был Вам полезен и интересен, пока!

Источник

Нормализация и денормализация базы данных, нормальные формы

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

Нормализация — не догма, но чтобы её нарушать, нужны основания

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

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

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

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

Предположим, мы нарушили 1НФ и стали хранить фамилии, имена и отчества клиентов в одной колонке. Пока операторы вносили информацию, эта ошибка проектирования особенно не мешала, Однако, на следующем этапе понадобилась отчётность, в которой ФИО клиентов выводились бы в виде фамилии и инициалов. Оказалось, что некоторые записи вместо «Сидоров Петр Иванович» содержат «Петр Иванович Сидоров», в других отчества нет вовсе, в третьих фамилия двойная и не всегда записана через тире, в четвёртых после фамилий расставлены запятые. Эту проблему пришлось решать программированием совсем нетривиальной логики с элементами распознавания по словарю. Было потрачено много времени и средств, но в отчётности нет-нет да и проскакивали непонятные значения типа «Оглы П.Б.Б.».

Следует отметить, что при добавлении к этому учёту клиентов- иностранцев, проектировщиков логической схемы БД не спасла бы и более структурированная форма из трёх колонок для раздельного хранения фамилий, имён и отчеств. Потому что это проблема уровня концептуального проектирования и соответствующих моделей: необходим синтез не привязанной к модели данных структуры, способной вмещать в себя комбинации имён людей разных стран и культур.

Вторая нормальная форма (2НФ) означает, что выполнены требования 1НФ, при этом все атрибуты целиком зависят от составного ключа и не зависят ни от какой его части.

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

Ассоциативная таблица — таблица, имеющая ключевые связи с двумя и более таблицами

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

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

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

Третья нормальная форма (3НФ) означает, что выполнены требования 2НФ, при этом в между атрибутами отношения нет транзитивных зависимостей.

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

Предположим, что продажа каждой товарной позиции имеет своим основанием документ (заказ, счёт и т.д.), а её стоимость характеризуется ценой, количеством и валютой. В этом случае имеем следующие зависимости между атрибутами (колонками):

Эти зависимости транзитивны: каждая продажа однозначно определяет свой документ-основание и расчётную валюту, однако, валюта определяется ещё и документом.

Результатом нарушения 3НФ является избыточность хранения и необходимость обновления данных в связанной таблице. Так, если вы оставите колонку «Код валюты» в таблице продаж, то при изменении валюты документа придётся также обновлять все связанные с ним строки продаж.

Демормализация в базе данных: «звезда» и «снежинка»

Как можно понять из вышеприведённых примеров, основными целями нормализации являются:

Но список заявленных целей касается приложений транзакционных.

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

Зачем нужна денормализация?

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

Чтобы избежать ситуации тяжёлых соединений таблицы денормализуют. Но не абы как. Существуют некоторые правила, позволяющие считать денормализованные с точки зрения транзакционной обработки таблицы «нормализованными» согласно правилам построения таблиц для хранилищ данных.

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

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

Рис. 1. Денормализация документов в таблицу фактов

Теперь можно оценить, насколько облегчится для выполнения СУБД запрос, например, следующего вида: определить объёмы продаж муки клиентам «ООО Пирожки» и «ЗАО Ватрушки» за период.

В нормализованной транзакционной БД:

В аналитической БД:

Вместо тяжёлого соединения между двумя таблицами документов и их состава с миллионами строк, СУБД достаётся прямая работа с таблицей фактов и лёгкие соединения с небольшими вспомогательными таблицами, без которых также можно обойтись, зная идентификаторы.

Вернёмся к схемам «звезда» и «снежинка». За кадром первого рисунка остались таблицы клиентов, их групп, магазинов, продавцов и, собственно, товаров. При денормализации эти таблицы, называющиеся измерениями, также соединяются с таблицей фактов. Если таблица фактов ссылается на таблицы-измерения, имеющие ссылки на другие измерения (измерения второго уровня и выше), то такая схема называется «снежинка».

Рис. 2. Таблица фактов в схеме «снежинка»

Как можно заметить, для запросов, включающих фильтрацию по группам клиентов, приходится делать дополнительное соединение.

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

Схема, в которой таблица фактов ссылается только на измерения, не имеющие второго уровня, называется «звезда». Число таблиц измерений соответствует числу «лучей» в звезде.

Схема «Звезда» полностью исключает иерархию измерений и необходимость соединения соответствующих таблиц в одном запросе.

Рис. 3. Таблица фактов в схеме «звезда»

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

В некоторых СУБД, например Oracle, специальные целочисленные типы на уровне определений схемы БД отсутствуют, необходимо использовать универсальный логический тип numeric(N), где N — число хранимых разрядов. Размер хранения такого числа рассчитывается по специальной формуле, приводимой в документации по физическому хранению данных, и, как правило, он превышает таковой для низкоуровневых типов вроде «16­битное целое» на 1-3 байта.Положим, таблица продаж не использует компрессию данных и содержит около 500 миллионов строк, а количество групп покупателей порядка 1000. В этом случае мы можем использовать в качестве типа идентификатора id_customer_group короткое целое (shortint, smallint), занимающее 2 байта.

Будем считать, что наша СУБД поддерживает двухбайтовый целочисленный тип (например, PostgreSQL, SQL Server, Sybase и другие). Тогда добавление соответствующей колонки id_customer_group в таблицу продаж вызовет увеличение её размера как минимум на 500 000 000 * 2 = 1 000 000 000 байт

Источник