Вы не любите кошек? Да вы просто не умеете их готовить! (с) Альф
При проектировании достаточно объёмных реляционных баз данных часто принимается решение об отступлении от нормальной формы — «денормализации». Причины могут быть разными. От попытки ускорения доступа к определённым данным, ограничений используемой платформы/фреймворка/средств разработки и до недостатка квалификации разработчика/проектировщика БД.
Впрочем, строго говоря, ссылка на ограничения фреймфорка и т.п. — по сути попытка оправдать недостаток квалификации.
Денормализованные данные — слабое звено, через которое легко можно привести нашу базу в неконсистентное (нецелостное) состояние.
Что с этим делать?
Пример
В базе данных есть таблица с какими-то финансовыми операциями: поступление и списание средств по разным счетам.
Нужно всегда знать остаток средств на счёте.
В нормализованных данных остаток средств — всегда рассчитываемая величина. Суммируем все поступления минус списания.
Однако, когда количество операций ну очень большое, то каждый раз рассчитывать остаток слишком затратно.
Поэтому принято решение хранить актуальные остатки в отдельной таблице. Как обновлять данные в этой таблице?
Решение «как обычно»
Практически во всех информационных системах, с которыми мне приходилось работать, эту задачу выполняло внешнее приложение, в котором реализована бизнес логика. Хорошо, если приложение несложное и точек изменения данных — одна, из формы в пользовательском интерфейсе. А если есть какие-то импорты, API, сторонние приложения и так далее? И эти вещи делают разные люди, команды? А если не одна таблица с итогами, а их несколько в разных разрезах? А если ещё и не одна таблица с операциями (встречал и такое)?
Тут уследить за тем, что разработчик при обновлении операции не забыл обновить ещё кучку таблиц, становится всё сложнее и сложнее. Данные теряют целостность. Остатки по счёту не соответствуют операциям. Конечно, тестирование должно выявить такие ситуации. Но мы живём не в таком идеальном мире.
Кошки Триггеры
В качестве альтернативы для контроля целостности денормализованных данных «взрослой» СУБД используют триггеры.
Часто приходилось слышать, что триггеры жутко замедляют базу данных, поэтому их использование нецелесообразно.
Вторым аргументом было то, что вся логика находится в отдельном приложении и держать бизнес логику в разных местах тоже нецелесообразно.
Давайте разберёмся.
Тормоза
Триггер срабатывает внутри транзакции, изменяющей данные в таблице. Транзакция не может быть завершена, если триггер не произвёл необходимых действий. Отсюда вывод: триггеры должны быть как можно «легче». Пример «тяжёлого» запроса в триггере:
update totals
set total = select sum(operations.amount) from operations where operations.account = current_account
where totals.account = current_accountЗапрос обращается к таблице операций (operations) и суммирует все суммы операций (amount) для счёта (account).
Такой запрос с ростом базы данных будет съедать всё больше и больше времени и ресурсов. Но того же результата можно добиться, используя «лёгкий» запрос типа:
update totals
set total = totals.total + current_amount
where totals.account = current_accountТакой триггер при добавлении новой строки просто увеличит итог по счёту, не рассчитывая его заново, он не зависит от объёма данных в таблицах. Рассчитывать итог заново нет смысла, так как мы можем быть уверены, что триггер срабатывает ВСЕГДА при добавлении новой операции.
Аналогично обрабатывается удаление и изменение строк. Такого типа триггеры практически не замедлят операции, но будут гарантировать связность и целостностность данных.
Всегда, когда я наблюдал «тормоза» при вставке данных в таблицу с триггером, это был образчик такого «тяжёлого» запроса. И в подавляющем большинстве случаев удавалось переписать его в «лёгком» стиле.
Бизнес логика
Тут стоит отделить мух от котлет. Есть смысл отличать функции, обеспечивающие целостность данных, от собственно бизнес логики. В каждом таком случае задаю вопрос: если бы данные были нормализованы, то была бы нужна такая функция? Если ответ положительный — это бизнес логика. Отрицательный — обеспечение целостности данных. Смело заворачивайте эти функции в триггеры.
Впрочем, есть мнение, что всю бизнес логику легко можно реализовать средствами современной СУБД, такой как PostgreSQL или Oracle. Подтверждение нахожу в своём just-for-fun проекте.
Надеюсь, эта статья поможет уменьшить количество багов в вашей информационной системе.
Конечно, я далёк от мысли, что всё здесь написанное, является истиной в последней инстанции. В реальной жизни, конечно же, всё сложнее. Поэтому решения в каждом конкретном случае принимать вам. Используйте своё инженерное мышление!
Комментарии (47)
Godless
15.09.2016 17:25+2триггеры прекрасны. особенно в pgsql.
не знаю как можно ими не пользоваться…
zip_zero
15.09.2016 17:36+1Пока в Oracle существует statement restart, серьезную логику на триггерах писать нельзя.
приходилось слышать, что триггеры жутко замедляют базу данных
Пф, снимите трассу 10046 на вставке с триггером — и не только услышите, но и увидите.
molnij
16.09.2016 10:30+1Сильное утверждение, про «нельзя», уточните пожалуйста, что у вас входит в серьезную логику?
Насколько я понял, statement restart можно огрести, если вы в рамках транзакции пытаетесь выйти за её рамки — написать в output (возможно, прокинуть внутри автономную транзакцию, но это проверять нужно), скорее всего — дернуть внешний сервис, но если работаете в обозначенных рамках транзакции — постэффектов не будет. С другой стороны, если вы в рамках транзакции дергаете внешний сервис, то либо у вас крупные проблемы, либо вы очень круто знаете как их решать — в обоих случаях это не проблема базы и триггеров.
А по поводу замедления — всё ж очень просто — триггер выполняет работу. Очевидно, что он замедляет выполнение операций. Насколько — зависит от вашего намерения и умения — поставите внутри крутиться длинный цикл -может и фатально замедлить. Но обычно работа триггера это размен в выполнении операции — либо её один раз выполнить в триггере при обновлении данных, либо выполнять на каждой выборке. А дальше простое сравнение характеристики нагрузки — если чтений существенно больше — ставим на триггер, если записей больше — на выборку.
Я как-то доходил и до точки, когда генерация последовательностей тормозила определенную работу, это же не значит, что ими нельзя пользоваться.zip_zero
16.09.2016 16:41Триггерам на Oracle есть интересная альтернатива, тему в подробностях и сравнением осветили лет десять назад. И чем «сложнее» эта логика, тем заметнее замедление триггеров, в сравнении с API на хранимых процедурах (например, как одна из опций).
Сорри, я не хотел бы сейчас ударяться в философию и рассуждать, когда у меня начинается «серьезная» логика, а когда ещё нет :)
QuickJoey
15.09.2016 18:17Впрочем, есть мнение, что всю бизнес логику легко можно реализовать средствами современной СУБД, такой как PostgreSQL или Oracle. Подтверждение нахожу в своём just-for-fun проекте.
.оффтопик
я далёк (к сожалению) от веб-технологий, поэтому задам тупой вопрос, извините. у меня есть проект, где вся бизнес-логика реализована на Postgres (с приложением под windows), права на процедуры (функции) заданы там же, на Postgres. насколько я понимаю, в веб-приложениях так не делается. вопрос собственно в том, что делать, если очень хочется, чтобы уже на этапе авторизации пользователя он представлял сам себя в БД? и, соответственно все запросы шли от его имени. такое вообще бывает? смысл в том, чтобы оставив всё как есть в БД, попробовать сделать к ней веб-интерфейс, (почти) ничего не меняя в процедурах, и уж точно ничего не меняя в распределении ролей.gearbox
15.09.2016 20:16такое вообще бывает?
Да, но зависит от числа одновременно работающих пользователей. Просто в пуле придется держать число коннектов по числу одновременно работающих авторизованных пользователей. А так, имхо, это более правильный подход.
darthunix
16.09.2016 03:06Базы данных держат сотни подключений, а не тысячи, как web сервера. И каждое простаивающее подключение съедает ресурсы. Именно поэтому никто взаимооднозначно не транслирует клиентов через сервер в базу, а заворачивают кучу пользователей в один пул. Иначе база не потянет. Поэтому штатными средствами acl базы вы сможете раздать права на выполнение функций только учеткам, которые используются в пуле. Не штатными — можно использовать GUC (в случае PostgreSQL), выставляя на сервере в каждой транзакции имя пользователя через set_config. А в функциях вытаскивать из глобальной переменной имя пользователя и делать проверку.
QuickJoey
16.09.2016 11:37если у меня сервер БД уже работает на каком-то количестве пользователей (сейчас это сотни), то и веб-сервер их переварит, это корпоративное приложение. создание новых ролей приведёт к тому, что я должен буду раздавать права пользователям веб заново. и самое ужасное, поддерживать целостность между двумя наборами пользователей.
кстати о «сделать проверку», каким образом? я пробовал писать пустые процедуры с разным доступом, только для проверки прав, и потом ловить исключения, в MSSQL это работает, в PostgreSQL победить не смог.
areht
16.09.2016 01:00-1Я в статье не увидел одного принципиального момента: зачем? Эта проблема давно и успешно решается без триггеров.
> Вы не любите кошек? Да вы просто не умеете их готовить! (с) Альф
А мне вспоминается «а вы на шкаф залезьте»
Rastishka
16.09.2016 13:01+1Расскажете как?
areht
16.09.2016 16:29Делаем одну точку изменения данных(репозиторий), куда вкручиваем «триггер».
TimsTims
16.09.2016 17:45Это скорее костыль от незнания, что есть триггеры, выполняющие то же самое, но на уровне той же бд, а не отдельной точки входа, которая просто дублирует функционал БД.
areht
16.09.2016 18:00Триггер гораздо примитивнее. Единая точка сохранения — это ещё и логирование, например (и не туда, куда оракл хочет, а куда мне надо и удобно).
Если надо внутри кортежа что-то проверять/поправить — это можно делать триггером, как только логика чуть сложнее — одни проблемы.
KonstantinSoloviov
16.09.2016 17:48То есть предлагается вместо триггера БД использовать триггер-велосипед, который придется тянуть во все бизнес-процессы изменяющие данные, которые в свою очередь могут быть очень разнообразными и ваш «репозиторий» в них будет всего лишь малой частью транзакции.
Да. так тоже можно.areht
16.09.2016 17:53Велосипед — это когда я не могу сделать один раз
set total = select sum(operations.amount) from operations where operations.account = current_account
после обновления 100 000 записей. И на «быстрое построчное» поменять можно далеко не всегда.KonstantinSoloviov
16.09.2016 18:02Несерьезно,
если ваше обновление затрагивает 100000 разных счетов в примере топикстартера, в таблице операций миллиард записей и нет индекса по счету — ну, вперед! :)
Естественно, исходить надо от задачи, можно придумать пример когда и триггер будет плох.areht
16.09.2016 19:14> нет индекса по счету
как будто триггеру индекс не нужен
> Естественно, исходить надо от задачи, можно придумать пример когда и триггер будет плох.
Можно. А когда репозиторий будет плох?
А задача топикстартера проще решается материализованной вьюшкой. То есть, в первом приближении, триггер или не нужен, или не применим.KonstantinSoloviov
16.09.2016 20:39Я устал с вами спорить :)
>> нет индекса по счету
>как будто триггеру индекс не нужен
индекс по счету нужен на таблицу totals (и в моем и в вашем варианте), но не на operations (в вашем)
>Можно. А когда репозиторий будет плох?
ваш репозиторий — «сферический конь в вакууме» его можно сделать хорошо, можно плохо, а вариант с триггером понятен потому прозрачен и просчитывается
>А задача топикстартера проще решается материализованной вьюшкой. То есть, в первом приближении, триггер или не нужен, или не применим.
вот! а как по вашему реализованы эти самые вьюшки :) триггера используются самим oracle в хвост и в гривуareht
16.09.2016 20:57> но не на operations (в вашем)
Я вас уверяю, что на operations индекс по счёту будет, к такой таблице не по счёту вообще не обращаются.
> ваш репозиторий — «сферический конь в вакууме» его можно сделать хорошо, можно плохо, а вариант с триггером понятен потому прозрачен и просчитывается
Потому, что вы на триггер изначально вешаете ограничения, что он быстр и нужные индексы созданы.
> а как по вашему реализованы эти самые вьюшки
А мне, простите, без разницы. Мне не надо писать и отлаживать триггера — это функционал оракла, который мне, действительно, не надо переписывать.
> триггера используются самим oracle
И, кстати, почему именно в оракле?KonstantinSoloviov
16.09.2016 21:14>Я вас уверяю, что на operations индекс по счёту будет, к такой таблице не по счёту вообще не обращаются.
домыслы.
триггера позволяют стоить учет по «левым» параметрам по которым не нужно строить выборку и потому индекс по ним — дорогое удовольствие
>… это функционал оракла, который мне, действительно, не надо переписывать.
триггер — точно такой же функционал ораклаareht
16.09.2016 21:34> домыслы.
Опыт.
И нехитрая логика.
> триггера позволяют стоить учет по «левым» параметрам по которым не нужно строить выборку и потому индекс по ним — дорогое удовольствие
Вы меня обвиняли в сферичности коней, а сами загадками говорите. Я не представляю зачем строить учёт по параметрам, по которым не надо строить выборку.
Впрочем, ради хитрой оптимизации можно и не так раскорячиться.
> триггер — точно такой же функционал оракла
Нет, это абстракция более низкого уровня. Ниже KonstantinSoloviov выписал часть того, что надо учитывать с триггерами. С вьюхой всё проще.
zoroda
17.09.2016 08:53А задача топикстартера проще решается материализованной вьюшкой. То есть, в первом приближении, триггер или не нужен, или не применим.
Увы, не решается. Материализованное представление требует периодического обновления. При первой же записи в operations это представление будет содержать устаревшие данные до очередного обновления.
sergio_deschino
16.09.2016 09:01Триггеры действительно очень выручают, особенно, когда необходимо реализовать незначительные изменения в конфигурации на одном из серверов филиала или реализовать логирование изменений бд в самой бд.
KonstantinSoloviov
16.09.2016 17:53+1О, точно! Для логов триггеры — вообще мастхев. А если сделать автогенератор таких логирующих триггеров да вспомнить, что триггер можно повесить и на изменение метаданных — получаем мастхев в квадрате :)
Vjatcheslav3345
16.09.2016 10:05Такой триггер при добавлении новой строки просто увеличит итог по счёту, не рассчитывая его заново, он не зависит от объёма данных в таблицах. Рассчитывать итог заново нет смысла, так как мы можем быть уверены, что триггер срабатывает ВСЕГДА при добавлении новой операции.
Тогда, наверное, мы сменим зависимость "тормозов" от объёма данных на тормоза от нагрузки на базу? — но, в принципе, для большинства нужд типа бухучёта, это подойдёт.
Nagh42
16.09.2016 10:11Такой триггер при добавлении новой строки просто увеличит итог по счёту, не рассчитывая его заново, он не зависит от объёма данных в таблицах. Рассчитывать итог заново нет смысла, так как мы можем быть уверены, что триггер срабатывает ВСЕГДА при добавлении новой операции.
На самом деле это не так. Триггер может в какой-то момент для чего-нибудь быть отключен или сломан. Так что рассчитывать итог всё равно нужно. Просто это можно делать не в триггере, а, например, в шедулере с необходимой периодичностью, для того чтобы исправлять возможные сбои в подсчётах суммы.
defecator
16.09.2016 10:13+2Триггеры очень сильно размывают логику программ.
В случае использования хранимых процедур всю логику и алгоритмы работы можно сосредоточить в одном месте.
А триггеры — зло, имхо. На них можно делать только логирование изменений в таблицах,
но запихивать в них бизнес-код нет смысла, если не хочется выстрелить себе в ногу.
И да, для сохранения целостности нужно не бояться использовать конестрейнты,
благо, в Оракле они очень развиты.QuickJoey
16.09.2016 11:29+1Триггеры очень сильно размывают логику программ.
В случае использования хранимых процедур всю логику и алгоритмы работы можно сосредоточить в одном месте.
с логикой становится тяжелее, да. но есть узловые (критические) таблицы, с большим количеством точек записи/изменений в них (десятки хранимых процедур), где очень хочется иметь целостные данные вне зависимости от того, кто как и когда придумает менять данные в таблиц. и решение предложенное в статье мне кажется изящным и, самое главное, лёгким для переваривания сервером БД.
Vjatcheslav3345
16.09.2016 10:52-2Немного не в тему, но...FLAP из примера стоило бы "заточить" по антикоррупционную направленность, научив обнаруживать основные схемы мошенничеств и оформить её в виде одного исполняемого файла (который даже установки не потребует) — тогда она была бы востребована в среде старших по дому и граждан, проживающих в домах, а также у правоохранителей (в качестве несложного "термометра" для коррупции).
KonstantinSoloviov
16.09.2016 11:24Ох, хорошая тема и так нераскрыта…
Триггеры прекрасны, но пользоваться надо ими очень аккуратно.
Ваш...«лёгкий» запрос типа:
update totals
set total = totals.total + current_amount
where totals.account = current_account
сработает только в случае инсерта в таблицу operations и то при условии что в totals есть соотвествующий account.
То есть не забываем делать insert новых account в totals.
Дальше, если изменяется значение current_amount в operations триггер должен учитавать :old и :new значения current_amount.
Eсли делается delete operations и это последная запись с таким account в totals — надо делать delete в totals.
И вишенка на торте: в operations можно изменить не только current_amount но и сам account! Тогда в totals надо модифицировать значение по старому счету и добавить по новому.
И закрываться надо со всех сторон, не полагаясь, например, на то, что бизнес-логика не позволяет менять номер счета — все рано или поздно найдется умник с каким-нибудь sqldevelop-ером на перевес :)
Да и триггера конечно должны быть только AFTER т.к. в BEFORE триггерах :new, :old значения окончательно не определены.QuickJoey
16.09.2016 11:33И закрываться надо со всех сторон, не полагаясь, например, на то, что бизнес-логика не позволяет менять номер счета — все рано или поздно найдется умник с каким-нибудь sqldevelop-ером на перевес :)
у умника должен быть соответствующий доступ. а простой смертный имеет права только на процедуры, которые за него придумал разработчик, и у него нет прав даже читать из таблицы простым select'ом. но вы правы, метод хорош тем, что защищает данные в том числе и от самого себя.
zoroda
17.09.2016 09:18Полностью согласен с вами.
Конечно, в реальности триггер несколько развесистее, с контролями и обработкой разных ситуаций. Я сознательно привёл только участок кода, на оптимизацию которого обращаю внимание в статье.
Опять же, призываю всех включать своё инженерное мышление. Воможно, в каких-то конкретных случаях есть смысл ради повышения производительности упростить триггерную функцию и запретить изменения, например, поля account в триггере BEFORE. В других случаях может быть принято решение пожертвовать скоростью операций вставки/изменения/удаления ради тотального контроля. А в каких-то случаях жертвуют целостностью ради повышения скорости вставки данных.
Tercel
16.09.2016 19:24От попытки ускорения доступа к определённым данным, ограничений используемой платформы/фреймворка/средств разработки и до недостатка квалификации разработчика/проектировщика БД.
Впрочем, строго говоря, ссылка на ограничения фремфорка и т.п. — по сути попытка оправдать недостаток квалификации.
Отклонили комментарий, ну и ладно, хотя бы ошибку исправьте.
DrPass
> Есть смысл отличать функции, обеспечивающие целостность данных, от собственно бизнес логики.
Функция, которая считает баланс взаиморасчетов в разрезе клиентов и заносит его во временную таблицу, это целостность данных или бизнес-логика?
Rastishka
Это целостность данных, так как это денормализация посредством агрегации уже имеющихся данных в таблице.
vlivyur
>временную таблицу
Это бизнес-логика какая-то.
Rastishka
Почему?
Если отчет типа "Баланс клиента с XX.XX.2016 по YY.YYY. 2016 на момент ZZ.ZZ.2016", который потом не должен меняться, даже если что то добавили задним числом — то бизнеслогика.
Если просто кеширование расчетов — денормализация.
vlivyur
Потому что перезапустил сервер и привет. Начинай с начала. Иначе это хранится где-то рядом с клиентом. Ну и если отчёт, то это отчёт, в нём логики как бы и нет, она была применена раньше, чтоб этому отчёту было что извлекать.
zoroda
Ключевое слово — «временную». Временная таблица не может быть частью нормализованных данных. Ваша функция — часть бизнес логики, которая готовит срез данных на какой-то момент времени по запросу пользователя.
Если же вам нужно обеспечить, чтобы эта таблица ВСЕГДА содержала данные, соответствующие вашим операциям, тогда её можно и нужно рассматривать как часть ваших данных, к которым предъявляются требования к целостности.
DrPass
На самом деле вопрос с подвохом. Это и то, и другое :)
Сам по себе расчёт баланса — чистой воды бизнес-логика, а поддержка целостности данных — это одно из требований к бизнес-логике. Иногда, в частных случаях, одно от другого можно отделить. Но в общем случае это вещи, идущие параллельно, и просто так взять и поставить слева умных, а справа красивых, не получится.
> Временная таблица не может быть частью нормализованных данных.
Почему? Понятие «временности» тут всего лишь определяет характер хранения данных в ней. Непосредственной связи с нормализацией/денормализацией нет. Если во временной таблице вы храните результаты агрегирования данных по каким-либо признакам, они вполне себе могут быть нормализованы.
Rastishka
По вашей логике и HTML/CSS/OpenGL можно за уши притянуть.
Ведь графический интерфейс — это одно из требований бизнес логики, не так ли?
По вашей формулировке это задача однозначно относится к целостности.
DrPass
> По вашей логике и HTML/CSS/OpenGL можно за уши притянуть
За уши можно притянуть при желании всё, что угодно, при любой логике.
> Ведь графический интерфейс — это одно из требований бизнес логики, не так ли?
Бизнес-логика определяет правила поведения и преобразования данных. Соответственно, графический интерфейс к бизнес-логике отношения не имеет, а вот целостность данных очень даже.
> По вашей формулировке это задача однозначно относится к целостности.
Обоснуйте своё мнение, пожалуйста. Я исхожу из того, что «функция, которая считает баланс взаиморасчетов в разрезе клиентов и заносит его во временную таблицу,» на выходе получает результат, имеющий вполне определённое значение в предметной области.
areht
Не понимаю. Мне нужна не функция — мне нужна сводная таблица (двумерный массив), в БЛ.
Поэтому появляется код, который её создаёт.
1) Код её создаёт быстро, таблица в БД нет — это БЛ.
2) Код тормозит, решили прихранивать в БД — теперь это функция, обеспечивающая целостность?
3) Код тормозит, в БД триггер тормозит, решили кешировать на клиенте — снова БЛ?
> В каждом таком случае задаю вопрос: если бы данные были нормализованы, то была бы нужна такая функция?
А как может быть не нужна функция, генерящая данные, если данные то нужны?
mickvav
Это кашв. IMHO, БЛ — это смысл вашей таблицы (типа, в ячейке x,y должны быть суммарные платежи клиентов типа x за продукты типа y), а то, как вы это храните и представляете пользователю — чистой воды целостность.
areht
Тогда получается «total = totals.total + current_amount» — БЛ, а целостность — это инфраструктурный код, который прихранивает?