serge_gorshkov


Сергей Горшков - о бизнесе в сфере ИТ

о семантической интеграции, программировании, управлении...


Previous Entry Share Next Entry
Реализация элементов логики приложения в MySQL: триггеры, хранимые процедуры, кэширование
serge_gorshkov
9 июня в Москве начнется devConf, главная российская конференция для веб-разработчиков. В этом посте - тезисы моего доклада.

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



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




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

Другим примером подобного решения является построение системы контроля прав доступа. Пусть имеется список записей (например, о клиентах), права доступа пользователей к которым определяются сложным набором правил. Правила могут зависеть от роли (группы) пользователя, каких-либо свойств записи (клиента), или от совпадения свойств пользователя и записи (например, наличие привязки к одному и тому же подразделению). Поскольку правила формулируются при помощи административного интерфейса системы, нет возможности заранее заложить в программе алгоритм определения того, какой уровень доступа будет иметь пользователь к тому или иному клиенту. Вместо этого, мы располагаем достаточно громоздкой PHP-функцией, которая для каждой пары пользователь-клиент определяет набор применимых правил, последовательно проверяет их выполнение, и вычисляет общий результат проверки.
Представим теперь, что нам нужно построить список клиентов с постраничной навигацией, и показать его пользователю. Для этого нужно, как минимум, определить общее число клиентов, которых он увидит, и выстроить их в определенном порядке для того, чтобы иметь возможность показать пользователю любую страницу такого списка. Процедура, решающая эту задачу, была нами реализована в рамках программного компонента – органа управления «список».
В первой реализации списка использовалось «лобовое» решение – проверка прав доступа при помощи PHP-функции. Работоспособность такой подпрограммы ограничивалась списками из нескольких десятков записей: уже на сотнях клиентов время выполнения проверки прав достигало значений, которые делали список непригодным для практического использования.
Первый шаг рефакторинга заключался в создании кэша прав доступа в базе данных. Кэш (таблица, хранящая права доступа для каждой пары пользователь-клиент) формировался той же самой PHP-функцией. Фрагменты кэша пересчитывались каждый раз при изменении правил доступа, свойств клиента или пользователя. В результате, операции изменения свойств стали выполняться недопустимо долго, хотя списки работали быстро даже на тысячах записей.
На втором шаге рефакторинга мы пришли к генерации хранимой процедуры в базе данных, которая получала в качестве параметров идентификаторы клиента и пользователя, и возвращала права доступа. Для этого в PHP была реализована генерация текста этой функции; функция пересоздавалась каждый раз при изменении правил доступа. Обращение к функции можно было включить в условие WHERE запроса к таблице «клиенты». Такой вариант работал намного быстрее двух предыдущих, и позволил обеспечить нормальную работу списка из тысяч записей. Однако, для практических целей этого было недостаточно.
На третьем шаге рефакторинга было принято решение соединить оба подхода. Был создан кэш в базе данных (таблица, хранящая права доступа каждого пользователя к каждому клиенту), фрагменты которого автоматически перестраиваются при помощи триггеров на таблицах «клиенты» и «пользователи». Текст триггеров генерируется автоматически в зависимости от набора правил доступа. Триггеры помещают участки кэша в специальную очередь на пересчет прав, которая обсчитывается в фоновом режиме. Для обсчета участков кэша используется генерация хранимых процедур, синтаксис которых зависит от правил доступа.
Четвертый шаг рефакторинга состоял в совершенствовании механизма: сокращении множества значений, хранимых в кэше, и отказ от кэша в частных случаях, когда правила доступа дают однозначный результат (например, администраторы имеют доступ ко всем клиентам).
При помощи такого подхода, обеспечена работоспособность списка записей, содержащего сотни тысяч значений. При этом сохранена гибкость системы – администратор имеет возможность создавать любые правила доступа, зависящие от свойств клиента и пользователя.
В программном интерфейсе PHP была создана «обертка», позволяющая максимально просто проверять права доступа как к набору записей, так и к отдельным клиентам, не задумываясь о физических механизмах реализации контроля прав.

Схема базы данных - система распределения прав доступа

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



  • 1
Это весь текст? Т.е. все что Вы будете рассказывать? Если да, то хотя бы в нескольких словах раскройте значение кэша в MySQL. Я лично очень заинтересовался, и полностью с Вами согласен, сейчас база по учету ресурсов для Интернет абонентов расползлась настолько, что создание нового абонента затягивается на секунд 5 :( хотя конечно ещё сервер старый, но все же, оптимизировать надо, и думаю Ваш способ самый лучший, полезу изучать, просто мне кажется многие этим также заинтересуются, но вот что есть сам по себе кэш в MySQL Вы вообще не раскрыли. Это просто таблица дополнительная, которая формируется с помощью триггеров, или какая то специальная временная таблица? Т.е. это просто первое что пришло ко мне в голову после прочтения, заинтересовывает, но оставляет за собой вопросы.

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

В докладе будет все то же самое, но более развернуто, с картинками.

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

CREATE TABLE `rights_cache` (
`module` int(11) DEFAULT NULL,
`record` int(11) DEFAULT NULL,
`user` int(11) DEFAULT NULL,
`access` int(11) DEFAULT NULL,
KEY `module` (`module`,`record`,`user`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

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

безумно огромное Вам спасибо за этот пост ))) честно говоря до сего дня вообще о триггерах не задумывался. ну есть и есть :) всю логику в коде делал. Проблем не было, но вот со связанными таблицами, в одной более 5000 записей, в другой более 2000. 3,5 секунд, сегодня замерял :) переделал схему по Вашему совету, 0.01секунда ) все ожило ))) ещё раз респектую.
И удачи на докладе, очень актуальная и полезная тема.

Спасибо!
Здорово, я рад, что вам пригодилось.

вот, мой случай во всех подробностях http://mad-ghost.livejournal.com/91531.html

О, мои любимые триггеры и хранимки. :)

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

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

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


  • 1
?

Log in

No account? Create an account