Влияет ли использование подготовленного оператора для предотвращения внедрения SQL на производительность?

Question

По-видимому, лучший способ предотвратить внедрение SQL-кода - использовать подготовленные операторы. Однако подготовленные заявления предназначены для чего-то совсем другого:

В системах управления базами данных подготовленный оператор или параметризованный оператор - это функция, используемая для многократного выполнения одних и тех же или похожих операторов базы данных с высокой эффективностью.
[...]
С другой стороны, если запрос выполняется только один раз, подготовленные операторы на стороне сервера могут быть медленнее из-за дополнительной обратной передачи на сервер. Ограничения реализации также могут привести к снижению производительности: некоторые версии MySQL не кэшируют результаты подготовленных запросов, а некоторые СУБД, такие как PostgreSQL, не выполняют дополнительную оптимизацию запросов во время выполнения.

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

Предполагая, что мы не используем PDO или "эмулированные" подготовленные операторы, имеет ли смысл использовать подготовленные операторы для запросов, которые используются только один раз на странице.

Answer 1

Следующее относится к SQL Server

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

Подготовленный оператор работает хуже, чем специальный запрос

Предположим, у нас есть таблица, содержащая записи за последние 10 лет и 10 возможных статусов. Предположим, что данные распределены равномерно, и у нас есть следующие запросы:

... WHERE date >= '2017-01-01' AND status = 1
... WHERE date >= '2009-01-01' AND status = 1
... WHERE date >= ? AND status = ?

Для первых двух запросов (ad hoc) SQL Server может генерировать отдельные планы выполнения для каждого запроса, например, используя индекс по столбцу даты для первого запроса и используя индекс по столбцу состояния для второго запроса.

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

Специальный запрос работает хуже, чем подготовленный оператор

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

наблюдения

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

Answer 2

Просто личная мысль..

Несмотря на возможные потери производительности для операторов SQL, выполняемых один раз на страницу, я считаю, что это имеет смысл.

Каждое приложение обычно состоит из комбинации операторов SQL. Некоторые исполняются только один раз. Несколько раз. Обычно можно не игнорировать SQL-инъекцию в некоторых местах. Делая все это параметризованным, приложение сохраняет код более согласованным. Возможно, более элегантный (например, если я считаю, апострофы в strings - не нужно избегать их, удваивая их и т. д.). Подготовленные заявления заботятся обо всем этом. Я никогда не измерял потери производительности для параметризованных запросов, но, вероятно, никогда не сосредоточился бы на них в случае каких-либо проблем с производительностью. Если у меня есть проблемы с производительностью, это обычно означает проблему где-то еще (план выполнения, правильные индексы, статистика и т. Д.).

Answer 3

Ответ, безусловно, зависит от того, какую СУБД вы используете.

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

Тем не менее, Oracle (и, возможно, и другие СУБД) различают программный и жесткий синтаксический анализ. Когда Oracle просят подготовить оператор, он сначала вычисляет значение хеш-функции для оператора, которое сравнивается со списком уже подготовленных операторов. Если он найден в этом списке, Oracle знает, что он уже проанализировал инструкцию и не нуждается в ее повторном анализе. Это называется мягким разбором. Если статус действительно новый (так что его нет в этом списке), он должен тщательно проанализировать оператор. Жесткий анализ обычно считается намного более дорогим, чем мягкий анализ.

Таким образом, когда ваше приложение выдает оператор только один раз на страницу, СУБД все равно будет видеть один и тот же оператор несколько раз и сможет мягко анализировать операторы начиная со 2-го и далее. Это даже огромное преимущество перед альтернативой, когда вы используете разные операторы для каждого запроса страницы, потому что в альтернативе каждый оператор должен быть подвергнут жесткому анализу.