Общий вопрос без конкретного случая – обычно ли предпочитают использовать хранимые процедуры MySQL для написания PHP-скрипта, который выполняет те же вычисления и запросы?
Каковы преимущества каждого метода?
Point / Counter Point с Джеффом Атвудом «Кому нужны хранимые процедуры, в любом случае?» с 2004 года :
1) Хранимые процедуры написаны на больших «базах» базы данных железа, таких как PL / SQL (Oracle) или T-SQL (Microsoft). Эти так называемые языки являются архаичными и полны сумасшедших, бессвязных вариантов дизайна, которые всегда являются результатом мучительной эволюции десятилетней обратной совместимости. Вы действительно не хотите писать много кода в этом материале. Для контекста JavaScript является гигантским шагом вверх от PL / SQL или T-SQL.
Ответ : «S» в «SQL» означает «структурированный», а не «стандартизованный» – PLSQL и TSQL – это как пользовательские расширения SQL, которые также привносят ANSI SQL, потому что очень мало SQL, что является агностиком базы данных. Как правило, если вы хотите, чтобы запрос работал хорошо, вы не можете полагаться на ANSI SQL.
ORM не является серебряной пулей – из-за абстракции базы данных, большинство из которых поддерживают собственные хранимые процедуры / функции, чтобы получить хорошо выполняемый запрос. Что приятно, но совершенно поражает цель ORM …
Я никогда не пойму, почему веб-разработка, мольба бесчисленных технологий (HTML, Javascript / AJAX, Flash …) всегда изолирует SQL как черную овцу семьи. Как и все остальные, вы должны научиться этому извлекать что-то из этого. Должно быть мгновенное удовлетворение, которое вы получаете при использовании других технологий …
2) Хранимые процедуры обычно не могут быть отлажены в той же среде IDE, что и ваш пользовательский интерфейс. Каждый раз, когда я изолирую исключение в procs, я должен остановить то, что делаю, выкинуть свою копию Toad и загрузить пакеты базы данных, чтобы понять, что происходит. Часто переход между двумя совершенно разными IDE, с совершенно разными интерфейсами и языками, не совсем продуктивен.
Ответ . Был ли исходный отладчик Javascript в Eclipse или Visual Studio? Нет, они позволяют плагинам, чтобы вывести продукт из двери и активировать ранее несуществующий рынок. У большинства из них нет проблем с использованием Firebug за пределами Visual Studio / Eclipse, почему SQL-отладка должна отличаться?
3) Хранимые процедуры не дают большой обратной связи, когда все идет не так. Если proc не закодирован во взаимосвязи с необычной обработкой исключений T-SQL или PL / SQL, мы получаем критические «ошибки», возвращенные на основе конкретной строки внутри провала, которые не выполнялись, например в таблице нет строк. Ну, хорошо?
Ответ . Недостаток незнакомого языка – это плохой язык. Как и вам никогда не приходилось искать в Google странную ошибку на выбранном вами языке … По крайней мере, Oracle и MySQL дают вам ссылочные номера ошибок.
4) Хранимые процедуры не могут передавать объекты. Итак, если вы не будете осторожны, вы можете получить миллион параметров. Если вам нужно заполнить строку таблицы 20 + полями с помощью proc, поздоровайтесь с 20 + параметрами. Хуже всего, если я передаю плохой параметр – слишком много, недостаточно или плохие типы данных. Я получаю общую ошибку «плохой вызов». Oracle не может сказать мне, какие параметры ошибочны! Таким образом, я должен определить более 20 параметров, чтобы выяснить, какой из них является виновником.
Ответ : SQL основан на SET, полностью в отличие от процедурного / OO-программирования. Типы близки к объектам, но в какой-то момент должно быть сопоставление между процедурными / OO-объектами и объектами базы данных.
5) Хранимые процедуры скрывают бизнес-логику. Я понятия не имею, что делает proc, или какой курсор (DataSet) или значения он вернется ко мне. Я не могу просмотреть исходный код для proc (по крайней мере, не прибегая к # 2, если у меня есть соответствующий доступ), чтобы убедиться, что он действительно делает то, что я думаю, или то, что дизайнер намеревался сделать. Inline SQL может быть не очень красивым, но, по крайней мере, я вижу его в контексте, наряду с другой бизнес-логикой.
Ответ : Это хорошая вещь ™ – вот как вы получаете Model-View-Controller (MVC), поэтому вы можете иметь интерфейс на любом множестве языков без необходимости дублировать логику каждый раз, когда речь идет о каждом языке, причуды, чтобы воспроизвести эту логику. Или хорошо, что база данных позволяет добавлять плохие данные, если кто-то подключается непосредственно к базе данных? Отправляйтесь назад и вперед между приложением и временем и ресурсами ресурсов базы данных, которые ваше приложение никогда не окупит.
Я думаю, что Джефф Этвуд ударил ногой по голове в 2004 году в отношении сохраненных проков:
Кому нужны хранимые процедуры, в любом случае?
Я использую как хранимые процедуры, так и динамический SQL, я определенно предпочитаю последнее: проще управлять, улучшать инкапсуляцию, нет BL на уровне доступа к данным, большую гибкость и многое другое. Практически каждый крупный PHP-проект с открытым исходным кодом использует динамический SQL для хранимых процессов (см .: Drupal, WordPress, Magento и многие другие).
Этот разговор кажется почти архаичным: получите хороший ОРМ , перестаньте беспокоиться о доступе к данным и приступите к созданию потрясающих приложений.
Хранимая процедура в 99 раз из 100. Если бы я столкнулся с одной причиной, то было бы, если бы ваше веб-приложение php делало все базы данных доступными через хранимые процедуры, и ваш пользователь базы данных имел только право на выполнение указанных хранимых процедур, тогда вы на 100% защищены от SQL-атаки.
Для меня преимущество сохранения каких-либо связей с базой данных в базе данных – отладка. Если у вас есть ваши расчеты (по крайней мере, большинство из них), выполненные в хранимой процедуре, и вам нужно внести изменения, тогда вы просто изменяете их, проверяете, сохраняете. Не будет никаких изменений в вашем PHP-коде.
Если вы храните основные вычисления в своем PHP-коде, вам нужно взять инструкции SQL из кода, очистить его, затем изменить, протестировать, а затем скопировать и снова проверить.
Простота обслуживания приходит на ум с сохранением вещей в отдельности. Код выглядит более чистым, и его легче читать, если вы используете хранимые процедуры, потому что все мы знаем, что SQL-скрипты просто становятся смехотворно большими. Храните всю эту логику базы данных в базе данных.
Если база данных правильно настроена, у вас, вероятно, будет немного быстрее время для выполнения запроса, потому что вместо того, чтобы PHP анализировать строку, затем отправляйте ее в базу данных, затем база данных выполняет ее и отправляет обратно, вы можете просто выталкивать параметры в базу данных с помощью хранимой процедуры, у нее будет хранимый в кэше план выполнения хранимой процедуры, и все будет немного быстрее. Несколько тщательно размещенных индексов могут помочь ускорить получение данных, потому что на самом деле – веб-сервер является просто каналом, а скрипты PHP не загружают его так сильно.
Для нас использование хранимых процедур абсолютно критично. У нас довольно большое приложение .net. Повторное развертывание всего приложения может занять наших пользователей в течение короткого периода времени, который просто не разрешен.
Однако, несмотря на то, что приложение работает, мы иногда должны внести незначительные исправления в наши запросы. Простые вещи, такие как добавление или удаление NOLOCK или, возможно, изменение входящих соединений. Это почти всегда по соображениям производительности. Только сегодня у нас была ошибка, вызванная посторонним NOLOCK. 2 минуты, чтобы найти проблему, определить решение и развернуть новый провал: нулевое время простоя. Чтобы сделать это с запросами в коде, это вызвало бы, по крайней мере, незначительный отрыв, потенциально раздражающий многих людей.
Другая причина – безопасность. С помощью proc мы передаем идентификатор пользователя (не последовательный, недопустимый) в каждый вызов proc. Мы проверяем, что у пользователя есть доступ для запуска этой функции в веб-приложении и снова внутри самой базы данных. Это радикально повышает барьер для хакеров, если наше веб-приложение было скомпрометировано. Мало того, что они не могут запускать любой sql, который они хотят, но даже для запуска proc они должны иметь конкретный ключ авторизации. Что было бы трудно приобрести. (и нет, это не наша единственная защита)
У нас есть контроль над исходным кодом, так что это не проблема. Кроме того, мне не нужно беспокоиться о том, как я называю вещи (некоторые ORM ненавидят определенные схемы именования), и мне не нужно беспокоиться об эффективности полета. Вы должны знать больше, чем просто SQL, чтобы правильно настроить ORM. Вы должны знать особенности поведения ORM.
Я бы сказал: «Не делайте слишком много магии с базой данных». В худшем случае для нового разработчика проекта следует заметить, что ** выполняется операция **, но он не видит, где находится код. Поэтому он продолжает искать его. Но это делается в базе данных.
Поэтому, если вы выполняете некоторые «невидимые» операции с базой данных (я думаю о триггерах), просто напишите их в некоторой документации по коду.
// add a new user $user = new User("john", "doe"); $user->save(); // The id is computed by the database see MYPROC_ID_COMPUTATION print $user->getId(); С другой стороны, функции записи для БД – хорошая идея и обеспечит разработчику хороший уровень абстракции.
// Computes an ID for the given user DB->execute("SELECT COMPUTE_ID(" . $user->getLogin() . ") FROM DUAL");
Конечно, это все псевдокод, но, надеюсь, вы понимаете мою неясную идею.
Ну, есть сторона этого аргумента, которую я очень редко слышу, поэтому я напишу здесь …
Код контролируется версией. Базы данных нет. Поэтому, если у вас более одного экземпляра вашего кода, вам понадобится какой-то способ выполнения миграции автоматически при обновлении или вы рискуете сломать вещи. И даже при этом вы по-прежнему сталкиваетесь с проблемами «забывания», чтобы добавить обновленный SP к сценарию миграции, а затем взломать сборку (возможно, даже не понимая ее, если вы не тестируете ДЕЙСТВИТЕЛЬНО idepth).
От отладки и обслуживания я нахожу SP 100x настолько же сложным, как и необработанный SQL. Причина в том, что для этого требуется как минимум три шага. Во-первых, посмотрите в PHP-код, чтобы узнать, какой код вызывается. Затем зайдите в базу данных и найдите эту процедуру. Затем, наконец, посмотрите на код процедуры.
Другой аргумент (в соответствии с контролем версий) заключается в том, что для SP нет команды svn st . Поэтому, если вы получаете разработчика, который вручную модифицирует SP, вам придется черпать время, понимая это (если они не все управляются одним администратором баз данных).
Где действительно сияет SP, когда у вас несколько приложений, работающих с одной и той же схемой базы данных. Затем у вас есть только одно место, где хранятся DDL и DML, и оба приложения могут делиться им, не добавляя кросс-зависимость в одну или несколько библиотек.
Итак, вкратце, мое мнение таково:
Использовать хранимые процедуры:
Используйте необработанный SQL / ORM / сгенерированный SQL в любом другом случае (примерно так, поскольку обязательно будут случаи кросс, о которых я не думаю)
Опять же, это всего лишь мои 0,02 доллара …
Я использую хранимые процедуры как можно больше по ряду причин.
Сократить круглые поездки в базу данных
Если вам нужно сразу изменить несколько связанных таблиц, вы можете использовать одну хранимую процедуру, чтобы сделать только один вызов в базе данных.
Четко определить бизнес-логику
Если определенные вещи должны быть истинными в отношении запроса, то процедура хранения позволяет тому, кто знает SQL (довольно простой язык), гарантирует, что все будет сделано правильно.
Создание простых интерфейсов для других программистов
Ваши компетентные товарищи по команде, отличные от sql, могут использовать гораздо более простые интерфейсы для базы данных, и вы можете быть уверены, что не могут установить отношения в плохом состоянии при аварии.
Рассматривать:
SELECT a.first_name, IFNULL( b.plan_id, 0 ) AS plan_id FROM account AS a LEFT JOIN subscription AS s ON s.account_id = a.id WHERE a.id = 23
В сравнении с:
CALL account_get_current_plan_id( 23 );
Напиши им симпатичную маленькую упаковку, чтобы заботиться о том, как обращаться с вызовами хранимых процедур, и они находятся в бизнесе.
Обновлять все обычаи в системе сразу
Если каждый использует хранимые процедуры для запроса базы данных, и вам нужно изменить то, как что-то работает, вы можете обновить хранимую процедуру, и она обновляется везде, пока вы не меняете интерфейс.
Принудительная безопасность
Если вы можете использовать только хранимые процедуры, чтобы делать все в своей системе, вы можете предоставить серьезно ограниченные разрешения учетной записи пользователя, которая обращается к данным. Не нужно давать им разрешения UPDATE, DELETE или даже SELECT.
Легкая обработка ошибок
Многие люди этого не осознают, но вы можете создавать свои хранимые процедуры таким образом, чтобы отслеживать большинство проблем стало очень легко.
Вы даже можете интегрировать свою базу кода, чтобы правильно обрабатывать возвращаемые ошибки, если используете хорошую структуру.
Вот пример, который делает следующее:
Вот внутренняя часть созданной хранимой процедуры, которая принимает идентификатор учетной записи, идентификатор закрывающей учетной записи и IP-адрес, а затем использует их для обновления соответствующим образом. Разделитель уже установлен в $$:
BEGIN # Helper variables DECLARE r_code INT UNSIGNED; DECLARE r_message VARCHAR(128); DECLARE it_exists INT UNSIGNED; DECLARE n_affected INT UNSIGNED; # Exception handler - for when you have written bad code # - or something really bad happens to the server DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 0 as `id`, 10001 as `code`, CONCAT(r_message, ' Failed with exception') as `message`; END; # Warning handler - to tell you exactly where problems are DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN SET r_code = 20001, r_message = CONCAT( r_message, 'WARNING' ); END; SET r_code = 0, r_message = '', it_exists = 0, n_affected = 0; # STEP 1 - Obvious basic sanity checking (no table scans needed) IF ( 0 = i_account_id ) THEN SET r_code = 40001, r_message = 'You must specify an account to close'; ELSEIF ( 0 = i_updated_by_id ) THEN SET r_code = 40002, r_message = 'You must specify the account doing the closing'; END IF; # STEP 2 - Any checks requiring table scans # Given account must exist in system IF ( 0 = r_code ) THEN SELECT COUNT(id) INTO it_exists FROM account WHERE id = i_account_id; IF ( 0 = it_exists ) THEN SET r_code = 40001, r_message = 'Account to close does not exist in the system'; END IF; END IF; # Given account must not already be closed # - if already closed, we simply treat the call as a success # - and don't bother with further processing IF ( 0 = r_code ) THEN SELECT COUNT(id) INTO it_exists FROM account WHERE id = i_account_id AND status_id = 2; IF ( 0 < it_exists ) THEN SET r_code = 1, r_message = 'already closed'; END IF; END IF; # Given closer account must be valid IF ( 0 = r_code ) THEN SELECT COUNT(id) INTO it_exists FROM account WHERE id = i_updated_by_id; END IF; # STEP 3 - The actual update and related updates # r-message stages are used in case of warnings to tell exactly where a problem occurred IF ( 0 = r_code ) THEN SET r_message = CONCAT(r_message, 'a'); START TRANSACTION; # Add the unmodified account record to our log INSERT INTO account_log ( field_list ) SELECT field_list FROM account WHERE id = i_account_id; IF ( 0 = r_code ) THEN SET n_affected = ROW_COUNT(); IF ( 0 = n_affected ) THEN SET r_code = 20002, r_message = 'Failed to create account log record'; END IF; END IF; # Update the account now that we have backed it up IF ( 0 = r_code ) THEN SET r_message = CONCAT( r_message, 'b' ); UPDATE account SET status_id = 2, updated_by_id = i_updated_by_id, updated_by_ip = i_updated_by_ip WHERE id = i_account_id; IF ( 0 = r_code ) THEN SET n_affected = ROW_COUNT(); IF ( 0 = n_affected ) THEN SET r_code = 20003, r_message = 'Failed to update account status'; END IF; END IF; END IF; # Delete some related data IF ( 0 = r_code ) THEN SET r_message = CONCAT( r_message, 'c' ); DELETE FROM something WHERE account_id = i_account_id; END IF; # Commit or roll back our transaction based on our current code IF ( 0 = r_code ) THEN SET r_code = 1, r_message = 'success'; COMMIT; ELSE ROLLBACK; END IF; END IF; SELECT r_code as `code`, r_message as `message`, n_affected as `affected`; END$$
Значения кода состояния:
Вместо того, чтобы доверять программистам, которые не знакомы с базами данных (или просто не знакомы с этой схемой), гораздо проще предоставить им интерфейсы.
Вместо выполнения всех вышеуказанных проверок они могут просто использовать:
CALL account_close_by_id( 23 );
Затем проверьте код результата и выполните соответствующие действия.
Лично я считаю, что если у вас есть доступ к хранимым процедурам, и вы их не используете, вы действительно должны изучить их.
Возможно, что это возможно, конечный пользователь получит выгоду от абстракции данных из пользовательского интерфейса. Поэтому вы должны стараться максимально эффективно использовать хранимые процедуры.
Вам не обязательно нужны базовые значения, если вычисления выполняются в базе данных, а затем дайте базе данных их выполнить. Это позволяет свести к минимуму объем передачи данных между базой данных PHP-скриптом; но в целом вычисления с данными базы данных лучше всего выполнять сама база данных.
Я слышал, как люди говорят «дайте базе данных сделать как можно больше», а другие плакали, как «wtf, что вы делаете с моей базой данных».
Поэтому я предполагаю, что это должно быть главным образом решением скорости использования (хранимые процедуры будут подчеркивать, что процесс MySQL и PHP-код будут подчеркивать процесс веб-сервера).