Хранимые процедуры в MySql

Хранимые процедуры в MySql

В этом посте я хочу рассмотреть хранимые процедуры в MySql, а также их выполнение в MySql и PHP. Я не ставлю себе целью охватить все аспекты использования хранимых процедур. В любой момент вы можете обратиться к оффициальной справке.

Как мне кажется большинство читателей очень близко знакомо с созданием приложений баз данных, в частности: созданием базы данных, созданием таблиц, установкой индексов, добавлением CRUD-записей, выполнение запросов с клиентской стороны и дальнейшей их обработкой при необходимости.

Для большинства случаев такой последовательности действий более чем достаточно. Однако здесь не рассмотрен один важный момент, а именно Хранимые процедуры.

Четыре "За" в пользу хранимых процедур

На вскидку, я могу привести, по крайней мере, 4 причины в пользу использования хранимых процедур в приложениях баз данных.

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

  • Клиентский уровень, как правило это веб-браузер. Браузер обеспечивает взаимодействие с пользователем и представляет информацию в пользовательском интерфейсе;
  • Уровень Веб-сервера. Сервер разбирает пользовательские запросы, обрабатывает их и отправляет ответ клиенту;
  • Уровень PHP. Здесь сосредотачивается вся логика приложения, и генерируется ответ клиенты;
  • Уровень базы данных. Данный уровень заведует всеми запросами к базе данных, включая SELECT и INSERT запросы.

Обычно, все уровни размещаются на нескольких машинах, возможно даже в разных сетях (для больших приложений). Таким образом, взаимодействие между уровнями происходит по сети.

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

Во-вторых, использование хранимых процедур способствует увеличению производительности. Хранимая процедура размещается и выполняется на сервере MySql. Она может быть предварительно скомпилирована и проанализирована сервером базы данных. Такое поведение сильно отличается от выполнения запросов с клиентской стороны, когда запрос разбирается, анализируется и оптимизируется при каждом его выполнении. Это сравнимо с поведением интерпретируемых и компилируемых языков программирования. Думаю, каждый знает, что скомпилированная программа работает быстрее.

В-третьих, хранимые процедуры удовлетворяют принципу: Один раз написал - используй везде. SQL является стандартом и на 100% платформонезависим. Его реализация зависит лишь от сервера баз данных. Посмотрите, как много существует различных языков и библиотек, которые можно использовать для работы с базами данных. Вы можете писать одну и ту же логику с использованием этих языков или просто использовать логику, хранимую в базе данных.

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

Если есть "за", то должны быть и "против"

Раз уж я назвал преимущества использования хранимых процедур, то, наверное, стоит упомянуть и о недостатках.

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

Как создать хранимую процедуру?

Так как хранимые процедуры хранятся на сервере, то рекомендуется создавать их непосредственно на сервере, без использования PHP или других языков программирования для выполнения этих задач.

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

Предположим, что имеется следующая таблица:

Теперь создадим пользователя wr.

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

Теперь все готово к тому, чтобы создать хранимую процедуру:

Для выполнения всех указанных выше операций потребуются root права MySql.

После выполнения комманды в MySQL будет создана хранимая процедура avg_sal. Указанная процедура готова к использованию. Она будет возвращать среднюю зарплату по таблице зарплат.

Замечу, что если теперь зарегистрироваться в качестве пользователя wr, таблица salary будет не доступна, но может быть выполнена процедура avg_sal. Последнее замечание, касается безопасности данных. Таким образом, wr не сможет получить каких либо данных из таблицы salary, но сможет выполнить созданную хранимую процедуру.

В результате этих действий будет показана средняя зарплата.

Теперь, когда хранимая процедура создана и готова к работе, можно рассмотреть вопрос о том, как вызвать ее из php.

Как вызвать хранимую процедуру из PHP?

При помощи PDO это можно сделать, например, так:

Теперь содержимое переменной res можно использовать для обработки на php.

Закругляемся

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

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

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

Нет комментариев.
Добавить комментарий
Вам необходимо включить показ изображений в браузере для того чтобы увидеть код

Дайте нам знать, что вы - живой человек. Для нас это важно!
Кликните, если плохо видно

Прежде чем высказать свое мнение, прочитайте пожалуйста: правила

Рейтинг@Mail.ru