Должны ли базы данных OLAP быть денормализованы для производительности чтения?

агрегация: Рассмотрим таблицу, содержащую 1 миллиард покупок. Сравните его с таблицей, содержащей одну строку с суммой покупок. Теперь, что быстрее? Выберите сумму (сумма) из таблицы с одной миллиардной строкой или выберите сумму из таблицы в одной строке таблицы? Конечно, это глупый пример, но он довольно ясно иллюстрирует принцип агрегации. Почему это происходит быстрее? Потому что независимо от того, какую магическую модель/аппаратное/программное обеспечение/религию мы используем, чтение 100 байт быстрее, чем чтение 100 гигабайт. Просто.

денормализации: Типичное измерение продукта в розничном хранилище данных содержит множество столбцов. Некоторые столбцы-это простые вещи, такие как" имя "или" цвет", но у него также есть некоторые сложные такие вещи, как иерархия. Несколько иерархий (ассортимент продукции (5 уровней), предполагаемый покупатель (3 уровня), сырье (8 уровней), способ производства (8 уровней) наряду с несколькими вычисленными числами, такими как среднее время выполнения (с начала года), вес/упаковочные меры и т. д. Я поддерживал таблицу измерения продукта с 200 + столбцами, которая была построена из ~70 таблиц из 5 различных исходных систем. Просто глупо спорить, является ли запрос нормализованным модель (ниже)

select product_id
  from table1
  join table2 on(keys)
  join (select average(..)
          from one_billion_row_table 
         where lastyear = ...) on(keys)
  join ...table70
 where function_with_fuzzy_matching(table1.cola, table37.colb) > 0.7
   and exists(select ... from )
   and not exists(select ...)
   and table20.version_id = (select max(v_id from product_ver where ...)
   and average_price between 10 and 20
   and product_range = 'High-Profile'

...быстрее, чем эквивалентный запрос на денормализованной модели:

select product_id
  from product_denormalized
 where average_price between 10 and 20
   and product_range = 'High-Profile';

почему? Отчасти по той же причине, что и агрегированный сценарий. Но и потому, что запросы просто "сложные". Они настолько отвратительно сложны, что оптимизатор (и теперь я собираюсь конкретизировать Oracle) запутывается и портит планы выполнения. Неоптимальные планы выполнения могут быть не такими уж большими, если запрос имеет дело с небольшими объемами данных. Но как как только мы начинаем объединяться в большие таблицы это важно что база данных получает план выполнения права. Денормализовав данные в одной таблице с помощью одного синтетического ключа (черт, почему бы мне не добавить больше топлива к этому продолжающемуся огню), фильтры становятся простыми фильтрами диапазона/равенства на предварительно приготовленных столбцах. Дублирование данных в новые столбцы позволяет нам собирать статистику по столбцам, которая поможет оптимизатору в оценке избирательности и, таким образом, предоставит нам при правильном выполнении плана (ну,...).

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

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

обновление 1

PerformanceDBA: 1 строка будет обновляться миллиард раз в день

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

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

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

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

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

нормальная форма (обычно 5-я / 6-я нормальная форма), как правило, является лучшей моделью для хранилища данных. Причины для нормализации хранилища данных точно такие же, как и для любой другой базы данных: это уменьшает избыточность и позволяет избежать потенциальных аномалий обновления; это позволяет избежать встроенного смещения и, следовательно, является самым простым способом поддержки изменения схемы и создания новых требования. Использование обычной формы в хранилище данных также помогает сохранить процесс загрузки данных простым и последовательным.

нет никакого "традиционного" подхода денормализации. Хорошие хранилища данных всегда были нормализованы.

Не следует ли денормализовать базу данных для производительности чтения?

хорошо, здесь идет общий "ваш пробег может варьироваться", "это зависит", "Используйте правильный инструмент для каждой работы"," один размер не подходит всем " ответ, с немного "не исправить, если он не сломан" бросил в:

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

Это следует учитывать только тогда, когда вы сталкиваетесь с проблемой производительности (потому что вы даете преимущества нормализации и вводите сложность).

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

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

сначала мои мнения, потом какой-то анализ

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

это, строго говоря, является ложные см. Этот вопрос/ответ, денормализация в строгом смысле означает нарушение любого из обычные формы от 1NF-6NF, другие зависимости вставки, обновления и удаления адресуются с принцип ортогонального проектирования.

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

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

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

анализ

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

1) Нарушение 1NF

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

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

account_balances(month, report)

который будет содержать структурированные остатки XML для каждого счета. Это нарушает 1NF (см. Примечания ниже), но позволяет выполнить один конкретный запрос с помощью минимальный ввод / вывод.

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

Примечание: Это на самом деле тривиальный пример, и с ним есть одна проблема - определение 1NF. Предположение, что приведенная выше модель разбивает 1NF, соответствует требованию, что значения атрибута'содержать ровно одно значение из соответствующего домена'.

это позволяет сказать, что домен атрибута report представляет собой набор всех возможных отчетов и что из всех них есть ровно одно значение и утверждать, что 1NF является не сломано (подобно аргументу, что хранение слов не нарушает 1NF, даже если у вас может быть letters отношения где-то в вашей модели).

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

в любом случае это объясняет, почему люди утверждают это нарушение NFs может повысить производительность.

2) Нарушение 3NF

предполагая таблицы в 3NF

CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(10) unsigned NOT NULL,
  `status` tinyint(3) unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `opening` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  CONSTRAINT `t_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `m` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

CREATE TABLE `m` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

С данными по образца (1м строк в Т, 100к в М)

предположим, что общий запрос, который вы хотите улучшить

mysql> select sql_no_cache m.name, count(*) 
       from t join m on t.member_id = m.id 
       where t.id between 100000 and 500000 group by m.name;
+-------+----------+
| name  | count(*) |
+-------+----------+
| omega |       11 |
| test  |        8 |
| test3 |   399982 |
+-------+----------+
3 rows in set (1.08 sec)

вы можете найти предложения по перемещению атрибута name в таблицу m, которая разбивает 3NF (он имеет FD: member_id -> имя и member_id не является ключом t)

после

alter table t add column varchar(255);
update t inner join m on t.member_id = t.id set t.name = m.name;

под управлением

mysql> select sql_no_cache name, count(*) 
       from t where id 
       between 100000 and 500000 
       group by name;
+-------+----------+
| name  | count(*) |
+-------+----------+
| omega |       11 |
| test  |        8 |
| test3 |   399982 |
+-------+----------+
3 rows in set (0.41 sec)

заметки: Приведенное выше время выполнения запроса разрезать пополам, а

• таблица не была в 5NF/6NF для начала
• тест был выполнен с no_sql_cache, поэтому большинство механизмов кэша были исключены (и в реальных ситуациях они играют определенную роль в производительности системы)
• потребление пространства увеличивается примерно на 9x размер имени столбца x 100k строки
• должны быть триггеры на t, чтобы сохранить целостность данных, что значительно замедлит все обновления для name и добавит дополнительные проверки, которые вставки в t должны будут пройти
• вероятно, лучшие результаты могут быть достигнуты путем отбрасывания суррогатных ключей и переключения на естественные ключи и / или индексирования или перепроектирования на более высокий NFs

нормализовать правильный путь в долгосрочной перспективе. Но у вас не всегда есть возможность перепроектировать ERP компании (который, например, уже только в основном 3NF) - иногда вы должны достичь определенной задачи в рамках заданных ресурсов. Конечно, это только краткосрочное "решение".

итог

Я думаю, что наиболее уместным ответом на ваш вопрос является то, что вы найдете промышленность и образование, используя термин "денормализация" в

• строго говоря, для нарушение NFs
• свободно, введение любых зависимостей вставки, обновления и удаления (оригинальная цитата Кодда комментарии о нормализации поговорка: 'нежелательными(!) зависимости вставки, обновления и удаления", см. Некоторые подробности здесь)

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

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

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

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

• они не являются частью логической модели
• они прозрачны и гарантированно не нарушают целостность вашей модели

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

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

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

двумя наиболее популярными методологиями построения хранилища данных (DW), по-видимому, являются Билл Инмон и Ральф Кимбалл.

методология Inmon использует нормализованный подход, в то время как Кимбалл использует размерное моделирование-де-нормализованную звездную схему.

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

Я не могу прокомментировать подход 6NF ни на Якорное моделирование, потому что я никогда не видел и не участвовал в проекте DW, использующем эту методологию. Когда дело доходит до реализации, Мне нравится путешествовать по хорошо проверенным путям-но это только я.

Итак, подводя итог, должен ли DW быть нормализован или де-нормализован? Зависит от методологии, которую вы выберете - просто выберите один и придерживайтесь его, по крайней мере, до конца проекта.

EDIT-пример

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

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

дело в том, что беспорядок скрипта python и SQL занимал восемь часов (да, e-i-g-h-t часов) для запуска каждый день. Излишне говорить, что база данных и приложение были построены в течение многих лет несколькими партиями разработчиков-так что, не совсем ваш 5NF.

Это было время, чтобы заново создать наследие от ДГ. Хорошо, чтобы держать его коротким это сделано, и это занимает 3 минуты (t-h-r-e-e минут), чтобы произвести его, шесть секунд на суб-отчет. И я спешил доставить, поэтому даже не оптимизировал все запросы. Это фактор 8 * 60 / 3 = В 160 раз быстрее - не говоря уже о преимуществах удаления восьмичасового задания с рабочего сервера. Я думаю, что еще могу побриться минутку или около того, но сейчас это никого не волнует.

в качестве точки интереса я использовал метод Кимбалла (размерное моделирование) для DW, и все, что используется в этой истории, является открытым исходным кодом.

вот о чем все это (хранилище данных) должно быть, я думаю. Имеет ли вообще значение, какая методология (нормализованная или де-нормализованная) была использовали?

EDIT 2

в качестве точки интереса, Билл Инмон имеет красиво написанный документ на своем веб-сайте -Повесть о двух архитектурах.

проблема со словом "денормализованных" заключается в том, что он не указывает, в каком направлении идти. Это примерно то же самое, что пытаться добраться до Сан-Франциско из Чикаго, уезжая из Нью-Йорка.

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

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

короткий ответ:не исправляйте проблему производительности, которую вы не получили!

Что касается таблиц на основе времени, то общепринятый pardigm должен иметь valid_from и valid_to даты в каждой строке. Это все еще в основном 3NF, поскольку он только изменяет семантику от "это единственная и единственная версия этой сущности "до" это единственная и единственная версия этой сущности в это время"

упрощение:

база данных OLTP должна быть нормализована (насколько это имеет смысл).

хранилище данных OLAP должно быть денормализовано в таблицы фактов и измерений (для минимизации соединений).