Денормализация данных лучше, чем делать вычитание таблиц

Здравствуйте господа.

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

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

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

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

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

Представим структуру базы данных.

Заявка

• id
• raw_data

Ответы

• id
• id_admin -> (внешний ключ на таблицу админов — не привел так как и так понятно)
• id_заявка -> (внешний ключ на Заявка.id)
• raw_data

— Реляционное решение и проблема производительности
Все заявки пользователей хранятся в единственном виде в нормальной форме. Все отвеченные заявки для определенного администратора ясное дело как найти, надо просто выбрать данные по id_admin AND id_заявка. А вот что делать с не отвеченными заявками? Единственное правильное решение это сделать вычитание отвеченных заявок из не отвеченных. Это означает, что каждый администратор должен сделать полную выборку всех своих ответов и вычесть их из всех заявок в целом. Ну хорошо 10 записей ладно, 100 записей ладно, но время идет, проходит год — администраторы уже имеют по 10к записей, а их например сотни. Понятное дело вычитать при каждом запросе 10к+ записей из скажем 12к+ не очень оптимально. Мы видим в этой схеме явную деградацию производительности.

— Решение с помощью денормализации
Попробуем поправить ситуацию с помощью денормализации и созданим новую структуру данных, вот она:

Заявка

• id
• raw_data

Ответы

• id
• id_admin -> (внешний ключ на таблицу админов — не привел так как и так понятно)
• id_заявка -> (внешний ключ на Заявка.id)
• raw_data

Ответы_админов

• id
• id_admin -> (внешний ключ на таблицу админов — не привел так как и так понятно)
• id_заявка -> (внешний ключ на Заявка.id)
• id_ответы DEFAULT NULL -> (внешний ключ на Ответы.id)

Особых различий нет, часть где данные хранятся в нормальном виде полностью сохранились, а денормализация данных добавилась в виде таблицы Ответы_админов. При создании новой записи в заявке происходит генерация данных в Ответы_админов при в количестве N копий — по одной на каждого админа.
Тут выборка уже происходит намного легче. Для одной таблицы мы вибераем такие записи которые id_ответы NOT NULL а для другой которые NULL, вся работа полностью производится на индексированных полях что делает производительность теоретически максимальной даже на огромном количестве данных. Пропорциональность пользователей я написал не зря, так как количество заявок, которые они создают намного больше чем администраторов, а значит на каждую новую заявку будет создано сравнительно небольшое количество копий в таблице Ответы_админов. В противном случае за «точку» денормализации можно было взять и другое. Ну и естественно в случае баланса самым оптимальным является хранение в нормальной форме + запастись хорошим сервером на вертикальное масштабирование и сервера репликации.

— Выводы
Хорошие стороны денормализации

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

Плохие стороны денормализации

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

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

Автор: wartur