Полезная денормализация - Materialized View
При проектировании отношений в реляционной базе данных обычно стараются придерживаться общепринятых форм нормализации (как минимум первых трех) для того, чтобы избежать проблем/неудобств при использований базы. Например, при игнорировании форм нормализации мы можем столкнуться с проблемой, когда у нас есть несколько источников правды - дублирование данных ведет к тому что один из источников в будущем может отличаться от другого.
Иллюстрация ситуации когда у нас есть несколько источников правды:
В данном случае у нас данные юзера дублируются в связующей таблице job_applications
и чтобы синхронизировать эти данные между собой нам нужно писать дополнительный код.
Но в некоторых случаях нам может потребоваться денормализовать отношения/данные, чтобы ускорить SQL запрос.
контекст
- Имеется сущность
Пользователь
. Некоторые пользователи могут являтьсяAдминистраторами
- Имеется сущность
Вакансия
- Пользователь может выбрать в профиле интересующую категорию, под-категорию и фокус (под-категория под-категории :))
- Вакансия также может принадлежать определенной категории, под-категории и иметь свои список фокусов (под-категории под-категории :))
- Администратор может запускать подбор кандидатов для каждой вакансии - проверяется совпадение категории между кандидатом и вакансией
- Администратор может заблокировать определенные категории для выбранных кандидатов - тогда данные кандидаты не будут учитываться в выборках по этим категориям.
ER-схема:
P.S.: Из-за бага в MySQL Workbench не смог указать флаг admin
как BOOLEAN
- пришлось оставить INT
. Представьте что там BOOLEAN
:)
- users - сущность пользователя
- jobs - сущность вакансии
- preference_themes - сущность категории, где level - ее уровень (0 - категория, 1 - под-категория и 2 - фокус)
- selected_themes - связующая сущность. С помощью полиморфного ключа связывает вакансии/пользователей с выбранными категориями
- rejected_themes - связующая сущность. Связывает пользователей и категории по которым будет осуществляться блокировка
Будем считать что у нас система рекомендации работает по следующему принципу:
- Мы находим тех пользователей, кто соответствует категориям указанным в вакансии
- Мы находим тех пользователей, кто заблокирован по категориям указанным в вакансии
- Возвращаем тех пользователей, кто присутствует в первой коллекции и не присутствует во второй
На данный момент нас интересует пункт 2. Предположим что интересующая нас вакансия имеет следующие категории: категория с id = 183 (level = 0), под-категория с id = 207 (level = 1) и фокус с id = 260 (level = 2).
SQL запрос для получения всех пользователей кто так или иначе заблокирован по этим категориям:
Что тут вообще происходит???
Мы просто находим всех пользователей кто:
- заблокирован по категории 183
- заблокирован по категории 183 и по под-категории 207
- заблокирован по категории 183 и по под-категории 207 и по под-категории под-категории 260
Это сделано для того чтобы не блокировать пользователей, у которых блокировка создана по другой категории (например, по 183 => 207 => 262). Такой пользователь все еще имеет шанс попасть в рекомендацию потому что блокировка задана с точностью до под-категории фокуса. Но если блокировка задана с меньшей точностью - например 183 => 207, то такие пользователи должны быть заблокированы в данной выборке.
проблема
Описанный выше SQL запрос выполняется довольно часто и при этом является довольно медленным. Выхлоп EXPLAIN ANALYZE
:
Planning Time: 3.382 ms
Execution Time: 11.168 ms
При этом сами блокировки для пользователей создаются довольно редко (всего около 100 штук было создано за 5 месяцев)
решение
Решающий фактор в пользу денормализации запроса и использования Materialized View
:
- сущности, используемые в запросе изменяются редко (100 раз за 5 месяцев), но при этом
SELECT
запрос происходит довольно часто (каждые 20 минут)
Нужно иметь данный фактор ввиду, так как при каждом изменении данных Materialized View
нужно обновлять, чтобы подтянуть свежие изменения. И для часто-обновляющихся данных такой способ не подойдет.
Что такое Materialized View
? Это результат нашего SELECT
запроса который занимает физическое место на нашем диске, тогда как обычный View
скорее является алиасом. Соответственно это дает нам возможность использовать наш materialized view как обычную таблицу, на которую можно навесить нужные нам индексы. Но, в отличии от обычной таблицы, мы не можем: удалять, вставлять, изменять данные непосредственно в materialized view. Обновление данных materialized view происходит согласно SELECT
запросу командой REFRESH MATERIALIZED VIEW
.
Денормализуем наши данные и сохраним их в materialized view:
Теперь нужно не забыть обновлять наш созданный materialized view при изменении блокировок:
REFRESH MATERIALIZED VIEW users_with_theme_rejects
Далее можно переписать запрос из предыдущего раздела с использованием созданного materialized view:
Выглядит гораздо короче! Что там по производительности?:
Planning Time: 0.065 ms
Execution Time: 0.050 ms
Это намного быстрее чем было раньше :) Но нужно также учитывать что мы теперь тратим время на обновление materialized view, это тоже можно замерить:
[Sidekiq::Extensions::DelayedClass] [30695bed5bfbfaf4b068ae5b] (18.8ms) REFRESH MATERIALIZED VIEW CONCURRENTLY users_with_theme_rejects
итого
Денормализация бывает полезна когда нам нужно упростить SQL запрос и избавиться от большого количества JOIN
-ов. Можно использовать materialized view для того чтобы хранить денормализованный вариант наших данных. Однако, нужно помнить что materialized view имеет свои ограничения и использовать его нужно только в тех ситуациях, когда это может дать нам преимущество.