Хранимые процедуры MySQL используют их или не используют

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

Мы будем использовать хранимые процедуры только для вставки и обновления сущностей бизнес-модели. Существует несколько таблиц, которые представляют сущность модели, и мы бы абстрагировали ее в этих хранимых процедурах insert/update.

С другой стороны, мы можем вызывать вставку и обновление из слоя Model, но не в MySQL, а в PHP.

По вашему опыту, какой вариант лучше? Преимущества и недостатки обоих подходов. Какой самый быстрый с точки зрения высокой производительности?

PS: Это веб-проект, который в основном читается, а высокая производительность является наиболее важным требованием.

Ответ 1

В отличие от реального кода языка программирования, они:

не переносится (у каждого db есть своя версия PL/SQL. Иногда разные версии одной и той же базы данных несовместимы - я это видел!)
нелегко проверить - вам нужен реальный экземпляр базы данных (dev) для их тестирования, и, таким образом, модульное тестирование их кода как части сборки практически невозможно.
нелегко обновлять/освобождать - вы должны отбросить/создать их, т.е. изменить выпуск db для их выпуска.
не поддерживают библиотеку (зачем писать код, когда кто-то еще)
нелегко интегрировать с другими технологиями (попробуйте вызвать у них веб-сервис).
они используют язык как примитивный, как Fortran, и поэтому неэффективны и трудоемки, чтобы получить полезную кодировку, поэтому сложно выразить бизнес-логику, хотя обычно это и есть их основная цель
не предлагают отладки/трассировки/протоколирования сообщений и т.д. (некоторые dbs могут поддерживать это - я их еще не видел)
Отсутствует достойная среда IDE, чтобы помочь с синтаксисом и привязкой к другим существующим процедурам (например, например, Eclipse для java).
люди, умеющие их кодировать, реже и дороже, чем программы для приложений
их "высокая производительность" - это миф, поскольку они выполняются на сервере базы данных, они обычно увеличивают нагрузку на сервер db, поэтому их использование обычно снижает максимальную пропускную способность транзакций.
неспособность эффективно делиться константами (обычно решается путем создания таблицы и поиска ее из вашей процедуры - очень неэффективно).
и др.

Если у вас есть очень специфичное для базы данных действие (например, действие внутри транзакции для поддержания целостности db) или ваши процедуры очень атомарны и просты, возможно, вы можете их рассмотреть.

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

Используйте хранимые процедуры на свой страх и риск (от того, кто был там и никогда не хочет возвращаться). Моя рекомендация - избегать их, как чумы.

Ответ 2

В отличие от кода программирования, они:

визуализировать SQL-инъекции почти невозможно (если вы не являетесь построение и выполнение динамических
SQL из ваших процедур)
требуется гораздо меньше данных для отправки IPC как часть выноски
включить базу данных намного лучше кэш-планов и наборов результатов (это по общему признанию, не столь эффективен с MySQL из-за его внутреннего кэширования структуры)
легко проверяются изолированно (т.е. не как часть тестов JUnit)
являются переносимыми в том смысле, что они позволяют использовать db-specific функции, отвлеченные за название процедуры (в коде вы застряли с типичным материалом типа SQL)
почти никогда не медленнее, чем SQL вызывается из кода

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

Ответ 3

Что касается характеристик, у них есть потенциал , чтобы быть действительно результативным в будущей версии MySQL (в SQL Server или Oracle, это настоящее удовольствие!). Тем не менее, для всех остальных... Они полностью взорвали конкуренцию. Я подведу итог:

