🐘 PostgreSQL, PHP и подготовленные запросы
Одно из отличий «Постгреса» от «Оракла» — подготовленные планы запросов лежат не в разделяемом ресурсе, а в неком хранилище в рамках одного соединения. Программист, в рамках каждого соединения, должен подготовить их оператором PREPARE, в который передаётся выбранный идентификатор запроса и сам запрос, а потом выполнить его при помощи оператора EXECUTE, используя в качестве параметра переданный при подготовке идентификатор и значения параметров.
Польза подготовленных запросов в том, что можно единожды (за время жизни соединения, напоминаю) подготовить запрос, то есть попросить «Постгрес» выполнить его компиляцию в стандартное представление и оптимизацию, а после многократно его использовать, что очевидным образом поднимает производительность приложения.
Схема работы тут очень простая: вычисляем от запроса хеш, ищем его в специальном массиве, если не находим, подготавливаем запрос, используя хеш в качестве идентификатора, после чего хеш добавляем в специальный массив. После чего выполняем подготовленный запрос.
Если план запроса больше не нужен, его можно удалить по идентификатору оператором DEALLOCATE, если же в качестве параметра передать ключевое слово ALL, то будут уничтожены все подготовленные в этом соединении планы. С закрытием соединения, планы так же исчезают.
Теме работы «Постргеса» с ПХП удивительно мало посвящено в руководстве к языку, так что пришлось произвести ряд самостоятельных исследований. Основной вопрос, на который я хотел для себя ответить — есть ли какая-то долгосрочная польза от подготовленных запросов в разрезе того, что обычная модель работы с ПХП — полная смерть скрипта после обработки запроса от клиента.
Оказалось, что это вполне возможно. В ПХП есть «постоянные соединения» — обрабатывающий процесс интерпретатора держит открытым соединение с базой, передавая его новому скрипту, это уменьшает затраты, так как стадия установки соединения и авторизации выполняется только в первый раз.
С открытым соединением в скрипт попадают и подготовленные запросы из предыдущего сеанса, остаётся только как-то получить их имена, но с этим в «Постгресе» проблем нет — они могут быть получены из специальной таблицы.
Код всего описанного выглядит вот так, я его упростил и переписал процедурно:
// открываем постоянное соединение с базой
$con = pg_pconnect($connection_string);
// загружаем подготовленные запросы
$res = pg_query("SELECT name FROM pg_prepared_statements");
$prepared = array_fill_keys(pg_fetch_all_columns($res), true);
pg_free_result($res);
// хеш от запроса, tiger — быстрый алгоритм, я люблю его использовать
$hash = base64_encode(hash('tiger160,3', $sql, true));
// смотрим — был ли уже подготовлен такой запрос
if (!isset($prepared[$hash])) {
$prepared[$hash] = true;
pg_prepare($con, $hash, $sql);
}
// выполняем запрос
$res = pg_execute($con, $hash, $params);
Общая идея, думаю, понятна из кода. Пока для меня остаётся открытым вопрос «просачиваются» ли в переданное соединение незакрытые транзакции или временные таблицы (предыдущий его владелец, например, мог умереть из-за нехватки памяти), эксперименты я ещё не проводил, но, думаю, да.
Надо заметить, что модуль «Постгреса» в ПХП в каждое соединение, перед его передачей в приложение, посылает команду RESET ALL, но она, насколько я понял из руководства, лишь сбрасывает различные конфигурационные переметры времени выполнения на значения по-умолчанию и не трогает транзакции и всё остальное (более того, она сама транзакционна).
И что, вот так выбирать prepared statement-ы действительно выгодно в плане производительности?
Комментарий для Александр Макаров:
Уточните вопрос, вы имеете ввиду вот эту строку что ли?
А что вас смущает? Вы считаете, что этот время выполнения этого запроса может перевесить пользу от подготовленных запросов? Если бы польза от них была столь незначительна, то и не имело бы смысла вообще с ними заморачиваться.
FYI Недавно на хабре статья была про отличия реализации prepared statements в oracle и postgres — https://habrahabr.ru/company/postgrespro/blog/275755
Комментарий для Alex Crown:
Спасибо, судя по выводам статьи, там рассказывается о той же разнице, о которой я рассказал в первом абзаце, только длиннее.
Я практически все запросы в хранимые процедуры превращаю. Перейти от параметризированного запроса к ХП достаточно просто, а plpgsql хранит все запросы как prepared.
Комментарий для Vladimir:
Если бы IDE для баз данных не застряли в 20-м веке, это было бы для нас более чем приемлемо, многие запросы именно туда и уехали бы. А так — никакой пошаговой отладки, интеграции с системой контроля версий, инспекции, ничего.
Комментарий для Евгения Степанищева:
Согласен, предоставляемые БД средства разработки сильно отстают от таковых по индустрии.
Поэтому ХП храним в .sql-файлах рядом с кодом, отлаживаем через raise notice и мечтаем о нормальной структуре типов в plpgsql.