Хранимые процедуры MySQL эффективно лечат сонливость

Дата публикации: 2016-06-14

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

От автора: чего это вы спите на рабочем месте! Вы не спите, а ждете, пока СУБД выполнит запрос? Так ее нужно разогнать. Хранимые процедуры MySQL применяли? Не знаете как? Ну, тогда просыпайтесь, потому что сейчас мы будем рассматривать как раз эту тему.

Что за процедуры еще?

Если у вас фобия насчет медицинских процедур, то данные структуры «не из той темы». Так что можно не бояться. А если серьезно, то хранимые процедуры – это удобная и полезная для «здоровья» СУБД вещь. Их еще называют «хранимыми функциями MySQL», но это не совсем точное определение. Хотя давайте разбираться со всем по порядку.

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

В MySQL вызов хранимой процедуры означает, что вписанные в ее код запросы обработаются лишь наполовину. И то, если будет изменены значения их параметров. Но не все так идеально. Опишем сначала положительные стороны использования процедур:

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

Изоляция доступа к данным – все пользователи получают доступ не к строкам таблиц, а только к хранимым процедурам. Что в свою очередь повышает уровень защищенности всех данных.

Повышение скорости работы сервера – за счет кэширования и объединения запросов.

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

Нагрузка на сервер БД повышается – большая часть кода процедур выполняется на стороне сервера. Данная СУБД построена по модели «клиент-сервер», в которой задействованы несколько устройств.

Усложняется манипуляция данными – при разработке приложений придется прописывать часть кода и на клиентской стороне.

Усложняется процесс переноса БД на другие рельсы (СУБД).

Процедуры в phpMyAdmin

Для начала рассмотрим применение в MySQL хранимых процедур на примере phpMyAdmin. Таким образом нам будет легче разобраться с этим типом структур. Стартуем!

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

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

Уже на этом этапе мы знакомимся с особенностями синтаксиса создания хранимых процедур MySQL. В поле «Определение» прописываем тело структуры. Обратите внимание, что выполняемый запрос находится между ключевыми словами BEGIN и END:

BEGIN
SELECT 'HELLO, WORD!';
END

BEGIN

SELECT 'HELLO, WORD!';

END

Данный запрос не выполняет никаких действий с базой, а лишь выводит надпись. Это мы указали в поле «Доступ к SQL данным».

Для окончания создания нашей первой процедуры жмем внизу «Ок». После этого программа выводит «зелененькое» сообщение об удачном выполнении запроса. Его код представлен ниже. В MySQL хранимые процедуры и функции создаются с помощью специальной команды CREATE PROCEDURE. Но об этом позже.

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

Куда смотрел автор данной публикации…! Извините, немного запутался. Ведь автор – это я :). Спокойствие, сейчас все исправим! Такая ошибка возникает из-за того, что в главном конфигурационном файле значение параметра thread_stack оставлено без изменений. По умолчанию для каждого потока выделяется 128 Kb. Выделенного лимита оперативки для выполнения простых запросов вполне хватает, но для процедур мало.

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

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

Обратите внимание, что для вызова используется команда CALL с указанием имени процедуры и принимаемых параметров (в скобках).

Более сложный пример

Но все же phpMyAdmin по своим возможностям больше подходит для быстрого составления процедур. А для разработки в MySQL хранимой процедуры с динамическим количеством аргументов (например) потребуется более удобное ПО. Почему:

phpMyAdmin не хочет нормально «понимать» процедуры, созданные не через специальный конструктор.

Программа не выполняет структуры, запущенные под root и пустым паролем, а в Денвере создать нового пользователя и зайти под ним в phpMyAdmin целая проблема.

Если вы внимательно следите за моими публикациями и выполняете все прописанные в них «пожелания», то у вас должен уже стоять MySQL Administrator. В привязку к нему осталось скачать MySQL Query Browser по этой ссылке. Эти две программы лучше использовать вместе: в первой создавать процедуры, а в другой тестировать их. Поехали:

Запускаем MySQL Administrator.

Авторизуемся.

Слева вверху переходим через вкладку «Catalog».

Выбираем нужную БД, и в верхнем меню жмем на «Stored procedures», а внизу на «Create Stored Proc»

В появившемся окне редактора вводим код процедуры и жмем «Execute SQL».

CREATE DEFINER=`roman`@`localhost` PROCEDURE `proc5`()
BEGIN
declare a int;
set a='SELECT COUNT( * ) FROM  city as a';
if(a > 1000)THEN
SELECT '<1000';
ELSE
SELECT '>1000';
END IF;
END

CREATE DEFINER=`roman`@`localhost` PROCEDURE `proc5`()

BEGIN

declare a int;

set a='SELECT COUNT( * ) FROM city as a';

if(a > 1000)THEN

SELECT '<1000';

ELSE

SELECT '>1000';

END IF;

END

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

Для запуска процедуры заходим в MySQL Query Browser. Сначала вводим свою учетку и пароль, а затем слева в «Object Explorer» находим папку с нужной базой. Остальная очередность действия показана на следующем снимке.

Запуск процедуры в PHP

Теперь рассмотрим, как происходит в PHP вызов хранимой процедуры MySQL. Для этого нам придется немного «перекроить» код нашего предыдущего примера. Мы добавим в процедуру параметр на вывод, а также изменим код запроса:

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc6`(out col decimal)
BEGIN
SELECT COUNT( * ) into col FROM city;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc6`(out col decimal)

BEGIN

SELECT COUNT( * ) into col FROM city;

END

Для вызова из файла PHP процедуры и вывода результата будем использовать возможности класса PDOStatement, созданного специально для работы с БД через SQL

Этот класс реализован сравнительно недавно, и поддерживается PHP, начиная с версии 5.1.0. Советую перед использованием проверить используемую версию языка c помощью встроенной функции phpversion().

<?php
$dbms = 'mysql';
$host = 'localhost';
$db_name = 'world';
$user_name = 'root';
$pass = '';
$str_con = "$dbms:host=$host;dbname=$db_name";
$cn=new PDO($str_con, $user_name, $pass);
$q=$cn->exec('CALL proc6(@var)');
$result=$cn->query('select @var')->fetchColumn();
print($result);
?>

<?php

$dbms = 'mysql';

$host = 'localhost';

$db_name = 'world';

$user_name = 'root';

$pass = '';

$str_con = "$dbms:host=$host;dbname=$db_name";

$cn=new PDO($str_con, $user_name, $pass);

$q=$cn->exec('CALL proc6(@var)');

$result=$cn->query('select @var')->fetchColumn();

print($result);

Для асинхронного вызова хранимой процедуры MySQL лучше использовать возможности расширения MySqli. Но в рамках текущего материала этим мы не будем заниматься. Оставим эту «вкусную» тему на потом. Кстати, а чего это вам теперь на работе не спится? Значит, наша статья помогла, и вы наконец-то разогнали не только MySQL, но и свою дремоту :).

Метки: MySQL