Безопасность: вы можете дать своему приложению EXECUTE только право, все в порядке. Ваш SP будет вставлять выбор обновлений..., без какой-либо возможной утечки. Это означает глобальный контроль над вашей моделью и надежную защиту данных.
Безопасность 2: я знаю, что это редко, но иногда PHP-код протекает с сервера (т.е. становится видимым для общественности). Если он включает ваши запросы, возможные злоумышленники знают вашу модель. Это довольно странно, но я хотел все равно сигнализировать.
Целевая группа: да, создание эффективных SQL-пакетов требует определенных ресурсов, иногда более дорогостоящих. Но если вы считаете, что вам не нужны эти ресурсы только потому, что вы интегрируете свои запросы в своем клиенте... у вас будут серьезные проблемы. Я бы упомянул аналогию с веб-разработкой: хорошо отделить представление от остальных, потому что ваш дизайнер может работать над своей собственной технологией, в то время как программисты могут сосредоточиться на программировании бизнес-уровня.
Инкапсулирующий бизнес-уровень: использование хранимых процедур полностью изолирует бизнес, в котором он находится: база данных damn.
Быстрое тестирование: одна командная строка под вашей оболочкой и ваш код проверен.
Независимость от клиентской технологии: если завтра вы хотите переключиться с php на что-то еще, не проблема. Хорошо, просто сохранение этих SQL в отдельном файле тоже поможет. Кроме того, хороший момент в комментариях о том, если вы решите переключить SQL-движки, вам придется много работать. В любом случае у вас есть все основания для этого, потому что для крупных проектов и крупных компаний это редко случается (из-за затрат и управления персоналом в основном)
Обеспечение гибких 3 + -развитых событий: если ваша база данных находится не на том же сервере, кроме вашего клиентского кода, у вас могут быть разные серверы, но только одна для базы данных. В этом случае вам не нужно обновлять любой из ваших php-серверов, когда вам нужно изменить код, связанный с SQL.

Хорошо, я думаю, что самое важное, что я должен был сказать по этому вопросу. Я развивался в обоих духах (SP vs client), и я действительно очень люблю стиль SP. Я просто хотел, чтобы у Mysql была настоящая IDE для них, потому что сейчас это немного ~~боль в заднице~~ ограничено.

Ответ 4

Сохраненные процедуры удобны в использовании, поскольку они упорядочивают ваши запросы и позволяют выполнять пакет одновременно. Хранимые процедуры обычно выполняются быстро, потому что они предварительно скомпилированы, в отличие от запросов, которые скомпилированы при каждом запуске. Это имеет существенное влияние в ситуациях, когда база данных находится на удаленном сервере; если запросы находятся в PHP script, между приложением и сервером базы данных существует множественная связь - запрос отправляется, выполняется и возвращается. Однако, если использовать хранимые процедуры, ему нужно только отправить небольшую инструкцию CALL вместо больших сложных запросов.

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

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

Ответ 5

Я сообщу свое мнение, несмотря на то, что мои жесты, возможно, напрямую не связаны с вопросом.

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

Что вы хотите получить.

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

Что у вас есть, чтобы получить его.

Какая у вас технология баз данных? Какая инфраструктура? Ваша команда полностью обучена технологии баз данных? Является ли ваша команда более способной создавать решение для создания баз данных?

Время для его получения.

Никаких секретов об этом.

Архитектура.

Требуется ли ваше решение для распределения в нескольких местах? требуется ли ваше решение для использования удаленной связи? ваше решение работает на нескольких серверах баз данных или, возможно, с использованием кластерной архитектуры?

Mainteinance.

Сколько требуется изменить приложение? у вас есть личная подготовка специально для поддержки решения?

Управление изменениями.

Вы видите, что ваша технология базы данных изменится в короткие, средние и длительные периоды времени? вы увидите, что потребуется часто переносить решение?

Стоимость

Сколько будет стоить внедрение этого решения с использованием той или иной стратегии?

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

Использование хранимых процедур имеет тенденцию быть более сложными, если:

