CC BY
3УДК 004.65 Яковлев М.В., Обломов И.А.
Яковлев М.В.
магистрант 2 курса факультета информатики и вычислительной техники Чувашский государственный университет им. И.Н. Ульянова
(г. Чебоксары, Россия)
Обломов И.А.
канд. техн. наук, доцент кафедры вычислительной техники Чувашский государственный университет им. И.Н. Ульянова
(г. Чебоксары, Россия)
ИССЛЕДОВАНИЕ СПОСОБОВ ОПТИМИЗАЦИИ КОДА ПРИ ПЕРЕВОДЕ ХРАНИМЫХ ПРОЦЕДУР НА POSTGRESQL В РАМКАХ ИМПОРТОЗАМЕЩЕНИЯ
Аннотация: в работе представлен обзор некоторых способов оптимизации SQL кода, которые будут полезны при переводе хранимых процедур на PostgreSQL. Статья включает обзор типовых ошибок, допускаемых разработчиками, и вариантов их устранения при создании SQL запросов по импортозамещению. Представлены примеры, подтверждающие необходимость замены существующих алгоритмов.
Ключевые слова: оптимизация, SQL запросы, импортозамещение, СУБД, базы данных, хранимые процедуры.
На сегодняшний день всё больше компаний переходят на отечественное программное обеспечение. Это связано как с политическими изменениями, так и с санкциями, введенными по отношению к Российской Федерации. Такой переход называется импортозамещением. Одним из мероприятий по импортозамещению является переход от импортных систем управления базами данных (СУБД) на СУБД с открытым исходным кодом, которое позволит обеспечить безопасность и стабильность баз данных (БД). В состав
1653
реляционной базы данных входят не только таблицы с данными, а еще и хранимые процедуры - объекты БД, содержащие набор SQL-инструкций и выполняющие аналогичные функции как в других языках программирования. Рассмотрим наиболее полезные приёмы оптимизации кода в хранимых процедурах на PostgreSQL, обнаруженные методами анализа, экспериментов и сравнения в процессе импортозамещения хранимых процедур в базе данных.
По возможности стоит использовать тип «date» вместо «timestamptz». Тип «date» занимает 4 байта фиксированного размера и не содержит информации о времени суток и часовых поясах. Тип данных «timestamptz» занимает 8 байт и хранит значения даты и времени с точностью до микросекунды и обеспечивает корректную работу с данными в различных часовых поясах.
Создание скалярных INLINE функций, подставляющих максимальную (минимальную) дату, позволит унифицировать подстановку конкретных значений, что позволит исключить ошибки, когда при ручном заполнении будет введено 2009 вместо 2079. Сами значения выбраны из типа «smalldatetme» в Microsoft SQL Server, который имеет именно такие границы значений. Ниже приведен код функции подставляющей максимальное значение даты: CREATE OR REPLACE FUNCTIONglobals.max date () RETURNS date LANGUAGE 'sql' AS $$ SELECT
'20790606': :date
$$,
Тогда вместо COALESCE (t.d_date1, '20790606') нужно использовать COALESCE (t.d_date1, globals.max_date())
Объявлять переменные следует через колоночные типы. Например: DECLARE CCash dbo.t sale items. link%type, к преимуществам данного решения относятся:
1654
-невозможно ошибиться с типом, -не нужно вспоминать тип колонки,
-если тип переменной будет отличен от типа колонки, то в запросе будет неявное приведение типов, что существенно замедлит запрос,
-если будет меняться тип колонки (тип «int» поменяется на «smallmt»), то не придется менять типы переменных повсюду.
При инициализации переменной из какого-либо справочника и если она точно должна быть присвоена, то используем «STRICT» присвоение. Часто бывает так, что при переносе процедуры, функции или операции на другую базу, там отсутствуют необходимые значения в каких-то справочниках. Тогда при выполнении этой процедуры, функции или операции немедленно возникает критическая ошибка с указанием переменной, где не выполнилось присвоение и можно сразу найти источник ошибки. Если для нашей логики корректно «NULL» значение, то «STRICT» указывать не нужно.
Коррелирующие подзапросы с TOP/LIMIT 1 лучше переделать на LATERAL. Тогда имеем в два раза меньше логических чтений, а значит сокращение времени выполнения запроса. Также исключается вероятность ошибки, когда условия соединения отличаются во внутреннем и внешнем запросе.
При соединении или фильтрации к секционированным таблицам по ключу секционирования поля нужно указывать как константу к этим таблицам, тогда отбрасывание лишних секций произойдет на этапе разбора запроса, а не планирования, что существенно увеличит скорость выполнения запроса.
Если скалярная функция возвращает одно значение определенного типа, то следует сделать её INLINE. Такая функция уже на этапе разбора запроса «встраивается» в сам запрос. Тогда не возникает накладных расходов на работу со стеком вызова функций, на порождение framed и так далее.
Табличную функцию также можно сделать INLINE. При использовании в запросе табличной функции, которая не умеет «Г^ШЕ'иться», она будет выполнена полностью изолировано, её результат будет помещен в рабочую
1655
память и затем уже этот результат будет «присоединен» к основному запросу, который может состоять из множества фильтраций и соединений. Таким образом, оптимизатор планов не будет иметь никакой возможности как-то повлиять на выполнение этой функции, так как она будет выглядеть как «черный ящик», из которого выходят данные. Если функция написана корректно, то оптимизатор будет иметь возможность пробросить внутрь какие-то фильтры, условия соединений, вытащить статистику таблиц, используемых в функции, либо вообще переставить местами порядок соединений - иными словами, тело функции станет просто частью самого запроса. Ниже приведён запрос с вызовом INLINE функции: EXPLAINANALYZE VERBOSE SELECT
dbo.cmonthdateendn _period(t.n _period), t.n _period
FROM pe.fdpayments t
WHERE fdivision = 1 AND sarchive = 0
LIMIT 500,
На рисунке 1 показан план выполнения запроса, где видно «тело» функции, а не вызов напрямую.
1 Lira it (cost =0.00, .43.37 rows=503 width=12) (actual time=0 .029. .1 .514 rows=50B loops=1)
2 "Output: {((to_tin>estamp((({ (t.n_period / 1B0))::text || lpad( ([t .ri.period % 100})::text,
3 Seq Scan on pe . fd_payments_d1_o t (cost =0.00. .33775.40 row3-389469 width=12) i actual
4 "Output: {(to_timestarop{({((t,n_period I 100}}::text || lpadj((t.n_period % 1BB)}::text.
Рисунок. 1. План выполнения запроса с вызовом INLINE функции.
При переводе хранимых процедур в рамках импортозамещения разработчиками могут допускаться следующие виды ошибок: создание реальных таблиц для временных нужд, изменение типов параметров функции,
1656
указание лишних условий в соединениях и фильтрациях, применение каскадных (вложенных) соединений без прямой необходимости. Приведём обоснование необходимости замены этих алгоритмов.
Применение временных таблиц вместо реальных даёт ряд преимуществ:
- нет блокировок, так как мы единственные пользователи таблицы,
- получение гарантированно пустой таблицы (если иное не предусмотрено логикой) - вследствие чего вставки в таблицу происходят быстрее,
- отсутствует расширение таблицы, за счет ненужных строк, ввиду постоянных модификаций.
Если не требуется сохранять данные между сессиями, то использование временных таблиц будет оптимальным решением. Создание временной таблицы может быть осуществлено следующим SQL кодом: CREATE TEMP TABLE temptable (...) ON COMMIT DROP.
Особое внимание стоит уделить при изменении одного или нескольких параметров функции, удалении или добавлении их. В этом случае при компиляции создается новая копия функции с таким же именем, но другим набором параметров. Таким образом, в базе данных могут существовать множество функций с одинаковым именем, но разными параметрами -перегрузка функции. Поэтому важно знать и помнить об этом, что избежать проблем с данными.
Каскадные (вложенные) соединения можно вынести в обычное соединение, при условии, что смысл запроса никак не изменится. Тогда у планировщика появляется возможность полноценно «переставлять» эти соединения на этапе поиска оптимального плана.
Вышеописанные приёмы помогут ускорить выполнение запросов, уменьшить потребление программных ресурсов и избежать возможных ошибок при переводе хранимых процедур на СУБД PostgreSQL в рамках импортозамещения.
1657
СПИСОК ЛИТЕРАТУРЫ:
1. Домбровская Г., Новиков Б., Бейликова А. Оптимизация запросов в PostgreSQL / пер. с англ. Д. А. Беликова. - М.: ДМК Пресс, 2022.: ил. С. 57-66;
2. Рогов ЕВ. PostgreSQL 16 изнутри. - М.: ДМК Пресс, 2024. С. 245-295;
3. PostgreSQL Documentation [Электронный ресурс]. URL: https://www.postgresql.org/docs/ (дата обращения: 08.06.24);
4. Inlining of SQL functions [Электронный ресурс]. URL: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions (дата обращения: 08.06.24)
Yakovlev M. V., Oblomov I.A.
Yakovlev M.V.
Chuvash State University (Cheboksary, Russia)
Oblomov I.A.
Chuvash State University (Cheboksary, Russia)
RESEARCH OF OPTIMIZATION METHODS CODE WHEN TRANSLATING STORED PROCEDURES ON POSTGRESQL WITHIN IMPORT SUBSTITUTION
Abstract: the paper provides an overview of some ways to optimize SQL code, which will be useful when translating stored procedures to PostgreSQL. The article includes an overview of typical mistakes made by developers and options for eliminating them when creating SQL queries for import substitution. Examples are presented that confirm the need to replace existing algorithms.
Keywords: optimization, SQL queries, import substitution, PostgreSQL, databases, stored procedures.
1658
