Интернет, компьютеры, софт и прочий Hi-Tech

Подписаться через RSS2Email.ru

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

Тэйлор Рен (Taylor Ren), 03.01.2014

Проще говоря, хранимые процедуры («ХП») — это сохраненные в базе данных процедуры (написанные с помощью SQL и других управляющих операторов), которые могут быть выполнены движком баз данных и вызваны из программного кода, который с этим движком работает.

В этой статье мы рассмотрим создание ХП в MySQL и их выполнение на сервере MySQL из кода PHP.

Замечание: мы не собираемся раскрывать здесь все вопросы, связанные с ХП. В качестве полного справочника следует использовать официальную документацию по MySQL.

ХП доступны и в других распространенных СУБД (например, в Postgre). Поэтому то, что мы здесь обсудим, поможет лучше понять работу с базами данных в целом.

Зачем нужны хранимые процедуры

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

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

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

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

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

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

Хотя скорость сети сильно выросла за последние несколько лет, сеть все равно остается самой медленной и ненадежной по сравнению с другими способами передачи данных (кэш CPU, память, жесткий диск и т.п.). Поэтому, для сохранения пропускной способности сети и повышения надежности приложения иногда желательно перенести часть логики на сервер (в данном случае на сервер MySQL) с тем, чтобы гонять по сети меньше данных.

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

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

И, наконец, ХП являются фундаментальным аспектом безопасности баз данных.

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

Мы знаем, что MySQL поддерживает исчерпывающий контроль доступа. В нашем случае мы не можем дать привилегию SELECT нашему сотруднику отдела кадров, так как в противном случае он сможет видеть все зарплаты. Но если он не будет иметь доступа к таблице salary, то как он сможет получать статистическую информацию, связанную с salary? Как нам разрешить ему получать эту информацию не нарушая собственную кадровую политику?

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

ХП становится мостом, связывающим пользователя (нашего служащего отдела кадров) и таблицу (salary), к которой этот пользователь не имеет непосредственного доступа.

Мост ХП

Вот так! С помощью ХП мы можем разрешить пользователю выполнить свою задачу сохранив при этом безопасность нашей базы (и кадровую политику)!

Недостатки использования хранимых процедур

После перечисления всех преимуществ ХП мы ради объективности должны указать на их недостатки. А также рассмотрим способы их преодоления.

  • ХП не предусматривает никакого контроля версий.

    Когда она изменяется, на серверной стороне никакая история не сохраняется. Это может создать проблемы, когда пользователь хочет откатить изменения. Я предлагаю писать ХП на клиентской стороне под управлением контроля версий. Когда она будет закончена, ее код можно будет легко скопировать, например, в MySQL Workbench и создать ее на стороне сервера. Делая так, мы в какой-то степени управляем ее версиями.

  • Есть проблемы с «синхронизацией» примененных изменений для использования в новых версиях.

    В частности, когда каждый член команды разработчиков имеет собственную базу для разработки и тестирования. Контроль версий может помочь, но для обновления локальной копии ХП на локальном сервере все равно требуется ручное вмешательство.

    Можно также использовать «мокинг». Команда выделяет единственного человека, который поддерживает ХП и реализует ее вызов в коде. Все остальные, если им нужны результаты вызова этой ХП, могут разрабатывать и тестировать свой код с помощью мок-объектов, т.е. с помощью «фальшивых» вызовов ХП, которые должны возвращать условный результат. На последней стадии код объединяется и мок-объекты заменяются вызовами реальной ХП.

  • Сложность бэкапа и экспорта.

    ХП существуют на стороне сервера. Разработчики обычно имеют только базовые привилегии (SELECT, EXECUTE и т.д.) и не имеют админских прав для бэкапа и экспорта.

    Я не могу назвать такое положение вещей недостатком. Это, скорее, фундаментальный аспект безопасности БД. Обходить это ограничение крайне нежелательно. Поэтому можно выделить в команде одного администратора БД, на которого и возложить работы по регулярному бэкапу базы, ее экспорту и импорту.

Продолжение этой статьи: «Хранимые процедуры в MySQL и PHP. Часть 2».

Автор: Тэйлор Рен, 03.01.2014
Перевод с английского: Дмитрий Скоробогатов, специально для xBB.uz, 10.03.2014
Оригинальный текст может быть найден по адресу http://www.sitepoint.com/stored-procedures-mysql-php/

Тэйлор Рен

Тэйлор — свободный разработчик веб- и десктопных приложений, проживающий в Сужоу в восточном Китае. Начинал со средств разработки Borland (C++Builder, Delphi), опубликовал книгу по InterBase. С 2003 является сертифицированным экспертом Borland. Затем переключился на веб-разработку в типичной конфигурации LAMP. Позднее начал работать с jQuery, Symfony, Bootstrap, Dart и т.д.


Предыдущие публикации:

Биржа долевых инвестиций SIMEX.

Последнее редактирование: 2014-03-12 05:33:30

Метки материала: db, mysql, базы данных, php, процедуры, базы данных mysql, хп, sql, хранимые процедуры, бд, программирование, mysql и php, базы

Оставьте, пожалуйста, свой комментарий к публикации

Представиться как     Антибот:
   

Просьба не постить мусор. Если вы хотите потестить xBB, воспользуйтесь кнопкой предварительного просмотра на панели инструментов xBBEditor-а.


© 2007-2017, Дмитрий Скоробогатов.
Разрешается воспроизводить, распространять и/или изменять материалы сайта
в соответствии с условиями GNU Free Documentation License,
версии 1.2 или любой более поздней версии, опубликованной FSF,
если только иное не указано в самих материалах.