Ваша технология базы данных не предоставляется для изменения за короткое время.
Ваша технология базы данных может обрабатывать параллельные операции, таблицы или любую другую стратегию для разделения рабочей нагрузки на несколько процессоров, память и ресурсы (кластеризация, сетка).
Ваша технология базы данных полностью интегрирована с сохраненным языком определения процедуры, то есть поддержка находится внутри механизма базы данных.
У вас есть команда разработчиков, которые не боятся использовать процедурный язык (третий язык поколений) для получения результата.
Операции, которые вы хотите достичь, встроены или поддерживаются внутри базы данных (экспорт в XML-данные, управление целостностью данных и согласованностью с помощью триггеров, запланированных операций и т.д.).
Переносимость - это не важная проблема, и вы не понимаете, какое изменение технологии на короткое время в вашей организации, даже, это нежелательно. Как правило, переносимость рассматривается как веха разработчиков приложений и многоуровневых разработчиков. С моей точки зрения, переносимость не является проблемой, когда ваше приложение не требуется развертывать для нескольких платформ, меньше, если нет причин для изменения технологии, или усилия по миграции всех организационных данных выше, чем выгоду для внесения изменений. То, что вы можете выиграть, используя приложенный на уровне приложений подход (переносимость), вы можете потерять в производительности и ценности, полученные из вашей базы данных (зачем тратить тысячи долларов на то, чтобы получить Ferrari, чтобы вы проехали не более 60 миллиметров в час?).
Производительность - проблема. Во-первых: в нескольких случаях вы можете добиться лучших результатов, используя один вызов хранимой процедуры, чем несколько запросов на данные из другого приложения. Более того, некоторые характеристики, которые вам нужно выполнить, могут быть встроены в вашу базу данных, а ее использование менее дорогое с точки зрения рабочей нагрузки. Когда вы используете решение, основанное на прикладном уровне, вы должны учитывать затраты, связанные с подключением к базе данных, совершать вызовы в базу данных, сетевой трафик, перенос данных (т.е. Используя либо Java, либо .NET, существует неявная стоимость, когда используя вызовы JDBC/ADO.NET, поскольку вы должны обертывать свои данные в объекты, которые представляют данные базы данных, поэтому при создании данных, связанных с обработкой, памятью и сетью, при использовании данных и выходе на внешний ресурс создается соответствующая стоимость.)

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

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

Надеюсь, это поможет любому, кто спрашивает себя, что лучше использовать.

Ответ 6

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

Их язык в MySQL очень дрянной
Невозможно отправить массивы, списки или другие типы данных в хранимую процедуру
Хранимая процедура не может изменить свой интерфейс; MySQL не разрешает ни именованные, ни необязательные параметры
Это упрощает развертывание новых версий вашего приложения - скажем, у вас есть 10-кратные серверы приложений и 2 базы данных, которые вы обновляете в первую очередь?
Вашим разработчикам все нужно изучить и понять язык хранимой процедуры - это очень дерьмо (как я уже упоминал ранее)

Вместо этого я рекомендую создать слой/библиотеку и разместить все ваши запросы там

Вы можете

Обновите эту библиотеку и отправьте ее на свои серверы приложений с помощью своего приложения.
Имеются богатые типы данных, такие как массивы, структуры и т.д.
Unit test эту библиотеку вместо хранимых процедур.

По производительности:

Использование хранимых процедур снизит производительность ваших разработчиков приложений, что является основным, о чем вы заботитесь.
Чрезвычайно сложно определить проблемы с производительностью в сложной хранимой процедуре (это намного проще для простых запросов)
Вы можете отправить пакет запросов в один блок по кабелю (если флаг CLIENT_MULTI_STATEMENTS включен), что означает, что вы не получаете больше латентности без хранимых процедур.
Код на стороне приложения обычно масштабируется лучше, чем код на стороне базы данных

Ответ 7

Если ваша база данных сложна, а не тип форума с ответами, но истинное хранилище SP определенно выиграет. Вы можете использовать всю свою бизнес-логику там, и ни один разработчик не будет заботиться об этом, они просто называют ваши SP. Я делаю это, присоединяясь к более чем 15 таблицам, это не весело, и вы не можете объяснить это новому разработчику.

Разработчики также не имеют доступа к БД, отлично! Оставьте это до разработчиков баз данных и сопровождающих. Если вы также решите, что структура таблицы будет изменена, вы можете скрыть это за своим интерфейсом. n-Tier, помните

Высокопроизводительная и реляционная БД - это не то, что происходит вместе, даже с MySQL InnoDB медленно, MyISAM следует выбросить из окна. Если вам нужна производительность с помощью веб-приложения, вам нужен правильный кеш, memcache или другие.

в вашем случае, потому что вы упомянули "Web", я бы не использовал хранимые процедуры, если бы это был хранилище данных, я бы определенно его рассмотрел (мы используем SP для нашего склада).

