9 июня(суббота) 2012 Москва

Телефон: +7 (495) 502-33-78
E-mail: 2012@devconf.ru

Архив 2012 года - актуальная информация тут

Реализация элементов логики приложения в MySQL: триггеры, хранимые процедуры, кэширование.

Сергей Горшков, технический директор Центра информационных технологий index.art. Автор платформы index.CRM, руководитель нескольких десятков проектов по ее внедрению.

Доклад(30 мин)    Презентация (ppt, 1.3 Mb)

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

Подробно:
Реализация элементов логики приложения в MySQL:
триггеры, хранимые процедуры, кэширование


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



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



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

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

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

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

На втором шаге рефакторинга мы пришли к генерации хранимой процедуры в базе данных, которая получала в качестве параметров идентификаторы клиента и пользователя, и возвращала права доступа. Для этого в PHP была реализована генерация текста этой функции; функция пересоздавалась каждый раз при изменении правил доступа. Обращение к функции можно было включить в условие WHERE запроса к таблице «клиенты». Такой вариант работал намного быстрее двух предыдущих, и позволил обеспечить нормальную работу списка из тысяч записей. Однако, для практических целей этого было недостаточно.

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

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

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

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



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




Программа конференции