Збережені процедури MySQL ефективно лікують сонливість

11

Від автора: чого це ви спите на робочому місці! Ви не спите, а чекаєте, поки СУБД виконає запит? Так її потрібно розігнати. Збережені процедури MySQL застосовували? Не знаєте як? Ну, прокидайтеся, тому що зараз ми будемо розглядати як раз цю тему.

Що за процедури ще?

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

Збережені процедури дозволяють значно оптимізувати роботу сервера і підвищити швидкість її роботи, оскільки їх код після першого виконання зберігається в кеші оперативки. При всіх наступних викликах процедура буде вилучатись з кеша, а не вирушати знову на виконання.

В MySQL виклик збереженої процедури означає, що вписані в її код запити обработаются лише наполовину. І то, якщо буде змінено значення їх параметрів. Але не все так ідеально. Опишемо спочатку позитивні сторони використання процедур:

Інкапсуляція функціональності – весь код зберігається в одному місці, що полегшує доступ до нього для інших додатків. У цьому збережені процедури схожі з програмними функціями.

Ізоляція доступу до даних – всі користувачі отримують доступ до рядків таблиці, а тільки до збережених процедур. Що в свою чергу підвищує рівень захищеності всіх даних.

Підвищення швидкості роботи сервера – за рахунок кешування і об’єднання запитів.

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

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

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

Ускладнюється процес перенесення БД на інші рейки (СУБД).

Процедури в phpMyAdmin

Для початку розглянемо застосування в MySQL збережених процедур на прикладі phpMyAdmin. Таким чином, нам буде легше розібратися з цим типом структур. Стартуємо!

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

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

Збережені процедури MySQL ефективно лікують сонливість

Вже на цьому етапі ми знайомимося з особливостями синтаксису створення збережених процедур MySQL. У полі «Визначення» прописуємо тіло структури. Зверніть увагу, що виконується запит знаходиться між ключовими словами BEGIN та END:

BEGIN
SELECT ‘HELLO WORD!’;
END

Даний запит не виконує ніяких дій з базою, а лише виводить напис. Це ми вказали в полі «Доступ до SQL даними».

Для закінчення створення нашої першої процедури тиснемо внизу «Ок». Після цього програма виводить «зелененькі» повідомлення про вдале виконання запиту. Код представлений нижче. В MySQL збережені процедури і функції створюються за допомогою спеціальної команди CREATE PROCEDURE. Але про це пізніше.

Збережені процедури MySQL ефективно лікують сонливість

Тепер запустимо створену структуру на виконання. Для цього в розділі «Процедури» натискаємо кнопку «Виконати». Але що це за неподобство! Куди подівся наш улюблений «зелененький»? Чому програма «лається» і «кричить», що їй не вистачає виділеної пам’яті?

Збережені процедури MySQL ефективно лікують сонливість

Куди дивився автор даної публікації…! Вибачте, трохи заплутався. Адже автор – це я Збережені процедури MySQL ефективно лікують сонливість . Спокій, зараз все виправимо! Така помилка виникає із-за того, що у головному конфігураційному файлі значення параметра thread_stack залишено без змін. За замовчуванням для кожного потоку виділяється 128 Kb. Виділеного ліміту оперативки для виконання простих запитів цілком вистачає, але для процедур мало.

Це ще раз доводить, що на виконання тригерів і збережених процедур в MySQL витрачається більше ресурсів.

Збережені процедури MySQL ефективно лікують сонливість

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

Збережені процедури MySQL ефективно лікують сонливість

Зверніть увагу, що для виклику використовується команда CALL із зазначенням імені процедури та прийнятих параметрів (у дужках).

Більш складний приклад

Але все ж phpMyAdmin по своїм можливостям більше підходить для швидкого складання процедур. А для розробки в MySQL збереженої процедури з динамічним кількістю аргументів (наприклад) буде потрібно більш зручне. Чому:

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

Програма не виконує структури, запущені під root і порожнім паролем, а в Денвері створити нового користувача і зайти під ним в phpMyAdmin ціла проблема.

Якщо ви уважно слідкуєте за моїми публікаціями і виконуєте всі прописані в них «побажання», то у вас повинен стояти MySQL Administrator. У прив’язку до нього залишилося скачати MySQL Query Browser за цим посиланням. Ці дві програми краще використовувати разом: у першій створювати процедури, а в іншій тестувати їх. Поїхали:

Запускаємо MySQL Administrator.

Авторізуємось.

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

Збережені процедури MySQL ефективно лікують сонливість

Вибираємо потрібну БД, і у верхньому меню тиснемо на «Stored procedures», а внизу на «Create Stored Proc»

Збережені процедури MySQL ефективно лікують сонливість

У вікні редактора вводимо код процедури і тиснемо «Execute SQL».

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

Збережені процедури MySQL ефективно лікують сонливість

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

Для запуску процедури заходимо в MySQL Query Browser. Спочатку вводимо свою учетку і пароль, а потім ліворуч у «Object Explorer» знаходимо папку з потрібною базою. Інша черговість дії показана на наступному знімку.

Збережені процедури MySQL ефективно лікують сонливість

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

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

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().

Збережені процедури MySQL ефективно лікують сонливість

exec(‘CALL proc6(@var)’);
$result=$cn->query(‘select @var’)->fetchColumn();
print($result);
?>

Збережені процедури MySQL ефективно лікують сонливість

Для асинхронного виклику збережених процедур MySQL краще використовувати можливості розширення MySqli. Але в рамках цього матеріалу цим ми не будемо займатися. Залишимо цю «смачну» тему на потім. До речі, а чого це вам тепер на роботі не спиться? Значить, наша стаття допомогла, і ви нарешті розігнали не тільки MySQL, але і свою дрімоту Збережені процедури MySQL ефективно лікують сонливість .