Подсказка: Поскольку вы упомянули веб-проект, хотя о каком-то решении nosql? Кроме того, вам нужна быстрая БД, почему бы не использовать PostgreSQL? (пытается защититься здесь...)

Ответ 8

Я использовал MySql, и мое понимание sql в лучшем случае было плохим, я потратил немало времени на использование Sql Server, у меня четкое разделение уровня данных и уровня приложения, в настоящее время я смотрю на сервер с 0,5 терабайта.

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

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

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

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

Мой совет состоит в том, чтобы узнать и понять sql, и ваши приложения действительно принесут пользу.

Ответ 9

Я бы рекомендовал, чтобы вы держались подальше от конкретных хранимых процедур БД.

Я прошел через множество проектов, где они с трудом хотят переключить платформу БД, а код внутри SP обычно не очень переносимый = дополнительная работа и возможные ошибки.

Разработка хранимой процедуры также требует, чтобы разработчик имел доступ непосредственно к SQL-движку, где, когда обычное соединение может быть изменено любым в проекте только с помощью кода доступа.

Относительно идеи модели/уровня/уровня: да, придерживайтесь этого.

Веб-сайты Бизнес-уровень (BL)
BL вызывает слой данных (DL)
DL вызывает любое хранилище (SQL, XML, Webservice, Sockets, Textfiles и т.д.).

Таким образом, вы можете поддерживать логический уровень между уровнями. ЕСЛИ И ТОЛЬКО ЕСЛИ вызовы DL кажутся очень медленными, вы можете начать возиться со Хранимыми процедурами, но где-то поддерживать исходный код не-SP, если вам нужно перевести БД на совершенно новую платформу. Со всем облачным хостингом в бизнесе вы никогда не знаете, что будет следующей платформой DB...

Я внимательно слежу за Amazon AWS по той же причине.

Ответ 10

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

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

Противоположность, в которой я нахожусь, пытаясь быстро выбить решение для веб-приложений, что сводит на нет требования к бизнесу, было очень быстро оставить разработчика (удаленно для меня), чтобы сбить страницы и SQL-запросы, а я определить структуру БД.

Однако сложность растет и без простого способа предоставления API-интерфейсов, я смотрю, как использовать SP, чтобы содержать бизнес-логику. Я думаю, что он работает хорошо и разумно, я контролирую это, потому что я могу построить логику и предоставить простой набор результатов для моего оффшорного разработчика, чтобы построить внешний интерфейс вокруг.

Если я нахожу свое программное обеспечение феноменальным успехом, тогда произойдет большее разделение проблем, и появятся различные реализации n teir, но пока SP отлично.

Вы должны знать, что все доступные вам наборы инструментов и их совпадение разумно начинать. Если вы не строите корпоративную систему, чтобы начать с нее, то лучше и проще.

Ответ 11

Я думаю, что существует много дезинформации о запросах, хранящихся в базе данных.

Я бы порекомендовал использовать хранимые процедуры MySQL, если вы выполняете много статических запросов для манипулирования данными. Особенно, если вы перемещаете вещи из одной таблицы в другую (т.е. переходите от реальной таблицы к исторической таблице по любой причине). Конечно, существуют недостатки, заключающиеся в том, что вам придется вести отдельный журнал изменений к ним (теоретически вы можете создать таблицу, в которой просто хранятся изменения хранимых процедур, которые обновляет администратор БД). Если у вас есть много разных приложений, взаимодействующих с базой данных, особенно если, скажем, у вас есть настольная программа, написанная на С#, и веб-программа на PHP, может быть более выгодно хранить некоторые из ваших процедур в базе данных, поскольку они не зависят от платформы.

Этот сайт содержит некоторую интересную информацию, которую вы можете найти полезной.

https://www.sitepoint.com/stored-procedures-mysql-php/

Как всегда, сначала создайте песочницу и протестируйте.

Ответ 12

Попробуйте обновить 100 000 000 записей в действующей системе из среды, и дайте мне знать, как это происходит. Для небольших приложений SP не обязательны, но для больших серьезных систем они являются реальным преимуществом.